Wednesday, November 6, 2013

Create database


Note: UPDATE DB CFG FOR unicadb USING APP_CTL_HEAP_SIZE 4000 --> optional

db2 => CREATE DATABASE unicadb USING CODESET UTF-8 TERRITORY US PAGESIZE 32K
SQL0104N  An unexpected token "32K" was found following "PAGESIZE".  Expected
tokens may include:  "<unsigned-short-number>".  SQLSTATE=42601              
db2 => CREATE DATABASE unicadb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k
DB20000I  The CREATE DATABASE command completed successfully.                
db2 => get connection state                                                  
   Database Connection State
 Connection state       = Connectable and Unconnected
 Connection mode        =                           
 Local database alias   =                           
 Database name          =                           
 Hostname               =                           
 Service name           =                           
db2 => connect to unicadb
   Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.3
 SQL authorization ID   = DB2INST1
 Local database alias   = UNICADB
db2 => list tablespaces
           Tablespaces for Current Database
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
db2 => create regular tablespace unica pagesize 32k managed by database using (file 'home/db2inst1/db2inst1/NODE0000/UNICADB/UNICA01.TBS' 20000 )
DB20000I  The SQL command completed successfully.
db2 => drop tablespace unica;
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token ";" was found following "rop tablespace unica".
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601
db2 => drop tablespace unica
DB20000I  The SQL command completed successfully.
db2 => create regular tablespace unica pagesize 32k managed by database using (file '/home/db2inst1/db2inst1/NODE0000/UNICADB/UNICA01.TBS' 20000 )
DB20000I  The SQL command completed successfully.

Grants:
db2 => GRANT USE OF TABLESPACE UNICA TO USER UMP WITH GRANT OPTION
DB20000I  The SQL command completed successfully.
db2 => GRANT USE OF TABLESPACE UNICA TO USER UC  WITH GRANT OPTION
DB20000I  The SQL command completed successfully.
db2 => GRANT USE OF TABLESPACE UNICA TO USER UMO WITH GRANT OPTION
DB20000I  The SQL command completed successfully.
REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

1 comment:

  1. Alternatively can use the following without specifying any file:
    create regular tablespace datamart pagesize 32k

    ReplyDelete