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?

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

      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
    2. 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;

To collect diagnostic data for query performance issues.

1. db2support output_directory -d database_name -sf sql_file -cl 1
        • output_directory is the name of the directory where the archived library will be created.
        • 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.
    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

    [{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"Compiler - Runstats","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","Edition":"Enterprise Server;Express;Personal;Personal Developer's;Workgroup Server"}]

    Document Information

    Modified date:
    16 June 2018

    UID

    swg21106550