Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
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
$29.99 $42.99
Paperback
$70.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

IBM DB2 9.7 Advanced Administration Cookbook

Chapter 2. Administration and Configuration of the DB2 Non-partitioned Database

In this chapter, we will cover:

  • Creating and configuring DB2 non-partitioned databases

  • Using configuration advisor

  • Creating a database from an existing backup

  • Configuring automatic database maintenance

  • Managing federated databases, connecting to Oracle and MSSQL

  • Altering databases

  • Dropping databases

Introduction


This chapter provides recipes in order to create a database and get operational in simple and easy steps. We will do so in a manner that respects best practices in the industry.

You have created an instance named db2inst1 on nodedb21, in the previous chapter. You will prepare for available disk space. You will then be able to configure your database for its mission and prepare it for automatic maintenance, so its operation is worry free.

While the nature of these recipes makes them useful right away, it is strongly recommended that they be attempted in a test environment first. We suggest you execute the commands individually, so you can learn as you go along.

Creating and configuring DB2 non-partitioned databases


We will discuss here how to create a single-partitioned database, which is sufficient for most database applications and is the most common configuration for small- to medium-sized databases.

If you plan on having a Business Intelligence (BI) database, you should be planning for a partitioned database. You can estimate one processor core for every 300 GB of data. We will cover this topic in Chapter 3, DB2 Multi-partitioned Databases—Administration and Configuration.

Getting ready

Gather as much technical information as you can about the hardware or virtual machine(s) you have at your disposal, for this database. Identify in which instance you will create your database, and ensure you will have enough memory and disk space for what you need.

Identify the location where you will create the table spaces (filesystems for Unix platforms, disk drives on Windows servers) and how much available space you will have. Make sure the instance owner has...

Using Configuration Advisor


The configuration advisor will help you configure the best settings for the database mission, using this server's hardware configuration. You can then accept or cancel the proposed settings.

Getting ready

Obtain as much information as possible on the database size, future growth, number of concurrent users, and so on.

How to do it...

  1. Select database Configuration Advisor....

    Go to the left pane of Control Center, and expand the databases node. Right-click on the NAV database, and select Configuration Advisor....

    A first screen will ask you to confirm whether this is the database you want to configure; then, click Next.

  2. Choose how much memory you want to allocate to this database.

    You will see a slider bar and the amount of physical memory available on the server. Allow around 300-500 MB for the operating system, or ask your system administrator how much space is needed. Then, you will have to divide the remaining space for each of the active databases. Click Next.

    In our...

Creating a database from an existing backup


Let's suppose you want to copy a production database into a QA server. You can prepare a script using the GUI. It will walk you through the process, and will allow you to save the script. With the GUI, you can also schedule this script as a regular task so you can refresh your QA environment on a weekly basis or at any frequency you wish.

Getting ready

Identify the backup you want to recover, and do a verification to make sure it's valid. In our example, we'll start here from a cold (offline) backup stored on disk, so there is no tape command or rollforward to do.

How to do it...

  1. Make sure the backup is valid.

    Log in as instance owner. Go to the backup location and, from the operating system command line (Linux in this example), type db2 ckbkp:

    	[db2inst1@nodedb21 ~]$ cd /maint/backups
    	[db2inst1@nodedb21 backups]$ db2ckbkp   NAV.0.db2inst1.NODE0000.CATN0000.20101114190028.001
    
    	[1] Buffers processed:  #######
    
    	Image Verification Complete – successful...

Configuring automatic database maintenance


The automatic maintenance of the database involves tools to simplify your administration tasks. You can configure this option anytime after you create the database.

Basically, you define maintenance schedules:

  • online: Period of low activity to allow for runstats

  • offline: Period when there is no activity (no user connecting), for backups and reorgs

You also define maintenance activities:

  • backups: DB2 determines if a backup is needed and schedules it during maintenance windows (offline)

  • reorgs: Used to reorganize tables for optimal table space use (offline)

  • runstats: Makes sure DB2 has a quantitative view of data for its optimizer to produce best results (online)

You can have configured notifications on maintenance jobs.

Getting ready

Identify the best time for maintenance windows, when there is low or no activity on this database. Choose which maintenance activities you want.

For backups, prepare your strategy first, between online or offline backups. In case...

Managing federated databases—connecting to Oracle and MSSQL


A federated database allows client applications to see their data as a single database, even though the data itself can be located across different databases, or even databases created with another RDBMS such as Oracle or MSSQL.

Getting ready

IBM InfoSphere Federation Server must be installed. You can either choose to install a new copy of DB2 Enterprise Server Edition Version 9.7 or install Federation Server on top of an existing copy.

Since DB2 acts as a client for the remote databases, the client software for the databases you want to access must be installed on the same system as the federated server. For Oracle databases, you will need Oracle Net Client. Make sure you can do a tnsping and access your remote database with sqlplus.

[ora10g@nodedb21 ~]$ tnsping ERP10R2
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-JUN-2011 15:34:00
Copyright (c) 1997, 2005, Oracle.  All rights reserved.
Used parameter files:
Used...

Altering databases


This topic involves making changes to a database's characteristics. We will see this command from the DB2 point of view and from an Oracle point of view. This may help clear misunderstandings from one environment to the other.

How to do it…

As understood in DB2, ALTER DATABASE only has one function. Those of you coming from an Oracle DBA background should read on to From Oracle to DB2..., for further explanations.

The ALTER DATABASE command lets you add or remove storage paths from the list of storage paths used for automatic storage table spaces.

  • Add storage path:

     [db2inst1@nodedb21 ~]$ db2 "ALTER DATABASE NAV ADD STORAGE ON '/data1/db2'"
    DB20000I  The SQL command completed successfully.
    
  • Remove storage path:

    [db2inst1@nodedb21 ~]$ db2 "ALTER DATABASE NAV DROP STORAGE ON '/data1/db2'"
    DB20000I  The SQL command completed successfully.
    

How it works…

Adding a storage path to a manual storage database makes it an automatic storage database. Existing manual storage table spaces...

Dropping databases


Dropping a database is an easy task compared to Oracle. You select the database you want to drop, and all its objects, containers, and files will be deleted.

Getting ready

The database must not be used, so all users have to be disconnected. I recommend you take a backup at this point. For many reasons, it could have been the wrong database to drop.

How to do it...

  1. Select the database:

    Select the databases folder on the left pane of the control center; or, you can list databases:

    	[db2inst1@nodedb21 ~]$ db2 list database directory
    
    	 System Database Directory
    
    	 Number of entries in the directory = 1
    
    	Database 1 entry:
    
    	 Database alias                       = NAV
    	 Database name                        = NAV
    	 Local database directory             = /data/db2
    	 Database release level               = d.00
    	 Comment                              = Aviation Geo Data
    	 Directory entry type                 = Indirect
    	 Catalog database partition number    = 0
    	 Alternate server hostname...
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 : 9781849683326
Vendor :
IBM
Category :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

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

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.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
$199.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 $5 each
Feature tick icon Exclusive print discounts
$279.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 $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $ 207.97
IBM WebSphere Application Server 8.0 Administration Guide
$65.99
IBM DB2 9.7 Advanced Application Developer Cookbook
$70.99
IBM DB2 9.7 Advanced Administration Cookbook
$70.99
Total $ 207.97 Stars icon
Banner background image

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

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.