DB2 FOR LUW / DB2FORLUW / 970 - 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:                                              * 
* Please upgrade to DB2 version 970 fixpack 7                  * 
****************************************************************

Fix the invalid clause.

DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

The problem has been fixed in DB2 version 970 fixpack 7

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC84602 
follow-up : 

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

23.02.2012
29.01.2013
29.01.2013

9.7.0