DB2 LUW: Default tablespace/bufferpool

If you don’t specify a tablespace clause while creating a new table, the tablespace to which your table gets associated depends upon N number of factors.

Below example will help you understand that:

Step1: create a student table as below

db2 => create table student (sid int)
DB20000I The SQL command completed successfully.

Step2: find out tablespace & bufferpool associated with this STUDENT table

db2 => select substr(t.tabname,1,20) table_name, substr(ts.tbspace,1,20) Tablespace_name, substr(b.bpname,1,20) bufferpool_name, b.PAGESIZE from
syscat.tables t, syscat.tablespaces ts, syscat.bufferpools b where t.tabname = ‘STUDENT’ and t.tbspaceid = ts.tbspaceid and ts.BUFFERPOOLID = b.BUFFERPOOLID

TABLE_NAME TABLESPACE_NAME BUFFERPOOL_NAME PAGESIZE
——————– ——————– ——————– ———–
STUDENT TS4KDATA1 BUFFERPOOL1 4096

1 record(s) selected.
We haven’t anywhere specified bufferpool or tablespace names while creating STUDENT table. Theny why TS4KDATA1/BUFFERPOOL1 came?

Because, Database manager choose these values.

Let’s first look at why TABLESPACE_NAME defaulted to TS4KDATA1?

If you don’t specify “in tablespace” clause while creating a new table, DB2 following below rules to assign a tablespace:
Ref: http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html

***
If this 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.

If more than one table space qualifies, choose the table space in the following order of preference, depending how the authorization ID of the statement was
granted USE privilege on the table space:
1) The authorization ID
2) A role to which the authorization ID is granted
3) A group to which the authorization ID belongs
4) A role to which a group the authorization ID belongs is granted
5) PUBLIC
6) A role to which PUBLIC is granted

If more than one table space still qualifies, the final choice is made by the database manager.

Table space determination can change if:
Table spaces are dropped or created
USE privileges are granted or revoked
***

So, from above STUDENT example, since we haven’t specified any tablespace with IN clause, database manager took TS4KDATA1 as default tablespace

Step3: Let’s try an example by specifying a tablespace:

db2 => create table prod.student (c1 int) in ts4kdata2

db2 => select substr(t.tabschema,1,20) OWNER, substr(t.tabname,1,20) table_name, substr(ts.tbspace,1,20) Tablespace_name, substr(b.bpname,1,20)
bufferpool_name, b.PAGESIZE from syscat.tables t, syscat.tablespaces ts, syscat.bufferpools b where t.tabname = ‘STUDENT’ and t.tbspaceid = ts.tbspaceid and
ts.BUFFERPOOLID = b.BUFFERPOOLID

db2 => select substr(t.tabschema,1,20) OWNER, substr(t.tabname,1,20) table_name, substr(ts.tbspace,1,20) Tablespace_name, substr(b.bpname,1,20)
bufferpool_name, b.PAGESIZE from syscat.tables t, syscat.tablespaces ts, syscat.bufferpools b where t.tabname = ‘STUDENT’ and t.tbspaceid = ts.tbspaceid and
ts.BUFFERPOOLID = b.BUFFERPOOLID

OWNER TABLE_NAME TABLESPACE_NAME BUFFERPOOL_NAME PAGESIZE
——————– ——————– ——————– ——————– ———–
DB2INST1 STUDENT TS4KDATA1 BUFFERPOOL1 4096
PROD STUDENT TS4KDATA2 BUFFERPOOL2 4096

2 record(s) selected.

Step4: Why Bufferpool defaulted to BUFFERPOOL1/BUFFERPOOL2?

While creating a tablespace you have to associate a bufferpool as below. Of course, you can change this later.

create bufferpool BUFFERPOOL1 size 3000 pagesize 4096
alter tablespace TS4KDATA1 bufferpool BUFFERPOOL1

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: