List of JobNames from a Sequnce

[Sudhindra_ps]

hi All,

Am running Sequnce from a Unix Shell Script. [b]I need to generate list of job names belonging to an sequnce dynamically in a shell script[/b].
Can any DSExperts, please help me in how to obtain jobname list?

Thanks & regards
Sudhindra P S

[ArndW]

Welcome to DSXchange, Sudhindra.

This thread comes close to answering your question. I got it by entering "sequence list jobs" in the forum's search function.

[Sudhindra_ps]

hi Arndw,

Many Thanks!!!! The link which you provided is very helpfull in achieving list of job names by executing below mentioned SQL command through Datastage Administrator. [b][color=brown]Could you please further guide me on to how I can fire this query from my shell script and take its output in my script?[/[/color]b]

SELECT NAME||':'||EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L' FROM DS_JOBS WHERE NAME='TCISQJBTD' ORDER BY NAME ;

Thanks & regards
Sudhindra P S

[kduke]

Add a uvsh or dssh in front of the SQL with double quotes around all of it. You need to cd to the project dirctory first.

[kduke]

I noticed you have double quotes in your SQL statement. That gets a little more tricky. You may need a backslash in front of your embedded double quotes. You will have to try it.

Post your solution please.

[ray.wurlod]

It can be done without the TRANS() function using a "proper" join.

Code: Select all

SELECT DS_JOBS.NAME, EVAL DS_JOBOBJECTS.'@RECORD<31>' FMT '32L' COL.HDG 'Dependent Job(s)'
FROM   DS_JOBS,  DS_JOBOBJECTS 
WHERE  DS_JOBS.NAME = '<<SequenceName>>' 
AND    DS_JOBOBJECTS.OBJIDNO = DS_JOBS.JOBNO 
AND    DS_JOBOBJECTS.OBJTYPE = 'J' 
AND    DS_JOBOBJECTS.OBJNAME = 'ROOT'
AND    DS_JOBS.JOBTYPEIND = '2';

Using single quotes around the EVAL expression means that double quotes around the lot can be employed to run from the operating system command prompt/shell.

[Sudhindra_ps]

hi Kduke,

Thanks for providing before hand caution on double quotes in the query. When I execute Query using either 'uvsh or dvsh' it is not getting through.
It says "uvsh:is not found" and "dssh:not found". Could you please suggest what else should I take care in my shell script to get these "uvsh or dssh" to execute.

I have been changing to "Project Directory" as you have suggested also I have been calling "dsenv" file in my script.

Thanks & regards
Sudhindra P S

[chulett]

Those live in the $DSHOME/bin directory, so it either needs to be in your PATH or you'll need to prefix 'uvsh' or 'dssh' with it.

[Sudhindra_ps]

hi Chulett,

Thanks for the info you have provided. Am able to execute query but, it is throwing some syntax error as mentioned below. Could you please help me out in trouble shooting this error.

--------- Command Executed---------------------
/apps/Ascential/DataStage/DSEngine/bin/dssh SELECT NAME||':'||EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS DEPEND_JOBS FMT '35L' FROM DS_JOBS WHERE NAME='TCISQJBTD' ORDER BY NAME ;
--------- End of Command ------------------------
DataStage/SQL: syntax error. Unexpected verb. Token was "SELECT".
Scanned command was SELECT

Thanks & regards
Sudhindra P S

[Sudhindra_ps]

hi DSExperts,

Many thanks for all of you in providing me tips in such a nice way to achieve desired results.
Atlast am able to achieve output the way I required.

Chulett,
I just had to call $QRY in double codes--->"$QRY" and it worked fine.

Kduke,
Please find query modified as below to handle double quotes in shell scripts.

----- Start of Query-----
"SELECT NAME||':'||EVAL \"TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,
'X')\" AS DEPEND_JOBS FMT '35L' FROM DS_JOBS WHERE NAME='TCISQJBTD' ORDER BY N
AME ;"
----- End of Query -------

Please let me know incase if you guys need any more information on this. But, am very pleased the way you guys have supported me today.

Thanks & regards
Sudhindra P S

[kduke]

You are welcome.

[ray.wurlod]

I prefer my way of avoiding double quotes! :D