Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    15

    Unanswered: Index usage is different between UAT and PROD

    Hi All,
    I have some thing to discuss about index.

    I am trying to find the un used indexes on my database.
    I have a query which will find the unused indexes.

    db2 "SELECT TBNAME, NAME,COLNAMES,LASTUSED FROM SYSIBM.SYSINDEXES WHERE TBNAME IN ('T1', 'T2', 'T3', 'T4') AND TBCREATOR='S1' ORDER BY LASTUSED"

    but I am getting two differnet outputs between uat and production.
    In Uat :
    Indexes had been used till recent time.

    In Prod :
    Indexes never used that means Lastused values is null.


    I am assuming some thing is not correct in prod db. Please help me to provide some information to utilize the all required indexes as expected like UAT.

    Regards,
    Barath Kaveripakam

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    it is very ordinary to have a different access path in a test environment than you have in prod. Sometimes it is due to difference in data, difference in CPU/memory/etc... Also, your development staff may be running queries to verify something, so you have queries running that are not run in production. I would make the decision on what indexes to keep based on my production database usage. There may be an occasion that you need an additional index or 2 in your test environment, due to validation of testing or something like that.

  3. #3
    Join Date
    May 2005
    Posts
    29
    Check to make sure db2lused isn't disabled in prod, Following registry variable disables it

    DB2_SYSTEM_MONITOR_SETTINGS=LAST_USE_INTERVAL:0

    There were memory leaks in some version, which required them to be disabled.

  4. #4
    Join Date
    Oct 2013
    Posts
    15
    Quote Originally Posted by db2inst1 View Post
    Check to make sure db2lused isn't disabled in prod, Following registry variable disables it

    DB2_SYSTEM_MONITOR_SETTINGS=LAST_USE_INTERVAL:0

    There were memory leaks in some version, which required them to be disabled.
    Hi,
    I am new to DB2DBA .. Please help me to know how to check the variable. Do I need to use db2set command.



    Regards,
    Barath Kaveripakam

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by bkumar.my View Post
    Hi,
    I am new to DB2DBA .. Please help me to know how to check the variable. Do I need to use db2set command.

    Regards,
    Barath Kaveripakam
    db2set -all

    But I doubt it is set. Unless the servers are identical and the data is identical, and all db cfg parms are the same, it is not unusual to have different access paths (some use an index and some don't). Of course, we are assuming that your runstats are up to date on both databases.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2013
    Posts
    15
    Quote Originally Posted by Marcus_A View Post
    db2set -all

    But I doubt it is set. Unless the servers are identical and the data is identical, and all db cfg parms are the same, it is not unusual to have different access paths (some use an index and some don't). Of course, we are assuming that your runstats are up to date on both databases.
    HI Marcus:
    Thank you..
    The output of db2 set -all is here from UAT. I have requested the Production results. I am yet to recieve. I have noticed that there is differnece on STATS_TIME column value between UAT and Production to identfy the runstats.
    UAT :
    STATS_TIME value is 2014-01-22-03.32.12.442528
    PROD:
    2013-09-21-00.09.04.809659
    below are the UAT variables values.
    There is no db2lused variable.
    etdbacp-mbbaixdev25:/home/etdbacp >db2set -all
    [i] DB2_USE_IOCP=ON
    [i] DB2FCMCOMM=TCPIP4
    [i] DB2_SKIPINSERTED=ON
    [i] DB2_USE_ALTERNATE_PAGE_CLEANING=ON
    [i] DB2_EVALUNCOMMITTED=ON
    [i] DB2_SKIPDELETED=ON
    [i] DB2MEMDISCLAIM=YES
    [i] DB2COMM=TCPIP
    [i] DB2_PARALLEL_IO=*
    [g] DB2FCMCOMM=TCPIP4
    [g] DB2SYSTEM=mbbaixdev25
    etdbacp-mbbaixdev25:/home/etdbacp >
    Regards,
    Barath Kaveripakam

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Check the CARD column in SYSCAT.TABLES and compare that to the actual number of rows in the table with "select count(*) from tab-name". If there is a discrepancy, then your problem may be that runstats is out of date.

    It would be best to run runstats on all tables in production and UAT, and check to see if that solves your problem.
    db2 runstats on tab-name with distribution on key columns and detailed indexes all

    If you are using DB2 Linux, UNIX, Windows, never use the SYSIBM tables if there are equivalent SYSCAT views (always use the corresponding SYSCAT views). The SYSCAT views are documented in the manual.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Oct 2013
    Posts
    15
    Quote Originally Posted by Marcus_A View Post
    Check the CARD column in SYSCAT.TABLES and compare that to the actual number of rows in the table with "select count(*) from tab-name". If there is a discrepancy, then your problem may be that runstats is out of date.

    It would be best to run runstats on all tables in production and UAT, and check to see if that solves your problem.
    db2 runstats on tab-name with distribution on key columns and detailed indexes all

    If you are using DB2 Linux, UNIX, Windows, never use the SYSIBM tables if there are equivalent SYSCAT views (always use the corresponding SYSCAT views). The SYSCAT views are documented in the manual.
    Yes Marcus. It seems I need to gather the stats in PROD.
    Can u tell me what is the frequency we need to run the RUN stats in production.
    I mean Every Day or Every Week or Every MOnth?

    -Barath

  9. #9
    Join Date
    Nov 2004
    Posts
    67
    You can use Configure Automatic Maintenance in Control Center for your DB. It makes automatic runstats.

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    "Yes Marcus. It seems I need to gather the stats in PROD.
    Can u tell me what is the frequency we need to run the RUN stats in production.
    I mean Every Day or Every Week or Every MOnth?"
    All depends .. if data changes in volume/size you need more runstats.. if static/old data runstats is not required anymore, once done, because data never changes
    we create a table with schema/tablename and assign by schema/table the interval for runstats, according the usage/movement of data
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by bkumar.my View Post
    Yes Marcus. It seems I need to gather the stats in PROD.
    Can u tell me what is the frequency we need to run the RUN stats in production.
    I mean Every Day or Every Week or Every MOnth?

    -Barath
    I would not use automatic maintenance for this, because it is better to control the exact runstats syntax options as I described them above.

    If you have a maintenance window on weekends (or a period of relatively low activity), weekly runstats is fine. If you have any stored procedures or other static SQL then you will need to rebind the packages after the runstats are done. Make sure to rebind them individually, otherwise you may get lock contention problems if the database is being used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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