Leverging DSODB to perform impact analysis.
Posted: Sun Apr 19, 2015 9:12 pm
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:
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.
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, 3There 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.