Metadata - list of all tables

[prabu]

Hi DataStage Folks,
Hope you all do great. i would like to know the following:-

1)Is there any table that lists all the available system table[internal tables] information of datastage?. something like in Oracle below.

Code: Select all

LIST DICT MYTOPLEVELMETADATATABLE 

:D

Code: Select all

SQL> desc dict
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)

2)i would like to get a list of jobs that use a particular oracle table, say TIME_DIM table. can this be derived from the metadata tables??


excuse me if i have posted this in a wrong forum


regards,
Prabu

[prabu]

sorry, forgot to add , is it possible to export these system tables to some other db say oracle.

regards,
Prabu

[meena]

Hi Prabu..
You can know which jobs used a particular table...Go to DS manager/table definations/go to your table/click on it and click usage analysis...You can see relationship of the table with the type ,category, name of the job and source...
Check it out...

[meena]

You want to export the systemtables from datastage to a database..Is it right...

sorry, forgot to add , is it possible to export these system tables to some other db say oracle.

regards,
Prabu

[prabu]

You want to export the systemtables from datastage to a database..Is it right...

that's correct

[prabu]

Hi Prabu..
You can know which jobs used a particular table...Go to DS manager/table definations/go to your table/click on it and click usage analysis...
Check it out...

that's great! thanks Meena. i'm curious to know, is the same kind of logic be extended to a file. say, i would like know all the jobs involed [either created or using] with a sequential file , say "primary_sales.lst".

[meena]

Yes Prabu...
You will do the same to a file too....Just select your file.and usage analysis..

Hi Prabu..
You can know which jobs used a particular table...Go to DS manager/table definations/go to your table/click on it and click usage analysis...
Check it out...

that's great! thanks Meena. i'm curious to know, is the same kind of logic be extended to a file. say, i would like know all the jobs involed [either created or using] with a sequential file , say "primary_sales.lst".

[kduke]

The system tables are part of the DataStage engine. The respoitory tables are what I think you want a list of. The repository tables usually start with DS or RT. Most of what you want is in either DS_JOBS or DS_JOBOBJECTS. You can access these through SQL. The DataStage engine is an old version of a database called Universe. There is a shell to query this database called dssh or uvsh. This has all been explained in great detail. Extracting this metadata is part of what is included in EtlStats. You need to search for any of these terms to see in greater detail how to get the information you need.

[ray.wurlod]

Or, at rather more expense than EtlStats, you can use the vendor's preferred tool, MetaStage. The benefit of the extra cost is that this tool can also tell you where else (apart from DataStage) these tables and files have been used.

[prabu]

Thanks Kim & Ray! very helpful explaination.

Most of what you want is in either DS_JOBS or DS_JOBOBJECTS. You can access these through SQL. .

coming back to my origianl question, is there any repository table that lists all available repository tables. something like a metadata table - data about data

regards,
Prabu

[ray.wurlod]

All table names are recorded in the project's vocabulary. Therefore

Code: Select all

SELECT "@ID" FMT '50L' FROM VOC WHERE "TYPE" IN ('F', 'Q');

[prabu]

Wow Ray, you are a genius! :D

[prabu]

Code: Select all

>SELECT DISTINCT "TYPE" FMT '50L' FROM VOC
SQL+
TYPE..............................................

V
K
X
D
R
PA

F
M
PQ
Q
PH
S

13 records listed.

>SELECT  "TYPE" FMT '50L' FROM VOC WHERE "TYPE" IS NULL
SQL+

0 records listed.

i have tried UNIQUE in place of DISTINCT. but it gives me a syntax error. and suprisingly DISTINCT dislplays NULL also. is the meaning of DISTINCT different in Datastage ??

i have tried UNION, BETWEEN etc and it works great
or
1)What is the SQL compatibilty that datastage repository supports?

[kduke]

If you type HELP SQL then you can see some of what is supported. UNIQUE and other commands are used by Pick/Prime style commands. Universe is a derivative of Prime Information which is a derivative of Pick. Some of these keywords are used in both SQL and Pick style commands so it is difficult to know when it will work and when it won't. I would say keep trying it. Your DISTINCT may have pulled up a blank and not a null. What is or is not null in Universe is sometimes hard to figure out.

[ray.wurlod]

There are no nulls. What you are seeing is a blank "TYPE". This is an incorrectly (or lazily) stored VOC record. Don't worry about it. Try

Code: Select all

SELECT "TYPE",COUNT(*) FROM VOC GROUP BY "TYPE";

(TYPE is a reserved word in DataStage/SQL used in the CREATE TABLE statement. That's why it must be a quoted identifier.)