DSXchange

Posted: **Mon Apr 25, 2005 7:23 am**

Hi

I am running this command:

$DSBIN/uv "select TIMESTAMP, SEVERITY, FULL.TEXT from RT_LOG32 where EVAL \"@ID\" NOT LIKE '//%' order by @ID;"

Is there any way i can filter out only those entries for the latest run of the job.

Thanks,
Ashwin

Posted: **Mon Apr 25, 2005 7:34 am**

The first entry of a job run sequence is where the TYPE column is equal to "5".

Posted: **Mon Apr 25, 2005 7:37 am**

Code: Select all

select 
   TIMESTAMP, 
   SEVERITY, 
   FULL.TEXT 
from 
   RT_LOG32 
where 
   EVAL \"@ID\" NOT LIKE '//%' 
   AND @ID > CAST(EVAL "FIELD(TRANS('RT_LOG32','//JOB.STARTED.NO',1,'X'), @SVM, 1)" AS DECIMAL)
order by 
   @ID
;

Posted: **Mon Apr 25, 2005 1:23 pm**

You're really getting into this UniVerse/SQL now, aren't you? :D

Posted: **Mon Apr 25, 2005 1:37 pm**

Maybe. That is what they asked for.

Posted: **Mon Apr 25, 2005 2:06 pm**

Certainly is. :D

Posted: **Tue Apr 26, 2005 12:28 am**

Hi Kim,

First of all...thnx a lot

But can you throw some light on this error:

Code: Select all

$DSHOME/bin/uv "select TIMESTAMP, SEVERITY, FULL.TEXT from RT_LOG32 where EVAL \"@ID\" NOT LIKE '//%' AND @ID > CAST(EVAL \"FIELD('RT_LOG32','//JOB.STARTED.NO',1,'X')\" AS DECIMAL) order by @ID;"

Getting o/p:

Code: Select all

.
.
.
DataStage/SQL: CAST detected illegal data for the specified data type.
Program ".ITYPE.": pc = 0, Nonnumeric data when numeric required.  Zero used.
.
.
.

Also, can you tell where can I find documentation on this...I dont seem to find it in the DS PDF's.

Thanks,
Ashwin

Posted: **Tue Apr 26, 2005 12:47 am**

Your query is not the same as Kim's. You left out @SVM, 1 as the second and third arguments of the field function.

This stuff is not in the public domain. That's why you can't find it in the PDFs. We've come to learn the structures by inspection (hacking?).

Posted: **Tue Apr 26, 2005 1:05 am**

Thnx Ray....
hmm...Inspection

Posted: **Tue Apr 26, 2005 1:08 am**

Code: Select all

where EVAL \"@ID\" NOT LIKE '//%'

Code: Select all

CAST(EVAL \"FIELD(TRANS('RT_LOG32','//JOB.STARTED.NO',1,'X'), @SVM, 1)

Can u tell me what these actually do?

Posted: **Tue Apr 26, 2005 1:30 am**

Ashwin,

The EVAL statement evaluates a variable, so in this case it will check if the value of @ID is unlike '//%'.

The second statement is a bit trickier. The TRANS function is essentially a READ function, you give the filename, key and other attributes; the resultant record is a string separated by @FM and @VM characters, which the FIELD function then strips down to returning just the first value.

The EVAL, FIELD, TRANS I-type functions are UniVerse commands, they are not documented as part of the DataStage product but if you are still curious you can download all the UniVerse/DataStage database engine documents from IBM's websites (You can search this forum or IBM for links).

DSXchange

RT_LOGNN latest run only

RT_LOGNN latest run only