IBM Support

Why optimizer db2support is collecting very minimal details in Purescale setup

Technical Blog Post


Why optimizer db2support is collecting very minimal details in Purescale setup


Many times  an  optimizer db2support is collected to investigate issues related to optimizer issue with a DB2 query.

The common  syntax to collect optimizer db2support  is,

db2support . -d  <db-name>  -sf  <sql_file>  -cl 1

However,  many times this fail  to collect  the required optimizer data in a Purescale setup.  Even sometimes fail with multi-partition setup.


The way it will work is to  add  "-locahost"   as an option with the syntax. 


db2support . -d  <db-name>  -sf  <sql_file>  -cl 1 -localhost


-localhost  option is notable as the optimizer data is usually  needed from one partition only.

Without  -localhost option  the collection will go to all the members in the instance.


-localhost usage for optimizer db2support collection is  documented  in this   Knowledge Center page,

However, it's  very easy to miss.


This portion in the above page is to be  referred,


-sf SQL file | -sqlfile SQL file
Specifies the file path containing the SQL statement for which data is being collected.
In DB2 pureScale and partitioned database environments, you must specify the absolute file path and ensure that the SQL file is stored in a directory that is accessible by all host. However, for optimizer collections in these multiple host environments it is recommended that you use the -localhost option to collect data on a single host. For example, to collect data on a single host you might run the following command:
 db2support -d <database_name> -sf <filepath> -localhost

Where filepath is the absolute path of the SQL file. When you use the -localhost option, as shown in the preceding example, the file name can be used in place of the absolute path if the file is stored in the current directory.


[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]