I have a database where I have built a 4k,8k and 16k bufferpools and tablespaces.

I get the following error when I run the sql below to create a table
SQL0613N  The primary key, unique key, or table partitioning key identified by
"HASH_ID..." is too long or has too many columns and periods.  SQLSTATE=54008

CREATE TABLE LW_MESSAGE_SUMMARY (
  HASH_ID VARCHAR(2000) NOT NULL,
  CLIENT_ID int DEFAULT NULL,
  SUMMARY_DATE TIMESTAMP(0) NOT NULL,
  DIRECTION char(1) NOT NULL,
  PROTOCOL varchar(255) DEFAULT NULL,
  PROTOCOL_INFO varchar(255) DEFAULT NULL,
  PARM1_VALUE varchar(255) DEFAULT NULL,
  PARM2_VALUE varchar(255) DEFAULT NULL,
  STATUS varchar(255) DEFAULT NULL,
  MSG_COUNT int DEFAULT '0',
  TOTAL_DATA_SIZE BIGINT DEFAULT '0',
  DATA_MSG_COUNT int DEFAULT '0',
  DOC_COUNT int DEFAULT '0',
  DATE_CREATED TIMESTAMP(0) NOT NULL,
  SUMMARY_YEAR char(4) NOT NULL,
  SUMMARY_MONTH char(2) NOT NULL,
  SUMMARY_DAY char(2) NOT NULL,
  SUMMARY_HOUR char(2) NOT NULL,
  PRIMARY KEY (HASH_ID))

If I add the in tablespace the command works.
I also noticed that if I remove the primary key db2 places the table in the 4k tablespace, then it errors when I create the PK constraint.

I thought DB2 has the ability to chose the appropriate tablespace without explicitly declaring on the create table statement, which would be my preference?

Looking to understand this better, do I have to explicitly add the "in tablespace" on the create table statement or do something else that would allow db2 to correctly determine the tablespace to place the table in.

------------------------------
Wayne VanNewkirk
------------------------------

See https://www.ibm.com/docs/en/db2/11.5?topic=sql-xml-limits:

Maximum length of an index key, including all overhead = indexpagesize/4

You would need at least an 8KB page size; larger for futureproofing.

------------------------------
Jeremy Rickard
------------------------------

Your HASH_ID is two bytes too long for the primary_key. See docu:

The number of identified columns must not exceed 64. In addition, the sum of the length attributes of
the columns must not be greater than 2000 - n - 2m - 3d, where m is the number of varying-length
columns and d is the number of DECFLOAT columns in the key.

So you have 2000 - 0 - 2 - 0 = 1998 but your HASH_ID is 2000

Cheers!

------------------------------
Roy Boxwell
Senior Software Architect
Software Engineering GmbH
Duesseldorf
+4921196149675
------------------------------

Hello Wayne,

DB2 has a default tablespace: USERSPACE1 that is created when you run the CREATE DATABASE statement. The tablespace pagesize is the same as the PAGESIZE value used in the CREATE DATABASE (4K is the default). In the case of the CREATE TABLE statement does not state explicitly the IN <tablespace> INDEX IN <tablespace> the table and indexes are put by database manager automatically in USERSPACE1. As in your case the HASH_ID column in the Primary Key doesn't fit the default tablespace pagesize so the error is returned.

Without stating explicity the IN <tablespace> INDEX IN <tablespace> clause, the database manager put the table and its indexes in the USERSPACE1 tablespace automatically or if it doesn't exists (you could have chosen to drop it) the selection criteria is the first tablespace that has been granted to USE TABLESPACE TO PUBLIC. In this wat pay attention that sometimes the tablespace pagesize could not be right for the table or index you would create and the statement will fail.

Hope it helps.

Ciao. Roberto

------------------------------
Roberto Stradella
------------------------------

Thanks Roberto,

Interestingly.  My database is created with a pagesize of 32k and I do see the USERSPACE1 tbspace created with the 32k size.  Since the command fails I am assuming its not choosing this one by default.

When I created the DB I also created 3 tables spaces, 4k,8k and 16k.  I created the DB and tablespaces with db2inst1.  Showing my auths below, none of the ones I created have Public.  If I remove the primary key the create table command works and places the table in the 4k tablespace I built, but when I add the constraint that fails.

Does DB2 provide any documentation for how the tabalespace is selected if not explicitly stated?

------------------------------
Wayne VanNewkirk
------------------------------

Original Message:
Sent: Wed February 28, 2024 03:52 AM
From: Roberto Stradella
Subject: Error creating a table

Hello Wayne,

DB2 has a default tablespace: USERSPACE1 that is created when you run the CREATE DATABASE statement. The tablespace pagesize is the same as the PAGESIZE value used in the CREATE DATABASE (4K is the default). In the case of the CREATE TABLE statement does not state explicitly the IN <tablespace> INDEX IN <tablespace> the table and indexes are put by database manager automatically in USERSPACE1. As in your case the HASH_ID column in the Primary Key doesn't fit the default tablespace pagesize so the error is returned.

Without stating explicity the IN <tablespace> INDEX IN <tablespace> clause, the database manager put the table and its indexes in the USERSPACE1 tablespace automatically or if it doesn't exists (you could have chosen to drop it) the selection criteria is the first tablespace that has been granted to USE TABLESPACE TO PUBLIC. In this wat pay attention that sometimes the tablespace pagesize could not be right for the table or index you would create and the statement will fail.

Hope it helps.

Ciao. Roberto

------------------------------
Roberto Stradella
------------------------------

For a scenario without specifying PRIMARY KEY constraint, selection of the TABLESPACE IN which table is to be created is performed like this:

If this clause [IN clause] is not specified, the database manager chooses a table space (from the set of existing table spaces in the database) with the smallest sufficient page size and where the row size is within the row size limit of the page size on which the authorization ID of the statement has USE privilege.

For a scenario with specifying PRIMARY KEY constraint, previous discussion wrt length of PRIMARY KEY column apply.


Ref.:
https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-table

------------------------------
Jan Nelken
------------------------------

Original Message:
Sent: Wed February 28, 2024 10:23 AM
From: Wayne VanNewkirk
Subject: Error creating a table

Thanks Roberto,

Interestingly.  My database is created with a pagesize of 32k and I do see the USERSPACE1 tbspace created with the 32k size.  Since the command fails I am assuming its not choosing this one by default.

When I created the DB I also created 3 tables spaces, 4k,8k and 16k.  I created the DB and tablespaces with db2inst1.  Showing my auths below, none of the ones I created have Public.  If I remove the primary key the create table command works and places the table in the 4k tablespace I built, but when I add the constraint that fails.

Does DB2 provide any documentation for how the tabalespace is selected if not explicitly stated?

[db2inst1@ip-10-160-212-57 support]$ db2 "select * from syscat.tbspaceauth"

 

GRANTOR                                                                                                                          GRANTORTYPE GRANTEE                                                                                                                          GRANTEETYPE TBSPACE                                                                                                                          USEAUTH

-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------

SYSIBM                                                                                                                           S           PUBLIC                                                                                                                           G           USERSPACE1                                                                                                                       Y

SYSIBM                                                                                                                           S           DB2INST1                                                                                                                         U           TS_REG04_QAB2BI                                                                                                                  G

SYSIBM                                                                                                                           S           DB2INST1                                                                                                                         U           TS_REG08_QAB2BI                                                                                                                  G

SYSIBM                                                                                                                           S           DB2INST1                                                                                                                         U           TS_REG16_QAB2BI                                                                                                                  G

SYSIBM                                                                                                                           S           DB2INST1                                                                                                                         U           SYSTOOLSPACE                                                                                                                     G

SYSIBM                                                                                                                           S           DB2INST1                                                                                                                         U           SYSTOOLSTMPSPACE                                                                                                                 G

DB2INST1                                                                                                                         U           PUBLIC                                                                                                                           G           SYSTOOLSTMPSPACE                                                                                                                 Y

------------------------------
Wayne VanNewkirk
------------------------------


Original Message:
Sent: Wed February 28, 2024 03:52 AM
From: Roberto Stradella
Subject: Error creating a table

Hello Wayne,

DB2 has a default tablespace: USERSPACE1 that is created when you run the CREATE DATABASE statement. The tablespace pagesize is the same as the PAGESIZE value used in the CREATE DATABASE (4K is the default). In the case of the CREATE TABLE statement does not state explicitly the IN <tablespace> INDEX IN <tablespace> the table and indexes are put by database manager automatically in USERSPACE1. As in your case the HASH_ID column in the Primary Key doesn't fit the default tablespace pagesize so the error is returned.

Without stating explicity the IN <tablespace> INDEX IN <tablespace> clause, the database manager put the table and its indexes in the USERSPACE1 tablespace automatically or if it doesn't exists (you could have chosen to drop it) the selection criteria is the first tablespace that has been granted to USE TABLESPACE TO PUBLIC. In this wat pay attention that sometimes the tablespace pagesize could not be right for the table or index you would create and the statement will fail.

Hope it helps.

Ciao. Roberto

------------------------------
Roberto Stradella