This chapter describes backup and restore methods of database.

Introduction

Backup and recovery methods are designed to keep our information safe. In Command Line Interface (CLI) or Graphical User Interface (GUI) using backup and recovery utilities you can take backup or restore the data of databases in DB2 UDB.

Logging

Log files consist of error logs, which are used to recover from application errors. The logs keep the record of changes in the database. There are two types of logging as described below:

Circular logging

It is a method where the old transaction logs are overwritten when there is a need to allocate a new transaction log file, thus erasing the sequences of log files and reusing them. You are permitted to take only full back-up in offline mode. i.e., the database must be offline to take the full backup.

Archive logging

This mode supports for Online Backup and database recovery using log files called roll forward recovery. The mode of backup can be changed from circular to archive by setting logretain or userexit to ON. For archive logging, backup setting database require a directory that is writable for DB2 process.

Backup

Using Backup command you can take copy of entire database. This backup copy includes database system files, data files, log files, control information and so on.

You can take backup while working offline as well as online.

Offline backup

Syntax: [To list the active applications/databases]

db2 list application

Output:

Auth Id  Application    Appl.      Application Id
DB       # of
         Name           Handle
Name    Agents
-------- -------------- ---------- ---------------------
----------------------------------------- -------- -----
DB2INST1 db2bp          39
*LOCAL.db2inst1.140722043938
ONE      1

Syntax: [To force application using app. Handled id]

db2 "force application (39)"

Output:

DB20000I  The FORCE APPLICATION command completed
successfully.

DB21024I  This command is asynchronous and may not
be effective immediately.

Syntax: [To terminate Database Connection]

db2 terminate

Syntax: [To deactivate Database]

db2 deactivate database one

Syntax: [To take the backup file]

db2 backup database <db_name> to <location>

Example:

db2 backup database one to /home/db2inst1/

Output:

Backup successful. The timestamp for this backup image is :
20140722105345

Online backup

To start, you need to change the mode from Circular logging to Archive Logging.

Syntax: [To check if the database is using circular or archive logging]

db2 get db cfg for one | grep LOGARCH

Output:

First log archive method (LOGARCHMETH1) = OFF
 Archive compression for logarchmeth1  (LOGARCHCOMPR1) = OFF
 Options for logarchmeth1              (LOGARCHOPT1) =
 Second log archive method             (LOGARCHMETH2) = OFF
 Archive compression for logarchmeth2  (LOGARCHCOMPR2) = OFF
 Options for logarchmeth2              (LOGARCHOPT2) =

In the above output, the highlighted values are [logarchmeth1 and logarchmeth2] in off mode, which implies that the current database in “CIRCULLAR LOGGING” mode. If you need to work with ‘ARCHIVE LOGGING’ mode, you need to change or add path in the variables logarchmeth1 and logarchmeth2 present in the configuration file.

Updating logarchmeth1 with required archive directory

Syntax: [To make directories]

mkdir backup
mkdir backup/ArchiveDest

Syntax: [To provide user permissions for folder]

chown db2inst1:db2iadm1 backup/ArchiveDest

Syntax: [To update configuration LOGARCHMETH1]

db2 update database configuration for one using LOGARCHMETH1
''DISK:/home/db2inst1/backup/ArchiveDest''

You can take offline backup for safety, activate the database and connect to it.

Syntax: [To take online backup]

db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs

Output:

db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs

Verify Backup file using following command:

Syntax:

db2ckbkp <location/backup file>

Example:

db2ckbkp
/home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001

Listing the history of backup files

Syntax:

db2 list history backup all for one

Output:

                    List History File for one

Number of matching file entries = 4

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
Backup ID
 -- --- ------------------ ---- --- ------------ ------------
 --------------
  B  D  20140722105345001   F    D  S0000000.LOG S0000000.LOG

 ------------------------------------------------------------
 ----------------

 Contains 4 tablespace(s):
 00001 SYSCATSPACE

 00002 USERSPACE1

 00003 SYSTOOLSPACE

 00004 TS1
  ------------------------------------------------------------
  ----------------
  Comment: DB2 BACKUP ONE OFFLINE

 Start Time: 20140722105345

   End Time: 20140722105347

     Status: A
 ------------------------------------------------------------
 ----------------
 EID: 3 Location: /home/db2inst1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
 Backup ID
 -- --- ------------------ ---- --- ------------ ------------
 --------------
  B  D  20140722112239000   N       S0000000.LOG S0000000.LOG
 ------------------------------------------------------------
 -------------------------------------------------------------
 -------------------------------

 Comment: DB2 BACKUP ONE ONLINE

 Start Time: 20140722112239

   End Time: 20140722112240

     Status: A
 ------------------------------------------------------------
 ----------------
  EID: 4 Location:
SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -2413   sqlerrml: 0

 sqlerrmc:
 sqlerrp : sqlubIni
 sqlerrd : (1) 0                (2) 0                (3) 0

           (4) 0                (5) 0                (6) 0

 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)

           (7)      (8)      (9)      (10)       (11)
 sqlstate:

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
 Backup ID
  -- --- ------------------ ---- --- ------------ ------------
  --------------
   B  D  20140722112743001   F    D  S0000000.LOG S0000000.LOG

 ------------------------------------------------------------
 ----------------
 Contains 4 tablespace(s):

 00001 SYSCATSPACE

 00002 USERSPACE1

 00003 SYSTOOLSPACE

 00004 TS1
  -------------------------------------------------------------
  ----------------
  Comment: DB2 BACKUP ONE OFFLINE

 Start Time: 20140722112743

   End Time: 20140722112743

     Status: A
 -------------------------------------------------------------
  ----------------
 EID: 5 Location: /home/db2inst1

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
 Backup ID
  -------------------------------------------------------------
  ----------------

R  D  20140722114519001   F
20140722112743

 ------------------------------------------------------------
 ----------------
 Contains 4 tablespace(s):

 00001 SYSCATSPACE

  00002 USERSPACE1

 00003 SYSTOOLSPACE

 00004 TS1
 ------------------------------------------------------------
 ----------------
Comment: RESTORE ONE WITH RF

 Start Time: 20140722114519

   End Time: 20140722115015
     Status: A

 ------------------------------------------------------------
 ----------------
  EID: 6 Location:

Restoring the database from backup

To restore the database from backup file, you need to follow the given syntax:

Syntax:

db2 restore database <db_name> from <location>
taken at <timestamp>

Example:

db2 restore database one from /home/db2inst1/ taken at
20140722112743

Output:

SQL2523W  Warning!  Restoring to an existing database that is
different from

the database on the backup image, but have matching names.
The target database

will be overwritten by the backup version.  The Roll-forward
recovery logs

associated with the target database will be deleted.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

Roll forward all the logs located in the log directory, including latest changes just before the disk drive failure.

Syntax:

db2 rollforward db <db_name> to end of logs and stop

Example:

db2 rollforward db one to end of logs and stop

Output:

                                 Rollforward Status
 Input database alias                   = one
 Number of members have returned status = 1
 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000000.LOG -
 S0000001.LOG
 Last committed transaction            = 2014-07-22-
 06.00.33.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

This chapter describes introduction of views, creating, modifying and dropping the views.

Introduction

A view is an alternative way of representing the data stored in the tables. It is not an actual table and it does not have any permanent storage. View provides a way of looking at the data in one or more tables. It is a named specification of a result table.

Creating a view

You can create a view using the following syntax:

Syntax:

db2 create view <view_name> (<col_name>,
<col_name1...) as select <cols>..
from <table_name>

Example: Creating view for shopper.sales1 table

db2 create view view_sales1(id, itemname, qty, price)
as select id, itemname, qty, price from
shopper.sales1

Modifying a view

You can modify a view using the following syntax:

Syntax:

db2 alter view <view_name> alter <col_name>
add scope <table_or_view_name>

Example: [To add new table column to existing view ‘view_sales1’]

db2 alter view view_sales1 alter id add
scope shopper.sales1

Dropping the view

You can drop a view using the following syntax:

Syntax:

db2 drop view <view_name>

Example:

db2 drop view sales1_view

Introduction

An Instance is a logical environment for DB2 Database Manager. Using instance, you can manage databases. Depending on our requirements, you can create multiple instances on one physical machine. The contents of Instance directory are:

• Database Manager Configuration file
• System Database Directory
• Node Directory
• Node Configuration File [db2nodes.cfg]
• Debugging files, dump files

For DB2 Database Server, the default instance is “DB2”. It is not possible to change the location of Instance directory after its creation. An instance can manage multiple databases. In an instance, each database has a unique name, its own set of catalog tables, configurations files, authorities and privileges.

Architecture of instance in DB2 product

Multiple instances

You can create multiple instances in one DB2Server on Linux, UNIX and Windows. It is possible to install multiple DB2Servers on a physical machine.

Creating instance on Linux

You can create multiple instances on Linux and UNIX if DB2 Server is installed as root user. An instance can run simultaneously on Linux and UNIX independently. You can work within a single instance of the database manager at a time.

An Instance folder contains database configuration files and folders. The Instance directory is stored at different locations on Windows depending on the operating system versions.

Listing instances

The following command is used to list instances:

db2ilist

This command lists all the instances that are available on a system.

Syntax:

db2ilist

Example:[To see how many instances are created in DB2 copy]

db2ilist

Output:

db2inst1
db2inst2
db2inst3

Instance environment commands

These commands are useful to work with arrangement of instance in the DB2 CLI.

Get instance

This command shows details of the currently running instance.

Syntax:

db2 get instance

Example:[To see the current instance which activated the current user]

db2 get instance

Output:

The current database manager instance is : db2inst1

Set instance

To start or stop the database manager of an instance on DB2 UDB, the following command is executed for the current instance.

Syntax:

set db2instance=<instance_name>

Example:[ To arrange the “db2inst1” environment to current user]

set db2instance=db2inst1

db2start

Using this command, you can start an instance. Before this, you need to run “set instance”.

Syntax:

db2start

Example:[To start an instance]

db2start

Output:

SQL1063N DB2START processing was successful

db2stop

Using this command you can stop a running instance.

Syntax:

db2stop

Output:

SQL1064N DB2STOP processing was successful.

Creating an instance

Let us see how to create a new instance.

db2icrt

If you want to create a new instance, you need to log in with root. Instance id is not a root id or a root name.

Here are the steps to create a new instance:

Step1: Create an operating system user for instance.

Syntax:

useradd -u <ID> -g <group name> -m -d <user location> <user name>
-p <password>

Example: [To create a user for instance with name ‘db2inst2’ in group ‘db2iadm1’ and password ‘db2inst2’]

useradd -u 1000 -g db2iadm1 -m -d /home/db2inst2 db2inst2 -p db2inst2

Step2: Go to the DB2 instance directory in root user for create new instance.

Location:

cd /opt/ibm/db2/v10.1/instance

Step3: Create instance using the syntax below:

Syntax:

./db2icrt -s ese -u <inst id> <instance name>

Example: [To create a new instance ‘db2inst2’ in user ‘db2inst2’ with the features of ‘ESE’ (Enterprise Server Edition)]

./db2icrt -s ese -u db2inst2 db2inst2

Output:

DBI1446I The db2icrt command is running, please wait.
 ….
 …..
DBI1070I Program db2icrt completed successfully.

Arranging communication port and host for an instance

Edit the /etc/services file and add the port number. In the syntax given below, ‘inst_name’ indicates the Instance name and ‘inst_port’ indicates port number of instance.

Syntax:

db2c_<inst name> <inst_port>/tcp

Example: [Adding ‘50001/tcp’ port number for instance ‘db2inst2’ with variable ‘db2c_db2inst2’ in ‘services’ file]

db2c_db2inst2 50001/tcp

Syntax 1: [Update Database Manager Configuration with service name. The following syntax ‘svcename’ indicates the instance service name and ‘inst_name’ indicates the instance name]

db2 update database manager configuration using svcename db2c_&<inst_name>

Example 1: [Updating DBM Configuration with variable svcename with value ‘db2c_db2inst2’ for instance ‘db2inst2’

db2 update database manager configuration using svcename db2c_db2inst2

Output

DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.

Syntax 2: set the “tcpip” communication protocol for the current instance

db2set DB2COMM=tcpip

Syntax 3: [Stopping and starting current instance to get updated values from database manager configuration]

db2stop
db2start

Updating an instance

You can update an instance using following command:

db2iupdt

This command is used to update the instance within the same version release. Before executing this command, you need to stop the instance database manager using “db2stop” command. The syntax below “inst_name” indicates the previous released or installed db2 server instance name, which you want to update to newer release or installed db2 server version.

Syntax 1: To update an instance in normal mode

db2iupdt <inst_name>

Example1:

./db2iupdt db2inst2

Syntax 2: To update an instance in debugging mode

db2iupdt -D <inst_name>

Example

db2iupdt -D db2inst2

Upgrading an instance

You can upgrade an instance from previous version of DB2 copy to current newly installed version of DB2 copy.

db2iupgrade

On Linux or UNIX system, this command is located in DB2DIR/instance directory. In the following syntaxes, “inst_name” indicates the previous version DB2 instance and “inst_username” indicates the current installed version DB2 copy instance user.

Syntax 2:

db2iupgrade -d -k -u <inst_username> <inst_name>

Example:

db2iupgrade -d -k -u db2inst2 db2inst2

Command Parameters:

-d : Turns debugging mode on.

-k : Keeps the pre-upgrade instance type if it is supported in the DB2 copy, from where you are running this command.

If you are using the Super User (su) on Linux for db2iupgrade command, you must issue the “su” command with the “-” option.

Dropping an instance

You can drop or delete the instance, which was created by “db2icrt” command.

db2idrop

On Linux and UNIX operating system, this command is located in the DB2_installation_folder/instance directory.

Syntax: [in the following syntax, ‘inst_username’ indicates username of instance and ‘inst_name’ indicates instance name]

db2idrop -u <inst_username> <inst_name>

Example: [To drop db2inst2]

./db2idrop -u db2inst2 db2inst2

Using other commands with instance

Command to find out which DB2 instance we are working on now.

Syntax 1: [to check the current instance activated by database manager]

db2 get instance

Output:

The current database manager instance is:  db2inst1

Syntax 2: [To see the current instance with operating bits and release version]

db2pd -inst | head -2

Example:

db2pd -inst | head -2

Output:

Instance db2inst1 uses 64 bits and DB2 code release SQL10010

Syntax 3: [To check the name of currently working instance]

db2 select inst_name from sysibmadm.env_inst_info

Example:

db2 select inst_name from sysibmadm.env_inst_info

Output:

INST_NAME  --------------------------------------
db2inst1
1 record(s) selected.

Syntax: [To set a new instance as default]

db2set db2instdef=<inst_name> -g

Example: [To array newly created instance as a default instance]

db2set db2instdef=db2inst2 -g

This chapter describes use of XML with DB2.

Introduction

PureXML feature allows you to store well-formed XML documents in columns of database tables. Those columns have XML database. Data is kept in its native hierarchical form by storing XML data in XML column. The stored XML data can be accessed and managed by DB2 database server functionality. The storage of XML data in its native hierarchical form enables efficient search, retrieval, and update of XML. To update a value in XML data, you need to use XQuery, SQL or combination of both.

Creating a database and table for storing XML data

Create a database by issuing the following syntax:

Syntax:

db2 create database xmldb

By default, databases use UTF-8 (UNICODE) code set. Activate the database and connect to it:

Syntax:

db2 activate db <db_name>
db2 connect to <db_name>

Example:

db2 activate db xmldb
db2 connect to xmldb

Create a well-formed XML file and create a table with data type of the column as ‘XML’. It is mandatory to pass the SQL query containing XML syntax within double quotation marks.

Syntax:

db2 “create table <schema>.<table>(col <datatype>,
col <xml datatype>)”

Example:

db2 "create table shope.books(id bigint not null
primary key, book XML)"

Insert xml values into table, well-formed XML documents are inserted into XML type column using SQL statement ‘INSERT’.

Syntax:

db2 “insert into <table_name> values(value1, value2)”

Example:

db2 "insert into shope.books values(1000, ''<catalog>
<book>

<author> Gambardella Matthew</author>
<title>XML Developers Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating application
with XML</description>
</book>

</catalog>'')"

Updating XML data in a table

You can update XML data in a table by using the following syntax:

Syntax:

db2 “update <table_name> set <column>=<value> where
<column>=<value>”

Example:

db2 "update shope.books set book=''<catalog>

<book>
<author> Gambardella, Matthew</author>
<title>XML Developers Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth XML</description>

</book>

</catalog>'' where id=1000"

This chapter introduces and describes the concept of Schema.

Introduction

A schema is a collection of named objects classified logically in the database.

In a database, you cannot create multiple database objects with same name. To do so, the schema provides a group environment. You can create multiple schemas in a database and you can create multiple database objects with same name, with different schema groups.

A schema can contain tables, functions, indices, tablespaces, procedures, triggers etc. For example, you create two different schemas named as “Professional” and “Personal” for an “employee” database. It is possible to make two different tables with the same name “Employee”. In this environment, one table has professional information and the other has personal information of employee. In spite of having two tables with the same name, they have two different schemas “Personal” and “Professional”. Hence, the user can work with both without encountering any problem. This feature is useful when there are constraints on the naming of tables.

Let us see few commands related to Schema:

Getting currently active schema

Syntax:

db2 get schema

Example: [To get current database schema]

db2 get schema

Setting another schema to current environment

Syntax:

db2 set schema=<schema_name>

Example: [To arrange ‘schema1’ to current instance environment]

db2 set schema=schema1

Creating a new Schema

Syntax: [To create a new schema with authorized user id]

db2 create schema <schema_name> authroization <inst_user>

Example: [To create “schema1” schema authorized with ‘db2inst2”]

db2 create schema schema1 authorization db2inst2

Exercise

Let us create two different tables with same name but two different schemas. Here, you create employee table with two different schemas, one for personal and the other for professional information.

Step 1: Create two schemas.

Schema 1: [To create schema named professional]

db2 create schema professional authorization db2inst2

Schema 2: [To create schema named personal]

db2 create schema personal authorization db2inst2

Step 2: Create two tables with the same name for Employee details

Table1: professional.employee

[To create a new table ‘employee’ in the database using schema name ‘professional’]

db2 create table professional.employee(id number, name
varchar(20), profession varchar(20), join_date date,
salary number);

Table2: personal.employee

[To create a new table ‘employee’ in the same database, with schema name ‘personal’]

db2 create table personal.employee(id number, name
varchar(20), d_birth date, phone bigint, address
varchar(200));

After executing these steps, you get two tables with same name ’employee’, with two different schemas.

This chapter describes the tablespaces in detail

Introduction

A table space is a storage structure, it contains tables, indexes, large objects, and long data. It can be used to organize data in a database into logical storage group which is related with where data stored on a system. This tablespaces are stored in database partition groups

Benefits of tablespaces in database

The table spaces are beneficial in database in various ways given as follows:

Recoverability: Tablespaces make backup and restore operations more convenient. Using a single command, you can make backup or restore all the database objects in tablespaces.

Automatic storage Management: Database manager creates and extends containers depending on the needs.

Memory utilization: A single bufferpool can manage multiple tablespaces. You can assign temporary tablespaces to their own bufferpool to increase the performance of activities such as sorts or joins.

Container

Tablespaces contains one or more containers. A container can be a directory name, a device name, or a filename. In a database, a single tablespace can have several containers on the same physical storage device. If the tablespace is created with automatic storage tablespace option, the creation and management of containers is handled automatically by the database manager. If it is not created with automatic storage tablespace option, you need to define and manage the containers yourself.

Default tablespaces

When you create a new database, the database manager creates some default tablespaces for database. These tablespace is used as a storage for user and temporary data. Each database must contain at least three tablespaces as given here:

1. Catalog tablespace
2. User tablespace
3. Temporary tablespace

Catalog tablespace: It contains system catalog tables for the database. It is named as SYSCATSPACE and it cannot be dropped.

User tablespace: This tablespace contains user-defined tables. In a database, we have one default user tablespace, named as USERSPACE1. If you do not specify user-defined tablespace for a table at the time you create it, then the database manager chooses default user tablespace for you.

Temporary tablespace: A temporary tablespace contains temporary table data. This tablespace contains system temporary tablespaces or user temporary tablespace.

System temporary tablespace holds temporary data required by the database manager while performing operation such as sorts or joins. A database must have at least one system temporary tablespace and it is named as TEMPSPACE1. It is created at the time of creating the database. User temporary tablespace holds temporary data from tables. It is created with DECLARE GLOBAL TEMPORARY TABLE or CREATE GLOBAL TEMPORARY TABLE statement. This temporary tablespace is not created by default at the time of database creation.

Tablespaces and storage management:

Tablespaces can be setup in different ways, depending on how you want to use them. You can setup the operating system to manage tablespace allocation, you can let the database manager allocate space or you can choose automatic allocation of tablespace for your data.

The following three types of managed spaces are available:

System Managed Space (SMS): The operating system’s file system manager allocates and manages the space where the table is stored. Storage space is allocated on demand. This model consists of files representing database objects. This tablespace type has been deprecated in Version 10.1 for user-defined tablespaces, and it is not deprecated for catalog and temporary tablespaces.

Database Managed Space (DMS): The Database Server controls the storage space. Storage space is pre- allocated on the file system based on container definition that you specify when you create the DMS table space. It is deprecated from version 10.1 fix pack 1 for user-defined tablespaces, but it is not deprecated for system tablespace and temporary tablespace.

Automatic Storage Tablespace: Database server can be managed automatically. Database server creates and extends containers depend on data on database. With automatic storage management, it is not required to provide container definitions. The database server looks after creating and extending containers to make use of the storage allocated to the database. If you add storage space to a storage group, new containers are automatically created when the existing container reach their maximum capacity. If you want to use the newly-added storage immediately, you can rebalance the tablespace.

Page, table and tablespace size:

Temporary DMS and automatic storage tablespaces, the page size you choose for your database determines the maximum limit for the tablespace size. For table SMS and temporary automatic storage tablespaces, the page size constrains the size of table itself. The page sizes can be 4kb, 8kb, 16kb or 32kb.

This chapter describes installation steps of DB2 server.

Introduction

You can download the DB2 Server trial version or purchase the product license from . There are two separate DB2 servers available for downloading, depending upon the size of operating system, on which it is intended to execute. For example, if you want to download a DB2 server for 32bit Linux or UNIX operating system, then you need to download a 32 bit DB2 server. The same applies for 64bit DB2 server.

Hardware requirements

Processor : Minimum Core 2Duo

Ram : 1GB minimum

Hard disk : 30GB minimum

Software requirements

Before installing the DB2 server, your system needs to get ready with the required software on it. For Linux, you need to install “libstdc++6.0”.

Checking system compatibility

Before installing DB2 Server, you need to verify if your system is compatible with the DB2 server. For confirming the compatibility, you need to call ”db2prereqcheck” command on command console.

Installing DB2 on Linux operating system

Open the Terminal and set the db2 installation image folder path on console using “CD <DB2 installation folder>” command. Then type “./db2prereqcheck” command, which confirms the compatibility of your system with DB2 server.

./db2prereqcheck

Figure-1 shows the compatibility requirements of Linux operating system and hardware system.

Follow the given steps for installing DB2 on your Linux system:

• Open the terminal.
• Login as root user.
• Open DB2 Installation folder.
• Type “./db2setup” and press Enter.

This process will start execution of DB2 server setup.

Type “./db2setup” and press Enter on root terminal to start setup process of DB2 Server.

On doing so, the “Set up Launch Pad” screen appears. [Figure-2]

On Setup Launch pad page, select “Install a Product” option from left side menu. Select option “DB2 Advanced Enterprise Server Edition”. Select “Install New” Button.

A new frame appears with name “DB2 setup wizard”. Click “Next”. [Figure-3]

The next screen appears with DB2 license agreement. Select “I accept the terms…” Click “Next”. [Figure-4]

Next screen comes up with offer of Installation type, which is set to “Typical” by default.

Keep the same selection. Click “Next”. [Figure-5]

The next screen appears with installation action.

Select “Install DB2 Advanced Enterprise Server Edition…”

Click “Next”. [Figure-6]

On the next screen, the setup program asks for selection of installation directory.

Keep the default and click “Next”.

The next screen comes up with the user authentication. Enter your password for “dasusr1” user.

(Your password can be identical to username so that it is convenient to remember.)

On the following screen, the setup asks you for creation of DB2 Server Instance.

Here, it is creating a DB2 instance with name “db2inst1”.

The next screen asks you the number of partitions you require for your default instance.

You have a choice of “single or Multiple” partitions.

Select “single partition instance”. Click “next”.

On the next screen, the setup asks you for authentication for DB2 instance being created.

Here, by default username is created as “db2inst1”. You can enter password same as username.

Click “Next”.

On the next screen, the setup asks to enter authentication information for “db2fenc” user.

Here, you can enter password same as username.

Click “Next”.

On the next screen, you can select “Do not setup your db2 server to send notifications at this time” option.

Click ”Next”.

The next screen shows you the information about db2 setup.

Click “Finish”.

The DB2 Installation procedure is complete at this stage.

Verifying DB2 installation

You need to verify the installation of DB2 server for its usefulness. On completing the DB2 Server installation, logout from current user mode and login to “db2inst1” user. In “db2inst1” user environment, you can open terminal and execute the following commands to verify if your db2 product is installed properly or not.

db2level

This command shows the current version and service level of the installed DB2 product for current instance.

Syntax:

db2level

Example:

db2level

Output:

DB21085I Instance "db2inst2" uses "64" bits
And DB2 code release "SQL10010" with level
identifier "0201010E". Informational tokens
are "DB2 v10.1.0.0", "s120403",
"LINUXAMD64101", and Fix Pack "0".
Product is installed at "/home/db2inst2/sqllib".

db2licm

This command shows all the license related information of our DB2 Product.

Syntax:

db2licm <parameter>

Example:

db2licm -l

Output:

Product name:                     "DB2 Advanced Enterprise Server Edition"
License type:                     "Trial"
Expiry date:                      "10/02/2014"
Product identifier:               "db2aese"
Version information:              "10.1"
Product name:                     "DB2 Connect Server"
License type:                     "Trial"
Expiry date:                      "10/02/2014"
Product identifier:               "db2consv"
Version information:              "10.1"

Command Line Processor (CLP)

The CLP can be started in one of the three modes:

• Command mode: In this mode, each command and SQL statement must be prefixed by “db2”. For example, query “db2 activate database sample”.

• Interactive input mode: you can launch this mode by using the “db2” command. Here, you can pass SQL statements without prefix. For example, “activate database sample”.

• Batch mode: Here, you need to create a script file, which contains all SQL queries of requirements and save the file with “.db2” extension. You can call this in command line using syntax “db2 –tf <filename.db2>”.

This chapter describes the Database Storagegroups.

Introduction

A set of Storage paths to store database table or objects, is a storage group. You can assign the tablespaces to the storage group. When you create a database, all the tablespaces take default storagegorup. The default storage group for a database is ‘IBMSTOGROUP’. When you create a new database, the default storage group is active, if you pass the “AUTOMATIC STOGROUP NO” parameter at the end of “CREATE DATABASE” command. The database does not have any default storage groups.

Listing storagegroups

You can list all the storagegroups in the database.

Syntax: [To see the list of available storagegroups in current database]

db2 select * from syscat.stogroups

Example: [To see the list of available storagegorups in current database]

db2 select * from syscat.stogroups

Creating a storagegroup

Here is a syntax to create a storagegroup in the database:

Syntax: [To create a new stogroup. The ‘stogropu_name’ indicates name of new storage group and ‘path’ indicates the location where data (tables) are stored]

db2 create stogroup  on ‘path’

Example: [To create a new stogroup ‘stg1’ on the path ‘data1’ folder]

db2 create stogroup stg1 on ‘/data1’

Output:

DB20000I The SQL command completed succesfully

Creating tablespace with stogroup

Here is how you can create a tablespace with storegroup:

Syntax: [To create a new tablespace using existed storage group]

db2 create tablespace <tablespace_name>  using stogroup <stogroup_name>

Example: [To create a new tablespace named ‘ts1’ using existed storage group ‘stg1’]

db2 create tablespace ts1 using stogroup stg1

Output:

DB20000I The SQL command completed succesfully

Altering a storagegroup

You can alter the location of a storegroup by using following syntax:

Syntax: [To shift a storage group from old location to new location]

db2 alter stogroup  add ‘location’, ‘location’

Example: [To modify location path from old location to new location for storage group named ‘sg1’]

db2 alter stogroup sg1 add ‘/path/data3’, ‘/path/data4’

Dropping folder path of storagegroup

Before dropping folder path of storagegroup, you can add new location for the storagegroup by using alter command.

Syntax: [To drop old path from storage group location]

db2 alter stogroup  drop ‘/path’

Example: [To drop storage group location from ‘stg1’]

db2 alter stogroup stg1 drop ‘/path/data1’

Rebalancing a tablespace

Rebalancing the tablespace is required when we create a new folder for storagegroup or tablespaces while the transactions are conducted on the database and the tablespace becomes full. Rebalancing updates database configuration files with new storagegroup.

Syntax: [To rebalance the tablespace from old storage group path to new storage group]

db2 alter tablspace <ts_name> rebalance

Example: [To rebalance]

db2 alter tablespace ts1 rebalance

Renaming a storagegroup

Syntax: [To modify the name of existing storage name]

db2 rename stogroup <old_stg_name> to <new_stg_name>

Example: [To modify the name of storage group from ‘sg1’ to new name ‘sgroup1’]

db2 rename stogroup sg1 to sgroup1

Dropping a storage group

Step 1: Before dropping any storagegroup, you can assign some different storagegroup for tablespaces.

Syntax: [To assign another storagegroup for table space.]

db2 alter tablspace <ts_name> using stogroup <another sto_group_name>

Example: [To change from one old stogroup to new stogroup named ‘sg2’ for tablespace ‘ts1’]

db2 alter tablespace ts1 using stogroup sg2

Step 2:

Syntax: [To drop the existing stogroup]

db2 drop stogorup <stogroup_name>

Example: [To drop stogroup ‘stg1’ from database]

db2 drop stogroup stg1

This chapter introduces you to Bufferpools in the database.

Introduction

The bufferpool is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk. All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. It called as “IBMDEFAULTBP”. Depending on the user requirements, it is possible to create a number of bufferpools. In the bufferpool, the database manager places the table row data as a page. This page stays in the bufferpool until the database is shutdown or until the space is written with new data. The pages in the bufferpool, which are updated with data but are not written onto the disk, are called “Dirty” pages. After the updated data pages in the bufferpool are written on the disk, the bufferpool is ready to take another data.

Relationship between tablespaces and bufferpools

Each table space is associated with a specific buffer pool in a database. One tablespace is associated with one bufferpool. The size of bufferpool and tablespace must be same. Multiple bufferpools allow you to configure the memory used by the database to increase its overall performance.

Bufferpool sizes

The size of the bufferpool page is set when you use the “CREATE DATABASE” command. If you do not specify the page size, it will take default page size, which is 4KB. Once the bufferpool is created, it is not possible to modify the page size later

Listing the available bufferpools in the current database directory

Syntax: [The syntax below shows all available bufferpools in database]

db2 select * from syscat.bufferpools

Example: [To see available bufferpools in current database]

db2 select * from syscat.bufferpools

Output:

BPNAME      BUFFERPOOLID DBPGNAME   NPAGES      PAGESIZE    ESTORE
NUMBLOCKPAGES BLOCKSIZE   NGNAME
------------------------------------------------------------
IBMDEFAULTBP
 1 -
 -2        4096 N                  0           0 -

 1 record(s) selected.

Creating the bufferpool

To create a new bufferpool for database server, you need two parameters namely, “bufferpool name” and “size of page”. The following query is executed to create a new bufferpool.

Syntax: [In the syntax below,‘bp_name’ indicates bufferpool name and ‘size’ indicates size for page you need to declare for bufferpools (4K,8K,16K,32K)]

db2 create bufferpool <bp_name> pagesize <size>

Example: [To create a new bufferpool with name “bpnew” and size “8192”(8Kb).]

db2 create bufferpool bpnew pagesize 8192

Output

DB20000I The SQL command completed successfully.

Dropping the bufferpool

Before dropping the bufferpool, it is required to check if any tablespace is assigned to it.

Syntax: [To drop the bufferpool]

drop bufferpool <bp_name>

Example: [To drop ‘bpnew’ named bufferpool]

db2 drop bufferpool bpnew

Output

DB20000I The SQL command completed successfully.