Statment too long or too complex

[J.Haan]

Hi all.

I'm currently coping with a problem on which I hope you could shed some
light.
Imagine the following:
I have table in DB2 8.1 (.5) which is defined as:
table test {
t1 smallint,
t2 smallint,
t3 smallint,
t4 smallint,
t5 smallint,
t6 decimal,
t7 decimal,
t8 decimal,
t9 decimal,
t10 decimal,
t11 decimal,
t12 timestamp
}

No triggers, no indexes, no constraints.

Now, I insert 370 rows (in one statement) (insert .. (), (), ())
Next, I get an error stating "Statement too long or too complex (sql code
101))
Not too strange, could be some internal limitation.

But next, I drop the table and recreate it, but instead of using
smallints, I use integers (4bytes in stead of 2).

Now, when I insert, no problem.
Strange, eh?

But wait, there's more.
When I create the same table again, but with bigints in stead of integers.
Again the 101 error.

The strange part is that integer lays between smallint and bigint when it
comes to memory used.

Trial-and-error revealed that with smallints, I could only insert 276 rows.

Is this solved by APAR? (currently using fp5).
Does anyone know what this is all about? Is this a bug, a feature,
something I missing here?

Help is greatly appreciated.
Greetings,

-R-

[Serge Rielau]

J.Haan wrote:

Hi all.

I'm currently coping with a problem on which I hope you could shed some
light.
Imagine the following:
I have table in DB2 8.1 (.5) which is defined as:
table test {
t1 smallint,
t2 smallint,
t3 smallint,
t4 smallint,
t5 smallint,
t6 decimal,
t7 decimal,
t8 decimal,
t9 decimal,
t10 decimal,
t11 decimal,
t12 timestamp
}

No triggers, no indexes, no constraints.

Now, I insert 370 rows (in one statement) (insert .. (), (), ())
Next, I get an error stating "Statement too long or too complex (sql code
101))
Not too strange, could be some internal limitation.

But next, I drop the table and recreate it, but instead of using
smallints, I use integers (4bytes in stead of 2).

Now, when I insert, no problem.
Strange, eh?

But wait, there's more.
When I create the same table again, but with bigints in stead of integers.
Again the 101 error.

The strange part is that integer lays between smallint and bigint when it
comes to memory used.

Trial-and-error revealed that with smallints, I could only insert 276 rows.

Is this solved by APAR? (currently using fp5).
Does anyone know what this is all about? Is this a bug, a feature,
something I missing here?

Help is greatly appreciated.
Greetings,

-R-

That is working as designed. Let me first assume that you stay below the
64K statement length limit appliable before V8.2.
By default any literal number is presumed by DB2 to be an INTEGER (I
think that's SQL Standard - important to know for function resolution!)
even if the value would fit into a SMALLINT. (By the same token strings
are VARCHAR, not CHAR - go figure....)
When you now INSERT such a constant into a column that is of a different
data type DB2 will have to inject an implicit cast for the assignment.
As the SQL compiler does that it figures that it can save some runtime
work by pre-computing the resulting expression. e.g.
CAST(5 /*integer*/ AS SMALLINT)
to 5 /*smallint*/
We call this "constant folding". The SQL Compiler remembers all
constants that it has come across in the hope to reuse them.
So it will now remmber the smallint 5 and the int 5. This is what drives
up the memory usage. At some point during compilation of teh SQL
statement DB2 frees the unreferenced values, but you never reach that spot.
So.. working as designed, in general not not harmful.
check out:
UPDATE DB CFG FOR <db> USING STMTHEAPSZ <biggervalue.. e.g. 1000>
This should work online if I recall correctly.. no need to restart or
disconnect.
In FP5 you should then be able to crank the statement up to 64k.

I do have a follow up comment though:
Are you composing these statements and fire them in a loop (like
inserting 10,000 rows 200 rows at a time? You would be a lot better
ofusing parameter parkers and host hostvariables and save the
compilation cost.
I have had very good experince with a set of insert statements with
differnt number of rows. E.g. 4096, 1024, 256, 64, 16, 4, 1.
This way you compile 7 statements total and your burn though the batch
quickly by taking the biggest available at any time.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

I do have a follow up comment though:
Are you composing these statements and fire them in a loop (like
inserting 10,000 rows 200 rows at a time? You would be a lot better
ofusing parameter parkers and host hostvariables and save the
compilation cost.
I have had very good experince with a set of insert statements with
differnt number of rows. E.g. 4096, 1024, 256, 64, 16, 4, 1.
This way you compile 7 statements total and your burn though the batch
quickly by taking the biggest available at any time.

The Spatial Extender does something like that in a slightly different way.
The statement is constructed for as many rows as possibly (limited only be
the 64K statement size). Now that statement is compiled and used as often
as possible. For the last rows, for which the statement is too big, we
build a new statement for fewer rows. Only 2 compiles instead of 7. ;-))

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

Knut Stolze wrote:

Serge Rielau wrote:

I do have a follow up comment though:
Are you composing these statements and fire them in a loop (like
inserting 10,000 rows 200 rows at a time? You would be a lot better
ofusing parameter parkers and host hostvariables and save the
compilation cost.
I have had very good experince with a set of insert statements with
differnt number of rows. E.g. 4096, 1024, 256, 64, 16, 4, 1.
This way you compile 7 statements total and your burn though the batch
quickly by taking the biggest available at any time.

The Spatial Extender does something like that in a slightly different way.
The statement is constructed for as many rows as possibly (limited only be
the 64K statement size). Now that statement is compiled and used as often
as possible. For the last rows, for which the statement is too big, we
build a new statement for fewer rows. Only 2 compiles instead of 7. ;-))

But the cleanup INSERT has a different # rows per batch. So that second
compile may not be reused. So it depends on the application

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Knut Stolze wrote:

Serge Rielau wrote:

I do have a follow up comment though:
Are you composing these statements and fire them in a loop (like
inserting 10,000 rows 200 rows at a time? You would be a lot better
ofusing parameter parkers and host hostvariables and save the
compilation cost.
I have had very good experince with a set of insert statements with
differnt number of rows. E.g. 4096, 1024, 256, 64, 16, 4, 1.
This way you compile 7 statements total and your burn though the batch
quickly by taking the biggest available at any time.

The Spatial Extender does something like that in a slightly different
way. The statement is constructed for as many rows as possibly (limited
only be
the 64K statement size). Now that statement is compiled and used as
often
as possible. For the last rows, for which the statement is too big, we
build a new statement for fewer rows. Only 2 compiles instead of 7. ;-))

But the cleanup INSERT has a different # rows per batch. So that second
compile may not be reused. So it depends on the application

Yes, that's right. In that particular scenario, we needed dynamic SQL in
any case because the table structure is only known at runtime. So reusing
compiled statements isn't really an issue in the first place.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[J.Haan]

On Mon, 24 Jan 2005 09:19:52 +0100, Knut Stolze wrote:

Knut Stolze wrote:

Serge Rielau wrote:

---

Thank you for your comments.
It's clear to me what the situation is.
Instead of cranking up the statement heap, I will iterate through the
inserts per n and insert those using a prepared statement. (I will have to
test if it helps when I cast the values during insert time).

-R-

[Knut Stolze]

J.Haan wrote:

On Mon, 24 Jan 2005 09:19:52 +0100, Knut Stolze wrote:

Knut Stolze wrote:
Serge Rielau wrote:

---

Thank you for your comments.
It's clear to me what the situation is.
Instead of cranking up the statement heap, I will iterate through the
inserts per n and insert those using a prepared statement. (I will have to
test if it helps when I cast the values during insert time).

Depending on what you want to do, the casting might actually be necessary.
For example, if you build a statement like this:

INSERT INTO ...
SELECT ..., udf(...), ...
FROM ( SELECT ...
FROM TABLE ( VALUES (...), (...), (...) ) AS t(...) )

In such a case, you'd have to CAST the values so that DB2 knows the proper
data type needed for function resolution.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

J.Haan wrote:

On Mon, 24 Jan 2005 09:19:52 +0100, Knut Stolze wrote:

Knut Stolze wrote:

Serge Rielau wrote:

---

Thank you for your comments.
It's clear to me what the situation is.
Instead of cranking up the statement heap, I will iterate through the
inserts per n and insert those using a prepared statement. (I will have to
test if it helps when I cast the values during insert time).

-R-

Explicit vs. implicit casting makes no difference to that respect.
What's bad about cranking up the heap a bit? The default is very low.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[J.Haan]

On Mon, 24 Jan 2005 09:09:24 -0500, Serge Rielau wrote:

>Serge Rielau wrote:

Explicit vs. implicit casting makes no difference to that respect.
What's bad about cranking up the heap a bit? The default is very low.

Cheers
Serge

Agreed. cranking the heap up a bit is not a bad idea since the default is
indeed quite low. But the routine that's causing problems can grow fairly
large, so if I increase it now, it might be too small again in a couple of
months, and we cannot have that. :-) So we have to alter the routine a bit
so that it iterates using blocks. This way it doesn't matter how many
records are inserted.

-R-