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

@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
------------------------------

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
------------------------------

Original Message:
Sent: Sat April 04, 2020 10:57 PM
From: SangGyu Jeong
Subject: Column Usage Statistics

@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
------------------------------

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
------------------------------

Original Message:
Sent: Sun April 05, 2020 08:41 PM
From: Anurag Agrawal
Subject: Column Usage Statistics

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
------------------------------


Original Message:
Sent: Sat April 04, 2020 10:57 PM
From: SangGyu Jeong
Subject: Column Usage Statistics

@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

thanks for the info

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

Original Message:
Sent: Sun April 05, 2020 10:18 PM
From: SangGyu Jeong
Subject: Column Usage Statistics

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
------------------------------


Original Message:
Sent: Sun April 05, 2020 08:41 PM
From: Anurag Agrawal
Subject: Column Usage Statistics

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


Original Message:
Sent: Sat April 04, 2020 10:57 PM
From: SangGyu Jeong
Subject: Column Usage Statistics

@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

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
------------------------------

Original Message:
Sent: Sat April 04, 2020 10:57 PM
From: SangGyu Jeong
Subject: Column Usage Statistics

@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
------------------------------