LOG_ENTRY table function

When a Db2® Big SQL statement fails, the returned SQL message might contain a log entry identifier that you can use to find more details about the error in the bigsql.log file. By using the SYSHADOOP.LOG_ENTRY table function, you can get log file information for a particular log entry identifier.

Syntax

The schema is SYSHADOOP.

Table 1. Information returned by the LOG_ENTRY function
Column name	Data type	Description
LINE	VARCHAR(8192)	Contains log file information for a particular log entry identifier.

Description

identifier

Specifies the log entry identifier that was reported in an error message. A log entry identifier is a three-part name. For more information about log entry identifiers, see Logs and their locations.

lines-before

Specifies the number of lines to be returned before the identifier. The root cause for an error condition might precede the specified identifier. The default is 10.

lines-after

Specifies the number of lines to be returned after the identifier. The default is 25.

Usage

If the contents of the log file have rolled over since the error occurred, an identifier might no longer be available and no data is returned.

Example

To create a table named varchar_part partitioned on a VARCHAR column, run the following command example:

create datalake table varchar_part (c1 int) partitioned by (part3 varchar(4)) tblproperties ('external.table.purge'='true') location 'db2remote://odfdefault//default/varchar_part'

resulting in the following return:

DB20000I The SQL command completed successfully.

Now, let's INSERT a not acceptable value that is a zero-length STRING:

insert into varchar_part values (1,'')

resulting in the following return:

DB21034E The command was processed as an SQL statement because it was not a 
valid Command Line Processor command. During SQL processing it returned:
SQL5104N The statement failed because the data in HDFS cannot be mapped to 
the Hadoop table definition. Hadoop table name: "test1234.varchar_part". 
Column index: "0". Log entry identifier: "[BSL-1-8e2cf3ca]". Reason: 
"0-length partitioning column". SQLSTATE=530AC

You can use the BSL-1-8e2cf3ca log entry identifier to get more details about the error from the bigsql.log file by running the following SELECT statement example:

select cast (line as varchar(200)) from table (syshadoop.log_entry('BSL-1-8e2cf3ca')) where line like '%5104%'

resulting in the following return:

1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Exception Label: DFS_ZERO_LENGTH_PARTITION_VALUE sqlcode= -5104(Attempt to start partition with a 0-length value)

1 record(s) selected.