IBM DB2 User Manual

0 (0)


	DB2®
	DB2®
DB2 Version 9
DB2 Version 9
for Linux, UNIX, and Windows

Administration Guide: Implementation

SC10-4221-00


	DB2®
	DB2®
DB2 Version 9
DB2 Version 9
for Linux, UNIX, and Windows

Administration Guide: Implementation

SC10-4221-00

Before using this information and the product it supports, be sure to read the general information under Notices.

Edition Notice

This document contains proprietary information of IBM. It is provided under a license agreement and is protected by copyright law. The information contained in this publication does not include any product warranties, and any statements provided in this manual should not be interpreted as such.

You can order IBM publications online or through your local IBM representative.

vTo order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order

vTo find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/ planetwide

To order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU (426-4968).

When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you.

US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

About this book . . . . . . . . . . . ix				Configuration files and parameters . . . .	.	80
Who should use this book . . . . . . . . .		. x		Database history file . . . . . . . . .	.	87
How this book is structured . . . . . . . .		. x		Chapter 2. Creating and using the DB2

				Administration Server (DAS) . . . .	.	91
	Part 1. Implementing Your Design . . 1
				DB2 Administration Server . . . . . . . .	.	91
Chapter 1. Before creating a database.		. 3		Creating a DB2 administration server (DAS) . .	. 93
				Starting and stopping the DB2 administration server
Working with instances . . . . . . . . . .		.	4
				(DAS) . . . . . . . . . . . . . . .	.	94
	Starting a DB2 instance (Linux, UNIX). . . .	. 4
				Listing the DB2 administration server (DAS) . .	. 95
	Starting a DB2 instance (Windows) . . . . .	. 4
				Configuring the DB2 administration server (DAS)		95
	Attaching to and detaching from a non-default
				Tools catalog database and DB2 administration
	instance of the database manager . . . . .	.	5
				server (DAS) scheduler setup and configuration .	. 96
	Grouping objects by schema . . . . . . .	.	6
				Notification and contact list setup and
	Enabling inter-partition query parallelism. . .	.	7
				configuration . . . . . . . . . . . . . 100
	Enabling intra-partition parallelism for queries . .		7
				DB2 administration server (DAS) Java virtual
	Enabling intra-partition parallelism for utilities . .		8
				computer setup. . . . . . . . . . . .	.	101
	Enabling large page support in a 64-bit
				Security considerations for the DB2 administration
	environment (AIX) . . . . . . . . . .	.	12
				server (DAS) on Windows . . . . . . . .	. 102
	Stopping an instance (Linux, UNIX) . . . .	. 13
				Updating the DB2 administration server (DAS) on
	Stopping an instance (Windows) . . . . .	. 14
				UNIX . . . . . . . . . . . . . . .	.	102
Working with multiple DB2 copies . . . . .		. 15
				Removing the DB2 administration server (DAS)		103
	Multiple DB2 copies roadmap . . . . . .	. 15
				Setting up DB2 administration server (DAS) with
	Multiple instances of the database manager .	. 16
				Enterprise Server Edition (ESE) systems . . .	. 104
	Multiple DB2 copies on the same computer
				DB2 administration server (DAS) configuration on
	(Windows). . . . . . . . . . . . . .		17
				Enterprise Server Edition (ESE) systems . . .	. 106
	Changing the Default DB2 copy after installation
				Discovery of administration servers, instances, and
	(Windows). . . . . . . . . . . . . .		21
				databases. . . . . . . . . . . . . .	.	107
	Client connectivity using multiple DB2 copies
				Discovering and hiding server instances and
	(Windows). . . . . . . . . . . . . .		22
				databases. . . . . . . . . . . . . .	.	108
	Setting the DAS when running multiple DB2
				Setting discovery parameters . . . . . . .	.	109
	copies (Windows) . . . . . . . . . .	.	24
				Setting up the DB2 administration server (DAS) to
	Setting the default instance when using multiple
				use the Configuration Assistant and the Control
	DB2 copies (Windows). . . . . . . . .	.	25
				Center . . . . . . . . . . . . . . .	.	110
	Managing DB2 copies (Windows) . . . . .	. 26
				Updating a DB2 administration server (DAS)
	Running multiple instances concurrently
				configuration for discovery . . . . . . . .	.	110
	(Windows). . . . . . . . . . . . .	.	27
				DB2 administration server (DAS) first failure data
	Removing DB2 copies (Linux, UNIX, and
				capture (FFDC) . . . . . . . . . . . .	.	111
	Windows) . . . . . . . . . . . . . . 28

Working with partitioned databases . . . . .		. 29		Chapter 3. Creating a database . . . .	113
	Management of database server capacity . .	. 29
				Creating a database . . . . . . . . . .	.	113
	Multiple logical partitions . . . . . . .	.	30
				Initial database partition groups . . . . . .	.	115
	Fast communications manager (FCM)
				Creating and managing database partitions and
	communications . . . . . . . . . . . .		32
				database partition groups . . . . . . . .	.	115
Preparing to create a database . . . . . . .		.	33
				Creating database partition groups . . . .	.	115
	Designing logical and physical database
				Managing database partitions . . . . . .	.	116
	characteristics . . . . . . . . . . .	.	34
				Adding and dropping database partitions . . . 119
	Instance creation. . . . . . . . . . .	.	34
				Redistributing data in a database partition
	Instance management . . . . . . . . .	.	36
				group . . . . . . . . . . . . . .	.	128
	Setting the DB2 environment automatically on
				Error recovery when adding database partitions		128
	UNIX . . . . . . . . . . . . . .	.	43
				Issuing commands to multiple database
	Setting the DB2 environment manually on UNIX		44
				partitions . . . . . . . . . . . . .	.	130
	Automatic client rerouting . . . . . . .	.	44
				Using Windows database partition servers .	. 143
	Automatic storage . . . . . . . . . .	.	54
				Creating table spaces . . . . . . . . . .	.	147
	License management . . . . . . . . .	.	64
				Table spaces . . . . . . . . . . . .	.	148
	Registry and environment variables . . . .	. 65

© Copyright IBM Corp. 1993, 2006						iii

Defining initial table spaces . . . . . .	.	148	Creating a staging table . . . . . . . . .	.	211
Creating a table space . . . . . . . .	.	149	Creating a user-defined temporary table . . .	.	212
Automatic resizing of table spaces . . . .	.	154	Creating range-clustered tables . . . . . .	.	213
Creating a system temporary table space . .	. 158		Examples of range-clustered tables . . . .	. 213
Creating a user temporary table space . . .	. 159		How the query compiler works with
Creating table spaces without file system			range-clustered tables . . . . . . . .	.	215
caching . . . . . . . . . . . . .	.	159	Guidelines for using range-clustered tables .	.	216
Table spaces in database partition groups . .	.	163	Creating typed tables. . . . . . . . . .	.	216
Attaching a direct disk access device . . .	. 163		Creating a hierarchy table or a typed table .	. 216
Setting up a direct disk access device on Linux		164	Populating a typed table . . . . . . .	. 217
Creating a buffer pool . . . . . . . . .	.	166	Creating and populating a table . . . . . .	.	217
Creating buffer pools for partitioned databases .	. 167		Details on creating and populating a table. . .	. 219
Creating schemas . . . . . . . . . . .	.	168	Defining columns . . . . . . . . . .	.	219
Creating a schema. . . . . . . . . .	.	168	Defining keys and constraints . . . . . .	.	223
Setting a schema . . . . . . . . . .	.	169	Defining dimensions on a table . . . . .	.	235
Copying a schema. . . . . . . . . .	. 170		Loading data into a table using the Load wizard		237
Restarting a failed copy schema operation . .	. 173		Making a table in no data movement mode
System catalog tables . . . . . . . . . .	.	175	fully accessible . . . . . . . . . . .	.	238
Cataloging a database . . . . . . . . .	.	176	Quiescing tables . . . . . . . . . .	.	239
Cataloging database systems . . . . . . .	.	177	Defining triggers . . . . . . . . . . .	.	240
Database directories, directory services, and logs		178	Creating triggers . . . . . . . . . .	.	240
Local database directory. . . . . . . .	.	178	Trigger dependencies . . . . . . . . .	.	242
System database directory . . . . . . .	.	178	Defining UDFs and UDTs . . . . . . . .	.	243
Viewing the local or system database directory			User-defined functions (UDFs) or methods .	. 243
files . . . . . . . . . . . . . .	.	179	Details on creating a user-defined function
Node directory . . . . . . . . . . .	.	179	(UDF) or method . . . . . . . . . .	.	244
Changing database directory information . .	. 180		User-defined types (UDTs) . . . . . . .	. 246
Updating the directories with information about			Details on creating a user-defined type (UDT)		247
remote database server computers . . . .	.	180	Source data types . . . . . . . . . .	.	249
Lightweight Directory Access Protocol (LDAP)			Length limits for source data types . . . .	. 250
directory service . . . . . . . . . .	.	181	Creating a view . . . . . . . . . . .	.	251
Database recovery log . . . . . . . .	.	182	Creating an alias . . . . . . . . . . .	.	254
Administration notification log . . . . .	.	182	Creating indexes . . . . . . . . . . .	.	255
Binding utilities to the database . . . . . .	.	183	Creating an index . . . . . . . . . .	.	256
Generating DDL statements for database objects		183	Index, index extension, or index specification		258
Quiescing and unquiescing databases . . . .	.	186	Using an index . . . . . . . . . . .	.	260
			Options on the CREATE INDEX statement .	. 261
Chapter 4. Creating tables and other			User-defined extended index types . . . .	. 265
related table objects . . . . . . . .	187		Creating user-defined extended index types .	. 266
Space compression for tables . . . . . . .	.	187	Showing related objects . . . . . . . . .	.	270
Space compression for tables . . . . . . .	.	187	Validating related objects . . . . . . . .	.	271
Space value compression for new tables . . .	. 187		Validating related objects . . . . . . . .	.	271
Space value compression for new tables . . .	. 187		Estimating space requirements for tables and
Data row compression . . . . . . . . .	.	188	Estimating space requirements for tables and
Data row compression . . . . . . . . .	.	188	indexes . . . . . . . . . . . . . .	.	272
Table creation . . . . . . . . . . . .	.	189	indexes . . . . . . . . . . . . . .	.	272
Table creation . . . . . . . . . . . .	.	189
Creating a table using the Create Table wizard .	. 190		Chapter 5. Altering a database . . . .	275
Creating a table in multiple table spaces . . .	.	190	Chapter 5. Altering a database . . . .	275
Creating a table in multiple table spaces . . .	.	190	Altering an instance . . . . . . . . . .	.	275
Creating a table in a partitioned database			Altering an instance . . . . . . . . . .	.	275
environment. . . . . . . . . . . . .	.	191	Changing instances (UNIX only) . . . . .	.	275
Creating partitioned tables . . . . . . . .	.	193	Details on changing instances . . . . . .	.	276
Details of partitioned tables . . . . . . .	.	194	Changing node and database configuration files		279
Approaches to defining ranges on partitioned			Changing the database configuration across
tables . . . . . . . . . . . . . .	.	195	multiple database partitions . . . . . .	.	281
Approaches to migrating existing tables and			Altering a database . . . . . . . . . .	.	281
views to partitioned tables . . . . . . .	.	198	Altering a database partition group . . . .	.	281
Creating materialized query tables . . . . .	. 201		Managing database partitions from the Control
Creating a materialized query table . . . .	.	201	Center. . . . . . . . . . . . . .	.	282
Creating a user-maintained materialized query			Altering a buffer pool . . . . . . . .	.	283
table . . . . . . . . . . . . . .	.	204	Altering a table space . . . . . . . .	.	284
Populating a user-maintained materialized			Details on altering a table space . . . . .	. 285
query table . . . . . . . . . . . .	.	205	Dropping a database . . . . . . . . . .	.	293
Partitioned materialized query table behavior		206	Dropping a schema . . . . . . . . . .	.	294
Creating a new source table using db2look . .	. 210

iv Administration Guide: Implementation

Chapter 6. Altering tables and other
related table objects . . . . . . .	. 295
Modifying tables . . . . . . . . . . .	.	295
Space value compression for existing tables .	. 295
Copying tables . . . . . . . . . . .	.	296
Altering a table. . . . . . . . . . .	.	297
Changing table attributes . . . . . . .	.	298
Changing table properties . . . . . . .	.	299
Altering columns and rows. . . . . . .	.	300
Altering keys and constraints . . . . . .	.	309
Changing distribution keys . . . . . . .	.	318
Altering an identity column . . . . . .	.	318
Altering a sequence . . . . . . . . .	.	319
Dropping a sequence . . . . . . . . .	.	320
Dropping or removing columns . . . . .	. 320

Defining a generated column on an existing

table . . . . . . . . . . . . . . . 321

Declaring a table volatile . . . . . .	.	.	323
Using a stored procedure to alter a table . .	.	.	324
Modifying indexes . . . . . . . . .	.	.	326
Renaming an existing table or index. . . . . 326

Dropping an index, index extension, or an index specification . . . . . . . . . . . . . 327

Modifying triggers . . . . . . . . .	.	.	328
Updating view contents using triggers . . . . 328
Dropping a trigger . . . . . . . .	.	.	329
Modifying aliases and views . . . . . .	.	.	330
Altering or dropping a view . . . . .	.	.	330
Recovering inoperative views . . . . .	.	.	331
Dropping aliases . . . . . . . . .	.	.	332
Modifying UDFs and UDTs . . . . . .	.	. 333
Altering a user-defined structured type. .	.	.	333
Dropping a user-defined function (UDF),
function mapping, or method . . . . .	.	. 333

Dropping a user-defined type (UDT) or type mapping . . . . . . . . . . . . . . 334

Modifying materialized query tables . . . .	.	335
Altering materialized query table properties .	.	335

Refreshing the data in a materialized query

table . . . . . . . . . . . . . . . 336

Modifying partitioned tables . . . . . . .	.	336
Altering partitioned tables . . . . . . .	.	336
Guidelines and restrictions on altering
partitioned tables with attached or detached
data partitions . . . . . . . . . . .	.	338
Rotating data in a partitioned table . . . .	.	339
Examples of rolling in and rolling out
partitioned table data. . . . . . . . .	.	342
Attaching a data partition . . . . . . .	.	346
Resolving a mismatch when trying to attach a
data partition to a partitioned table . . . .	.	348
Detaching a data partition . . . . . . .	.	352
Attributes of detached data partitions . . .	.	354
Adding data partitions to partitioned tables .	. 356
Dropping a data partition . . . . . . .	.	358
Updating table and view contents using the
MERGE statement . . . . . . . . . . .	.	360
Recovering inoperative summary tables . . .	. 361
Dropping or deleting tables . . . . . . .	.	362
Deleting and updating rows of a typed table		362
Deleting the contents of staging tables . . .	.	362

Dropping a table . . . . . . . . . .	.	363
Dropping a user-defined temporary table . .	.	364
Dropping a materialized query or staging table		365
Statement dependencies when changing objects		366

Chapter 7. Using the DB2
administration tools . . . . . . .	.	369
Starting the server DB2 administration tools .	.	. 369

Shutting down server DB2 administration tools . . 369

Finding service level information about the DB2
administration tools environment. . . . . .	.	370
Using the DB2 database help . . . . . . .	.	370
Environment-specific information. . . . . .	.	371
Menus and toolbars . . . . . . . . . .	.	371
DB2 toolbar . . . . . . . . . . . .	.	371
DB2 secondary toolbar . . . . . . . .	.	373
DB2 Tools menu . . . . . . . . . .	.	374
DB2 Help menu . . . . . . . . . .	.	375
Control Center . . . . . . . . . . . .	.	376
Control Center overview . . . . . . .	.	376
Control Center Legend . . . . . . . .	.	380
Opening new Control Centers . . . . . .	. 382
Creating database objects . . . . . . .	.	382
Changing system names displayed in the
Control Center . . . . . . . . . . .	.	383
Getting help in the Control Center . . . .	.	385
Using advisors, wizards, and launchpads to
perform tasks quickly and easily . . . . .	.	385
Wizard overviews . . . . . . . . . .	.	387
Control Center object tree and details view .	. 388
Extending the Control Center . . . . . .	.	394
License Center . . . . . . . . . . . .	.	411
License Center overview. . . . . . . .	.	411
Adding licenses . . . . . . . . . .	.	412
Changing licenses and policies . . . . .	.	413
Viewing licensing information . . . . . .	.	413
Viewing license policy information . . . .	.	414
Viewing authorized user infraction information		415
Viewing and resetting compliance details . .	. 415
Removing licenses. . . . . . . . . .	.	416
Task Center and Journal . . . . . . . . .	.	416
Task Center overview . . . . . . . .	.	416
Journal overview . . . . . . . . . .	.	418
Enabling scheduling settings in the Task Center		419
Scheduler. . . . . . . . . . . . . . 420
Success code sets . . . . . . . . . .	.	420
Running tasks immediately. . . . . . .	.	421
Scheduling a task . . . . . . . . . .	.	422
Changing the default notification message. . . 423
Creating a database for the DB2 tools catalog		424
Creating or editing a task . . . . . . .	.	425
Selecting users and groups for new tasks . .	. 427
Managing contacts . . . . . . . . .	.	428
Managing saved schedules . . . . . . .	.	429
Managing success code sets . . . . . .	.	430
Managing task categories . . . . . . .	.	431
Tools Settings . . . . . . . . . . . .	.	432
Tools Settings overview . . . . . . . .	.	432
Setting the server administration tools startup
property . . . . . . . . . . . . .	.	434

Contents v

Setting a command statement termination			Details on privileges, authorities, and authorization		506
character . . . . . . . . . . . . .	.	434	System administration authority (SYSADM) .	.	506
Setting up access to DB2 contextual help and			System control authority (SYSCTRL). . . .	. 507
documentation . . . . . . . . . . . . 435			System maintenance authority (SYSMAINT) .	. 508
Setting startup and default options for the DB2			Security administration authority (SECADM)		508
administration tools . . . . . . . . .	. 436		Database administration authority (DBADM)		509
Changing the fonts for menus and text . . .	. 437		System monitor authority (SYSMON) . . .	. 510
Setting DB2 UDB OS/390 and z/OS utility			LOAD authority . . . . . . . . . .	. 511
execution options . . . . . . . . . .	.	437	Database authorities . . . . . . . . .	.	511
DB2 for z/OS health monitor . . . . . .	.	441	Authorization ID privileges. . . . . . .	.	513
Enabling or disabling notification using the			Implicit schema authority (IMPLICIT_SCHEMA)
Health Center Status Beacon . . . . . .	.	448	considerations . . . . . . . . . . . . 513
Setting the default scheduling scheme . . .	.	449	Schema privileges . . . . . . . . . .	.	514
Setting Command Editor options . . . . .	.	449	Table space privileges . . . . . . . .	.	515
Setting IMS options . . . . . . . . .	.	450	Table and view privileges . . . . . . .	.	515
Visual Explain . . . . . . . . . . . .	.	451	Package privileges. . . . . . . . . .	.	517
Visual Explain overview. . . . . . . .	.	451	Index privileges . . . . . . . . . .	.	518
Visual Explain concepts . . . . . . . .	.	452	Sequence privileges . . . . . . . . .	.	518
Dynamically explaining an SQL or an XQuery			Routine privileges . . . . . . . . . .	. 518
statement. . . . . . . . . . . . . . 464			Controlling access to database objects . . . .	.	519
Creating an access plan using the Command			Details on controlling access to database objects		519
Editor . . . . . . . . . . . . . . . 465			Granting privileges . . . . . . . . .	.	519
Explain tables . . . . . . . . . . .	.	466	Revoking privileges . . . . . . . . .	.	521
Guidelines for creating indexes . . . . .	. 467		Managing implicit authorizations by creating
Out-of-date access plans . . . . . . . .	.	467	and dropping objects . . . . . . . . .	.	522
Retrieving the access plan when using			Establishing ownership of a package . . .	. 523
LONGDATACOMPAT . . . . . . . . . 468			Indirect privileges through a package . . .	. 523
Using RUNSTATS . . . . . . . . . .	. 468		Indirect privileges through a package containing
Viewing SQL or XQuery statement details and			nicknames . . . . . . . . . . . . . 524
statistics . . . . . . . . . . . . . . 469			Controlling access to data with views . . .	.	525
Viewing a graphical representation of an access			Monitoring access to data using the audit
plan . . . . . . . . . . . . . .	.	473	facility. . . . . . . . . . . . . .	.	527
Viewing explainable statements for a package		474	Data encryption . . . . . . . . . .	.	527
Viewing the history of previously explained			Granting database authorities to new groups		529
query statements . . . . . . . . . .	.	476	Granting database authorities to new users .	.	529
Visual Explain support for earlier and later			Granting privileges to new groups . . . .	. 530
releases . . . . . . . . . . . . .	.	478	Granting privileges to new users . . . . .	.	534
			Label-based access control (LBAC) . . . . .	.	538
			Label-based access control (LBAC) overview		538
Part 2. Database Security . . . . .	479		Label-based access control (LBAC) overview	.	538
			LBAC security policies . . . . . . . .	.	540
Chapter 8. Controlling database			LBAC security label components . . . . .	. 541
Chapter 8. Controlling database			LBAC security labels . . . . . . . . .	.	547
access . . . . . . . . . . . . . . 481			LBAC security labels . . . . . . . . .	.	547
access . . . . . . . . . . . . . . 481			Format for security label values . . . . .	.	549
Security issues when installing the DB2 database			How LBAC security labels are compared . .	. 550
manager . . . . . . . . . . . . . . . 481			LBAC rule sets . . . . . . . . . . .	.	551
Acquiring Windows users’ group information			LBAC rule exemptions . . . . . . . .	. 556
using an access token. . . . . . . . . .	.	483	Built-in functions for dealing with LBAC
Details on security based on operating system .	.	485	security labels . . . . . . . . . . .	.	557
Windows platform security considerations for			Protection of data using LBAC . . . . .	. 558
users . . . . . . . . . . . . . . . 485			Reading of LBAC protected data . . . . .	.	560
Windows local system account support. . .	. 485		Inserting of LBAC protected data. . . . .	. 563
Extended Windows security using DB2ADMNS			Updating of LBAC protected data . . . .	. 565
and DB2USERS groups . . . . . . . .	. 486		Deleting or dropping of LBAC protected data		569
UNIX platform security considerations for users		489	Removal of LBAC protection from data . .	. 572
Location of the instance directory . . . .	. 489		Lightweight directory access protocol (LDAP)
Security plug-ins . . . . . . . . . .	.	490	directory services . . . . . . . . . . .	.	573
Authentication methods for your server . . .	. 490		Lightweight Directory Access Protocol (LDAP)
Authentication considerations for remote clients		495	overview . . . . . . . . . . . . . . 573
Partitioned database authentication considerations		496	Supported LDAP client and server
Kerberos authentication details . . . . . .	.	496	configurations . . . . . . . . . . . . 575
Authorization, privileges, and object ownership		501	Support for Active Directory . . . . . .	. 575

vi Administration Guide: Implementation

Configuring DB2 to use Active Directory . . .	576
Configuring DB2 in the IBM LDAP environment	576
Creating an LDAP user . . . . . . . . .	577

Configuring the LDAP user for DB2

applications . . . . . . . . . . . . . 578 Registration of DB2 servers after installation . . 578 Update the protocol information for the DB2

server . . . . . . . . . . . . . . . 580 Rerouting LDAP clients to another server . . . 580

Catalog a node alias for ATTACH . . . .	.	581
Deregistering the DB2 server . . . . . .	.	582
Registration of databases in the LDAP directory		582

Attaching to a remote server in the LDAP environment. . . . . . . . . . . . . 583 Deregistering the database from the LDAP

directory . . . . . . . . . . . . . . 584

Refreshing LDAP entries in local database and
node directories . . . . . . . . . .	. 584
Searching the LDAP servers . . . . . .	. 585
Registering host databases in LDAP . . . .	. 586
Setting DB2 registry variables at the user level
in the LDAP environment . . . . . . .	. 587

Enabling LDAP support after installation is complete . . . . . . . . . . . . . . 588

Disabling LDAP support . . . .	.	. .	. 589
LDAP support and DB2 Connect . .	.	. .	. 589

Security considerations in an LDAP

environment. . . . . . . . . . . . . 589

Security considerations for Active Directory .	. 590
Extending the LDAP directory schema with DB2
object classes and attributes . . . . . .	.	591
Extending the directory schema for Active
Directory . . . . . . . . . . . . .	.	591
DB2 objects in the Active Directory . . . .	.	593

Netscape LDAP directory support and attribute definitions . . . . . . . . . . . . . 593

Extending the directory schema for IBM Tivoli
Directory Server . . . . . . . . . .	.	595
Extending the directory schema for Sun One
Directory Server . . . . . . . . . .	.	596
LDAP object classes and attributes used by DB2		598
Tasks and required authorizations . . . . .	.	608
Using the system catalog for security issues . .	. 609

Details on using the system catalog for security

issues . . . . . . . . . . . . . . . . 610 Retrieving authorization names with granted privileges. . . . . . . . . . . . . . 610

Retrieving all names with DBADM authority		611
Retrieving names authorized to access a table		612
Retrieving all privileges granted to users . .	.	613
Securing the system catalog view. . . . .	.	613
Security considerations . . . . . . . . .	.	616
Introduction to firewall support . . . . . .	.	619
Screening router firewalls . . . . . . . .	.	619
Application proxy firewalls. . . . . . . .	.	620
Circuit level firewalls . . . . . . . . . .	.	620
Stateful multi-layer inspection (SMLI) firewalls .	.	620

Chapter 9. Auditing DB2 database activities . . . . . . . . . . . . . 621

Introduction to the DB2 database audit facility . . 621

Audit facility behavior . . . . . . . . .	.	623
Audit facility usage . . . . . . . . . .	.	624
Working with DB2 audit data in DB2 tables . .	. 628
Working with DB2 audit data in DB2 tables .	. 628
Creating tables to hold the DB2 audit data .	. 628
Creating DB2 audit data files . . . . . .	.	631
Loading DB2 audit data into tables . . . .	. 632
Selecting DB2 audit data from tables . . .	. 635
Audit facility messages . . . . . . . . .	.	636
Audit facility record layouts (introduction) . .	.	636

Details on audit facility record layouts . . . . . 637 Audit record layout for AUDIT events . . . . 637

Audit record layout for CHECKING events .	. 638
Audit record object types . . . . . . .	. 639
List of possible CHECKING access approval
reasons . . . . . . . . . . . . .	. 640
List of possible CHECKING access attempted
types . . . . . . . . . . . . . .	. 641
Audit record layout for OBJMAINT events .	. 643
Audit record layout for SECMAINT events .	. 645
List of possible SECMAINT privileges or
authorities . . . . . . . . . . . .	. 647
Audit record layout for SYSADMIN events .	. 650
List of possible SYSADMIN audit events . .	. 650
Audit record layout for VALIDATE events .	. 651
Audit record layout for CONTEXT events . . . 652
List of possible CONTEXT audit events . .	. 653
Audit facility tips and techniques. . . . . .	. 654
Controlling DB2 database audit facility activities	655

Part 3. Appendixes . . . . . . .	.	661
Appendix A. Conforming to the
naming rules . . . . . . . . . .	.	663
General naming rules. . . . . . . . .	.	. 663
DB2 database object naming rules . . . .	.	. 663
Delimited identifiers and object names . . .	.	. 665
User, user ID and group naming rules . . .	.	. 666
Federated database object naming rules . .	.	. 666
Additional restrictions and recommendations
regarding the use of schema names . . . .	.	. 667
Maintaining passwords on servers . . . .	.	. 667
Workstation naming rules . . . . . . .	.	. 667
Naming rules in an NLS environment . . .	.	. 668
Naming rules in a Unicode environment . .	.	. 669

Appendix B. Using Windows Management Instrumentation (WMI)

support . . . . . . . . . . . . . . 671

Introduction to Windows Management
Instrumentation (WMI) . . . . . . . . .	.	671
DB2 database system integration with Windows
Management Instrumentation . . . . . . .	.	672

Appendix C. Using Windows security	675
DB2 and Windows security introduction . . .	. 675
A scenario with server authentication (Windows)	676
A scenario with client authentication and a
Windows client machine. . . . . . . . .	. 677

Contents vii

Support for global groups (on Windows) . . .	. 677		Resetting DB2 performance values . . . . .	. 688
Using a backup domain controller with DB2
database systems . . . . . . . . . . .	.	677	Appendix E. DB2 Database technical
User authentication with DB2 for Windows . .	. 678		information . . . . . . . . . . . .	691
User name and group name restrictions			Overview of the DB2 technical information . .	. 691
(Windows) . . . . . . . . . . . . .		678
			Documentation feedback . . . . . . .	.	691
Groups and user authentication on Windows		679
			DB2 technical library in hardcopy or PDF format		692
Trust relationships between domains on
			Ordering printed DB2 books . . . . . . .	. 694
Windows . . . . . . . . . . . . . .		679
			Displaying SQL state help from the command line
DB2 database system and Windows security
			processor . . . . . . . . . . . . . .	. 695
service. . . . . . . . . . . . . . .		680
			Accessing different versions of the DB2
Installing DB2 on a backup domain controller		680
			Information Center . . . . . . . . . .	.	696
Authentication with groups and domain
			Displaying topics in your preferred language in the
security (Windows) . . . . . . . . .	.	681
			DB2 Information Center . . . . . . . . .	.	696
Authentication using an ordered domain list		682
			Updating the DB2 Information Center installed on
Domain security support (Windows) . . .	. 683
			your computer or intranet server . . . . . .	.	697

Appendix D. Using the Windows			DB2 tutorials . . . . . . . . . . . .	.	699
			DB2 troubleshooting information . . . . . .	.	699
Performance Monitor . . . . . . . .	685
			Terms and Conditions . . . . . . . . .	.	700
Windows performance monitor introduction . . . 685
Registering DB2 with the Windows performance			Appendix F. Notices . . . . . . . .	701
monitor . . . . . . . . . . . . . . .		685	Trademarks . . . . . . . . . . . . .	. 703
Enabling remote access to DB2 performance

information . . . . . . . . . . . . . .		686	Index . . . . . . . . . . . . . . .	705
Displaying DB2 database and DB2 Connect

performance values . . . . . . . . . .	.	687	Contacting IBM . . . . . . . . . .	719
Windows performance objects . . . . . . .	. 687

Accessing remote DB2 database performance
information . . . . . . . . . . . . .	.	688

viii Administration Guide: Implementation

About this book

The Administration Guide in its two volumes provides information necessary to use and administer the DB2® relational database management system (RDBMS) products, and includes:

vInformation about database planning and design (found in Administration Guide: Planning)

vInformation about implementing and managing databases (found in

Administration Guide: Implementation)

In Version 9, the information about configuring and tuning your database environment to improve performance can be found in the Performance Guide.

Many of the tasks described in this book can be performed using different interfaces:

vThe command line processor, which allows you to access and manipulate databases from a command-line interface. From this interface, you can also execute SQL and XQuery statements and DB2 utility functions. Most examples in this book illustrate the use of this interface. For more information about using the command line processor, see the Command Reference.

vThe application programming interface, which allows you to execute DB2 utility functions within an application program. For more information about using the application programming interface, see the Administrative API Reference.

vThe Control Center, which allows you to use a graphical user interface to

manage and administer your data and database components. You can invoke the Control Center using the db2cc command on a Linux® or Windows® command line, or using the Start menu on Windows platforms. The Control Center presents your database components as a hierarchy of objects in an object tree, which includes your systems, instances, databases, tables, views, triggers, and indexes. From the tree you can perform actions on your database objects, such as creating new tables, reorganizing data, configuring and tuning databases, and backing up and restoring databases, database partitions, and table spaces. In many cases, wizards and launchpads are available to help you perform these tasks more quickly and easily.

The Control Center is available in three views:

–Basic. This view provides you with the core DB2 functions. From this view you can work with all the databases to which you have been granted access, including their related objects such as tables and stored procedures. It provides you with the essentials for working with your data.

–Advanced. This view provides you with all of the objects and actions available in the Control Center. Use this view if you are working in an enterprise

environment and you want to connect to DB2 UDB Version 8 for z/OS or DB2 Version 9 for z/OS (DB2 for z/OS®) or IMS™.

–Custom. This view provides you with the ability to tailor the Control Center to your needs. You select the objects and actions that you want to appear in your view.

For help on using the Control Center, select Getting started from the Help pull-down on the Control Center window.

There are other graphical tools that you can use to perform administration tasks. They include:

–The Activity Monitor helps you monitor application performance and concurrency, resource consumption, and SQL statement usage of a database or database partition.

–The Command Editor is used to generate, edit, run, and manipulate SQL and XQuery statements; IMS and DB2 commands; work with the resulting output; and to view a graphical representation of the access plan for explained SQL and XQuery statements.

–The Configuration Assistant is used to configure and maintain the database objects that your applications will be using.

–The Health Center helps you resolve performance and resource allocation problems.

–The Indoubt Transaction Manager is used to display indoubt transactions, that is, the transactions that are waiting to be committed, rolled back, or forgotten for a selected database and one or more selected partitions.

–The License Center is used to display license status and usage information for DB2 products installed on your system. You can also use the License Center to configure your system for license monitoring.

–The Task Center is used to schedule jobs that are to run unattended. The Journal can be used to view historical information about tasks, database actions and operations, messages, and notifications.

–The Memory Visualizer helps you monitor the memory-related performance of an instance and all of its databases organized in a hierarchical tree.

–Tools Settings is used to change the settings for the Control Center, Health Center, and the Information Center.

–Visual Explain is used to display access plan graphs for explained SQL or XQuery statements. You can use the information in the graph to tune your queries.

For more information about the Control Center and the administration tools listed above, refer to Chapter 7, or search for them in the DB2 Information Center.

Who should use this book

This book is intended primarily for database administrators, system administrators, security administrators, and system operators who need to design, implement and maintain a database to be accessed by local or remote clients. It can also be used by programmers and other users who require an understanding of the administration and operation of the DB2 relational database management system.

How this book is structured

This book contains information about the following major topics:

Implementing Your Design

vChapter 1, Chapter 1, “Before creating a database,” describes the prerequisites needed before creating a database and the objects within a database.

vChapter 2, Chapter 2, “Creating and using the DB2 Administration Server (DAS),” discusses what a DAS is, how to create it, and how to use it.

vChapter 3, Chapter 3, “Creating a database,” describes the tasks associated with creating a database and the objects within a database.

xAdministration Guide: Implementation

vChapter 4, Chapter 4, “Creating tables and other related table objects,” describes how to create tables with specific characteristics when implementing your database design.

vChapter 5, Chapter 5, “Altering a database,” describes the prerequisites and the tasks associated with altering or dropping a database and the objects within a database.

vChapter 6, Chapter 6, “Altering tables and other related table objects,” describes how to drop tables or how to modify specific characteristics associated with those tables. Dropping and modifying related table objects is also presented here.

vChapter 7, Chapter 7, “Using the DB2 administration tools,” describes the graphical user interface tools and includes some tasks that can only be performed using the graphical user interface. This chapter also discusses how you can extend the Control Center by adding new tool bar buttons including new actions, adding new object definitions, and adding new action definitions.

Database Security

vChapter 8, Chapter 8, “Controlling database access,” describes how you can control access to your database’s resources.

vChapter 9, Chapter 9, “Auditing DB2 database activities,” describes how you can detect and monitor unwanted or unanticipated access to data.

Appendixes

vAppendix A, “Conforming to the naming rules,” presents the rules to follow when naming databases and objects.

vAppendix B, “Using Windows Management Instrumentation (WMI) support,” provides information about how DB2 can be managed using Windows Management Instrumentation (WMI).

vAppendix C, “Using Windows security,” describes how DB2 works with Windows security.

vAppendix D, “Using the Windows Performance Monitor,” describes how to use the Windows Performance Monitor to collect DB2 performance data.

About this book xi

xii Administration Guide: Implementation

Part 1. Implementing Your Design

2 Administration Guide: Implementation

Chapter 1. Before creating a database

After determining the design of your database, you must create the database and the objects within it. These objects include schemas, database partition groups, table spaces, tables, views, wrappers, servers, nicknames, type mappings, function mappings, aliases, user-defined types (UDTs), user-defined functions (UDFs), automatic summary tables (ASTs), triggers, constraints, indexes, and packages. You can create these objects:

vUsing SQL and XQuery statements in the command line processor.

vThrough SQL and XQuery statements in applications using application programming interfaces (APIs).

vThrough the Control Center.

In this and other chapters, the Control Center method for completing tasks is highlighted by placing it within a box. This is followed immediately by a comparable method using the command line, and if applicable, using an API. In some cases, there may be tasks showing only one method. When working with the Control Center, recall that you can use the help to obtain more detail than the overview information found in this manual.

For information on SQL and XQuery statements, refer to the SQL Reference manual. For information on command line processor commands, refer to the Command Reference manual. For information on APIs, refer to the Administrative API Reference manual. For information on the Control Center and other administration tools, refer to Chapter 7.

This chapter focuses on the information you should know before you create a database with all of its objects. There are several prerequisite concepts and topics as well as several tasks you must perform before creating a database.

The chapter following this one contains brief discussions of the various objects that may be part of the implementation of your database design.Chapter 6 presents topics you must consider before you alter a database and then explains how to alter or drop database objects.

For those areas where DB2 Database interacts with the operating system, some of the topics in this and the following chapters may present operating system-specific differences. You may be able to take advantage of native operating system capabilities or differences beyond those offered by DB2 Database. Refer to the Quick Beginnings manual and operating system documentation for precise differences.

As an example, Windows supports an application type known as a “service”. DB2 for Windows will have each DB2 instance defined as a service. A service can be started automatically at system boot, by a user through the Services control panel applet, or by a Windows 32-bit application that uses the service functions included in the Microsoft® Windows 32-bit application programming interface (API). Services can execute even when no user is logged on to the system.

References to Windows will mean all supported Windows operating systems.

Working with instances

Before you implement a database, you should understand the prerequisite concepts and tasks described in this section.

Starting a DB2 instance (Linux, UNIX)

You might need to start or stop the DB2 database during normal business operations; for example, you must start an instance before you can perform the following tasks:

vConnecting to a database on the instance

vPrecompiling an application

vBinding a package to a database

vAccessing host databases.

Prerequisites:

Before you start a DB2 instance on your system:

1.Log in with a user ID or name that has SYSADM, SYSCTRL, or SYSMAINT authority on the instance; or log in as the instance owner.

2.Run the startup script as follows:

. INSTHOME/sqllib/db2profile	(for	Bourne or Korn shell)
source INSTHOME/sqllib/db2cshrc	(for	C shell)

where INSTHOME is the home directory of the instance you want to use.

Procedure:

To start the instance using the Control Center:

1.Expand the object tree until you see the Instances folder.

2.Right-click the instance that you want to start, and select start from the pop-up menu.

To start the instance using the command line, enter:

db2start

Note: When you run commands to start or stop an instance’s database manager, the DB2 database manager applies the command to the current instance. For more information, see Setting the current instance environment variables.

Related tasks:

v“Setting the current instance environment variables” on page 67

v“Starting a DB2 instance (Windows)” on page 4

v“Stopping an instance (Linux, UNIX)” on page 13

Starting a DB2 instance (Windows)

You might need to start or stop a DB2 instance during normal business operations; for example, you must start an instance before you can perform the following tasks:

v Connecting to a database on the instance

4 Administration Guide: Implementation

vPrecompiling an application

vBinding a package to a database

vAccessing host databases.

Prerequisites:

In order to successfully launch the DB2 database instance as a service from db2start, the user account must have the correct privilege as defined by the Windows operating system to start a Windows service. The user account can be a member of the Administrators, Server Operators, or Power Users group. When extended security is enabled, only members of the DB2ADMNS and Administrators groups can start the database by default.

Procedure:

To start the instance using the Control Center:

1.Expand the object tree until you see the Instances folder.

2.Right-click the instance that you want to start, and select start from the pop-up menu.

To start the instance using the command line, enter:

db2start

The db2start command will launch the DB2 database instance as a Windows service. The DB2 database instance on Windows can still be run as a process by specifying the ″/D″ switch when invoking db2start. The DB2 database instance can also be started as a service using the Control Panel or ″NET START″ command.

When running in a partitioned database environment, each database partition server is started as a Windows service. You can not use the ″/D″ switch to start a DB2 instance as a process in a partitioned database environment.

Related tasks:

v“Setting the current instance environment variables” on page 67

v“Starting a DB2 instance (Linux, UNIX)” on page 4

v“Stopping an instance (Windows)” on page 14

v“Stopping an instance (Linux, UNIX)” on page 13

Attaching to and detaching from a non-default instance of the database manager

To attach to another instance of the database manager, which might be remote, use the ATTACH command.

Prerequisites:

More than one instance must already exist.

Procedure:

Chapter 1. Before creating a database 5

To attach to another instance of the database manager using the Control Center:

1.Expand the object tree until you see the Instances folder.

2.Click on the instance you want to attach.

3.Right-click the selected instance name.

4.In the Attach-DB2 window, type your user ID and password, and click OK.

To attach to an instance using the command line, enter:

db2 attach to <instance name>

For example, to attach to an instance called testdb2 that was previously cataloged in the node directory:

db2 attach to testdb2

To attach to an instance from a client application, call the sqleatin API.

After performing maintenance activities for the testdb2 instance, you can then DETACH from that instance by running the following command:

db2 detach

To detach from an instance from a client application, call the sqledtin API.

Related reference:

v“ATTACH command” in Command Reference

v“DETACH command” in Command Reference

Grouping objects by schema

Database object names might be made up of a single identifier or they might be schema-qualified objects made up of two identifiers. The schema, or high-order part, of a schema-qualified object provides a means to classify or group objects in the database. When an object such as a table, view, alias, distinct type, function, index, package or trigger is created, it is assigned to a schema. This assignment is done either explicitly or implicitly.

Explicit use of the schema occurs when you use the high-order part of a two-part object name when referring to that object in a statement. For example, USER A issues a CREATE TABLE statement in schema C as follows:

CREATE TABLE C.X (COL1 INT)

Implicit use of the schema occurs when you do not use the high-order part of a two-part object name. When this happens, the CURRENT SCHEMA special register is used to identify the schema name used to complete the high-order part of the object name. The initial value of CURRENT SCHEMA is the authorization ID of the current session user. If you want to change this during the current session, you can use the SET SCHEMA statement to set the special register to another schema name.

Some objects are created within certain schemas and stored in the system catalog tables when the database is created.

In dynamic SQL and XQuery statements, a schema qualified object name implicitly uses the CURRENT SCHEMA special register value as the qualifier for unqualified

6 Administration Guide: Implementation

object name references. In static SQL and XQuery statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified database object names.

Before creating your own objects, you need to consider whether you want to create them in your own schema or by using a different schema that logically groups the objects. If you are creating objects that will be shared, using a different schema name can be very beneficial.

Related concepts:

v “System catalog tables” on page 175

Related tasks:

v “Creating a schema” on page 168

Related reference:

v“CURRENT SCHEMA special register” in SQL Reference, Volume 1

v“SET SCHEMA statement” in SQL Reference, Volume 2

Enabling inter-partition query parallelism

Inter-partition parallelism occurs automatically based on the number of database partitions and the distribution of data across these database partitions.

Note: You must modify configuration parameters to take advantage of parallelism within a database partition or within a non-partitioned database. For example, intra-partition parallelism can be used to take advantage of the multiple processors on a symmetric multi-processor (SMP) machine.

Related concepts:

v“Partitioned database environments” in Administration Guide: Planning

v“Database partition group design” in Administration Guide: Planning

v“Database partition and processor environments” in Administration Guide: Planning

v“Adding database partitions in a partitioned database environment” on page 123

Related tasks:

v“Redistributing data across database partitions” in Performance Guide

v“Enabling database partitioning in a database” on page 9

v“Enabling intra-partition parallelism for queries” on page 7

Enabling intra-partition parallelism for queries

The Control Center can be used to find out, or modify, the values of individual entries in a specific database, or in the database manager configuration file.

You could also use the GET DATABASE CONFIGURATION and the GET DATABASE MANAGER CONFIGURATION commands to find out the values of individual entries in a specific database, or in the database manager configuration file. To modify individual entries for a specific database or in the database manager configuration file, use the UPDATE DATABASE CONFIGURATION and the UPDATE DATABASE MANAGER CONFIGURATION commands respectively.

Chapter 1. Before creating a database 7

Configuration parameters that affect intra-partition parallelism include the max_querydegree and intra_parallel database manager parameters, and the dft_degree database parameter.

In order for intra-partition query parallelism to occur, you must modify one or more database configuration parameters, database manager configuration parameters, precompile or bind options, or a special register.

intra_parallel

Database manager configuration parameter that specifies whether the database manager can use intra-partition parallelism. The default is not to use intra-partition parallelism.

max_querydegree

Database manager configuration parameter that specifies the maximum degree of intra-partition parallelism that is used for any SQL statement running on this instance. An SQL statement will not use more than the number given by this parameter when running parallel operations within a database partition. The intra_parallel configuration parameter must also be set to “YES” for the value in max_querydegree is used. The default value for this configuration parameter is -1. This value means that the system uses the degree of parallelism determined by the optimizer; otherwise, the user-specified value is used.

dft_degree

Database configuration parameter that provides the default for the DEGREE bind option and the CURRENT DEGREE special register. The default value is 1. A value of ANY means the system uses the degree of parallelism determined by the optimizer.

DEGREE

Precompile or bind option for static SQL.

CURRENT DEGREE

Special register for dynamic SQL.

Related concepts:

v“Parallel processing for applications” in Performance Guide

v“Parallel processing information” in Performance Guide

Related tasks:

v “Configuring DB2 with configuration parameters” in Performance Guide

Related reference:

v“dft_degree - Default degree configuration parameter” in Performance Guide

v“intra_parallel - Enable intra-partition parallelism configuration parameter” in

Performance Guide

v“max_querydegree - Maximum query degree of parallelism configuration parameter” in Performance Guide

v“BIND command” in Command Reference

v“PRECOMPILE command” in Command Reference

v“CURRENT DEGREE special register” in SQL Reference, Volume 1

Enabling intra-partition parallelism for utilities

This section provides an overview of how to enable intra-partition parallelism for the following utilities:

8 Administration Guide: Implementation

vLoad

vCreate index

vBackup database or table space

vRestore database or table space

Inter-partition parallelism for utilities occurs automatically based on the number of database partitions.

Enabling database partitioning in a database

The decision to create a multi-partition database must be made before you create your database. As part of the database design decisions you make, you will have to determine if you should take advantage of the performance improvements database partitioning can offer.

Some of the considerations surrounding your decision to create a database in a partitioned database environment are made here.

When running in a partitioned database environment, you can create a database from any database partition that exists in the db2nodes.cfg file using the CREATE DATABASE command or the sqlecrea() application programming interface (API).

Before creating a multi-partition database, you must select which database partition will be the catalog partition for the database. You can then create the database directly from that database partition, or from a remote client that is attached to that database partition. The database partition to which you attach and execute the CREATE DATABASE command becomes the catalog partition for that particular database.

The catalog partition is the database partition on which all system catalog tables are stored. All access to system tables must go through this database partition. All federated database objects (for example, wrappers, servers, and nicknames) are stored in the system catalog tables at this database partition.

If possible, you should create each database in a separate instance. If this is not possible (that is, you must create more than one database per instance), you should spread the catalog partitions among the available database partitions. Doing this reduces contention for catalog information at a single database partition.

Note: You should regularly do a backup of the catalog partition and avoid putting user data on it (whenever possible), because other data increases the time required for the backup.

When you create a database, it is automatically created across all the database partitions defined in the db2nodes.cfg file.

When the first database in the system is created, a system database directory is formed. It is appended with information about any other databases that you create. When working on UNIX®, the system database directory is sqldbdir and is located in the sqllib directory under your home directory, or under the directory where DB2 database was installed. When working on UNIX, this directory must reside on a shared file system, (for example, NFS on UNIX platforms) because there is only one system database directory for all the database partitions that make up the partitioned database environment. When working on Windows, the system database directory is located in the instance directory.

Chapter 1. Before creating a database 9

Also resident in the sqldbdir directory is the system intention file. It is called sqldbins, and ensures that the database partitions remain synchronized. The file must also reside on a shared file system since there is only one directory across all database partitions. The file is shared by all the database partitions making up the database.

Configuration parameters have to be modified to take advantage of database partitioning. Use the GET DATABASE CONFIGURATION and the GET DATABASE MANAGER CONFIGURATION commands to find out the values of individual entries in a specific database, or in the database manager configuration file. To modify individual entries in a specific database, or in the database manager configuration file, use the UPDATE DATABASE CONFIGURATION and the

UPDATE DATABASE MANAGER CONFIGURATION commands respectively.

The database manager configuration parameters affecting a partitioned database environment include conn_elapse, fcm_num_buffers, fcm_num_channels, max_connretries, max_coordagents, max_time_diff, num_poolagents, and stop_start_time.

Related tasks:

v “Configuring DB2 with configuration parameters” in Performance Guide

Related reference:

v“CREATE DATABASE command” in Command Reference

v“sqlecrea API - Create database” in Administrative API Reference

Enabling parallelism for loading data

The load utility automatically makes use of parallelism, or you can use the following parameters on the LOAD command:

vCPU_PARALLELISM

vDISK_PARALLELISM

In a partitioned database environment, inter-partition parallelism for data loading occurs automatically when the target table is defined on multiple database partitions. Inter-partition parallelism for data loading can be overridden by specifying OUTPUT_DBPARTNUMBS. The load utility also intelligently enables database partitioning parallelism depending on the size of the target database partitions. MAX_NUM_PART_AGENTS can be used to control the maximum degree of parallelism selected by the load utility. Database partitioning parallelism can be overridden by specifying PARTITIONING_DBPARTNUMS when ANYORDER is also specified.

Related concepts:

v“Load overview” in Data Movement Utilities Guide and Reference

v“Load in a partitioned database environment - overview” in Data Movement Utilities Guide and Reference

Enabling parallelism when creating indexes

To enable parallelism when creating an index:

vThe intra_parallel database manager configuration parameter must be ON

vThe table must be large enough to benefit from parallelism

vMultiple processors must be enabled on an SMP computer.

10 Administration Guide: Implementation

Related reference:

v“CREATE INDEX statement” in SQL Reference, Volume 2

v“intra_parallel - Enable intra-partition parallelism configuration parameter” in

Performance Guide

Enabling I/O parallelism when backing up a database or table space

To enable I/O parallelism when backing up a database or table space:

vUse more than one target media.

vConfigure table spaces for parallel I/O by defining multiple containers, or use a single container with multiple disks, and the appropriate use of the DB2_PARALLEL_IO registry variable. If you want to take advantage of parallel I/O, you must consider the implications of what must be done before you define any containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to backup your database or table space.

vUse the PARALLELISM parameter on the BACKUP command to specify the degree of parallelism.

vUse the WITH num-buffers BUFFERS parameter on the BACKUP command to ensure enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra.

Also, use a backup buffer size that is:

–As large as feasible. 4 MB or 8 MB (1024 or 2048 pages) is a good rule of thumb.

–At least as large as the largest (extentsize * number of containers) product of the table spaces being backed up.

Related reference:

v “BACKUP DATABASE command” in Command Reference

Enabling I/O parallelism when restoring a database or table space

To enable I/O parallelism when restoring a database or table space:

vUse more than one source media.

vConfigure table spaces for parallel I/O. You must make the decision to use this option before you define your containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to restore your database or table space.

vUse the PARALLELISM parameter on the RESTORE command to specify the degree of parallelism.

vUse the WITH num-buffers BUFFERS parameter on the RESTORE command to ensure enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra.

Also, use a restore buffer size that is:

–As large as feasible. 4 MB or 8 MB (1024 or 2048 pages) is a good rule of thumb.

–At least as large as the largest (extentsize * number of containers) product of the table spaces being restored.

Chapter 1. Before creating a database 11

– The same as, or an even multiple of, the backup buffer size.

Related reference:

v “RESTORE DATABASE command” in Command Reference

Enabling large page support in a 64-bit environment (AIX)

In addition to the traditional page size of 4 KB, the POWER4™ processor in the IBM® eServer™ pSeries® systems also supports a new 16 MB page size. AIX 5L™ for POWER™ Version 5.1 with the 5100-02 Recommended Maintenance package, or Version 5.2, contain support for pages with a 16 MB size. When running under this environment, IBM DB2 Version 9.1 for AIX® 64-bit Edition can be enabled to use these large pages.

Large page usage is primarily intended to provide performance improvements to high performance computing applications. Applications that require intensive memory access and that use large amounts of virtual memory may obtain performance improvements by using large pages.

Notes:

1.For detail instructions on how to run the vmtune or the vmo command, refer to your AIX manuals.

2.You should be extremely cautious when configuring your system for pinning memory and supporting large pages. Pinning too much memory results in heavy paging activities for the memory pages that are not pinned. Allocating too much physical memory to large pages will degrade system performance if there is insufficient memory to support the 4 KB pages.

3.Setting the DB2_LGPAGE_BP registry variable also implies that the memory is pinned.

Prerequisites:

You are working in an AIX 5.x or later 64-bit environment. You must have root authority to work with the AIX operating system commands.

Procedure:

To enable large page support, you must:

1. Configure your AIX server for large page support:

For AIX 5.1 operating systems: Issue the vmtune command with the following flags:

vmtune -g <LargePageSize> -L <LargePages>

For AIX 5.2 operating systems: Issue the vmo command with the following flags:

vmo -r -o lgpg_size=<LargePageSize> lgpg_regions=<LargePages>

where

Specifies the size in bytes of the hardware-supported large pages.

Specifies the number of large pages to reserve.

For example, if you need to allocate 25 GB for large page support, run the command as follows:

12 Administration Guide: Implementation

For AIX 5.1 operating systems:

vmtune -g 16777216 -L 1600

On AIX 5.2 operating systems:

vmo -r -o lgpg_size=16777216 lgpg_regions=1600

2.Run the bosboot command so that the previously run vmtune command or vmo command will take effect following the next system boot.

3.After the server comes up, enable it for pinned memory:

For AIX 5.1 operating systems: Issue the vmtune command with the following flags:

vmtune -S 1

For AIX 5.2 operating systems: Issue the vmo command with the following flags:

vmo -o v_pinshm=1

4.Use the db2set command to set the DB2_LGPAGE_BP registry variable to “YES”, then start DB2:

db2set DB2_LGPAGE_BP=YES db2start

Related concepts:

v“Database managed space” in Administration Guide: Planning

v“System managed space” in Administration Guide: Planning

v“Table space design” in Administration Guide: Planning

Stopping an instance (Linux, UNIX)

You might need to stop the current instance of the database manager.

Prerequisites:

To stop an instance on your system, you must do the following:

1.Log in or attach to an instance with a user ID or name that has SYSADM, SYSCTRL, or SYSMAINT authority on the instance; or, log in as the instance owner.

2.Display all applications and users that are connected to the specific database that you want to stop. To ensure that no vital or critical applications are running, list applications. You need SYSADM, SYSCTRL, or SYSMAINT authority for this.

3.Force all applications and users off the database. You require SYSADM or SYSCTRL authority to force users.

Restrictions:

The db2stop command can only be run at the server. No database connections are allowed when running this command; however, if there are any instance attachments, they are forced off before the instance is stopped.

Note: If command line processor sessions are attached to an instance, you must run the terminate command to end each session before running the db2stop command. The db2stop command stops the instance defined by the DB2INSTANCE environment variable.

Procedure:

Chapter 1. Before creating a database 13

To stop the instance using the Control Center:

1.Expand the object tree until you find the Instances folder.

2.Click each instance you want to stop.

3.Right-click any of the selected instances, and select stop from the pop-up menu.

4.On the Confirm stop window, click OK.

To stop the instance using the command line, enter:

db2stop

You can use the db2stop command to stop, or drop, individual database partitions within a partitioned database environment. When working in a partitioned database environment and you are attempting to drop a logical partition using

db2stop drop nodenum <0>

you must ensure that no users are attempting to access the database. If they are, you will receive an error message SQL6030N.

Related tasks:

v “Setting the current instance environment variables” on page 67

Related reference:

v“db2stop - Stop DB2 command” in Command Reference

v“TERMINATE command” in Command Reference

Stopping an instance (Windows)

You might need to stop the current instance of the database manager.

Prerequisites:

To stop an instance on your system, you must do the following:

1.The user account stopping the DB2 database service must have the correct privilege as defined by the Windows operating system. The user account can be a member of the Administrators, Server Operators, or Power Users group.

3.Force all applications and users off the database. You require SYSADM or SYSCTRL authority to force users.

Restrictions:

14 Administration Guide: Implementation

Procedure:

To stop an instance on your system, use one of the following methods:

vdb2stop

vStop the service using the Control Center

1.Expand the object tree until you find the Instances folder.

2.Click each instance you want to stop.

3.Right-click any of the selected instances, and select Stop from the pop-up menu.

4.On the Confirm Stop window, click OK.

vStop using the “NET STOP” command.

vStop the instance from within an application.

Recall that when you are using the DB2 database manager in a partitioned database environment, each database partition server is started as a service. Each service must be stopped.

Related tasks:

v “Setting the current instance environment variables” on page 67

Related reference:

v “db2stop - Stop DB2 command” in Command Reference

Working with multiple DB2 copies

This section describes how to run and administer multiple DB2 copies on the same computer, including migration, installation, and configuring information. A DB2 Copy refers to one or more installations of DB2 database products in a particular location on the same computer. Each DB2 copy can be at the same or different code levels.

Multiple DB2 copies roadmap

With DB2 Version 9, you can install and run multiple DB2 copies on the same computer. A DB2 Copy refers to one or more installations of DB2 database products in a particular location on the same computer. Each DB2 copy can be at the same or different code levels. The benefits of doing this include:

vThe ability to run applications that require different DB2 versions on the same computer at the same time.

vThe ability to run independent copies of DB2 products for different functions.

vThe ability to test on the same computer before moving the production database to the latter version of the DB2 product.

Chapter 1. Before creating a database 15

vFor independent software vendors, the ability to embed a DB2 server product into your product and hide the DB2 database from your users. For COM+ applications, we recommend that you use and distribute the IBM DB2 Driver for ODBC and CLI with your application instead of the DB2 Runtime Client as only one DB2 Runtime Client can be used for COM+ applications at a time. The IBM DB2 Driver for ODBC and CLI does not have this restriction.

Table 1 lists the relevant topics in each category.

Table 1. Roadmap to multiple DB2 copies information

Category	Related topics
General information	v	Multiple DB2 copies on the same computer (Linux and UNIX)
and restrictions	v	Multiple DB2 copies on the same computer (Windows)
	v	Multiple DB2 copies on the same computer (Windows)
Migration	v	Migrating from a system with multiple DB2 copies (Linux and
		UNIX)
	v	Migrating a DB2 server (Windows)
	v Migrating DB2 32-bit servers to 64-bit systems (Windows)
Installation	v	Installing DB2 servers (Linux and UNIX)
	v Installing DB2 servers (Windows)
Configuration	v	Changing the Default DB2 copy after installation (Windows)
	v Client connectivity using multiple DB2 copies (Windows)
	v Selecting a different DB2 copy for your Windows CLI application
	v	Setting the DAS when running multiple DB2 copies (Windows)
	v Setting the default instance when using multiple DB2 copies
		(Windows)

Administration	v	Listing DB2 products installed on your system (Linux and UNIX)
	v	Managing DB2 copies (Windows)
	v Running multiple instances concurrently (Windows)
Uninstalling	v	Removing DB2 copies (Linux, UNIX, and Windows)
	v Removing DB2 products using the db2_deinstall or doce_deinstall
		command (Linux and UNIX)

Multiple instances of the database manager

Multiple instances of the database manager might be created on a single server. This means that you can create several instances of the same product on a physical computer, and have them running concurrently. This provides flexibility in setting up environments.

You might want to have multiple instances to create the following environments:

vSeparate your development environment from your production environment.

vSeparately tune each environment for the specific applications it will service.

vProtect sensitive information from administrators. For example, you might want to have your payroll database protected on its own instance so that owners of other instances will not be able to see payroll data.

16 Administration Guide: Implementation

+ 707 hidden pages