IT31580: FODC_APPERR SQL0901N "UNEXPECTED ERROR AT COLUMN.HPP" WHEN QUERYING FROM COLUMN-ORGANIZED TEMPORAL TABLES

APAR status

• Closed as program error.

Error description

• When querying from column-organized temporal tables, we can get
  -901 errors and FODC_AppErr files are created.
  Symptoms happen after an alter table alter column/add column
  statement on temporal tables.
  
  
  Example:
  
  2019-12-04-11.58.55.944359+060 I925102A899          LEVEL:
  Severe
  PID     : 63963732             TID : 44328          PROC :
  db2sysc 0
  INSTANCE: db2inst1             NODE : 000           DB   :
  SAMPLE
  APPHDL  : 0-13111              APPID:
  *LOCAL.db2inst1.191225135252
  AUTHID  : DB2INST1             HOSTNAME: TEST1VS
  EDUID   : 44328                EDUNAME: db2agent (SAMPLE) 0
  FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
  probe:250
  DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
  sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 34
  sqlerrmc: unexpected error at Column.hpp:343
  sqlerrp : SQLCT001
  sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 0x00000000
             (4) 0x00000000      (5) 0xFFFFFEA9      (6)
  0x00000000
  sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
             (7)      (8)      (9)      (10)        (11)
  sqlstate:
  

Local fix

• Avoid an "Alter table alter column" on column-organized temporal
  tables.
  
  If the problem occurs, the recommended fix for moving the data
  over to a new table with optimal dictionary creation is:
  
  1) Create identical tables
  
    # BAD_TAB is the temporal table and bad_tab_hist is the
  history table.
    db2 "create table NEW_TAB like BAD_TAB organize by column";
    db2 "create table BAD_TAB_HIST like BAD_TAB_HIST organize by
  column";
  
  2) Pre-build the dictionary with sampled data from source table,
  then insert data into the new tables. Aim for roughly 5 million
  rows.
    db2 "declare cursor1 CURSOR FOR select * from BAD_TAB
  tablesample bernoulli(1)";
      <= 1% rate if we have 500M rows in bad_tab, 100% if we're
  not close to 5M.
    db2 "load from cursor1 OF CURSOR MODIFIED BY
  CDEANALYZEFREQUENCY=100 replace resetDictionaryOnly into
  NEW_TAB";
    db2 "insert into NEW_TAB select * from BAD_TAB";
  
    # Do the same for history table
    db2 "declare cursor1 CURSOR FOR select * from BAD_TAB_HIST
  tablesample bernoulli(1)";
      <= 1% rate if we have 500M rows in bad_tab, 100% if we're
  not close to 5M.
    db2 "load from cursor1 OF CURSOR MODIFIED BY
  CDEANALYZEFREQUENCY=100 replace resetDictionaryOnly into
  NEW_TAB_HIST";
    db2 "insert into NEW_TAB_HIST select * from BAD_TAB_HIST";
  
  3) Convert new_tab into a temporal table
    db2 "alter table NEW_TAB add period SYSTEM_TIME(sys_start,
  sys_end) maintained by user";
  
  4) Link history table
    db2 "alter table NEW_TAB add user versioning use history table
  NEW_TAB_HIST";
  
  5) Test the new temporal table with some temporal queries.
  
  6) Drop old tables and rename new tables as desired.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1.4.6 or higher                            *
  ****************************************************************
  

Problem conclusion

• Upgrade to Db2 11.1.4.6 or higher
  

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

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels