PH10156: SQL0901N WITH UNEXPECTED AVGBUILDTUPLESIZE=0 IN JOIN QUERY

APAR status

• Closed as fixed if next.

Error description

• Big SQL may produce 901 with unexpected avgBuildTupleSize=0 when
  following conditions are satisfied:
  1. The query has one or more complex joins.
  2. There are join columns of type
  char/varchar/graphic/vargraphic with length < 4.
  2019-02-25-09.07.50.577537-420 E5531828E2888         LEVEL:
  Info (Origin)
  PID     : 30673                TID : 140330682803968 PROC :
  db2sysc 0
  INSTANCE: bigsql               NODE : 000            DB   :
  BIGSQL
  APPHDL  : 0-23661              APPID: *N0.bigsql.190325160632
  AUTHID  : BIGSQL               HOSTNAME: bigsqlheadhost
  EDUID   : 240                  EDUNAME: db2agent (BIGSQL) 0
  FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
  MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
            "unexpected error but state is OK"
  DATA #1 : String, 62 bytes
  An unexpected error was detected during statement compilation.
  DATA #2 : Boolean, 1 bytes
  true
  DATA #3 : Boolean, 1 bytes
  false
  DATA #4 : Boolean, 1 bytes
  true
  DATA #5 : Boolean, 1 bytes
  false
  DATA #6 : Hex integer, 4 bytes
  0x00000000
  DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 30
   sqlerrmc: unexpected avgBuildTupleSize=0
   sqlerrp : SQLNG060
   sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
  0x00000000
             (4) 0x00004CD0      (5) 0xFFFFF762      (6)
  0x00000000
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
  
             (7)      (8)      (9)      (10)        (11)
   sqlstate:
  DATA #8 : Hex integer, 4 bytes
  0x00000040
  DATA #8 : Hex integer, 4 bytes
  0x00000040
  DATA #9 : String with size, 1000 bytes
  SELECT * FROM ..(snip)...
  DATA #10: String, 435 bytes
  DATA #10: String, 509 bytes
  Compiler error stack for rc = -2144272209:
  sqlnn_cmpl[415]
  sqlng_main[3444]
  sqlng_main[2029]
  sqlng_build_thread[894]
  sqlngProcessLolepop[555]
  sqlng_process_return_op[1686]
  sqlngProcessLolepop[555]
  sqlng_process_pipe_op[887]
  sqlngProcessLolepop[555]
  sqlng_process_mate_op[1477]
  sqlngProcessLolepop[555]
  sqlng_process_pipe_op[887]
  sqlngProcessLolepop[555]
  sqlng_process_mate_op[1477]
  sqlngProcessLolepop[555]
  sqlng_process_TQ_op[841]
  sqlng_build_thread[960]
  sqlngProcessLolepop[555]
  sqlng_process_hsjn_op[2206]
  

Local fix

• Update the optimizer statistics on the tables being joined to
  adjust the AVGCOLLEN to a value greater than 4:
  
  update sysstat.columns set AVGCOLLEN = 5 where tabschema =
  <joinschema> and tabname = <jointable> and colname =
  <joincolumn>
  

Problem summary

• Please see 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