Working with Extended Timestamp Precision

Provides information on using the Timestamp data type with extended timestamp precision, which is available in release 7.2 and later, with the Db2® for i CLI functions.

Extended Timestamp Precision in CLI applications

In Db2 for i, timestamps now have increased and variable precision, with timestamp precision having a range of 0-12. To accommodate this change, CLI has been updated to allow the user to specify and retrieve the precision for timestamp parameters and columns. These changes include a means to preserve the existing behavior, since there can be unexpected side effects to your CLI applications if they are not coded to take advantage of this new support. To preserve existing behavior, use the SQL_ATTR_TIMESTAMP_PREC connection attribute.

Using the new SQL_ATTR_TIMESTAMP_PREC connection attribute

Since changing applications to take advantage of the increased timestamp precision can take a long time to implement and test, there is a new connection attribute, SQL_ATTR_TIMESTAMP_PREC, which can be set to SQL_TRUE to cause APIs to revert to the prior release behavior for timestamp types. This is meant as a temporary measure to allow existing applications to run with minimal modification on IBM® i 7.2 , until they can be updated to comply with the new behavior. With this attribute set, timestamps are always treated as a 26 byte, fixed length value with a precision of 6. Applications using this attribute will be unable to insert timestamps with a precision greater than 6 using parameter markers and any timestamp columns fetched with greater than 6 precision will be truncated (and any column with less than 6 precision will be padded with zeroes).

Examples of necessary changes for Existing CLI Applications

If you do not set the new SQL_ATTR_TIMESTAMP_PREC connection attribute to SQL_TRUE, then an existing application may see these side effects when running against a Db2 for i database in a 7.2 release or later, if that application binds parameters using the SQL_TYPE_TIMESTAMP type.

For example, an application calling SQLBindParameter may have passed the value 0 for the ColumnSize parameter, since it was ignored for timestamps in earlier releases:

   :
char *ts = "1970-01-01 12:34:56.123456";
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 0, 6, ts, 0, &ind); 
SQLExecute(hstmt);
   :
// If a timestamp that is bound as shown above is then passed on the SQLExecute call, it will fail with 
// SQLCODE -303 "Variable *N not compatible or value too long", because of the ColumnSize parameter being 0.
// To correct this problem, bind the parameter as follows, with a ColumnSize parameter of 26 : 
   : 
char *ts = "1970-01-01 12:34:56.123456";
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP,26, 6, ts, 0, &ind); 
SQLExecute(hstmt);
   :

Perhaps instead, the timestamp was stored in a large buffer and the size of the buffer was passed in:

   :
char buffer[50] = "1970-01-01 12:34:56.123456";
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, sizeof(buffer), 6, buffer, 0, &ind);
SQLExecute(hstmt);
   :
// If a timestamp that is bound as shown above is then passed on the SQLExecute call, it will fail with 
// SQLCODE -180 ""Syntax of date, time, or timestamp value not valid.", because of the ColumnSize parameter being 
// sizeof(buffer), or 50.  
// To correct this problem, bind the parameter as follows, with a ColumnSize parameter of 26 : 
   : 
char buffer[50] = "1970-01-01 12:34:56.123456";
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP,26, 6, ts, 0, &ind); 
SQLExecute(hstmt);
   :

Note that the same problem occurs when binding timestamp types on Db2 for i CLI SQLBindParam and SQLBindCol functions.

To fix the problems described in the examples above, use either the corrective action shown in the example or something similar to insure the ColumnSize parameter is set properly. Here are the details on the changes for the parameters for the SQLBindParameter, SQLBindParam, and the SQLBindCol functions:

SQLBindParameter, ColumnSize must be between 19 and 32 and DecimalDigits must be between 0 and 12.
SQLBindParam, cbParamDef must be between 19 and 32 and ibScale must be between 0 and 12.
SQLBindCol, cbValueMax must be greater than or equal to 19.

The easiest way to always ensure these values are correct is to use the information retrieved using SQLDescribeParam for parameter markers and SQLDescribeCol or SQLColAttribute for columns.