Cooling Your Overloaded Database with New DB2 UDB EEE Partitions - Page 2

Existing Partitioned Database Inventory

For our example, we are using a Sun Solaris machine with 4 CPU, 8 GB of RAM, and separate disk volumes for database partitions. DB2 UDB EEE 7.1 database is running on that machine and has 2-database partition (0, 1).

Database General Information

$ db2level
DB21085I  Instance "db2inst1" uses DB2 code release "SQL07010" with level 
identifier "02010105" and informational tokens "DB2 v7.1.0", "s000510" and "".

db2 => LIST DATABASE DIRECTORY
 	 System Database Directory
 	 Number of entries in the directory = 1
 	 Database 1 entry:
 	 Database alias              = ARTIST
 Database name                   = ARTIST
 Local database directory        = /home/artist
 Database release level          = 9.00
 Catalog node number             = 0        

The catalog partition is NODE 0, and the database name is ARTIST, version 7.1.

Partition configuration

$ cat db2nodes.cfg
0 ARTIST0A1 0
1 ARTIST0A1 1

Database has 2 partitions defined on the same physical host.

db2 => LIST NODES
 
NODE NUMBER
----------------------
                     0
                     1

Database Connection Ports Configuration

DB2_db2inst1      60000/tcp   # Connection port for DB2 instance db2inst1
DB2_db2inst1_END  60004/tcp   # End connection port     
db2cdb2inst1      50000/tcp   # Connection port for DB2 instance db2inst1
db2idb2inst1      50001/tcp   # Interrupt  port for DB2 instance db2inst1

Rsh Configuration

-rw-------   1 db2inst1 db2iadm1      18 Feb  6 20:18 .rhosts

Hostname  Userid  
ARTIST0A1 db2inst1

Each DB2 UDB EEE partition must have the authority to perform remote commands (rsh) on the other partitions. On single SMP machines, only one entry is needed, and that is for local host name.

Node groups Configuration

db2 =>  select * from sysibm.sysnodegroupdef
 
NGNAME             NODENUM IN_USE
------------------ ------- ------
IBMCATGROUP              0 Y     
IBMDEFAULTGROUP          0 Y     
IBMDEFAULTGROUP          1 Y     
LOCAL                    0 Y     
GLOBAL                   1 Y  

• IBMCATGROUP - default node group for table space containing system catalogs

• IBMTEMPGROUP - default node group for system temporary table spaces

• IBMDEFAULTGROUP - default node group for user table spaces

• GLOBAL - user defined node group for big tables, partition 1

• LOCAL - user defined node group for small tables, only catalog partition 0

Tablespace Configuration

db2 => select TBSPACE,TBSPACETYPE,NGNAME from syscat.tablespaces
 
TBSPACE            TBSPACETYPE NGNAME            
------------------ ----------- ------------------
SYSCATSPACE        S           IBMCATGROUP       
TEMPSPACE1         S           IBMTEMPGROUP      
USERSPACE1         S           IBMDEFAULTGROUP   
ARTIST_BIG         S           GLOBAL            
ARTIST_SMALL       S           LOCAL    

Two tablespaces have been defined for user objects:

ARTIST_BIG        - tablespace for big tables, node 1
ARTIST _SMALL - tablespace for small lookup tables, node 0

Test Table Definition

db2 => CREATE TABLE PART_TEST (
TESTCOL1 CHARACTER(6) NOT NULL,
TESTCOL2 VARCHAR(24) NOT NULL,
TESTCOL3 CHARACTER(3) NOT NULL,
TESTCOL4 CHARACTER(6) NOT NULL,
TESTCOL5 DECIMAL(5,2),
TESTCOL6 DATE,
TESTCOL7 DATE,
TESTCOL8 CHARACTER(6)) IN ARTIST_BIG
DB20000I  The SQL command completed successfully.

db2 =>  ALTER TABLE db2inst1.PART_TEST ADD PARTITIONING KEY(TESTCOL1) USING HASHING
DB20000I  The SQL command completed successfully.

db2 => list tables
 
Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
--
PART_TEST                       DB2INST1        T     2003-02-10-16.31.30.803230 
--

db2 => select NODENUMBER(testcol1) PARTITION,COUNT(*) TOTAL 
	from part_test group by NODENUMBER(testcol1)
 
PARTITION   TOTAL      
----------- -----------
          1        5120
 
  1 record(s) selected.

Test table PART_TEST will be used as the test table.