I-descriptor was not compiled error

[kamesh]

Hi,
The requirement is that for all control log entries success/failure/aborted/reset I need the MSG.ARGS populated with jobname.inovationid if multi-instance job else jobname. Currently for every aborted entry MSG.ARGS is empty.

Below query works for me to get the jobname.invocationid from the RT_LOGXX file when job is aborted but the same when used in if condition throws me I-descriptor was not compiled error.
Working:
$DSHOME/bin/uv "SELECT SUBSTRING(MSG.TEXT FROM 5 FOR (CHAR_LENGTH(MSG.TEXT)-13)) FROM RT_LOG21 WHERE TYPE=5;"
Not Working:
$DSHOME/bin/uv "SELECT EVAL \"IF MSG.ARGS='' THEN SUBSTRING(MSG.TEXT FROM 5 FOR (CHAR_LENGTH(MSG.TEXT)-13)) ELSE MSG.ARGS\" FROM RT_LOG19 WHERE TYPE=5;"

IF MSG.ARGS = THEN Unidentified type "SUBSTRING".
SUBSTRING syntax error

I-descriptor "IF MSG.ARGS='' THEN SUBSTRING(MSG.TEXT FROM 5 FOR (CHAR_LENGTH(MSG.TEXT)-13)) ELSE MSG.ARGS" was not compiled.

[ray.wurlod]

EVAL expressions take BASIC functions, not SQL functions. There is no SUBSTRING function in BASIC (you can use SUBSTRINGS or the square bracket notation).

Code: Select all

SELECT EVAL "IF MSG.ARGS = '' THEN MSG.TEXT[5,LEN(MSG.TEXT)-13)] ELSE MSG.ARGS" FROM RT_LOG21 WHERE TYPE=5;

[kamesh]

Thanks a lot Ray!!! It works, modified SQL is as below.
$DSHOME/bin/uv "SELECT EVAL \"IF MSG.ARGS='' THEN MSG.TEXT[5,LEN(MSG.TEXT) - 13] ELSE MSG.ARGS\" FROM RT_LOG21 WHERE TYPE=5;"

Is there any easy way to get invocation id from RT_LOGXX file? Or is there any direct way to get elapsed time of each run from RT_LOGXX file? My only intention is to get the start/end times of concurrently running multi-instace jobs and order them by invocation ids so that correct elapsed time can be calculated per each invocation.

Thank you in advance!

[ray.wurlod]

Field #7 contains the invocation ID. It is not defined in the metadata, but can be accessed as

Code: Select all

EVAL "@RECORD<7>" AS INVOCATION_ID

If your invocation IDs are longer than 10 characters, then you might include FMT '20L' after the AS clause as well.

Invocation ID is also reported as part of the job name in the Control (job starting, job finished) entries.