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
Original Message:
Sent: Tue February 27, 2024 12:52 PM
From: Wayne VanNewkirk
Subject: Error creating a table
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
------------------------------