Informix fails with exception "java.sql.SQLException: Blob not found"

Troubleshooting

Problem

Informix creates temporary files for BLOB and CLOB data types. When accessing these temporary files, Informix may fail with an SQLException caused by Java™ 2 Security or unexpected changes to the directory structure containing these files.

Symptom

Informix may create temporary files in the profiles directory of the Application Server environment having format IfxTmpFile_xxxx or Ifxb_xxxx, where xxxx is a numerical value. In the event Informix cannot find or open these temporary files, the following exception will appear in the WebSphere Application Server logs:

java.sql.SQLException: Blob not found
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373)
at com.informix.jdbc.IfxResultSet.blobCheck(IfxResultSet.java:1713)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:686)
at com.informix.jdbc.IfxResultSet.b(IfxResultSet.java:626)
at com.informix.jdbc.IfxResultSet.getBlob(IfxResultSet.java:3301)
...
Caused by: java.sql.SQLException: System or internal error
java.security.PrivilegedActionException: java.io.IOException: Permission denied
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:492)
at com.informix.jdbc.IfxTmpFile.<init>(IfxTmpFile.java:96)
at com.informix.jdbc.IfxSqli.executeFetchBlob(IfxSqli.java:4894)
at com.informix.jdbc.IfxResultSet.blobCheck(IfxResultSet.java:1706)
... 4 more
Caused by: java.security.PrivilegedActionException: java.io.IOException:
Permission denied
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessController.doPrivileged(Native Method)
at com.informix.jdbc.IfxTmpFile.<init>(IfxTmpFile.java:80)
... 6 more
Caused by: java.io.IOException: Permission denied
at java.io.UnixFileSystem.createFileExclusively(Native Method)
at java.io.File.checkAndCreate(File.java:1314)
at java.io.File.createTempFile(File.java:1402)

The nested exception (For example; the exception indicated after "Caused by:") can vary based on JDK version, whether the directory containing the temporary files exists, or whether Java 2 Security is enabled, for example:

Caused by: java.sql.SQLException: System or internal error java.lang.SecurityException: Unable to create temporary file

Caused by: java.security.PrivilegedActionException: java.io.IOException: No such file or directory

Caused by: java.sql.SQLException: System or internal error java.security.PrivilegedActionException: java.io.IOException: Permission denied

Cause

Informix creates IfxTmpFile_xxxxx and Ifxb_xxxx files as temporary storage for CLOB and BLOB data types. These files, by default, are placed in the <InstallRoot>/profiles directory by Informix. Informix may fail with the exceptions above when accessing these temporary files in the default directory due to restrictive Java 2 Security file access permission or changes to either the Informix temporary files or the <InstallRoot>/profiles directory (where <InstallRoot> is the directory where you installed WebSphere Application Server).

Resolving The Problem

Specify a specific directory for Informix's temporary files by opening the data source on the WebSphere Administrative Console; then open the link for the data source custom properties; create a new custom property called ifxJDBCTEMP for your Informix data source. The value for the ifxJDBCTEMP property must be set to an absolute path name (For example; c:\temp or \opt\temp).

For more information on how to configure or change a data source, see the WebSphere Information Center section, WebSphere Application Server data source properties.

For more information on custom properties, see the WebSphere Information Center section Custom property collection

https://www.ibm.com/support/pages/informix-fails-exception-javasqlsqlexception-blob-not-found

https://www.databaseadm.com/article/11187195/temporary+files+are+not+getting+deleted