Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    134

    Unanswered: problems with db2advis

    Hi all,
    I'm using Db2 10.5.0.8 on Linux.

    I need to define indexes on my database PROD respect to a particular workload.

    What I do is:

    - create the explain tables for DB2INST1
    - run as db2inst1 "db2 reset monitor for database PROD"
    - run the workload
    - run as db2inst1 "db2advis -g -p -d PROD -o advis.out -m I -q MYSCHEMA -n MYSCHEMA -noxml

    At the end in the output file I found a number of recommended indexes which is good, but I also find many errors that exclude many statements from the processed workload.
    These errors are mostly:
    - SQL0440N No authorized routine named routine-name of type routine-type having compatible arguments was found
    ( These functions are invoked without schema in the workload, being it set to MYSCHEMA globally for the session. It seems like db2advis doesn't associate the function with the schema MYSCHEMA, even if this is specified in the db2advis cmd line)

    - SQL0104N An unexpected token "DECLARE C1 cursor with return to caller for" was found following "BEGIN-OF-STATEMENT"
    (cursors are used in some stored procedures invoked during the workload, and those statements are excluded by the computation for indexes)

    - SQL0418N The statement was not processed because the statement contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value.
    (these statements use untyped parameter markers, but they are run without errors during the workload, so why db2advis excludes them? )

    - SQL0204N "SESSION.VISIBLE_ASSETS" is an undefined name.
    (so excluding the statemets referencing temporary tables)

    Could you please suggest how to eliminate those errors and avoid that db2advis excludes these statements?
    Thanks a lot

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Without seeing the code, we can only guess at the causes, as you don't specify exactly how you run the workload and how that differs from how the workload normally gets run.

    Consider which of those errors matter for purpose of index recommendation.

    Queries involving DGTTs (session tables) will use any indexes on those session tables that are appropriate.

    Does the workload set its own function path (set path ...) ? I believe the -q is for unqualified table/view objects not routines. If the workload does not set a function path you should set it for the session made by db2advis by including it in the workload.

    Consider monitoring the workload with other tools (simple one is monreport.dbsummary, and also checking the package-cache after the workload, and top_dynamic_sql etc) to find the statements that are the most expensive (in terms of I/O and metrics like rows-read to rows-returned ratio etc).

  3. #3
    Join Date
    Mar 2012
    Posts
    134
    Quote Originally Posted by db2mor View Post
    Without seeing the code, we can only guess at the causes, as you don't specify exactly how you run the workload and how that differs from how the workload normally gets run.
    The workload runs the same way it normally gets run. At some time I reset the monitors, wait some time, then execute db2advis.

    Quote Originally Posted by db2mor View Post
    Does the workload set its own function path (set path ...) ?
    The workload leverages an ODBC connection pool, every connection is established setting the current schema and current function path as connection properties, so the functions called without a schema are automatically referred to MYSCHEMA. The workload always runs without errors, the errors appears just in db2advis.

    Quote Originally Posted by db2mor View Post
    Consider monitoring the workload with other tools...
    I prefer db2advis because it seems the only one which can take into account and optimize the whole workload, without proceeding statement by statement

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,540
    Provided Answers: 11
    to avoid all these messages of invalid statements we do :
    get snapshot ..dynamic sql ... > to file
    we extract only the real statements - with grep -v we filter all un-wanted stmts : declare cursor, inserts ..... and add ; after each statement ....
    the clean file we submit to db2advis
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5-V11 Fundamentals- DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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