IBM Support

Collecting Data for DB2 Compiler Issues

Technote (FAQ)


If you think that the DB2® Universal Database™ (DB2 UDB) or DB2 Version 9 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.



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?

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

To collect diagnostic data for compiler issues:


Use this method if you are running DB2 UDB Version 8.2 FixPak 2 (also known as DB2 UDB Version 8.1 FixPak 9) or greater and DB2 Version 9.

1. Use the db2support tool to gather the diagnostics. Note, the "-cl 1" option of db2support below gathers db2exfmt output and the "-cl 0" option does not. Please select the appropriate command based on the appropriate symptoms listed in the "Solution" section above.
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.

2. Once the zip file is complete, unzip it into a temporary directory, then unzip the file and examine the file "optimizer.log" to ensure the export of systables, export of sysviews and db2look output are not missing.
3. If either of these pieces of information is missing, then either manually collect the missing information or re-run db2support but with these options:
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.


Use this method if you are running DB2 UDB Version 8.2 FixPak 1 (also known as DB2 UDB Version 8.1 FixPak 8) or an earlier FixPak.

  1. Prior to beginning gathering the diagnostic information, create a directory called "catalogs". This is where you should store the information you will be gathering.

  2. Gather the following configuration information:

  3. Operation system version, # CPUs, memory/RAM available

  4. db2level > db2level.out

  5. db2 get dbm cfg > dbmcfg.txt

  6. db2set -all > db2set.txt

  7. If you are using a single partition, issue:
    db2 get db cfg for db_name > dbcfg.txt

  8. If there are multiple partitions gather:
    db2 get db cfg for db_name > dbcfg.txt
    db2_all db2 get db cfg for db_name > dbcfg.all
    Also gather the db2nodes.cfg file.

  9. Gather the following query-specific information:

  10. Save the query with a semicolon (";") at the end of the query in a file. This file name will be substituted for <input file> below.

  11. From the operating system command line, cd to the sqllib/misc directory (SQLLIB\MISC on Intel) and run:
    db2 -tvf EXPLAIN.DDL

  12. Go back to the catalogs directory you created and gather explains and batch for the problem query. If there is a good and bad query which are comparable, then repeat these steps for both queries.

  • If it is dynamic SQL (the slow query can be reproduced from the CLP - command line processor), run:
    db2 set current explain mode explain
    db2 -tvf input_file
    db2 set current explain mode no
    db2exfmt -d db_name -g TIC -w -1 -n % -s % -# 0 -o exfmt_bad.txt

  • If it is static SQL (slow query ONLY reproducible via application), run:
    PREP command with the "explain all" option or perform the BIND command on the package, specifying the "explain" option to populate the explain tables
    db2exfmt -d db_name -g TIC -w -1 -n % -s % -# 0 -o exfmt_bad.txt

  • If the slow query cannot be reproduced from the CLP, but rather only through a stored procedure where the BIND is not explicitly issued (that is, DB2 UDB automatically issues the BIND), then do the following:
    db2 terminate
    Run the stored procedure
    db2exfmt -d db_name -g TIC -w -1 -n % -s % -# 0 -o exfmt_bad.txt

    i. Run: db2look -d db_name -l -f -o storage.txt

    ii. Get all the table names from the bottom of the exfmt_bad.txt file above and hardcode the table names (for example, TABLE_1, TABLE_2, etc.) in the db2look command below. The table names must be in UPPER CASE:
  • db2look -d db_name -a -e -m -z schema_name -t TABLE_1 TABLE_2 -o tabstat.ddl
  • db2look -d db_name -f -m -l -a -e -o db2look.txt

    i. Gather db2batch output. Take caution in running db2batch as it runs the SQL statement, and for long running SQL statements this may not be desirable. db2batch runs with default isolation level Repeatable Read and in some cases can hang the system. The isolation level can be changed by rebinding the package to use a different isolation level. The db2batch.bnd file can be found in the sqllib/bnd subdirectory:
  • To change the isolation level to CS (cursor stability), connect to the database and run:
    db2 bind db2batch.bnd isolation cs blocking all grant public
  • To run db2batch:
    db2batch -d db_name -f input_file -i complete -o p 5 o opt_level r 0 -r bad.txt
    ... replacing opt_level with the optimization level of your choice. Note input_file is the same as step 2: 2.B.iv.

    1. Export the system catalogs.
    Note the extra information required for specific releases and specific environments (such as where federated databases are involved).

    ii. The subdirectories "TABLES", "VIEWS", "FUNCTIONS", "SEQUENCES" and "ROUTINES" have to be created before running the exports.

    iii. Run these export statements:
    [The subdirectories in the EXPORT statements below must use: "\" if you are using DB2 UDB on Windows® or OS/2® instead of "/" which is for UNIX® and Linux® (for the LOBS TO part). If you see a SQL3040, return code 1 returned from the EXPORT statements, you need to add more LOB files (lob61, lob 62 etc..) to the command.]

  • export to systables.ixf of ixf lobs to TABLES/ lobfile tlob1, tlob2, tlob3, tlob4, tlob5, tlob6, tlob7, tlob8, tlob9, tlob10, tlob11, tlob12, tlob13, tlob14, tlob15, tlob16, tlob17, tlob18, tlob19, tlob20, tlob21, tlob22, tlob23, tlob24, tlob25, tlob26, tlob27, tlob28, tlob29, tlob30, tlob31, tlob32, tlob33, tlob34, tlob35, tlob36, tlob37, tlob38, tlob39, tlob40, tlob41, tlob42, tlob43, tlob44, tlob45, tlob46, tlob47, tlob48, tlob49, tlob50, tlob51, tlob52, tlob53, tlob54, tlob55, tlob56, tlob57, tlob58, tlob59, tlob60
    modified by lobsinfile
    select * from sysibm.systables;

  • export to sysviews.ixf of ixf lobs to VIEWS/ lobfile
    vlob1, vlob2, vlob3, vlob4, vlob5, vlob6, vlob7, vlob8, vlob9, vlob10, vlob11, vlob12, vlob13, vlob14, vlob15, vlob16, vlob17, vlob18, vlob19, vlob20, vlob21, vlob22, vlob23, vlob24, vlob25, vlob26, vlob27, vlob28, vlob29, vlob30, vlob31, vlob32, vlob33, vlob34, vlob35, vlob36, vlob37, vlob38, vlob39, vlob40, vlob41, vlob42, vlob43, vlob44, vlob45, vlob46, vlob47, vlob48, vlob49, vlob50, vlob51, vlob52, vlob53, vlob54, vlob55, vlob56, vlob57, vlob58, vlob59, vlob60
    modified by lobsinfile
    select * from sysibm.sysviews;

  • export to sysfunctions.ixf of ixf lobs to FUNCTIONS/ lobfile
    flob1, flob2, flob3, flob4, flob5, flob6, flob7, flob8, flob9, flob10, flob11, flob12, flob13, flob14, flob15, flob16, flob17, flob18, flob19, flob20, flob21, flob22, flob23, flob24, flob25, flob26, flob27, flob28, flob29, flob30, flob31, flob32, flob33, flob34, flob35, flob36, flob37, flob38, flob39, flob40, flob41, flob42, flob43, flob44, flob45, flob46, flob47, flob48, flob49, flob50, flob51, flob52, flob53, flob54, flob55, flob56, flob57, flob58, flob59, flob60
    modified by lobsinfile
    select * from sysibm.sysfunctions;

  • export to syssequences.ixf of ixf lobs to SEQUENCES/ lobfile
    sqlob1, sqlob2, sqlob3, sqlob4, sqlob5, sqlob6, sqlob7, sqlob8, sqlob9, sqlob10, sqlob11, sqlob12, sqlob13, sqlob14, sqlob15, sqlob16, sqlob17, sqlob18, sqlob19, sqlob20, sqlob21, sqlob22, sqlob23, sqlob24, sqlob25, sqlob26, sqlob27, sqlob28, sqlob28, sqlob30, sqlob31, sqlob32, sqlob33, sqlob34, sqlob35, sqlob36, sqlob37, sqlob38, sqlob39, sqlob40, sqlob41, sqlob42, sqlob43, sqlob44, sqlob45, sqlob46, sqlob47, sqlob48, sqlob49, sqlob50, sqlob51, sqlob52, sqlob53, sqlob54, sqlob55, sqlob56, sqlob57, sqlob58, sqlob59, sqlob60
    modified by lobsinfile
    select * from sysibm.syssequences;

  • export to systablespaces.ixf of ixf
    modified by lobsinfile
    select * from sysibm.systablespaces;

  • export to sysbufferpools.ixf of ixf
    modified by lobsinfile
    select * from sysibm.sysbufferpools;

  • export to sysnodegroupdef.ixf of ixf
    modified by lobsinfile
    select * from sysibm.sysnodegroupdef;

  • export to syspartitionmaps.ixf of ixf
    modified by lobsinfile
    select * from sysibm.syspartitionmaps;

  • export to sysdatatypes.ixf of ixf
    modified by lobsinfile
    select * from sysibm.sysdatatypes;

    i. If you are using federated databases, collect the following additional information:

  • Create a subdirectory called "SERVERS" (Note the "/" below for UNIX and Linux. If you are using Windows, use "\" instead).

  • export to sysservers.ixf of ixf lobs to SERVERS/ lobfile
    slob1, slob2, slob3, slob4, slob5, slob6, slob7, slob8, slob9, slob10, slob11, slob12, slob13, slob14, slob15, slob16, slob17, slob18, slob19, slob20, slob21, slob22, slob23, slob24, slob25, slob26, slob27, slob28, slob29, slob30, slob31, slob32, slob33, slob34, slob35, slob36, slob37, slob38, slob39, slob40, slob41, slob42, slob43, slob44, slob45, slob46, slob47, slob48, slob49, slob50, slob51, slob52, slob53, slob54, slob55, slob56, slob57, slob58, slob59, slob60
    modified by lobsinfile
    select * from sysibm.sysservers;

  • export to sysserveroptions.ixf of ixf select * from sysibm.sysserveroptions;

  • export to syswrappers.ixf of ixf select * from sysibm.syswrappers;

  • export to syswrapoptions.ixf of ixf select * from sysibm.syswrapoptions;

  • export to sysuseroptions.ixf of ixf select * from sysibm.sysuseroptions;

  • export to sysfuncmappings.ixf of ixf select * from sysibm.sysfuncmappings;

  • export to sysfuncmapoptions.ixf of ixf select * from sysibm.sysfuncmapoptions;

  • export to sysfuncmapparmoptions.ixf of ixf select * from sysibm.sysfuncmapparmoptions;

  • db2 list node directory show detail

  • db2 list db directory

    i. If you are using DB2 UDB Version 8 or DB2 Version 9 (instead of DB2 UDB Version 7), collect the following additional information:

  • export to sysbufferpoolnodes.ixf of ixf
    modified by lobsinfile
    select * from sysibm.sysbufferpoolnodes;

  • export to sysroutines.ixf of ixf lobs to ROUTINES/ lobfile
    rlob1, rlob2, rlob3, rlob4, rlob5, rlob6, rlob7, rlob8, rlob9, rlob10, rlob11, rlob12, rlob13, rlob14, rlob15, rlob16, rlob17, rlob18, rlob19, rlob20, rlob21, rlob22, rlob23, rlob24, rlob25, rlob26, rlob27, rlob28, rlob28, rlob30, rlob31, rlob32, rlob33, rlob34, rlob35, rlob36, rlob37, rlob38, rlob39, rlob40, rlob41, rlob42, rlob43, rlob44, rlob45, rlob46, rlob47, rlob48, rlob49, rlob50, rlob51, rlob52, rlob53, rlob54, rlob55, rlob56, rlob57, rlob58, rlob59, rlob60
    modified by lobsinfile
    select * from sysibm.sysroutines;

    i. If you are using DB2 UDB Version 8.1 FixPak 7A or greater (also known as DB2 UDB Version 8.2) or DB2 Version 9, collect the following additional information as well:

  • export to syscolgroups.ixf of ixf
    modified by lobsinfile
    select * from sysibm.syscolgroups

  • export to syscolgroupscols.ixf of ixf
    modified by lobsinfile
    select * from sysibm.syscolgroupscols;

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


Related information

db2support tool
Additional Collecting Data Documents

Document information

More support for: DB2 for Linux, UNIX and Windows
Compiler - Runstats

Software version: 7, 8, 9.1, 9.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: Enterprise Server, Express, Personal, Personal Developer's, Workgroup Server

Reference #: 1106550

Modified date: 11 August 2009

Translate this page: