home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC64063 Status: Closed

UNNECESSARY CODEPAGE CONVERSION WHEN LOADING DATA FROM DATE OR TIME OR
TIMESTAMP COLUMN, INTO CHARACTER TYPE COLUMNS.

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
This problem is specific for LOAD FROM CURSOR. It does not 
affect LOAD FROM FILES. 
 
When you load data into character type columns from DATE or TIME 
or TIMESTAMP column, unnecessary codepage conversion is 
performed and error message is reported in db2diag.log. 
 
1) In db2 version 9.5, 
here are the error messages reported by db2 load from cursor: 
=============================== 
2008-10-15-15.24.05.106581+480 I7322547A445       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:30 
DATA #1 : signed integer, 4 bytes 
-2029059907 
 
2008-10-15-15.24.05.145696+480 I7322993A480       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:100 
DATA #1 : unsigned integer, 4 bytes 
950 
DATA #2 : unsigned integer, 4 bytes 
950 
 
2008-10-15-15.24.05.145917+480 I7323474A503       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:105 
DATA #1 : Hexdump, 10 bytes 
0x07000000667FCC30 : 3030 3031 2D30 312D 3031 
0001-01-01 
 
2008-10-15-15.24.05.146143+480 I7323978A524       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:110 
DATA #1 : Pointer, 8 bytes 
0x07000000667fcc30 
DATA #2 : unsigned integer, 4 bytes 
10 
DATA #3 : unsigned integer, 4 bytes 
10 
 
2008-10-15-15.24.05.146370+480 I7324503A524       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:120 
DATA #1 : Pointer, 8 bytes 
0x070000033c8937a5 
DATA #2 : unsigned integer, 4 bytes 
10 
DATA #3 : unsigned integer, 4 bytes 
10 
 
2008-10-15-15.24.05.146595+480 I7325028A436       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:130 
DATA #1 : signed integer, 4 bytes 
1 
 
2008-10-15-15.24.05.146769+480 I7325465A476       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:140 
DATA #1 : unsigned integer, 4 bytes 
0 
DATA #2 : unsigned integer, 4 bytes 
0 
 
2008-10-15-15.24.05.146968+480 I7325942A492       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:150 
DATA #1 : Pointer, 8 bytes 
0x07000000667fcc3a 
DATA #2 : Pointer, 8 bytes 
0x070000033c8937a5 
 
2008-10-15-15.24.05.147166+480 I7326435A628       LEVEL: Error 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, National Language Support, sqlnlscpst2, 
probe:160 
DATA #1 : unsigned integer, 4 bytes 
0 
DATA #2 : Hexdump, 32 bytes 
0x07000000667FCAF8 : 0000 0000 0000 0000 0700 0003 3C89 37AF 
............<.7. 
0x07000000667FCB08 : 0000 0000 0000 0000 0000 0101 0101 FFFF 
................ 
 
2008-10-15-15.24.05.147351+480 I7327064A510       LEVEL: Severe 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, data services, sqlrxput, probe:130 
MESSAGE : sqlnlscpst2 error 
DATA #1 : Hexdump, 4 bytes 
0x07000000667FCC10 : 870F 00BD 
.... 
=============================== 
 
2) In db2 version 9.1, 
you will see only one error message like this in db2diag.log: 
======================================== 
2008-10-15-15.24.05.147351+480 I7327064A510       LEVEL: Severe 
PID     : 258182               TID  : 36938       PROC : db2sysc 
0 
INSTANCE: db2inst1             NODE : 000         DB   : SAMPLE 
APPHDL  : 0-4911               APPID: 
*LOCAL.db2inst1.081015072139 
AUTHID  : DWADM 
EDUID   : 36938                EDUNAME: db2lfrm0 0 
FUNCTION: DB2 UDB, data services, sqlrxput, probe:130 
MESSAGE : sqlnlscpst2 error 
DATA #1 : Hexdump, 4 bytes 
0x07000000667FCC10 : 870F 00BD 
.... 
======================================== 
 
Anyway, "870F 00BD" reported by function sqlrxput is the 
eye-catcher for this problem. 
"870F 00BD" equals -2029059907 and represents SQLO_CPSAME. 
 
Here is a reproduce steps for your reference: 
================= 
On system 1: 
db2set db2codepage=950 
db2set db2comm=tcpip 
db2start 
db2 create db cp950src using codeset big5 territory TW 
db2 connect to cp950src 
db2 "create table t (d date)" 
db2 "insert into t values( date('0001-01-01') )" 
db2 terminate 
 
On system 2: 
(catalog db cp950src from system 1) 
db2set db2codepage=950 
db2start 
db2 create db cp950tgt using codeset big5 territory TW 
db2 connect to cp950tgt 
db2 "create table t2 (vc long varchar)" 
db2 declare c cursor database cp950src user myuserid password 
mypasswd for select d from t 
db2 load from c of cursor replace into t2 
=================
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users of LOAD                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See the error description.                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.7 FP1.                                     * 
****************************************************************
Local Fix:
You have at least two methods to bypass this problem. 
1) Explicitly cast the date/time/timestamp column into character 
string type in the cursor. 
   For example, with the above reproduce step, do this instead: 
   db2 "declare c cursor database cp950src user myuserid 
password mypasswd for select varchar(d) from t" 
2) Use laod from files.
available fix packs:
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
Solution
Problem fixed in DB2 V9.7 FP1
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

22.10.2009
22.02.2010
22.02.2010
Problem solved at the following versions (IBM BugInfos)
9.7.FP1
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.1 FixList