The best you could probably get is compile time info.
You might be able to find what parameter was assigned to a property (and if it has a default value), but not what was actually used.
Where I am right now, this wouldn't work: it's all framework'ed and dynamic.
If you have DSODB set up, I'd try looking into DSODB to see if your operational metadata stores it.
That said, if your job properties are fairly static, you can brute force search XMETA and find what you need.
Use a catalog query to search all varchar2(64) columns (filter out the obviously knucklehead ones if you can: %_PRINCIPAL_XMETA is rarely useful) for your RID and follow the trail that way. Excel is your friend here.
Grab the RID for one of your oracle connectors in DATASTAGEX_DSSTAGECXXXXXX try it out.
I haven't done it much for DS, but a lot for IA,BG, user stuff, and while it can be a little slow, it will definitely get you there.
XMETA query for source/target db/server info
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 45
- Joined: Sat May 08, 2010 11:07 pm
- Location: banglore
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Saw this post, while researching another issue. Operations console database (DSODB) has target table information.:srinath0253 wrote:I am also searching for query to get target table information.
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
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 DataLocator, thus making DSODB a complete solution for impact analysis. If you agree with this enhancement request, please VOTE using following linkwith your IBM ID.
Thank you!
Based on this, we developed a query that enables us to find jobs that read or write a specific table.
Note that we used following variable identifier ${my_var}$ in this script. (This is default in DbVisualizer)
Based on this, we developed a query that enables us to find jobs that read or write a specific table.
Note that we used following variable identifier ${my_var}$ in this script. (This is default in DbVisualizer)
Code: Select all
SELECT
J.NAME_XMETA JOB_NAME
, S.NAME_XMETA STAGE_NAME
, S.HAS_INPUTPIN_XMETA
, S.HAS_OUTPUTPIN_XMETA
, XMLTYPE(VALUEEXPRESSION_XMETA).EXTRACT('//Properties/Usage/SQL/SelectStatement/text()').GETSTRINGVAL() SELECT_STATEMENT
, XMLTYPE(VALUEEXPRESSION_XMETA).EXTRACT('//Properties/Usage/SQL/InsertStatement/text()').GETSTRINGVAL() INSERT_STATEMENT
, XMLTYPE(VALUEEXPRESSION_XMETA).EXTRACT('//Properties/Usage/SQL/UpdateStatement/text()').GETSTRINGVAL() UPDATE_STATEMENT
, XMLTYPE(VALUEEXPRESSION_XMETA).EXTRACT('//Properties/Usage/SQL/DeleteStatement/text()').GETSTRINGVAL() DELETE_STATEMENT
FROM DATASTAGEX_DSSTAGE${HEX_ID}$ S
INNER JOIN DATASTAGEX_DSJOBDEF${HEX_ID}$ J
ON S.CONTAINER_RID = J.XMETA_REPOS_OBJECT_ID_XMETA
LEFT JOIN DATASTAGEXDSPARAMETRVL${HEX_ID}$ V
ON S.XMETA_REPOS_OBJECT_ID_XMETA = V.CONTAINER_RID
AND V.PARAMETERNAME_XMETA = 'XMLProperties'
WHERE J.DSNAMESPACE_XMETA LIKE '%${project}$'
AND S.STAGETYPE_XMETA = 'OracleConnectorPX'
-- AND S.HAS_INPUTPIN_XMETA IS NOT NULL
-- AND S.HAS_OUTPUTPIN_XMETA IS NOT NULL
AND VALUEEXPRESSION_XMETA LIKE '%${TABLE_NAME}$%'
AND XMLTYPE(VALUEEXPRESSION_XMETA).EXISTSNODE('//Properties/Usage/SQL/*') = 1
-- AND XMLTYPE(VALUEEXPRESSION_XMETA).EXISTSNODE('//Properties/Usage/SQL/SelectStatement') = 1
-- AND XMLTYPE(VALUEEXPRESSION_XMETA).EXISTSNODE('//Properties/Usage/SQL/InsertStatement') = 1
-- AND XMLTYPE(VALUEEXPRESSION_XMETA).EXISTSNODE('//Properties/Usage/SQL/UpdateStatement') = 1
-- AND XMLTYPE(VALUEEXPRESSION_XMETA).EXISTSNODE('//Properties/Usage/SQL/DeleteStatement') = 1