As per my requirement, i want to know in there is any alternative way to identify the Columns which are getting update in DB2 Tables?
Excluding the following ways to identify:
1) Search String using: Opt ISPF 3.14
2) Looking into the component (Source code) and check whether there are any Insert, Update, or Cursor with Update Option query.
3) Referential Integrity/Cascade
I am looking is there is any utility which can generate the report for the specific Column which is getting updated in DB2 table by some specific Transactions...?
You can parse the info in SYSPACKSTMT, which is a PITA (and I don't mean "Flat bread of Mediterranean origin")
IF everything is explained in DB2 v8 or DB2 9 :
You can find the updated columns with this fancy query (DB2 9 only):
xmlquery('$c/UPDATE/TAB-REF/text()' passing xmlparse(a.node_data) as "c") as Tablename
from explowner.dsn_query_table a
, xmltable('$c/UPDATE/SET/ASSIGN/COLUMN' passing xmlparse(a.node_data) as "c"
columns tno char(3) path '@TNO'
, col varchar(100) path './text()') as x
, table (select * from explowner.plan_table pl where pl.queryno = a.queryno and pl.bind_time = a.explain_time
fetch first 1 row only) p
where a.type = 'UPDATE'
and a.query_stage = 'BEFORE'
--and xmlexists('$c/UPDATE[TAB-REF="tbcreator.tbname"]' passing xmlparse(a.node_data) as "c")
You'll have to learn some XML, but you could do something similar for Insert ,update where current
As we can identify the particular clumns in table which are getting updated by transactions.
Now lets say there are 10 components which are updating 5 coulumns of some table say X.Employee_Tbl.... Now i need to identify the names of those particular 10 components which are doing an updation(this updation may be insert/delete). .........!!
Is it possible from the following approaches that I mentioned earlier, is any alternative way to find the same....!! as looking into the components and then check the SQL's ...is quiet a time taken process....!!