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 | |
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 | |
10.5.0.2 |