How to fetch Meta data of table or file to a file

rhaddur · Post by **rhaddur** » Fri Mar 20, 2009 3:32 am

I want to fetch metadata of any source or target stage

of type db2 or seq file to xls file

kduke · Post by **kduke** » Fri Mar 20, 2009 10:47 am

Not sure I understand your question but normally you need to understand the system tables in order to do this. DB2 system tables are a little different based on the version of DB2. UNIX versions are different than mainframe. I have these SQL statements for several databases if you want them. Post a reply and maybe I will post the SQL.

chulett · Post by **chulett** » Fri Mar 20, 2009 10:56 am

'Fetch' why, for what purpose?

rhaddur · Post by **rhaddur** » Thu Mar 26, 2009 7:01 am

chulett wrote:'Fetch' why, for wwe able to get job parameters using

SELECT
EVAL DS_JOBOBJECTS."@RECORD<14>" AS Param_Name,
EVAL DS_JOBOBJECTS."@RECORD<16>" AS Prompt,
EVAL DS_JOBOBJECTS."@RECORD<19>" AS Type,
EVAL DS_JOBOBJECTS."@RECORD<17>" AS Default_Value,
EVAL DS_JOBOBJECTS."@RECORD<18>" AS Help_Text
FROM
DS_JOBOBJECTS , DS_JOBS
WHERE
DS_JOBOBJECTS.OLETYPE='CJobDefn' AND DS_JOBS.JOBNO=DS_JOBOBJECTS.OBJIDNO AND DS_JOBS.NAME='<jobName>';

then what is the way to get the

fetch Meta data of souce or target stage to a file hat purpose? ...

Because our We want to prepare the test data , based on the metadata
for arround 100 jobs

kduke · Post by **kduke** » Thu Mar 26, 2009 9:13 am

This SQL statement will return job parameters. Exactly what metadata do you need. You need source and target table names? You need column names too? You need run time metadata like job start and end times? You need run time parameter names and values?

Exactly what do you need and why?

rhaddur · Post by **rhaddur** » Tue Mar 31, 2009 5:03 am

kduke wrote:This SQL statement will return job parameters. Exactly what metadata do you need. You need source and target table names? You need column names too? You need run time metadata like job start and end t ...

Thanks for your reply

I am expecting the query , which will give the,

Table names and its meta data

like Column Name , Key, SQL type

kduke · Post by **kduke** » Tue Mar 31, 2009 9:38 am

It is not that simple. Each stage type can be stored in a different format. Most PX jobs have a custom stage type so they may be easier to extract. The table name can also be an issue if you use user defined SQL.

If this was easy then we would all have a canned solution. Maybe it is easier in version 8. Not sure.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 31, 2009 4:48 pm

It's actually more difficult in version 8, for example because DS_METADATA (which holds table definitions in earlier versions) now holds only category (folder) "pathnames" - the actual table definitions are in the unified metadata repository for Information Server.

The easiest way is probably a routine that ends up invoking DSGetLinkMetaData(), but that will only give the column definitions, not the table name or SQL.

You can, of course, obtain the information from the job configuration records, in particular the INPIN or OUTPIN records from RT_CONFIGnnn table for job number nnn. Or you can get it from the design records in DS_JOBOBJECTS, but not from the ROOT record. Again you'll need the link records.

None of this is documented. Reverse engineering is explicitly prohibited in the licensing agreement.

rhaddur · Post by **rhaddur** » Wed Apr 15, 2009 10:16 am

ray.wurlod wrote:It's actually more difficult in version 8, for example because DS_METADATA (which holds table definitions in earlier versions) now holds only category (folder) "pathnames" - the actual table definitio ...

When I am running the following query

SELECT * FROM DS_JOBOBJECTS , DS_JOBS WHERE DS_JOBS.JOBNO=DS_JOBOBJECTS.OBJIDNO AND DS_JOBS.NAME='job_name';

Getting following recods

record 1

Object type........ J
Object record id... 1911
Object record name. V0S63P2
Description........
Job name........... job_name

Description........
No................. 1911
Category........... Test
Object type........ J
Object record id... 1911
Object record name. V0
Description........
Job name........... job_name

Records 2

Description........
No................. 1911
Category........... Test
Object type........ J
Object record id... 1911

Object record name. V0S63
Description........
Job name........... job_name

Description........
No................. 1911
Category........... Test

my query

What exactly following values

Object record name. V0S63P2

Object record name. V0S63

ray.wurlod · Post by **ray.wurlod** » Wed Apr 15, 2009 1:53 pm

There's not a whole lot you can do with the knowledge, because it's more for the internal management of jobs, but here goes...

VnSmPk is the DataStage Repository ID (DSRID) of an object.

V is a "view" - the basic job design is always V0 and containers are numbered V1, V2, and so on.

Sm is stage number m in the job, where m is the order in which added to the job design (the same number as used in the default name for the job).

Pk is a "pin", or one end of a link. VnSmPk is that end of the link that connects to stage VnSm.