IBM Support

Error SQL1188N returned when a DB2 LOAD from a cursor is executed when the declare cursor select list has NULL values in place of decimal columns in the target table.

Troubleshooting


Problem

A DB2 LOAD from a cursor might fail with an SQL1188N error if there are NULL values for the decimal data types in the select list of the declare cursor statement.

Symptom

When attempting a LOAD from a cursor and the 'declare cursor' select list has NULLs for the decimal data types the subsequent LOAD, using the previously defined cursor, might fail with an SQL1188N error:

These are some example table definitions with decimals:

db2 "create table tab1 (col1 varchar(10), col2 timestamp)"
db2 "create table tab2 (col1 varchar(10), col2 timestamp, col3 varchar(3), col4 decimal(10,0), col5 timestamp)"

Declare the cursor definition:
db2 "declare curs cursor for select col1, col2, null, null, null from tab1"

LOAD from that cursor:
db2 "load from curs of cursor messages load_cursor.txt insert into tab2 nonrecoverable"

SQL1188N Column "4" of the SELECT or VALUES statement is not compatible with
table column "4". The source column has sqltype "448", and the target column
has sqltype "484".

Other data types like BIGINT could be affected too.

Cause

The NULLs need to be explicitly cast to decimals. The DB2 INSERT function is unaffected as it will implicitly cast the NULLs to decimals.

Resolving The Problem

To resolve the SQL1188N during a LOAD from a cursor in these circumstances you can explicitly cast the NULLs as decimals:

db2 "declare cur cursor for select col1, col2, NULL, cast(NULL as decimal), NULL from tab1"
db2 "load from cur of cursor messages load_cursor.txt insert into tab2 nonrecoverable"

Ensure all affected datatypes are explicitly cast, for example:

db2 "declare cur cursor for select col1, col2, NULL, cast(NULL as decimal), NULL, cast(null as decimal), cast(null as decimal), null from tab1"

[{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Data Movement - Load","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21982183