Statements with 2MB size

Hi all,

DB2 V9 runtime client acessing a DB2 UDB 8.2 FP 15 on LUW

According to this thread: (http://www.dbtalk.net/comp-databases-ibm-
db2/running-long-statements-java-c-406862.html), we could execute
statements with up to 2MB size.

My test statement is similar to:

insert into T (tuple1), (tuple2), ... , (tuple1000).

If N > 1000, then the following message is returned:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0101N The statement is too long or too complex. LINE NUMBER=4.
SQLSTATE=54001

MY statement heap size is 8192 * 4k pages.

Then I reduced the number of tuples per insert and tested the
following:

begin atomic
insert into T (tuple1), (tuple2), ... , (tuple600);
insert into T (tuple601), (tuple602), ... , (tuple1200);
end

That test was successfull.

Another test:

begin atomic
insert into T (tuple1), (tuple2), ... , (tuple600);
insert into T (tuple601), (tuple602), ... , (tuple1200);
insert into T (tuple1201), (tuple1202), ... , (tuple1800);
end

This statement has approximately 180k in size.

This test failed with SQL0101N. I have changed db2diag level to 4 and
the following message appeared:

2008-08-27-07.00.16.593645-180 I145179G383 LEVEL: Warning
PID : 22713 TID : 3330544 PROC : db2
INSTANCE: db2inst1 NODE : 000
FUNCTION: DB2 UDB, command line process, clp_savecmd, probe:12
MESSAGE : Command is too long to be saved in cmdhistory
DATA #1 : Command text truncated (max 20 chars), PD_CLP_TYPE_CMD, 20
bytes
begin atomic
insert

2008-08-27-07.00.18.972999-180 I145563G682 LEVEL: Info
PID : 22648 TID : 3057556272 PROC : db2bp
INSTANCE: db2inst1 NODE : 000
APPID : AC110126.AFE1.080827095804
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCAL sqlcabc: 136 sqlcode: -101 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLNQ01B
sqlerrd : (1) 0x8B0F000C (2) 0x0000000C (3) 0x00000004
(4) 0x00000000 (5) 0xFFFFFF9C (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 54001

The question is: is there a soft limit for the number of tuples per
insert ? If the statement could be up to 2M, why can´t I run an insert
with thousands of tuples ?

Can anyone suggest another way to reach the upper limit of 2M per
statement, inserting more tuples per statement ?

brunoalsantos wrote:

>
> The question is: is there a soft limit for the number of tuples per
> insert ? If the statement could be up to 2M, why can´t I run an insert
> with thousands of tuples ?

DB2 (the engine) supports a 2MB statement size; however unfortunately
the CLP does not.

> Can anyone suggest another way to reach the upper limit of 2M per
> statement, inserting more tuples per statement ?

Sure, do it via an application.

However, if your goal is truly just inserting large amounts of data
into the database, why not just use the IMPORT or LOAD utilities?

On 27 ago, 13:31, Ian wrote:
> brunoalsantos wrote:
>
> > The question is: is there a soft limit for the number of tuples per
> > insert ? If the statement could be up to 2M, why can´t I run an insert
> > with thousands of tuples ?
>
> DB2 (the engine) supports a 2MB statement size; *however unfortunately
> the CLP does not.
>
> > Can anyone suggest another way to reach the upper limit of 2M per
> > statement, inserting more tuples per statement ?
>
> Sure, do it via an application.
>
> However, if your goal is truly just inserting large amounts of data
> into the database, why not just use the IMPORT or LOAD utilities?

Hello Ian,

Thanks for the quick reply.

I have written a simple Java application using type-4 driver, and
tried to run a single insert with 10k tuples using (insert into T
values (T1), (T2), ... (Tn)).

The total statement size is approx 1M, which is below the upper limit.
However, SQL0101N (too long or too complex) was returned.

This does not seem to me a problem with the statement being long. My
impression is that somehow DB2 is unable to handle a long list of
values passed in a single insert statement.

My application is OLTP and several incoming connections are writing to
the same table. In average, 300 tuples are inserted but this number
can grow up to 5k rows.

Using LOAD doesn´t seem to be an option because it locks the table and
this would kill concurrency.
However, what benefits should I expect if import using used inside my C
++ application? What is the expected impact on concurrency with other
applications ?

Thanks,
Bruno.

> My application is OLTP and several incoming connections are writing to
> the same table. In average, 300 tuples are inserted but this number
> can grow up to 5k rows.
If your application is OLTP then you should use parameter markers and
not pass in literals causing the compiler to pin your CPU because every
statment will look different. Do you have triggers, RI?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab