Category: db2

DB2 – Databases ”; Previous Next 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 – Introduction ”; Previous Next This chapter describes history of DB2, its versions, editions and their respective features. Overview DB2 is a database product from IBM. It is a Relational Database Management System (RDBMS). DB2 is designed to store, analyze and retrieve the data efficiently. DB2 product is extended with the support of Object-Oriented features and non-relational structures with XML. History Initially, IBM had developed DB2 product for their specific platform. Since year 1990, it decided to develop a Universal Database (UDB) DB2 Server, which can run on any authoritative operating systems such as Linux, UNIX, and Windows. Versions For IBM DB2, the UDB current version is 10.5 with the features of BLU Acceleration and its code name as ”Kepler”. All the versions of DB2 till today are listed below: Version Code Name 3.4 Cobweb 8.1, 8.2 Stinger 9.1 Viper 9.5 Viper 2 9.7 Cobra 9.8 It added features with Only PureScale 10.1 Galileo 10.5 Kepler Data server editions and features Depending upon the requirement of needful features of DB2, the organizations select appropriate DB2 version. The following table shows DB2 server editions and their features: Editions Features Advanced Enterprise Server Edition and Enterprise Server Edition (AESE / ESE) It is designed for mid-size to large-size business organizations. Platform – Linux, UNIX, and Windows. Table partitioning High Availability Disaster Recovery (HARD) Materialized Query Table (MQTs) Multidimensional Clustering (MDC) Connection concentrator Pure XML Backup compression Homogeneous Federations Workgroup Server Edition (WSE) It is designed for Workgroup or mid-size business organizations. Using this WSE you can work with – High Availability Disaster Recovery (HARD) Online Reorganization Pure XML Web Service Federation support DB2 Homogeneous Federations Homogeneous SQL replication Backup compression Express -C It provides all the capabilities of DB2 at zero charge. It can run on any physical or virtual systems with any size of configuration. Express Edition It is designed for entry level and mid-size business organizations. It is full featured DB2 data server. It offers only limited services. This Edition comes with – Web Service Federations DB2 homogeneous federations Homogeneous SQL Replications Backup compression Enterprise Developer Edition It offers only single application developer. It is useful to design, build and prototype the applications for deployment on any of the IBM server. The software cannot be used for developing applications. Print Page Previous Next Advertisements ”;

DB2 – Data Types ”; Previous Next This chapter introduces various data types used in DB2. Introduction In DB2 Database tables, each column has its own data type depending on developer’s requirements. The data type is said to be type and range of the values in columns of a table. Built-in data types Datetime TIME: It represents the time of the day in hours, minutes and seconds. TIMESTAMP: It represents seven values of the date and time in the form of year, month, day, hours, minutes, seconds and microseconds. DATE: It represents date of the day in three parts in the form of year, month and day. String Character CHAR (fixed length): Fixed length of Character strings. Varying length VARCHAR: Varying length character strings. CLOB: large object strings, you use this when a character string might exceed the limits of the VARCHAR data type. Graphic GRAPHIC Fixed length: Fixed length graphic strings that contains double-byte characters Varying length VARGRAPHIC: Varying character graphic string that contains double bye characters. DBCLOB: large object type Binary BLOB (varying length): binary string in large object BOOLEAN: In the form of 0 and 1. Signed numeric Exact Binary integer SMALLINT [16BIT]: Using this you can insert small int values into columns INTEGER [32BIT]: Using this you can insert large int values into columns BIGINT [64BIT]: Using this you can insert larger int values into columns Decimal DECIMAL (packed) DECFLOAT (decimal floating point): Using this, you can insert decimal floating point numbers Approximate Floating points REAL (single precision): Using this data type, you can insert single precision floating point numbers. DOUBLE (double precision): Using this data type, you can insert double precision floating point numbers. eXtensible Mark-up Language XML: You can store XML data into this data type column. Print Page Previous Next Advertisements ”;

DB2 – Indexes ”; Previous Next This chapter covers introduction to indexes, their types, creation and dropping. Introduction Index is a set of pointers, which can refer to rows in a table, blocks in MDC or ITC tables, XML data in an XML storage object that are logically ordered by the values of one or more keys. It is created on DB2 table columns to speed up the data access for the queries, and to cluster and partition the data efficiently. It can also improve the performance of operation on the view. A table with a unique index can have rows with unique keys. Depending on the table requirements, you can take different types of indexes. Types of indexes Unique and Non-Unique indexes Clustered and non-clustered indexes Creating indexes For creating unique indexes, you use following syntax: Syntax: db2 create unique index <index_name> on <table_name>(<unique_column>) include (<column_names..>) Example: To create index for “shopper.sales1” table. db2 create unique index sales1_indx on shopper.sales1(id) include (itemname) Dropping indexes For dropping the index, you use the following syntax: Syntax: db2 drop unique index <index_name> on <table_name>(<unique_column>) include (<column_names..>) Example: db2 drop index sales_index Print Page Previous Next Advertisements ”;

DB2 – Instance ”; Previous Next 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

DB2 – Bufferpools ”; Previous Next 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. Print Page Previous Next Advertisements ”;