|
View previous topic :: View next topic
|
| Author |
Message |
ankvardhan
New User
Joined: 05 Sep 2008
Posts: 14
Location: Mumbai
|
|
|
|
Hi,
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...?
Thanks in Advance
Ank |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
You can parse the info in SYSPACKSTMT, which is a PITA (and I don't mean "Flat bread of Mediterranean origin")
or
IF everything is explained in DB2 v8 or DB2 9 :
You can find the updated columns with this fancy query (DB2 9 only):
| Code: |
select
xmlquery('$c/UPDATE/TAB-REF[1]/text()' passing xmlparse(a.node_data) as "c") as Tablename
, x.col
, p.progname
, p.version
from explowner.dsn_query_table a
, xmltable('$c/UPDATE/SET/ASSIGN/COLUMN[1]' 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[1]="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 |
|
| Back to top |
|
|
ankvardhan
New User
Joined: 05 Sep 2008
Posts: 14
Location: Mumbai
|
|
|
|
Hi,
Thanks for the response....!!
But still this wont work ...as per my requirement...!! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| But still this wont work ...as per my requirement...!! |
If this will not work, you need to explain why. . . And more clearly explain the "requirement". |
|
| Back to top |
|
|
ankvardhan
New User
Joined: 05 Sep 2008
Posts: 14
Location: Mumbai
|
|
|
|
Hi,
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....!!
Hope this gives u a clear idea..?
Ank |
|
| Back to top |
|
|
|
|
