How to track which ETL job is loading 1 particular DB table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
How to track which ETL job is loading 1 particular DB table
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.
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.
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
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
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
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.
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.
Mamu Kim
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
Hello Kim,kduke wrote: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.
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
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
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.
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.
Mamu Kim
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
;
Mamu Kim
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'
)
;
Mamu Kim
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'
)
;
=========================================
Mamu Kim
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.
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.
Mamu Kim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is the correct conclusion.maneesahmed wrote:Unfortunately, We have not used the metadata defintions in manager.
So cannot use the same to track the job.
You WILL use the table definitions from the Repository in future, won't you?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Can we save the metadata definitiona at a later stage and use this method?maneesahmed wrote:Hello Narasimha,narasimha wrote:Just curious. Did you try Usage Analysis, did it not help?
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.