TIMESTAMP WITH TIME ZONE values in SQLJ applications
Db2 for z/OS® supports table columns with the TIMESTAMP WITH TIME ZONE data type. IBM® Data Server Driver for JDBC and SQLJ supports update into and retrieval from a column with the TIMESTAMP WITH TIME ZONE data type in SQLJ programs.
When you update or retrieve a TIMESTAMP WITH TIME ZONE value, or call a stored procedure with a TIMESTAMP WITH TIME ZONE parameter, you need to use host variables that are com.ibm.db2.jcc.DBTimestamp objects to retain the time zone information. If you use java.sql.Timestamp objects to pass TIMESTAMP WITH TIME ZONE values to and from the data server, you lose the time zone information.
Because the com.ibm.db2.jcc.DBTimestamp class is a IBM Data Server Driver for JDBC and SQLJ-only class, if you run an uncustomized SQLJ application that uses com.ibm.db2.jcc.DBTimestamp objects, the application receives an SQLException.
Examples
Suppose that table TSTABLE has a single column, TSCOL, which has data type TIMESTAMP WITH TIME ZONE. The following code assigns a timestamp value with a time zone to the column, and retrieves the value from the column.
#sql iterator TSIter(com.ibm.db2.jcc.DBTimestamp TSVar);
{
…
java.util.TimeZone esttz = java.util.TimeZone.getTimeZone("EST");
// Set the time zone to UTC-5
java.util.Calendar estcal= java.util.Calendar.getInstance(esttz);
// Create a calendar instance
// with the EST time zone
java.sql.Timestamp ts =
java.sql.Timestamp.valueOf("2009-02-27 21:22:33.444444");
// Initialize a timestamp object
// with the datetime value that you
// want to put in the table
com.ibm.db2.jcc.DBTimestamp dbts =
new com.ibm.db2.jcc.DBTimestamp(ts,estcal);
// Create a datetime object that
// includes the time zone
#sql[ctx] {INSERT INTO TSTABLE (TSCOL) VALUES (:dbts)};
// Insert the datetime object in
// the table
#sql[ctx] {COMMIT};
TSIter iter = null;
#sql [ctx] iter = {SELECT TSCOL FROM TSTABLE};
// Assign the result table of the SELECT
while (iter.next()) {
System.out.println ("Timestamp = " +
((com.ibm.db2.jcc.DBTimestamp)iter.TSVar()).toDBString(true));
// Use accessor method TSVar to retrieve
// the TIMESTAMP WITH TIME ZONE value,
// cast it to a DBTimestamp value,
// and retrieve its string representation.
// Value retrieved:
// 2009-02-27 21:22:33.444444-05:00
}
}Suppose that stored procedure TSSP has a single INOUT parameter, TSPARM, which has data type TIMESTAMP WITH TIME ZONE. The following code calls the stored procedure with a timestamp value that includes a time zone, and retrieves a parameter value with a timestamp value that includes a time zone.
{
…
java.util.TimeZone esttz = java.util.TimeZone.getTimeZone("EST");
// Set the time zone to UTC-5
java.util.Calendar estcal= java.util.Calendar.getInstance(esttz);
// Create a calendar instance
// with the EST time zone
java.sql.Timestamp ts =
java.sql.Timestamp.valueOf("2009-02-27 21:22:33.444444");
// Initialize a timestamp object
// with the timestamp value that you
// want to pass to the stored procedure
com.ibm.db2.jcc.DBTimestamp dbts =
new com.ibm.db2.jcc.DBTimestamp(ts,estcal);
// Create a timestamp object that
// includes the time zone to
// pass to the stored procedure
#sql[ctx] { CALL TSSP (:INOUT dbts) };
System.out.println ("Output parameter: " + dbts.toDBString (true));
// Call the stored procedure with
// the timestamp value as input,
// and retrieve a timestamp value
// with a time zone in the same
// parameter
}