extract query for where clause used in all jobs

[basu.ds]

Hi

I want to extract all where clause used in a project or all jobs do we have any dsfunction or job design or any routine also help ful

Thanks in a advance,

[DS_SUPPORT]

There is no existing one for getting all the where clause used in the jobs.You need to get it from DS_JOBOBJECTS and it will vary based on your DB Stage.

The following query will list all the User defined SQLs and Where Clause used in a DRS Stage, like that you have to write it on your own for your DB Stage (OLETYPE will differ based on Stages)

Code: Select all

SELECT
 DS_JOBS.NAME AS JOB_NAME FMT '35L', 
 DS_JOBS.CATEGORY, 
 DS_JOBOBJECTS.NAME AS LINK_NAME FMT '50L',
 EVAL DS_JOBOBJECTS."@RECORD<14,2>" AS GEN_SQL FMT '10L',
 EVAL DS_JOBOBJECTS."IF @RECORD<14,2> = 'Yes'  THEN Convert(Char(10):Char(13),' ',@RECORD<14,6>)  ELSE IF @RECORD<14,2> = 'No' Then Convert(Char(10):Char(13),' ',@RECORD<14,3>) ELSE ''" AS WHERE_CLAUSE
 FROM   
 DS_JOBOBJECTS, DS_JOBS
 WHERE 
 DS_JOBOBJECTS.OLETYPE = 'CCustomOutput' 
 AND DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
 AND WHERE_CLAUSE <>''
 GROUP BY 
 JOB_NAME,
 CATEGORY,
 LINK_NAME,
 GEN_SQL ,
 WHERE_CLAUSE;

[basu.ds]

Dear ,

Where should i execute this query.
I tried in the administrator command mode and also the cmd prompt sqlplus.

Kindly let me know.
Thanks

[hamzaqk]

logon to the project from the DS shell and execute it there

[basu.ds]

Thanks for the quick response.
My OS is windows and i dont have an unix environment to run this
Kindly suggest

Thanks

[DS_SUPPORT]

You have to execute this from Administrator Command Prompt. Convert the query to a single line and execute it.

Or Else, create a job and use this SQL in the universe stage and pass the output to a file.

[chulett]

Thanks for the quick response.
My OS is windows and i dont have an unix environment to run this

Where did anyone mention you had to have UNIX to do this? You get to the "DS shell" from your operating system, regardless of what it is.

[DeepakCorning]

Where did anyone mention you had to have UNIX to do this? You get to the "DS shell" from your operating system, regardless of what it is.

Another Stupid question - when you say get to DS shell , what exactly am I doing?

I know this command can be executed from Admin tool but want to know how to do get into Ds shell and do it?

Thanks

[chulett]

This is similar to connecting to any database with a command line sql tool (like sqlplus and Oracle) - it gets you a shell where you can issue queries against the repository / database.

From your operating system, execute the "dssh" executable in the $DSHOME/bin directory. I can't check this but assume it is there as "dssh.exe" on a Windows server. On UNIX you should "source" the dsenv file first to make sure the environment is setup correctly:

Code: Select all

cd $DSHOME
. ./dsenv

Then either move to the Project directory you want to work with and launch from there, or launch from $DSHOME and then LOGTO the project in question once you are at the TCL prompt. Starting in the project directory gets you automatically logged onto that project.

If you ever do this and you get asked the (paraphrased) question "This is not setup for DataStage, do you want to do this now?" the answer is always NO. This means you've launched the shell from an improper starting point, back out and try again.

[DeepakCorning]

Awesome , thanks for the information !!!!