472,975 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,975 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 2919
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"....
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.