shell script to login to the project

[raja123]

Hi

I am running few commands on unix

$cd `cat /.dshome`
$. ./dsenv
$bin/dssh
>LOGTO <project_name>
>SELECT * FROM DS_JOBS ( here I am running few datastage shell commands.)

This is giving me the expected results.

This stuff I need to use in a shell script, so I created following shell script

$cd `cat /.dshome`
$. ./dsenv
$bin/dssh "LOGTO <project_name>"
$bin/dssh "SELECT * FROM DS_JOBS" (datastage shell commands I need to use)

However shell script is not giving me the expected results. It looks like $bin/dssh "LOGTO <project_name>" line of script is not taking me to project directory.

any help is appreciated.

[ArndW]

The problem is that the DS shell isn't getting it's input from the script. The easiest solution would be for you to skip the LOGTO by doing a CD to the project directory in the shell script, then use

Code: Select all

dssh "{command}"

to issue single commands that exit back out of the shell

[chulett]

The LOGTO does happen but then exits and only then is the second statement is issued, which defeats the purpose. You need to do multiple commands in the same session.

As Arnd noted, if you 'cd' to the project in question before launching the shell, the LOGTO is automatic. That way you're down to a single statement to run.

[raja123]

The LOGTO does happen but then exits and only then is the second statement is issued, which defeats the purpose. You need to do multiple commands in the same session.

As Arnd noted, if you 'cd' to the project in question before launching the shell, the LOGTO is automatic. That way you're down to a single statement to run.

I did "cd" to the project and was able to run the scripts, but i would like to use few administrator commands in shell script like:

I am running the following command from administrator command prompt and it is workin fine.


SELECT NAME FMT '35L', CATEGORY FMT '35L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL "@RECORD" LIKE '%ABC%');

However when I am running using the same command on dssh, it is giving me following error:

Table "DS_JOBOBJECTS" does not exist.

Please let me know how to get this work in shell script.

Thanks in advance.

[ray.wurlod]

The SQL query is syntactically valid. Please show us how you are trying to use it from the shell script.

[raja123]

The SQL query is syntactically valid. Please show us how you are trying to use it from the shell script.

I am trying to fetch all the jobnames and categories to a file which are using table "ABC", following is the script:

cd `cat /.dshome`

. ./dsenv

cd /etl/apps/ds752/Ascential/DataStage/Projects/<project name>

../../DSEngine/bin/dssh "SELECT NAME FMT '35L', CATEGORY FMT '35L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL "@RECORD" LIKE '%ABC%');" >> filename.txt

[ray.wurlod]

Double quotes within double quotes (the "@RECORD" in the EVAL clause) is the problem here. Try using single quotes around @RECORD since the SQL statement itself is already within double quotes.

Code: Select all

../../DSEngine/bin/dssh "SELECT NAME FMT '35L', CATEGORY FMT '35L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL '@RECORD' LIKE '%ABC%');" >> filename.txt 

[raja123]

Double quotes within double quotes (the "@RECORD" in the EVAL clause. Try using single quotes around @RECORD since the SQL statement itself is already within double quotes.

Code: Select all

../../DSEngine/bin/dssh "SELECT NAME FMT '35L', CATEGORY FMT '35L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL '@RECORD' LIKE '%ABC%');" >> filename.txt 

IT WORKED ... THANKS A LOT !! :D

[raja123]

Double quotes within double quotes (the "@RECORD" in the EVAL clause. Try using single quotes around @RECORD since the SQL statement itself is already within double quotes.

Code: Select all

../../DSEngine/bin/dssh "SELECT NAME FMT '35L', CATEGORY FMT '35L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE EVAL '@RECORD' LIKE '%ABC%');" >> filename.txt 

IT WORKED ... THANKS A LOT !! :D

I used this script to find out the jobs names and categories, which are using table ABC. This script is working fine, but it is doing case sensitive search. I need case insensitive search.

Like it should search all the job names, which are having tablename as ABC,abc,AbC or abC etc.

thanks in advance.

[ray.wurlod]

This is, of course, a new requirement. The change is trivial, involving an UPCASE function (you could also use an 'MCU' conversion). I have added an additional constraint on OLETYPE which, while strictly not necessary, will reduce the number of DS_JOBOBJECTS records examined.

Code: Select all

../../DSEngine/bin/dssh "SELECT NAME FMT '35L', CATEGORY FMT '35L' FROM DS_JOBS WHERE JOBNO IN (SELECT OBJIDNO FROM DS_JOBOBJECTS WHERE (OLETYPE LIKE '%Input% OR OLETYPE LIKE '%Output') AND (EVAL 'UPCASE(@RECORD)' LIKE '%ABC%'));" >> filename.txt 

[vsi]

I tried to do the same with below statments. For some reason it doesnt work. The terminal just hangs and only quits when I press Ctrl+C or Q

Is changing to project directory only solution???

Code: Select all

cd `cat /.dshome` 
. ./dsenv 
bin/dssh <<EOF > $File
LOGTO <project_name> 
SELECT * FROM DS_JOBS
Q
EOF

[ray.wurlod]

Be more precise about "doesn't work". What actually happens? What output is generated? Add a WHO command between the LOGTO and the SELECT.

Add the required terminator (";") to the SQL statement - it's prompting you to supply it, you supply "Q" which tells the SQL statement not to execute.

[vsi]

Hi ,

a) when I execute the code, I don't get any thing on the terminal. It appears to be hanged. Only way that I could come out of it is press Ctrl+C..
When I press Ctr+C. Optioins to exit TCL prompt are shown.

I tried to end the sql with ";", still the same result
I checked the re-directed file and its empty.

b) When I tried to run schema level query, without loging into the project i.e.

Code: Select all

SELECT PATH FROM UV_SCHEMA WHERE PROJE.........;

I get error such as -

SELECT not in VOC