APAR status
Closed as program error.
Error description
See the sqexplain differences: 12.10.FC12 QUERY: (OPTIMIZATION TIMESTAMP: 06-11-2019 12:04:13) ------ select first 501 distinct client0_.client_id as col_0_0_, client0_.first_name as col_1_0_, client0_.middle_name as col_2_0_, client0_.last_name as col_3_0_, client0_.generation as col_4_0_, client0_.date_of_birth as col_5_0_, client0_.fbi_no as col_6_0_, client0_.ssn as col_7_0_, client0_.regis_marsh_num as col_8_0_, client0_.ins_no as col_9_0_ from client client0_ inner join alternate_name alternaten1_ on client0_.client_id=alternaten1_.client_id and alternaten1_.lcd_id=client0_.lcd_id left outer join client_address clientaddr2_ on client0_.client_id=clientaddr2_.client_id and clientaddr2_.lcd_id=client0_.lcd_id where 1=1 and (lower(client0_.last_name)=lower('Smith') or lower(alternaten1_.last_name)=lower('Smith')) and client0_.lcd_id= "0970" order by client0_.last_name, client0_.first_name, client0_.middle_name Estimated Cost: 1860568 Estimated # of Rows Returned: 44582 Temporary Files Required For: Order By 1) informix.client0_: INDEX PATH (SKIP SCAN) (1) Index Name: informix. 167_4447 Index Keys: lcd_id second_country_citizenship (Serial, fragments: ALL) Lower Index Filter: informix.client0_.lcd_id = '0970' 2) informix.alternaten1_: SEQUENTIAL SCAN Filters: Table Scan Filters: informix.alternaten1_.lcd_id = '0970' DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: informix.client0_.client_id = informix.alternaten1_.client_id Other Join Filters: (LOWER(informix.client0_.last_name ) = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' ) 3) informix.clientaddr2_: INDEX PATH (1) Index Name: informix. 531_4323 Index Keys: lcd_id client_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.client0_.client_id = informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id = informix.client0_.lcd_id ) NESTED LOOP JOIN 11.70.FC8: QUERY: (OPTIMIZATION TIMESTAMP: 06-11-2019 12:36:46) ------ select first 501 distinct client0_.client_id as col_0_0_, client0_.first_name as col_1_0_, client0_.middle_name as col_2_0_, client0_.last_name as col_3_0_, client0_.generation as col_4_0_, client0_.date_of_birth as col_5_0_, client0_.fbi_no as col_6_0_, client0_.ssn as col_7_0_, client0_.regis_marsh_num as col_8_0_, client0_.ins_no as col_9_0_ from client client0_ inner join alternate_name alternaten1_ on client0_.client_id=alternaten1_.client_id and alternaten1_.lcd_id=client0_.lcd_id left outer join client_address clientaddr2_ on client0_.client_id=clientaddr2_.client_id and clientaddr2_.lcd_id=client0_.lcd_id where 1=1 and (lower(client0_.last_name)=lower('Smith') or lower(alternaten1_.last_name)=lower('Smith')) and client0_.lcd_id= "0970" order by client0_.last_name, client0_.first_name, client0_.middle_name Estimated Cost: 2007810 Estimated # of Rows Returned: 47971 Temporary Files Required For: Order By 1) informix.client0_: INDEX PATH (1) Index Name: informix. 167_4447 Index Keys: lcd_id second_country_citizenship (Serial, fragments: ALL) Lower Index Filter: informix.client0_.lcd_id = '0970' 2) informix.alternaten1_: INDEX PATH (1) Index Name: informix.idx_altnnm_clntid Index Keys: lcd_id client_id (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.client0_.client_id = informix.alternaten1_.client_id AND informix.alternaten1_.lcd_id = informix.client0_.lcd_id ) Index Key Filters: (informix.alternaten1_.lcd_id = '0970' ) ON-Filters:(informix.client0_.client_id = informix.alternaten1_.client_id AND informix.alternaten1_.lcd_id = informix.client0_.lcd_id ) Other Join Filters: (LOWER(informix.client0_.last_name ) = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' ) NESTED LOOP JOIN 3) informix.clientaddr2_: INDEX PATH (1) Index Name: informix. 531_4323 Index Keys: lcd_id client_id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (informix.client0_.client_id = informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id = informix.client0_.lcd_id ) ON-Filters:(informix.client0_.client_id = informix.clientaddr2_.client_id AND informix.clientaddr2_.lcd_id = informix.client0_.lcd_id ) NESTED LOOP JOIN(LEFT OUTER JOIN) PostJoin-Filters:(LOWER(informix.client0_.last_name ) = 'smith' OR LOWER(informix.alternaten1_.last_name ) = 'smith' ) Onconfig relevant parameters: MAX_PDQPRIORITY 0 DS_MAX_QUERIES 100 DS_TOTAL_MEMORY 7000000 DS_MAX_SCANS 1048576 DS_NONPDQ_QUERY_MEM 1500000 DATASKIP off If the DS_TOTAL_MEMORY / DS_MAX_QUERIES quantum ~ 3700kb then optimizer chooses the INDEX SCAN
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC14 and 14.10.xC3. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to Informix Server 12.10.xC14 or 14.10.xC3. * ****************************************************************
Problem conclusion
Fixed in Informix Server 12.10.xC14 and 14.10.xC3.
Temporary fix
Comments
APAR Information
APAR number
IT30688
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
2019-10-23
Closed date
2020-02-26
Last modified date
2020-02-26
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
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"C10","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
26 February 2020