PK79735: GEN OF TRIGGER RESULTS IN SQLCODEN204 / SQLSTATE42704

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS GEN command of a TRIGGER
  will contain an "AFTER INSERT ON" syntax with an unqualified
  name resulting in a SQLCODEN204 -204 if executed.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 Administration Tool who use the *
  *                 GEN command, and specify a "Run SQLID", to   *
  *                 generate DDL for a trigger.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION: Column references in an INSERT          *
  *                      statement within the trigger            *
  *                      statement were qualified when           *
  *                      generated by the GEN command.           *
  *                                                              *
  *                      The DDL is valid, but the columns       *
  *                      should remain unqualified when          *
  *                      qualification is not necessary.         *
  *                                                              *
  *                      The following was generated:            *
  *                      ----------------------------            *
  *                      CREATE TRIGGER USER123.TRIG1            *
  *                        AFTER INSERT ON USER345.TABLE1        *
  *                        REFERENCING NEW_TABLE AS TRIG1_NEW    *
  *                        FOR EACH STATEMENT MODE DB2SQL        *
  *                          BEGIN ATOMIC                        *
  *                          INSERT INTO USER789.VIEW1           *
  *                            (USER789.VIEW1.USERID,            *
  *                             USER789.VIEW1.GROUP_ID,          *
  *                             USER789.VIEW1.ID_AUTH,           *
  *                             USER789.VIEW1.UPDATE_TS,         *
  *                             USER789.VIEW1.MENTOR,            *
  *                             USER789.VIEW1.FUNCTION_KZ)       *
  *                            SELECT TRIG1_NEW.USERID,          *
  *                                   TRIG1_NEW.GROUP_ID,        *
  *                                   TRIG1_NEW.ID_AUTH,         *
  *                                   TRIG1_NEW.UPDATE_TS,       *
  *                                   TRIG1_NEW.MENTOR, 'I'      *
  *                              FROM TRIG1_NEW ;                *
  *                          END ?                               *
  *                                                              *
  *                      when the following is the desired DDL:  *
  *                      --------------------------------------  *
  *                      CREATE TRIGGER USER123.TRIG1            *
  *                        AFTER INSERT ON USER345.TABLE1        *
  *                        REFERENCING NEW_TABLE AS TRIG1_NEW    *
  *                        FOR EACH STATEMENT MODE DB2SQL        *
  *                          BEGIN ATOMIC                        *
  *                          INSERT INTO USER789.VIEW1           *
  *                            (USERID, GROUP_ID, ID_AUTH,       *
  *                             UPDATE_TS, MENTOR, FUNCTION_KZ)  *
  *                            SELECT USERID, GROUP_ID,          *
  *                                   ID_AUTH, UPDATE_TS,        *
  *                                   MENTOR, 'I'                *
  *                              FROM TRIG1_NEW ;                *
  *                          END ?                               *
  ****************************************************************
  * RECOMMENDATION: Apply The PTF.                               *
  ****************************************************************
  The GEN program was unnecessarily qualifying column references
  in the INSERT statement within a trigger statement.
  

Problem conclusion

• The problem has been fixed.
  

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:

Modules/Macros

•    ADB1000  ADB2RE   ADB2REE  ADB2REG  ADB2REM
  ADB2REZ
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• R720 PSY UK50262

     UP09/09/26 P F909

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.