DB2

DB2 v9.7 LUW is that version that I used.
It has built in compatibility with Oracle PL/SQL stored procedures and other Oracle-specific functionality

DB2 is able to host multiple instances in a host and each instance can have multiple databases.

The instance could be started without mounting the database. The instance it the access point to both the logical and physical parts of the database.

The database server uses Multithread architecture.

DB2 Architecture diagram

DB2 Database Adminstrator Server
The DB2 administration server (DAS) provides support services for DB2 tools such as the Control Center and the Configuration Assistant.

db2admin start

db2admin stop

Enable Oracle Compatibility
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop force
db2start
db2 “CREATE DATABASE dbName AUTOMATIC STORAGE YES ON PAGESIZE 32 K”

DB2 Version

db2level – Instance version

db2mtrk – Memory tracker

db2licm -l – Check the type of DB2 license used

db2stat – Displays the process running the current instance

DB2 Instance

db2ilist – Lists of instances

db2icrt instance_name – To Create an instance

db2idrop instance_name – To Drop an instance

db2start instance_name – To Start an instance

db2stop instance_name – To Stop an instance

db2 get instance – Gets the default instance

Environment Variables / Registry / Configuration

DB2INSTANCE – The current default instance

db2set -all – View current registry values of the Global Registry

db2set -lr – View all registry values

db2 get dbm cfg – Instance configuration file

db2 update dbm cfg using Key Value – Updates the DBM cfg

db2 get db cfg for db_name – Database configuration file

db2 update db cfg for db_name using Key Value – Updates the DB cfg

db2 terminate –

db2 list db directory – List System Directory

db2 list db directory on [ drive / path ] – List Local Directory

db2 list node directory – List Node Directory

Catalog a Node in the current default instance

db2 catalog tcpip noderemoteserver 50000

Catalog a Database in the current default instance

db2 catalog database <> as <> at node <> authentication server

Uncatalog a Database in the current default instance

db2 uncatalog database <>

DB2 Connect
“DB2 Connect” is a lightweight version of the DB2 database system whose instances cannot support a local database, and can only provide connectivity to a remote instance

Database Connection Services
The Database Connection Services (DCS) directory contains connectivity information for host databases residing on a zSeries (z/OS or OS/390) or iSeries (OS/400) server. You need to have DB2 Connect software installed unless the server you are working on has DB2 UDB Enterprise Server Edition (ESE) installed. DB2 ESE comes with DB2 Connect support built in

How to Set the CLP Command Prompt

db2set db2_clpprompt=”%ia@%i>%d:>”

Valid tokens for the DB2_CLPPROMPT registry variable

%i – Instance

%d – Database

%ia – Authorized user for instance

%id – Authorized user for database

%n – New Line

Create an Instance & Database

db2icrt

db2 catalog local node instance
system

db2 create database

db2 catalog database <> as <> at node <> authentication server

Create federated Objects
db2 create wrapper drda library ‘db2drda.dll’;

create server drddev type db2/udb version ‘9.7’ wrapper drda authid “” password “” options( add dbname ”);

Grant Permissions to Other Users
Unlike Oracle, DB2 uses the operating system to authenticate to the database.

db2 grant dbadm,createtab,bindadd,connect,create_not_fenced_routine,implicit_schema,load,create_external_routine,quiesce_connect,secadm on database to user db2admin;

List of DB2 process

db2pd -edus – Can be run in the interactive mode also.

Description of the DB2 process

db2pdbe –

db2agent –

db2resync – the resync agent that scans the global resync list

db2ipccm – for local client connections

db2licc – manages installed DB2 licenses

db2aiothr – manages asynchronous I/O requests for a database partition (UNIX only

db2sysc – the main system controller EDU; it handles critical DB2 server events

DB2 Load / Import / Export
Load command is for bulk loading the data and the table should already have been created. Requires DBA rights

Export command is for exporting the data out of the tables and can used by any user with SELECT privelage on the table or view

export to [file name] of del
messages [file name]
select * from [table name]

Import command is the complement of the export to import the data into the tables.

import from [file name] of del
messages [file name]
insert_update into [table name]