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