IBM Support

IC76688: SQL0204N RETURNED WHEN COMPILING AN SQL STATEMENT WITH A SUBQUER Y REFERENCING A GLOBAL OR MODULE VARIABLE IN REOPT ALWAYS MODE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

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

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(?);
    

Problem summary

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

Problem conclusion

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

Temporary fix

Comments

APAR Information

  • APAR number

    IC76688

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2011-05-30

  • Closed date

    2012-01-17

  • Last modified date

    2016-06-21

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC76688

Modified date: 21 June 2016