DS_JOBOBJECTS query for OCI stages

[ratikmishra1]

I'd like to develop a script that gets all the OCI stages of a project and provides the table name, mode (inert/update etc) for input/output stages.

Is it possible to write a query on DS_JOBOBJECTS to do this?

will appreciate any help.

Thanks
Rati

[kduke]

You will have to hack the table name. Each stage type is stored in OLETYPE. You SELECT DISTINCT OLETYPE FROM DS_JOBOBJECTS; to see which ones you have.

You need to figure out which field stores the table name. You can guess by doing EVAL "@RECORD<1>" AS TABLE_NAME change the 1 to 2,3... until you find the field with table names in it. You might have to limit it to a specific value like @RECORD<7,1>. That is how you hack your own solution.

If you post your OLETYPEs then I might be able to help.

[ray.wurlod]

You can save some work by constraining Kim's suggested query WHERE OBJNAME LIKE '%P%' so that only link ends are reported. Beyond that, however, you will need to hack as he indicated. For example:

Code: Select all

SELECT @ID, OLETYPE FROM DS_JOBOBJECTS WHERE OBJIDNO = '11' AND OBJNAME LIKE '%P%';
DS_JOBOBJECTS    OLEType.............

J\11\V0S2P2      CTrxOutput
J\11\V0S2P1      CTrxInput
J\11\V0S3P1      CHashedInput
J\11\V0S1P1      CODBCOutput

4 records listed.

This will give you some indication of what links you require. Table names, definitions, etc., are stored on output links. From memory the table name is in field number 6 (EVAL @RECORD<6>) but I can't be certain; my current site is not using OCI stages.

[ratikmishra1]

Thanks Kim and Ray.
I tried pritning upto @RECORD<9> But nothing showed up. The OLE type I am looking for is CCustomInput and CCustomOutput. I guess the OCI stages have those OLE type. (I may be wrong).

How do I know whether a particular record element for ex @RECORD<7> is multi-dimensional (From your suggestion to try at @RECORD<7,1>).

Here is what I've tried so far.
proddstage01:/local1/dsadm/Ascential/DataStage/DSEngine$cat try2.sql
bin/uvsh<<EOF
PERTM CASE NOINVERT
LOGTO dev
select
DS_JOBS.NAME AS JOB_NAME FMT '35L',
DS_JOBOBJECTS.NAME AS LINK_NAME FMT '35L' ,
DS_JOBOBJECTS.OLETYPE AS OLETYPE_NAME FMT '15L',
DS_JOBOBJECTS.OBJNAME AS OBJNAME_L FMT '15L',
eval DS_JOBOBJECTS."@RECORD<1>" AS COL1 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<2>" AS COL2 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<3>" AS COL3 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<4>" AS COL4 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<5>" AS COL5 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<6>" AS COL6 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<6,1>" AS COL6_1 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<6,2>" AS COL6_2 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<7>" AS COL7 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<7,1>" AS COL7_1 FMT '25L',
eval DS_JOBOBJECTS."@RECORD<8>" AS COL8 FMT '25L'
from
DS_JOBOBJECTS,
DS_JOBS
where
DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO
and DS_JOBS.NAME = 'CIF_HashBlds'
and DS_JOBOBJECTS.OLETYPE in ( 'CCustomOutput','CCustomInput')
;
Q

[kduke]

Keep going to you find your table. This is how you figure it out. You don't need 7,1. You need to find the right field.

If you post your OLETYPEs then maybe I can help. If I have some jobs with these OLETYPEs then I can find it faster by editing the records.