IBM Support

Collecting Data for DB2 Compiler Issues

Question & Answer


Question

[

If you think that the DB2® Universal Database™ (DB2 UDB) or DB2 Version 9,7 (or higher) compiler might be the origin of a problem, collect diagnostic data that you or IBM® Software Support can use to diagnose and resolve the problem.

]

Cause

Situation
This section lists questions regarding the conditions under which the problem occurred.
  • Is this a reoccurring issue or the first time experiencing the issue?
  • Have there been any recent changes to the instance or database cfg files?
  • Have there been any recent changes to your operating system?
  • Are you able to reproduce this behavior? If so, can a testcase be provided?

Impact


This section lists questions regarding the effects of the problem.
  • Is this a production, development, or test environment?
  • How many users are affected by this problem?
  • What is the business impact of this problem?
  • Are there other repercussions to the problem occurring?

Configuration

Create the explain tables: db2 -tvf ~/sqllib/cfg/EXPLAIN.DDL

Information to Collect



If your problem involves a query that fails to compile or if you cannot get an explain plan, you should omit collecting db2batch and db2exfmt output (to avoid running into the problem).
Examples of cases where an explain plan cannot be gathered are:
  • queries that return an SQL0101 when compiled or executed
  • queries that return an SQL0901 when compiled or executed
  • queries that crash the instance when compiled or executed
set current schema <schema name>;
set current explain mode explain;
// run any working query ex: "select count(*) from <table>"
set current explain mode no;
db2exfmt -d <db name> -g TIC -w -1 -n % -s % -# 0 -o exfmt.out;
1) db2support output_directory -d database_name -cl 0
  • output_directory is the name of the directory where the archived library will be created.
  • database_name is the name of the database
  • Note: Add -localhost parameter for databases using Data Partitioning Feature (DFP). Example:  db2support output_directory -d database_name -localhost -cl 0
To collect diagnostic data for query performance issues.
2) db2support output_directory -d database_name -sf sql_file -cl 1
  • database_name is the name of the database
  • sql_file is the name of the file that contains the query. The query should be terminated by a semicolon.
  • output_directory is the name of the directory where the archived library will be created.
  • Note: Add -localhost parameter for databases using Data Partitioning Feature (DFP) as per previous example
3) Collecting Explains for Stored Procedures and packages refer to Collecting explain data for SQL stored procedures and dynamic SQL
Submitting information to IBM Support

Once you have collected your information, you can begin Problem Determination through the product Support web page, or simply submit the diagnostic information to IBM support. Use the document below for submitting information to IBM Support.

Submitting diagnostic information to IBM Technical Support for problem determination

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[],"Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1;10.5;11.1;11.5","Edition":"Enterprise Server;Express;Personal;Personal Developer's;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
14 April 2020

UID

swg21106550

Page Feedback