PM45146: DB2 ERRORS LIKE SQLCODEN204 -204 DUE TO TRUNCATED SQL STATEMENT WHEN RUNNING SQL FROM FB LRECL(80) DATASET

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• DB2 Administration Tool for z/OS panel ADB222 option 1:
  1 - Run SQL statements from a data set
  truncates input dataset when LRECL(80) DSORG(PO) RECFM(FB) data
  exceeds 72 bytes.
  .
  Other DB2 errors could occur, depending on where the truncation
  takes place on the SQL statement in the input dataset.
  .
  For example: running under SQLID MHM, if the following SQL:
  .
  SELECT QUERYNO, QBLOCKNO, APPLNAME, PROGNAME, PLANNO, METHOD
  FROM  PLAN_TABLE ;
  .
  is entered on a single line of the input dataset, it will fail
  with sqlcode204 -204 in panel ADB2E1, as shown, below:
  .
  ADB2E1 in --------------------- DB2 Error Display 1
  
  
  DSNT408I SQLCODE = -204, ERROR:  MHM.PLAN_T IS AN UNDEFINED NAME
      SQLCODE : -204                           DSNTIAR CODE :  0
  PREPARE
  SELECT QUERYNO, QBLOCKNO, APPLNAME, PROGNAME, PLANNO, METHOD
  FROM PLAN_T
  .
  As shown in error message, DB2 object name was truncated.
  

Local fix

• In this case, use 2 lines for input statement, and avoid
  columns 73 - 80 for SQL statement input.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS who run SQL statements from             *
  *                 an input data set via panel ADB222           *
  *                 'Run or Explain SQL statements'.             *
  ****************************************************************
  * PROBLEM DESCRIPTION: When using the DB2 Administration Tool  *
  *                      for z/OS panel ADB222 to run SQL        *
  *                      statements from an input data set which *
  *                      is defined with DCB attributes LRECL(79)*
  *                      or LRECL(80), RECFM(FB) or RECFM(F) and *
  *                      which has SQL statements extend into    *
  *                      the last 8 columns of any record, may   *
  *                      fail with a SQLCODE=-104 / SQLCODEN104: *
  *                                                              *
  *                      SQLCODE -104: ILLEGAL SYMBOL            *
  *                      "token". SOME SYMBOLS THAT MIGHT        *
  *                      BE LEGAL ARE: token-list.               *
  *                                                              *
  *                      or a SQLCODE=-204 / SQLCODEN204:        *
  *                                                              *
  *                      SQLCODE -204: name IS AN UNDEFINED NAME.*
  *                                                              *
  *                      or a SQLCODE=-206 / SQLCODEN206:        *
  *                        name IS NOT VALID IN THE              *
  *                        CONTEXT WHERE IT IS USED              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When using the DB2 Administration Tool for z/OS panel ADB222
  to run SQL statements from an input dataset definedwith DBC
  attributes LRECL(79) or LRECL(80), RECFM(FB) or RECFM(F)
  and which has SQL statements extend into the last 8 columns of
  any record may fail with an error.  As the DB2 Admin Tool
  regards the last 8 columns as reserved for sequence numbers,
  this results in truncation of the SQL statement, and finally
  leads to a SQLCODE, may be SQLCODE=-104 / SQLCODEN104:
  SQLCODE -104: ILLEGAL SYMBOL "token". SOME SYMBOLS
  THAT MIGHT BE LEGAL ARE: token-list.
  
  or a SQLCODE=-204 / SQLCODEN204:
  SQLCODE -204: name IS AN UNDEFINED NAME.
  
  or a SQLCODE=-206 / SQLCODEN206:
    name IS NOT VALID IN THE
    CONTEXT WHERE IT IS USED
  

Problem conclusion

• Support has been added for input data sets with DCB
  attributes defined as LRECL(79), RECFM(FB) or RECFM(F)
  and the following restrictions added to help panel ADBH22
  and the DB2 Administration Tool for z/OS User's Guide
  and Reference Version 10 Release 1 and Version 7.2.
  
  Restrictions:
    The following restrictions apply to the input data set you
    specify:
  
    1) If the record format (RECFM) is either F or FB and the
       logical record length (LRECL) is either 79 or 80, DB2 Admin
       assumes that the last 8 bytes of each record are for
       sequence numbers. Therefore, you should not use the
       last 8 columns of each record to store SQL statements.
    2) If the record format (RECFM) is either F or FB and the
       logical record length (LRECL) is not 79 or 80, DB2 Admin
       assumes that all of the columns of each record are for
       SQL statements.
    3) If the record format (RECFM) is either V or VB, DB2 Admin
       checks to see if the content in columns 1 through 8 of the
       first record is numeric. If it is, DB2 Admin assumes that
       the first 8 bytes of each record are for sequence numbers.
       If it is not, DB2 Admin assumes that all columns are for
       SQL statements.
  

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:

  UK74154 UK74155

Modules/Macros

•    ADBH22   ADB2A2WS ADB222E
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK74154

     UP11/11/29 P F111

• R720 PSY UK74155

     UP11/11/29 P F111

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.