The explain output file

The SET EXPLAIN statement enables or disables recording measurements of queries in the current session, including the plan of the query optimizer, an estimate of the number of rows returned, and the relative cost of the query. The measurements appear in an output file.

When you run the onmode -Y command to turn on dynamic SET EXPLAIN, the output is displayed in a new explain output file. If a file from the SET EXPLAIN statement exists, the database server stops using it, and instead uses the file created by onmode -Y until the administrator turns off dynamic SET EXPLAIN for the session.

The output file specifies if external directives are in effect.

The following codes in the Query Statistics section of the explain output file provide information about external tables:

• xlcnv identifies an operation that is loading data from an external table and inserting the data into a base table. Here x = external table, l = loading, and cnv = converter
• xucnv identifies an operation that is unloading data from an external table and inserting the data into a base table. Here x = external table, u = unloading, and cnv = converter

The Query Statistics section of the explain output file is a useful resource for debugging performance problems. See Query statistics section provides performance debugging information.