Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • IBM DB2 9.7 Advanced Administration Cookbook
  • Toc
  • feedback
IBM DB2 9.7 Advanced Administration Cookbook

IBM DB2 9.7 Advanced Administration Cookbook

4.5 (8)
close
IBM DB2 9.7 Advanced Administration Cookbook

IBM DB2 9.7 Advanced Administration Cookbook

4.5 (8)

Overview of this book

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.
Table of Contents (21 chapters)
close
close
IBM DB2 9.7 Advanced Administration Cookbook
IBM DB2 9.7 Advanced Administration Cookbook
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
DB2 Instance—Administration and Configuration
DB2 Instance—Administration and Configuration
Introduction
Creating and configuring instances for non-partitioned environments
Creating and configuring a client instance
Creating and configuring an instance for multipartitioned environments
Starting and stopping instances
Configuring SSL for client-server instance communication
Listing and attaching to instances
Dropping instances
2
Administration and Configuration of the DB2 Non-partitioned Database
Administration and Configuration of the DB2 Non-partitioned Database
Introduction
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
3
DB2 Multipartitioned Databases—Administration and Configuration
DB2 Multipartitioned Databases—Administration and Configuration
Introduction
Creating and configuring a multipartitioned database
Adding database partitions
Creating database partition groups
Altering database partition groups—adding partitions to database partition groups
Managing data redistribution on database partition groups
The table distribution key and its role in a multipartitioned environment
Altering database partition groups—removing partitions from a database partition group
Removing database partitions
Converting a non-partitioned database to a multipartitioned database on MS Windows
Configuring Fast Communication Manager
4
Storage—Using DB2 Table Spaces
Storage—Using DB2 Table Spaces
Introduction
Creating and configuring table spaces within automatic storage databases
Creating and configuring SMS table spaces
Creating and configuring DMS table spaces
Getting ready
How to do it...
How it works...
There's more...
See also
Using system temporary table spaces
Using user temporary table spaces
Altering table spaces and dropping table spaces
Table spaces in a multipartitioned environment
5
DB2 Buffer Pools
DB2 Buffer Pools
Introduction
Creating and configuring buffer pools
Configuring the block-based area
Managing buffer pools in a multipartitioned database
Altering buffer pools
Dropping buffer pools
6
Database Objects
chevron up
Database Objects
Introduction
Creating and using MDC tables and block-based indexes
Creating and using materialized query tables
Implementing table partitioning
Using temporary tables
Created global temporary table
7
DB2 Backup and Recovery
DB2 Backup and Recovery
Introduction
Configuring database logging
Performing an offline database backup
Performing a full online database backup
Performing an incremental delta database backup
Performing an incremental cumulative database backup
Backing up table spaces
Crash recovery
Full database recovery
Database rollforward recovery
Incremental restore
Recovering table spaces—full and rollforward recovery
Redirected restore
Recovery history file
Configuring tape-based backup with IBM Tivoli Storage Manager
db2move and db2look utilities as alternative backup methods
8
DB2 High Availability
DB2 High Availability
Introduction
Setting up HADR by using the command line
Setting up HADR by using Control Center
Changing HADR synchronization modes
Performing takeover and takeover by force
Using automated client rerouting with HADR
Opening the standby database in read-only mode
Using the DB2 fault monitor
9
Problem Determination, Event Sources, and Files
Problem Determination, Event Sources, and Files
Introduction
Using db2mtrk—DB2 memory tracker
Using db2pd—DB2 problem determination tool
Using db2dart—DB2 database analysis and reporting tool command
Using db2ckbkp—DB2 check backup tool for backup integrity
Using db2support to collect diagnostic data
10
DB2 Security
DB2 Security
Introduction
Managing instance-level authorities
Managing database-level authorities and privileges
Managing object privileges
Using roles
Using table encryption
Using label-based access control (LBAC) to strengthen data privacy
Auditing DB2
11
Connectivity and Networking
Connectivity and Networking
Introduction
Configuring network communications
Cataloging and uncataloging instances and databases
Using DB2 Discovery
Communications with DRDA servers (z/OS and i/OS)
Monitoring and configuring FCM for optimal performance
12
Monitoring
Monitoring
Introduction
Configuring and using system monitoring
Configuring and using snapshot monitoring
Configuring and using event monitoring
Using Memory Visualizer
Using Health Monitor
13
DB2 Tuning and Optimization
DB2 Tuning and Optimization
Introduction and general tuning guidelines
Operating system tuning
Resolving CPU bottlenecks
Tuning memory utilization
Collecting object statistics with the RUNSTAT utility
Default automatic statistics collection
Tuning with indexes
Tuning sorting
Hit ratios and their role in performance improvement
I/O tuning
Using logging and nologging modes
Using parallelism
Loading a table
Using EXPLAIN PLAN
Creating a benchmark testing scenario
14
IBM pureScale Technology and DB2
IBM pureScale Technology and DB2
Introduction
Managing instances, members, and cluster facilities in DB2 pureScale
Monitoring DB2 pureScale environments
High availability in DB2 pureScale environments
Backup and recovery in DB2 pureScale environments
Index
Index

Using temporary tables

Temporary tables are used by applications that store results temporarily in a table.

There are two types of temporary tables, the declared global temporary table and the created global temporary table. The terms can appear confusing, so we'll explain, Declared Global temporary table

Also known as USER-DEFINED temporary table, a declared global temporary table is defined, or declared, for the current session and does not appear in the system catalog. This session has its own version, and this table is not visible to other sessions, hence the USER-DEFINED nickname. When the session ends, the table is dropped.

Getting ready

A user temporary table space must be present.

How to do it...

  1. Create a user temporary table space, if it does not exist:

    [db2instp@nodedb21 posp]$ db2 "CREATE USER TEMPORARY TABLESPACE
> POSP_TEMP8K  PAGESIZE 8K
> MANAGED BY DATABASE USING (
> FILE '/data/db2/db2instp/NODE0000/posp/posp_temp8k.dbf' 100 M )
> AUTORESIZE YES MAXSIZE 500 M"
DB20000I...
End of Section 6
Next Section
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete