Bad Data Symptoms for IBM Db2 Big SQL

Symptom

Data On Disk is INTEGER and yet declared as CHAR(8) or VARCHAR(8) or DECIMAL(8,2)

Consider the following external tables with the same LOCATION defined:

Control Table "A completely valid EXTERNAL table of 4 INTEGERS":

drop table if exists jj_control_ints;

create external hadoop table jj_control_ints (
col1 int,
col2 int,
col3 int,
col4 int)
LOCATION '/tmp/jj_control_ints'
STORED AS ORC;
insert into jj_control_ints values(1,1,1,1);
insert into jj_control_ints values(2,2,2,2);
insert into jj_control_ints values(3,3,3,3);
insert into jj_control_ints values(4,4,4,4);

(It is worth noting an "oversight" in Big SQL. If  /tmp/jj_control_ints does NOT exist at the level of HDFS, it is CREATED which is wrong .. consider permissions).

Bad Data Example Table 1 "A table which refers to an INTEGER column (col2) as a CHAR(8)"

drop table if exists jj_base_char;

create external hadoop table jj_base_char (
col1 int,
col2 char(8),
col3 int,
col4 int)
LOCATION '/tmp/jj_control_ints'
STORED AS ORC;

Running the above SQL in and selecting from the two tables:

connect to bigsql

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.9.0
 SQL authorization ID   = BIGSQL
 Local database alias   = BIGSQL

drop table if exists jj_control_ints
DB20000I  The SQL command completed successfully.

create external hadoop table jj_control_ints ( col1 int, col2 int, col3 int, col4 int) LOCATION '/tmp/jj_control_ints' STORED AS ORC
DB20000I  The SQL command completed successfully.

insert into jj_control_ints values(1,1,1,1)
DB20000I  The SQL command completed successfully.

insert into jj_control_ints values(2,2,2,2)
DB20000I  The SQL command completed successfully.

insert into jj_control_ints values(3,3,3,3)
DB20000I  The SQL command completed successfully.

insert into jj_control_ints values(4,4,4,4)
DB20000I  The SQL command completed successfully.

drop table if exists jj_base_char
DB20000I  The SQL command completed successfully.

create external hadoop table jj_base_char ( col1 int, col2 char(8), col3 int, col4 int) LOCATION '/tmp/jj_control_ints' STORED AS ORC
DB20000I  The SQL command completed successfully.

select 'jj_control_ints', * from jj_control_ints

1               COL1        COL2        COL3        COL4       
--------------- ----------- ----------- ----------- -----------
jj_control_ints           4           4           4           4
jj_control_ints           1           1           1           1
jj_control_ints           2           2           2           2
jj_control_ints           3           3           3           3

  4 record(s) selected.

select 'jj_base_char', * from jj_base_char

1            COL1        COL2     COL3        COL4       
------------ ----------- -------- ----------- -----------
SQL5105N  The statement failed because a Big SQL component encountered an
error. Component receiving the error: "BigSQL IO". Component returning the
error: "UNKNOWN". Log entry identifier: "[BSL-4-413537415]".  SQLSTATE=58040

Okay, so we see a FAILURE in Big SQL when we attempt to select the INTEGER column via a CHAR column:

SQL5105N  The statement failed because a Big SQL component encountered an error.

Component receiving the error: "BigSQL IO".

Component returning the error: "UNKNOWN". Log entry identifier: "[BSL-4-413537415]".   SQLSTATE=58040

1. db2diag -H 10m shows NOTHING on the Big SQL Head Node

2. db2diag -H 10m from node 4 shows THE FOLLOWING failure (N.B. "INFO") -> BSL-4-413537415]:

2018-06-18-15.36.33.685765+060 I310675E1099          LEVEL: Info
PID     : 14342                TID : 139738308667136 PROC : db2sysc 4
INSTANCE: bigsql               NODE : 004            DB   : BIGSQL
APPHDL  : 0-62077              APPID: *N0.bigsql.180618143634
AUTHID  : BIGSQL               HOSTNAME: jj4250-slave-4.fyre.ibm.com
EDUID   : 106                  EDUNAME: db2agnts (BIGSQL) 4
FUNCTION: DB2 UDB, routine_infrastructure, sqerFmpClient::commonPostRequestProcessing, probe:1364
MESSAGE : ZRC=0x8024006D=-2145124243=SQLER_CA_BUILT
          "SQLCA has already been built"
DATA #1 : String, 32 bytes
Remote SQLCA copied to RDS SQLCA
DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -5105   sqlerrml: 35
 sqlerrmc: BigSQL IO UNKNOWN [BSL-4-413537415]
 sqlerrp : SQLER000
 sqlerrd : (1) 0x822401EF      (2) 0x00000000      (3) 0x00000000
           (4) 0x00000000      (5) 0x00000000      (6) 0x00000004
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)    
           (7)      (8)      (9)      (10)        (11)     
 sqlstate: 58040

3. The bigsql.log on node 4 shows the following (a fairly big clue):

2018-06-18 15:36:33,684 ERROR com.ibm.biginsights.bigsql.dfsrw.orc.DfsOrcReader [DfsOrcReader #0 for S:61.1001.1.0.0.-3459] : Reader at node: 4 Scan ID: S:61.1001.1.0.0.-3459 Table: bigsql.jj_base_char Spark: false VORC: true thread DfsOrcReader #0 for S:61.1001.1.0.0.-3459 spawned by Master S:61.1001.1.0.0.-3459 exited with java.lang.ClassCastException
2018-06-18 15:36:33,685 ERROR com.ibm.biginsights.bigsql.dfsrw.reader.DfsBaseReader [Master S:61.1001.1.0.0.-3459] : [BSL-4-413537415] Exception raised by Reader at node: 4 Scan ID: S:61.1001.1.0.0.-3459 Table: bigsql.jj_base_char Spark: false VORC: true
Exception Label: UNMAPPED(java.lang.ClassCastException: org.apache.hadoop.hive.ql.exec.vector.LongColumnVector incompatible with org.apache.hadoop.hive.ql.exec.vector.BytesColumnVector)
java.lang.ClassCastException: org.apache.hadoop.hive.ql.exec.vector.LongColumnVector incompatible with org.apache.hadoop.hive.ql.exec.vector.BytesColumnVector
        at com.ibm.biginsights.bigsql.dfsrw.orc.DfsOrcRowBufferSerializer.serializeBatch(DfsOrcRowBufferSerializer.java:228)
        at com.ibm.biginsights.bigsql.dfsrw.orc.DfsOrcReader$ReaderWorker.run(DfsOrcReader.java:276)
        at java.lang.Thread.run(Thread.java:785)

What about an ANALYZE?

db2 => analyze table jj_base_char compute statistics for columns col1, col2, col3, col4;
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL5105N  The statement failed because a Big SQL component encountered an
error. Component receiving the error: "". Component returning the error:
"HIVE". Log entry identifier: "[BSL-0-4136945bf]".  SQLSTATE=58040

1. db2diag shows NOTHING on the Head Node

2. bigsql.log on Node 0 shows:

2018-06-18 16:00:23,615 WARN com.ibm.biginsights.catalog.translator.hive.HiveExceptionTranslator [Thread-6926] : [BSL-0-4136945bf]: The statement failed because a Big SQL component encountered an error. Component receiving the error: "BigSQL IO". Component returning the error: "UNKNOWN". Log entry identifier: "[BSL-3-4136938e5]".. SQLCODE=-5105, SQLSTATE=58040, DRIVER=4.22.29
com.ibm.db2.jcc.am.SqlException: The statement failed because a Big SQL component encountered an error. Component receiving the error: "BigSQL IO". Component returning the error: "UNKNOWN". Log entry identifier: "[BSL-3-4136938e5]".. SQLCODE=-5105, SQLSTATE=58040, DRIVER=4.22.29
        at com.ibm.db2.jcc.am.ld.a(ld.java:815)
        at com.ibm.db2.jcc.am.ld.a(ld.java:66)
        at com.ibm.db2.jcc.am.ld.a(ld.java:140)
        at com.ibm.db2.jcc.am.ResultSet.completeSqlca(ResultSet.java:4246)
        at com.ibm.db2.jcc.am.ResultSet.earlyCloseComplete(ResultSet.java:4228)
        at com.ibm.db2.jcc.uw.i.a(i.java:187)
        at com.ibm.db2.jcc.uw.i.p(i.java:139)
        at com.ibm.db2.jcc.t4.bb.k(bb.java:261)
        at com.ibm.db2.jcc.t4.bb.d(bb.java:55)
        at com.ibm.db2.jcc.t4.p.c(p.java:44)
        at com.ibm.db2.jcc.t4.vb.j(vb.java:157)
        at com.ibm.db2.jcc.am.up.nb(up.java:2234)
        at com.ibm.db2.jcc.am.vp.a(vp.java:4637)
        at com.ibm.db2.jcc.am.vp.b(vp.java:4151)
        at com.ibm.db2.jcc.am.vp.pc(vp.java:2871)
        at com.ibm.db2.jcc.am.vp.execute(vp.java:2846)
        at com.ibm.biginsights.biga.udf.stats.AnalyzeCatalog.execute(AnalyzeCatalog.java:705)
        at com.ibm.biginsights.biga.udf.stats.AnalyzeCatalog.execute(AnalyzeCatalog.java:693)
        at com.ibm.biginsights.biga.udf.stats.AnalyzeCatalog.execute(AnalyzeCatalog.java:676)
        at com.ibm.biginsights.biga.udf.stats.AnalyzeDB2.doAnalyze(AnalyzeDB2.java:71)
        at com.ibm.biginsights.biga.udf.stats.AnalyzeTool.analyze(AnalyzeTool.java:135)
        at com.ibm.biginsights.biga.udf.stats.AnalyzeTool.analyze(AnalyzeTool.java:69)
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.performAnalyze(BIGSQL_DDL.java:1410)
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.doAnalyzeStatement(BIGSQL_DDL.java:1321)
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.processDDL(BIGSQL_DDL.java:234)

3. bigsql.log on Node 3 shows - another big clue

2018-06-18 16:00:20,324 ERROR com.ibm.biginsights.bigsql.dfsrw.reader.DfsHiveReader [DfsHiveReader #1 for S:143.1001.1.0.0.-3295] : Reader at node: 3 Scan ID: S:143.1001.1.0.0.-3295 Table: bigsql.jj_base_char Spark: false VORC: false thread DfsHiveReader #1 for S:143.1001.1.0.0.-3295 spawned by Master S:143.1001.1.0.0.-3295 exited with java.lang.ClassCastException
2018-06-18 16:00:20,325 ERROR com.ibm.biginsights.bigsql.dfsrw.reader.DfsHiveReader [DfsHiveReader #0 for S:143.1001.1.0.0.-3295] : Reader at node: 3 Scan ID: S:143.1001.1.0.0.-3295 Table: bigsql.jj_base_char Spark: false VORC: false thread DfsHiveReader #0 for S:143.1001.1.0.0.-3295 spawned by Master S:143.1001.1.0.0.-3295 exited with java.io.InterruptedIOException
2018-06-18 16:00:20,325 ERROR com.ibm.biginsights.bigsql.dfsrw.reader.DfsBaseReader [Master S:143.1001.1.0.0.-3295] : [BSL-3-4136938e5] Exception raised by Reader at node: 3 Scan ID: S:143.1001.1.0.0.-3295 Table: bigsql.jj_base_char Spark: false VORC: false
Exception Label: UNMAPPED(java.lang.ClassCastException: org.apache.hadoop.io.IntWritable incompatible with org.apache.hadoop.hive.serde2.io.HiveCharWritable)
java.lang.ClassCastException: org.apache.hadoop.io.IntWritable incompatible with org.apache.hadoop.hive.serde2.io.HiveCharWritable
        at org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableHiveCharObjectInspector.getPrimitiveJavaObject(WritableHiveCharObjectInspector.java:53)
        at org.apache.hadoop.hive.serde2.objectinspector.primitive.WritableHiveCharObjectInspector.getPrimitiveJavaObject(WritableHiveCharObjectInspector.java:30)
        at com.ibm.biginsights.bigsql.dfsrw.stats.AnalyzeDfsHiveRowBufferSerializer.analyze(AnalyzeDfsHiveRowBufferSerializer.java:139)
        at com.ibm.biginsights.bigsql.dfsrw.stats.AnalyzeDfsHiveRowBufferSerializer.serializeRow(AnalyzeDfsHiveRowBufferSerializer.java:111)
        at com.ibm.biginsights.bigsql.dfsrw.reader.DfsHiveReader.processData(DfsHiveReader.java:286)
        at com.ibm.biginsights.bigsql.dfsrw.reader.DfsHiveReader.access$400(DfsHiveReader.java:46)
        at com.ibm.biginsights.bigsql.dfsrw.reader.DfsHiveReader$ReaderRunnable.run(DfsHiveReader.java:374)
        at java.lang.Thread.run(Thread.java:785)

So, for a simple, non-partitioned table, this is (ALMOST) pretty obvious.