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:


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

How should the create SQL look ?


CATALOG TABLESPACE 16K  -- Is the 16K ok?
USER TABLESPACE 16K  -- Is the 16K ok?
TEMPORARY TABLESPACE 16K;  -- Is the 16K ok?
  • Why do you want to set PAGESIZE, CODESET, TABLE SPACE manually and not use the defaults? Do you have specific requirements for them? If so: What are your requirements? – Karin Dec 11 '12 at 12:38
  • 1
    @Karin For example I need more pagesize, not 4K. – Kayser Dec 11 '12 at 12:40

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).

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

  • PAGESIZE, CODESET AND ALSO TABLESPACES ARE IMPORTANT IN MY CASE. For example I want to have 8K PAGESIZE NOT 4K – Kayser Dec 11 '12 at 12:37
  • That will pick the defaults, but if you want to set them, that is not what you want. – Chris Aldrich Dec 26 '12 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.