Useful DB2 Links

DB2 DBA Stuff : http://db2dbastuff.blogspot.sg

Difference between Oracle and DB2 : http://db2commerce.com/2011/05/16/newbie-differences-between-db2-and-oracle/ (good if you are familiar with Oracle and moving on to DB2)

Difference in data loading between Oracle and DB2 : http://www.ibm.com/developerworks/data/library/techarticle/dm-0505tham/

Autoextend

If you get the following error it means the tablespace has ran out of space:

CREATE TABLE UA_CampAttribute ( CampaignID           bigint NOT NULL, AttributeID          bigint NOT NULL, StringValue          varchar(1024), NumberValue          float, DatetimeValue        timestamp, CONSTRAINT cCampAttribute_PK PRIMARY KEY (CampaignID, AttributeID) )                                                           
DB21034E  The command was processed as an SQL statement because it was not a                                                                                        
valid Command Line Processor command.  During SQL processing it returned:                                                                                            
SQL0289N  Unable to allocate new pages in table space "UNICA".  SQLSTATE=57011

Note: You may also get an error like this:
DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC

Easiest way is to turn on autoextend:
db2 => connect to unicadb
   Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.3
 SQL authorization ID   = DB2INST1            
 Local database alias   = UNICADB             
db2 => alter tablespace unica autoresize yes     
DB20000I  The SQL command completed successfully.
db2 => quit                                      
And it should be OK now:
CREATE TABLE UA_CampAttribute ( CampaignID           bigint NOT NULL, AttributeID          bigint NOT NULL, StringValue          varchar(1024), NumberValue          float, DatetimeValue        timestamp, CONSTRAINT cCampAttribute_PK PRIMARY KEY (CampaignID, AttributeID) )                                                           
DB20000I  The SQL command completed successfully.                                                                                                                    

Simple but comprehensive create database for DB2

See this link : https://pubs.vmware.com/vsphere-4-esx-vcenter/index.jsp?topic=/com.vmware.vsphere.installclassic.doc_41/install/prep_db/t_create_db2_script.html

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