All Type Sources and Target Audit report

sekharnaskar · Post by **sekharnaskar** » Tue Feb 12, 2013 3:01 pm

Hi,

How all type of Sources and Target Audit report or information could be created for whole Data Stage project Job wise? Means, how many different types of data sources and targets associated with all the DS Jobs. Is there any command available to get the information Job wise at Unix? In the designer/director level is it possible to generate ? For Windows environment what is the way to get the information ?

Thanking in advance.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 12, 2013 3:35 pm

The approach I'd take would be to report all the link objects from the DS_JOBOBJECTS table, limiting OLETYTPE to just those that indicate that the link is an input link or an output link (for example WHERE OLETYPE LIKE '%put%').

This query could be executed from the Administrator client Command window or from within a dssh shell on the Engine.

sekharnaskar · Post by **sekharnaskar** » Wed Feb 13, 2013 3:57 pm

Thanks Ray.

I am using SELECT DS_JOBOBJECTS.NAME AS OBJECT_NAME,DS_JOBS.NAME AS JOB_NAME, DS_JOBOBJECTS.OLETYPE FROM DS_JOBOBJECTS WHERE DS_JOBOBJECTS.OLETYPE LIKE '%put%';

It is giving the result like below, where from it is not possible to get the information about what type dabase / File Stage is used.

OBJECT_NAME......... JOB_NAME............ OLEType.............

DSLink12 SEQ_LOAD_ECMS CJSActivityOutput
DSLink19 J_LOAD_C_L_CUST_CMNC CTrxOutput
ToInsert EUCAN_CUST_SIP_Updat CSeqOutput
LdReady EUCAN_CUST_SIP_Updat CTrxInput
OTHERWISE2 S_MSCRM_CHINA_OUTBOU CJSActivityOutput

Is there any other DS_JOBOBJECTS types available, where the information regarding the database type could be retrieved.

bob7027 · Post by **bob7027** » Thu Feb 14, 2013 8:05 pm

I am curious about this topic.
Can you please brief in detail where to check this detail job logs, i mean for audit purpose, such as how many rows it passed, inserted or rejected etc.,
can you let me know where did you use this DS_JOBOBJECTS table?

ray.wurlod · Post by **ray.wurlod** » Fri Feb 15, 2013 12:49 am

DS_JOBOBJECTS, as its prefix suggests, contains only design-time information, not runtime information.

Runtime information is stored in tables with RT_ prefix, particularly RT_STATUS.

Structure of these tables is not documented, but may be inferred from inspection of various header files. It is not a consistent structure; different record types use fields for different purposes.

Runtime information is also able to be stored in the common metadata repository (operational metadata) and/or in the database associated with the DataStage Operations Console (the latter in version 8.7 and later only).

bob7027 · Post by **bob7027** » Fri Feb 15, 2013 9:47 am

Thank you Ray for the information.