Db2

 View Only
  • 1.  Column Usage Statistics

    Posted Fri April 03, 2020 09:36 AM
    I am looking to get a report on which Column is used maximum as part of join conditions in any database operations (select, insert, delete, update, etc). I would like to know if this information can be used to decide which columns are the most critical columns in the database and probably I can create an index on these when this database is migrated to a different database. In Oracle, we get this information at sys.col_usage$ Table. Please advise.

    ------------------------------
    Anurag Agrawal
    ------------------------------

    #Db2


  • 2.  RE: Column Usage Statistics

    Posted Sat April 04, 2020 10:57 PM
    @Anurag Agrawal
    Db2 does not provide a view like sys.col_usage$.
    To create a proper index, why not use the MON_GET functions to track queries that cause many table scans?

    The link below will be a good reference.
    https://datageek.blog/en/2012/11/28/db2-table-scans/​

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 3.  RE: Column Usage Statistics

    Posted Sun April 05, 2020 08:42 PM
    Just to give more clarity, In Oracle I was running the below query and it was giving a lot of information which helped me design the new database while migrating from Oracle to the new Database (memsql), and I would like to get the same metrics so that it will help me in a similar way when I migrate from db2 to the new Database (memsql). I read about mon_get and it doesn't seem to give me all the details that I am looking for. Also, is there a way we can connect on live chat somwhere, Are you comfortable chatting on google hangout?

    select r.name as r_owner, o.name as r_table , c.name as r_column,
                   equality_preds, equijoin_preds, nonequijoin_preds, range_preds,
                like_preds, null_preds, timestamp
                from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
             where o.obj# = u.obj# and c.obj# = u.obj# and c.col# = u.intcol#
              and o.owner# = r.user# and (u.equijoin_preds > 0 or u.nonequijoin_preds > 0)

    ------------------------------
    Anurag Agrawal
    ------------------------------



  • 4.  RE: Column Usage Statistics

    Posted Sun April 05, 2020 10:18 PM
    Since there is no view in db2 that shows detailed information about the predicate, I think the problem should be approached by checking many table scans with the mon_get_table function and finding a query that uses the table.
    And even if it is possible to create an index based on predicate, I think you should also consider the performance of other queries being affected.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 5.  RE: Column Usage Statistics

    Posted Tue April 07, 2020 09:26 AM
    thanks for the info

    ------------------------------
    Boat Hire Sydney
    ------------------------------



  • 6.  RE: Column Usage Statistics

    Posted Tue April 07, 2020 09:26 AM
    Hi,
    I am curious and wanted to know that if DB2 does not contains all details of predicate like oracle col_usage view then how it's db2advisor recommends the partition and index ... It is recommending on the basis of only that query and not considering the complete workload /application ..


    Thanks in advance for your reply  and time. 

    ------------------------------
    Neeraj Awasthi
    ------------------------------