Restrictions

#### Search Results

Search

# Restrictions

- Restrictions related to Db2 datatypes

- Restrictions related to Db2 statements

- Restrictions related to static preparation

- Restrictions related to dynamic preparation

## Restrictions related to Db2 datatypes

- Casting DATE and TIME datatypes to CHAR

- Retrieval of Timestamp data with a precision more than nine

- Retrieval of Timestamp data with time zone

- Difference in Round off mechanism for Floating Point datatype

### 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 to set global variables fails during static preparation

- SENSITIVE DYNAMIC cursor changes to SENSITIVE STATIC during program static preparation

- FORWARD ONLY cursor changes to SENSITIVE STATIC during program 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.

### On this page

- Restrictions related to Db2 datatypes
- Restrictions related to Db2 statements
- Restrictions related to static preparation
- Restrictions related to dynamic preparation