extract query for where clause used in all jobs
Moderators: chulett, rschirm, roy
extract query for where clause used in all jobs
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,
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,
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
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)
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;
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore
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.basu.ds wrote:Thanks for the quick response.
My OS is windows and i dont have an unix environment to run this
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Another Stupid question - when you say get to DS shell , what exactly am I doing?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.
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
Thanks
Deepak Patil
Convince Them Confuse Them .. What's the difference?
Deepak Patil
Convince Them Confuse Them .. What's the difference?
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.
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am