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
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:
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