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"
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21982183