Monday, June 10, 2013

1 DB2 management 101

 

DB2 commands

  • db2ilist
  • db2icrt
  • db2idrop
  • db2imigr –> migrate db2 instance
  • db2iupdt –> upgrade db2 instance
  • db2start
  • db2stop

Create DB2 db2 example

CREATE DATABASE sample2 AUTOMATIC STORAGE YES  ON '/home/db2inst1' 
DBPATH ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096;

 

DB2 Database folder structure

dftdbpath  -> define the default database file path when create database command is executed.

image

  • NODE0000 –> always NODE000 for non partition database .
  • SAMPLE,SAMPLE2 is the database name in the below screenshot. SQL00001,SQL00002 is the database directory that assign to the database name. In this example, the SQL00001 is SAMPLE database. SQL00002 is the SAMPLE2 database.
  • DB2EVENT –> for event monitoring data.
  • SQLOGDIR –> for transaction log

image

Directory under the database name ( SAMPLE) contains the file for tablespace

image

  • db2rhist.asc –> db2 recovery history files. contains history information about backup,restore,table load,table reorg, table space and database changes.
  • db2rhist.bak –> backup file of db2rhist.asc
  • SQLBP.1/SQLBP.2 –> Buffer pool information.
  • SQLDBCON/SQLDBCONF –> Database configuration information
  • SQLINSLK –> Make sure DB is assign to the given DB2 instance
  • SQLOGCTL.LFH –> The file contains the information about active transaction log files.
  • SQLOGMIR.LFH –> Backup log file of SQLOGCTL.LFH
  • SQLSGF.1 –> Storage path of automatic storage
  • SQLSGF.2 backup file for SQLSGF.1
  • SQLSPCS.1 –> Table space information
  • SQLSPCS.2 –> Backup copy of SQLSPCS.1
  • SQLTMPLK –> Temporary table spaces.

image

IBMDEFAULTBP

Default Buffer pool size on Version 9 is 1000 4K pages on Linux, 250 4K pages on windows.

Default tablespace

  • SYSCATSPACE: DMS base
  • USERSPACE1 : DMS base
  • TEMPSPACE1: SMS base

DB2 directory and DB2 NoDE directory

DB2 directory contains the information about the db2 database information. DB2 Node directory contains the information about the remote DB2 system information.  In the below screenshot, the db2 catalog shows 3 db2 alias, 2 on the local ( SAMPLE and SAMPLE2), One at remote, SAMPLE10. Therefore the db2 node would show the remote SAMPLE10 information .

 

db2 list database directory
db2 list database directory show detail

image

 

db2 list node directory
db2 list node directory show detail 

image

Here is the command use to catalog the remote server.

db2 catalog tcpip node db2v10 remote 192.168.208.128 server 50001 

Connect to DB2

db2 connect to <DBNAME>
db2 connect to <DBNAME> USER <USERNAME> USING <PASSWORD>
db2 connect reset

image

db2 get connection state
db2 get instance
image

1 comments:

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates