Redirected Restore of DB With Stored Procedures

[Philip Nelson]

Ran into a problem doing a redirected restore of a database with SQL
stored procedures.

On our production server database DPXXX001 has a number of SQL stored
procedures defined. After installing a new release into production I
want to create another copy of this onto our test box using name DTXXX001.

I do a redirected restore, which completes successfully. However when I
try to connect to the restored database I get -

SQL2048N An error occurred while accessing object "SQL PROCEDURE FILES"
Reason code "7".

I tried putting back the original database (from a backup which I had just
taken) but was getting similar problems.

Eventually I had to db2look / db2move the database from production into an
empty database on our test server, and then manually recreate the stored
procedures.

We are DB2 V7.2 Fixpack 7 on AIX 4.3.3.

Phil

[Sean McKeough]

Sounds like this is a known problem. The easiest workaround should be to
shut down/restart the instance before trying to connect. It has to do
with file locking contentions/cached libraries etc.

Philip Nelson wrote:

Ran into a problem doing a redirected restore of a database with SQL
stored procedures.

On our production server database DPXXX001 has a number of SQL stored
procedures defined. After installing a new release into production I
want to create another copy of this onto our test box using name DTXXX001.

I do a redirected restore, which completes successfully. However when I
try to connect to the restored database I get -

SQL2048N An error occurred while accessing object "SQL PROCEDURE FILES"
Reason code "7".

I tried putting back the original database (from a backup which I had just
taken) but was getting similar problems.

Eventually I had to db2look / db2move the database from production into an
empty database on our test server, and then manually recreate the stored
procedures.

We are DB2 V7.2 Fixpack 7 on AIX 4.3.3.

Phil

[Giovanni]

Philip Nelson <gu*****@yahoo.co.uk> wrote in
news:pa****************************@yahoo.co.uk:

Ran into a problem doing a redirected restore of a database with SQL
stored procedures.

On our production server database DPXXX001 has a number of SQL stored
procedures defined. After installing a new release into production I
want to create another copy of this onto our test box using name
DTXXX001.

I do a redirected restore, which completes successfully. However when
I try to connect to the restored database I get -

SQL2048N An error occurred while accessing object "SQL PROCEDURE
FILES" Reason code "7".

Try to delete directory

<instpath>/sqllib/function/routine/sqlproc/<dbname>

ex:

/home/db2inst7/sqllib/function/routine/sqlproc/mydb

and then db2stop and db2start