Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
IBM DB2 9.7 Advanced Administration Cookbook
IBM DB2 9.7 Advanced Administration Cookbook

IBM DB2 9.7 Advanced Administration Cookbook: Over 100 recipes focused on advanced administration tasks to build and configure powerful databases with IBM DB2 book and ebook

eBook
AU$70.99 AU$78.99
Paperback
AU$97.99
Subscription
Free Trial
Renews at AU$24.99p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Table of content icon View table of contents Preview book icon Preview Book

IBM DB2 9.7 Advanced Administration Cookbook

Chapter 1. DB2 Instance—Administration and Configuration

In this chapter, we will cover:

  • Creating and configuring instances for non-partitioned environments

  • Creating and configuring a client instance

  • Creating and configuring instances for multipartitioned environments

  • Starting and stopping instances

  • Configuring SSL for client-server instance communication

  • Listing instances

  • Attaching to instances

  • Dropping instances

Introduction


The main focus of this chapter is DB2 instance creation and configuration, for non-partitioned database and for multipartitioned database environments.

Creating and configuring instances for non-partitioned environments


A DB2 instance can be defined as a logical container or as a logical context for databases. It can also be described as a layer between DB2 software binaries, a database, and its objects. Also it provides a level of isolation between databases; for example, it is possible to have two or more databases on the same environment, with the same name, but under different instances. It also provides and ensures the communication layer between clients and databases.

Getting ready

For this recipe (and almost all recipes in this book), we will use two servers running Red Hat Enterprise Linux Server x64 release 5.5 (Tikanga), named nodedb21 and nodedb22. The hostnames are optional, but our recommendation is to set up an identical environment to avoid confusion during reading and applying the recipes.

As install location for the IBM DB2 9.7 Enterprise Server Enterprise software product, we will use the directory /opt/ibm/db2/V9.7 on nodedb21. On nodedb22, we will install DB2 Client software to location /opt/ibm/db2/V9.7_clnt. The instance owner will be db2inst1 on nodedb21 and db2clnt1 as client instance owner on nodedb22. Also, on nodedb21, we will create a second instance owner user named db2inst2, to demonstrate how to create an instance manually.

How to do it...

The default method to create an instance is during the IBM DB2 9.7 Enterprise Server Edition software installation. The other possible option is to use the db2icrt command.

In Linux and Unix, every instance is created under a dedicated user, called the instance owner. To create an instance in Linux and UNIX you have to be the root user; on these platforms, we are limited to one instance per user. On Microsoft Windows platforms, you may have more than one instance created under the same user.

Usually, if you set up the software in graphical mode you do not have to create the users manually—you can do this using the wizard. In our recipes, we want to reuse the same groups (db2iadm1 and db2fadm1) for the non-partitioned and the multipartitioned instance and database setup. For the multipartitioned setup we will have the same groups defined on both servers; because we have to deal with security regarding permissions, here, we should create the groups with the same group ID (GID):

  1. Create primary groups with the same GID on both servers:

    	[root@nodedb21 ~]# groupadd -g 1103 db2iadm1
    	[root@nodedb21 ~]# groupadd -g 1102 db2fadm1
    	[root@nodedb21 ~]#
    	[root@nodedb22 ~]# groupadd -g 1103 db2iadm1
    	[root@nodedb22 ~]# groupadd -g 1102 db2fadm1
    	[root@nodedb22 ~]#
    

    Tip

    Downloading the example code

    You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

  2. Run db2setup from the IBM DB2 9.7 Enterprise Server Edition software installation kit.

    Note

    Instance owner user db2inst and fenced user db2fenc will be created during installation. The groups db2iadm1 and db2fadm1 will automatically fill in on the screen.

  3. To create a new instance during the installation with db2setup in graphical mode, navigate through configuration steps 1 to 6 and, at step 7 you will find Create a DB2 instance option checked; this is the default option.and let as it is.Click Next.

  4. At step 8—Partitioning options—you will find Single partition instance option checked ; this is the default option and let as it is. Click Next and finalize installation. If installation was successful, we have a new instance named db2inst1 created.

    Another way to create an instance is to use the db2icrt command. This method is suitable in the case that you install the DB2 software with db2_install (manual installation), or that you do not check the Create a DB2 instance option during installation with db2setup. Other scenarios would be if you drop an instance and want to create a new one, or if you want to create an additional instance.

  5. As mentioned previously, in Linux and Unix, every instance has to be created under an instance owner user. As a root user, we will create the user db2inst2 as instance owner and db2fenc2 as fenced user; set passwords identical to the individual usernames:

    	[root@nodedb21 ~]# useradd -g db2iadm1 db2inst2
    	[root@nodedb21 ~]# useradd -g db2fadm1 db2fenc2
    	[root@nodedb21 ~]# passwd db2inst2
    	Changing password for user db2inst2.
    	New UNIX password:
    	Retype new UNIX password:
    	passwd: all authentication tokens updated successfully.
    	[root@nodedb21 ~]# passwd db2fenc2
    	Changing password for user db2fenc2.
    	New UNIX password:
    	Retype new UNIX password:
    	passwd: all authentication tokens updated successfully.
    	[root@nodedb21 ~]#
    
  6. At this step, set the communication protocol to TCP/IP. The instance communication protocol is set up using the DB2COMM variable. We can set this variable no protocol managers will be started and will lead to communication errors at the client side.

    	[db2inst2@nodedb21 ~]$ db2set DB2COMM=TCPIP
    	[db2inst2@nodedb21 ~]$
    
  7. Next, as user root, edit /etc/services and add db2c_db2inst2 50002/tcp entry (highlighted in bold in the listing bellow). Port 50002 will be assigned to db2inst2 instance. Port 50001 corresponds to the db2c_db2inst1 service name and was added at db2inst1 instance creation. Port names prefixed with DB2 are reserved for inter-partition communication, a subject that we're going to discuss later on.

    	db2c_db2inst1   50001/tcp
    	DB2_db2inst1    60000/tcp
    	DB2_db2inst1_1  60001/tcp
    	DB2_db2inst1_2  60002/tcp
    	DB2_db2inst1_END        60003/tcp
    	db2c_db2inst2 50002/tcp
    

    Note

    If you choose to use only port numbers for SVCENAME database manager parameter you do not need to edit this file.

  8. As root user, create instance db2inst2, using the previously created users as instance owner and fenced user:

    [root@nodedb21.~]# /opt/ibm/db2/V9.7/instance/db2icrt -a SERVER ENCRYPT -p db2c_db2inst2 -u db2fenc2 db2inst2
    DBI1070I  Program db2icrt completed successfully.
    [root@nodedb21 ~]#
    

    We need to explain a little bit about the options used for creating instance db2inst2:

    • The –a option indicates the authentication type; the default is SERVER. Using the –a option, the following authentication modes are available: SERVER, CLIENT, and SERVER ENCRYPT. We may change it later by modifying the AUTHENTICATION or the SRVCONN_AUTH instance parameter.

    • The –u switch is used to set the fenced user.

    • The –p option is used to specify the port or its corresponding service name used for client communication, as defined in /etc/services. The port or service name may be changed later by modifying the SVCENAME database manager parameter

    • For MS Windows platforms, we don't have the –a option to specify the authentication mode. The –p option in Windows has a different meaning; it is used to specify the instance profile. The –u option is for specifying the account name and password used that will be included in the Windows service definition associated with the instance.

Note

To use the Control Center for managing an instance locally or remotely, you need to have DB2 Administration Server (DAS) up and running, on the server.

To check the status of DAS, execute the following command, as DAS owner user, which is in our case dasusr1:

[dasusr1@nodedb21 ~]$ db2dascfg get dasstatus

ACTIVE

[dasusr1@nodedb21 ~]$

Usually, it is installed and created during IBM DB2 software installation. If there is no DAS created, you should create it using the dascrt command. The steps are similar to those for creating an instance—create a group and a user. It has to be created by specifying the owner.

For example, /opt/ibm/db2/V9.7/instance/dascrt –u dasusr1.

How it works...

In Linux or Unix, when an instance is created, the db2icrt command builds up under the instance owner home directory, the sqllib directory, as a collection of symbolic links pointing to the IBM DB2 software installation home directory. If you want to see what is executing db2icrt in the background, you need to include the –d option to enable debug mode. This explains what happens behind the scenes for the steps mentioned earlier. Usually, this switch is used for detailed diagnostics, and should be activated at the request of IBM support.

Almost all files and directories from sqllib directory are symbolic links to the corresponding installation path (DB2HOME). A short listing inside sqllib directory looks like this:

[db2inst1@nodedb21]/home/db2inst1/sqllib>symlinks -v .
other_fs: /home/db2inst1/sqllib/map -> /opt/ibm/db2/V9.7/map
other_fs: /home/db2inst1/sqllib/bin -> /opt/ibm/db2/V9.7/bin
other_fs: /home/db2inst1/sqllib/ruby64 -> /opt/ibm/db2/V9.7/dsdriver/ruby64

On MS Windows platforms, the db2icrt command creates a service. The binaries are actually copied and a service associated with the instance is created.

On a generic Windows machine we'll create an instance named db2win. Initially, the associated service has the status set to stopped and the startup type set to manually. If you want the service to start automatically at system boot, you have to change its startup type to automatic.

To create instance db2win, execute the following command under a privileged user:

C:\Windows\system32>db2icrt db2win
DB20000I  The DB2ICRT command completed successfully.

To find the associated Windows service with db2win instance, execute the following command:

C:\Windows\system32>sc query state= all  | findstr "DB2WIN"
SERVICE_NAME: DB2WIN
DISPLAY_NAME: DB2 - DB2COPY1 - DB2WIN
C:\Windows\system32>

There's more...

The db2isetup graphical tool might be used also for creating instances; this tool is available only on the Linux and Unix platforms.

On Linux and Unix you have the possibility to create a non-root type instance using the installer. You are limited to only one non-root instance per server.

Updating instances using the db2iuptd command

Usually this command is used to update an instance after an upgrade to a higher version, or migrate an instance from a lower product level such as Workgroup Edition to Enterprise Edition. Also it might be used for instance debug using the –d option. Like db2icrt, this command has its own particularities on MS Windows operating systems. To find the available options and related descriptions of this command issue db2iuptd –h. For non-root type instances exists a variant of this command named db2nruptd.

Left arrow icon Right arrow icon

Key benefits

  • Master all the important aspects of administration from instances to IBM's newest High Availability technology pureScale with this book and e-book.
  • Learn to implement key security features to harden your database's security against hackers and intruders.
  • Empower your databases by building efficient data configuration using MDC and clustered tables.

Description

IBM DB2 LUW is a leading relational database system developed by IBM. DB2 LUW database software offers industry leading performance, scale, and reliability on your choice of platform on various Linux distributions, leading Unix Systems like AIX, HP-UX and Solaris and MS Windows platforms. With lots of new features, DB2 9.7 delivers one the best relational database systems in the market. IBM DB2 9.7 Advanced Administration Cookbook covers all the latest features with instance creation, setup, and administration of multi-partitioned database. This practical cookbook provides step-by-step instructions to build and configure powerful databases, with scalability, safety and reliability features, using industry standard best practices. This book will walk you through all the important aspects of administration. You will learn to set up production capable environments with multi-partitioned databases and make the best use of hardware resources for maximum performance. With this guide you can master the different ways to implement strong databases with a High Availability architecture.

Who is this book for?

If you are a DB2 Database Administrator who wants to understand and get hands on with the underlying aspects of database administration, then this book is for you. This book assumes that you have a basic understanding of DB2 database concepts.

What you will learn

  • Learn to make best use of scalability with multi-partitioned databases
  • Get to grips with advanced technological breakthroughs with RBAC, and pureScale technology
  • Sharpen your troubleshooting skills
  • Design and implement the best backup strategy
  • Master the new exciting features added to the DB2 9.7 version
  • Get step-by-step instructions to tune and optimize DB2 to achieve best performance
  • Understand the database objects and their integration

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Feb 24, 2012
Length: 480 pages
Edition : 1st
Language : English
ISBN-13 : 9781849683333
Vendor :
IBM
Category :
Tools :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Product Details

Publication date : Feb 24, 2012
Length: 480 pages
Edition : 1st
Language : English
ISBN-13 : 9781849683333
Vendor :
IBM
Category :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
AU$24.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
AU$249.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just AU$5 each
Feature tick icon Exclusive print discounts
AU$349.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just AU$5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total AU$ 286.97
IBM DB2 9.7 Advanced Administration Cookbook
AU$97.99
IBM DB2 9.7 Advanced Application Developer Cookbook
AU$97.99
IBM WebSphere Application Server 8.0 Administration Guide
AU$90.99
Total AU$ 286.97 Stars icon

Table of Contents

14 Chapters
DB2 Instance—Administration and Configuration Chevron down icon Chevron up icon
Administration and Configuration of the DB2 Non-partitioned Database Chevron down icon Chevron up icon
DB2 Multipartitioned Databases—Administration and Configuration Chevron down icon Chevron up icon
Storage—Using DB2 Table Spaces Chevron down icon Chevron up icon
DB2 Buffer Pools Chevron down icon Chevron up icon
Database Objects Chevron down icon Chevron up icon
DB2 Backup and Recovery Chevron down icon Chevron up icon
DB2 High Availability Chevron down icon Chevron up icon
Problem Determination, Event Sources, and Files Chevron down icon Chevron up icon
DB2 Security Chevron down icon Chevron up icon
Connectivity and Networking Chevron down icon Chevron up icon
Monitoring Chevron down icon Chevron up icon
DB2 Tuning and Optimization Chevron down icon Chevron up icon
IBM pureScale Technology and DB2 Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5
(8 Ratings)
5 star 62.5%
4 star 25%
3 star 12.5%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by




Srilu Mar 14, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This is First V9.7 DB2 udb book with detailed info. Book covers all the topics and new fetures for 'DB2 9.7 Advanced Database Administrator including V9.8 db2 pureScale and V9.7 DPF,MDC, Security new features .I read Chapter12 from packtpub about Monitoring .Really good with practical examples .This book will help every DB2 UDB DBA for daily Job duties. I would highly recommend this book to fellow DBAs and developers.Cheers ..Srilu
Amazon Verified review Amazon
aroushdi May 09, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Though IBM has the info center and other publications for DB2 , It is difficult to find the information we need and we jump from a page to another until we find the proper info .This book closes this gap and you find the information needed in a very short time.From my point of view it is targeted to all audience from beginners to the expert .Still relevant even for DB2 V10 with the exeption of the new functionalaties.The only thing I miss in the book is the WINDOWS specific environment .
Amazon Verified review Amazon
Adrian Neagu (OCM) Mar 02, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Excellent book, probably the best on this topic.The explanations and code snippets are outstanding.Very recommended for who want to learn DB2 in a different way.
Amazon Verified review Amazon
DL Apr 23, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This is a great book with quick reference and simple straight forward answers. This book is highly recommended to all DB2 DBA admins.
Amazon Verified review Amazon
Robert W. Cooke Mar 20, 2012
Full star icon Full star icon Full star icon Full star icon Full star icon 5
The IBM DB2 9.7 Advanced Administration Cookbook was a pleasant surprise to review. The book is in more of a step by step format than a book of recipes.The book begins where it should at initial DB2 installation and creating an instance. It then proceeds on to creating databases tablespaces, bufferpools, and database objects.I was most impressed by the Backup and Recovery, Monitoring, and Tuning and Optimization chapters. The Backup and Recovery chapter is short but it covers all forms of online and offline backups and different recovery options. Monitoring covers system, snapshot, and event monitoring as well as using the memory visualizer and health monitor. DB2 Tuning and Optimization gives examples of O/S monitoring, using explain, and how indexes, reorgs, and runstats can help performance.The cookbook also discusses DB2 tools such as db2dart, db2look, db2move, db2pd, reorgchk, list tablespaces and others. It mentions db2top but does not demonstrate it.There is a chapter on DB2 PureScale but I'm not experienced with that product.The cookbook could contain more common problems/issues and troubleshooting but an entire book could be written just on that topic. All in all the IBM DB2 9.7 Advanced Administration Cookbook provides a well rounded approach to a new or novice DB2 DBA. Even an experienced DBA would learn a few things. It does not deep drive into everything but it goes far enough and provides good examples to perform the main DB2 DBA tasks. The IBM DB2 document ion and redbooks will provide the fine details.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.