Diagnosing errors in Db2 Big SQL (LOG_ENTRY function)

When an SQL statement fails in Db2 Big SQL, the returned error message might contain a log_entry_identifier that you can use to find more details about the error from a specific diagnostic log file. It is sometimes challenging to find and use the log files to understand what is causing the underlying error.

A table function (SYSHADOOP.LOG_ENTRY) is available to help you retrieve Db2 Big SQL-specific diagnostic log information. We will use examples to illustrate the types of error that might occur, and how you can use the LOG_ENTRY function to more easily troubleshoot these errors.

Log files and log-entry-identifier

Several different log files are used for problem determination in Db2 Big SQL. In most configurations, the log files are located in one of two places:

• $BIGSQL_DIST_VAR/logs
• $BIGSQL_DIST_VAR/diag

The $BIGSQL_DIST_VAR variable is generally set to /var/ibm/bigsql.

It is important to remember that these log files are “rotated”, which means that when a log file reaches a defined size it is renamed and a new log file is created. This log file size limit is configurable and can be changed in the $BIGSQL_HOME/bigsql/conf/*log4j* configuration files.

A log-entry-identifier is a three-part name of the form LLL-NNN-XXYYZZ.

For example, given a cluster that has the following nodes:

      ======================
      0  host1.foobar.com  0
      1  host2.foobar.com  0
      2  host3.foobar.com  0
      ======================

The first field in each record represents the node number and the second field represents the host name.

• LLL identifies the diagnostic log file that contains the error:
  • BSL: The Db2 Big SQL log file, bigsql.log, is where most of the diagnostic information for Db2 Big SQL runtime execution is written ($BIGSQL_DIST_VAR/logs/bigsql.log).
  • SCL: The scheduler log file, bigsql-sched.log, is where the diagnostic information related to the Db2 Big SQL scheduler is written. It exists only on the node on which the scheduler is running, which is normally the Db2 Big SQL head node ($BIGSQL_DIST_VAR/logs/bigsql-sched.log).
  • DDL or DB2: The diagnostic information for these identifiers can be found in the $BIGSQL_DIST_VAR/diag directory in most configurations, but the location is configurable.
• NNN identifies the Db2 Big SQL cluster node from which the error originated. To map this value to the name of the host where the log file resides, look at the /home/bigsql/sqllib/db2nodes.cfg file.
• XXXYYYZZZ represents a unique message ID that is used to search the log file.

If the LLL portion of the log-entry-identifier is 'DDL' or 'DB2', the error was detected by the Db2 Big SQL main engine processor. In this case, the SYSHADOOP.LOG_ENTRY function cannot be used to get additional information about the error.

You can retrieve additional information about the following Db2 Big SQL error messages from the Db2 Big SQL log files:

• SQL5104
• SQL5105
• SQL5111
• SQL5197
• SQL5198
• SQL20557

Examples

• Example 1: If for some reason, a file that is associated with a table has been removed from HDFS, you might see the following error when running a SELECT statement against that table.  Using the LOG_ENTRY function, we can see that the error is caused by a missing file. Note: Truncation warnings have been removed.

• Example 2:  If for some reason, Hive is not active on the cluster, you might see the following error when running a SELECT statement.  Using the LOG_ENTRY function, we can see that the error is caused by an inability to access the Hive metastore. Note: Truncation warnings have been removed.

• Example 3:  If for some reason, the Db2 Big SQL scheduler has stopped running, an error is returned when an SQL statement is run.

   db2 "select * from t1"

   SQL5197N  The statement failed because of a communication error with a Big SQL component. 
   Big SQL component name: "SCHEDULER". 
   Reason code: "1". Log entry identifier: 
   "DB2".  
   SQLSTATE=57066