Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Posts
    111

    Arrow Unanswered: db2expln "Errors out"

    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

  2. #2
    Join Date
    Oct 2005
    Posts
    109
    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)
    Juliane

  3. #3
    Join Date
    Sep 2004
    Posts
    111

    Arrow

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by udbraja
    db2expln which does not use any explain tables and so.
    I'm afraid that's a mistake...

  5. #5
    Join Date
    Oct 2005
    Posts
    109
    correct.
    Before you can capture explain information, you create the relational tables in which the optimizer stores the explain information
    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).
    Juliane

  6. #6
    Join Date
    Sep 2004
    Posts
    111

    Arrow

    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

  7. #7
    Join Date
    Oct 2005
    Posts
    109
    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 ...
    Juliane

  8. #8
    Join Date
    Sep 2004
    Posts
    111

    Arrow

    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

  9. #9
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    try binding db2expln.bnd, db2exsrv.bnd, db2exdyn.bnd and granting the
    privilege to public

  10. #10
    Join Date
    Oct 2005
    Posts
    109
    What exactly is the complete error message and what was done? Any SQLCode or so ?

    From the documentation:
    To run db2expln, you must have the SELECT privilege on the system catalog views as well as the EXECUTE privilege for the db2expln, db2exsrv, and db2exdyn packages.
    So as wangwhonnew states you have to grant execute privileges on those packages to the user.
    Juliane

  11. #11
    Join Date
    Sep 2004
    Posts
    111

    Arrow

    This error is solved by doing...

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

    CHEERS :-)
    RJ

  12. #12
    Join Date
    Aug 2011
    Posts
    1

    Thumbs up from command prompt

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •