Inserting data from file reference variables into tables in JDBC applications

You can use file reference variable objects with IBM® Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2® for z/OS® Version 9 or later to stream LOB or XML input data.

Before you begin

You need to store your LOB or XML input data in HFS files.

About this task

Use of file reference variables eliminates the need to materialize the LOB or XML data in memory before the data is stored in tables.

Procedure

To use file reference variables to store LOB or XML data in tables, follow these steps:

  1. Invoke the Connection.prepareStatement method to create a PreparedStatement object from an INSERT statement.

    The parameter markers in the INSERT statement represent XML or LOB values.

  2. Execute constructors for file reference variable objects of the appropriate types.

    The following table lists the types of data in the input files and the appropriate constructors.

    Input data type Constructor
    BLOB com.ibm.db2.jcc.DB2BlobFileReference
    CLOB com.ibm.db2.jcc.DB2ClobFileReference
    XML AS BLOB com.ibm.db2.jcc.DB2XmlAsBlobFileReference
    XML AS CLOB com.ibm.db2.jcc.DB2XmlAsClobFileReference

    The first parameter in each constructor must specify the absolute path name for an existing HFS file.

  3. If you are performing single-row INSERT operations, repeat these steps for each row that you want to insert:
    1. Invoke DB2PreparedStatement.setXXX to pass values to the input variables. Alternatively, you can use PreparedStatement.setObject methods.

      The following table lists the types of data in the input files and the appropriate DB2PreparedStatement.setXXX methods to use for each data type.

      Input data type DB2PreparedStatement.setXXX method
      BLOB setDB2BlobFileReference
      CLOB setDB2ClobFileReference
      XML AS BLOB setDB2XmlAsBlobFileReference
      XML AS CLOB setDB2XmlAsClobFileReference

      If you use DB2PreparedStatement methods, you need to cast the PreparedStatement object that you created in step 1 to a DB2PreparedStatement object when you execute a DB2PreparedStatement.setXXX method.

      You can assign NULL values to input parameters in any of the following ways:
      • Using DB2PreparedStatement.setXXX methods, with null as the fileRef parameter value.
      • Using PreparedStatement.setObject, with null as the x (second) parameter value and the appropriate value from com.ibm.db2.jcc.DB2Types for the targetJdbcType (third) parameter value.
      • Using PreparedStatement.setNull, with the appropriate value from com.ibm.db2.jcc.DB2Types for the JdbcType (second) parameter value.
    2. Invoke the PreparedStatement.execute or PreparedStatement.executeUpdate method to update the table with the variable values.
  4. If you are performing multi-row INSERT operations, execute these steps:
    1. Repeat these steps for every row that you want to insert:
      1. Invoke DB2PreparedStatement.setXXX to pass values to the input variables. Alternatively, you can use PreparedStatement.setObject methods.
      2. Invoke the PreparedStatement.addBatch method after you set the values for a row of the table.
    2. Invoke the PreparedStatement.executeBatch method to update the table with the variable values.
  5. Invoke the PreparedStatement.close method to close the PreparedStatement object when you have finished using that object.

Examples

The following code inserts a single row into a table. The code inserts values from CLOB and BLOB file reference variables into CLOB and BLOB columns and a NULL value into an XML column. The numbers to the right of selected statements correspond to the previously-described steps.

Connection conn;
…
PreparedStatement pstmt = 
  conn.prepareStatement(
    "INSERT INTO TEST02TB(RECID,CLOBCOL,BLOBCOL,XMLCOL) VALUES('003',?,?,?)");
                          // Create a PreparedStatement object              1 
com.ibm.db2.jcc.DB2ClobFileReference clobFileRef = 
  new com.ibm.db2.jcc.DB2ClobFileReference("/u/usrt001/jcc/test/TEXT.FILE","Cp037");
com.ibm.db2.jcc.DB2BlobFileReference blobFileRef = 
  new com.ibm.db2.jcc.DB2BlobFileReference("/u/usrt001/jcc/test/BINARY.FILE");
com.ibm.db2.jcc.DB2XmlAsBlobFileReference xmlAsBlobFileRef = 
  new com.ibm.db2.jcc.DB2XmlAsBlobFileReference(
  "/u/usrt001/jcc/test/XML.FILE");
                          // Execute constructors for the file reference    2 
                          // variable objects
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setDB2ClobFileReference(1,clobFileRef);
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setDB2BlobFileReference(2,blobFileRef);
pstmt.setNull(3,com.ibm.db2.jcc.DB2Types.XML_AS_BLOB_FILE);
                          // Assign values to the CLOB and BLOB parameters. 3a 
                          // Assign a null value to the XML parameter.
int numUpd = pstmt.executeUpdate();   
                          // Perform the update                             3b 
pstmt.close();            // Close the PreparedStatement object             5 

The following code uses multi-row INSERT to insert two rows in a table. The code inserts values from XML AS CLOB and XML AS BLOB file reference variables into XML columns. The numbers to the right of selected statements correspond to the previously-described steps.

Connection conn;
…
PreparedStatement pstmt = 
  conn.prepareStatement(
    "INSERT INTO TEST03TB(RECID,XMLCLOBCOL,XMLBLOBCOL) VALUES('003',?,?)");
                          // Create a PreparedStatement object              1 
com.ibm.db2.jcc.DB2XmlAsClobFileReference xmlAsClobFileRef1 = 
  new com.ibm.db2.jcc.DB2XmlAsClobFileReference("/u/usrt001/jcc/test/XMLCLOB1.FILE","Cp037");
com.ibm.db2.jcc.DB2XmlAsBlobFileReference xmlAsBlobFileRef1 = 
  new com.ibm.db2.jcc.DB2XmlAsBlobFileReference("/u/usrt001/jcc/test/XMLBLOB1.FILE");
com.ibm.db2.jcc.DB2XmlAsClobFileReference xmlAsClobFileRef2 = 
  new com.ibm.db2.jcc.DB2XmlAsClobFileReference("/u/usrt001/jcc/test/XMLCLOB2.FILE","Cp037");
com.ibm.db2.jcc.DB2XmlAsBlobFileReference xmlAsBlobFileRef2 = 
  new com.ibm.db2.jcc.DB2XmlAsBlobFileReference("/u/usrt001/jcc/test/XMLBLOB2.FILE");
                          // Execute constructors for the file reference    2 
                          // variable objects
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setDB2ClobFileReference(1,xmlAsClobFileRef1);
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setDB2BlobFileReference(2,xmlAsBlobFileRef1);
                          // Assign first set of values to the              4ai 
                          // XML parameters
pstmt.addBatch();         // Add the first input parameters to the batch    4aii 
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setDB2ClobFileReference(1,xmlAsClobFileRef2);
((com.ibm.db2.jcc.DB2PreparedStatement)pstmt).setDB2BlobFileReference(2,xmlAsBlobFileRef2);
                          // Assign second set of values to the             4ai 
                          // XML parameters
pstmt.addBatch();         // Add the second input parameters to the batch   4aii 
int [] numUpd = pstmt.executeBatch();   
                          // Perform the update                             4b 
pstmt.close();            // Close the PreparedStatement object             5