XMETA query for source/target db/server info

[stuartjvnorton]

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.

[priyadarshikunal]

I have seen where these details are stored, however can't remember at the moment. I think its worth looking at the DSLINKXXXXXX table or PARSEDEXPRESSIONXXXXXX or something like that.

[srinath0253]

Any update on this post? I am also searching for query to get target table information.

[ray.wurlod]

Any update on this post?

Clearly not.

[rkashyap]

I am also searching for query to get target table information.

Saw this post, while researching another issue. Operations console database (DSODB) has 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

This query would also help with OP's quest for "source and target server/database names for each job under a particular project folder", albeit with a caveat.

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.

[JKenklies]

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)

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