PM28928: ORDER OF DEPENDENT OBJECTS IN GEN OUTPUT RESULTS IN SQLCODE440

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS GEN function creates DDL
  in which the order of objects does not reflect the order that
  they need to be created in to prevent a SQLCODE440 being issued.
  

Local fix

• Manually reorder the DDL such that called functions/procedures
  are created before they are referenced by other procedures.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS who use the GEN line command to         *
  *                 generate DDL for DB2 stored procedures and   *
  *                 user defined functions.                      *
  ****************************************************************
  * PROBLEM DESCRIPTION: The order of the procedures generated   *
  *                      by GEN resulted in an SQLCODEN440       *
  *                      error or SQLCODE 440 warning message:   *
  *                                                              *
  *                      'NO routine_type BY THE NAME            *
  *                      routine_name HAVING COMPATIBLE          *
  *                      ARGUMENTS WAS FOUND IN THE CURRENT      *
  *                      PATH SQLSTATE 42884'                    *
  *                                                              *
  *                      depending on if the procedure was       *
  *                      defined using VALIDATE BIND or          *
  *                      VALIDATE RUN.                           *
  *                                                              *
  *                      The procedures and functions were       *
  *                      generated alphabetically, instead       *
  *                      of based on dependency on other         *
  *                      procedures and functions.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The order of the procedures generated by GEN resulted
  in an SQLCODEN440 error, or SQL warning message
  SQLCODE 440, depending on if the procedure was defined
  using VALIDATE BIND or VALIDATE RUN.
  
  e.g. if procedure P1 calls P2, P2 should be generated before
  P1.  GEN was generating P1 before P2 because it generated
  the procedures in alphabetical order.  If P1 was defined with
  VALIDATE BIND, an SQLCODE -440 error would occur if P2 did
  not exist.  If P1 was defined using VALIDATE RUN an
  SQLCODE 440 warning would occur if P2 did not exist.
  

Problem conclusion

• The procedures and functions (routines) are now generated
  based on dependency on other routines such that no SQL
  error will occur when the DDL is applied.  In some cases,
  GEN will have to initially define a routine using VALIDATE
  BIND in order to avoid an SQL error when the DDL is applied.
  In this case GEN will alter the routine back to VALIDATE BIND
  in the DDL after all routines are defined.  Message ADB1460I
  will be issued listing each routine that will initially be
  defined using VALIDATE BIND instead of VALIDATE RUN.
  

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:

  UK66440 UK66441

Modules/Macros

•    ADB1000  ADB2REG  ADB2REH  ADB2REM  ADB2REP
  ADB2REU  ADB2REY
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK66440

     UP11/04/08 P F104

• R720 PSY UK66441

     UP11/04/08 P F104

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.