Working with Duke's ETL Stats package

[ozgurgul]

Hi there,

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".

for DsJobObjects table in UV repository

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".

Kim's annotation mentions that

Non-technical description:
Used to prototype Parameter Navigator. Need to create
dictionary items first. May need to create VOC for
DsJobObjects.

Yet there is no details about either DsJobObjects (not DS_JOBOBJECTS) or DsJobs (not DS_JOBS) user defined UV tables in VOC.

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 ...
...

And then separately populate DsJobs and DsJobobjects tables with select statements like?

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

[kduke]

DsJobs and DsJobObjects are q-pointers to DS_JOBS and DS_JOBOBJECTS. DataStage will not let you import from DS_JOBS or DS_JOBOBJECTS. You can trick it by creating an alias with a q-pointer.

ED VOC DsJobs
1:Q
2:
3:DS_JOBS

This is a valid q-pointer. This is a Universe concept. To use some of the more advanced parts of EtlStats then you need to get good with Universe.

After creating this q-pointer then:

SELECT * FROM DsJobs;

is same as:

SELECT * FROM DS_JOBS;

There is a folder which is called SqlScripts. This folder has other folders which create the dictionary items for DS_JOBS and DS_JOBOBJECTS to make SQL against these tables work.

Code: Select all

INSERT INTO DICT DS_JOBOBJECTS 
(
   FIELD, 
   CODE, 
   EXP
) VALUES (
   'MvCols', 
   'PH', 
   'ColNames ColDesc ColSqlType ColPrecision ColScale ColFieldPos ColKeyPos ColNullable ColDerivation ColParsedDerivation'
)
; 

This will create a multivalue association named MvCols. It needs all the dictionary items "ColNames ColDesc ColSqlType ColPrecision ColScale ColFieldPos ColKeyPos ColNullable ColDerivation ColParsedDerivation" created first. A multivalued association is a nested table within DS_JOBOBJECTS. These insert scripts are in a folder named ColDicts. Almost nobody sets all this up. It is not hard but Metadata Workbench does a lot better job of extracting this metadata.

There are about 40 jobs in EtlStats. You need a lot of this setup to make them all work. I think there are lots of old posts explaining this.

Good luck. Post more questions if you get stuck.

[ozgurgul]

Kim - Many thanks for picking my post. As per suggested, I've created an alias with a Q-pointers in VOC using TCL and now I can access both DsJobs and DsJobObjects tables.

Code: Select all

dsadm> cd $DSHOME
dsadm> . ./dsenv
dsadm> ./bin/uvsh
DataStage Command Language 8.1 Licensed Materials - Property of IBM
(c) Copyright IBM Corp. 1997, 2007 All Rights Reserved.
DSEngine logged on: Wednesday, July 15, 2015 11:19

>LOGTO MISDEV
>LONGNAMES ON
Your 'VOC' has been modified to use long file names.
>ED VOC DsJobObjects
New record.

----: I
0001= Q
0002=
0003= DS_JOBOBJECTS
0004=
Bottom at line 3.
----: FI
"DsJobObjects" filed in file "VOC".
>SELECT * FROM DsJobObjects;

Object type........ J
Object record id... 2923
Object record name. V0A7
Description........

Again many thanks for your help.

Regards,
Ozgur

[kduke]

Sure. No problem.