db2advis giving errors - not able to INSERT into ADVISE_INSTANCE table

Re: db2advis giving errors - not able to INSERT into ADVISE_INSTANCE table

Verify the schema name of your advise tables.
Also verify that the id you are using has select, insert, update, delete
privileges in the tables.
I'd assume so as you're using the db2admin id which is typically a sysadmin
authority.

Possibly the advise/esplain tables were create dusing another id.
This is what I think the msg:
"Using user id as default schema name. Use -n option to specify schema"
means.
HTH, Pierre.


--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"dotyet" <dotyet@yahoo.c om> a écrit dans le message de
news:1114020609 .110436.173670@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> I have been given the daunting task of sql query tuning. I am looking
> for ways to get started with that. I am on DB2 UDB 8.2 (8.1 with Fixpak
> 8) on Windows. One point which I could think about starting from is the
> db2 design advisor. However, whenever I try to execute the db2advis
> utility I get the following error:
>
> C:\db_tuning>db 2advis -d DB_TEST1 -user db2admin/db2admin -i
> query_1.sql
>
> Using user id as default schema name. Use -n option to specify schema
>
> SQL1042C An unexpected system error occurred. SQLSTATE=58004
>
> *************** *************** *******
> * CONTENTS OF SQLCA *
> *************** *************** *******
>
> SQLCABC = 136
> SQLCODE = -1042
> SQLERRMC =
> SQLERRP = SQLERFME
> sqlerrd[0] = 0
> sqlerrd[1] = 0
> sqlerrd[2] = 0
> sqlerrd[3] = 0
> sqlerrd[4] = 0
> sqlerrd[5] = 0
> SQLWARN =
> SQLSTATE = 58004
> Critical SQLCODE. Exiting db2advis ...
> The insert into the ADVISE_INSTANCE table has failed.
>
> 0 solutions were evaluated by the advisor
>
> exiting with error code [-1042]
>
> C:\db_tuning>
>
> I can see the table ADVISE_INSTANCE in the database, but I don't know
> what is preventing the db2advis utility from inserting the rows in that
> table.
>
> The db2diag.log has the following information:
>
> 2005-04-20-13.19.42.875000-240 E2119721H402 LEVEL: Error
> PID : 2816 TID : 2836 PROC : db2advis.exe
> INSTANCE: DB2 NODE : 000
> APPID : *LOCAL.DB2.0504 20171942
> FUNCTION: DB2 UDB, User Application, User Function, probe:0
> DATA #1 : String, 22 bytes
> Design Advisor Message
> DATA #2 : String, 48 bytes
>
> *** Error at line 1362, in file db2advio.SQC
>
> 2005-04-20-13.19.42.875000-240 E2120125H401 LEVEL: Error
> PID : 2816 TID : 2836 PROC : db2advis.exe
> INSTANCE: DB2 NODE : 000
> APPID : *LOCAL.DB2.0504 20171942
> FUNCTION: DB2 UDB, User Application, User Function, probe:0
> DATA #1 : String, 22 bytes
> Design Advisor Message
> DATA #2 : String, 47 bytes
>
> *** Error at line 945, in file db2advis.SQC
>
> 2005-04-20-13.19.42.890000-240 E2120528H402 LEVEL: Error
> PID : 2816 TID : 2836 PROC : db2advis.exe
> INSTANCE: DB2 NODE : 000
> APPID : *LOCAL.DB2.0504 20171942
> FUNCTION: DB2 UDB, User Application, User Function, probe:0
> DATA #1 : String, 22 bytes
> Design Advisor Message
> DATA #2 : String, 48 bytes
>
> *** Error at line 1396, in file db2advis.SQC
>
> 2005-04-20-13.19.42.890000-240 E2120932H404 LEVEL: Error
> PID : 2816 TID : 2836 PROC : db2advis.exe
> INSTANCE: DB2 NODE : 000
> APPID : *LOCAL.DB2.0504 20171942
> FUNCTION: DB2 UDB, User Application, User Function, probe:0
> DATA #1 : String, 22 bytes
> Design Advisor Message
> DATA #2 : String, 50 bytes
>
> *** Error at line 2456, in file db2advmisc.SQC
>
> 2005-04-20-13.19.42.906000-240 E2121338H367 LEVEL: Error
> PID : 2816 TID : 2836 PROC : db2advis.exe
> INSTANCE: DB2 NODE : 000
> FUNCTION: DB2 UDB, User Application, User Function, probe:0
> DATA #1 : String, 22 bytes
> Design Advisor Message
> DATA #2 : String, 48 bytes
>
> Any help would be much appreciated.
>
> regards,
> dotyet
>[/color]

Re: db2advis giving errors - not able to INSERT into ADVISE_INSTANCE table

that did not help, I have given this user dbadm authority and also
provided it with the correct explain table schema name (-n flag).

still need some help here.

regards,
dotyet

Re: db2advis giving errors - not able to INSERT into ADVISE_INSTANCE table

This was fixed in FP9. The problem was that the userid in the real user's
id was being used as the explain table schema name instead of either
SYSTOOLS or the new -user userid. In FP8, the workaround is to add the
option: -* <explain table schema>

E.g., If the userid is Bill and logging into userid Jim, then use:
db2advis -d XX -a Jim -i filein -* Jim

Note that the explain tables need to be created under the Jim schema

Danny

Re: db2advis giving errors - not able to INSERT into ADVISE_INSTANCE table

Note that "-*" is the minus sign followed by the asterisk symbol.
Just needed to clarify as it looks like an underscore in my font
Danny

re:db2advis giving errors - not able to INSERT into ADVISE_INST

This was fixed in FP9. The problem was that the userid in the real
user's
id was being used as the explain table schema name instead of either
SYSTOOLS or the new -user userid. In FP8, the workaround is to add
the
option: -* <explain table schema>

E.g., If the userid is Bill and logging into userid Jim, then use:
db2advis -d XX -a Jim -i filein -* Jim

(-* is a minus sign followed by the asterisk)
Note that the explain tables need to be created under the Jim schema

Danny