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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 28, 2018 at 2:43:20 AM UTC-7, Joachim Tuchel wrote:

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).

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, December 28, 2018 at 2:43:20 AM UTC-7, Joachim Tuchel wrote:

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.

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.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am Samstag, 29. Dezember 2018 00:36:16 UTC+1 schrieb Ian:

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.

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.

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...

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.

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am Samstag, 29. Dezember 2018 14:01:05 UTC+1 schrieb 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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


Am Montag, 31. Dezember 2018 21:19:49 UTC+1 schrieb 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)