Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    33

    Question Unanswered: The row length of the table exceeded a limit of "32677" bytes

    I am getting the above message while creating a table with200 columns with VARCHAR(256) in DB2 9.5 LUW. I have overcome this by changing the data type to LONG VARCHAR.
    But the problem is the literature in fixpack 1 for 9.5 says that the datatype LONG VARCHAR is deprecated. What is the way out of this problem.
    Please help folks.
    bs

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Right, LONG VARCHAR because it is deprecated (for quite a while already). One alternative is to use CLOBs instead.

    However, I would like to understand why you need 200 VARCHAR(256) columns. What kind of information is stored there? I am asking because it rather looks like a data model issue.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The replacement for LONG VARCHAR is CLOB. Both are stored in a separate area (not in the row on the data page with other columns) which is why it does not impact the 32677 page size limit (not counting page overhead). But performance will suffer since neither of these data types uses the bufferpool to cache pages, and all read and write I/O is synchronous to disk. Make sure you have file caching on for any tablespace with a LOB column (best to have a separate tablespace for LONG objects).

    You would be better off performance-wise if you created two tables without LOB’s or LONG VARCHAR and linked them together, but you will need more bufferpool space for the 32K page sizes and real memory to support it (do not use virtual memory for bufferpools).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Sep 2003
    Posts
    33

    Question

    Thanks for revert. Well, i may not be able to answer your question as this db is getting migrated from Oracle. Model questions are beyond me as this is a product in use for while supported on Oracle. Recently there is a move to enhance this product for support on db2 as well.

    Well, coming to CLOBs.. i am still getting the failure message.
    bs

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think you need to post the create table DDL and the exact error message. I would put the LONG data in a separate tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2003
    Posts
    33

    Question

    Well i could overcome the error by replacing CLOB with CLOB(256). I am not sure if this would call for any code changes in the application. Also the requirement is not to alter tables structure.
    Can somebody point out any trivial issues associated with this. Like support of CLI Drivers and dynamic sql etc.
    bs

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2 you must specify the maximum length, even in a CLOB. That is probably what the error is saying. It will not affect the code.

    You need to make sure that you specify that the data on the CLOB is logged, because I think LOB's are not logged by default (not sure). Please refer to the SQL Reference manual Vol 2 for details on create table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    CLOB and VARCHAR data types are treated differently when the database is accessed via JDBC. I suspect the same may be true with CLI access as well. Before you do any further changes try to test the application with the modified table.

    One workaround would be to create a view that casts CLOB columns to VARCHAR "on the fly". Obviously, there will be some performance overhead.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I would myself indeed also go for the "split in two tables" solution.

    Let's say you need
    Code:
    CREATE TABLE t
    ( col001 VARCHAR(256) PRIMARY KEY,
      col002 VARCHAR(256),
     ...
      col200 VARCHAR(256))
    Instead, create the following three objects:
    Code:
    CREATE TABLE t1
    ( col000 VARCHAR(256) PRIMARY KEY,
      col002 VARCHAR(256),
     ...
      col100 VARCHAR(256)) ;
    CREATE TABLE t2
    ( col001 VARCHAR(256) PRIMARY KEY,
      col101 VARCHAR(256),
      col102 VARCHAR(256),
     ...
      col200 VARCHAR(256)) ;
    CREATE VIEW t AS
    (SELECT col001, ..., col200 FROM t1 INNER JOIN t2 ON t1.col000 = t2.col001)
    Reading applications will not see the difference. For writing applications you'll need INSTEAD OF triggers.

    Alternatively (if that's an option), reduce the max. size of some of the columns such that the sum of the sizes is small enough. (Account for 1 additional byte per column without NOT NULL, and 2 additional bytes per VARCHAR column.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Peter.Vanroose
    Reading applications will not see the difference. For writing applications you'll need INSTEAD OF triggers.
    I think it could be a problem if the result set is wider than fits on a 32K page. But that would depend on the actual length of the data stored in the VARCHAR's.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •