468,537 Members | 1,628 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,537 developers. It's quick & easy.

Redirected Restore of DB With Stored Procedures

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
Nov 12 '05 #1
2 2780
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

Nov 12 '05 #2
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by M A Srinivas | last post: by
2 posts views Thread by Philip Nelson | last post: by
9 posts views Thread by GL | last post: by
1 post views Thread by Jack | last post: by
7 posts views Thread by CCC via DBMonster.com | last post: by
11 posts views Thread by Chris | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.