DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
rkashyap



Group memberships:
Premium Members

Joined: 02 Dec 2011
Posts: 526
Location: Richmond VA
Points: 4750

Post Posted: Sun Apr 19, 2015 9:12 pm Reply with quote    Back to top    

DataLocator table of Operations Console database(DSODB) can be leveraged to identify database tables to DataStage job correlation and perform impact analysis.

SQL that we are using to perform impact analysis is:
Code:
SELECT distinct X.ProjectName, X.JobName, JS.StageName, JL.LinkName, DL.DATASTORENAME, DL.DataCollectionName AS TableName, JL.IsTarget
     FROM  DSODB.JOBRUN R
     JOIN  DSODB.JOBEXEC X      ON R.JOBID = X.JOBID
     JOIN  DSODB.JOBSTAGE JS    ON X.JOBID = JS.JOBID
     JOIN  DSODB.JOBRUNSTAGE RS ON (JS.STAGEID = RS.STAGEID and R.RUNID = RS.RUNID)
     JOIN  DSODB.JOBLINK JL     ON (JS.STAGEID = JL.FROMSTAGEID OR JS.STAGEID = JL.TOSTAGEID)
     JOIN  DSODB.JOBRUNLINK RL  ON (R.RUNID = RL.RUNID AND RL.LINKID = JL.LINKID)
     JOIN  DSODB.DATALOCATOR DL ON RL.LOCATORID = DL.LOCATORID
     WHERE DL.DATACOLLECTIONSUBCLASS = 'TABLE'
--          AND R.RUNSTARTTIMESTAMP > (SYSDATE - 1)
     ORDER BY 1, 2, 3

However this approach has a limitation ... DSODB.DataLocator has names of all tables from single table operations (insert/updates, deletes, singleton selects), but only first tablename from a custom SQL (Join/Pl SQL etc).

There is an outstanding Request For Enhancement to capture all the tablenames in DSODB.DataLocator.

Please review this enhancement request. If you agree with it, please VOTE using following link with your IBM ID.


Last edited by rkashyap on Mon Apr 20, 2015 7:34 pm; edited 1 time in total
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43043
Location: Denver, CO
Points: 222227

Post Posted: Mon Apr 20, 2015 7:16 am Reply with quote    Back to top    

There's a specific forum for things like this... moved.

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours