Very Computer

Hi All,

I am trying to get to the bottom of getting my understanding right
on the database and tablespace creations:

I did suffer a bit in trying to search for the right documentation
from the varius redbooks...but I manage to get lost everytime!

Here's what I am out to do:

1. Create a database.
        - This would be have to be code set UTF-8
        - Setup archive logging of the LOG files
        - know how to specify the location for the archive log files.

I have managed the first sub-step with the following command:  

CREATE DATABASE TEST USING CODESET UTF-8
DFT_EXTENT_SZ 64
CATALOG TABLESPACE MANAGED BY SYSTEM
USING ( '/dsk0/db2/TEST/ts/syscat/containers/catalog0.dbf',
'/dsk1/db2/TEST/ts/syscat/containers/catalog1.dbf')
EXTENTSIZE 64 PREFETCHSIZE 32
TEMPORARY TABLESPACE MANAGED BY SYSTEM
USING ('/dsk0/db2/TEST/ts/temporary/containers/systemp0.dbf','/dsk1/db2/TEST/ts/temporary/containers/systemp1.dbf')
USER TABLESPACE MANAGED BY DATABASE
USING (FILE '/dsk0/db2/TEST/ts/user/containers/user00.dbf' 5000)
EXTENTSIZE 24 PREFETCHSIZE 48 ;

I am not sure of the other two.

I shall later on create separate tablespaces for all my objects and
assign them accordingly.

2. What is a bufferpool?
        I have seen that there is an IBMDEFAULTBUFFERPOOL created when I do a
'create database'... Is there a way I can create my own? What is it
used for?

Any points would be greatly appreciated.

Many Thanks.

Hello there.

What would be wrong with starting with the manuals rather than redbooks :
try the SQL Reference and the Command Reference from

http://www.software.ibm.com/data/db2/library

Quote:> Here's what I am out to do:

> 1. Create a database.
>    - This would be have to be code set UTF-8 - Setup archive logging of
>    the LOG files - know how to specify the location for the archive log
>    files.

> I have managed the first sub-step with the following command:

> CREATE DATABASE TEST USING CODESET UTF-8 DFT_EXTENT_SZ 64 CATALOG
> TABLESPACE MANAGED BY SYSTEM
> USING ( '/dsk0/db2/TEST/ts/syscat/containers/catalog0.dbf',
> '/dsk1/db2/TEST/ts/syscat/containers/catalog1.dbf') EXTENTSIZE 64
> PREFETCHSIZE 32
> TEMPORARY TABLESPACE MANAGED BY SYSTEM USING
> ('/dsk0/db2/TEST/ts/temporary/containers/systemp0.dbf','/dsk1/db2/TEST/ts/temporary/containers/systemp1.dbf')
> USER TABLESPACE MANAGED BY DATABASE
> USING (FILE '/dsk0/db2/TEST/ts/user/containers/user00.dbf' 5000)
> EXTENTSIZE 24 PREFETCHSIZE 48 ;

To setup archive logging you update a database parameter called LOGRETAIN
(or USEREXIT if you are going to use on for moving logs).

To specify a location for active logs update database parameter
NEWLOGPATH.

If you want to move archive logs away from the main directory you can use
a user exit.  Samples (called db2uext2) are in the sqllib/samples
directory.  You can use any language you want as long as the file is
called db2uext2 and is executable.  I've written ours in Perl.

Quote:> I am not sure of the other two.

> I shall later on create separate tablespaces for all my objects and
> assign them accordingly.

> 2. What is a bufferpool?
>    I have seen that there is an IBMDEFAULTBUFFERPOOL created when I do a
> 'create database'... Is there a way I can create my own? What is it used
> for?

A bufferpool is a memory area where database pages are stored to save
having to read and write syncronously to / from disk.  As such they are
great for performance.  See the "CREATE BUFFERPOOL" SQL statement.

There are various tuning strategies.  The simplest scheme is to only use
the IBMDEFAULTBP for the catalog and create separate BPs for the
temporary table space, for tables and for indexes.  After that you can
look at using separate pools for tables with different access profiles
(e.g. separate small lookup tables from sequentially read large tables).

Quote:

> Any points would be greatly appreciated.

> Many Thanks.

HTH

Phil Nelson
ScotDB Limited

   I second that.  Specifically, I think you should see the "Admininstration
Guide: Performance".  Look up "Bufferpool" and "Logging".
--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of All Things Natural

('/dsk0/db2/TEST/ts/temporary/containers/systemp0.dbf','/dsk1/db2/TEST/ts/te
mporary/containers/systemp1.dbf')