Page 1 of 1
extract query for where clause used in all jobs
Posted: Tue Dec 16, 2008 2:43 am
by 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,
Posted: Tue Dec 16, 2008 3:08 am
by 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;
Posted: Tue Dec 16, 2008 3:53 am
by 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
Posted: Tue Dec 16, 2008 4:03 am
by hamzaqk
logon to the project from the DS shell and execute it there
Posted: Tue Dec 16, 2008 4:08 am
by basu.ds
Thanks for the quick response.
My OS is windows and i dont have an unix environment to run this
Kindly suggest
Thanks
Posted: Tue Dec 16, 2008 5:33 am
by 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.
Posted: Tue Dec 16, 2008 7:48 am
by chulett
basu.ds wrote: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.
Posted: Tue Dec 16, 2008 10:46 am
by DeepakCorning
chulett wrote:
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
Posted: Tue Dec 16, 2008 11:01 am
by 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:
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.
Posted: Tue Dec 16, 2008 11:54 am
by DeepakCorning
Awesome , thanks for the information !!!!