PH07494: EXECUTE GENERATED VIEW DDL SQLCODE -206 COMMON TABLE EXPRESSION CTE GEN ERROR NEW VALUE FOR SQL_STMT_PREP TOO LARGE STRINGSIZE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• This APAR addresses two issues:
  1.  The GEN of a view containing a COMMON TABLE EXPRESSION may
  fail with the following errors:
  Error in COMMON_TABLE_EXPRESSION:New value for sql_stmt_prep is
  too large in procedure.
  STRINGSIZE condition was raised
  Traceback of user routines:
  Compile Unit   Entry          Statement  CU offset   Entry
  offset  Address
                 _Concnvs                  +00000062   +00000062
     3BB858EA
                 ML_SUBSTR      3363       +00000502   +00000502
     3BB7B5EA
                 _Begin_9907_Blk_53
                                9917       +000001DE   +000001DE
     3BB8017E
                 COMMON_TABLE_EXPRESSION
                                9907       +0000083E   +0000083E
     3BB61546
                 VIEW_STMT      9677       +000009C8   +000009C8
     3BB62490
                 CREATE_STMT    6330       +00001C6C   +00001C6C
     3BB729EC
                 SQL_STATEMENT  3836       +00000FA6   +00000FA6
     3BB78EAE
                 PARSE          3605       +00000136   +00000136
     3BB7AE16
                 ADB2PA         2967       +00002B2C   +00002B2C
     3BA9CA3C
                 PARSE_SQL_STMT 7019       +000001D4   +000001D4
     3B8D89BC
                 EDIT_DDL_STMT  6023       +00000490   +00000490
     3B8D8FB0
                 CREATE_VIEW    30418      +0000134C   +0000134C
     3B8935F4
                 ADB2REG        3640       +00000FC0   +00000FC0
     3B8722D8
                 GENERATE       3338       +00001420   +00001420
     3BF0F888
                 REU0023        2125       +0000120C   +0000120C
     3BF00E3C
                 GENERATE       2082       +00000084   +00000084
     3BE5FDEC
                 GEN_VIEWS      7911       +000026AE   +000026AE
     3BE5411E
                 ADB2REP        2311       +000014E2   +000014E2
     3BDFA4DA
                 ADB2GEN        3509       +00005408   +00005408
     3BC83170
  Note: Statement numbers and offsets are approximate, and may
  vary somewhat, depending on release or other maintenance
  applied.
  2.  An attempt to execute GEN-generated DDL of a view
  containing numerous Common Table Expressions (CTEs), may result
  in an SQLCODE -206 / SQLCODEN206 error:
  DSNT408I SQLCODE = -206, ERROR:  schema.viewname.colname IS NOT
           VALID IN THE CONTEXT WHERE IT IS USED
  DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE
  DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD    = -225 0  0  -1  0  0 SQL DIAGNOSTIC
  INFORMATION
  DSNT416I SQLERRD    = X'FFFFFF1F'  X'00000000'  X'00000000'
  X'FFFFFFFF'
           X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool         *
  *                 for z/OS.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: DDL or GEN of a view may fail with      *
  *                      error in COMMON_TABLE_EXPRESSION,       *
  *                      due to STRINGSIZE condition. If DDL     *
  *                      or GEN does succeed, executing the DDL  *
  *                      may result in an SQLCODE=-206 /         *
  *                      SQLCODEN206 error.                      *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR addresses two issues:
  
  1. When using the DDL or GEN line command against a VIEW
  containing a COMMON TABLE EXPRESSION, DB2 Admin Tool
  may fail with the following error:
  
  Error in COMMON_TABLE_EXPRESSION:New value for sql_stmt_prep is
  too large in procedure
  
  The error is caused by a STRINGSIZE exception.
  
  2. If the DDL or GEN is successful, executing the DDL
  may result in an SQLCODE=-206 / SQLCODEN206 error:
  
  DSNT408I SQLCODE = -206, ERROR: schema.viewname.colname
  IS NOT VALID IN THE CONTEXT WHERE IT IS USED
  

Problem conclusion

• Problems have been resolved.
  

Temporary fix

Comments

APAR Information

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

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

  UI62825

Modules/Macros

•    ADB2PA
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RC10 PSY UI62825

     UP19/05/04 P F905

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.