ChannelDB2

Executing packages get SQL0525N error after nickname drop and recreate

Notice:
The content here is verified on DB2 v97fixpack 0.

Questions

There are a number of changes (such as adding new columns) in the datasource tables. As a result, we must drop and recreate the nicknames for any tables included in the changes. Hence all of our dependent packages are getting set to VALID=NO resulting in -525 errors at execution time.

SQL0525N The SQL statement cannot be executed because it was in error at bind time for section = section-number package = pkgschema.pkgname consistency token = 0x contoken.

Reading the following document:

It would seem to me that when the nickname is recreated following a drop, that the packages should be revalidated automatically without having to be rebound. If a package gets invalidated, a dynamic bind normally happens at next execution, and if that fails, something changed in the table dictates that the program must be changed. If the dynamic rebind is successful, then the change in the table did not affect the package (ie. new column in table and program doesn't reference it or care about it.).

Explanations

This is not only for drop/recreate nicknames but also for drop/recreate any database objects that packages depend on.

A -525 will occur at runtime for a package bound with SQLERROR CONTINUE if the section did not compile successfully. The failed compile could occur either as part of the original bind of the package - or as part of an implicit or explicit rebind. SQLERROR CONTINUE is meant to allow a bind to succeed even if there are compile failures - but will return an error at runtime if any of the specific sections are executed.

Here's an example. Consider a package P1 with a single static SQL statement - an insert to table X.T1. The table exists and the package is bound with SQLERROR CONTINUE. At the time of binding the single statement bound successfully, so at runtime the execution of this package's single statement succeeds.
Now, let's say the table X.T1 is dropped. This will invalidate the package P1. An attempt to execute P1 will drive an implicit rebind. The implicit rebind will attempt to compile all static sections of a package; in this case the single statement will fail to compile - however because of the SQLERROR CONTINUE setting for this package, the implicit rebind will not return the error, rather it will mark the single section as being unexecutable. After the implicit rebind completes, the section will be executed - but since it has been marked as unexecutable, the -525 sqlcode will be returned.

Let's say X.T1 is recreated. Now if the package is executed again, it's 'too late' - the previous implicit package rebind already is saved - and the package is no longer marked invalid. The package will be executed and the -525 error will result again - even though the table is there.

If the package is now explicitly rebound, it still doesn't 'fix' the -525 problem. Once the section is marked as 'not executable' it will get a -525 error from that point on as long as the package exists.

How to correct the error?

Option 1:
Bind the package manually again (which implicitly drops the existing package and creates a new one from scratch).

Option 2:
Don not use "sqlerror continue" at bind time, the first implicitly rebind will just report error.

Option 3:
Specify (in addition to SQLERROR CONTINUE) the VALIDATE RUN bind command. In this case when the implicit rebind occurs, the statements that fail to compile will not be identified as UNEXECUTABLE (resulting in the -525), but will be identified as what we call 'incremental bind' - which means that even though it could not recompile at rebind time, it will be run (dynamically) at runtime - so once the object has been recreated, the error will go away.

Option 4:
The error situation could be simplified as the following steps:

1. drop nickname
2. implicitly rebind to mark the section as being unexecutable.
3. recreate nickname
4. implicitly rebind without changing package
5. package execution with -525 error

So after drop nickname(step 1), if you recreate nickname fast enough to prevent an implicitly rebind at the middle(step 2), the problem can be workaround.



Views: 442

Tags: -525, DB2, Information, Integration, SQL, bind, package, static

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service