Following the rule of thumb "Never re-invent the wheel unnecessarily" I am using Kim's ETL Stats package to gather job run details.
@Kim Duke thanks for sharing the ETL Stats package in the past.
Found ETL Stats (EtlStats_20060704) in my personal datastage archive. Wanted to re-use for my experimental project on Infosphere version 8.1.
I think I bumped into a wall though.
for DS_JOBOBJECTS table in UV repository
Code: Select all
BuildDsJobobjectsHash..DsJobobjects.DsJobobjects: DSD.BCIOpenR call to function SQLExecDirect failed.
Statement was:SELECT DS_JOBOBJECTS.NAME, DS_JOBOBJECTS.ColNames, DS_JOBOBJECTS.ColDerivation, DS_JOBOBJECTS.ColDesc, DS_JOBOBJECTS.ColFieldPos, DS_JOBOBJECTS.ColKeyPos, DS_JOBOBJECTS.ColNullable, DS_JOBOBJECTS.ColParsedDerivation, DS_JOBOBJECTS.ColPrecision, DS_JOBOBJECTS.ColScale, DS_JOBOBJECTS.ColSqlType, DS_JOBOBJECTS.OBJIDNO FROM UNNEST DS_JOBOBJECTS ON MvCols WHERE OLETYPE LIKE '%Output'
SQLSTATE=S1000, DBMS.CODE=950220
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Invalid association operation: Column or association name "MvCols" does not exist in DICT of file "DS_JOBOBJECTS".
Code: Select all
EtlJobActivities..DsJobObjects.DsJobObjects: DSD.BCIOpenR call to function SQLExecDirect failed.
Statement was:SELECT DsJobObjects.OBJIDNO, DsJobObjects.JobActParamNames, DsJobObjects.JobActParamDefault, NAME, DsJobObjects.JobActJobName FROM UNNEST "DsJobObjects" ON MvJobAct WHERE OLETYPE = 'CJSJobActivity'
SQLSTATE=S1000, DBMS.CODE=950220
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Invalid association operation: Column or association name "MvJobAct" does not exist in DICT of file "DsJobObjects".
Yet there is no details about either DsJobObjects (not DS_JOBOBJECTS) or DsJobs (not DS_JOBS) user defined UV tables in VOC.Non-technical description:
Used to prototype Parameter Navigator. Need to create
dictionary items first. May need to create VOC for
DsJobObjects.
Has anyone have any idea what are the metadata for the DsJobobjects and DsJobs since Install notes doesn't cover recreating these two UV tables?
I reckon I shall use
Code: Select all
CREATE.FILE DsJobobjects 19
ED DICT DsJobobjects ColNames
ED DICT DsJobobjects ColDerivation
ED DICT DsJobobjects ColFieldPos
...
ED VOC DsJobobjects
New record.
----: I<Enter>
0001= F<Enter>
0002= Enter_here_full_path_to_hash_data_file<Enter>
0003= Enter_here_full_path_to_hash_dictionary_file<Enter>
0004= <Enter>
Bottom at line 3.
----: FI<Enter>
CREATE.FILE DsJobs 19
ED DICT DsJobs ...
...
Code: Select all
SELECT
EVAL DS_JOBOBJECTS."@RECORD<3>" AS PARAMNAMES FMT '35L',
EVAL DS_JOBOBJECTS."@RECORD<4>" AS PARAMDEFAULT FMT '35L',
DS_JOBOBJECTS.OBJIDNO,
DS_JOBOBJECTS.OBJNAME,
DS_JOBS.NAME,
DS_JOBS.JOBNO,
DS_JOBS.JOBTYPE,
DS_JOBS.CATEGORY
FROM DS_JOBOBJECTS, DS_JOBS
WHERE DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO AND
DS_JOBOBJECTS.OBJNAME = 'ROOT' AND DS_JOBS.CATEGORY LIKE '%Jobs%';
Would you help me out please?
Thanks,
Ozgur