Mas DB2 Notes

Sunday, July 6, 2014

SQL to find out DB2 Instance Name

SELECT INST_NAME FROM TABLE(SYSPROC.ENV_GET_INST_INFO()) AS SYSTEMINFO

The default value is DB2

Friday, July 4, 2014

Another set of create database command for reference

CREATE DATABASE dmdb USING CODESET UTF-8 TERRITORY US PAGESIZE 32 k

connect to dmdb

create regular tablespace datamart pagesize 32k

GRANT USE OF TABLESPACE datamart TO USER datamart WITH GRANT OPTION

REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC

Script to drop disconnect and drop DB2 database


#!/bin/sh

 

db2 "connect to emmdb"

 

# Not sure if the following is actually need if the database needs to be restarted

# because of the 'currently in use' issues. However it should disconnect all application

# connected to the database.

 

db2 "quiesce database immediate force connections"

db2 "unquiesce database"

 

db2 "force application all"

 

# Note the following will impact all databases and applications connected to this instance

# Only perform these steps if the above is not a problem.

# These steps take care of 'The database is currently in use' issues after doing the above

 

db2stop force

db2start

 

# Finally drop the database

db2 "drop database emmdb"

Friday, November 8, 2013

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/

Wednesday, November 6, 2013

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