DB2 FOR LUW / DB2FORLUW / A10 - DB2

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]

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Problem is fixed in DB2 version 101 fixpack 2                * 
****************************************************************

Fix the invalid clause.

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
 

Problem is fixed in DB2 version 101 fixpack 2

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

14.06.2012
17.01.2013
17.01.2013