Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: db2 create database command

    Can someone help me with the db2 create database command?

    I am comfortable with the notion of using on /some/location in unix for the database, however I am not comfortable with specifying locations for tablespaces, temporary tablespace managed by system or user.

    I have some examples:

    CREATE DB dbname
    ON location
    USER TABLESPACE MANAGED BY DATABASE USING (xxx)
    TEMPORARY TABLESPACE MANAGED BY SYSTEM USING (xxx);

    db2 create database db1 \
    user tablespace managed by database \
    using (FILE 'c:\db2inst\database1\tabsp1\container1' <#pages>,
    ... as many containers as you need/want...
    FILE 'c:\db2inst\database1\tabsp1\container<x>'
    <#pages>)

    but am not confident in the options #pages and what not.

    Also for dms containers, does the file (container) need to exist on the system

    For the first crack at this we are on AIX5.1 db2 7.2 FP6

    and help would be greatly appreciated

    thanks

    Mark

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    If you do not specify the tablespace definitions for catalog, user and temp tablespaces, they are created by default in the database directory under /NODEnnnn/SQLmmmm/SQLT0000.0/. If you have enough space allocated to the directory, it may work, however it is always a good idea to avoid contention with the database directory and put them elsewhere.

    The catalog tablespace has to have a 4k pagesize. For this reason you will need a sytem tempspace with 4k pagesize. If there are other tablespaces in the database with different pagesizes(8k, 16k etc) a tempspace needs to be created for each of these or atleast one with the largest pagesize with the knowledge that space will be wasted when the smaler pagesize is sorted.

    You can have DMS and SMS tablespaces. For a DMS tablespace, say in your eg,

    'c:\db2inst\database1\tabsp1\container1' <#pages>

    The directory c:\db2inst\database1\tabsp1\ has to exist as far as I know.
    If the ddl specifies the tablespace to have a 4k pagesize and if you specify 1000 pages then the container will have a size of 4MB. And if you specify 2 containers, that becomes 8M. To monitor usage, you can do a list tablespaces show detail and check for total pages, useable pages, used pages and free pages to monitor usage. To add space you will have to alter the tablespace and resize the db containers - increase only.

    On the other hand, you can specify SMS(managed by system) and not specify the number of pages and the tablespace will grow upto the filesystem size or ulimit for instance owner. If the ulimit for the id is, say 2G, then any one container will not grow above 2G even though there's space in the filesystem. If ulimit is unlimited, it will grow upto the filesystem size - then you'll have to keep an eye on how quick the filesystems are filling up to determine whether you need to allocate more space to the filesystems.

    Hope this helps.

  3. #3
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    This is what I wound up doing:

    create database avc on /archive/dev3/DB2/avcda1/avc USING CODESET 1252 TERRITORY
    US COLLATE USING SYSTEM
    user TABLESPACE managed by system using ('/archive/dev3/DB2/avcda1/avc/d1sms') E
    XTENTSIZE 16 PREFETCHSIZE 16
    catalog TABLESPACE managed by system using ('/archive/dev3/DB2/avcda1/avc/SYSCAT
    SPACE') EXTENTSIZE 8 PREFETCHSIZE 8
    TEMPORARY TABLESPACE managed by system using ('/archive/dev3/DB2/avcda1/avc/TEMP
    SPACE') EXTENTSIZE 32 PREFETCHSIZE 32;

    thanks for your help

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •