DSXchange

I would like to get a list of log entries for a job which were generated after a point in time. I am using a UNIVERSE stage with the below SQL.

SELECT @ID, "TYPE", TIMESTAMP, EVAL "FULL.TEXT[1,8]", EVAL "@RECORD<7>" FROM UNNEST RT_LOG2547 ON FULL.TEXT WHERE @ID >= 0 AND TIMESTAMP >= '2007-02-05 07:03:30' AND ((FULL.TEXT LIKE "Starting Job%" or FULL.TEXT LIKE "Finished Job%" or FULL.TEXT like "Job%aborted.") and TYPE=5) or (FULL.TEXT like "%Stopped" and TYPE=1) ORDER BY @ID

The job runs fine for projects with small number of jobs but on one project where there are 1700+ jobs, it failed intermittently with the following errors.

The main objective is to find all "start" and "finish" entries so we can calculate actual runtime of the job.

Any idea on the error as well as possible solution is greatly appreciated.

Thanks

SQLSTATE=S1000, DBMS.CODE=40030
[DataStage][SQL Client][UNIVERSE]I-descriptor "@RECORD<7>" was not compiled.

DataStage Job 2768 Phantom 20765
@RECORD < 7 >
Job Aborted after Fatal Error logged.
Attempting to Cleanup after ABORT raised in stage mdsGetJobElapsedTime.1.findFinishes
DataStage Phantom Aborting with @ABORT.CODE = 1

I have seen this before. I think you need to learn to create dictionary items and not use EVAL. It is less stable.

Does this occur reliably or occasionally? Is there a pattern - for example does it occur for some values of job number in RT_LOGnnnn and not for other values?

Why are you using UNNEST (normalizing on FULL.TEXT)? I don't think it's necessary for selecting.

Thanks for the quick response. The job is scheduled to run at 6:00 everyday on all DataStage servers. It usually fails on Sunday morning and on large (500+ jobs) projects. I have not been able to establish a pattern and will continue to track this.

As far as using the UNNEST to normalize FULL.TEXT, we used the code from a sample job and did not know that was not neccessary. I will try without it.

Thanks

kduke wrote:I have seen this before. I think you need to learn to create dictionary items and not use EVAL. It is less stable.

Kim,

I only want to bring back the first 8 characters of FULL.TXT so the EVAL "FULL.TEXT[1,8]" entry. Also, I need to find the invocation id and hence the EVAL "@RECORD<7>" entry. Don't know of another way to do this beside the EVALs. I appreciate any suggestions that you can provide.

Thanks

You could try using F7 instead of the EVAL statement to get field 7. I'm not at a client now to check, but am fairly certain that F1-F9 are declared for use as positional columns.

That only works in RetrieVe, not in DataStage SQL. What Arnd is referring to is that VOC contains D-type field descriptors F1 through F9, that can be used to refer to any field (from 1 to 9) in a record. You could certainly do this by specifying RT_LOGnnnn USING DICT VOC in your FROM clause; however, you could not then get at FULL.TEXT which is in DICT RT_LOG rather than in DICT VOC.

You could add a descriptor for field #7 to the log dictionary.

Now your query, with no EVALs, looks like this:

Ray - thanks for pointing that out; I didn't realize that the SQL syntax was being used.

We found out that "INVOCATION.ID" is one of the fields that we can use in the SQL. Also decided to bring the FULL.TEXT and not portion of it back. The new SQL now looks like.
EVAL is not used any more. We have redeployed this change and will continue to monitor to see if the problem will reoccur. Many thanks to you all.

Ray, in your post you have the following code
Can you let me know what is the effect of this condition?

Thanks

There are up to 3 log file entries that are used to hold internal data (including the purge settings) and that have different metadata than the other records. Since you don't want (or need) them, Ray added the filter to remove them. These records begin with "//"

Luckily for you, the ASCII code for "/" is 47 and the ASCII code for "0" is 48, so that your filter (WHERE @ID >= 0) will also exclude the control records.

Thanks for saving me some work. Just getting ready to add the filter below and found your latest reply. Ran the new code multiple times today against the largest project and the error has not occured. Will continue to monitor the jobs for a few more days.

The error won't occur. Because you have no EVAL expressions in your SQL, there are no I-descriptor expressions (which are introduced by EVAL) to compile.

This issue has been resolved. The change to remove EVALs works. Many thanks to all for your help :D

Ray I thought the slashes were backslashes?