How to find list of jobs containing Teradata Connector stage

arvind_ds · Post by **arvind_ds** » Fri Nov 28, 2014 5:26 am

Hello Experts,

There are close to 100 datastage projects in our infosphere environment and total job count is close to 100K across all datastage projects.

Now the requirement is to get a list of parallel jobs which contain teradata connector stages in their job design.

How to find out this, any clue, please help. Many thanks.

Mike · Post by **Mike** » Fri Nov 28, 2014 9:16 am

I don't know of a great way to do this in a supported manner...

You'll find some options in the forum for using Designer's advanced find feature and parsing dsx files, but given your volume of projects/jobs, those may be a bit tedious. The advanced search in Information Server Manager might be an option, but I've never explored it myself. Metadata Workbench might be an option, but again, I've never explored it myself.

Personally, I've cobbled together a query against the XMETA database to fulfill this need for myself. The XMETA data model itself is unpublished and it's not exactly user friendly, but the table stuctures are readily available from the database catalog.

I'm not sure posting my query is a great idea since it's definitely unsupported. Use at your own risk.

This is for DB2 Connector stage usage... you'll have to adapt it for the Teradata Connector:

Code: Select all

SELECT   J.DSNAMESPACE_XMETA AS PROJECT_NAME,
         J.NAME_XMETA AS JOB_NAME,
         J.CATEGORY_XMETA AS JOB_CATEGORY,
         S.NAME_XMETA AS STAGE_NAME,
         L.NAME_XMETA AS LINK_NAME,
         'TARGET' AS CONNECTOR_USAGE
FROM     XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 J,
         XMETA.DATASTAGEX_XMETAGEN_DSSTAGEC2E76D84 S,
         XMETA.DATASTAGEX_XMETAGEN_DSLINKC2E76D84 L,
         XMETA.DATASTAGEX_XMETAGEN_DSINPUTPINC2E76D84 I
WHERE    S.CONTAINER_RID         = J.XMETA_REPOS_OBJECT_ID_XMETA
AND      L.CONTAINER_RID         = J.XMETA_REPOS_OBJECT_ID_XMETA
AND      I.CONTAINER_RID         = S.XMETA_REPOS_OBJECT_ID_XMETA
AND      I.ISTARGETOF_LINK_XMETA = L.XMETA_REPOS_OBJECT_ID_XMETA
AND      S.STAGETYPE_XMETA       = 'DB2ConnectorPX'
UNION ALL
SELECT   J.DSNAMESPACE_XMETA AS PROJECT_NAME,
         J.NAME_XMETA AS JOB_NAME,
         S.NAME_XMETA AS STAGE_NAME,
         L.NAME_XMETA AS LINK_NAME,
         'SOURCE' AS CONNECTOR_USAGE
FROM     XMETA.DATASTAGEX_XMETAGEN_DSJOBDEFC2E76D84 J,
         XMETA.DATASTAGEX_XMETAGEN_DSSTAGEC2E76D84 S,
         XMETA.DATASTAGEX_XMETAGEN_DSLINKC2E76D84 L,
         XMETA.DATASTAGEX_XMETAGEN_DSOUTPUTPINC2E76D84 O
WHERE    S.CONTAINER_RID         = J.XMETA_REPOS_OBJECT_ID_XMETA
AND      L.CONTAINER_RID         = J.XMETA_REPOS_OBJECT_ID_XMETA
AND      O.CONTAINER_RID         = S.XMETA_REPOS_OBJECT_ID_XMETA
AND      O.ISSOURCEOF_LINK_XMETA = L.XMETA_REPOS_OBJECT_ID_XMETA
AND      S.STAGETYPE_XMETA       = 'DB2ConnectorPX'
ORDER BY 1, 3, 2, 4
WITH UR

Mike

chulett · Post by **chulett** » Fri Nov 28, 2014 10:16 am

One should also note that "WITH UR" is DB2 syntax.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 28, 2014 7:50 pm

Do a "Where Used" report (in Designer or in Metadata Workbench) for the Teradata Connector stage.

arvind_ds · Post by **arvind_ds** » Mon Dec 01, 2014 4:28 am

Thank you Ray,Chulett & Mark for providing your valuable inputs.