Is there a limit to the length of the SQL Statement?

[peter_dickinson]

DataStage Server Edition 7.5.1

Hi There,

I'm wondering if anyone can provide an answer my question (in the subject line). I've seen some references to the issue on this forum, but no answer to the question.

We've encountered this problem recently when using a Sybase_OC stage. It wasn't possible to break up the SQL statement, so we ended up having to create a database view, which we then used at the source.

The reason I'm asking is that we're creating jobs that will have "dynamic" where clauses, created outside of DataStage, and it is possible that we may hit this limit. I'm hoping we may be able to trap this problem prior to running the DataStage jobs.

Cheers,

Pete

[kris]

Hi Pete,

According to the Oracle9i Server Reference Guide:

SQL Statement Length: 64 K maximum;
particular tools may impose lower limits.


Kris~

[peter_dickinson]

Hi Pete,

According to the Oracle9i Server Reference Guide:

SQL Statement Length: 64 K maximum;
particular tools may impose lower limits.


Kris~

Thanks for the info Kris. I didn't mention in my original post, but we're actually running on Sybase.
The issue we actually have, though, is that the SQL runs fine when we run it directly against the database, it's just when we use it as User defined SQL in a DataStage stage that it causes us problems.

Cheers,

Pete

[gpatton]

At one time there was a limit in the Sybase API of an 8K sql statement.

What does Sybase support say?

[peter_dickinson]

At one time there was a limit in the Sybase API of an 8K sql statement.

What does Sybase support say?

Many thanks for your suggestion. I reworked one of the jobs we were having problems with, substituting an ODBC stage for the Sybase_OC stage (as we were able to run the query via ODBC using WinSQL). This fixed the problem, so it seems that the issue is related to the Sybase_OC stage....or, as you pointed out, a possible limit in the Sybase API. I'll see if I can find out what the limit actually is, when I've a bit more time to spare.

Cheers,

Pete