Category: db2

DB2 – Roles ”; Previous Next Introduction A role is a database object that groups multiple privileges that can be assigned to users, groups, PUBLIC or other roles by using GRANT statement. Restrictions on roles A role cannot own database objects. Permissions and roles granted to groups are not considered when you create the following database objects. Package Containing static SQL Views Materialized Query Tables (MQT) Triggers SQL Routines Creating and granting membership in roles Syntax: [To create a new role] db2 create role <role_name> Example: [To create a new role named ‘sales’ to add some table to be managed by some user or group] db2 create role sales Output: DB20000I The SQL command completed successfully. Granting role from DBADM to a particular table Syntax: [To grant permission of a role to a table] db2 grant select on table <table_name> to role <role_name> Example: [To add permission to manage a table ‘shope.books’ to role ‘sales’] db2 grant select on table shope.books to role sales Output: DB20000I The SQL command completed successfully. Security administrator grants role to the required users. (Before you use this command, you need to create the users.) Syntax: [To add users to a role] db2 grant role <role_name> to user <username> Example: [To add a user ‘mastanvali’ to a role ‘sales’] db2 grant sales to user mastanvali Output: DB20000I The SQL command completed successfully. Role hierarchies For creating a hierarchies for roles, each role is granted permissions/ membership with another role. Syntax: [before this syntax create a new role with name of “production”] db2 grant role <roll_name> to role <role_name> Example: [To provide permission of a role ‘sales’ to another role ‘production’] db2 grant sales to role production Print Page Previous Next Advertisements ”;

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

DB2 Useful Resources ”; Previous Next If you want to list down your website, book or any other resource on this page, then please contact at [email protected]. Useful Links on DB2 Official Website – This site is the official website for DB2. More Details – Know more about DB2. Certification – This website will let you know about DB2 certification. Print Page Previous Next Advertisements ”;

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

DB2 Quick Guide ”; Previous Next DB2 Introduction 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. DB2 Server Installation This chapter describes installation steps of DB2 server. Introduction You can download the DB2 Server trial version or purchase the product license from www.ibm.com. 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

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

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

DB2 – LDAP ”; Previous Next Introduction LDAP is Lightweight Directory Access Protocol. LDAP is a global directory service, industry-standard protocol, which is based on client-server model and runs on a layer above the TCP/IP stack. The LDAP provides a facility to connect to, access, modify, and search the internet directory. The LDAP servers contain information which is organized in the form of a directory tree. The clients ask server to provide information or to perform some operation on a particular information. The server answers the client by providing required information if it has one, or it refers the client to another server for action on required information. The client then acquires the desired information from another server. The tree structure of directory is maintained same across all the participating servers. This is a prominent feature of LDAP directory service. Hence, irrespective of which server is referred to by the client, the client always gets required information in an error-free manner. Here, we use LDAP to authenticate IBM DB2 as a replacement of operating system authentication. There are two types of LDAP: Transparent Plug-in Let us see how to configure a transparent LDAP. Configuring transparent LDAP To start with configuration of transparent LDAP, you need to configure the LDAP server. LDAP server configuration Create a SLAPD.conf file, which contains all the information about users and group object in the LDAP. When you install LDAP server, by default it is configured with basic LDAP directory tree on your machine. The table shown below indicates the file configuration after modification. The text highlighted with yellow the code box means for the following: DBA user-id = “db2my1”, group = “db1my1adm”, password= “db2my1” Admin user-id = “my1adm”, group = “dbmy1ctl”. # base dn: example.com dn: dc=example,dc=com dc: example o: example objectClass: organization objectClass: dcObject # pc box db dn: dc=db697,dc=example,dc=com dc: db697 o: db697 objectClass: organization objectClass: dcObject # # Group: dbadm # dn: cn=dbmy1adm,dc=db697,dc=example,dc=com cn: dbmy1adm objectClass: top objectClass: posixGroup gidNumber: 400 objectClass: groupOfNames member: uid=db2my1,cn=dbmy1adm,dc=db697,dc=example,dc=com memberUid: db2my1 # # User: db2 # dn: uid=db2my1,cn=dbmy1adm,dc=db697,dc=example,dc=com cn: db2my1 sn: db2my1 uid: db2my1 objectClass: top objectClass: inetOrgPerson objectClass: posixAccount uidNumber: 400 gidNumber: 400 loginShell: /bin/csh homeDirectory: /db2/db2my1 # # Group: dbctl # dn: cn=dbmy1ctl,dc=db697,dc=example,dc=com cn: dbmy1ctl objectClass: top objectClass: posixGroup gidNumber: 404 objectClass: groupOfNames member: uid=my1adm,cn=dbmy1adm,dc=db697,dc=example,dc=com memberUid: my1adm # # User: adm # dn: uid=my1adm,cn=dbmy1ctl,dc=db697,dc=example,dc=com cn: my1adm sn: my1adm uid: my1adm objectClass: top objectClass: inetOrgPerson objectClass: posixAccount uidNumber: 404 gidNumber: 404 loginShell: /bin/csh homeDirectory: /home/my1adm Save the above file with name ‘/var/lib/slapd.conf’, then execute this file by following command to add these values into LDAP Server. This is a linux command; not a db2 command. ldapadd r- -D ‘cn=Manager,dc=example,dc=com” –W –f /var/lib/slapd.conf After registering the DB2 users and the DB2 group at the LDAP Server, logon to the particular user where you have installed instance and database. You need to configure LDAP client to confirm to client where your server is located, be it remote or local. LDAP client configuration The LDAP Client configuration is saved in the file ‘ldap.conf’. There are two files available for configuration parameters, one is common and the other is specific. You should find the first one at ‘/etc/ldap.conf’ and the latter is located at ‘/etc/openldap/ldap.conf’. The following data is available in common LDAP client configuration file # File: /etc/ldap.conf # The file contains lots of more entries and many of them # are comments. You show only the interesting values for now host localhost base dc=example,dc=com ldap_version 3 pam_password crypt pam_filter objectclass=posixAccount nss_map_attribute uniqueMember member nss_base_passwd dc=example,dc=com nss_base_shadow dc=example,dc=com nss_base_group dc=example,dc=com You need to change the location of server and domain information according to the DB2 configuration. If we are using server in same system then mention it as ‘localhost’ at ‘host’ and at ‘base’ you can configure which is mentioned in ‘SLAPD.conf’ file for LDAP server. Pluggable Authentication Model (PAM) is an API for authentication services. This is common interface for LDAP authentication with an encrypted password and special LDAP object of type posixAccount. All LDAP objects of this type represent an abstraction of an account with portable Operating System Interface (POSIX) attributes. Network Security Services (NSS) is a set of libraries to support cross-platform development of security-enabled client and server applications. This includes libraries like SSL, TLS, PKCS S/MIME and other security standards. You need to specify the base DN for this interface and two additional mapping attributes. OpenLDAP client configuration file contains the entries given below: host localhost base dc=example,dc=com Till this you just define the host of LDAP serve and the base DN. Validating OpenLDAP environment After you configured your LDAP Server and LDAP Client, verify both for communication. Step1: Check your Local LDAP server is running. Using below command: ps -ef | grep -i ldap This command should list the LDAP deamon which represents your LDAP server: /usr/lib/openldap/slapd -h ldap:/// -u ldap -g ldap -o slp=on This indicates that you LDAP server is running and is waiting for request from clients. If there is no such process for previous commands you can start LDAP server with the ’rcldap’ command. rcldap start When the server starts, you can monitor this in the file ‘/var/log/messages/ by issuing the following command. tail –f /var/log/messages Testing connection to LDAP server with ldapsearch The ldapsearch command opens a connection to an LDAP server, binds to it and performs a search query which can be specified by using special parameters ‘-x’ connect to your LDAP server with a simple authentication mechanism by using the –x parameter instead of a more complex mechanism like Simple Authentication and Security Layer (SASL) ldapsearch –x LDAP server should reply with a response given below, containing all of your LDAP entries in a LDAP Data Interchange Format(LDIF). # extended LDIF # # LDAPv3 # base <> with scope subtree # filter: (objectclass=*) # requesting: ALL # example.com dn: dc=example, dc=com dc: example o: example objectClass: organization objectClass: dcObject # search result search: 2 result: 0 Success # numResponses: 2 # numEntries:

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

DB2 Questions and Answers ”; Previous Next DB2 Questions and Answers has been designed with a special intention of helping students and professionals preparing for various Certification Exams and Job Interviews. This section provides a useful collection of sample Interview Questions and Multiple Choice Questions (MCQs) and their answers with appropriate explanations. SN Question/Answers Type 1 DB2 Interview Questions This section provides a huge collection of DB2 Interview Questions with their answers hidden in a box to challenge you to have a go at them before discovering the correct answer. 2 DB2 Online Quiz This section provides a great collection of DB2 Multiple Choice Questions (MCQs) on a single page along with their correct answers and explanation. If you select the right option, it turns green; else red. 3 DB2 Online Test If you are preparing to appear for a Java and DB2 related certification exam, then this section is a must for you. This section simulates a real online test along with a given timer which challenges you to complete the test within a given time-frame. Finally you can check your overall test score and how you fared among millions of other candidates who attended this online test. 4 DB2 Mock Test This section provides various mock tests that you can download at your local machine and solve offline. Every mock test is supplied with a mock test key to let you verify the final score and grade yourself. Print Page Previous Next Advertisements ”;