IC64816: MODIFICATION FOR FRAGMENT'S EXPRESSION IS SLOW WHILE THERE IS NO DATA MOVEMENT

APAR status

• Closed as program error.

Error description

• Modifying a fragment's expression is very slow
  It takes more than 20
  minutes to complete it. The number of rows in the fragment is
  about 100 million. There is no data movement in this period.
  
  ---------table schema---------
  create table acct_item_23
    (
      acct_item_id decimal(12,0)  ,
      acct_id decimal(12,0)  ,
      item_source_id decimal(9,0)  ,
      bill_id decimal(12,0),
      billing_cycle_id decimal(9,0)  ,
      acct_item_type_id decimal(9,0)  ,
      serv_id decimal(12,0)  ,
      amount decimal(16,5)  ,
      fee_cycle_id decimal(9,0)  ,
      balance_paid decimal(16,5)  ,
      payment_method decimal(9,0),
      frg_id decimal(9,0)  ,
      old_amount decimal(16,5),
      unit_num decimal(9,0),
      disct_express_id decimal(9,0),
      duration decimal(9,0)  ,
      rate_duration decimal(18,0)  ,
      area_id decimal(9,0),
      bill_item_type_id decimal(9,0),
      plan_owner_inst_id decimal(12,0),
      pricing_plan_id decimal(9,0),
      event_pricing_strategy_id decimal(9,0),
      meter_reading decimal(12,0),
      partition_item_type decimal(9,0),
      partition_charge decimal(16,5),
      pay_cycle_id decimal(9,0),
      product_id decimal(9,0),
      had_invoice_amount decimal(16,5)
          default 0.00000,
      source_serv_id decimal(12,0),
      no_invoice_amount decimal(16,5)
    )
    fragment by expression
      (billing_cycle_id = 177. ) in dbs1 ,
      (billing_cycle_id = 178. ) in dbs2 ,
      (billing_cycle_id = 179. ) in dbs3 ,
      (billing_cycle_id < 177. ) in dbs4
    extent size 100000 next size 100000 lock mode page;
  
  create index idx_acct23_acctid05 on acct_item_23
      (acct_id) using btree ;
  create index idx_acct23_acctitemid05 on acct_item_23
      (acct_item_id) using btree ;
  create index idx_acct23_acctitemtypeid05 on acct_item_23
      (acct_item_type_id) using btree ;
  create index idx_acct23_billid05 on acct_item_23
      (bill_id) using btree ;
  create index idx_acct23_servid05 on acct_item_23
      (serv_id) using btree ;
  
  --------- steps -------
  begin work;
  lock table acct_item_23 in exclusive mode;
  alter fragment on table acct_item_23 DETACH dbs1
  acct_item_23_old;
  alter fragment on table acct_item_23 MODIFY dbs4 to
  billing_cycle_id <
  178 in dbs4;
  commit work;
  
  The expression is changed from "billing_cycle_id < 177" to
  "billing_cycle_id < 178" . It is always true and there is no
  data movement in this period. It takes more than 20 minutes
  to complete this action.
  
  
  
  It takes 10 seconds for the modification when the number of rows
  
  is 2 million in the fragment.
  It takes 36 seconds for the modification when the number of rows
  
  is 7.5 million in the fragment.
  
  The related PMR is 87067,000,672
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALTER FRAGMENT ON TABLE <tab> MODIFY option can scan the     *
  * rows even though there are no data movement is required      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * ALTER FRAGMENT ON TABLE <tab> MODIFY option will scan the    *
  * rows even though there no data movement is required. If the  *
  * user is only modifying a constant expression and the old     *
  * constant expression is a subset of the new expression then   *
  * no data movement is required. This causes slow performance.  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade 11.50.xC7 and above.                                 *
  ****************************************************************
  

Problem conclusion

• The following case will eliminate the data movement.
  
  1. Alter fragment on table  (attached index also)
  2. Fragment expression
  3. no remainder
  4. fragment to be modify should be last fragment in the list
  5. only one modify option ( no multiple fragment modification)
  6. partition name and dbspace name should match from old and new
  expression
  7. both old and new expression should be simple ( no OP_AND or
  OP_OR nodes )
  8. The constant expression needs to be subset of new constant
  expression
  
  Problem first fixed in 11.50.xC6
  

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 PSY

     UP