unable to run db2advis on restored backup from another machine

[lennart]

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

[Shashi Mannepalli]

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

[lennart]

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]

[Shashi Mannepalli]

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]

[Lennart]

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

[lennart]

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