IBM DB2 - Database Creation Steps
Below provided are the steps I have used to create a database in IBM db2 database engine.
Connect to db2 prompt after installation:
$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.1.0
You can issue database manager commands and SQL statements from the command prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
db2 => db2start
DB20000I The DB2START command completed successfully.
db2 =>
Create a new database with the name 'database1'
$ db2 create db database1 on /glotus4/db2/db1 using codeset UTF-8 territory US pagesize 4096 dft_extent_sz 2;
DB20000I The CREATE DATABASE command completed successfully.
$ db2 connect to database1;
Database Connection Information
Database server = DB2/AIX64 10.1.0
SQL authorization ID = DASUSR2
Local database alias = DATABASE1
$ db2 create database partition group dpg_32k;
DB20000I The SQL command completed successfully.
$ db2 create bufferpool bp_32k size 1000 automatic pagesize 32 k;
DB20000I The SQL command completed successfully.
$
***************************************************************
Set all the database parameters:
$ db2 update db cfg using DBHEAP AUTOMATIC;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
$ db2 update db cfg using APPLHEAPSZ 4096;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
$ db2 update db cfg using APP_CTL_HEAP_SZ 1024;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W Database must be deactivated and reactivated before the changes to one or more of the configuration parameters will be effective.
$ db2 update db cfg using LOGFILSIZ 10240;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W Database must be deactivated and reactivated before the changes to one or more of the configuration parameters will be effective.
$ db2 update db cfg using AUTO_RUNSTATS OFF;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
$ db2 disconnect all;
DB20000I The SQL DISCONNECT command completed successfully.
$ db2 connect to database1;
Database Connection Information
Database server = DB2/AIX64 10.1.0
SQL authorization ID = DASUSR2
Local database alias = DATABASE1
$ db2 create bufferpool bpool size 500 pagesize 32K;
DB20000I The SQL command completed successfully.
**********************************************************
Create tablespaces on the database:
$ db2 CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k EXTENTSIZE 80 bufferpool bpool;
DB20000I The SQL command completed successfully.
$ db2 create large tablespace DATA in dpg_32k pagesize 32 k extentsize 2 bufferpool bp_32k AUTORESIZE YES INCREASESIZE 5 PERCENT MAXSIZE NONE;
DB20000I The SQL command completed successfully.
$ db2 create large tablespace INDEX in dpg_32k pagesize 32 k extentsize 2 bufferpool bp_32k AUTORESIZE YES INCREASESIZE 5 PERCENT MAXSIZE NONE;
DB20000I The SQL command completed successfully.
$
**********************************************************
Grant privileges to the user:
$ db2 GRANT DBADM, CREATETAB, BINDADD, CONNECT, CREATE_NOT_FENCED_ROUTINE, IMPLICIT_SCHEMA, LOAD, CREATE_EXTERNAL_ROUTINE, QUIESCE_CONNECT, SECADM ON DATABASE TO USER db2inst2;
DB20000I The SQL command completed successfully.
$
$ db2 grant use of tablespace DATA to user db2inst2;
DB20000I The SQL command completed successfully.
$ db2 grant use of tablespace INDEX to user db2inst2;
DB20000I The SQL command completed successfully.
$
db2 => db2 catalog tcpip node database1 remote <ip address> server 50000
*****************************************************
Catalog the ip and node details
catalog tcpip node database1 remote <ip address> server 50000
catalog tcpip node database1 remote <ip address> server 1525
catalog dcs database database1 as database1
catalog database database1 as database1 at node SAN_NODE authentication dcs
connect to database1 user dasusr1: Database Connection Information Database server = DB2/AIX64 9.5.0 SQL authorization ID = < schema_name > Local database alias = <database name>
*****************************************************
Created node must be displayed upon testing:
db2 => LIST NODE DIRECTORY
Node Directory
Number of entries in the directory = 2
Node 1 entry:
Node name = DATABASE1
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = <ip address>
Service name = 50000
db2 => db2 LIST DATABASE DIRECTORY
SQL0104N An unexpected token "db2" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601
db2 => LIST DATABASE DIRECTORY
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = DATABASE1
Database name = DATABASE1
Node name = DATABASE1
Database release level = f.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
No comments:
Post a Comment