var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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.
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
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?