Search code examples
db2db2-luw

How to increase row length in db2 column organized table

I want to run the following command to create a column organized table:

CREATE TABLE T0 (ABC VARCHAR(8000)) IN abc_tablespace organize by column

I get the following error:

SQL0670N  The statement failed because the row or column size of the resulting
            table would have exceeded the row or column size limit: "3920". Table space
            name: "ABC_TABLESPACE". Resulting row or column size: "8000".  SQLSTATE=54010

I have the extended_row_sz enabled. I checked and verified this. Not sure if this is only valid for row org tables or not. I do not want to enable DB2_WORKLOAD=ANALYTICS. I have just set INTRA_PARALLEL YES. Anyone know how I can create this column in an column organized table?

Solution

  • You would need to create your table in a tablespace with a larger page size. Typically Column Organized tables are created in a 32K page-size tablespace, although this is not mandatory. Setting DB2_WORKLOAD=ANALYTICS before creating a database https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t0061527.html sets the default page size to 32K. As you don't want to enable this parameter, you will need to create a 32K (or 16K or 8K) tablespace (and bufferpool) and create your table in it.