DB2 for Linux, Unix, and
Windows
- Ravi
Database Creation
CREATE DATABASE
$ db2 create database TEST
DB20000I The CREATE DATABASE command completed successfully.
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias
= TEST
Database name
= TEST
Local database directory
= /home/db2inst1
Database release level
= 10.00
Comment
=
Directory entry type
= Indirect
Catalog database partition number = 0
Alternate server hostname
=
Alternate server port number
=
$ db2 ? create database
If no path is specified when a database is created, the DB is created under DFTDBPATH
DFTDBPATH
Default database path configuration parameter
If no path is specified when a database is created, the DB is created under DFTDBPATH
DB2 UPDATE DBM CFG USING DFTDBPATH
/mnt/storage/DBPATH/
DFTDBPATH update will take effect immediately
Note: DFTDBPATH should exist locally on the box. dont give any NFS mounted paths.
CREATE DATABASE w/ Storage & dbpath
$ db2 create database DB1 ON '/mnt/storage/storage_db1' DBPATH ON
'/mnt/storage/DBPATH
DB20000I The CREATE DATABASE command completed successfully.
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias
= DB1
Database name
= DB1
Local database directory
= /mnt/storage/DBPATH
Database release level
= 10.00
Comment
=
Directory entry type
= Indirect
Catalog database partition number = 0
Alternate server hostname
=
Alternate server port number
=
DB2 Objects Location after default ROOT installation (10.5)
DB2 Object
SCOPE
Location
User: db2inst1
Product is installed in :
/opt/IBM/db2/v10.5 (INSTALLDIR)
Instance directory: /home/db2inst1
(INSTHOME)
DBPATH: /home/db2inst1
Instance Home directory
Instance
/home/db2inst1
Instance Information
Instance
/home/db2inst1/sqllib
DBM Configuration File
Instance
INSTHOME/sqllib/db2systm
Database Directory,
which contains
Database
DBPATH/db2inst1/NODE0000/
SQL00001
Database
DBPATH/db2inst1/NODE0000
1/SQL00001/SQLDBCON
- Buffer pool, Storage path,
tables space information
- History information, Log control
files
DB Configuration File
DB2 Commands
Product (or) Instance
INSTALLDIR/bin
DB2 Error message file
Instance
INSTHOME/sqllib/db2dump/db
2diag.log
Directory for event
monitor data (DB
Specific)
Database
DBPATH/db2inst1/NODE0000/
SQL00001/db2event
SCOPE
Directory for transaction
logs (DB Specific)
Installation log file
Database
DBPATH/db2inst1/NODE0000/
SQL00001/LOGSTREAM0000
Product (or) Instance
INSTALLDIR/install/logs/db2in
stall.history
Local database directory
for instance
Database
DBPATH/db2inst1/NODE0000/
sqldbdir
System database
directory
Instance
INSTHOME/sqllib/sqldbdir
Node directory for
instance
Instance
INSTHOME/sqllib/sqlnodir
Partitioned database
environment file
Instance
INSTHOME/sqllib/db2nodes.cf
g
System Database Directory
Contains information about all the databases to which we can connect from DB2 server
System db directory is stored at instance level (./sqllib/sqldbdir)
db2 list db directory
Note: Indirect (local); remote(remote)
To enter information into system database directory, use catalog command:
db2 catalog db dbname as alias at node nodename
The catalog commands are normally used only when adding information for remote databases.
For local databases, a catalog entry is automatically created after creating the database with the
create database command
Local Database Directory
Contains information about databases residing on the server where you are currently working
Local DB directory doesnt reside inside the database, also doesnt reside at instance level. It is
in a layer between two.
db2 list db directory on drive/path
No specific command to enter information into this directory, only to retrieve it. When we create
a database with the CREATE DATABASE command, an entry is added to this directory.
Location: <Database Path>/ <Instance Name>/ <Parition Name>/sqldbdir
Node Directory
Connectivity information for remote database servers
$ db2 list node directory
$ db2 catalog tcpip node TCPFED remote 192.168.182.133 server 50000
$ db2 list node directory
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name
Comment
Directory entry type
Protocol
Hostname
Service name
= TCPFED
=
= LOCAL
= TCPIP
= 192.168.182.133
= 50000
Location: $DB2_HOME/sqlnodir directory
Database Connection Services (DCS) Directory
Connectivity information for host databases residing on System z (Z/OS or OS/390) or System i
(OS/400) server
DB2 Connect Software is needed
To enter information in DCS directory, use db2 catalog dcs db <dbname> as <target db>
For Example: db2 catalog dcs db mydb as db1g
To list contents of a DCS directory: db2 list dcs directory
Location: $DB2_HOME/sqlnodir directory
List active databases
You can deactivate a database using deactivate command. For example:
$db2 deactivate database DB1
You can activate a database using activate command. For example:
$db2 activate database DB1
To list all active databases:
List active databases (contd )
List active databases (contd )
$ db2 connect to DB1
Database Connection Information
Database server
= DB2/LINUXX8664 10.5.3
SQL authorization ID = DB2INST1
Local database alias = DB1
$ db2 list tablespaces
$ db2 ? list tablespaces
LIST TABLESPACES [SHOW DETAIL]
$ db2 list tablespaces show detail
[db2inst1@linuxdb2 ~]$ db2 list tablespaces
Tablespaces for Current Database
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=0
= SYSCATSPACE
= Database managed space
= All permanent data. Regular table space.
= 0x0000
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=1
= TEMPSPACE1
= System managed space
= System Temporary data
= 0x0000
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=2
= USERSPACE1
= Database managed space
= All permanent data. Large table space.
= 0x0000
Tablespace ID
Name
Type
Contents
State
Detailed explanation:
Normal
=3
= SYSTOOLSPACE
= Database managed space
= All permanent data. Large table space.
= 0x0000
[db2inst1@linuxdb2 ~]$ db2 list tablespace containers for 2
Tablespaces
(by space mangement)
Tablespaces (on space management):
System managed space (SMS):
- OS manages
- I/O is buffered by operating system
- table space is automatically extended when needed
- containers cant be dropped from SMS table spaces
- Less maintenance & not optimal performance
$ db2 "create tablespace tbspace1 managed by system USING ('/mnt/storage/sai/tbspace1')
$ db2 list tablespace containers for 4
Tablespace Containers for Tablespace 4
Container ID
Name
Type
=0
= /mnt/storage/sai/tbspace1
= Path
Database managed space (DMS):
- Either as files or as devices
- DB2 manages I/O
- Extend containers using ALTER TABLESPACE
- Unused DMS containers can be released
- Ideal for performance sensitive applications
- Increased maintenance and monitoring
$ db2 list tablespace containers for 2
Tablespace Containers for Tablespace 2
Container ID
=0
Name
=
/mnt/storage/storage_db1/db2inst1/NODE0000/DB1/T0000002/C0000000.LRG
Type
= File
$ db2 CREATE TABLESPACE TBSPACE1 PAGESIZE 8K MANAGED BY DATABASE
USING ( FILE /mnt/storage/storage_db1/dms1.LRG 10 M )
Automatic managed space (AMS):
- DBM creates and extends containers as and when needed
- Automatically handles resizing table spaces
- can be either DMS or SMS
- DMS for regular/large table spaces
- SMS for user or system temporary table spaces
- New databases and table spaces use automatic storage by default
Tablespaces
(by Usage)
Tablespaces (on usage):
Regular:
- To store regular tables and indexes. No temporary data
- This type is allowed in both, SMS and DMS table spaces
- Only type allowed for SMS table spaces
- Example: SYSCATSPACE
Large:
- To store any kind of data except temporary
- only DMS
- Ex: USERSPACE1
System temporary: Used to store temp data required by DB2. Ex: TEMPSPACE1
User temporary: To store declared global temp tables. These are optional
DB2 CLP
DB2 Command Line Processor (CLP)
CLP SYNTAX
For example:
db2 ?
db2 ? command string
db2 ? SQLnnnn (nnnn = 4 or 5 digit SQLCODE)
db2 ? nnnnn (nnnnn = 5 digit SQLSTATE)
USING CLP
QUIT/TERMINATE/CONNECT RESET DIFFERENCES
CLP COMMAND OPTIONS
MODIFY CLP OPTIONS
Running SQL Files
DB2 CLP HISTORY
db2=> history
db2=> history reverse
db2=> h r
db2=> runcmd 9
db2=> r 9
db2=> edit 9
db2=> e 9
DB2 Catalog Tables
Catalog tables are created under SYSIBM schema
Some catalog tables have parent-child relationship. For example, SYSIBM.SYSCOLAUTH is a
child of SYSIBM.SYSTABAUTH
Few examples:
SYSIBM.SYSCOLAUTH: details column level privileges
SYSIBM.SYSTABAUTH: details table/view level privileges
SYSIBM.SYSDBAUTH: database level privileges
SYSIBM.SYSTABSPACEAUTH: tablespace level privileges
The catalog tables can be classified into broad categories based on the type of data they store,
for example:
- Authorization: SYSDBAUTH, SYSTABSPACEAUTH, SYSTABAUTH, SYSCOLAUTH
- Data type and routine: SYSDATATYPES, SYSROUTINES, SYSROUTINEPARMS
- Constraint: SYSCHECKS, SYSRELS, SYSKEYCOLUSE
- Dependency: SYSCONSTDEP, SYSDEPENDENCIES, SYSVIEWDEP
- Storage management: SYSTABLESPACES, SYSTABLES
- Database partition: SYSNODEGROUPS, SYSPARTITIONMAPS
On top of system catalog tables, database manager creates two sets of system catalog views.
One set of read-only views is created under the SYSCAT schema, and a smaller set of
updatable views is created under the SYSSTAT schema
So,
SYSIBM: All catalog tables
SYSCAT: Useful read only views of catalog tables
SYSSTAT: Updatable views
db2 list tables for schema SYSCAT
db2 SELECT TABNAME from syscat.tables where tabschema = SYSCAT
db2 describe table syscat.systablespaces
db2 describe table syscat.sysbufferpools
BUFFERPOOLS
Area of main memory used to cache table and index data
Each database must have at least one buffer pool
Default: IBMDEFAULTBP
Buffer pools can be created, dropped, or altered
Query: SYSCAT.BUFFERPOOLS
Every table space must associate with the same buffer pool page size
A table space can only use one buffer pool
CREATE BUFFERPOOL bp8k PAGESIZE 8K
CREATE TABLESPACE tbsp1 PAGESIZE 8K BUFFERPOOL bp8k
SELECT BPNAME, BUFFERPOOLID, PAGESIZE FROM SYSCAT.BUFFERPOOLS
To get the buffer pool information:
db2look -d <Database Name> -l
db2 SELECT tbspace, bufferpoolid FROM syscat.tablespaces
db2 describe table <table name>
DB2 STORAGE GROUPS
A storage group is a named set of storage paths where data can be stored.
Helps to manage storage automatically
IBMSTOGROUP: Default storage group when you create a database
AUTOMATIC STORAGE NO:
- If we use this for database, then there is no default storage group
- This clause is depreciated in DB2 10 and might be removed from future releases
CREATE STOGROUP sgroup1 ON /mnt/storage/storage1, /mnt/storage/storage2
db2pd -db DB1 -storagegroups
