APAR status
Closed as program error.
Error description
When adding a Foreign Key constraint from a child with interval fragmentation to a parent with interval fragmentation with all required supporting indexes in place the required time to complete will increase exponentially as opposed to linearly when the number of keys to validate increases. The following test Case demonstrates this: create table parent (parent_pk serial8) fragment by range (parent_pk) interval (50000) store in (dbspace2k_1, dbspace2k_2, dbspace2k_3, dbspace2k_4) partition parent_p0 values < 50000 in dbspace2k_3 extent size 512 next size 128 lock mode row statlevel fragment; create unique index parent_pk_idx on parent(parent_pk); alter table parent add constraint (primary key (parent_pk)); create table child (child_pk serial8, parent_pk int8) fragment by range (parent_pk) interval (50000) in (dbspace2k_1, dbspace2k_2, dbspace2k_3, dbspace2k_4) partition child_p0 values < 50000 in dbspace2k_4 extent size 512 next size 128 lock mode row statlevel fragment; create unique index child_pk_idx on child(child_pk) in rootdbs; create index child_parent_pk_idx on child(parent_pk); create procedure pop_parent() define i int; let i=1; while i < 250000 insert into parent values (i); let i = i + 1; end while end procedure; execute procedure pop_parent(); create procedure pop_child() define i int; let i=1; while i < 250000 insert into child values (i,i); let i = i + 1; end while end procedure; execute procedure pop_child(); select current from sysmaster:sysdual; alter table child add constraint (foreign key (parent_pk) references parent constraint child_parent_pk); select current from sysmaster:sysdual; The run time duration increases exponentially (VPCLASS 4, 2k BUFFERS 10000): Number of PARENT CHILD Rows non-PDQ PDQ 250,000 4.3S 1.4S 500,000 17.4S 5.4S 750,000 40.0S 11.0S 1,000,000 70.0S 19.0S 2,000,000 314.0S 90.0S
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC6. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC15 (when available) or * * 14.10.xC6 (when available). * ****************************************************************
Problem conclusion
Fixed in Informix Server 12.10.xC15 and 14.10.xC6.
Temporary fix
Comments
APAR Information
APAR number
IT35229
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-12-10
Closed date
2021-05-13
Last modified date
2021-05-13
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
INFORMIX SERVER
Fixed component ID
5725A3900
Applicable component levels
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"C10"}]
Document Information
Modified date:
14 May 2021