DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under REOPT=ALWAYS, when compiling an SQL statement that 
contains a subquery referencing a global or module variable, the 
following message may be returned even though the object already 
exists: 
 
SQL0727N  An error occurred during implicit system action type 
"6". 
Information returned for the error includes SQLCODE "-204", 
SQLSTATE "42704" 
and message tokens "<variable name>".  SQLSTATE=56098 
 
If the DBM CFG DIAGLEVEL=4 the following may also be returned: 
 
2011-01-01-12.00.49.365836-240 I12241237A685      LEVEL: Info 
PID    : 26607748            TID  : 49902      PROC : db2sysc 
0 
INSTANCE: db2inst1              NODE : 000        DB  : SAMPLE 
APPHDL  : 0-130                APPID: 
192.168.1.100.9402.110525222148 
AUTHID  : db2inst1 
EDUID  : 94902                EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670 
MESSAGE : ZRC=0x803100FF=-2144272129=SQLNN_E_QGMCOL 
          "generic error associated with columns" 
DATA #1 : String, 155 bytes 
Compiler error stack for rc = -2144272129: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[330] 
sqlnp_smactn[100] 
sqlnq_sem[464] 
sqlnq_check_correlation[110]

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See ERROR DESCRIPTION.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v9.7 Fixpack 5                                    * 
****************************************************************

If the value of the global or module variable does not change 
within the procedure, a workaround would be to declare a local 
variable to store its value, and then replace the global or 
module variable reference in the subquery with a reference to 
the local variable. 
 
For example: 
 
(Assume the existence of a schema S1 and a module variable 
ModuleA.var1) 
 
  call set_routine_opts('REOPT ALWAYS'); 
 
  CREATE OR REPLACE PROCEDURE s1.proc1(out V_ATTR int) 
  BEGIN 
    SELECT M.MVAR1 
      into V_ATTR 
            FROM 
                (SELECT 
                    ModuleA.var1 MVAR1 
                   FROM 
                    sysibm.sysdummy1 
                  ) M;-- 
  END ; 
 
  call s1.proc1(?); 
 
can be changed to: 
 
  call set_routine_opts('REOPT ALWAYS'); 
 
  CREATE OR REPLACE PROCEDURE s1.proc1( out V_ATTR int) 
  BEGIN 
    declare localvar1 int;-- 
    set localvar1 = ModuleA.var1;-- 
 
    SELECT M.MVAR1 
      into V_ATTR 
            FROM 
                (SELECT 
                    localvar1 MVAR1 
                   FROM 
                    sysibm.sysdummy1 
                  ) M;-- 
  END ; 
 
  call s1.proc1(?);

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

Problem was first fixed in v9.7 Fixpack 5.  This is a server 
side fix.

not known / see Local fix

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

30.05.2011
17.01.2012
21.06.2016

9.7.FP5