Leverging DSODB to perform impact analysis.

Do you have features you'd like to see in future releases of DataStage, MetaStage, Parameter Manager, Version Control or one of the other tools represented on this forum? Post your ideas here!

Moderators: chulett, rschirm

Post Reply
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Leverging DSODB to perform impact analysis.

Post by rkashyap »

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 all

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 linkwith your IBM ID.
Last edited by rkashyap on Mon Apr 20, 2015 7:34 pm, edited 1 time in total.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply