PM59280: INCORRECT SQLCODE -104 DSNHLEX WITH REOPT ALWAYS AND ALIAS TO TABLE OR VIEW. SQLCODE104

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Dynamic statement in program (eg. DSNTEP2 or DSNTEP4, or
  customer app) bound with reopt(always)
  when is reffered alias to table or view on remote DB2,
  we are getting incorrect SQLCODE104:
  
  SELECT *  FROM STLEC1B.SYSIBM.SYSDUMMY1;
  
                 +---------+
                 | IBMREQD |
                 +---------+
               1_| Y       |
                 +---------+
  
  CREATE ALIAS MYDUMMY1 FOR STLEC1B.SYSIBM.SYSDUMMY1;
  
  SELECT *  FROM MYDUMMY1;
  
  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ".". SOME
  SYMBOLS THAT MIGHT BE LEGAL ARE: <ERR_STMT> <WNG_STMT> GET SQL
               SAVEPOINT HOLD FREE ASSOCIATE CALL
      DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE
      DSNT415I SQLERRP    = DSNHLEX SQL PROCEDURE DETECTING ERROR
      DSNT416I SQLERRD    = 17  0  0  -1  1  502 SQL DIAGNOSTIC
  INFORMATION
      DSNT416I SQLERRD    = X'00000011'  X'00000000'  X'00000000'
  X'FFFFFFFF'  X'00000001'  X'000001F6' SQL DIAGNOSTIC
               INFORMATION
  

Local fix

• REOPT(NONE)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 and 10 users with "alias           *
  *                 resolution enabled" for applications bound   *
  *                 with REOPT bind option and that issue        *
  *                 distributed dynamic SQL statements that      *
  *                 specify an ALIAS to refer to remote objects  *
  *                 at a remote Application Server (AS), where   *
  *                 "alias resolution enabled" is defined as     *
  *                 follows:                                     *
  *                                                              *
  *                 For DB2 9,                                   *
  *                  DB2 subsystem parameter                     *
  *                  DSN6SPRM.DRDA_RESOLVE_ALIAS = YES           *
  *                                                              *
  *                 For DB2 10,                                  *
  *                  alias resolution enabled by default         *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -104 issued by an Application   *
  *                      Server (AS) for an inbound dynamic SQL  *
  *                      statement that originally used an alias *
  *                      to refer to a remote table at that      *
  *                      server. At the DB2 9 or 10 for z/OS     *
  *                      Application Requester (AR) where the    *
  *                      distributed dynamic SQL statement       *
  *                      originated,                             *
  *                       - the application is bound with option *
  *                         REOPT(ONCE|AUTO|VARS|ALWAYS), and    *
  *                       - "alias resolution enabled"           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  On a DB2 9 or 10 for z/OS Application Requester (AR) where
  alias resolution is enabled, an application bound with bind
  option REOPT( ONCE | AUTO | VAR | ALWAYS ) issued a distributed
  dynamic SQL statement that used an ALIAS to refer to a remote
  object at an Application Server (AS). At that target AS, the
  dynamic SQL statement failed with SQLCODE -104 during the
  prepare of the dynamic statement, and the SQLCODE104 was
  returned to the user application at the DB2 9 or 10 for z/OS AR.
  The target remote AS may or may not be a DB2 for z/OS AS. The
  dynamic statement should not have have failed with SQLCODE104.
                                                                 .
  Note that enabling alias resolution means that the DB2 AR should
  resolve any aliases specified in distributed SQL statements
  before the SQL statements are sent outbound to the target remote
  AS, so DB2 performs the alias resolution using values defined
  for that ALIAS at the DB2 AR, not at the target AS. So the DB2
  AR modifies the outbound SQL statement text by replacing the
  alias with the associated DB2 AR defined value, therefore the
  alias is no longer part of the outbound statement text sent to
  the target AS and the alias is not resolved there.
                                                                 .
  The SQLCODE -104 occurred at the target remote AS because
  the DB2 9 or 10 for z/OS AR passed an incorrect CCSID to
  the target AS for this modified outbound dynamic SQL statement,
  when the DB2 AR application issuing the dynamic SQL statement
  is bound REOPT(ONCE|AUTO|VARS|ALWAYS).
                                                                 .
  This SQLCODE -104 failure when using aliases does not occur when
  either the DB2 AR application is bound REOPT( NONE ), which is
  the DB2 default, or when alias resolution is disabled.
  

Problem conclusion

• DB2 9 and 10 for z/OS AR code has been changed to pass the
  correct statement CCSID to the target remote AS for the dynamic
  SQL scenario described above.
                                                                 .
  *************************************************************
  * The PTF for PM59280 *must* be applied to the DB2 for z/OS *
  * Application Requester subsystem in order to resolve       *
  * the SQLCODE104 that occurs at the target remote AS.       *
  * There is no PTF change to apply to the target remote AS   *
  * where the SQLCODE104 occurred.                            *
  *************************************************************
                                                                 .
  Additional Search Keywords:  SQLCODE104 SQLDRDA DB2DDF
  

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:

  UK77745 UK77747

Modules/Macros

• DSNXEDP  DSNXEDS1 DSNXEPM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK77745

     UP12/04/27 P F204

• R910 PSY UK77747

     UP12/04/27 P F204

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.