Unsupported and Restricted Functions

#### Search Results

Search

# Unsupported and Restricted Functions

- Date, Time and Timestamp

- Statements

## Date, Time and Timestamp

- Date

- Time

- Timestamp

- Timestamp with Timezone

- Floating Point

### 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

- Unsupported Statements

### Statements with Restrictions

- DELETE
- INSERT
- UPDATE
- MERGE

#### 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:

- `CALLDBPROC`

- `READ RESULT SET`

- `CONNECT TO`

- `GET DIAGNOSTICS`

### On this page

- Date, Time and Timestamp
- Statements