PM12089: DSNT408I SQLCODE440 WHEN IMPORTING REMOTE ALIAS USING DSNACLRT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• When using MetaData Stored Procedure to import remote aliases,
  the following error results:
  DSNT408I SQLCODE = -443, ERROR: ROUTINE SQLCOLUMNS (SPECIFIC
  NAME SQLCOLUMNS) HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC
  TEXT 0 DSNT408I SQLCODE = -440, ERROR: NO PROCEDURE BY THE NAME
  ZDB2 HAVING COMPATIBLE ARGUMENTS WAS FOUND IN THE CURRENT PATH
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of the DB2 Universal JDBC Driver   *
  *                 or the CLI Driver provided with DB2 for      *
  *                 Linux Unix and Windows Version 8 and higher  *
  *                 that connects to DB2 UDB for z/OS Version 8  *
  *                 and DB2 Version 9.1 for z/OS.                *
  ****************************************************************
  * PROBLEM DESCRIPTION: When the JDBC Driver or CLI Driver      *
  *                      calls one of the following stored       *
  *                      procedures:                             *
  *                                                              *
  *                        SYSIBM.SQLCOLUMNS                     *
  *                        SYSIBM.SQLCOLPRIVILEGES               *
  *                        SYSIBM.SQLFOREIGNKEYS                 *
  *                        SYSIBM.SQLPRIMARYKEYS                 *
  *                        SYSIBM.SQLSPECIALCOLUMNS              *
  *                        SYSIBM.SQLSTATISTICS                  *
  *                        SYSIBM.SQLTABLEPRIVILEGES             *
  *                                                              *
  *                      one of the following errors can result  *
  *                      when importing remote aliases:          *
  *                                                              *
  *                        DSNT408I SQLCODE = -443, ERROR:       *
  *                         ROUTINE routine-name (SPECIFIC NAME  *
  *                         specific-name) HAS RETURNED AN       *
  *                         ERROR SQLSTATE WITH DIAGNOSTIC TEXT  *
  *                         msg-text                             *
  *                                                              *
  *                        DSNT408I SQLCODE = -440, ERROR:       *
  *                         NO PROCEDURE BY THE NAME name        *
  *                         HAVING COMPATIBLE ARGUMENTS WAS      *
  *                         FOUND IN THE CURRENT PATH            *
  ****************************************************************
  * RECOMMENDATION: Apply PTF.                                   *
  ****************************************************************
  The DB2-supplied Database Metadata stored procedures are invoked
  by the DB2 Universal JDBC Driver and the CLI Driver provided
  with DB2 Connect Version 8 and higher.
  
  The following stored procedures use the location name
  when retrieving information of an alias for a remote object.
  
    SYSIBM.SQLCOLUMNS (DSNACOL8)
    SYSIBM.SQLCOLPRIVILEGES (DSNACPR8)
    SYSIBM.SQLFOREIGNKEYS (DSNAFNK8)
    SYSIBM.SQLPRIMARYKEYS (DSNAPRK8)
    SYSIBM.SQLSPECIALCOLUMNS (DSNASPC8)
    SYSIBM.SQLSTATISTICS (DSNASTA8)
    SYSIBM.SQLTABLEPRIVILEGES (DSNATBP8)
  
  They do not consider the case when the location name of the
  remote alias has embedded spaces. As a result, calling these
  stored procedures can result in one of the following errors
  when an alias for a remote object is specified in the call
  and the location name (SYSIBM.LOCATIONS) has embedded spaces:
  
    DSNT408I SQLCODE = -443, ERROR: ROUTINE routine-name
             (SPECIFIC NAME specific-name) HAS RETURNED AN
             ERROR SQLSTATE WITH DIAGNOSTIC TEXT msg-text
  
    DSNT408I SQLCODE = -440, ERROR: NO PROCEDURE BY THE
             NAME name HAVING COMPATIBLE ARGUMENTS WAS
             FOUND IN THE CURRENT PATH
  

Problem conclusion

• The following stored procedures were updated to remove embedded
  spaces in the location name when they are used to access a
  remote alias:
  
    SYSIBM.SQLCOLUMNS (DSNACOL8)
    SYSIBM.SQLCOLPRIVILEGES (DSNACPR8)
    SYSIBM.SQLFOREIGNKEYS (DSNAFNK8)
    SYSIBM.SQLPRIMARYKEYS (DSNAPRK8)
    SYSIBM.SQLSPECIALCOLUMNS (DSNASPC8)
    SYSIBM.SQLSTATISTICS (DSNASTA8)
    SYSIBM.SQLTABLEPRIVILEGES (DSNATBP8)
  

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:

  UK57664 UK57665

Modules/Macros

• DSN@COL8 DSN@CPR8 DSN@FNK8 DSN@PRK8 DSN@SPC8
  DSN@STA8 DSN@TBP8 DSNACOL8 DSNACPR8 DSNAFNK8 DSNAPRK8 DSNASIDK
  DSNASPC8 DSNASTA8 DSNATBP8
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R810 PSY UK57664

     UP10/06/22 P F006

• R910 PSY UK57665

     UP10/06/22 P F006

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.