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 IC84602 Status: Closed

SETTING VARIABLE WITH DURATION FAILS WITH SQL0901 SQLNQ_CONVERTNODE.

product:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problem description:
Under rare scenarios, setting a variable to a duration value 
might fail with a SQL0901N error message. 
 
This can manifest in two ways: 
1. Stored procedure creation fails with SQL0901 
SQLNQ_CONVERTNODE 
due to presence of invalid clause. 
 
CREATE PROCEDURE db2inst1.t1 (IN PDATE TIMESTAMP) 
    DYNAMIC RESULT SETS 1 
    LANGUAGE SQL 
    NO EXTERNAL ACTION 
BEGIN 
    DECLARE V_ROLLUP_BEGIN TIMESTAMP;-- 
    SET V_ROLLUP_BEGIN = PDATE;-- 
    SET V_ROLLUP_BEGIN = - 2 DAYS;--    <<<<<<<<< invalid clause 
 
END 
@ 
 
2. Global variable creation might fail if default value is 
invalid. 
create variable v1 timestamp default 8 days; 
 
Here's the error - 
 
DB21034E  The command was processed as an SQL statement because 
it was not a 
valid Command Line Processor command.  During SQL processing it 
returned: 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason 
"SQLNQ_CONVERTNODE 010".) 
LINE NUMBER=131.  SQLSTATE=58004 
 
Here's a snippet from the db2diag.log - 
 
2012-02-06-02.03.39.516686-300 E48851E1297         LEVEL: Info 
(Origin) 
PID     : 25523                TID  : 46912925329728PROC : 
db2sysc 2 
INSTANCE: db2inst1             NODE : 002          DB   : SAMPLE 
APPHDL  : 2-77                 APPID: *N2.db2inst1.120206070134 
AUTHID  : DB2INST1 
EDUID   : 44                   EDUNAME: db2agent (SAMPLE) 2 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 
MESSAGE : ZRC=0x8031000A=-2144272374=SQLNN_E_BADTYP 
"unrecognized type code" 
DATA #1 : String, 62 bytes 
An unexpected error was detected during statement compilation. 
DATA #2 : Boolean, 1 bytes 
true 
DATA #3 : Boolean, 1 bytes 
false 
DATA #4 : Boolean, 1 bytes 
false 
DATA #5 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCAL    sqlcabc: 136   sqlcode: -901   sqlerrml: 21 
 sqlerrmc: SQLNQ_CONVERTNODE 010 
 sqlerrp : SQLNQ23A 
 sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 
0x00000083 
           (4) 0x00000000      (5) 0xFFFFFFF6      (6) 
0x00000002 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 
DATA #6 : String, 157 bytes 
Compiler error stack for rc = -2144272374: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[330] 
sqlnp_smactn[100] 
sqlnq_psm_sem_act[2590] 
sqlnq_convertNode[10]
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Problem is fixed in DB2 version 101 fixpack 2                * 
****************************************************************
Local Fix:
Fix the invalid clause.
available fix packs:
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

Solution
Problem is fixed in DB2 version 101 fixpack 2
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
14.06.2012
17.01.2013
17.01.2013
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
10.1.0.2 FixList
10.5.0.2 FixList