Fixes are available
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 number
IT31580
Reported component name
DB2 FOR LUW
Reported component ID
DB2FORLUW
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-01-20
Closed date
2021-03-31
Last modified date
2021-03-31
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
RB10 PSN
UP
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
04 May 2022