DB2 Database

DB2 Database:

In the IBM DB2 database is the group of Schemas, Logs, Tablespaces, Bufferpools, and groups storage working efficiently together to handle the operations of the database.

Database directory:

It is an arranged repository of the databases. If you are creating

A database the details about those databases are saved in the database directory, details like the configuration files, temporary tables list, storage devices, and many more 

In the instance folder, the Partition global directory is created. All the global information allocated to the database is stored in this directory. The global partition directory's name is NODExxxx/SQLyyy, in the name xxx is the number of the data partition, and yyy is the database token. 

The directory consists of local information from the database. The directory that is member-specific has been named as MEMBERxxxx, where xxx is the member number. DB2- DB2 Enterprise Server Edition executes on the sole member and has one member-specific directory. This directory which is member-specific, has a unique name as MEMBER0000.

Partitioned global directory

Directory Location : <instance>/NODExxx/SQLxxx

- Database associated files stored in the global partition directory are as follows:

- Files those monitor Global deadlock write to file events

- Information files of table space names as SQLSPCS.1, SQLSPCS.2

- Storage group control files [SQLSGF.1, SQLSGF.2]

- Files that contain the temporary table

- Global Configuration file

- Files that contain History

- Files those store logging details

- Locking files

- Containers of automatic storage

Member specific directory

- Directory location : /NODExxxx/SQLxxxx/MEMBER0000

- The directory holds the following files:

- The objects related to the databases

- Information files of the Buffer pool

- Monitoring files of local event

- Files those perform logging

- Configuration local files

- Field that monitors Deadlocks 

Creating database in DB2:

DB2 allows us to create the database in the instance by the command 'CREATE DATABASE". With default storage group named "IBMSTOGROUP," all the databases are generated. In contrast, the instance is being created. All the databases in DB2 are saved in "tablespace" those use their respective storage groups.

Before starting, we must ensure that the database manager is running. We need to use the command db2start to start the database manager.

Before creating the database, we should plan the database. According to the layout, contents, and how we wish it should be. Once it is created, we can add data to it and make changes.

Let's see how to create a non-restrictive database:

 The command used to create a non-restrictive database is as follows:

Syntax:

DB2 create database <database_name>

'Database_name' is the name you wish for the database.

Let's see an example. We will create a non-restrictive database which we will name as Books:

C:\Program Files\IBM\SQLLIB\BIN>db2 create database books

It will, in return, display the following statement:

DB20000I The CREATE DATABASE command was completed successfully.

Let's see how to create a restrictive database:

We add the restrictive keyword, and the syntax is as follows:

DB2 Create database <database_name> restrictive

Database_name is the name we wish to give to the database we are creating.

Let us see an example:
C:\Program Files\IBM\SQLLIB\BIN>db2 create database details restrictive

'details' is the name of the database

It will, in return, display the following statement:

DB20000I  The CREATE DATABASE command was completed successfully. 

We can verify the database directory files on local or system databases:

We need to execute the command to which lists the directory which is present in the current instance:

Syntax as  follows:

DB2 list database directory

Let's see an example:

C:\Program Files\IBM\SQLLIB\BIN>db2 list database directory

The following will be displayed:

Activating the database:

We can activate the database, which starts all the required services for the database, which makes the database available for the application. The commands activate the target database on all the members in the instance.

Following the is the syntax:

DB2 activate db database_name;

'Database_name' is the name you wish for the database.

Let's see an example:

TO activate the dataset named book, we will run the following command:

C:\Program Files\IBM\SQLLIB\BIN>db2 activate db books

It will, in return, display the following statement:

DB20000I The ACTIVATE DATABASE command was completed successfully.

Deactivating the database

We can also deactivate the database to stop the database services by the use of deactivating command

The syntax is as follows:

DB2 deactivate db <database_name>

 'Database_name' is the name you wish for the database.

Let's see the example:

C:\Program Files\IBM\SQLLIB\BIN>db2 deactivate db books

It will, in return, display the following statement:

DB20000I The DEACTIVATE DATABASE command was completed successfully.

Connecting to the database:

Once we have created the database can put it to use, that we should connect or start the database

Syntax:

DB2 connect to <database_name>

Let's see an example:

To connect to the current CLI the database named books, we will run the following command:

C:\Program Files\IBM\SQLLIB\BIN>db2 connect to books

The output will be:

Configuring the database manager and the database

The instance database manager configuration is saved in the file named 'db2system', and the configuration associated with the database is saved in the file named 'SQLDBCON'. We can not modify the files directly, and we can modify them by using tools those call API. We can run the following commands.

Database Manager Configuration Parameters

Let's see the command to get the information of the database manager instance

db2 get database manager configuration  
OR
db2 get dbm cfg  

..

Lets see to check for te size of current Active database:

Syntax:

db2 "call get_dbsize_info(?,?,?,-1)"   

Example:

C:\Program Files\IBM\SQLLIB\BIN>db2 "call get_dbsize_info(?,?,?,-1)"

Output:

Dropping the Database

After the use of the database, we can drop it by the drop command. We can delete it form the instance database directory. These commands deleted all the related containers, objects, tables, related files.

Syntax:

db2 drop database <database_name>

'Database_name' is the name you wish for the database.

Let's see an example:

We will be dropping the database named journals:

C:\Program Files\IBM\SQLLIB\BIN>db2 drop db journals

It will, in return, display the following statement:

DB20000I The DROP DATABASE command was completed successfully.

Output: