Category: db2

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 ”;

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

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

DB2 Tutorial PDF Version Quick Guide Resources Job Search Discussion This tutorial provides you the basic understanding of concepts of database, database installation and management. At the end of the tutorial you should be equipped with well understanding of database management concepts. Audience This tutorial is designed for the readers pursuing education in database management domain and all enthusiastic readers. Prerequisites This tutorial is designed and developed for absolute beginners. Though, awareness about software systems, operating systems and computer fundamentals would be beneficial. Print Page Previous Next Advertisements ”;