Internal MetaData Query

ariear · Post by **ariear** » Mon Jan 20, 2003 12:58 pm

Does anyone know how to extract the category name of the table definition that was loaded into a sequential stage in a job ? I need the information in Batch mode i.e. I can't use the Documentation tool it's manual only or the dsexport command-line - it's not job selective. The data i'm looking for is there it shows in dsx/xml of an exported job or in the doc tool database

Thanks,

Arie

ray.wurlod · Post by **ray.wurlod** » Mon Jan 20, 2003 4:36 pm

Table definitions are stored in a hashed file called DS_METADATA in the Repository.
This is keyed by a fully qualified table definition name (including category name), but there is a CATEGORY column (which is indexed).
What exactly are you trying to achieve, and where?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

ariear · Post by **ariear** » Mon Jan 20, 2003 4:48 pm

Thanks ,
I'm trying to achive a batch usage analysis. for instance I'd like to be sure that some job was compiled with a specific table definition (loaded) before deploying it let's say to the Production.

ArieAR

ray.wurlod · Post by **ray.wurlod** » Mon Jan 20, 2003 5:02 pm

OK, what you're going to need to do is to interrogate the design time information for the job (recorded in DS_JOBOBJECTS for DataStage 5.x and later) as well as for the table definition in DS_METADATA.
Unfortunately, the record layout in DS_JOBOBJECTS is not publicly documented. It is keyed by a three-component key, made up (for a stage) of the letter "J" (for job) or "C" (for container), the job number (decoded from DS_JOBS) and an internal representation of the stage (for example "V0S22").
Are you sufficiently competent with hashed files to inspect the layout to find out where the table definition name is stored? This is actually a link property, so you would need to check a pin record rather than a stage record in DS_JOBOBJECTS. Pin record keys have an internal representation of the form "V0S22P2".
Beware, too, that the actual storage model may change in future. There was a major paradigm shift between DataStage 4.x and 5.x.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

ariear · Post by **ariear** » Mon Jan 20, 2003 5:16 pm

O.K - I'm still in the SQL domain and got this far using the telnet :

>SELECT OLETYPE,NAME,@ID FROM DS_JOBOBJECTS;
OLEType............. Object name......... DS_JOBOBJECTS

CComplexFFOutput DSLink_deposits J1V0S0P1
CLookupInput DSLink_deposits J1V0S2P1

where the @ID has the pattern of P in it.
Where do i go from here ?

Thanks,

Ariear

ray.wurlod · Post by **ray.wurlod** » Mon Jan 20, 2003 6:56 pm

You need to specify the link name in a WHERE clause, and to extract the contents of field number 36 from the DS_JOBOBJECTS record.

SELECT NAME, EVAL "@RECORD" FMT '50T'
FROM DS_JOBOBJECTS
WHERE NAME LIKE '%LinkName%'
AND OBJNAME LIKE '%P%'
AND OLETYPE LIKE '%Seq%'
AND OBJIDNO = (SELECT JOBNO FROM DS_JOBS WHERE NAME = 'JobName');

Field number 36 contains a multi-valued list of table definitions from which the Columns grid on the link properties was loaded. There may be duplicates. (You can adjust the SQL to eliminate duplicates.)

Note that knowing that field number 36 contains the table definition name is no guarantee that this information will continue to be at that location in future releases of DataStage. Nor is it necessarily true for all link (pin) types; I have given you the answer for an output pin from a Sequential File stage. This is not official information from Ascential Software; it was obtained by inspection of the records in DS_JOBOBJECTS. No liability will be accepted either by me or by Ascential Software for its misuse.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

ariear · Post by **ariear** » Thu Jan 23, 2003 3:41 pm

Hi,

This is the first version of a routine that tries to establish which table definition was loaded into a stage

=======================================
$include DSINCLUDE DSD_CONST.H
$include DSINCLUDE DSD.H
$include DSINCLUDE JOBCONTROL.H
$include DSINCLUDE DSD_RTCONFIG.H

JOBNAME = Arg1
LASTTAB = ''
PATH = ''
Ans = ''

CMD = " SELECT EVAL '@RECORD' FMT '60T' FROM DS_JOBOBJECTS WHERE
OLETYPE LIKE '%CRecord%' AND OBJIDNO = (SELECT JOBNO FROM DS_JOBS WHERE NAME =
'" : JOBNAME : "') ;"
EXECUTE CMD CAPTURING CMDRST ;

x = convert ( char(13) : char(10) , @FM, CMDRST )

For i = 0 To count(x,@FM)
TABLENAME = x

IF TABLENAME = LASTTAB Then GOTO 10
LASTTAB = x
CMD = "LIST.ITEM DS_METADATA '": TABLENAME :"' HDR.SUP
COL.SUP NO.SPLIT"
EXECUTE CMD CAPTURING CMDRST ;

y = convert ( char(13) : char(10) , @FM, CMDRST )

For j = 0 To count(y,@FM)
IF y[1,3] = '057' Then
Ans = y[4, len(y)]
PRINT "PATH= " : y[4, len(y)]
End

NEXT j

10:
NEXT i

Return (Ans)
===============
It's been tested on 6.0r3 it seems that it's not suitable for 5.2.2

Any ideas will be most welcomed [:)]

ray.wurlod · Post by **ray.wurlod** » Thu Jan 23, 2003 5:05 pm

Don't evaluate the Count() function within the loop; evaluate it prior to the FOR statement.
I can't see how TABLENAME = x inside the loop will change for each iteration of the loop, I would have expected a construct such as Field(x,@FM,i,1). Also, dynamic arrays are 1-based, not zero-based (your VB oozing in here!), so the loop should begin at 1 not 0.
If you're going to do the thing in BASIC, I would be doing less LIST.ITEM and more direct READ of the records in question.
I take it "057" at the beginning of a table definition is (part of) a category name that you don't want to include in processing.
The output of PRINT is being captured into an obscurely named record in the &PH& directory. Why not use WRITESEQ to direct it to a file?
Nowhere in the code do you seem to specify the stage name. The class CRecord does not exist in DataStage 5.x, which is probably why nothing gets selected in that release.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

AmosR · Post by **AmosR** » Thu Jan 30, 2003 9:58 am

The code is just test example and therefore the use of PRINT commands. It's the quickest way I know to write code and debug in Datastage.
The routine gets job name as a single arg and returns the name of the file from which the Cobol copybooks were imported.
TABLENAME gets it's values from the outer loop, I created a dynamic array of table definitions names to get all Cobol copy books and then I saw that the value I was looking for has a 57 record id when running LIST.ITEM.
This routine can be very helpful in Endevor systems to do impact analysis on the ETL when a copy member changes.
How can I use READ command to get this field instead of LIST.ITEM ??

Cheers,
Amos