Unsupported and Restricted Functions

Date, Time and Timestamp

Date

When casting a DATE to CHAR, the date format resulting from the function will always have the ISO format. It will not honor the format selected in the db2.properties. This occurs due to a Db2 JDBC driver limitation. 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

Time

When casting a TIME to CHAR, the time format resulting from the function will always have the ISO format. It will not honor the format selected in the db2.properties file. This occurs due to a Db2 JDBC driver limitation. You can use the additional parameter in the CHAR function to change the DATE/TIME format 

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

Timestamp

When selecting timestamp data, if the fractional part of the source column has more than 9 digits, the result may be truncated to 9 digits. This occurs 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

Timestamp with Timezone

When selecting data from a column of the type TIMESTAMP WITH TIMEZONE, the time zone is not provided in the result. This occurs 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

Floating Point

When selecting data from a FLOATING POINT column type, and the data has more than 16 digits, the round off rule is different from Natural for Db2 (NDB). Thus, the last digit value may be different from the NDB result. 

/* 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

Statements

Statements with Restrictions

DELETE

Positioned DELETE over a rowset using the clause FOR ROW or WHERE CURRENT OF is not supported by Natural for Db2 for zIIP. A positioned delete using the clause WHERE CURRENT OF is supported over a single row.

INSERT

Natural for Db2 for zIIP does not support multiple-row-insert.

UPDATE

Positioned UPDATE over a rowset using the clause FOR ROW or WHERE CURRENT OF is not supported by Natural for Db2 for zIIP. A positioned update using the clause WHERE CURRENT OF is supported over a single row.

MERGE

Natural for Db2 for zIIP does not support MERGE statements with multiple rows (values-multiple-row).

Unsupported Statements

The following Natural/Db2 SQL statements are not supported by Natural for Db2 for zIIP: