|
Problem IC76688
|
Status: Closed |
SQL0204N RETURNED WHEN COMPILING AN SQL STATEMENT WITH A SUBQUER Y REFERENCING A GLOBAL OR MODULE VARIABLE IN REOPT ALWAYS MODE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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] |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See ERROR DESCRIPTION. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v9.7 Fixpack 5 *
**************************************************************** |
| Local Fix: |
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(?); |
| available fix packs: |
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
|
| Solution |
Problem was first fixed in v9.7 Fixpack 5. This is a server
side fix. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 30.05.2011
17.01.2012
21.06.2016 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
