Prepared statement too long

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Arun Srinivasan
    #1

    Prepared statement too long

    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
    Tags: None
    • Serge Rielau
      #2
      Re: Prepared statement too long

      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

        Comment

        • Arun Srinivasan
          #3
          Re: Prepared statement too long

          On Nov 17, 4:52 pm, Serge Rielau <srie...@ca.ibm .comwrote:
          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.

            Comment

            Previous template Next
            Working...