Problem:
One of the production MQ job started getting failure messages due one of the Trigger package in the system Trigger delete package failed with SQLCODE -909 DSNT408I SQLCODE = -723, ERROR: AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN TRIGGER XXXXX.TRIGPKG. INFORMATION RETURNED: SQLCODE -909, SQLSTATE 57007, MESSAGE TOKENS , SECTION NUMBER 2 Even though the -909 says it could be below conditions -909 THE OBJECT HAS BEEN DELETED OR ALTERED
Explanation:
The application program has performed one of the following actions: o Dropped or altered a table and then accessed it. o Dropped or altered an index and then accessed the table on which the index was defined. o Executed the EXCHANGE statement on a table more than once without executing a COMMIT statement between consecutive EXCHANGE statements.
Reason :
The -909 was misleading as there was no Drop or alter on table or index happened in the system. We not have any EXCHANGE statement either in our system. Analysed what changes happened to the system, found PTF UK91616 was applied on the system as part DB2 system Maintenance, is the cause for the -909 failure in the system.
Back Ground
There was bug identify when a V10 Native Stored procedure on V9 System, it hung /looped with high CPU usage, it was fixed as part UK80992 . DSNXEBR was changed to set the internal variable correctly which will prevent DB2 from looping. This problem happens becausePM66040 fixed an internal infinite loop problem and the fix bring more opportunity to open another code path for an Incorrect procedure handling .This started giving ABEND0C4 on DSNXEBR when running a package bound before DB2 V8 with special register setting ((CURRENT PACKAGESET,CURRENT PATH,CURRENT SQLID,CURRENT USER or CURRENT OPTIMIZATION HINT), which was fixed using UK91616. The PTF UK91616 was applied on the system having packages with older than version 8 will impact.
Resolution:
Found that related trigger package was last bound during 2002, which prior to DB2 V8. So we need REBIND the packages and which resolve the -909 failure in the system.
Lesson Learnt:
Even though it is software bug, complete the REBIND after each version upgrade to avoid any failure of this type issues in the system.
One of the production MQ job started getting failure messages due one of the Trigger package in the system Trigger delete package failed with SQLCODE -909 DSNT408I SQLCODE = -723, ERROR: AN ERROR OCCURRED IN A TRIGGERED SQL STATEMENT IN TRIGGER XXXXX.TRIGPKG. INFORMATION RETURNED: SQLCODE -909, SQLSTATE 57007, MESSAGE TOKENS , SECTION NUMBER 2 Even though the -909 says it could be below conditions -909 THE OBJECT HAS BEEN DELETED OR ALTERED
Explanation:
The application program has performed one of the following actions: o Dropped or altered a table and then accessed it. o Dropped or altered an index and then accessed the table on which the index was defined. o Executed the EXCHANGE statement on a table more than once without executing a COMMIT statement between consecutive EXCHANGE statements.
Reason :
The -909 was misleading as there was no Drop or alter on table or index happened in the system. We not have any EXCHANGE statement either in our system. Analysed what changes happened to the system, found PTF UK91616 was applied on the system as part DB2 system Maintenance, is the cause for the -909 failure in the system.
Back Ground
There was bug identify when a V10 Native Stored procedure on V9 System, it hung /looped with high CPU usage, it was fixed as part UK80992 . DSNXEBR was changed to set the internal variable correctly which will prevent DB2 from looping. This problem happens becausePM66040 fixed an internal infinite loop problem and the fix bring more opportunity to open another code path for an Incorrect procedure handling .This started giving ABEND0C4 on DSNXEBR when running a package bound before DB2 V8 with special register setting ((CURRENT PACKAGESET,CURRENT PATH,CURRENT SQLID,CURRENT USER or CURRENT OPTIMIZATION HINT), which was fixed using UK91616. The PTF UK91616 was applied on the system having packages with older than version 8 will impact.
Resolution:
Found that related trigger package was last bound during 2002, which prior to DB2 V8. So we need REBIND the packages and which resolve the -909 failure in the system.
Lesson Learnt:
Even though it is software bug, complete the REBIND after each version upgrade to avoid any failure of this type issues in the system.
No comments:
Post a Comment