Tables are logical structure maintained by Database manager. In a table each vertical block called as column (Tuple) and each horizontal block called as row (Entity). The collection of data stored in the form of columns and rows is known as a table. In tables, each column has different data type. Tables are used to store persistent data.

Type of tables

• Base Tables: They hold persistent data. There are different kinds of base tables, including:
  • Regular Tables: General purpose tables, Common tables with indexes are general purpose tables.
  • Multidimensional Clustering Table (MDC): This type of table physically clustered on more than one key, and it used to maintain large database environments. These type of tables are not supported in DB2 pureScale.
  • Insert time clustering Table (ITC): Similar to MDC tables, rows are clustered by the time they are inserted into the tables. They can be partitioned tables. They too, do not support pureScale environment.
  • Range-Clustered tables Table (RCT): These type of tables provide fast and direct access of data. These are implemented as sequential clusters. Each record in the table has a record ID. These type of tables are used where the data is clustered tightly with one or more columns in the table. This type of tables also do not support in DB2 pureScale.
  • Partitioned Tables: These type of tables are used in data organization schema, in which table data is divided into multiple storage objects. Data partitions can be added to, attached to and detached from a partitioned table. You can store multiple data partition from a table in one tablespace.
  • Temporal Tables: History of a table in a database is stored in temporal tables such as details of the modifications done previously.
• Temporary Tables: For temporary work of different database operations, you need to use temporary tables. The temporary tables (DGTTs) do not appear in system catalog, XML columns cannot be used in created temporary tables.
• Materialized Query Tables: MQT can be used to improve the performance of queries. These types of tables are defined by a query, which is used to determine the data in the tables.

Creating Tables

The following syntax creates table:

Syntax: [To create a new table]

db2 create table <schema_name>.<table_name>
(column_name column_type....) in <tablespace_name>

Example: We create a table to store “employee” details in the schema of “professional”. This table has “id, name, jobrole, joindate, salary” fields and this table data would be stored in tablespace “ts1”.

db2 create table professional.employee(id int, name
varchar(50),jobrole varchar(30),joindate date,
salary double) in ts1

Output:

DB20000I The SQL command completed successfully.

Listing table details

The following syntax is used to list table details:

Syntax: [To see the list of tables created with schemas]

db2 select tabname, tabschema, tbspace from syscat.tables

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

db2 select tabname, tabschema, tbspace from syscat.tables

Output:

TABNAME      TABSCHEMA     TBSPACE
------------ ------------- --------
EMPLOYEE     PROFESSIONAL    TS1


 1 record(s) selected.

Listing columns in a table

The following syntax lists columns in a table:

Syntax: [To see columns and data types of a table]

db2 describe table <table_name>

Example: [To see the columns and data types of table ‘employee’]

db2 describe table professional.employee

Output:

             Data type                   Column
Column name  schema    Data type name    Length    Scale Nulls
------ ----- --------- ----------------- --------- ----- ------
ID           SYSIBM    INTEGER             4         0     Yes
NAME         SYSIBM    VARCHAR             50        0     Yes
JOBROLE      SYSIBM    VARCHAR             30        0     Yes
JOINDATE     SYSIBM    DATE                4         0     Yes
SALARY       SYSIBM    DOUBLE              8         0     Yes

  5 record(s) selected.

Hidden Columns

You can hide an entire column of a table. If you call “select * from” query, the hidden columns are not returned in the resulting table. When you insert data into a table, an “INSERT” statement without a column list does not expect values for any implicitly hidden columns. These type of columns are highly referenced in materialized query tables. These type of columns do not support to create temporary tables.

Creating table with hidden column

The following syntax creates table with hidden columns:

Syntax: [To create a table with hidden columns]

db2 create table <tab_name> (col1 datatype,col2 datatype
implicitly hidden)

Example: [To create a ‘customer’ table with hidden columns ‘phone’]

db2 create table professional.customer(custid integer not
null, fullname varchar(100), phone char(10)
implicitly hidden)

Inserting data values in table

The following syntax inserts values in the table:

Syntax: [To insert values into a table]

db2 insert into <tab_name>(col1,col2,...)
 values(val1,val2,..)

Example: [To insert values in ‘customer’ table]

db2 insert into professional.customer(custid, fullname, phone)
values(100,''ravi'',''9898989'')


db2 insert into professional.customer(custid, fullname, phone)
values(101,''krathi'',''87996659'')


db2 insert into professional.customer(custid, fullname, phone)
values(102,''gopal'',''768678687'')

Output:

DB20000I  The SQL command completed successfully.

Retrieving values from table

The following syntax retrieves values from the table:

Syntax: [To retrieve values form a table]

db2 select * from &lttab_name>

Example: [To retrieve values from ‘customer’ table]

db2 select * from professional.customer

Output:

CUSTID      FULLNAME
----------- ------------------------
        100 ravi

        101 krathi

        102 gopal

  3 record(s) selected.

Retrieving values from a table including hidden columns

The following syntax retrieves values from selected columns:

Syntax: [To retrieve selected hidden columns values from a table]

db2 select col1,col2,col3 from <tab_name>

Example: [To retrieve selected columns values result from a table]

db2 select custid,fullname,phone from professional.customer

Output:

CUSTID  FULLNAME    PHONE
------- ---------   ------------
100     ravi        9898989

101     krathi      87996659

102     gopal       768678687

  3 record(s) selected.

If you want to see the data in the hidden columns, you need to execute “DESCRIBE” command.

Syntax:

db2 describe table <table_name> show detail

Example:

db2 describe table professional.customer show detail

Output:

Column name     Data type schema     Data type name  Column
           column    Partitionkey  code
                                       Length   Scale    Nulls
number     sequence      page     Hidden      Default
--------------- -------------------- --------------- -------- ----
---- -------- ---------- ------------- -------- ----------- ------
---
CUSTID          SYSIBM               INTEGER         4        0
No       0          0         0        No
FULLNAME        SYSIBM               VARCHAR         100      0
Yes      1          0        1208     No

PHONE           SYSIBM               CHARACTER       10       0
Yes      2          0             1208     Implicitly

3 record(s) selected.

Altering the type of table columns

You can modify our table structure using this “alter” command as follows:

Syntax:

db2 alter table <tab_name> alter column <col_name> set data type <data_type>

Example: [To modify the data type for column “id” from “int” to “bigint” for employee table]

db2 alter table professional.employee alter column id set data type bigint

Output::

DB20000I The SQL command completed successfully.

Altering column name

You can change column name as shown below:

Syntax: [To modify the column name from old name to new name of a table]

db2 alter table <tab_name> rename column <old_name> to <new_name>

Example: [To modify the column name from “fullname” to “custname” in “customers” table.]

db2 alter table professional.customer rename column fullname to custname

Dropping the tables

To delete any table, you need to use the “DROP” command as follows:

Syntax:

db2 drop table <tab_name>

Example: [To drop customer table form database]

db2 drop table professional.customers

To delete the entire hierarchy of the table (including triggers and relation), you need to use “DROP TABLE HIERARCHY” command.

Syntax:

db2 drop table hierarchy <tab_name>

Example: [To drop entire hierarchy of a table ‘customer’]

db2 drop table hierarchy professional.customers

This chapter describes triggers, their types, creation and dropping of the triggers.

Introduction

A trigger is a set of actions, which are performed for responding to an INSERT, UPDATE or DELETE operation on a specified table in the database. Triggers are stored in the database at once. They handle governance of data. They can be accessed and shared among multiple applications. The advantage of using triggers is, if any change needs to be done in the application, it is done at the trigger; instead of changing each application that is accessing the trigger. Triggers are easy to maintain and they enforce faster application development. Triggers are defined using an SQL statement “CREATE TRIGGER”.

Types of triggers

There are two types of triggers:

1. BEFORE triggers

They are executed before any SQL operation.

2. AFTER triggers

They are executed after any SQL operation.

Creating a BEFORE trigger

Let us see how to create a sequence of trigger:

Syntax:

db2 create sequence <seq_name>

Example: Creating a sequence of triggers for table shopper.sales1

db2 create sequence sales1_seq as int start with 1 increment by 1

Syntax:

db2 create trigger <trigger_name> no cascade before insert on
<table_name> referencing new as <table_object> for each row set
<table_object>.<col_name>=nextval for <sequence_name>

Example: Creating trigger for shopper.sales1 table to insert primary key numbers automatically

db2 create trigger sales1_trigger no cascade before insert on
shopper.sales1 referencing new as obj for each row set
obj.id=nextval for sales1_seq

Now try inserting any values:

db2 insert into shopper.sales1(itemname, qty, price)
values(''bicks'', 100, 24.00)

Retrieving values from table

Let us see how to retrieve values from a table:

Syntax:

db2 select * from <tablename>

Example:

db2 select * from shopper.sales1

Output:

  ID       ITEMNAME       QTY
-------  ------------   ----------
    3      bicks            100
    2      bread            100

  2 record(s) selected.

Creating an AFTER trigger

Let us see how to create an after trigger:

Syntax:

db2 create trigger <trigger_name> no cascade before insert on
<table_name> referencing new as <table_object> for each row set
 <table_object>.<col_name>=nextval for <sequence_name>

Example: [To insert and retrieve the values]

db2 create trigger sales1_tri_after after insert on shopper.sales1
for each row mode db2sql begin atomic update shopper.sales1
set price=qty*price; end

Output:

//inseting values in shopper.sales1
db2 insert into shopper.sales1(itemname,qty,price)
values(''chiken'',100,124.00)
//output
ID    ITEMNAME       QTY         PRICE
----- -------------- ----------- -----------
    3 bicks          100         2400.00
    4 chiken         100         12400.00
    2 bread          100         2400.00

	3 record(s) selected.

Dropping a trigger

Here is how a database trigger is dropped:

Syntax:

db2 drop trigger <trigger_name>

Example:

db2 drop trigger slaes1_trigger

This chapter describes the creation of alias and retrieving data using alias of database objects.

Introduction

Alias is an alternative name for database objects. It can be used to reference the database object. You can say, it is a nick name for database objects. Alias are defined for the objects to make their name short, thereby reducing the query size and increasing readability of the query.

Creating database object aliases

You can create database object alias as shown below:

Syntax:

db2 create alias <alias_name> for <table_name>

Example: Creating alias name for table “professional.customer” table

db2 create alias pro_cust for professional.customer

If you pass “SELECT * FROM PRO_CUST” or “SELECT * FROM PROFESSIONAL.CUSTOMER” the database server will show the same result.

Syntax: [To retrieve values from a table directly with schema name]

db2 select * from <schema_name>.<table_name>

Example: [To retrieve values from table customer]

db2 select * from professional.customer

Output:

CUSTID  FULLNAME    PHONE
------- ---------   ------------
100     ravi        9898989
101     krathi      87996659
102     gopal       768678687

  3 record(s) selected.

Retrieving values using alias name of the table

You can retrieve values from database using alias name as shown below:

Syntax: [To retrieve values from table by calling alias name of the table]

db2 select * from <alias_name>

Example: [To retrieve values from table customer using alias name]

db2 select * from pro_cust

Output:

CUSTID  FULLNAME    PHONE
------- ---------   ------------
100     ravi        9898989
101     krathi      87996659
102     gopal       768678687

  3 record(s) selected.

This chapter introduces you to the concept of sequence, creation of sequence, viewing the sequence, and dropping them.

Introduction

A sequence is a software function that generates integer numbers in either ascending or descending order, within a definite range, to generate primary key and coordinate other keys among the table. You use sequence for availing integer numbers say, for employee_id or transaction_id. A sequence can support SMALLINT, BIGINT, INTEGER, and DECIMAL data types. A sequence can be shared among multiple applications. A sequence is incremented or decremented irrespective of transactions.

A sequence is created by CREATE SEQUENCE statement.

Types of Sequences

There are two type of sequences available:

• NEXTVAL: It returns an incremented value for a sequence number.

• PREVIOUS VALUE: It returns recently generated value.

Parameters of sequences

The following parameters are used for sequences:

Data type: This is the data type of the returned incremented value. (SMALLINT, BIGINT, INTEGER, NUMBER, DOUBLE)

START WITH: The reference value, with which the sequence starts.

MINVALUE: A minimum value for a sequence to start with.

MAXVALUE: A maximum value for a sequence.

INCREMENT BY: step value by which a sequence is incremented.

Sequence cycling: the CYCLE clause causes generation of the sequence repeatedly. The sequence generation is conducted by referring the returned value, which is stored into the database by previous sequence generation.

Creating a sequence

You can create sequence using the following syntax:

Syntax:

db2 create sequence <seq_name>

Example: [To create a new sequence with the name ‘sales1_seq’ and increasing values from 1]

db2 create sequence sales1_seq as int start
with 1 increment by 1

Viewing the sequences

You can view a sequence using the syntax given below:

Syntax:

db2 value <previous/next> value for <seq_name>

Example: [To see list of previous updated value in sequence ‘sales1_seq’]

db2 values previous value for sales1_seq

Output:

 1
-----------
  4
  1 record(s) selected.

Dropping the sequence

To remove the sequence, you need to use the “DROP SEQUENCE ” command. Here is how you do it:

Syntax:

db2 drop sequence <seq_name>>

Example: [To drop sequence ‘sales1_seq’ from database]

db2 drop sequence sales1_seq

Output:

 DB20000I The SQL command completed successfully.

This chapter describes creating, activating and deactivating the databases with the associated syntax.

Database architecture

A database is a collection of Tables, Schemas, Bufferpools, Logs, Storage groups and Tablespaces working together to handle database operations efficiently.

Database directory

Database directory is an organized repository of databases. When you create a database, all the details about database are stored in a database directory, such as details of default storage devices, configuration files, and temporary tables list etc.

Partition global directory is created in the instance folder. This directory contains all global information related to the database. This partition global directory is named as NODExxxx/SQLyyy, where xxxx is the data partition number and yyy is the database token.

In the partition-global directory, a member-specific directory is created. This directory contains local database information. The member-specific directory is named as MEMBERxxxx where xxxx is a member number. DB2 Enterprise Server Edition environment runs on a single member and has only one member specific directory. This member specific directory is uniquely named as MEMBER0000.

Partitioned global directory

Directory Location : <instance>/NODExxx/SQLxxx

The partition-global directory contains database related files as listed below.

• Global deadlock write-to-file event monitoring files
• Table space information files [SQLSPCS.1, SQLSPCS.2]
• Storage group control files [SQLSGF.1, SQLSGF.2]
• Temporary table space container files. [/storage path//T0000011/C000000.TMP/SQL00002.MEMBER0001.TDA]
• Global Configuration file [SQLDBCONF]
• History files [DB2RHIST.ASC, DB2RHIST.BAK, DB2TSCHG.HIS, DB2TSCHG.HIS]
• Logging-related files [SQLOGCTL.GLFH.1, SQLOGCTL.GLFH.2]
• Locking files [SQLINSLK, SQLTMPLK]
• Automatic Storage containers

Member specific directory

Directory location : /NODExxxx/SQLxxxx/MEMBER0000

This directory contains:

• Objects associated with databases
• Buffer pool information files [SQLBP.1, SQLBP.2]
• Local event monitoring files
• Logging-related files [SQLOGCTL.LFH.1, SQLOGCTL.LFH.2, SQLOGMIR.LFH].
• Local configuration files
• Deadlocks event monitor file. The detailed deadlock events monitor files are stored in the database directory of the catalog node in case of ESE and partitioned database environment.

Creating database

You can create a database in instance using the “CREATE DATABASE” command. All databases are created with the default storage group “IBMSTOGROUP”, which is created at the time of creating an instance. In DB2, all the database tables are stored in “tablespace”, which use their respective storage groups.

The privileges for database are automatically set as PUBLIC [CREATETAB, BINDADD, CONNECT, IMPLICIT_SCHEMA, and SELECT], however, if the RESTRICTIVE option is present, the privileges are not granted as PUBLIC.

Creating non-restrictive database

This command is used to create a non-restrictive database.

Syntax: [To create a new Database. ‘database_name’ indicates a new database name, which you want to create.]

db2 create database <database name>

Example: [To create a new non-restrictive database with name ‘one’]

db2 create database one

Output:

DB20000I The CREATE DATABASE command completed successfully.

Creating restrictive database

Restrictive database is created on invoking this command.

Syntax: [In the syntax below, “db_name” indicates the database name.]

db2 create database <db_name> restrictive

Example: [To create a new restrictive database with the name ‘two’]

db2 create database two restrictive

Creating database with different user defined location

Create a database with default storage group “IBMSTOGROUP” on different path. Earlier, you invoked the command “create database” without any user-defined location to store or create database at a particular location. To create the database using user- defined database location, the following procedure is followed:

Syntax: [In the syntax below, ‘db_name’ indicates the ‘database name’ and ‘data_location’ indicates where have to store data in folders and ‘db_path_location’ indicates driver location of ‘data_location’.]

db2 create database ''<db_name>'' on ''<data location>'' dbpath on ''<db_path_location>''

Example: [To create database named ‘four’, where data is stored in ‘data1’ and this folder is stored in ‘dbpath1’]

db2 create database four on ''/data1'' dbpath on ''/dbpath1''

Viewing local or system database directory files

You execute this command to see the list of directories available in the current instance.

Syntax:

db2 list database directory

Example:

db2 list database directory

Output:

 System Database Directory
 Number of entries in the directory = 6
 Database 1 entry:
 Database alias                       = FOUR
 Database name                        = FOUR
 Local database directory             =
 /home/db2inst4/Desktop/dbpath
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
Database 2 entry:
Database alias                       = SIX
Database name                        = SIX
Local database directory             = /home/db2inst4
Database release level               = f.00
Comment                              =
Directory entry type                 = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =

Activating database

This command starts up all necessary services for a particular database so that the database is available for application.

Syntax:[‘db_name’ indicates database name]

db2 activate db <db_name>

Example: [Activating the database ‘one’]

db2 activate db one

Deactivating database

Using this command, you can stop the database services.

Syntax:

db2 deactivate db <db_name>

Example: [To Deactivate database ‘one’]

db2 deactivate db one

Connecting to database

After creating a database, to put it into use, you need to connect or start database.

Syntax:

db2 connect to <database name>

Example: [To Connect Database one to current CLI]

db2 connect to one

Output:

 Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST4
 Local database alias   = ONE

Verifying if database is restrictive

To check if this database is restrictive or not, here is the syntax:

Syntax: [In the following syntax, ‘db’ indicates Database, ‘cfg’ indicates configuration, ‘db_name’ indicates database name]

db2 get db cfg for <db_name> | grep -i restrict

Example: [To check if ‘one’ database is restricted or not]

db2 get db cfg for one | grep -i restrict

Output:

Restrict access                       = NO

Configuring the database manager and the database

Instance configuration (Database manager configuration) is stored in a file named ”db2system” and the database related configuration is stored in a file named ”SQLDBCON”. These files cannot be edited directly. You can edit these files using tools which call API. Using the command line processor, you can use these commands.

Database Manager Configuration Parameters

Syntax: [To get the information of Instance Database manager]

db2 get database manager configuration

OR

db2 get dbm cfg

Syntax: [To update instance database manager]

db2 update database manager configuration

OR

db2 update dbm cfg

Syntax: [To reset previous configurations]

db2 reset database manager configuration

OR

db2 reset dbm cfg

Database Configuration Parameters

Syntax: [To get the information of Database]

db2 get database configuration

OR

db2 get db cfg

Syntax: [To update the database configuration]

db2 update database configuration

OR

db2 update db cfg

Syntax: [To reset the previously configured values in database configuration

db2 reset database configuration

OR

db2 reset db cfg

Syntax: [To check the size of Current Active Database]

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

Example: [To verify the size of Currently Activate Database]

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

Output:

Value of output parameters
--------------------------
Parameter Name  : SNAPSHOTTIMESTAMP
Parameter Value : 2014-07-02-10.27.15.556775
Parameter Name  : DATABASESIZE
Parameter Value : 105795584
Parameter Name  : DATABASECAPACITY
Parameter Value : 396784705536
Return Status = 0

Estimating space required for database

To estimate the size of a database, the contribution of the following factors must be considered:

• System Catalog Tables
• User Table Data
• Long Field Data
• Large Object (LOB) Data
• Index Space
• Temporary Work Space
• XML data
• Log file space
• Local database directory
• System files

Checking database authorities

You can use the following syntax to check which database authorities are granted to PUBLIC on the non-restrictive database.

Step 1: connect to database with authentication user-id and password of instance.

Syntax: [To connect to database with username and password]

db2 connect to <db_name> user <userid> using <password>

Example: [To Connect “one” Database with the user id ‘db2inst4’ and password ‘db2inst4’]

db2 connect to one user db2inst4 using db2inst4

Output:

 Database Connection Information
 Database server        = DB2/LINUXX8664 10.1.0
 SQL authorization ID   = DB2INST4
 Local database alias   = ONE

Step2: To verify the authorities of database.

Syntax: [The syntax below shows the result of authority services for current database]

db2 "select substr(authority,1,25) as authority, d_user, d_group,
d_public, role_user, role_group, role_public,d_role from table(
sysproc.auth_list_authorities_for_authid (''public'',''g''))as t
order by authority"

Example:

db2 "select substr(authority,1,25) as authority, d_user, d_group,
d_public, role_user, role_group, role_public,d_role from table(
sysproc.auth_list_authorities_for_authid (''PUBLIC'',''G''))as t
order by authority"

Output:

AUTHORITY                 D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
------------------------- ------ ------- -------- --------- ---------- ----------- ------
ACCESSCTRL                *      *       N        *         *          N           *
BINDADD                   *      *       Y        *         *          N           *
CONNECT                   *      *       Y        *         *          N           *
CREATETAB                 *      *       Y        *         *          N           *
CREATE_EXTERNAL_ROUTINE   *      *       N        *         *          N           *
CREATE_NOT_FENCED_ROUTINE *      *       N        *         *          N           *
CREATE_SECURE_OBJECT      *      *       N        *         *          N           *
DATAACCESS                *      *       N        *         *          N           *
DBADM                     *      *       N        *         *          N           *
EXPLAIN                   *      *       N        *         *          N           *
IMPLICIT_SCHEMA           *      *       Y        *         *          N           *
LOAD                      *      *       N        *         *          N           *
QUIESCE_CONNECT           *      *       N        *         *          N           *
SECADM                    *      *       N        *         *          N           *
SQLADM                    *      *       N        *         *          N           *
SYSADM                    *      *       *        *         *          *           *
SYSCTRL                   *      *       *        *         *          *           *
SYSMAINT                  *      *       *        *         *          *           *
SYSMON                    *      *       *        *         *          *           *
WLMADM                    *      *       N        *         *          N           *
20 record(s) selected.

Dropping Database

Using the Drop command, you can remove our database from instance database directory. This command can delete all its objects, table, spaces, containers and associated files.

Syntax: [To drop any database from an instance]

db2 drop database <db_name>

Example: [To drop ‘six’ database from instance]

db2  drop database six

Output:

DB20000I The DROP DATABASE command completed successfully

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"