IC86189: REMOTE SUB-QUERY HAS INCORRECT QUERY PATH WITH VERY LOW PERFORMANCE

APAR status

• Closed as program error.

Error description

• If all tables in sub-query are in remote instance the query path
  is incorrect and the performance of the SQL is quite low.
  
  For example:
  create synonym lacct for cq@fc9:acct;
  create synonym lacct2 for cq@fc9:acct2;
  select * from lacct
  where exists(select 1 from lacct2 where lacct2.acct_id =
  lacct.acct_id and lacct1.bill_id =10001)
  
  The query plan is:
  QUERY: (OPTIMIZATION TIMESTAMP: 08-28-2012 14:28:53)
  ------
  select * from lacct
  where acct_id in (select acct_id from lacct2 where
  lacct2.acct_id = lacct.acct_id and lacct2.bill_id =10001)
  
  
  Estimated Cost: 1521742
  Estimated # of Rows Returned: 1000000
  
    1) informix.lacct: REMOTE PATH
      REMOTE SESSION ID FOR 'fc9' is 35
  
      Remote SQL Request:
      select x1.acct_item_id ,x1.acct_id ,x1.item_source_id
  ,x1.bill_i
      d ,x1.billing_cycle_id ,x1.acct_item_type_id ,x1.serv_id
  ,x1.amo
      unt ,x1.fee_cycle_id ,x1.balance_paid ,x1.payment_method
  ,x1.frg
      _id ,x1.old_amount ,x1.unit_num ,x1.disct_express_id
  ,x1.duratio
      n ,x1.rate_duration ,x1.area_id ,x1.bill_item_type_id
  ,x1.plan_o
      wner_inst_id ,x1.pricing_plan_id
  ,x1.event_pricing_strategy_id ,
      x1.meter_reading ,x1.partition_item_type
  ,x1.partition_charge ,x
      1.pay_cycle_id ,x1.product_id ,x1.had_invoice_amount
  ,x1.source_
      serv_id ,x1.no_invoice_amount from cq:"informix".acct x1
  
  
          Filters: informix.lacct.acct_id = ANY <subquery>
  
      Subquery:
      ---------
      Estimated Cost: 1
      Estimated # of Rows Returned: 1
  
        1) informix.lacct2: REMOTE PATH
          REMOTE SESSION ID FOR 'fc9' is 35
  
          Remote SQL Request:
          select x0.acct_id ,x0.bill_id from cq:"informix".acct2
  x0 where
          ((x0.acct_id = ? ) AND (x0.bill_id = 10001. ) )
  
  The remote instance excuted the remote SQL for each rows in
  cq:"informix".acct2. For exmaple there are 10000 rows in the
  table the server executed the sql 10000 times,which lead to very
  low performance.
  
  The following is the output of onstat -g ses in remote instance.
  Sess       SQL            Current            Iso Lock       SQL
  ISAM F.E.
  Id         Stmt type      Database           Lvl Mode       ERR
  ERR  Vers  Explain
  35         SELECT         cq                 LC  Not Wait   0
  0    9.52  Dyn. stat
  
  Current SQL statement (22351) :
    select x0.acct_id ,x0.bill_id from cq:"informix".acct2 x0
  where
      ((x0.acct_id = ? ) AND (x0.bill_id = 10001. ) )
  
  Host variables :
     address            type       flags value
     -----------------------------------------
     0x07000000324930a8 DECIMAL    0x000 22350
  .....
  
  Sess       SQL            Current            Iso Lock       SQL
  ISAM F.E.
  Id         Stmt type      Database           Lvl Mode       ERR
  ERR  Vers  Explain
  35         SELECT         cq                 LC  Not Wait   0
  0    9.52  Dyn. stat
  
  Current SQL statement (23919) :
    select x0.acct_id ,x0.bill_id from cq:"informix".acct2 x0
  where
      ((x0.acct_id = ? ) AND (x0.bill_id = 10001. ) )
  
  Host variables :
     address            type       flags value
     -----------------------------------------
     0x07000000324930a8 DECIMAL    0x000 23918
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All 11.70 users.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with remote table access may have sub-optimal query  *
  * plan under the following conditions:                         *
  *  - all tables referenced in the query are from the same      *
  * remote server                                                *
  *  - there is an EXISTS or NOT EXISTS subquery, and the        *
  * subquery only references tables from the same remote server  *
  * as the main query                                            *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Update to IDS-11.70.xC8.                                     *
  ****************************************************************
  

Problem conclusion

• Problem Fixed In IDS-11.70.xC8.
  

Temporary fix

Comments

APAR Information

• 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