Error when accessing DS_JOBS table from DSExecute command

[ssunda6]

Hi All,

My requirement is to fetch the job names involved in a particular sequence.

I used the following query in Datastage administrator and it successfully returned the jobnames of a sequence.

Code: Select all

SELECT  EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS JOBS FMT '35L'  FROM   DS_JOBS WHERE    EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',40,'X') = '2' then 'Y' else 'N'" = 'Y'    AND NAME = '<sequenceName>' 

The same query when tried to execute in a transform function(code given below):

Code: Select all

Command = "SELECT  EVAL "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" AS JOBS FMT '35L'  FROM   DS_JOBS WHERE    EVAL "if TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',40,'X') = '2' then 'Y' else 'N'" = 'Y'    AND NAME = 'NgnADimCalDatasetLd' "

Call DSExecute('UV',Command,Ans,SystemRetCode)

is giving the following output:

Code: Select all

Test completed. 
Output is Illegal verb "=".
Result = Illegal verb "=".

The SQL statement is not getting executed properly in UNIX also. With the help of posts from this group, I used the following commands in UNIX:

cd $DSHOME
sh dsenv
bin/dssh

><sql stmt mentioned in the beginning>

The output I am getting is

Code: Select all

SQL+
TRANS ( Unable to open file "DS_JOBOBJECTS".
DS_JOBOBJECTS syntax error

I-descriptor "TRANS('DS_JOBOBJECTS','J\':@RECORD<5>:'\ROOT',31,'X')" was not compiled.

Please help.

Regards,
ssunda.

[kcbland]

For your second issue, you must be in a DS project to see the DS_JOBOBJECTS file, so add a "LOGTO yourproject" statement after getting into dssh.

For your first issue, my guess is that the quotes are getting messed up when passed to the shell via the API, which has been a historical issue. I'm not sure if it's still a problem in 7.5, but that would be my first guess. You may consider writing a DS BASIC function and directly read the DS_JOBS hashed file record for your job and formulate the output.

[ssunda6]

Thank you very much Kenn.

After using 'LOGTO project' statement, it got executed perfectly.

Regards,
ssunda.

[ray.wurlod]

You might mark the post as Resolved, then.