How to avoid having to rebind a package after each restore

[Joachim Tuchel]

Hi there,

we regularly use backups from production db's to test recovery and rollforward. A few weeks ago we did some schema changes and added a few indexes and such. Since then, we have to rebind the same package every time we restore/rollforward on our testing environment before we can run our test scripts to validate the backup.

What do we have to do to avoid this? How can we determine which packages need to be rebound?

The risky thing about this is that in case of emergency, when we need to restore the DB in production, there is no time for games like stumbling from one rebind to the next... I am sure writing bash scripts to accompany our restore/roolforward scripts to rebind all the packages that needed rebinding in our testing is not working on the illness, just on the symptoms, right?

Thank you for any pointers

Joachim

[Ian]

Post by Joachim Tuchel
Hi there,
we regularly use backups from production db's to test recovery and rollforward. A few weeks ago we did some schema changes and added a few indexes and such. Since then, we have to rebind the same package every time we restore/rollforward on our testing environment before we can run our test scripts to validate the backup.
What do we have to do to avoid this? How can we determine which packages need to be rebound?
The risky thing about this is that in case of emergency, when we need to restore the DB in production, there is no time for games like stumbling from one rebind to the next... I am sure writing bash scripts to accompany our restore/roolforward scripts to rebind all the packages that needed rebinding in our testing is not working on the illness, just on the symptoms, right?

This is not a normal part of restore and rollfoward.

You should not have to rebind packages after performing a restore unless those packages were already invalid when you took the backup that you're restoring, or they were invalidated (by DDL changes that occurred between the backup and the point-in-time that you recovered to).

You can see invalid objects by querying SYSCAT.INVALIDOBJECTS. (Assuming you're using Db2 on Linux, UNIX or Windows).

[Ian]

Post by Joachim Tuchel
we regularly use backups from production db's to test recovery and rollforward. A few weeks ago we did some schema changes and added a few indexes and such. Since then, we have to rebind the same package every time we restore/rollforward on our testing environment before we can run our test scripts to validate the backup.
What do we have to do to avoid this? How can we determine which packages need to be rebound?

This is not a normal part of restore and rollfoward operation.

What packages are you having to rebind? Are they your own application's packages, or are they system packages (i.e. in NULLID schema)?

If it's your own application's packages, I can only imagine this being a necessity if the packages were already invalid when you took the backup that you're restoring, or if they were invalidated (by DDL changes that occurred between the backup and the point-in-time that you recovered to, but before rebinds occurred). The solution to this problem is to ensure that finding and fixing invalidated packages is part of any deployment.

Post by Joachim Tuchel
The risky thing about this is that in case of emergency, when we need to restore the DB in production, there is no time for games like stumbling from one rebind to the next... I am sure writing bash scripts to accompany our restore/roolforward scripts to rebind all the packages that needed rebinding in our testing is not working on the illness, just on the symptoms, right?

You can see invalid objects by querying SYSCAT.INVALIDOBJECTS. (Assuming you're using Db2 on Linux, UNIX or Windows).

Also (again, if you're on Linux, UNIX or Windows) you may want to look at the ADMIN_REVALIDATE_DB_OBJECTS procedure.

[Joachim Tuchel]

Post by Ian

Post by Joachim Tuchel
What do we have to do to avoid this? How can we determine which packages need to be rebound?

This is not a normal part of restore and rollfoward operation.

That's what I thought as well. Never had this before.

Post by Ian
What packages are you having to rebind? Are they your own application's packages, or are they system packages (i.e. in NULLID schema)?

It's a package in our application's schema, so our own.

Post by Ian
If it's your own application's packages, I can only imagine this being a necessity if the packages were already invalid when you took the backup that you're restoring, or if they were invalidated (by DDL changes that occurred between the backup and the point-in-time that you recovered to, but before rebinds occurred). The solution to this problem is to ensure that finding and fixing invalidated packages is part of any deployment.

I guess the procedure you mention below is key to that...

Post by Ian

Post by Joachim Tuchel
The risky thing about this is that in case of emergency, when we need to restore the DB in production, there is no time for games like stumbling from one rebind to the next... I am sure writing bash scripts to accompany our restore/roolforward scripts to rebind all the packages that needed rebinding in our testing is not working on the illness, just on the symptoms, right?

You can see invalid objects by querying SYSCAT.INVALIDOBJECTS. (Assuming you're using Db2 on Linux, UNIX or Windows).

select * from syscat.invalidobjects yields 41 results, all of them using the schema SYSIBMADM.

Post by Ian
Also (again, if you're on Linux, UNIX or Windows) you may want to look at the ADMIN_REVALIDATE_DB_OBJECTS procedure.

Thanks for that pointer. I called it using zero parameters and the table invalidobjects is now empty. I will see if the next backups will work better when they are restored...


Again: thanks fpr your help.

Joachim

[Joachim Tuchel]

Post by Joachim Tuchel
Thanks for that pointer. I called it using zero parameters and the table invalidobjects is now empty. I will see if the next backups will work better when they are restored...

Unfortunately, the problem is not solved. Calling a stpred procedure in e freshly restored backup of the database using

db2 call myproc(some_parameter)

yields

SQL20249N The statement was not processed because the package named "MYSCHEMA.P865..." needs to be explicitly rebound


So what else can I do?

Joachim

[Jeremy Rickard]

Hi Joachim,

Are the stored procedures external ones (i.e. not SQL procedures)? I'm wondering if behaviour after restore might then be different.

Is the instance fix pack level definitely identical in both environments? See https://www-01.ibm.com/support/docview.wss?uid=swg22002381


Jeremy Rickard

[Joachim Tuchel]

Jeremy,

yeah, that was it. The Procedures were pure SQL, no external ones.

So we'll add the rebinding stuff from your linked resource to our restore scripts, just to be sure...


Thanks for the pointer

Joachim

Post by Jeremy Rickard
Hi Joachim,
Are the stored procedures external ones (i.e. not SQL procedures)? I'm wondering if behaviour after restore might then be different.
Is the instance fix pack level definitely identical in both environments? See https://www-01.ibm.com/support/docview.wss?uid=swg22002381
Jeremy Rickard

[Jeremy Rickard]

Hi Joachim,

Glad if it helped. Although, if your test system is downlevel then a better option might be to bring it into line with production?

Jeremy