Routine Sql to list all jobs linked to specific table-fails

[DSguru2B]

Can you post the entire routine? Is the sql in a single line or multiple lines?

[cundyp]

(single line)
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME,EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;"

[DSguru2B]

I think the double quotes is getting messed up.
Put a DSLogInfo() command right after the sql and see if the correct sql shows up in the log as you intended it to be.

[cundyp]

Sql without the EVAl works.

#INCLUDE DSINCLUDE JOBCONTROL.H
#INCLUDE DSINCLUDE DSJ_XFUNCS.H
* #INCLUDE UNIVERSE.INCLUDE ODBC.H
*---- Getting Directory path--------------*
strStatsFileLoc = DSGetParamInfo(DSJ.ME, "$HMK_DIRLOGS", DSJ.PARAMVALUE)
Call DSLogInfo("Statistics File Will be located at (strStatsFileLoc) :":strStatsFileLoc, DSJ.ME)

*---- getting Job Name-------------*
strJobName = DSGetJobInfo(DSJ.ME,DSJ.JOBNAME)
Call DSLogInfo("Job Name (strJobName) :":strJobName, DSJ.ME)
strWorking = DSGetParamInfo(DSJ.ME,"$HMK_DIRWORKING", DSJ.PARAMVALUE)
Call DSLogInfo("Working Directory (strWorking) :":strWorking, DSJ.ME)

*-----Getting Current Date ------*
Call DSExecute("UNIX",'echo `date "+%Y%m%d%H%M%S"`', Output, SystemReturnCode)
Call DSLogInfo(Output,DSJ.ME)
Jobrun_datetime = Output[1,14]
Jobrun_date = Output[1,4]:'-':Output[5,2]:'-':Output[7,2]
*------Parse Input args ------*
* "#POPAPPL.ANALYSIS_VERSION"
vTableName=Field(InputArg,'#',2)
FOUND= ' '
*------Searching for all jobs with vTableName ------*
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME,EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;"

execute cmd capturing output returning RtnValue

Lines = DCount(output,@FM)
Jobs=""
for i=3 to Lines
Space = Count(output,' ')
if Trim(Field(output,' ',1))<>"" Then Jobs := ',' : Field(output,' ',1): '|' : Field(output,' ',Space+1)
next i
*Ans = Right(Jobs,LEN(Jobs)-1)

*------- Writing to the File----------*
WriteFile:
strFileName = "SEARCH.":strJobName:Jobrun_datetime:".log"
strFile = strStatsFileLoc:'/':strFileName
OpenSeq strFile To objFileVar
Else Create objFileVar Else ErrorCode =1
WeofSeq objFileVar
WriteSeq "Start Search TableName for Jobs Report" To objFileVar Then
End
WriteSeq "vTableName: " :vTableName To objFileVar Then
End
WriteSeq "Jobs: " :output To objFileVar Then
End
WriteSeq FMT("Jobs: ","8L"):FMT(Jobs,"20L") To objFileVar Then
End
WriteSeq FMT("Lines: ","6L"):FMT(Lines,"6L") To objFileVar Then
End
WriteSeq "End Search TableName for Jobs Report" To objFileVar Then
End
CloseSeq objFileVar
Call DSLogInfo("Search file ":strFile:" created.",DSJ.ME)

ErrorCode = 0 ;

[cundyp]

(single line)
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME,EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;"

Question: The inner or evaluate quotes are the problem. Can i use escape characters to allow DataStage/Universe to accept the evaluate
or does anyone have a solution?

[DSguru2B]

I thought you said that it works without the EVAL?
Try putting the sql within DQUOTE() function. See if that works.

[cundyp]

HERE ARE THE TEST RESULTS USING DQUOTE (failed):
ORIGINAL SQL - One Line (Works in DS Admin):
cmd = "SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',DS_JOBS.CATEGORY,DS_JOBOBJECTS.NAME AS OBJECT_NAME,
DS_JOBOBJECTS.OLETYPE,
EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'" AS FOUND FMT '5L'
FROM DS_JOBS,DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO and FOUND = 'FOUND'
GROUP BY JOB_NAME,DS_JOBS.CATEGORY,OBJECT_NAME,DS_JOBOBJECTS.OLETYPE, FOUND;"

TEST DQUOTE IN PLACE OF QUOTES:
(Single Line - Test DQUOTE in place of outer quotes)
cmd = DQUOTE(SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',
EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'"
FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;)

(Compile Error)
0045 cmd = DQUOTE(SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',
EVAL DS_JOBOBJECTS."if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND'"
FROM DS_JOBS, DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;)
^
')' unexpected, Was expecting: ')', ',', ';'
WARNING: Variable 'SELECT' never assigned a value.
WARNING: Variable 'DS_JOBS.NAME' never assigned a value.
WARNING: Variable 'AS' never assigned a value.
WARNING: Variable 'JOB_NAME' never assigned a value.
WARNING: Variable 'FMT' never assigned a value.
WARNING: Variable 'EVAL' never assigned a value.
WARNING: Variable 'DS_JOBOBJECTS.' never assigned a value.
WARNING: Variable 'FROM' never assigned a value.
WARNING: Variable 'DS_JOBS' never assigned a value.
WARNING: Variable 'DS_JOBOBJECTS' never assigned a value.
WARNING: Variable 'WHERE' never assigned a value.
WARNING: Variable 'DS_JOBS.JOBNO' never assigned a value.
WARNING: Variable 'DS_JOBOBJECTS.OBJIDNO' never assigned a value.

1 Errors detected, No Object Code Produced.

(Single Line - Test DQUOTE in place of Evaluate Quotes)
rpcDSTableSearch2Log..JobControl (1):
cmd sql= SELECT DS_JOBS.NAME AS JOB_NAME FMT '35L',
EVAL DS_JOBOBJECTS.DQUOTE(if index(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 then 'FOUND' else 'NTFND')
FROM DS_JOBS, DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;

ERROR:
rpcDSTableSearch2Log..JobControl (1): Output= DataStage/SQL: syntax error. Unexpected symbol.
Token was "DS_JOBOBJECTS.DQUOTE".
Scanned command was FROM DS_JOBS , DS_JOBOBJECTS SELECT DS_JOBS.NAME AS JOB_NAME FMT "35L" ,
EVAL DS_JOBOBJECTS.DQUOTE

[DSguru2B]

Well, that did not work very well. I am currently not on a DS Machine. Some other kind soul needs to test it out by trial and error and give you a response. Lets just wait for the experts. Be patient.

[ray.wurlod]

You need double quotes and single quotes within the SQL command's syntax, therefore you need to surround the command itself with the third quote character - backslash. DQUOTE() yields double quote characters, which are used in the EVAL.

Code: Select all

cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, \
cmd := \ EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 THEN 'FOUND' ELSE 'NTFND'" \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;\

[cundyp]

Your previous post with the \ and := worked without any problems executing from a sequence routine stage. (THANKS Ray)
I then added in the AS FOUND logic and this errored out:

cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, \
cmd := \ EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 THEN 'FOUND' ELSE 'NTFND'" AS FOUND \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND FOUND = 'FOUND';\


Generated SQL from Director log:
rpcDSTableSearch2Log..JobControl (1): cmd sql= SELECT DS_JOBS.NAME AS JOB_NAME,
EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0
THEN 'FOUND' ELSE 'NTFND'" AS FOUND FROM DS_JOBS,DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND FOUND = 'FOUND';

Error from Director log:
pcDSTableSearch2Log..JobControl (1):
Output= IF INDEX ( @RECORD , POPAPPL.ANALYSIS_VERSION , 1 ) > 0 THEN FOUND ELSE NTFND
DataStage/SQL: Internal problem with ALIAS, submit GTAR.

Issue:
The AS FOUND connected with the EVAL is bad.

Pete

[ray.wurlod]

This is an internal problem with DataStage/SQL (GTAR = general technical assistance request). Try it without AS introducing the alias name, and try it with a different alias name.

Code: Select all

cmd = \SELECT DS_JOBS.NAME AS JOB_NAME, \ 
cmd := \ EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'POPAPPL.ANALYSIS_VERSION',1) > 0 THEN 'FOUND' ELSE 'NTFND'" GOTCHA\ 
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \ 
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO AND GOTCHA = 'FOUND';\ 

[cundyp]

Test Results (alias):
I tried many combinations renaming, omitting 'as' etc with the same result (GTAR).
ACTION: I am opening a case with IBM on this issue.

New problem, same SQL:
The SQL is not converting the vTableName variable consequently all result sets are NTFND.
I have tried many different syntax combinations and none work.

PS: If anyone can point me to an information source other than the Universe Manuals please do!

*------Parse Input args ------*
vTableName=Field(InputArg,'#',2)

*------Searching for all jobs with vTableName ------*
cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,':vTableName',1) > 0 THEN 'FOUND' ELSE 'NTFND'",\
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO\
cmd := \ ORDER BY 1,2;\

vTableName variable from Director Log:
rpcDSTableSearch..JobControl (1): vTableName= POPAPPL.ANALYSIS_VERSION

Original SQL from Director Log:
rpcDSTableSearch..JobControl (1): cmd sql=
SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,':vTableName',1) > 0
THEN 'FOUND' ELSE 'NTFND'", DS_JOBS.NAME AS JOB_NAME FMT '40L'
FROM DS_JOBS,DS_JOBOBJECTS
WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO ORDER BY 1,2;

Converted SQL from Director Log:
rpcDSTableSearch..JobControl (1): SQL = IF INDEX ( @RECORD , :vTableName , 1 ) > 0 THEN FOUND ELSE NTFND
IF INDEX(@RECORD,':v
TableName',1) > 0 TH
EN 'FOUND' ELSE 'NTF
ND'................. JOB_NAME................................

NTFND CreateDataSetJob
NTFND CreateDataSetJob

[ray.wurlod]

What are the quote characters in (@RECORD,':vTableName',1 meant to be doing? Since it's still inside the backquotes it's within the string. You're also missing a concatenation operator. Try

Code: Select all

cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,\ : vTableName : \,1) > 0 THEN 'FOUND' ELSE 'NTFND'",\ 
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \ 
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \ 
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO\ 
cmd := \ ORDER BY 1,2;\ 

[cundyp]

The \:vTableName:\ modification results in a syntax error:

*------Searching for all jobs with vTableName - Original SQL in Routine ------*
cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,\:vTableName:\,1) > 0 THEN 'FOUND' ELSE 'NTFND'",\
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;\

vTableName variable from Director Log:
rpcDSTableSearch2Log..JobControl (1): vTableName= POPAPPL.ANALYSIS_VERSION

Original SQL from Director Log:
rpcDSTableSearch2Log..JobControl (1): cmd sql=
SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,POPAPPL.ANALYSIS_VERSION,1) > 0 THEN 'FOUND' ELSE 'NTFND'", DS_JOBS.NAME AS JOB_NAME FMT '40L' FROM DS_JOBS,DS_JOBOBJECTS WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO;

Converted SQL from Director Log (syntax error):
rpcDSTableSearch2Log..JobControl (1): SQL = IF INDEX ( @RECORD , Word POPAPPL.ANALYSIS_VERSION.
POPAPPL.ANALYSIS_VERSION syntax error

I-descriptor "IF INDEX(@RECORD,POPAPPL.ANALYSIS_VERSION,1) > 0 THEN 'FOUND' ELSE 'NTFND'" was not compiled.

[ray.wurlod]

My error. The table name needs to be single-quoted.

Code: Select all

cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,'\ : vTableName : \',1) > 0 THEN 'FOUND' ELSE 'NTFND'",\ 
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \ 
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \ 
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO\ 
cmd := \ ORDER BY 1,2;\ 

or

Code: Select all

cmd = \SELECT EVAL DS_JOBOBJECTS."IF INDEX(@RECORD,\ : SQuote(vTableName) : \,1) > 0 THEN 'FOUND' ELSE 'NTFND'",\ 
cmd := \ DS_JOBS.NAME AS JOB_NAME FMT '40L' \ 
cmd := \ FROM DS_JOBS,DS_JOBOBJECTS \ 
cmd := \ WHERE DS_JOBS.JOBNO = DS_JOBOBJECTS.OBJIDNO\ 
cmd := \ ORDER BY 1,2;\