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- 8 5225
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
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
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
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 compilatio n 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
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-
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
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
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- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Trevor Fairchild |
last post by:
I've got a program that parses text files. The text files come to me
in Unicode and they contain goofy characters that VB chokes on -
treats them as eof markers. I have already been through this issue in
another thread and I have received an answer that works (code is
below)
However, my test data thus far has been in the 10-50MB size range. I
now have a case where the text file is 650MB in size!
I need to convert it to ANSI for my...
|
by: Pierre Espenan |
last post by:
A have a long integer class. The built integer type within a conditional
statement returns bool false for int i=0 and bool true for any other non
zero value. I want my long integer class to have similar behavior. My
class looks like this:
#ifndef long_int_H
#define long_int_H
#include <string>
using namespace std;
typedef valarray<complex<double> > VCD;
|
by: Maria |
last post by:
Hello
I have not used db2 in a long time, and I have this very long insert
script, which is giving me an error of duplicated inserts.
My question is how can I put something in the statment so that it will
ignore this message ?
Thank you all
Maria
|
by: 73blazer |
last post by:
Hello,
I'm looking for a way to make some of my insert templates more readable
by placing comments in between the values. I cannot seem to find a way
to do this with DB2, is there a way? I'm probably missing something basic.
I have
insert into ken.snyder id1,id2,id3
values (
|
by: sapnsapn |
last post by:
There is a statement in c code I am reviewing
filenum = atoll(item->d_name + strlen(msgid_with_append_str) + 1);
In a certain snapshot,
item->d_name = 050707143827.AAAA.11810.00000001
msgid_with_append_str = 050707143827.AAAA.11810
strlen(msgid_with_append_str) = 23
resulting in,
| |
by: Me, Myself, and I |
last post by:
First off, i apologize if my terminology is off...
I am currently in a project that is basically a front-end to a
database. In coding this, I am taking into account that it has the
*potential* to be front-ended on multiple databases as well as
rendered in multiple browser types.
That being said, is there a pre-constructed class out there that I
can call from within my code to systematically "build" my SQL
statement and have it take...
|
by: Denis Correard |
last post by:
I have an application that create an insert statment on the fly (it would be to complex to create stored procedures for all senarios) like:
insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0)
so fare so good but I would like my statement to return the new PK_ID
So I tryed this:
Dim lNewVariable As String = "DECLARE @ID INT insert into insert into Table (field1, field2,field3 ,field4 ) VALUES ('Test','Test',0,0)...
|
by: Fredy Halter |
last post by:
the following code is not working:
std::complex<long doublex(1.,1.);
std::complex<long doubleresult(0.,0.);
result = 1./x;
std::cout << "x = " << x << std::endl;
std::cout << "r = " << result << std::endl;
|
by: zeebiggie |
last post by:
Good morning
I have a form with the controls in the insert statment below. table1 has an Auto increment primary key hence is omitted in the insert statment and form. Am getting the error It didnt work " .mysql_insert_id(); and not any other php or mysql error to tell me if anything is wrong the insert statment.
To top it up, I have an echo statment of the insert statment before it executes, when I copy and paste the insert statment from...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |