DSXchange

I have a set of jobs which uses ODBC stages to retrieve data from an informix database based on time intervals.

SQL used in the ODBC


When I manually run the sql query with the exact same intervals, I'll get different number of records. Example: in the job, I got 0 rows, but running the sql query gives me 150 rows. the job will give me less number of rows

I run the jobs daily on few tables and I don't always get this problem -- it might happen on table1 on 1 ocassion, table 19 on the other or even does not happen at all.

Can anyone tell me where the problem lies?

edit: the records are insert-only, there is no update/delete of records happening at any point of time. So theoretically the number of rows from the same SQL should gives the same result.

Since DataStage just passes the query through to the database, I would tend to assume that your runtime values for start_time and end_time are not correct for the invalid runs. Particularly as the error seems to return 0 rows - as if one of the 2 AND'ed conditions might be wrong. Check the parameter values on one of your invalid runs and then do a "view data" using those same values to see if the result is any different.

hi ArndW,

Thanks for the prompt reply.

I've tried the view data by using the same parameter values of the invalid run, and I got the same result of the SQL query (by that i mean different from the result of the run).

It did not return 0 row everytime. It happens something like this:

run_attempt|no_of_rows_returned|actual_no_of_rows
1|15|15
2|46|46
3|30|35
4|92|92
5|77|77
6|0|0
7|0|16
8|16|16
9|35|35

Which is weird as the error is not consistent. I've been monitoring this for quite sometime now and I still can't find the root cause.

Where did you get the invalid run parameters from - the only sure method is to look at the director log entries. The error does sound quite odd and we all hate sporadic problems. Can you think of any outside factor that correlates to the problems happening (time of day, phase of moon [just kidding], userid of person running query/connector to the DB, etc.) which might put some reason behind the occasional errors?

Yes. I did get the parameters from the log in Director.