Inserting data from file reference variables into tables in SQLJ 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. 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.

  2. Execute an INSERT statement with the file reference variable object as the input host variable.

Example

Suppose that a table is defined like this:

CREATE TABLE TEST02TB (
  RECID INTEGER,
  CLOBCOL CLOB(100M),
  BLOBCOL(200M),
  XMLCOL XML)

The following code uses file reference variables to insert a CLOB value, a BLOB value, and an XML AS BLOB value into the table. The numbers to the right of selected statements correspond to the previously described steps.

…
com.ibm.db2.jcc.DB2ClobFileReference clobFileRef =                          1 
  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
                          // variable objects
#sql [ctx] {"INSERT INTO TEST03TB(RECID,CLOBCOL,BLOBCOL,XMLCOL)             2 
  VALUES('003',:clobFileRef,:blobFileRef,:xmlAsBlobFileRef)};