How to track which ETL job is loading 1 particular DB table

[maneesahmed]

Hello,

We are facing one problem where we are not able to track the ETL job which is populating data into one particular oracle table.

We have close to 500-600 ETL jobs and it is time consuming to go thru each of the job to find out which job is actually populating data into the table.

Is there any easier/faster way of finding out which ETL job is populating data into this oracle table.

Any help in this regard will be appreciated.

[narasimha]

Do you have the table metadata defined in the table definition?
If yes, then there is a easy way to find out all the reference to your table.

Go to DS Manager, select your table and click on "Usage Analysis" (A funnel shaped icon, next to the help icon)
This will give you all job references to your table

[kduke]

There are several SQL statements which I have posted which should give you this. They are all based on stage types. Most of the plugins use custom stage types. The metadata is consistent across these. I have several jobs included in EtlStats which can extract this information and load it into tables. You can download these and figure out how I did it.

Another option is to search for the table name in all the jobs. This is also been posted before. If you cannot find these posts then ask.

[maneesahmed]

There are several SQL statements which I have posted which should give you this. They are all based on stage types. Most of the plugins use custom stage types. The metadata is consistent across these. I have several jobs included in EtlStats which can extract this information and load it into tables. You can download these and figure out how I did it.

Another option is to search for the table name in all the jobs. This is also been posted before. If you cannot find these posts then ask.

Hello Kim,

Thanks for the reply. Can you please point me to the exact post where I can find this info. I did try out couple of searches but was not successful in narrowing down.

Thanks

[narasimha]

Just curious. Did you try Usage Analysis, did it not help?

[maneesahmed]

Just curious. Did you try Usage Analysis, did it not help?

Hello Narasimha,

Unfortunately, We have not used the metadata defintions in manager.
So cannot use the same to track the job.

I think your suggestion should help incase we had the metadata defintion defined.


Thanks.

[ShaneMuir]

Just as an aside - we populate our oracle tables with a JobID so that we know which records were updated by which jobs. This makes it very easy to determine which job was loading the table. Of course this only works to determine which job loads the table, if there is data in the table already.

[kduke]

Here are a few:

viewtopic.php?t=99543
viewtopic.php?t=100201
viewtopic.php?t=91211
viewtopic.php?t=90303
viewtopic.php?t=99349

I prefer the last one.

[kduke]

The jobs in EtlStats are:

Code: Select all

LoadEtlMdProperty
LoadEtlMdProperty2
LoadEtlMdTable

Code: Select all

SELECT 
   DS_JOBOBJECTS.NAME as LinkName, 
   DS_JOBS.NAME as JobName, 
   DS_JOBOBJECTS.OdbcTableNames, 
   DS_JOBOBJECTS.OdbcAction 
FROM 
   DS_JOBOBJECTS,  
   DS_JOBS 
WHERE 
   OBJIDNO = JOBNO 
   and DS_JOBS.CATEGORY LIKE 'Production%' 
   and DS_JOBOBJECTS.OLETYPE = 'CODBCInput' 
Group by 
   DS_JOBOBJECTS.NAME, 
   DS_JOBS.NAME, 
   DS_JOBOBJECTS.OdbcTableNames, 
   DS_JOBOBJECTS.OdbcAction
;

[kduke]

Requires these dictionary items:

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'OdbcSqlType', 
   'I', 
   'IF OLETYPE = "CODBCOutput" THEN @RECORD<75> : (IF @RECORD<75> = "1" THEN " Generated" ELSE (IF @RECORD<75> = "2" THEN " User Defined" ELSE "")) ELSE ""',
   'OdbcSqlType', 
   '15L', 
   'S'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'OdbcTableNames', 
   'I', 
   'IF OLETYPE[1,5] = "CODBC" THEN @RECORD<6> ELSE ""',
   'OdbcTableNames', 
   '40T', 
   'S'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'SqlPrimary', 
   'I', 
   'IF OLETYPE = "CODBCOutput" THEN @RECORD<9> ELSE ""',
   'SqlPrimary', 
   '40T', 
   'S'
)
; 

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM
) VALUES (
   'OdbcAction', 
   'I', 
   'IF OLETYPE = "CODBCInput" THEN @RECORD<7> ELSE ""',
   'OdbcAction', 
   '1R', 
   'S'
)
; 

[kduke]

Here are my notes on Custom stages:

Code: Select all

=========================================
INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'CustomPropName', 
   'I', 
   'IF OLETYPE = "CCustomInput" THEN @RECORD<14> ELSE ""',
   'CustomPropName', 
   '40L', 
   'M', 
   'MvCustom'
)
; 

DELETE DICT DS_JOBOBJECTS CustomPropName

CD DS_JOBOBJECTS CustomPropName

Select 
   DS_JOBS.NAME AS JOB_NAME FMT '40L', 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '25L',
   DS_JOBOBJECTS.CustomPropName
From 
   DS_JOBOBJECTS,
   DS_JOBS
Where
   DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
   and DS_JOBOBJECTS.OLETYPE = 'CCustomInput'
;
=========================================
INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP, 
   NAME, 
   FORMAT, 
   SM, 
   ASSOC
) VALUES (
   'CustomPropValue', 
   'I', 
   'IF OLETYPE = "CCustomInput" THEN @RECORD<15> ELSE ""',
   'CustomPropValue', 
   '40L', 
   'M', 
   'MvCustom'
)
; 

DELETE DICT DS_JOBOBJECTS CustomPropValue

CD DS_JOBOBJECTS CustomPropValue

Select 
   DS_JOBS.NAME AS JOB_NAME FMT '40L', 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '25L',
   DS_JOBOBJECTS.CustomPropName,
   DS_JOBOBJECTS.CustomPropValue
From 
   unnest DS_JOBOBJECTS on MvCustom,
   DS_JOBS
Where
   DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
   and DS_JOBOBJECTS.OLETYPE = 'CCustomInput'
   and DS_JOBOBJECTS.CustomPropName = 'TABLE'
;

   and DS_JOBOBJECTS.CustomPropName = 'TABLE'
   and DS_JOBOBJECTS.CustomPropName = 'LOADACTION'
   and DS_JOBOBJECTS.CustomPropValue = 'Truncate table then load'
=========================================
INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE,
   EXP
) VALUES (
   'MvCustom',
   'PH',
   'CustomPropName CustomPropValue'
)
; 
=========================================

[kduke]

I think it is really difficult to report on these while they are not in tables. I like to extract this ETL metadata into tables so I can write nice reports against this kind of metadata. Reporting either from DS_JOBOBJECTS or doc_tool.mdb and Reporting Assistant is not easy.

DwNav was designed to navigate from the tables to the jobs. When browsing a job it will show you the job names that built the sources for this job. In other words if a job does a lookup on a hashed file while browsing the metadata for this job it shows all the jobs which write to this hashed file. For example if HF_Cust_Lookup is used in CustLoad job then what job built it. Your naming conventions may help because CustBuildHash job may have built it but DwNav will point this out while browsing the dependent job CustLoad. This saves tons of time learning these dependencies. Also Impact Analysis is built in. You can easily jump from DwNav to Designer as well to modify these jobs.

[kduke]

By the way both DwNav and EtlStats can generate html reports on this metadata. DwNav has a save as html. EtlStats has a job called GenHtmlFromSql. Both html are nice and can be copied easily into spreadsheets.

[ray.wurlod]

Unfortunately, We have not used the metadata defintions in manager.
So cannot use the same to track the job.

This is the correct conclusion.
You WILL use the table definitions from the Repository in future, won't you?

[DSer]

Just curious. Did you try Usage Analysis, did it not help?

Hello Narasimha,

Unfortunately, We have not used the metadata defintions in manager.
So cannot use the same to track the job.

I think your suggestion should help incase we had the metadata defintion defined.


Thanks.

Can we save the metadata definitiona at a later stage and use this method?