IBM Support

IC65300: INFINITE LOOP OCCURS IN OPFINDLNKEG WITH A LARGE NUMBER OF OUTER JOINS

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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
            

            
                      

        


        

        


      

    

  



    

  

  
    
            

          
Page Feedback