DB2 Tools - SQL Analysis Tools

SQL Analysis Tools

     They are helpful in analyzing/tuning the SQL for better performance. db2exfmt, db2batch and db2advis are the tools available for SQL tuning. 

db2exfmt

   Exfmt (Explain Format) formats  data from explain tables into human readable format. One need to have read access on explain tables to use this tool. This tool uses statistics from explain snapshot if snapshot is available. Command syntax is as below 

>>-db2exfmt--+-----+--+-------------+--+-------------+---------->

             '- -1-'  '- -d--dbname-'  '- -e--schema-'   

>--+------------+--+---------------------------+--+-----+------->

   '- -f--+-O-+-'  |             .-----------. |  '- -l-'   

          +-Y-+    |             V           | |            

          '-C-'    '- -g--+---+----+-------+-+-'            

                          '-x-'    +-O-----+                

                                   +-I-----+                

                                   +-C-----+                

                                   '-+-T-+-'                

                                     '-F-'                  

>--+-----------+--+-------------+--+------------------+--------->

   '- -n--name-'  '- -s--schema-'  '- -m--module_name-'   

                                            .- -t-.   

>--+-------------------+--+--------------+--+-----+------------->

   '- -ot--object_type-'  '- -o--outfile-'            

>--+-----------------------+--+----------------+---------------->

   '- -u--userID--password-'  '- -w--timestamp-'   

>--+---------------+--+-------------+--+------------+----------->

   '- -no_map_char-'  '- -no_prompt-'  '- -runstats-'   

>--+--------------+--+--------------+--+-----+-----------------><

   '- -#--sectnbr-'  '- -v--srcvers-'  '- -h-'   

 use -1 for all the default options -e % -n % -s % -v % -w -1 -# 0

Click here to see the detail explanation for db2exfmt

db2advis

       DB2 design advisor suggests creation of indexes, materialized query tables, data partition tables and deleting unused objects. Suggestions are based on one or more queries passed by the user. The group of similar queries passed to db2advis is called workload.


Command Syntax
>>-db2advis--+- -d--+--database-name---------------------------->
             '- -db-'                  
>--+---------------------------------------------------------------------------------------------------------+-->
   +- -w--workload-name--------------------------------------------------------------------------------------+   
   +- -s--"statement"----------------------------------------------------------------------------------------+   
   +- -i--filename-------------------------------------------------------------------------------------------+   
   +- -g-----------------------------------------------------------------------------------------------------+   
   +- -qp--+--------------------------+----------------------------------------------------------------------+   
   |       '-start-time--+----------+-'                                                                      |   
   |                     '-end-time-'                                                                        |   
   '- -wlm--evmonname--+-----------------------------------------------------+--+--------------------------+-'   
                       +-+-workloadname-+--workloadname----------------------+  '-start-time--+----------+-'     
                       | '-wl-----------'                                    |                '-end-time-'       
                       '-+-serviceclass-+--superclassname--+---------------+-'                                   
                         '-sc-----------'                  '-,subclassname-'                                     
>--+--------------------------+--+------------------+----------->
   '- -a--userid--+---------+-'  '- -m--advise-type-'   
                  '-/passwd-'                           

>--+-----+--+-----+--+-----------------+------------------------>
   '- -x-'  '- -u-'  '- -l--disk-limit-'   

>--+----------------------+--+---------------+--+-----+--------->
   '- -t--max-advise-time-'  '- -k--+-HIGH-+-'  '- -f-'   
                                    +-MED--+              
                                    +-LOW--+              
                                    '-OFF--'              
>--+-----+--+------------------+--+------------------+---------->
   '- -r-'  '- -n--schema-name-'  '- -q--schema-name-'   

>--+----------------------+--+----------------------+----------->
   '- -b--tablespace-name-'  '- -c--tablespace-name-'   

>--+-----+--+-----+--+--------------+--+---------+-------------->
   '- -h-'  '- -p-'  '- -o--outfile-'  '- -nogen-'   

>--+---------------+--+----------------------------+------------>
   '- -delim--char-'  '- -mdcpctinflation--percent-'   

>--+----------------------------------+------------------------><
   '- -tables--table-predicate-clause-'   



Click here to see the detail explanation for db2advis




db2batch -Benchmark Tool

     To be honest I never used this tool but its great to see what this tool actually does. One can use this utility to get runtime stats for their query. It reads statements from flat file or standard input, dynamically prepares and describe the statements and print the answer set. Through this tool user can control the no of rows fetched from answer set and also send the fetched rows to output file.



Command Syntax
>>-db2batch--+-------------+--+----------------+---------------->
             '- -d--dbname-'  '- -f--file_name-'   
>--+--------------------+--+----------------------+------------->
   '- -a--userid/passwd-'  '- -m--parameters_file-'   

>--+-------------+--+-------------------------------------+----->
   '- -t--delcol-'  '- -r--result_file--+---------------+-'   
                                     '-,summary_file-'     
>--+-------------------------------------+--+--------------+---->
   '- -z--output_file--+---------------+-'  |      .-on--. |   
                       '-,summary_file-'    '- -c--+-off-+-'   
>--+-------------------+--+--------------+---------------------->
   |      .-short----. |  |      .-on--. |   
   '- -i--+-long-----+-'  '- -g--+-off-+-'   
          '-complete-'                       
>--+--------------------+--+-----------------+------------------>
   |      .-32768-----. |  |         .-on--. |   
   '- -w--+-col_width-+-'  '- -time--+-off-+-'   
>--+-----------------------+------------------------------------>
   '- -cli--+------------+-'   
            '-cache-size-'     
>--+-------------------------------+--+---------------------+--->
   |        .--------------------. |  |        .----------. |   
   |        V           .-hold-. | |  |        V          | |   
   '- -msw----switches--+-on---+-+-'  '- -mss----snapshot-+-'   
                        '-off--'                                
>--+---------------+--+----------------+--+--------------+------>
   |        .-RR-. |  '- -car--+-CC--+-'  '- -o--options-'   
   '- -iso--+-RS-+-'           '-WFO-'                       
            +-CS-+                                           
            '-UR-'                                           
>--+--------------+--+--------------+--+--------------+--------->
   |      .-off-. |  |      .-on--. |  |      .-off-. |   
   '- -v--+-on--+-'  '- -s--+-off-+-'  '- -q--+-on--+-'   
                                              '-del-'     
>--+---------------------+--+-----+----------------------------><
   '- -l--stmt_delimiter-'  +- -h-+   
                            +- -u-+   
                            '- -?-'   
Click here to see the detail explanation for db2batch