PH12778: LARGE INLIST IN QUERY FAILS WITH SQLCODE -901 CONTINUATION IS NOT SUPPORTED

APAR status

• Closed as fixed if next.

Error description

• Running a SELECT statement with a WHERE predicate that includes
  an IN list consisting of a large number of values (over 50,000
  for example) can result in  the query failing on one or more
  worker nodes with sqlcode -901 reporting the error
  "Continuation is not supported".
  .
  An example of such a query is:
  .
  SELECT DISTINCT P_ID FROM TABLE
   WHERE P_ID IN (1, ... lots of other elements ..., 57000)
  .
  The db2diag.log on the worker node shows the creation of an
  FODC_AppErr directory along with messages like the following:
  .
  2019-05-28-09.57.41.047206-240 I35046E4854           LEVEL:
  Severe
  PID     : 1533860              TID : 140562011252480 PROC :
  db2sysc 3
  INSTANCE: bigsql               NODE : 003            DB   :
  BIGSQL
  APPHDL  : 0-19743              APPID:
  10.60.104.223.49690.190528135739
  AUTHID  : BIGSQL               HOSTNAME: WORKER
  EDUID   : 52621                EDUNAME: db2agnts (BIGSQL) 3
  FUNCTION: DB2 UDB, routine_infrastructure,
  sqerCommMgr::continueWriteRecord, probe:885
  MESSAGE : ZRC=0x82240001=-2111569919=SQLER_INTERNAL_ERROR
            "unexpected system error"
            DIA8532C An internal processing error has occurred.
  DATA #1 : String, 29 bytes
  Continuation is not supported
  DATA #2 : sqerCommMgr, PD_TYPE_SQER_COMM_MGR, 424 bytes
     x0088    mTransportMetrics
        Measured Metrics
        x0038    mNumTransportSends            0
        x0040    mNumTransportRecvs            0
        x0028    mBytesDataSent                0
        x0030    mBytesDataReceived            0
        x0008    mSendWaitTicks                0.000000000
        x0010    mRecvWaitTicks                0.000000000
        x0018    mRecvWaitTicksHwm             0.000000000
        x0020    mRecvWaitTicksLwm             4154504685.55161500
        Computed Metrics
        Average milliseconds per record (recv): 0
        Average bytes per buffer (recv): 0
        Average bytes per buffer (send): 0
        Recv throughput (bytes/second): 0
        Recv throughput (recv/second): 0
     x0020    mConversationId               1729
     x0028    mConversationIdTracker        1729
     x00D0    mCommMgrCfg
        x0000    dumpRecords                   false
        x0008    bufferSize                    2162416
        x0010    maxBuffersPerOperation        20
        x0018    maxBuffersPerOperationInFmp   16
        x0020    maxRowsPerOutboundCommBuffer  32767
     x0174    mCommFuncId                   x0
     x0178    mCommProbe                    0
     x0180    mRecordFlowControl
  140000000000000080e2a54008000000
     x0198    mOperationContext
     x0198    mOperationContext.ctxType     1
     x019C    mOperationContext.ctx.et.tablespaceID-12
     x019E    mOperationContext.ctx.et.tableID-32685
  DATA #3 : sqerRecord, PD_TYPE_SQER_RECORD, 40 bytes
     x0000    mRecordHeader
     x0000    mRecordHeader.recordLength    4294967295
     x0004    mRecordHeader.recordType      1
              - SQLER_RECORD_TYPE_REQUEST
     x0005    mRecordHeader.recordFlags     4
              - SQLER_RECORD_FLAG_FIRST_SPLIT
     x0006    mRecordHeader.recordStreamId  18446744073709551615
     x0008    mChunk
        x0000    mChunkStart                   0x9589f00f0
        x0008    mChunkPos                     0x958bfffe0
        x0010    mChunkLength                  2162416
        x0014    mBytesRemaining               0
     x0020    mRecordStartPtr               0x9589f0118
  CALLSTCK: (Static functions may not be resolved correctly, as
  they are resolved to the nearest symbol)
    [0] 0x00007FFFEE82E6EF
  _ZN11sqerCommMgr19continueWriteRecordERP10sqerRecordS2_ + 0xFF
    [1] 0x00007FFFEE875895
  _Z36sqlerExtTableGenerateOpenScanRequestP8sqeAgentP18sqerExtTabl
  eHandleP17sqerExtTableSerDe + 0xBE5
    [2] 0x00007FFFEE874B72
  _Z32sqlerExtTableSendOpenScanRequestP8sqeAgentP18sqerExtTableHan
  dleP17sqerExtTableSerDe + 0x62
    [3] 0x00007FFFEE8740E6
  _Z21sqlerExtTableOpenScanP8sqeAgentP21SQLER_EXT_TABLE_PARMSP37SQ
  LER_EXT_TABLE_DESERIALIZATION_PARMSP18sqerExtTableHandleP17sqer
  + 0x346
    [4] 0x00007FFFF1156203 _Z20sqlriHadoopTableScanP8sqlrr_cb +
  0xAF3
    [5] 0x00007FFFF0F11EF8
  _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm + 0x4F800f0wing:0
    [6] 0x00007FFFF094E108
  _Z21sqlrr_subagent_routerP8sqeAgentP12SQLE_DB2RA_T + 0xD88bleHan
    [7] 0x00007FFFEE4E290D /home/bigsql/sqllib/lib64/libdb2e.so.1
  + 0x504B90D07FFFEE8740E6
    [8] 0x00007FFFEE4E11E0 _Z21sqleProcessSubRequestP8sqeAgent +
  0x3E0XT_TABLE_DESERIALIZATION_PARMSP18sqerExtTableHandleP17sqer
    [9] 0x00007FFFEE5155A7 _ZN8sqeAgent6RunEDUEv + 0x5D7
    [10] 0x00007FFFF1D28A66 _ZN9sqzEDUObj9EDUDriverEv + 0x116 +
    [11] 0x00007FFFF02F7708 sqloEDUEntry + 0x578
    [12] 0x00007FFFF7BC6DD5 /lib64/libpthread.so.0 + 0x7DD5
    [13] 0x00007FFFE7F5DEAD clone + 0x6D
  .
  2019-05-28-09.57.41.455299-240 I45023E1073           LEVEL:
  Severe
  PID     : 1533860              TID : 140562011252480 PROC :
  db2sysc 3
  INSTANCE: bigsql               NODE : 003            DB   :
  BIGSQL
  APPHDL  : 0-19743              APPID:
  10.60.104.223.49690.190528135739
  AUTHID  : BIGSQL               HOSTNAME: WORKER
  EDUID   : 52621                EDUNAME: db2agnts (BIGSQL) 3
  FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
  probe:250
  MESSAGE : ZRC=0x82240001=-2111569919=SQLER_INTERNAL_ERROR
            "unexpected system error"
            DIA8532C An internal processing error has occurred.
  DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 29
   sqlerrmc: Continuation is not supported
   sqlerrp : SQLER23B
   sqlerrd : (1) 0x82240001      (2) 0x00000001      (3)
  0x00000000
             (4) 0x00000000      (5) 0xFFFFFD3A      (6)
  0x00000003
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
             (7)      (8)      (9)      (10)        (11)
   sqlstate:
  

Local fix

• There are several ways the issue can be avoided:
  .
  1. Add an Optimizer hint to the query to prevent the predicates
  from being pushed down to the Big SQL reader.  This is an
  excellent work around when the columns used in the predicate are
  NOT partitioning columns.
  .
  For example:
  SELECT DISTINCT P_ID FROM TABLE
  WHERE P_ID IN (1, ... lots of other elements ..., 57000)
  /*<OPTGUIDELINES><REGISTRY><OPTION NAME="DB2COMPOPT"
  VALUE="SET_TO_JOIN,ENABLE_GEN_PRED_SUBSTRING,
  DISABLE_PRED_PD_JAVA_READER"/></REGISTRY></OPTGUIDELINES>*/
  .
  The Optimizer hint value should include all the default
  DB2COMPOPT settings (ie. what is shown in db2set for that
  variable) in addition to what is specified above.
  .
  2.  Rewrite the query to break the statement into smaller
  INLISTS when they are extremely long.
  .
  For example:
  SELECT DISTINCT P_ID FROM TABLE WHERE P_ID IN (1,2,3,...20000)
     UNION ALL
  SELECT DISTINCT P_ID FROM TABLE WHERE P_ID IN (20001, ...,
  40000)
     UNION ALL
  ...
  .
  3. Create a separate table for the INLIST items (global temp
  table)and then use a subquery in the INLIST.
  .
  For example:
  CREATE GLOBAL TEMPORAY TABLE TEMP_INLIST (TEMP_ID INTEGER);
  .
  INSERT INTO TEMP_INLIST VALUES (1,2, ... all in list elements
  ... 57000);
  .
  SELECT DISTINCT P_ID FROM TABLE
  WHERE P_ID IN (SELECT TEMP_ID FROM TEMP_INLIST);
  

Problem summary

• Please see the problem description.
  

Problem conclusion

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels