DataStage Engine commands?

[dsedi]

Hi all,

Do you have more information about DataStage Engine commands?
i need to fetch the job name, categroy,update action, and the array size and Rows per trasaction information for a list of jobs.
what could be the command that i need to issue in command interface of Ds administrator?

Thanks in advance
dsedi

[kduke]

Good luck. I doubt if Ray could get all that easily. Job and category are easy. They are already been posted. Do a search. I am sure some of the rest may be in Reporting Assistant or MetaStage. JobReport may pull that out as well. I would almost think an XML export or using Perl against the DSX file would work. Array size and transaction size should be easy to find if you know how to hack into DS_JOBOBJECTS records. Update action is a lot harder to hack. It is a probably a number like 1 to 7. You can put an odd value in array size in then go search for it and see where DataStage stores the value. It will all depend on the stage type on what field they use. How many stage types do you use?

[dsedi]

any comments on this Ray?

Thanks
dsedi

[mhester]

If you are referring to a stage other than ODBC, UV or UniData the metadata you require is stored in both the input and output stage. All of the records in DS_JOBOBJECTS that contain either CCustomInput or CCustomOutput in field 2 contain the information you seek.

This information is stored as multi valued associated data in both records. CCustomInput stores this information in fields 13 - -15 with 13 being the parent property and identifying how many values exist. The CCustomOutput stage stores this data in fields 12-14 and would have the same layout with 12 being the parent property.

In the input record field 14 gives you the property name and field 15 gives you the value. In the output record field 13 gives you the property name and field 14 gives you the value. Both are multi valued associated fields.

As far as getting at these values you have a couple of options. You can use SELECT (both SQL and Retrieve) syntax, although it will be a bit tricky if you do not understand multi valued fields.

I do not recommend writing custom routines to access these records since the chance exists that something could be corrupted, therefore your repository would be corrupted.

Regards,

[dsedi]

Thanks mike!

dsedi

[chucksmith]

Take a look at my tool, List all files and tables used by jobs in a dsx file. This may give you a starting point, or a short list of jobs and tables of interest.

You will find it on the DataStage Tools page at www.anotheritco.com.

There is also a newly posted tool that Lists routines called by jobs and routines in a dsx file.

[kduke]

Michael

Where are array size and transaction size stored in ODBC?

For custom stages:

Code: Select all

select 
   DS_JOBS.NAME AS JOB_NAME FMT '35L', 
   DS_JOBS.CATEGORY, 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L',
   EVAL DS_JOBOBJECTS."IF @RECORD<13,7> = 'ARRAYSIZE' THEN @RECORD<14,7> ELSE IF @RECORD<14,6> = 'ARRAYSIZE' THEN @RECORD<15,6> ELSE ''" AS ARRAYSIZE FMT '8R',
   EVAL DS_JOBOBJECTS."IF @RECORD<14,7> = 'TRANSSIZE' THEN @RECORD<15,7> ELSE ''" AS TRANSSIZE FMT '8R'
from 
   DS_JOBOBJECTS, 
   DS_JOBS
where 
   DS_JOBOBJECTS.OLETYPE LIKE 'CCustom%' 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
;

[mhester]

I knew it was a simple matter of placing this bit of information in front of Kim and there would be a nicely written SQL statement within hours :D

Thanks Kim!

[mhester]

Kim,

To answer your question regarding ODBC input and output stages, the following applies -

CODBCInput

F21 - Trans Isolation Level
F22 - Trans Size
F23 - Array Size

CODBCOutput

F15 - Trans Isolation Level

Trans Isolation can be -

• Value Meaning
  -1 All SQL statements are committed individually.
  0 No transaction isolation.
  1 Another transaction can access the data before it is committed.
  2 Acquire read or write lock so that other transactions cannot access the data.
  4 Acquire read locks on all data returned, and write locks on all modified data. Ensures that data does not change whilst transaction is in progress.
  8 Lock entire range of data affected by transaction.

Now give us another easy to use SQL statement

Regards,

[kduke]

Thanks a lot. This got flipped on me. I tried to trick Ray into doing all this work but once again Michael is too smart for me.

I will figure it out latter.

[mhester]

I would just like to reiterate that while some of this information can be easily gathered and used, other pieces of information are very difficult to retrieve without a very intimate knowledge of the repository metadata.

Others have pointed out numerous methods that are supported by the toolset like usage analysis, "right" clicking on a stage type to find out all the jobs where it is used etc.... I would like to empahsize that these should be used first since much of the information is contained within already established tools within DataStage.

It's certainly fun to hack away at the foundation of your project to gain a better understanding of how DS works, but this hacking can and does sometimes lead to corrupt projects that Ray and the others end up helping to recover!

Regards,

[ray.wurlod]

I usually add the caveat that the undocumented Repository structures are subject to change at any time without advice or consultation.

[mhester]

Ray,

Thanks for pointing that out and I meant to state that very fact. Every version does in fact add additional fields or will overload current fields depending on the version and if it is PX or server.

Good catch

Regards,

[ray.wurlod]

It's certainly fun to hack away at the foundation of your project to gain a better understanding of how DS works, but this hacking can and does sometimes lead to corrupt projects that Ray and the others end up helping to recover!

I should point out that this is a service we sell; it's not done for free through this forum. It's a highly skilled and time consuming process, and we do this (among other things) for a living.
Only last week I had to recover a project; not a hacked one, but one that got damaged (in the DS_JOBOBJECTS table) when a switch between the server and the SAN failed. It took six hours of intense work, and it wasn't that large a project. A couple of other files were also corrupted.

[kduke]

Michael

Here it is:

Code: Select all

select 
   DS_JOBS.NAME AS JOB_NAME FMT '35L', 
   DS_JOBS.CATEGORY, 
   DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L',
   EVAL DS_JOBOBJECTS."IF OLETYPE = 'CODBCInput' THEN @RECORD<23> ELSE ''" AS ARRAYSIZE FMT '8R',
   EVAL DS_JOBOBJECTS."IF OLETYPE = 'CODBCInput' THEN @RECORD<22> ELSE ''" AS TRANSSIZE FMT '8R',
   EVAL DS_JOBOBJECTS."IF OLETYPE = 'CODBCInput' THEN @RECORD<21> ELSE ''" AS TRANS_LEVEL FMT '8R'
from 
   DS_JOBOBJECTS, 
   DS_JOBS
where 
   DS_JOBOBJECTS.OLETYPE LIKE 'CODBCInput' 
   and DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
;