PI78014: DSNTEP2 SQLFORMAT(SQLPL) SQLCODE950 OR SQLCODE084 WITH CONNECT STATEMENT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Using SQLFORMAT(SQLPL), DSNTEP2 injects a LF at the end of each
  input record.  For CONNECT TO, that trailing LF is treated as
  part of the location name, which fails with SQLCODE -950.  For a
  simple CONNECT, DSNTEP2 passes the request for PREPARE, which
  fails with SQLCODE -84.
  
  Additional Keywords:
  SQLCODE950
  SQLCODE084
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS users of DSNTEP2 and                     *
  * DSNTEP4.                                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When DSNTEP2 or DSNTEP4 is run with                          *
  * SQLFORMAT(SQLPL):                                            *
  * - Simple CONNECT statements fail with                        *
  *   SQLCODE -084 (UNACCEPTABLE SQL                             *
  *   STATEMENT)                                                 *
  * - CONNECT TO statements fail with                            *
  *   SQLCODE -950 (THE LOCATION NAME                            *
  *   SPECIFIED IN THE CONNECT STATEMENT                         *
  *   IS INVALID OR NOT LISTED IN THE                            *
  *   COMMUNICATIONS DATABASE) even when                         *
  *   a valid, defined location name is                          *
  *   specified.                                                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  DSNTEP2 and DSNTEP4 are DB2 supplied PL/I sample application
  programs that can be used to perform dynamic SQL statement
  processing in the batch mode.  Results are written to the
  SYSPRINT DD.
  
  Before passing an SQL statement to DB2, DSNTEP2 and DSNTEP4
  perform shallow parsing to identify the end of the statement.
  By default, the shallow parser collapses each line of an SQL
  statement into a single line and also discards all SQL comments
  before passing the statement to DB2.  However, when the
  SQLFORMAT(SQLPL) option is in effect, the shallow parser
  retains embedded SQL comments and terminates each line of an
  SQL statement with a line feed character (hex 25) before
  passing the statement to DB2.
  
  Currently, when SQLFORMAT(SQLPL) is in effect, simple connect
  statements fail with SQLCODE -084 and CONNECT TO statements
  fail with SQLCODE -950.  In both cases, the error occurs
  because the CONNECT statement ends with a hanging line-feed
  character that has been appended by DSNTEP2 or DSNTEP4.
  

Problem conclusion

• In response, the shallow parsing logic of DSNTEP2 and DSNTEP4
  is modified to avoid injecting the superfluous line feed
  character.  Then the CONNECT statements can be processed
  successfully.
  
  This problem was resolved in DB2 10 and DB2 11 by APAR PI75287.
  

Temporary fix

Comments

APAR Information

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

  PI75287

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

  UI48667

Modules/Macros

• DSN@EP2L DSNTEP4L DSNTEP2L DSNTEP4  DSNTEP2  DSN@EP4L
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI48667

     UP17/07/26 P F707

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.