Team,

FLAVOUR: AIX-5.2/ Db2 V8.2 Fp9a Multi partition (32 bit)

$ db2expln -d sample -f f1.sql -terminal -g -z ';'

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool


The server was unable to establish a buffer for error reporting.

$ cat f1.sql
select count(*) from tester.employee;

The error message could not be found in DIAGLOG or any where.

If this user is granted with dbadm every thing went OK.
But, I don't want to give this user dbadm priv.

What might be the problem???

Thanks
RJ

for testing only: try to run it only with the help option.
What do you get then ?

Also: is the ';' needed or better a plain ; or ";" (but this might very much depend upon the shell being used)

Another way (my favorit, since it does not rely on packages and stuff):

change your statement to :
>db2 connect to sample
>db2 explain plan with snapshot for select * from employee

to see the plan, either use the Control Center GUI, or if you prefer ASCII (what I do) use

>db2exfmt
database is sample, leaving the timestamp empty returns you the last explain from the explain table.
Don't forget to give an output file, that is too much to just look at, usually also too many lines to scroll back.

Also dynexpln is finally using packages. Be aware that in special cases with default optimize level 5 packages generate a slightly different access path than dynamic SQL. I have seen that in Version 7 EEE last time, still it can give you a headache if not aware.

Of course I expect having the explain tables created and the necessary authority (DBADM, SYSADM or see the doc for details)

Thanks Juliane,

But this user does not have any tablespace to create explain tables. So, he cannot run db2exfmt.

He has to run db2expln which does not use any explain tables and so.

-RJ

I'm afraid that's a mistake...

correct.
http://publib.boulder.ibm.com/infoce...n/c0005134.htm

whenever you want to explain (does not matter using which tool or utility) you need explain tables.
Since this is only little data this should not be a problem.

Either generate them using the Control Center GUI, or manually: in SQLLIB\misc is a file EXPLAIN.DDL which has the ddl and should be executed with the userid used later for explain as well (default schema = userid).

OOPS!

May be I misunderstood these explain thingies.

I thought only db2exfmt needs Explain tables and db2expln will collect in memory, Iam wrong then.

The theory here is, user has to have the EXPLAIN tables in order to run explain utilities. In this case, he has to have a tablespace to store EXPLAIN tables. But, my user does not have tablespaces to use. So, first he need to have a tablespace to use/create EXPLAIN table.

Am I doing correct here?

Thanks,
RJ

Well, you being the administrator could do the explain for him if it is only a task done sometimes, and for you it should not be a problem.

If you have Development - Integration - Production instances: I usually create one tablespace for all developers to use for explain tables in Development and Integration; only if really needed in production - or I do explain there myself.

If that is too open for you: you can create the tables for them using the EXPLAIN.DDL and SET CURRENT SQLID = ... , followed by GRANT on the tables.

As it turns out: usually only few developers become explain specialists - in case of problems the others go to them anyway. so it is not that you get hundreds of new tables or so ...

ERROR:
The server was unable to establish a buffer for error reporting.

Have you seen this before. I cannot grant this particular user dbadm, so this time I need to solve the problem.
Any help for your extremely stressed UDB DBA would be appreciated.

NOTE: This user want to run only db2expln

Any taker, challenger.

-RJ

try binding db2expln.bnd, db2exsrv.bnd, db2exdyn.bnd and granting the
privilege to public

What exactly is the complete error message and what was done? Any SQLCode or so ?

From the documentation:
So as wangwhonnew states you have to grant execute privileges on those packages to the user.

This error is solved by doing...

binding db2expln.bnd, db2exsrv.bnd, db2exdyn.bnd and granting the
privilege to public

CHEERS :-)
RJ

solution from command prompt. result can be found in f1res.text or use -t to see in the terminal.


C:\Program Files\IBM\SQLLIB\BIN>db2expln -d dbname -u username password -f f1.sql -o f1res.txt