Top 50 Most Important DB2 Commands for Database Administrator

IBM Db2 is database software that can reliably handle multiple workloads. The latest version of Db2 is optimized to provide extreme performance while lowering costs.

Below are the most useful db2 admin commands:

Command	Description
db2cmd	Open DB2 Command line Tool
db2cc	Open DB2 Control Centre
db2start	Start Database Normaly
db2stop	Stop Database Normaly
db2stop force	Stop Database forcely
db2 force application all	Close all applications that uses DB2 Database.
db2level	Display DB2 version and fix pack level
db6level	Display DB2 Client Version
db2 terminate	Close the database connection
db2_kill -all	Kill a hanged instance
db2licm -l	View license information
db2 connect to <db2sid>	Establish connection to an instance
db2 list tablespaces show detail	Displays table space information
db2 get dbm cfg	Display configuration parameter of database manager.
db2 get db cfg for <db2 sid>	Display configuration parameter of an instance
db2 update dbm cfg using <parameter_name> <new value>	Change value of a database manager configuration parameter.
db2 update db cfg for <db2 sid> using <parameter_name> <new value>	Change value of a instance configuration parameter.
db2 drop database <target db2sid>	Delete and instance
db2 activate db <dbsid>	Activate Database
db2 deactivate <dbsid>	Deactivate an active database
db2 rollforward db <SID> to end of logs	Apply all pending logs
db2 rollforward db <db2sid> query status	Display rollforward status
db2 backup database <sid> to “disk:\location”	Take an offline backup to specified location
db2 list utilities show detail	Display Database backup status
db2 restore db <sid> from “disk:\path” replace history file	Restore database from a backup image

Read more ...

How to Drop DB2 database Installed on Linux

DB2 Drop database command deletes all its objects, containers, and associated files. The dropped database is removed (uncataloged) from the database directories. You cannot drop a database if someone is connected to it. You would need to force everyone off the database first.

DROP DATABASE deletes all user data and log files, as well as any backup and restore history for the database. If the log files are needed for a rollforward recovery after a restore operation, or the backup history required to restore the database, these files should be saved before issuing this command.

When you use the DROP DATABASE command, archived log files for the dropped database are not affected. You must manually move archived log files from the log archive path. If you do not move these log files, future database recovery operations that use the same archive log path might fail.
The database must not be in use; all users must be disconnected database before the database can be dropped.

To be dropped, a database must be cataloged in the system database directory. Only the specified database alias is removed from the system database directory. If other aliases with the same database name exist, their entries remain. If the database being dropped is the last entry in the local database directory, the local database directory is deleted automatically.


Below are the Step-by-Step process to drop DB2 Database


Step 1: 
Login to the db2 instance. Example in my case db2inst1


Step 2:
$ db2 force application all
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.


Step 3:
$ db2 stop
SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

Step 4:

$ db2start

0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

Step 5: 

$ db2 drop database airsan    --Here airsan is database name or alias name.

SQL1032N  No start database manager command was issued.  SQLSTATE=57019

Step 6:

$ db2 connect to airsan

SQL1013N  The database alias name or database name "AIRSAN" could not be

found.  SQLSTATE=42705

Now we have dropped the database named AIRSAN. When you try to connect it will throw an error because we have dropped the database and now it is not in the catalog.

Read more ...

Hash Partitioning with Examples in Oracle

Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions. Hash partitioning is used where ranges aren't appropriate, i.e. employee number, customer ID, etc. Using this approach, data is randomly distributed across the partitions rather than grouped.

How to Create Hash Partition Table in Oracle

• Choose a column that is unique.
• Create multiple partitions and subpartitions for each partition that is a power of two. For example, 2, 4, 8, 16, 32, 64, 128, and so on.

Following are the Benefits and uses of Hash Partitioning:

1. To enable partial or full parallel partition-wise joins with likely equisized partitions.
2. To distribute data evenly among different partitions.
3. To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list.
4. To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices.

Examples to Create Hash Partition Table in Oracle

CREATE TABLE CUSTOMERS
(
RECEIVED_TIME_STAMP     DATE NOT NULL,
NETWORK_ID              VARCHAR2(10) NOT NULL,
RECORD_TYPE             VARCHAR2(50),
CUSTOMER_ID             NUMBER(18) NOT NULL,
PHONE_NUMBER            VARCHAR2(80),
CUSTOMER_NAME           VARCHAR2(80),
ACCOUNT_ID              NUMBER(18),
CUSTOMER_TYPE           VARCHAR2(80),
NRC_NUMBER              VARCHAR2(80),
CUSTOMER_FIRST_NAME     VARCHAR2(80),
CUSTOMER_MIDDLE_NAME    VARCHAR2(80),
CUSTOMER_LAST_NAME      VARCHAR2(80)
)
partition BY hash (CUSTOMER_ID)
(
partition PARTITION P001 tablespace TS_CUSTOMERS_01,
partition PARTITION P002 tablespace TS_CUSTOMERS_02,
partition PARTITION P003 tablespace TS_CUSTOMERS_03,
partition PARTITION P004 tablespace TS_CUSTOMERS_04,
partition PARTITION P005 tablespace TS_CUSTOMERS_01,
partition PARTITION P006 tablespace TS_CUSTOMERS_02,
partition PARTITION P007 tablespace TS_CUSTOMERS_03,
partition PARTITION P008 tablespace TS_CUSTOMERS_04,
partition PARTITION P009 tablespace TS_CUSTOMERS_01,
partition PARTITION P010 tablespace TS_CUSTOMERS_02,
partition PARTITION P011 tablespace TS_CUSTOMERS_03,
partition PARTITION P012 tablespace TS_CUSTOMERS_04,
partition PARTITION P013 tablespace TS_CUSTOMERS_01,
partition PARTITION P014 tablespace TS_CUSTOMERS_02,
partition PARTITION P015 tablespace TS_CUSTOMERS_03,
partition PARTITION P016 tablespace TS_CUSTOMERS_04
);


Example to Create Index on the above Partition Table

CREATE  INDEX IX_CUSTOMER_ID on CUSTOMERS (CUSTOMER_ID) INITRANS 4 STORAGE(FREELISTS 16)
LOCAL
(
PARTITION P001 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P002 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P003 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P004 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P005 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P006 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P007 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P008 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P009 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P010 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P011 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P012 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P013 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P014 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P015 TABLESPACE TS_CUSTOMER_ID_IDX,
PARTITION P016 TABLESPACE TS_CUSTOMER_ID_IDX
);

Read more ...

How to Share Windows Drives and Folders using Oracle VM VirtualBox With a Guest Linux OS

There are several virtual machine available in the market for free use. Oracle VM VirtualBox is one of it which enables you to deploy operating system and application software with supported virtualization environment. It is freely available for Windows, Mac OS X, Linux and Solaris x-86 platforms under GPLv2.  However, the main functionality of all VMs are the same.  The host operating system can support a number of virtual machines, each of which has the characteristics of a particular OS. The kernel manages all system resources and tasks, including process scheduling, memory, disk space, and processor time.

Download VM 

Once vmware is downloaded, you can install and setup your Guest OS. However, sharing host drives\folders is one of the tricky task and is not as simple as clicking and enabling sharing in windows. During my VM setup there was a need to mount and share windows folder which should be accessible from Linux OS. I went through many articles and finally able to mount windows folders to the Linux OS which was running on the top of VM.

Below are the steps by step guide using which you will be able to mount any folders or Drives.

STEP 1: 

Run host OS and launch VM VirtualBox.


STEP 2:

Start Linux box which you have installed in VM.


STEP 3:

Click on "Devices" tab which is located on the top of VM VirtualBox taskbar and select "Shared Folders" from the drop-down list.

STEP 4:

VirtualBox will open a window "Shared Folders". Click on the top icon in the right-hand corner to add the drive/folder. Another window will open—"Add Share".



Step 5:

You can also type the name of the folder in the "Folder Name" box. It can be any name you wish to have on Guest OS. Also, check the boxes "Auto Mount" and "Make Permanent" and click "OK".

STEP 7:

To mount shared folders in linux machine, Login to Linux box and type the below command 

$ mount -t vboxsf Softi /media

HERE Softi is the shared windows folder and you are mounting this folder on /media mount point or folder.

Finally you will be able to see you mounted folders using below command:

df -h

Read more ...