Restrictions


Restrictions related to Db2 datatypes

Casting DATE and TIME datatypes to CHAR

When casting a DATE/TIME datatype column to CHAR, the DATE/TIME format resulting from the function will always have the ISO format. The format in the db2.properties is not honoured. This is due to a Db2 JDBC driver limitation. For JCC packages, the pre-compiler DATE/TIME option is set to ISO. The CHAR function default behaviour is influenced by the pre-compiler DATE/TIME option which has precedence over the DECP options. You can use the additional parameter in the CHAR function to change the DATE/TIME format.

CHAR(CURRENT DATE)
/* RESULT FORMAT EXAMPLE: 2023-05-01

CHAR(CURRENT TIME)
/* RESULT FORMAT EXAMPLE: 01.01.01

Retrieval of Timestamp data with a precision more than nine

When retrieving timestamp data, the data is retrieved in a standard JDBC format with a precision of up to nine. This is due to a Db2 JDBC driver limitation. Casting the result to CHAR solves the problem.

CURRENT TEMPORAL BUSINESS_TIME
/* RESULT EXAMPLE: 2011-02-28-01.01.01.123456789
CHAR(CURRENT TEMPORAL BUSINESS_TIME)
/* RESULT EXAMPLE: 2011-02-28-01.01.01.123456789012
CURRENT TIMESTAMP(12)     
/* RESULT EXAMPLE: 2023-04-25-11.06.11.292553363
CHAR(CURRENT TIMESTAMP(12))
/* RESULT EXAMPLE: 2023-04-25-11.06.11.292553363769

Retrieval of Timestamp data with time zone

When retrieving data from a TIMESTAMP WITH TIMEZONE column, the data is retrieved in a standard JDBC format without a time zone. The returned value is adjusted for the difference between the time zone of the column value and the default time zone.

This is due to a Db2 JDBC driver limitation. Casting the result to CHAR solves the problem.

SELECT TIMESTAMP_W_TIMEZONE_COLUMN FROM ...
/* RESULT EXAMPLE: 2012-02-29-05.03.59.100000
SELECT CHAR(TIMESTAMP_W_TIMEZONE_COLUMN) FROM ...
/* RESULT EXAMPLE: 2012-02-29-07.03.59.100000+02:00 

Difference in Round off mechanism for Floating Point datatype

When retrieving data from a FLOATING POINT column which has data more than 16 digits, the last digit value may be different from the Natural for Db2 (NDB) result. This is due to the JDBC driver round off mechanism is different than Db2 for z/OS round off mechanism for Floating point datatype.

/* Result with NDB
STDDEV SALARY = .9742432961021595E 04
/* Result with NDZ
STDDEV SALARY = .9742432961021594E 04
/* Result with NDB
STDDEV SALARY =  9742.432961021595
/* Result with NDZ
STDDEV SALARY =  9742.432961021594

Restrictions related to Db2 statements

The following Natural/Db2 SQL statements are currently not supported by Natural for Db2 for zIIP as JDBC drivers do not support them natively.

  • CONNECT TO

  • GET DIAGNOSTICS

Restrictions related to static preparation

SET CURRENT DECFLOAT MODE = :HV1 fails with SQL error -104

SQL statement SET CURRENT DECFLOAT MODE = :HV1 fails with SQL error -104 during static preparation. This is due to a bug in IBM SQLJ customization and BIND. Alternately, the statement works fine when keyword or literal is specified instead of host variable.

SQL statement to set global variables fails during static preparation

The SQL statement to set the values for global variables fails during static preparation with the message ‘Unsupported statement’. This is due to the SQLJ customization and BIND behaviour.

SENSITIVE DYNAMIC cursor changes to SENSITIVE STATIC during program static preparation

During static preparation process, cursor type SENSITIVE DYNAMIC is changed to SENSITIVE STATIC. SENSITIVE DYNAMIC usage is currently not supported.

FORWARD ONLY cursor changes to SENSITIVE STATIC during program static preparation

During static preparation process, cursor type FORWARD ONLY is changed to SENSITIVE STATIC when we have positioning update on a Rowset. This is due to JDBC doesn’t support rowset natively.

Restrictions related to dynamic preparation

Cursor Sensitivity

Cursor types SENSITIVE DYNAMIC, STATIC and ASENSITIVE will be changed to the cursor type specified in the JDBC property ‘cursorSensitivity’. This JDBC property can be defined in the Db2.properties file.

The default value is STATIC. For more details on the cursorSensitivity property, please refer to IBM documentation.