PK93283: VALIDATE WORK STATEMENT LIST / WSL SQLCODEN104 WHEN USER INSERTS MORE THAN DDL THAN EXPECTED INTO A SINGLE WSL STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS Validate Work Statement
  List (WSL) fails with a SQLCODE -104 when the user inserts more
  SQL than expected in a single "Insert statement" command.
  .
  For example, the user used the "Insert statement" to add the
  following SQL:
  .
  --------------------------------------------------------------
  --    Table=aaaaaaa.aaaaaaaaaaaaaaaaaa In aaaaaaaa.aaaaaaaa
  --------------------------------------------------------------
  --
    CREATE TABLE aaaaaaa.aaaaaaaaaaaaaaaaaa
       (EXCP_APPL_DATABASE   VARCHAR(24) FOR SBCS DATA NOT NULL,
        EXCP_TBSP_INDXSP     VARCHAR(24) FOR SBCS DATA NOT NULL,
        EXCP_PARTITION       SMALLINT NOT NULL,
        EXCP_MNTR_ALERT      CHAR(10) FOR SBCS DATA NOT NULL,
        EXCP_EXCEPT_TYPE     CHAR(10) FOR SBCS DATA NOT NULL,
        EXCP_USERID          CHAR(8) FOR SBCS DATA NOT NULL,
        EXCP_TIME_ENTERED    TIMESTAMP NOT NULL WITH DEFAULT)
      IN aaaaaaaa.aaaaaaaa
      AUDIT NONE
      DATA CAPTURE NONE
      CCSID      EBCDIC
      NOT VOLATILE;
  --
    COMMIT;
  --
  --------------------------------------------------------------
  -- ADB2GEN - End of generated DDL
  --------------------------------------------------------------
  --;
  ( please note the last line is a comment with a sql terminator )
  They then used the Validate Work Statement List function and
  the job failed with a message:
  SQL error in PREPARE for statement:
  ------------------------------------------------------
  
   DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ";".
            LEGAL ARE: <END-OF-STATEMENT>
   DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
   DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTIN
   DSNT416I SQLERRD    = 502  0  0  -1  759  0 SQL DIAGN
   DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'000
  rather than warning the customer that the statement contained
  several lines.
  .
  If the user executes the WSL it end without error RC00, ignoring
  the comment:
  ADB5044I *** INPUT STATEMENT:
     COMMIT;
  ADB5062I *** User requested commit performed, number:
  ADB5178I RESULT OF SQL STATEMENT:
   DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION
  
  ADB5180I SUCCESSFUL
  
  PAGE    3
   --
   --
   ----------------------------------------------------
   -- ADB2GEN - End of generated DDL
   ----------------------------------------------------
   --;
  ******************************** BOTTOM OF DATA *****
  

Local fix

• Do not enter more than one statement per "Insert statement"
  function
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Administration Tool for         *
  *                 z/OS who insert a definition SQL             *
  *                 statement into a Work Statement List.        *
  ****************************************************************
  * PROBLEM DESCRIPTION: After inserting a definition SQL        *
  *                      statement that contains                 *
  *                      comments into a Work Statement          *
  *                      List and performing a Validate          *
  *                      operation on the Work Statement         *
  *                      List, a SQLCODE=-104 error is           *
  *                      observed.                               *
  ****************************************************************
  * RECOMMENDATION: Apply The PTF.                               *
  ****************************************************************
  Inserting a definition SQL statement that contains
  comments into a Work Statement List causes a
  SQLCODE=-104 error.
  

Problem conclusion

• REXX EXEC ADB2W1S was modified so the inserting
  of a definition SQL statement can separate COM, DML,
  and DCL types from the DDL statements. The validate
  Work Statement List job will now execute successfully.
  

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:

  UK60735

Modules/Macros

•    ADB2W1S
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• R720 PSY UK60735

     UP10/09/30 P F009

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.