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
Original Message:
Sent: Thu April 02, 2020 11:52 PM
From: Anurag Agrawal
Subject: Column Usage Statistics
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