APAR status
Closed as program error.
Error description
Using a large SQL statement generated by WAS, the optimizer enters into an infinite loop in function opfindlnkeg(): afsig_handler + 0x108 [/usr2/support/products/IDS1150FC6/bin/oninit] _sigreturn [/usr/lib/pa20_64/libc.2] opfindlnkeg + 0x3a0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2e0 [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x2bc [/usr2/support/products/IDS1150FC6/bin/oninit] opfindlnkeg + 0x238 [/usr2/support/products/IDS1150FC6/bin/oninit] opderiveeqp + 0x198 [/usr2/support/products/IDS1150FC6/bin/oninit] opinit + 0x7e0 [/usr2/support/products/IDS1150FC6/bin/oninit] op_opinit + 0x50 [/usr2/support/products/IDS1150FC6/bin/oninit] sqoptim + 0x1838 [/usr2/support/products/IDS1150FC6/bin/oninit] bldstructs + 0xe0 [/usr2/support/products/IDS1150FC6/bin/oninit] sqcmd + 0x29c [/usr2/support/products/IDS1150FC6/bin/oninit] sq_cmnd + 0x1c4 [/usr2/support/products/IDS1150FC6/bin/oninit] sq_prepare + 0x40 [/usr2/support/products/IDS1150FC6/bin/oninit] sqmain + 0x890 [/usr2/support/products/IDS1150FC6/bin/oninit] The select is : SELECT {+ALL_ROWS} DISTINCT po.po, po.split, s.po_date, po.shipper_code, po.delivery_code, po.consignee_code, s.orig, s.dest, s.mode, s.req_ins, s.req_exw, li.delv_text, po.cust_text, li.pa_text, li.length, li.width, li.height, li.weight, li.volume, li.weightg, li.ln, li.ln_level, li.part_no, li.part_desc, CASE WHEN (s.ln='0') THEN c_po.type ELSE c_ln.type END type, li.qty_ord, li.qty_rec, li.uom, li.unit_val, li.material, li.barcode_id, li.ln_type, li.ln_data, li.ln_status, po.email, po.notify, po.po_type, po.po_tdata, po.po_fam, po.po_status, po.po_text, '', '', '', po.flags, CASE WHEN (s.ln='0') THEN c_po.cont ELSE c_ln.cont END cont, CASE WHEN (s.ln='0') THEN c_po.seal ELSE c_ln.seal END seal, CASE WHEN (s.ln='0') THEN c_po.desc ELSE c_ln.desc END desc, CASE WHEN (s.ln='0') THEN c_po.size ELSE c_ln.size END size, s.flight_type, s.flight_line, s.flag, s.flight, s.pa_ref, po.cust_ref, s.inv_no, s.mawb, s.hawb, s.act_exw, s.act_pick, s.pick_stat, s.rec_pa, s.rec_stat, s.etd, s.eta, s.ata, s.cleared, s.carrier, s.deliv, s.pod, s.atd, s.origin_country, s.dest_country, li.ln_parent, se.line_desc_ext, se.line_notes, s.cd1, s.cd2, po.incoterms[3,6], po.incoloc, 'VER21', s.custom_date3, s.custom_date4, s.custom_date5, s.custom_code1, s.custom_code2, s.custom_code3, s.custom_code4, s.custom_text1, s.custom_text2, s.custom_text3, s.custom_text4, m5337.v2, m5336.v2, m6302.v2, m6303.v2, m6304.v2, m6305.v2, m6306.v2, m6307.v2, m6308.v2, m6309.v2, m6310.v2, m6311.v7, m6312.v2, m6313.v2, m6314.v2, m6315.v2, m6316.v2, m6317.v2, m5468.v2, m6366.v2, m6318.v2, m6319.v2, m6320.v2, m6321.v2, m6322.v2, m6326.v2, m5421.v2, m6323.v3, m6324.v3, m6325.v7, m5422.v3, m5423.v3, m5437.v3, m5438.v7, m5887.v2, m5436.v2, m10615.v2, m12212.v2 FROM ot_po po , ot_status s , ot_linesinfo li , ot_status_ext se , OUTER ot_misc m5337 , OUTER ot_misc m5336 , OUTER ot_misc m6302 , OUTER ot_misc m6303 , OUTER ot_misc m6304 , OUTER ot_misc m6305 , OUTER ot_misc m6306 , OUTER ot_misc m6307 , OUTER ot_misc m6308 , OUTER ot_misc m6309 , OUTER ot_misc m6310 , OUTER ot_misc m6311 , OUTER ot_misc m6312 , OUTER ot_misc m6313 , OUTER ot_misc m6314 , OUTER ot_misc m6315 , OUTER ot_misc m6316 , OUTER ot_misc m6317 , OUTER ot_misc m5468 , OUTER ot_misc m6366 , OUTER ot_misc m6318 , OUTER ot_misc m6319 , OUTER ot_misc m6320 , OUTER ot_misc m6321 , OUTER ot_misc m6322 , OUTER ot_misc m6326 , OUTER ot_misc m5421 , OUTER ot_misc m6323 , OUTER ot_misc m6324 , OUTER ot_misc m6325 , OUTER ot_misc m5422 , OUTER ot_misc m5423 , OUTER ot_misc m5437 , OUTER ot_misc m5438 , OUTER ot_misc m5887 , OUTER ot_misc m5436 , OUTER ot_misc m10615 , OUTER ot_misc m12212 , OUTER ot_cont c_po , ot_status s_po , OUTER ( ot_cont_link clk, ot_cont c_ln ) WHERE po.prj='TES' AND ( s_po.ata IS NOT NULL AND s_po.update_date >= TODAY-2 UNITS DAY ) AND s.prj='TES' AND s.prj=li.prj AND s.po=li.po AND s.split=li.split AND s.ln=li.ln AND s.ln_level=li.ln_level AND s.ln_parent=li.ln _parent AND li.prj='TES' AND li.prj=po.prj AND li.po=po.po AND li.split=po.split AND c_po.prj='TES' AND c_po.prj=po.prj AND c_po.po=po.po AND c_po.split=po.split AND c_ln.prj='TES' AND c_ln.prj=clk.prj AND c_ln.recno=clk.cont_id AND clk.prj='TES' AND clk.prj=li.prj AND clk.po=li.po AND clk.split=li.split AND clk.ln=li.ln AND clk.ln_level=li.ln_level AND clk.l n_parent=li.ln_parent AND se.prj='TES' AND se.prj=li.prj AND se.po=li.po AND se.split=li.split AND se.ln=li.ln AND se.ln_level=li.ln_level AND se.ln_paren t=li.ln_parent AND m5337.prj='TES' AND m5337.prj=li.prj AND m5337.po=li.po AND m5337.split=li.split AND m5337.ln=li.ln AND m5337.id=5337 AND m5336.prj='TES' AND m5336.prj=li.prj AND m5336.po=li.po AND m5336.split=li.split AND m5336.ln=li.ln AND m5336.id=5336 AND m6302.prj='TES' AND m6302.prj=li.prj AND m6302.po=li.po AND m6302.split=li.split AND m6302.ln=li.ln AND m6302.id=6302 AND m6303.prj='TES' AND m6303.prj=li.prj AND m6303.po=li.po AND m6303.split=li.split AND m6303.ln=li.ln AND m6303.id=6303 AND m6304.prj='TES' AND m6304.prj=li.prj AND m6304.po=li.po AND m6304.split=li.split AND m6304.ln=li.ln AND m6304.id=6304 AND m6305.prj='TES' AND m6305.prj=li.prj AND m6305.po=li.po AND m6305.split=li.split AND m6305.ln=li.ln AND m6305.id=6305 AND m6306.prj='TES' AND m6306.prj=li.prj AND m6306.po=li.po AND m6306.split=li.split AND m6306.ln=li.ln AND m6306.id=6306 AND m6307.prj='TES' AND m6307.prj=li.prj AND m6307.po=li.po AND m6307.split=li.split AND m6307.ln=li.ln AND m6307.id=6307 AND m6308.prj='TES' AND m6308.prj=li.prj AND m6308.po=li.po AND m6308.split=li.split AND m6308.ln=li.ln AND m6308.id=6308 AND m6309.prj='TES' AND m6309.prj=li.prj AND m6309.po=li.po AND m6309.split=li.split AND m6309.ln=li.ln AND m6309.id=6309 AND m6310.prj='TES' AND m6310.prj=li.prj AND m6310.po=li.po AND m6310.split=li.split AND m6310.ln=li.ln AND m6310.id=6310 AND m6311.prj='TES' AND m6311.prj=li.prj AND m6311.po=li.po AND m6311.split=li.split AND m6311.ln=li.ln AND m6311.id=6311 AND m6312.prj='TES' AND m6312.prj=li.prj AND m6312.po=li.po AND m6312.split=li.split AND m6312.ln=li.ln AND m6312.id=6312 AND m6313.prj='TES' AND m6313.prj=li.prj AND m6313.po=li.po AND m6313.split=li.split AND m6313.ln=li.ln AND m6313.id=6313 AND m6314.prj='TES' AND m6314.prj=li.prj AND m6314.po=li.po AND m6314.split=li.split AND m6314.ln=li.ln AND m6314.id=6314 AND m6315.prj='TES' AND m6315.prj=li.prj AND m6315.po=li.po AND m6315.split=li.split AND m6315.ln=li.ln AND m6315.id=6315 AND m6316.prj='TES' AND m6316.prj=li.prj AND m6316.po=li.po AND m6316.split=li.split AND m6316.ln=li.ln AND m6316.id=6316 AND m6317.prj='TES' AND m6317.prj=li.prj AND m6317.po=li.po AND m6317.split=li.split AND m6317.ln=li.ln AND m6317.id=6317 AND m5468.prj='TES' AND m5468.prj=li.prj AND m5468.po=li.po AND m5468.split=li.split AND m5468.ln=li.ln AND m5468.id=5468 AND m6366.prj='TES' AND m6366.prj=li.prj AND m6366.po=li.po AND m6366.split=li.split AND m6366.ln=li.ln AND m6366.id=6366 AND m6318.prj='TES' AND m6318.prj=li.prj AND m6318.po=li.po AND m6318.split=li.split AND m6318.ln=li.ln AND m6318.id=6318 AND m6319.prj='TES' AND m6319.prj=li.prj AND m6319.po=li.po AND m6319.split=li.split AND m6319.ln=li.ln AND m6319.id=6319 AND m6320.prj='TES' AND m6320.prj=li.prj AND m6320.po=li.po AND m6320.split=li.split AND m6320.ln=li.ln AND m6320.id=6320 AND m6321.prj='TES' AND m6321.prj=li.prj AND m6321.po=li.po AND m6321.split=li.split AND m6321.ln=li.ln AND m6321.id=6321 AND m6322.prj='TES' AND m6322.prj=li.prj AND m6322.po=li.po AND m6322.split=li.split AND m6322.ln=li.ln AND m6322.id=6322 AND m6326.prj='TES' AND m6326.prj=li.prj AND m6326.po=li.po AND m6326.split=li.split AND m6326.ln=li.ln AND m6326.id=6326 AND m5421.prj='TES' AND m5421.prj=li.prj AND m5421.po=li.po AND m5421.split=li.split AND m5421.ln=li.ln AND m5421.id=5421 AND m6323.prj='TES' AND m6323.prj=li.prj AND m6323.po=li.po AND m6323.split=li.split AND m6323.ln=li.ln AND m6323.id=6323 AND m6324.prj='TES' AND m6324.prj=li.prj AND m6324.po=li.po AND m6324.split=li.split AND m6324.ln=li.ln AND m6324.id=6324 AND m6325.prj='TES' AND m6325.prj=li.prj AND m6325.po=li.po AND m6325.split=li.split AND m6325.ln=li.ln AND m6325.id=6325 AND m5422.prj='TES' AND m5422.prj=li.prj AND m5422.po=li.po AND m5422.split=li.split AND m5422.ln=li.ln AND m5422.id=5422 AND m5423.prj='TES' AND m5423.prj=li.prj AND m5423.po=li.po AND m5423.split=li.split AND m5423.ln=li.ln AND m5423.id=5423 AND m5437.prj='TES' AND m5437.prj=li.prj AND m5437.po=li.po AND m5437.split=li.split AND m5437.ln=li.ln AND m5437.id=5437 AND m5438.prj='TES' AND m5438.prj=li.prj AND m5438.po=li.po AND m5438.split=li.split AND m5438.ln=li.ln AND m5438.id=5438 AND m5887.prj='TES' AND m5887.prj=li.prj AND m5887.po=li.po AND m5887.split=li.split AND m5887.ln=li.ln AND m5887.id=5887 AND m5436.prj='TES' AND m5436.prj=li.prj AND m5436.po=li.po AND m5436.split=li.split AND m5436.ln=li.ln AND m5436.id=5436 AND m10615.prj='TES' AND m10615.prj=li.prj AND m10615.po=li.po AND m10615.split=li.split AND m10615.ln=li.ln AND m10615.id=10615 AND m12212.prj='TES' AND m12212.prj=li.prj AND m12212.po=li.po AND m12212.split=li.split AND m12212.ln=li.ln AND m12212.id=12212 AND s_po.prj='TES' AND s_po.prj=po.prj AND s_po.po=po.po AND s_po.split=po.split AND s_po.ln='0' ORDER BY 1 ASC, 21 ASC; The problem was reproduced on AIX IDS 11.50.FC5W2 (customer's version) and IDS 11.50.FC6 on HP-UX
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of 10.00.xC6 or later, 11.10.xC1 and later, 11.50.xC1 * * and later. * **************************************************************** * PROBLEM DESCRIPTION: * * When a query involves a large number of equivalent columns * * (such as t1.c1 = t2.c2 and t2.c2 = t3.c3, ...etc), and many * * of the columns involved are from subservient tables in an * * outer join, then the optimizer may spend excessive amount of * * time trying to properly deduce relationships among the * * equivalent columns, leading to what appears to be a hang of * * the query. * **************************************************************** * RECOMMENDATION: * * Upgrade to 11.50.xC7 and above. * ****************************************************************
Problem conclusion
This problem is fixed in 11.50.xC7.
Temporary fix
Comments
APAR Information
APAR number
IC65300
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2009-12-23
Closed date
2010-10-01
Last modified date
2010-10-01
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
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSN
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
01 October 2010