SQL2048N after TSM Restore cant access sql procedure files

[Ray]

Hello,
I tried a test backup and restore on our production (soon to be live)
database this weekend and the restore failed. Using our TSM server and DB2
7.1 EEE on AIX 4.3.3, just one partition.

Upon attempting to connect to the database this error is returned: SQL2048N
An error occurred while accessing object "SQL PROCEDURE FILES".
Reason code: "7".

The db2diag.log ends up with:

2001-03-31-20.34.17.034918 Instance:db2inst1 Node:000
PID:19444(db2agent (ACSEEDB) 0) Appid:*LOCAL.db2inst1.010401043414
oper_system_services sqloopenp Probe:36 Database:ACSEEDB
errno: 0000 0015 ....

2001-03-31-20.34.17.178492 Instance:db2inst1 Node:000
PID:19444(db2agent (ACSEEDB) 0) Appid:*LOCAL.db2inst1.010401043414
oper_system_services sqloopenp Probe:36 Database:ACSEEDB
errno: 0000 001a ....

2001-03-31-20.34.17.326553 Instance:db2inst1 Node:000
PID:19444(db2agent (ACSEEDB) 0) Appid:*LOCAL.db2inst1.010401043414
PSM - SQL Procedure psm_remove_database_executab Probe:30 Database:ACSEEDB
OSS error deleting SQL procedure files: ffff f616 яяц.

2001-03-31-20.34.17.471422 Instance:db2inst1 Node:000
PID:19444(db2agent (ACSEEDB) 0) Appid:*LOCAL.db2inst1.010401043414
PSM - SQL Procedure psm_remove_database_executab Probe:31 Database:ACSEEDB
Error removing SQL procedure directory: 2f68 6f6d 652f 6462 3269 6e73 7431
2f73 /home/db2inst1/s
716c 6c69 622f 6675 6e63 7469 6f6e 2f72 qllib/function/r
6f75 7469 6e65 2f73 716c 7072 6f63 2f41 outine/sqlproc/M
4343 5456 4945 572f ONSEEDB/

2001-03-31-20.34.17.701945 Instance:db2inst1 Node:000
PID:19444(db2agent (ACSEEDB) 0) Appid:*LOCAL.db2inst1.010401043414
PSM - SQL Procedure psm_recover_all_procs Probe:2 Database:ACSEEDB
SQL2048N An error occurred while accessing object "SQL PROCEDURE FILES".
Reason code: "7".

Appreciate any help....thank you,
Ray

[Larry Menard]

Ray, you **might** be in luck. I saw the same error once, but my RESTORE
was successful... I was getting the error when I tried to CONNECT to the
database immediately after the RESTORE.

Here's what one of the SQL Procedures developers told me:

Larry - this error typically happens when you call an SQL procedure sometime
before doing your first connect.
On first connect, DB2 tries to remove a directory containing SQL Procedure
dlls.
The problem is that when you call a procedure, the OS will load a dll into
memory and this dll is loaded from
a directory that DB2 will try to remove on first connect after a restore. The
directory removal operation will
fail because the OS does not allow you to delete the directory when it contains
a library in use.

Three possible workarounds:

1) Obvious: if possible, do not call an SQL procedure before your first
connect.

2) Run with KEEPDARI=NO (this has a performance impact becasue DB2 will create
a new process every time
you call a procedure)

3) Do a db2stop; db2start before the first connect.

In my case, it turns out I must have still had a "db2dari" process running
in the background. After making sure I killed all DB2 processes and restarting
DB2, the problem went away.

Hope that helps.

Ray wrote:

--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of all things Natural

[Ray]

Thanks, that didn't solve the problem entirely but led me in the right
direction. After killing all processes (including using ipcrm) the problem
didn't go away for me. I then renamed the directory the db2diag.log was
referencing under sqlproc, created a new empty one w/same permissions,
restarted the DB, was then able to connect with SP's not working, stopped
again, moved old directory back, restarted, then everything was fine. It
defintely seems that some process was holding a directory or file in there.
Thanks again,
Ray

"Larry Menard" <lme...@ca.ibm.com> wrote in message
news:3AC76907...@ca.ibm.com...