Prepared statement too long

[Arun Srinivasan]

I have an SP that forms the create table ddl statement by going thro
the syscat tables, and also the indexes. We are using this in
production. I have declared a varchar of 6000 bytes which stores the
ddl, and prepare stmt1 and execute it. It worked well on all tables
except one that is really big, that has 200+ column it throws the
SQL0433N value "create table...." is too long.

I've changed the stmtheap to 10 k and still no joy. Please help

[Serge Rielau]

Arun Srinivasan wrote:

I have an SP that forms the create table ddl statement by going thro
the syscat tables, and also the indexes. We are using this in
production. I have declared a varchar of 6000 bytes which stores the
ddl, and prepare stmt1 and execute it. It worked well on all tables
except one that is really big, that has 200+ column it throws the
SQL0433N value "create table...." is too long.

I've changed the stmtheap to 10 k and still no joy. Please help

We need more context. How is the CREATE TABLE glues together?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Arun Srinivasan]

On Nov 17, 4:52*pm, Serge Rielau <[e-mail]:

Arun Srinivasan wrote:

I have an SP that forms the create table ddl statement by going thro
the syscat tables, and also the indexes. We are using this in
production. I have declared a varchar of 6000 bytes which stores the
ddl, and prepare stmt1 and execute it. It worked well on all tables
except one that is really big, that has 200+ column it throws the
SQL0433N *value "create table...." is too long.

I've changed the stmtheap to 10 k and still no joy. Please help

We need more context. How is the CREATE TABLE glues together?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

What I did was to get the code for the copy_schema procedure that db2
uses and tweaked it so as to include primary key definitions and use
command line parameters for table/index space.
So I use a for statement, use the syscat.columns and glue each column
with its definition
like
set txt = txt || colname ......
at last, I get the complete create table statement, and
prepare stmt1 from txt
execute stmt1
is all I do, it had been working fine. I had deadlocking problems
initially, then I manually spaced the sp timing.

So this table claims_common is a denormalized one containing 232
columns, and so the statement goes well over 4000 characters. I have
changed the stmtheap, also the variable declaration og the 'txt' to
accomodate big DDLs, still no joy.

As a band aid solution (I don't know if this ought to have been used
in the first place), what I've done is to comment out that code and do
a create table <like <in <index in <statement, and it works
fine for now.
All I am concerned is the future, if something like this happens
again.The 0433N is bugging me now.