Results 1 to 9 of 9

Thread: Index Help

  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: Index Help

    db2 9.5 on AIX 6.1.

    I create an index

    create index db2gtpd.tps_liability_info on mygtp.tps_liability_info ("CUSTOMER_ID" ASC, "INSTRUMENT_ID" ASC) allow reverse scans;

    and then I do a reorg and runstats on that table.

    But then I found this from the web

    select char(a.tabschema,8 ) as schema,
    char(a.tabname,18 ) as table,
    char(a.indname,18 ) as index,
    a.fullkeycard as IXFULLKEYCARD,
    b.card as TBCARD,
    int((float(a.fullkeycard)/float(b.card)) * 100) as ratio
    from syscat.indexes a inner join syscat.tables b
    on a.tabschema = b.tabschema
    and a.tabname = b.tabname
    where a.fullkeycard > 1
    and a.tabschema <> 'SYSIBM'
    and b.card > 100
    and a.uniquerule <> 'U'
    and int((float(a.fullkeycard)/float(b.card)) * 100) < 60
    order by 1, 2, 3;

    which gives me schema, table, index, ixfullkeycard (2099), tbcard(74989) and ratio (2)

    So the ratio is low and probably not really helping at all. I just don't know what else i can do to make it better. The table in question doesn't have a primary key either.

    I'm very far removed from DB2 in terms of working on it day to day so any help or guidance would be appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mark_maz View Post
    I just don't know what else i can do to make it better.
    By increasing the index key cardinality, I guess. What prompted you to create the index, and did the index solve the problem it was intended to solve?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    The context of the problem is the table in question has a number of inserts during a batch process then after the inserts complete and extraction takes place and a file is generated to be uploaded to another system. It has been taking over 5 minutes to do this whole process and sometimes 10 which causes an abend in the scheduler. The file gets generated but it just takes a long time. The index was created to hopefully speed up the extraction process but in our test environment it actually made it worse, so I am at a loss.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So why do you think that particular index would help? Did you run db2advis on the query?

    If you post the db2exfmt output for the query explain plan, I'm sure some people here might suggest something.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    sorry for the late reply but that is the problem, no one can tell me the sql that is being invoked. The reason why no one can tell me the sql is that another application is invoking a process that is updating/extracting from the database and the logging is very very poor.

    I was hoping the index would work as I looked at other indexes that have been created in the past and they used the same columns on a different table so I just assumed it would work on this table.

    Can you tell how I could capture the sql?

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Look at:

    SNAPSHOT_DYN_SQL
    SYSIBMADM.SNAPSTMT
    MON_GET_PKG_CACHE_STMT
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    I got the query finally:

    SELECT T1."TPS_LIABILITY_SEQ_ID",T1."EXTRACT_RECORD_IDENT IFICATION",T1."BANK_ORG_MANAG_ID",T1."BANK_ORG_MAN AG_NAME",T1."BANK_ORG_OPER_ID",T1."BANK_ORG_OPER_N AME",T1."PROD_SUBTYPE",T1."CUSTOMER_TYPE_CODE",T1. "CURRENCY_CODE",T1."LOGICAL_DATE",T1."AMOUNT_OUT_L IAB_COC",T1."AMOUNT_OUT_LIAB_BASE",T1."CUSTOMER_ID ",T1."BUSINESS_NAME",T1."INSTRUMENT_ID",T1."BANK_O RG_ID_MANAGERIAL",T1."PRODUCT_CODE",T1."ORIG_LCS_F UNCTION_CODE",T1."OLL_CURRENCY", T1."OLL_TRANSIT_NUMBER",T1."OLL_ACCOUNT_NUMBER",T1 ."COUNTRY_OF_RISK_CODE",T1."DATE_EXPIRY",T1."SIGHT _TIME_INDICATOR",T1."COLL_FLAG",T1."ATTR_FLAG2",T1 ."DSA_INDICATOR",T1."ELIGIBLE_FLAG",T1."DATE_MATUR ITY",T1."FILLER",T1."UPDATED_BY",T1."UPDATED_ON",T 1."OLL_LOAN_NO",T1."OLL_LOAN_SUB_TYPE",T1."BATCH_C YCLE" FROM MYGTP.TPS_LIABILITY_INFO T1 WHERE (T1."TPS_LIABILITY_SEQ_ID" = ?)FOR UPDATE OF "TPS_LIABILITY_SEQ_ID", "EXTRACT_RECORD_IDENTIFICATION","BANK_ORG_MANAG_ID ","BANK_ORG_MANAG_NAME","BANK_ORG_OPER_ID","BANK_O RG_OPER_NAME","PROD_SUBTYPE","CUSTOMER_TYPE_CODE", "CURRENCY_CODE","LOGICAL_DATE","AMOUNT_OUT_LIAB_CO C","AMOUNT_OUT_LIAB_BASE","CUSTOMER_ID","BUSINESS_ NAME","INSTRUMENT_ID","BANK_ORG_ID_MANAGERIAL","PR ODUCT_CODE","ORIG_LCS_FUNCTION_CODE","OLL_CURRENCY ","OLL_TRANSIT_NUMBER","OLL_ACCOUNT_NUMBER","COUNT RY_OF_RISK_CODE","DATE_EXPIRY", "SIGHT_TIME_INDICATOR","COLL_FLAG","ATTR_FLAG2","D SA_INDICATOR","ELIGIBLE_FLAG","DATE_MATURITY","FIL LER","UPDATED_BY","UPDATED_ON","OLL_LOAN_NO","OLL_ LOAN_SUB_TYPE","BATCH_CYCLE"

    And I ran db2expln on it and was wondering how could i tune the table based on this output as the query cannot be changed. It is created by another process

    Isolation Level = Cursor Stability
    Blocking = Block Unambiguous Cursors
    Query Optimization Class = 5

    Partition Parallel = No
    Intra-Partition Parallel = No

    SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
    "DB2GTPI"


    Statement:

    SELECT T1."TPS_LIABILITY_SEQ_ID" , T1.
    "EXTRACT_RECORD_IDENTIFICATION" , T1."BANK_ORG_MANAG_ID" ,
    T1."BANK_ORG_MANAG_NAME" , T1."BANK_ORG_OPER_ID" , T1.
    "BANK_ORG_OPER_NAME" , T1."PROD_SUBTYPE" , T1.
    "CUSTOMER_TYPE_CODE" , T1."CURRENCY_CODE" , T1.
    "LOGICAL_DATE" , T1."AMOUNT_OUT_LIAB_COC" , T1.
    "AMOUNT_OUT_LIAB_BASE" , T1."CUSTOMER_ID" , T1.
    "BUSINESS_NAME" , T1."INSTRUMENT_ID" , T1.
    "BANK_ORG_ID_MANAGERIAL" , T1."PRODUCT_CODE" , T1.
    "ORIG_LCS_FUNCTION_CODE" , T1."OLL_CURRENCY" , T1.
    "OLL_TRANSIT_NUMBER" , T1."OLL_ACCOUNT_NUMBER" , T1.
    "COUNTRY_OF_RISK_CODE" , T1."DATE_EXPIRY" , T1.
    "SIGHT_TIME_INDICATOR" , T1."COLL_FLAG" , T1."ATTR_FLAG2" ,
    T1."DSA_INDICATOR" , T1."ELIGIBLE_FLAG" , T1."DATE_MATURITY"
    , T1."FILLER" , T1."UPDATED_BY" , T1."UPDATED_ON" , T1.
    "OLL_LOAN_NO" , T1."OLL_LOAN_SUB_TYPE" , T1."BATCH_CYCLE"
    FROM MYGTP.TPS_LIABILITY_INFO T1
    WHERE (T1."TPS_LIABILITY_SEQ_ID" =?)
    FOR UPDATE OF "TPS_LIABILITY_SEQ_ID" ,
    "EXTRACT_RECORD_IDENTIFICATION" , "BANK_ORG_MANAG_ID" ,
    "BANK_ORG_MANAG_NAME" , "BANK_ORG_OPER_ID" ,
    "BANK_ORG_OPER_NAME" , "PROD_SUBTYPE" , "CUSTOMER_TYPE_CODE"
    , "CURRENCY_CODE" , "LOGICAL_DATE" , "AMOUNT_OUT_LIAB_COC" ,
    "AMOUNT_OUT_LIAB_BASE" , "CUSTOMER_ID" , "BUSINESS_NAME" ,
    "INSTRUMENT_ID" , "BANK_ORG_ID_MANAGERIAL" , "PRODUCT_CODE",
    "ORIG_LCS_FUNCTION_CODE" , "OLL_CURRENCY" ,
    "OLL_TRANSIT_NUMBER" , "OLL_ACCOUNT_NUMBER" ,
    "COUNTRY_OF_RISK_CODE" , "DATE_EXPIRY" ,
    "SIGHT_TIME_INDICATOR" , "COLL_FLAG" , "ATTR_FLAG2" ,
    "DSA_INDICATOR" , "ELIGIBLE_FLAG" , "DATE_MATURITY" ,
    "FILLER" , "UPDATED_BY" , "UPDATED_ON" , "OLL_LOAN_NO" ,
    "OLL_LOAN_SUB_TYPE" , "BATCH_CYCLE"


    Section Code Page = 1208

    Estimated Cost = 1153.464478
    Estimated Cardinality = 1.003720

    Access Table Name = MYGTP.TPS_LIABILITY_INFO ID = 8,5
    | #Columns = 34
    | Evaluate Block/Data Predicates Before Locking Committed Row
    | Relation Scan
    | | Prefetch: Eligible
    | Lock Intents
    | | Table: Intent Exclusive
    | | Row : Update
    | Sargable Predicate(s)
    | | #Predicates = 1
    Return Data to Application
    | #Columns = 35

    End of section

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You have a table scan.
    Don't you have an index on TPS_LIABILITY_SEQ_ID? Did you try to add one? Did you try using db2advis?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  9. #9
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    yes i have run db2advis and it states to create the index on the column you mentioned. Thanks for all your guidance. It just took me a while to get the damn query

Posting Permissions

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