PI74863: SQL0407N ERROR WHEN CALLING IDAX.KMEANS PROCEDURE

APAR status

• Closed as program error.

Error description

• BigSQL may give a SQL0407N error message when you call
  IDAX.KMEANS procedure for cluster analysis.
  
  Error message:
  call idax.kmeans('intable=t1, id=id, model=t1_m1,
  outtable=t1_out')
  SQL0407N  Assignment of a NULL value to a NOT NULL column
  "TBSPACEID=4,
  TABLEID=41, COLNO=1" is not allowed.  SQLSTATE=23502
  
  When the issue happens, the diag.log would show the following:
  2017-01-12-07.53.44.277304-480 I4195E998             LEVEL:
  Error
  PID     : 24523 TID : 139655819290368 PROC : db2sysc 0
  INSTANCE: bigsql               NODE : 000            DB   :
  BIGSQL
  APPHDL  : 0-36554              APPID: *N0.bigsql.170112143510
  AUTHID  : BIGSQL               HOSTNAME: zoo.***.***.com
  EDUID   : 97                   EDUNAME: db2agent (BIGSQL) 0
  FUNCTION: DB2 UDB, relation data serv, sqlrr_execute_immed,
  probe:650
  MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
            "SQLCA has already been built"
  DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -407   sqlerrml: 32
   sqlerrmc: TBSPACEID=4, TABLEID=41, COLNO=1
   sqlerrp : sqldFmt1
   sqlerrd : (1) 0x8016001C      (2) 0x0000001C      (3)
  0x00000000
             (4) 0x00000000      (5) 0xFFFFFB22      (6)
  0x00000000
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
             (7)      (8)      (9)      (10)        (11)
   sqlstate:
  
  
  Issue may be reproducible through the following steps:
  
  1. create hadoop table t1(id int,c2 varchar(10),c3
  varchar(10),c4 int,c5 int) stored AS parquetfile
  DB20000I  The SQL command completed successfully.
  
  2. nsert into t1(id) with temp (id) as (values (1) union all
  select id+1 from temp where id < 5) select * from temp
  DB20000I  The SQL command completed successfully.
  
  3. call idax.kmeans('intable=t1, id=id, model=t1_m1,
  outtable=t1_out')
  SQL0407N  Assignment of a NULL value to a NOT NULL column
  "TBSPACEID=4,
  TABLEID=41, COLNO=1" is not allowed.  SQLSTATE=23502
  

Local fix

• Remove all the NULL values from source table
  

Problem summary

• See error description.
  

Problem conclusion

• The problem is fixed in Version 4.3 and later fix packs.
  

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:

Modules/Macros

• n/a
  

Fix information

• Fixed component name

  INFO BIGINSIGHT

• Fixed component ID

  5725C0900

Applicable component levels

• R430 PSY

     UP