473,327 Members | 1,892 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 2836
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: M A Srinivas | last post by:
Hello all, I have the following requirement (SQL 2000, SP2) 1. Need to restore production database(A) to another database (B) on another server (No direct connection) 2. In the restored...
2
by: Philip Nelson | last post by:
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...
3
by: Vikrant | last post by:
Friends, Due an application (old) install program problem, Under AIX I could only create DB2 instance, I could also catalog it. Its complex application and I am not expert in creating full...
1
by: Vikrant | last post by:
Hi Kelly This is from old response by you. What is 'callerac' parameter in REDIRECTED restore, how can I provide 'callerac' value ? Thanks, VKG =====
9
by: GL | last post by:
I am running DB2 8.1.1 on AIX 5.1 Having a problem with a redirected restore. Once into the restore continue phase, I immediately get the following “SQL2059W A device full warning was...
1
by: Jack | last post by:
Is it possible to find out what the tablespace definitions are of database you are about to build using a redirected restore? What I am wanting to do is build a script that can take only a backup...
7
by: CCC via DBMonster.com | last post by:
We use redirected restore to refresh test from prod on different AIX 5.2 servers. The databases have the same number, size and names of cooked containers. The only difference is the fully qualified...
11
by: Chris | last post by:
I have searched this group for answers and tried the responses. I am trying to Use an full online backup from our production server and apply it to our test server. The Tablespaces in the...
1
by: Kent.Brooke | last post by:
When a backup is taken from server a and then redirected restore is done on server b, the tables and data is fine however db2advis says "explain & advise tables not created for user x properly"....
1
by: okonita | last post by:
Hi all, This is a very urgent issue for my team. We are trying a Redirected restore the most current online DB2 UDBv8.2 backup copy from prod disk to disk on another/test instance. Our...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.