Is there a limit on query length in Informix IDS 11.50?

If there is one, can it be increased?

Also can anyone suggest some information on how to optimize IDS 11.50
for longer queries to make them run faster?


We have some Cognos reports that send very long queries to IDS 11.50
(about 50,000 characters including space).

We use Informix Client SDK 3.50.

Cognos support insists that the query is too long for Informix and it
might be even though we're not getting an error that the query is
longer than necessary, it simply never stops running.


Thank you

Genti

Should be 64 KB.at least V7 etc had that limit..

why not test it; eq put it in dbaccess and set explain on!!
will also tell you hopefully what is going on;
may be you need to set optimization low;
i suspect a large nr of tables are joined??? causing the optimizer to
go crazy
or work....???


Superboer.


On 29 sep, 20:19, Gentian Hila <genti.t... (AT) gmail (DOT) com> wrote:

On 29 Sep, 19:19, Gentian Hila <genti.t... (AT) gmail (DOT) com> wrote:
http://publib.boulder.ibm.com/infoce...s_adr_0721.htm

Maximum size of an SQL statement 64K

http://publib.boulder.ibm.com/infoce...s_sqs_0658.htm

The maximum length of a DECLARE statement is 64 kilobytes.

I will try dbaccess.

No it's not a lot of tables joined, it's just a whole lot of
calculations that cognos does not do internally but sends them over to
database and apparently the version 8.4 we just got cannot be forced
to do them internally as 8.2 used to do
There are long case ... when and nested select statements

It's really complex too read it - as I said below : almost 50,000
characters in a query.

On Wed, Sep 29, 2010 at 3:49 PM, Superboer <superboer7 (AT) t-online (DOT) de> wrote:

On Wed, Sep 29, 2010 at 9:21 PM, Gentian Hila <genti.tech (AT) gmail (DOT) com> wrote:


50K is less than the normal limit which someone already stated is 64K.
So, the problem is not that the engine is "cutting" the query or something
like that (instead you should receive an error).
Naturally a 50K query is hard to read, optimize and understand. But you must
do it... You should get a query plan and check the execution of the query.
You can use SQLTRACE, EXPLAIN ON, onstat -g pqs etc. Of course, if you need
help you could open a PMR, but the job of technical support is not do the
DBA job for the customer. What I mean is that ideally the first analysis
should be made by you and then, if you suspect that the engine is making the
wrong choices you could ask for help.

Saying that it's too long for Informix is a simplistic and unproven
statement. Specially when some Cognos versions use IDS as their repository.
There is no such thing as a query too long. What exist are bugs which we
solve and wrong data models for the querys being run, which our customer
should solve (with our help if needed).

So you could check for sequential scans, try to recreate statistics on the
tables used etc.


Finaly, the 64K limit is a limitation of the SQLi protocol
(native/traditional Informix client/server protocol). Your version, and in
fact since 11.10, support DRDA, the open (although created by IBM) RDBMS
client/server protocol. This protocol has a maximum limit of 2MB (to the
best of my knowledge) and I was able to run queries with that that failed
with SQLi. What I mean is that if your queries get even largers, and given
that you're getting closer to the limit of SQLi, you may need to consider in
the future creating another engine listener using DRDA protocol.

Regards.



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...