IBM Support

IT39319: SQL0818N CAN OCCUR AFTER A ROLLED BACK REVALIDATION

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • ERROR SQL0818 appear after rolled back revalidation of an SQL
    routine or trigger
    
    Consider the following example.
    
    1) Update trigger TRIG1 becomes invalid due to transfer owner
    
    select trigname, valid from syscat.triggers where
    trigname='TRIG1'
    
    TRIGNAME     VALID
    
    ----------     ----
    
    TRIG1           Y
    
    
    select pkgname, valid from syscat.packages where pkgname in
    (select bname from syscat.trigdep where trigname='TRIG1')
    
    PKGNAME       VALID
    
    ---------       ----
    
    P441013123     Y
    
    
    
    
    
    TRANSFER OWNERSHIP OF TABLE DUMMYSCH.TEST to user TESTUSR
    PRESERVE PRIVILEGES
    
    
    select trigname, valid from syscat.triggers where
    trigname='TRIG1'
    
    TRIGNAME     VALID
    
    ----------     ----
    
    TRIG1           N
    
    
    select pkgname, valid from syscat.packages where pkgname in
    (select bname from syscat.trigdep where trigname='TRIG1')
    
    PKGNAME       VALID
    
    ---------       ----
    
    P441013123     Y
    
    
    2) An update statement is issued that triggers the trigger.
    This should cause revalidation, but the statement itself fails
    due to some expected error.  E.g. SQL0438N.
    The revalidation is rolled back.
    
    
    db2 "update DUMMYSCH.TEST set f1=0"
    
    SQL0438N Application raised error or warning with diagnostic
    text: "Trying to update: forbidden". SQLSTATE=75002
    
    
    
    select trigname, valid from syscat.triggers where
    trigname='TRIG1'
    
    TRIGNAME     VALID
    
    ----------     ----
    
    TRIG1           N
    
    
    select pkgname, valid from syscat.packages where pkgname in
    (select bname from syscat.trigdep where trigname='TRIG1')
    
    PKGNAME       VALID
    
    ---------       ----
    
    P441013123     Y
    
    
    3) The trigger is revalidated again on a new statement, but the
    update
    statement returns a -818 sqlcode error
    
    db2 "update DUMMYSCH.TEST1 set f1=0"
    
    SQL0723N An error occurred in a triggered SQL statement in
    trigger
    
    "DUMMYSCH.TEST". Information returned for the error includes
    SQLCODE "-818",
    SQLSTATE "51003" and message tokens "". SQLSTATE=09000
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to  Db2 v11.1 mod 4 Fixpack 7                        *
    ****************************************************************
    

Problem conclusion

  • Fixed in Db2 v11.1 mod 4 Fixpack 7
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT39319

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2021-12-06

  • Closed date

    2022-04-17

  • Last modified date

    2022-04-17

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

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

    IT39321

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

  • RB50 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2022