APAR status
Closed as program error.
Error description
Optimizer chooses sequential scan for a query in 11.70 but chooses INDEX PATH in 9.40 and 11.50 QUERY: (OPTIMIZATION TIMESTAMP: 06-01-2012 18:49:12) ------ select customer.SLMN_NUM, inv.CHARGE_TO, inv.CUST_NUM, customer.CUST_NAME, inv.TRANS_DATE, inv.TRANS_TYPE, case when inv_shp_l.ITEM_NUM like '%FF8351%' then 3 else 5 end , inv_shp_l.COMMABLE_AMT, inv_shp_l.TOT_NET_AMT, inv_shp_l.WHSE_CODE, inv_shp_l.ITEM_NUM, item_.DESC_1, item_.SA_ITEM, inv_shp.ORDER_TYPE_CODE, inv.TRANS_NUM, inv_shp_l.TOT_NET_AMT - inv_shp_l.COMMABLE_AMT, case when inv_shp.ORDER_TYPE_CODE='CONV' then .02 when inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE in ('VHDI', 'WBDI', 'ACDI', 'CHINA', 'INDIA') then .03 when inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE not in ('VHDI', 'WBDI', 'ACDI', 'CHINA', 'INDIA') then .05 else 0 end , inv_shp_l.COMMABLE_AMT*case when (inv_shp.ORDER_TYPE_CODE='CONV') then .02 when (inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE in ('VHDI', 'WBDI', 'ACDI', 'CHINA', 'INDIA')) then .03 when (inv_shp.ORDER_TYPE_CODE<>'CONV' and inv_shp_l.WHSE_CODE not in ('VHDI', 'WBDI', 'ACDI', 'CHINA', 'INDIA')) then .05 else 0 end , inv.ORG_CODE, inv_shp_l.SLMN_NUM, case when inv_shp_l.ITEM_NUM like '%FF8351%' then 3 else 5 end /100, inv_shp.CUST_PO_NUM from ((CUSTOMER customer LEFT OUTER JOIN INV inv on customer.CUST_NUM=inv.CUST_NUM) LEFT OUTER JOIN INV_SHP inv_shp on inv.INV_ID=inv_shp.INV_ID) LEFT OUTER JOIN (ITEM item_ inner join INV_SHP_L inv_shp_l on item_.ITEM_NUM=inv_shp_l.ITEM_NUM) on inv_shp.INV_SHP_ID=inv_shp_l.INV_SHP_ID where inv.TRANS_DATE>=DATETIME( 2012-04-26 00:00:00.00000 ) YEAR TO FRACTION(5) and inv.TRANS_DATE<=DATETIME( 2012-05-23 23:59:59.99900 ) YEAR TO FRACTION(5) and inv_shp_l.SLMN_NUM='C001' and item_.ITEM_GROUP in ('SENS', 'COSH') order by 20 asc Estimated Cost: 337408832 Estimated # of Rows Returned: 21 Temporary Files Required For: Order By 1) informix.item_: SEQUENTIAL SCAN Filters: informix.item_.item_group IN ('SENS' , 'COSH' ) 2) informix.inv_shp_l: INDEX PATH Filters: informix.inv_shp_l.slmn_num = 'C001' (1) Index Name: tecsys.inv_shp_l2 Index Keys: item_num whse_code (Serial, fragments: ALL) Lower Index Filter: informix.item_.item_num = informix.inv_shp_l.item_num ON-Filters:informix.item_.item_num = informix.inv_shp_l.item_num NESTED LOOP JOIN 3) informix.inv: INDEX PATH (1) Index Name: tecsys.inv6 Index Keys: trans_date (Serial, fragments: ALL) Lower Index Filter: informix.inv.trans_date >= 2012-04-26 Upper Index Filter: informix.inv.trans_date <= 2012-05-23 4) informix.customer: INDEX PATH (1) Index Name: tecsys.customer Index Keys: cust_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.cust_num = informix.inv.cust_num ON-Filters:informix.customer.cust_num = informix.inv.cust_num NESTED LOOP JOIN 5) informix.inv_shp: INDEX PATH (1) Index Name: tecsys.inv_shp Index Keys: inv_shp_id (Serial, fragments: ALL) Lower Index Filter: informix.inv_shp.inv_shp_id = informix.inv_shp_l.inv_shp_id ON-Filters:informix.inv.inv_id = informix.inv_shp.inv_id NESTED LOOP JOIN(LEFT OUTER JOIN) ON-Filters:informix.inv_shp.inv_shp_id = informix.inv_shp_l.inv_shp_id NESTED LOOP JOIN
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All users of ANSI queries * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to IDS-11.70.xC6 * ****************************************************************
Problem conclusion
Problem Fixed In IDS-11.70.xC6
Temporary fix
Comments
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp http://www.ibm.com/support/fixcentral
APAR Information
APAR number
IC83944
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
B70
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2012-06-05
Closed date
2012-10-30
Last modified date
2024-09-24
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":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B70","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
24 September 2024