How do you set the pagesize, codeset and table space when creating a database in DB2?

Question

I want to create a database in DB2.

I found some information on this page

I want to use only some options which are explained on the page:

• PAGESIZE
• CODESET
• TABLESPACE

Then, I want to add/configure the buffer pools.

How should the create SQL look ?

CRAETE DATABASE myDatabase 
USING CODESET UTF-8 TERRITORY US
COLLATE USING SYSTEM PAGESIZE 8192

CATALOG TABLESPACE 16K  -- Is the 16K ok?
USER TABLESPACE 16K  -- Is the 16K ok?
TEMPORARY TABLESPACE 16K;  -- Is the 16K ok?

Answer 1

To answer your question, yes you would specify the codeset, territory, collation, and pagesize like you do above. (And as a note, your link is off, you probably mean something like this which is the page for DB2 LUW 9.7 as an example.). By what you are setting up, you are specifying a 8K default pagesize. If you wanted a 16K default pagesize you would need to specify that.

And yes, 16K tablespaces are fine; it just depends on what you are doing.

But I will note that you can't catalog tablespaces. I think you meant to say that you want the CREATE TABLESPACE statement. And yes, if you do not specify a pagesize in your create tablespace statement, then it takes the default as specified by your CREATE DATABASE command.

Personally, I do not think there is anything wrong with specifying exactly what options you want with any statement, even if they are the defaults. For the following two reasons:

1. You know exactly what settings you are choosing to use and (hopefully) why.
2. In case IBM ever changes the default behavior, you are not left wondering why your scripts broke. You can continue to use the settings you choose and migrate to IBM's new defaults when you choose to (again, by explicit settings).

Answer 2

You should have just this and leave the rest to defaults:-

 CREATE DATABASE myDatabase