How to get all the tablenames used in DSX

[Andal]

Hi All,
I want to Know how to get all the Tablenames used in a DSX and in Viceversa, If i was given with a Tablename I have to find in which jobs that particular table is used.

I have tried List all files and tables used by jobs in a dsx file --> chucksmith --> www.anotheritco.com , but it gives me only the HashFileNames used in that DSX, I modified that and got my requirement, But it is Slow, So I have to come up with new queries.

So i tried like this

SELECT
DS_JOBOBJECTS.OLETYPE,
DS_JOBS.NAME AS JOB_NAME,
DS_JOBS.CATEGORY,
FROM DS_JOBOBJECTS , DS_JOBS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO
AND DS_JOBOBJECTS.OLETYPE IN ('CCustomInput','CCustomOutput');

But here i got Strucked, In some queries they are using
IF @RECORD<13,7> = 'ARRAYSIZE'

what is that <13,7> is used for.
Is that the line no after BEGIN DSRECORD?

We are using only DRS Stage for Source and Target , So that only i had used CCustomInput and CCustomOutput.

Please provide your suggestions.

[rajiivnb]

Hi,

Load the tables in the table defination category, and in the Manager,therez a option called Usage Analysis.

Select the table which u want to check from the table definition, then click the Usage Analysis option. U can see all the jobs, which uses this table.

Thnx

Rajiiv.

[ray.wurlod]

The structure of the DataStage repository tables is not in the public domain and may change at any release. Why you you want to know?

A normalized format of the repository is set up in doc_tool.mdb (for the Reporting Assistant). Open this with MS Access bypassing the startup macro (for example hold the Shift key when opening it), then choose the Tools option that displays the relationship between tables. This will give you some idea of what is stored in the repository, but not how.

Within the repository, a non-first-normal-form is used. This involves, conceptually, a cell whose data type is "table". Nested tables. For example, a table definition includes a collection of column definitions, each of which is made up of a collection of column properties. In the DataStage repository, all this information is in a single record, so that it can be retrieved with a single I/O.

[Andal]

Within the repository, a non-first-normal-form is used. This involves, conceptually, a cell whose data type is "table". Nested tables. For example, a table definition includes a collection of column definitions, each of which is made up of a collection of column properties. In the DataStage repository, all this information is in a single record, so that it can be retrieved with a single I/O.

Thanx for the immediate reply, I saw as you suggested but couldnt understand. I didnt get anything from there to satisfy my requirement. I explored the DSX, the table names are specified like this
BEGIN DSSUBRECORD
Name "TABLE"
Value "TBL_PROD_ERR"
END DSSUBRECORD

By identifying the name =TABLE And getting the corresponding value for it satisfies my requirement, But as i said in my previous post this method is slow, So i want to write query.

For that only I asked what is @RECORD and how it can be used, bcoz in many posts i found this @RECORD is used for getting the values and i suppose by exploring this i can get my requirement.

[battaliou]

If you have to use SQL to extract table usage from your repository, maybe you should consider setting up a few i desciptors? You'll have to issue the following commands from Administrator:

ED DICT DS_DSJOBOBJECTS ID1
I
0001 = I
0002 = @RECORD<13,7>
0003 =
----: FI

ED DICT DS_DSJOBOBJECTS ID2
I
0001 = I
0002 = @RECORD<14,1>
0003 =
----: FI


Now you can modify your SQL to:
SELECT
DS_JOBOBJECTS.OLETYPE, ID2 TABLE,
DS_JOBS.NAME AS JOB_NAME,
DS_JOBS.CATEGORY
FROM DS_JOBOBJECTS , DS_JOBS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO
AND ID1 = 'ARRAYSIZE' ;

I'd advise backing up your project first...

Regards
Martin Battaliou

[kduke]

You cannot rely on any of these to be consistant. The table is filled in column generated queries and fully generated but not user defined. We tend to copy jobs and use them as templates. If these fields are filled in meaning at some point this stage was column generated or fully and switched to user defined then the "TABLE" value in the DSX, Reporting Assistant , MetaStage and JobReport is wrong. The only way to get an accurate "TABLE" value is to parse the user defined SQL. The table value is used only in building the SQL in column generated and fully generated and not used in user defined.

Also if you are going to create I-descriptors then make the names of the dictionary items more descriptive. Call them ARRAY_SIZE or TABLE_NAME. I think you make the i-descriptors more complete. Field 2 of the I-descriptor is the formula. Make it something like

if @RECORD<14,7> = "ARRAYSIZE" THEN "" ELSE @RECORD<13,7>

for TABLE_NAME. Most of the time use OLETYPE to determine what value returns. OLETYPE is the record type.

if OLETYPE = "ODBC" then @RECORD<13,7> else ""

Is much more accurate way to build these fields. You are playing around with ASCL files. Do it cleanly.

[chucksmith]

The current version of my List all files and tables used by jobs in a dsx file tool should provide you with the names of all tables, hashed files, and sequential files referenced by jobs in your dsx file. In database stages where user defined SQL is used, the SQL is returned, giving you the option to manually parse it.

[ray.wurlod]

It's all going to change in the next ("Hawk") release of DataStage, so don't rely too heavily on what you can do now.

There is to be a common, open, repository for all Ascential products (DataStage, MetaStage, AuditStage, QualityStage and ProfileStage).

[Andal]

Thanks for all your responses, Now I am having some idea to proceed.