Database Creation

Database Creation

Post by Nag » Fri, 19 Jul 2002 18:14:31



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.

 
 
 

Database Creation

Post by Philip Nelso » Fri, 19 Jul 2002 21:52:12


Hello there.


> 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!

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


 
 
 

Database Creation

Post by Larry Menar » Fri, 19 Jul 2002 21:56:56


   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


> Hello there.


> > 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!

> 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

> > 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/te
mporary/containers/systemp1.dbf')

- Show quoted text -

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

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

> > Any points would be greatly appreciated.

> > Many Thanks.

> HTH

> Phil Nelson
> ScotDB Limited


 
 
 

1. Database Creation Help

Hello All;

 I need some direction on how best to create/setup my data on a small client
application.  MS SQL 6.5 creates a MSDBData and a MSDBLog device when it is
first installed.  I am going to create a small application that will access
data in a database called MYTESTTBLS.  Should I use the MSDBData and MSDBLog
as the devices for the data/log or should I create a new device, one for the
data and another for the Log file and associate my database to these?  I
plan on putting about a dozen tables in the MYTESTTBLS database, each of
which will have lots of data that will grow on a daily basis.  I'm new in
this area and want to set up MS SQL correctly so I need some guidance.
Also, if new devices (data/log) are required, how do you script this?  I
looked in all the on-line help that comes with MS SQL 6.5 and I can't seem
to find any place that says how to script a new data/log device.  I found
Create Database, but no Create Device syntax.  Any help in this matter would
be much appreciated.

Dean

2. One more question

3. SSCE database creation using DTS Package on SQL 2K

4. ATLANTA POSITIONS - FOURTH GENERATION SOFTWARE

5. client component database creation

6. database error

7. HELP !!!!! - database creation in application

8. Binding Collection Error

9. DataBase Creation

10. Database creation failed

11. Database Creation through scripting

12. Database creation suring install time

13. automating database creation