maximum length of SQL WHERE clause? (Abnormal Termination)

[davidthree]

Hi All

I have had problems with two of my jobs, which abort with the following error message:

Code: Select all

Abnormal termination of stage JobNAME..Transform detected

(where Tranform is the name of the job's transformer).

Both jobs have long conditions specified within the WHERE clause of the input Oracle OCI stage. I have fixed one job by reducing the length of the WHERE clause. For the other, however, this is not so feasable.

I have other jobs which have longer SELECT statements and run fine, but which have shorter conditions within the WHERE clause. I assume, therefore, that this is the cause of the problem.

Could anybody confirm this diagnosis, and/or offer some tips on ways in which I could solve the problem?

Thanks

Dave

[1stpoint]

Consider using the SQL File option instead of keying all that code into designer.

[1stpoint]

Or consider using a View instead.

[ray.wurlod]

Why are you being so quick to blame the length of the WHERE clause? Just because that was the fix in one job doesn't mean that it's going to be be the fix in all jobs!
You need to determine what the cause of the abnormal stage termination was. There may be information in the job log after you reset the job (note - not re-compile, reset). Or you could look in the &PH& directory for information.
As often as not abnormal termination of Transformer stage is caused by doing something illegal, such as trying to shoehorn 12 characters into a CHAR(10) column, or doing something with NULL where NULL is not permitted.
As they teach in umpires' school: pause, read and only then react.

[roy]

Hi,
I guess that if you try running your sql statement via any sql editor, you'll get an error if your sql is wrong.
you could also try to save your sql in a sequential file and get the exact syntax beeing passed to the DB or make a trace for the sql you send.
(in case you got some parameters in it)

by the way how long is your query?

[sdevashis]

I had that problem sometime back...

I couldnt figure out the problem but its wise to create views/materialized views in the staging area to achive the purpose.

Create a view and let datastage think that its a table...rest is done by database

[davidthree]

Hi Guys

Thanks for all the feedback.

Ray - I have split the job in two, thus creating two jobs, each of which has only half the original number of conditions in the WHERE clause. Otherwise, these two jobs are identical to the original one job, and combined, they perform the same function on the same data. These jobs work perfectly (and thus are a reasonable, if not ideal, workaround). This leads me to think that the problem once again lies in the WHERE clause (although, as always, there are other possibilities).

Roy - Yes, I ran the SQL through TOAD and the correct results were returned without any problems. The SELECT contains 1127 characters. The WHERE clause contains 735 characters. Reducing the WHERE clause by 175 characters removes the problem.

My main concern now is that the WHERE clause is likely to grow, as more data states are facilitated for. I imagine I will reconsolidate the jobs into a single job again and construct a view, as recommended by 1stpoint and sdevashis.

Thanks

Dave

[1stpoint]

It's a good practice that anytime you do any complex SQL with a WHERE clause to create a view instead. This way if you change the condition of the WHERE clause you do not need to recompile your datastage jobs.

In many installations I have been at, the DBAs enforce the creating of views for EVERY table so that you don't access the tables directly.

[roy]

hmm,
well I knew informix driver had 1024 characters limit, now it seems more have this issue
I can understand the thought of using views, then again it hides implementation from the designer view, so the I dea of spliting the job could be better (depending on your point of view) in that scence. on hte other hand if you can get better performance from a view and performance inhancement is a critical factor it could be concidered as well. (just make shure to document that in you job).

Good Luck

[ray.wurlod]

I would report that as a bug. DataStage should not impose any restriction that is not imposed by the database server or its client software.

[sri1dhar]

I believe this a bug in datastage. I don't know about version 5, but we had the same problem in version 6. In a user-defined SQL ,if column name contains oracle reserved words (delete,insert,update etc) the job used get aborted. Later Ascentail came up with a patch that fixed this bug.

[kcbland]

Yikes! Methinks thy SQL statement might doing too much work. I would guess that you've forsaken DataStage's transformation capabilities for SQL.

Just my hunch and opinion. File it where appropriate.