469,602 Members | 1,931 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

unable to run db2advis on restored backup from another machine


Here's the scenario.

Take backup on production machine
Restore backup on development machine

OS and fixpak identical, but - and I suspect this has something to do
with it - the dbadm user differs between prod and dev.

drop explain tables and recreate them for dbadm on dev.

Now:

[db2inst1@faramir ~]$ db2advis -d NYA -m I -s "select 1 from
sysibm.sysdummy1"

Using user id as default schema name. Use -n option to specify schema
CALL SYSPROC.GET_DBSIZE_INFO failed (workaround - use -l)

SQL0444N Routine "*IZE_INFO" (specific name "GET_DBSIZE_INFO") is
implemented
with code in library or path "...sqllib/function/db2stmg", function
"get_dbsize_info" which cannot be accessed. Reason
*************************************
* CONTENTS OF SQLCA *
*************************************

SQLCABC = 136
SQLCODE = -444
SQLERRMC =
*IZE_INFO˙GET_DBSIZE_INFO˙...sqllib/function/db2stmg˙get_dbsize_info˙4
SQLERRP = SQLERLIB
sqlerrd[0] = 0
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 0
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42724
Critical SQLCODE. Exiting db2advis ...
The insert into the ADVISE_INSTANCE table has failed.

0 solutions were evaluated by the advisor

exiting with error code [-444]
I have tried binding as:
db2 bind @db2ubind.lst grant public
db2 bind @db2cli.lst grant public
But that doesnt seem to help. If I create a new db on dev machine,
explain works against that one, and explain works in prod machine. Any
ideas on what needs to be done on restored db to be able to run
db2advis, anyone?

Thanx in advance
/Lennart

Aug 3 '06 #1
5 4721
Try running

db2updv8 on the database.

cheers...
Shashi Mannepalli
le*****@kommunicera.umea.se wrote:
Here's the scenario.

Take backup on production machine
Restore backup on development machine

OS and fixpak identical, but - and I suspect this has something to do
with it - the dbadm user differs between prod and dev.

drop explain tables and recreate them for dbadm on dev.

Now:

[db2inst1@faramir ~]$ db2advis -d NYA -m I -s "select 1 from
sysibm.sysdummy1"

Using user id as default schema name. Use -n option to specify schema
CALL SYSPROC.GET_DBSIZE_INFO failed (workaround - use -l)

SQL0444N Routine "*IZE_INFO" (specific name "GET_DBSIZE_INFO") is
implemented
with code in library or path "...sqllib/function/db2stmg", function
"get_dbsize_info" which cannot be accessed. Reason
*************************************
* CONTENTS OF SQLCA *
*************************************

SQLCABC = 136
SQLCODE = -444
SQLERRMC =
*IZE_INFO˙GET_DBSIZE_INFO˙...sqllib/function/db2stmg˙get_dbsize_info˙4
SQLERRP = SQLERLIB
sqlerrd[0] = 0
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 0
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42724
Critical SQLCODE. Exiting db2advis ...
The insert into the ADVISE_INSTANCE table has failed.

0 solutions were evaluated by the advisor

exiting with error code [-444]
I have tried binding as:
db2 bind @db2ubind.lst grant public
db2 bind @db2cli.lst grant public
But that doesnt seem to help. If I create a new db on dev machine,
explain works against that one, and explain works in prod machine. Any
ideas on what needs to be done on restored db to be able to run
db2advis, anyone?

Thanx in advance
/Lennart
Aug 3 '06 #2

Shashi Mannepalli wrote:
Try running

db2updv8 on the database.
Thank you for the answer. Unfortenate the db has never been V7, anyhow
I tried your sugggestion:

[db2inst1@faramir ~]$ db2updv8 -d NYA -u db2inst1 -p XXXXXX -c 1

Alter procedures that use obsolete schema "db2schem":

Routineschema Routinename Implementation
--------------- ------------------ ------------------------------

0 procedure(s) modified.
DB2UPDV8 complete successfully for database 'NYA'.
but the problem remains

[db2inst1@faramir ~]$ db2advis -d NYA -m I -s "select * from
sysibm.sysdummy1"

Using user id as default schema name. Use -n option to specify schema
CALL SYSPROC.GET_DBSIZE_INFO failed (workaround - use -l)

SQL0444N Routine "*IZE_INFO" (specific name "GET_DBSIZE_INFO") is
implemented
with code in library or path "...sqllib/function/db2stmg", function
"get_dbsize_info" which cannot be accessed. Reason
*************************************
* CONTENTS OF SQLCA *
*************************************

SQLCABC = 136
SQLCODE = -444
SQLERRMC =
*IZE_INFOGET_DBSIZE_INFO...sqllib/function/db2stmgget_dbsize_info4
SQLERRP = SQLERLIB
sqlerrd[0] = 0
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 0
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42724
Critical SQLCODE. Exiting db2advis ...
The insert into the ADVISE_INSTANCE table has failed.

0 solutions were evaluated by the advisor

exiting with error code [-444]

Aug 4 '06 #3
Looks like all the Tables are not there...for this ADVISOR.

Run this again...

$insthome/sqllib/misc

db2 connect to <database>
db2 -tvf EXPLAIN.DDL

Then run this again....

cheers...
Shashi Mannepalli
le*****@kommunicera.umea.se wrote:
Shashi Mannepalli wrote:
Try running

db2updv8 on the database.

Thank you for the answer. Unfortenate the db has never been V7, anyhow
I tried your sugggestion:

[db2inst1@faramir ~]$ db2updv8 -d NYA -u db2inst1 -p XXXXXX -c 1

Alter procedures that use obsolete schema "db2schem":

Routineschema Routinename Implementation
--------------- ------------------ ------------------------------

0 procedure(s) modified.
DB2UPDV8 complete successfully for database 'NYA'.
but the problem remains

[db2inst1@faramir ~]$ db2advis -d NYA -m I -s "select * from
sysibm.sysdummy1"

Using user id as default schema name. Use -n option to specify schema
CALL SYSPROC.GET_DBSIZE_INFO failed (workaround - use -l)

SQL0444N Routine "*IZE_INFO" (specific name "GET_DBSIZE_INFO") is
implemented
with code in library or path "...sqllib/function/db2stmg", function
"get_dbsize_info" which cannot be accessed. Reason
*************************************
* CONTENTS OF SQLCA *
*************************************

SQLCABC = 136
SQLCODE = -444
SQLERRMC =
*IZE_INFOGET_DBSIZE_INFO...sqllib/function/db2stmgget_dbsize_info4
SQLERRP = SQLERLIB
sqlerrd[0] = 0
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 0
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42724
Critical SQLCODE. Exiting db2advis ...
The insert into the ADVISE_INSTANCE table has failed.

0 solutions were evaluated by the advisor

exiting with error code [-444]
Aug 4 '06 #4

Shashi Mannepalli wrote:
Looks like all the Tables are not there...for this ADVISOR.

Run this again...

$insthome/sqllib/misc

db2 connect to <database>
db2 -tvf EXPLAIN.DDL

Then run this again....
All the tables are there, but I ran the EXPLAIN.ddl anyhow. Problem
still remains. I also tested to restore another backup to another
machine and the error is there as well
/Lennart

Aug 4 '06 #5

In case anyone else encounters this, the solution is to add the dbadm
user from produktionserver, add user to dbadm group, grant dbadm on db
to user. The run db2advis as that user.
/Lennart

Aug 30 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Shane | last post: by
16 posts views Thread by gumby | last post: by
3 posts views Thread by Microsoft.com | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.