Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    2

    Unanswered: DB2 optimizer question

    DB2 Optimizer question

    The DB2 optimizer is driving me crazy. Any help is greatly apperciated. I have the query below

    SELECT fs_payaccount.tril_gid, cm_LedgerItem.tril_gid, cm_LedgerItem.ModificationDate, cm_LedgerItem.Allocation, cm_LedgerItem.fs_BatchID, cm_LedgerItem.fs_split, cm_LedgerItem.Credit, cm_LedgerItem.Debit, cm_LedgerItem.Description, cm_LedgerItem.Dirty, cm_LedgerItem.Ignore, cm_LedgerItem.ItemType, cm_LedgerItem.LCID, cm_LedgerItem.LedgerDate, cm_LedgerItem.Model, cm_LedgerItem.Payment, cm_LedgerItem.Promotion, cm_LedgerItem.RealmID, cm_LedgerItem.Reconciled, cm_LedgerItem.SalesTeam, cm_LedgerItem.SeqID, cm_LedgerItem.ServerSeqID, cm_LedgerItem.State, fs_payaccount.fs_Party, cm_Transaction.TransDate FROM fs_agrperson, fs_payaccount, cm_Transaction, cm_Allocation, cm_LedgerItem WHERE ( ( ( ( ( cm_LedgerItem.fs_BatchID ) = 2 ) AND ( ( fs_payaccount.fs_Party ) = ( fs_agrperson.fs_Party ) ) ) AND ( cm_LedgerItem.SalesTeam = fs_agrperson.tril_gid ) ) AND ( cm_Allocation.Trans = cm_Transaction.tril_gid ) ) AND ( cm_LedgerItem.Allocation = cm_Allocation.tril_gid ) ORDER BY fs_payaccount.fs_Party

    I have the following indexes on the respective tables.
    Index_name Table_name Column_name

    ------------------ -------------------- --------------------

    ALLOCSBYTRANS CM_ALLOCATION +TRANS

    KEY_CM_ALLOCATION CM_ALLOCATION +TRIL_GID

    KEY_CM_LEDGERITEM CM_LEDGERITEM +TRIL_GID

    TESTIND1 CM_LEDGERITEM +FS_BATCHID

    TRANSBYBATCH CM_TRANSACTION +BATCHNUMBER

    KEY_CM_TRANSACTION CM_TRANSACTION +TRIL_GID

    ICOMPE8072308FCM_T CM_TRANSACTION +COMPEVENT

    KEY_FS_AGRPERSON FS_AGRPERSON +TRIL_GID

    IDX_FSAP_PARTY FS_AGRPERSON +FS_PARTY

    IDX_FSAP_AGR FS_AGRPERSON +FS_AGREEMENT

    IDX_PACCOUNT_PARTY FS_PAYACCOUNT +FS_PARTY

    KEY_FS_PAYACCOUNT FS_PAYACCOUNT +TRIL_GID


    Respective of what I do DB2 does full table scans on all tables except it picks up the testind1 index on cm_ledgeritem table. The big tables are cm_transaction and cm_allocation. Each of then have a million rows in them. the Full table scans are killing the performance.

    How can I get DB2 pick up the indexes. I tried running stats using runstats. I tried reorging the tables. I tried updating the systables directly trying to bluff DB2 but nothing seems to work. Any suggestions will be of great help. It seems to be that I have all the right indexes in place but DB2 refuses to pick them up. Thanks.

  2. #2
    Join Date
    Sep 2002
    Posts
    2

    Re: DB2 optimizer question

    This data might also help

    I am using optimizer level 3 and using the following command to update stats.
    runstats on <tablename> with distribution and indexes all

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    You may try to use the index advisor from Control Center to determine the right indexes to be created for this workload.

    I guess you have already bind the package again after reorg and runstats, right?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •