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.