D

Data clustering, 43

Data concurrency, 79, 465–501. See also isolation levels;

locks

committing a transaction and, 466

Cursor Stability isolation level and, 468, 469, 472–473, 474, 476, 610, 612, 613

dirty reads and, 468–477, 469t

importance of, 465–466

inconsistency of data vs., 466

interleaved or parallel transactions and, 467

isolation levels and, 465, 466–477, 497. See also isolation levels

locks and, 465, 477–494, 478. See also locks

lost updates and, 467–477, 469t

nonrepeatable reads and, 468–477, 469t

phantoms and, 468–477, 469t

Read Stability isolation level and, 468, 469t, 471, 472, 476, 497, 501, 610, 611, 613

Repeatable Read isolation level and, 468, 469t, 469–470, 470, 476, 495, 501, 610, 613

rolling back a transaction and, 466

transaction management and, 322, 325, 466

Uncommitted Read isolation level and, 325, 468, 469t, 474–476, 475, 498, 499, 501, 610, 611, 613

Data consistency, transactions and, 322

Data Control Language (DCL), 2, 6, 9, 256, 257

Data Definition Language (DDL), 2, 6, 9, 50, 120, 122, 256, 257

Data Design Project, 13

Data integrity rules, 220

triggers and, enforcement using, 220

Data Manipulation Language (DML), 2, 6, 9, 86, 122, 255, 256–266

triggers and, 225

XML and, 333

Data mining, 14

Data Mining and Visualization Features, 46–47

Data models, 13

Data normalization, referential integrity constraints and, 381

Data Partition Elimination, 416

Data Partitioning Feature, 45, 414–416, 568

Data partitioning, 413. See also tables, partitioned

Data sharing, 7

Data Source Administrator tool, ODBC, 52

Data Stream Engine, 45

Data types, 198–200t, 246, 365, 366–374, 404–405t, 444, 445, 454, 599–600, 601, 606

big integer (BIGINT), 50, 198, 367, 601

binary large object (BLOB), 59, 200, 270, 371–372, 455, 600

built-in, 198–200t, 366, 373

casting, 9

character large object (CLOB), 59, 200, 270, 372, 455, 600, 601

character string (CHAR), 199, 368–370, 445, 600, 601

CREATE DISTINCT TYPE for, 373

CREATE TYPE for, 228, 373

Database Partitioning feature and, 373

date (DATE), 199, 370, 601

date/time, 370–371, 371t

decimal, 455

decimal (DECIMAL, DEC), 367, 455, 599, 600, 601, 606

default constraints and, 364, 374, 375–378, 376–377t, 378, 602, 605

distinct, 225–228, 252, 373, 587. See also user-defined data types

double-byte character large object (DBCLOB), 200, 270, 372, 455, 600

double-byte character set (DBCS) data and, 369

double-precision (DOUBLE, FLOAT), 199, 368, 600

extenders for, 366, 373

fixed-length character string (CHARACTER, CHAR), 199, 368–370, 445, 600, 601

fixed-length double-byte character string (GRAPHIC), 199, 369, 455, 600

formats for date/time, 371t

identity columns, GENERATED AS IDENTITY, and, 218, 583, 411–413, 455, 601

integer (INTEGER), 198, 600, 601

integer (SMALLINT), 198, 366–367, 601

large object (LOB), 371–372

long varying-length character string (LONG VARCHAR), 199, 227, 270, 369, 601

long varying-length double-byte character string (LONG VARGRAPHIC), 199, 227, 270, 369–370

numeric, 366–368

precision in, 367

sequences and, to generate, 217

single-precision (REAL, FLOAT), 199, 367–368, 600

strong data typing and, 225, 373–374

structured, 228. See also user-defined data types

subtypes and, 228

supertypes and, 228

time (TIME), 199, 370, 601

timestamp (TIMESTAMP), 199, 370–371, 601

user-defined, 225–228, 373–374. See also user-defined data types

varying-length character string (VARCHAR), 199, 200, 368–369, 601, 606

varying-length double-byte character string (VARGRAPHIC), 199, 369

XML, 372–373, 454, 461–462, 571, 599–600, 608, 609

XMLPARSE () and, 372, 455

XMLSERIALIZE () and, 372

Data Warehouse Project, 14

Data Warehouse Server (DWE), DB2, 12–14, 36, 45–47, 85, 568

Administration Console in, 46

Alphablox Analytics in, 47

Data Mining and Visualization Features in, 46–47

Data Partitioning Feature in, 45

DWE Design Studio in, 45–46

OLAP Acceleration in, 46

On-Line Analytical Processing (OLAP) in, 35, 39, 45, 46

On-Line Transaction Processing (OLTP) in, 35, 45, 85–87, 94, 436, 571

Query Patroller in, 47

SQL Warehousing Tool in, 46

WebSphere Application Server and, 46

Data warehousing, 35, 85–87, 90, 95, 571

DATA_ENCRYPT, 101, 155, 573, 581

DATA_ENCRYPT_CMP, 101, 155, 573

Database Administrator (DBADM), 3, 105, 110–111, 106, 118, 127, 137, 147t, 149t, 171, 575–576, 579

authorities and privileges using, 147t, 149t

granting authorities/privileges through, 124

privileges and, 129, 130, 134, 139, 140, 144, 147t

schemas and, 196

Database Connection Services (DCS), 580

Database Connection Services (DCS) directory, 181–182, 249

cataloging and uncataloging, 188–189

Database History, Journal, 75–76, 76

Database Manager, 11, 36, 162, 569, 574, 580, 605. See also databases and database objects

authentication and, 99, 102–103, 155

authorities and, 103

Configuration Assistant in, 77–78, 78

implicitly granting authorities and privileges in, 123–124

indexes and, 212

locks and, 478, 483, 485–487, 492, 611–612

packages and, 120

privileges, 103

stored procedures and, 237

strong data typing and, 225

System Administrator (SYSADM) authority and, 105–106, 108, 109, 110, 111, 118, 129, 130, 134, 139–144, 147t, 575–576, 579

System Control (SYSCTRL) authority and, 105–109, 110, 111, 147t, 149t, 574

System Maintenance (SYSMAINT) authority and, 105, 106, 108–109, 110, 147t, 149t, 574

System Monitor (SYSMON) authority and, 105, 106, 109–110, 147t, 149t

tables and, 197

transactions and, 326

Database Navigator, 50

Database Partitioning Feature (DPF), 44, 373

Database privileges, 113–114, 113. See also privileges

Database System Monitor, 72–73. See also Health Center

Database workload management, 85–87

Data Manipulation Language (DML) in, 86

Data Warehouse Server Edition and, 85, 568

data warehousing and, 85–87

On-Line Transaction Processing (OLTP) and, 35, 45, 85–87, 94, 436, 571

Database-level authorities, and GRANT, 127

Database-level authorities, and REVOKE, 137–138

Database-managed (DMS) files, 169, 174, 175

Databases and database objects, 161–253

access control in, 97, 98

Add Database dialog and, 182, 183

aliases in, 172, 192, 214–217, 245, 250

authentication for, 190, 191

authorities for, 170, 171. See authorities

bind files and, 170

buffer pools and, 169

cataloging and uncataloging, 182–186, 188–189

code set, code point, code page for, 173

collating sequence in, 173

columns in, 196

Configuration Advisor for, 171

connection establishment for, 190–191, 243, 249

constraints in, 374–401. See also constraints

container for, 174–175

Control Center and, 176–177

creating, 164–178, 191–241

bind files and, 170

buffer pool created for, 169

Configuration Advisor for, 171

configuration file initialization for, 170

CREATE DATABASE command for, 165, 171–176

Create Database Wizard for, 165, 176–178, 177, 178

DB2EVENT subdirectory for, 168

directories and subdirectories created for, 165–166, 167, 170, 179

files created for, 168

packages and, 170

schemas for, 170

SQLOGDIR subdirectory for, 168

system catalog tables created for, 169–170

table spaces created for, 169

utility programs and, 170

views created for, 169–170

data types in, 246, 365, 363–374. See also data types

Database Administrator (DBADM) for. See Database Administrator

Database Manager and, 162. See also Database Manager

database-managed (DMS) files and, 169, 174, 175

declared temporary tables in, 197, 202–203, 245, 250, 428–432, 453, 463, 585, 586, 610. See also declared temporary tables

deleting objects (DROP) from, 241–242, 241

descriptive comments for, 174

directories and subdirectories in, 170, 172, 173, 178–189. See also directories and subdirectories

Database Connection Services (DCS) directory in, 181–182, 188–189, 249

LIST DATABASE DIRECTORY and, 179–180

LIST DCS DIRECTORY and, 182

LIST NODE DIRECTORY and, 181

local database directory in, 180

node directory in, 181

system database directory in, 179–180

distinct data types and, 252

dropping objects. See deleting objects (DROP)

executable objects n, 246

extent size in, 174

fields in, 196

hierarchy of objects within, 161–162, 163

indexes in, 192, 244, 210–214, 210, 250, 365, 434–438

INSERT and, 245, 247, 253

instances of, 161–162, 163–164

keywords for, 174

packages and, 170, 192, 240–241, 245, 250–251

passwords for, 190, 191

privileges for, 170, 171

records in, 196, 248, 253

routines and, 251

rows in, 196

schemas in, 170, 192–196, 251, 418–419, 418. See also schemas

sequences in, 192, 217–220, 247, 250, 252–253

servers and, 161–162

stored procedures in, 192, 230–240, 244

system catalog tables and, 169–170, 174

system database directory in, 249

system-managed (SMS) files and, 169, 174, 175

table spaces in, 174–175, 174

tables in, 192, 196–203, 197, 244, 365, 401–418. See also tables

territory for, 173

triggers in, 192, 220–225, 246, 251, 365, 438–443. See also triggers

user IDs for, 190, 191

user-defined data types in, 192, 225–228

user-defined functions (methods) in, 192, 228–230, 249

utility programs and, 170

views and, 169–170, 192, 203–209, 203, 246, 252, 365, 432–434. See also views

Dataflows, 14

Date (DATE), 199, 370, 601

Date/time data types, 370–371, 371t

DB2 9 Family Application Development (Exam 733), 10

DB2 9 Family Fundamentals (Exam 730), 2, 4, 7, 10, 16, 17, 21, 35, 97, 255, 365, 465

DB2 9 for Linux, UNIX, and Windows Advanced Database (Exam 734), 12

DB2 9 for Linux, UNIX, and Windows Database Administration (Exam 731), 4, 5, 16, 21

DB2 9 for Linux, UNIX, and Windows Database Administration Upgrade (Exam 736), 5, 19, 21

DB2 9 for z/OS Database Administration (Exam 732), 7

DB2 control server, 65–66

DB2 Data Warehouse Edition V9.1 (Exam 716), 14

DB2 family, 36–51, 37. See also specific editions

Client in, 51–54

Command Editor in, 66–71, 67

Command Line Processor (CLP) in, 82–83, 83

Configuration Assistant in, 77–78, 78, 93

Connect in, 54–55

Control Center in, 61–64, 62, 63, 64

cost of installation in, 88

Data Warehouse Server Edition (DWE) in, 45–47

data warehousing and, 95

database workload management and, 85–87

DB2 for i5/OS in, 49–50

DB2for z/OS in, 50–51

Design Advisor in, 13, 78–81, 81, 88, 93, 568

Developer Workbench in, 10, 83–84, 85, 89, 94, 569, 570

Enterprise Developer's Edition in, 48–49

Enterprise Server Edition (ESE) in, 42–45, 92, 567, 568

Everyplace database and enterprise editions, 37–38

Express, 38–40, 92, 567

Express-C in, 40, 568

extenders in, 55–59, 90, 91, 95–96

Health Center in, 72–75, 74

history and development of, 36–51, 37. See also specific editions

i5/OS and, 92

iServer and, 88

Journal in, 75–76, 76, 90, 94

License Center in, 76, 77, 570

Personal Developer's Edition in, 47–48, 568

Personal Edition in, 41, 567

registry parameters in, 89, 93

Replication Center in, 64, 65, 568

Runtime Client in, 51–53, 52

Satellite Administration Center in, 65–66, 569

SQL Assist tool in, 66, 67–68, 68

Task Center in, 71–72, 72, 94, 569, 570

tool set for, 60–85

Universal Developer's Edition in, 48, 568

Visual Explain in, 68–71, 69

Workgroup Server Edition (WSE) in, 41–42, 41, 567

DB2 for Common Servers, 36

DB2 Universal Database, 36, 58

DB2 V8.1 Family Fundamentals (Exam 700), 4, 7, 10

DB2/2, 36

DB2 EVENT subdirectory, 168

DB2 I, 7

DB2 NFO, stored procedures and, 238–239

DB2 INSTANCE environment variable, 163

Db2 look utility, 4

Db2 move utility, 4

db2 rhist.asc file, 168

db2 rhist.bak file, 168

DBADM. See Database Administrator (DBADM)

DBCLOB. See double-byte character large object

dbtatld (AutoLoad utility), 112

Deadlock Cycle, 494, 495, 499, 611

Deadlock Detector, 494, 611

Deadlocks, 493–494, 493, 495, 499, 610, 611

DECFLOAT, 50

Decimal (DECIMAL, DEC) data type, 198, 367, 455, 599, 600, 601, 606

DECLARE CURSOR, 315, 316–317, 322

DECLARE GLOBAL TEMPORARY TABLE, 203, 428, 431, 432, 610

Declared temporary tables, 197, 202–203, 245, 250, 428–432, 453, 463, 585, 586, 610

columns in, 429–432

CREATE TABLE and, 428

DECLARE GLOBAL TEMPORARY TABLE for, 203, 428, 431, 432, 610

Default constraints, 364, 374, 375–378, 376–377t, 378, 602, 605

Deferred binding, 241

DELETE, 67, 112, 116, 118, 123, 124, 129, 130, 134, 139, 140, 144, 149t, 156, 157, 158, 257, 264–265, 265t, 347–348, 359, 569, 575–577, 584, 593, 594, 603, 607

Delete Rule for referential integrity constraint and, 392–397, 393, 394, 395, 396, 458

DROP vs., 359

locks and, 486t

NULL and, 260, 263, 264, 279–280, 447, 587, 588, 595–596, 605

ON DELETE CASCADE, 392–397, 458, 603, 604, 607

ON DELETE NO ACTION, 392–397, 396, 604, 607

ON DELETE RESTRICT, 392–397, 395, 458, 603, 604

ON DELETE SET NULL, 392–397, 394, 458, 604

positioned delete operations and, 265, 360, 594

referential integrity constraints and, 385, 392–397, 393, 394, 395, 396, 458

searched delete operations and, 265, 360, 594

SELECT and, 264

transactions and, 326

triggers and, 225

WHERE clause and, 264, 265

XML and, 333, 335

Delete Rule, referential integrity constraints and, 392–397, 393, 394, 395, 396, 458

DELETE trigger, 582

Deleting database objects (DROP), 241–242

Department Administrator, authorities and privileges, 149t

Dependents or children, referential integrity constraints, 384t

DESC (descending) sort, 592

Descendents, referential integrity constraints and, 385t

Design Advisor, 13, 78–81, 81, 88, 93, 568

Design Studio, 45–46

DETACH PARTITION, 416, 609

DETERMINISTIC, 236

Developer Workbench, 10, 83–84, 85, 89, 94, 569, 570

Diagnostics, 9

Dimension objects, 13

Directories and subdirectories, 170, 179, 178–189, 580

Database Connection Services (DCS) directory in, 181–182, 188–189, 249

database creation and, 165–166, 167

directory files in, 170, 179

LIST DATABASE DIRECTORY and, 179–180

LIST DCS DIRECTORY and, 182

LIST NODE DIRECTORY and, 181

local database directory in, 180

node directory in, 181

SQL and, 172, 173, 178–189

system database directory in, 179–180, 249

Directory files, 170, 179

Dirty reads, 468–477, 469t. See also isolation levels

Disaster Recovery, 6

DISPLAY, 7

DISTINCT clause, 267, 269, 270, 271, 355, 588

user-defined data types and, 227

Distinct data types, 225–228, 252, 373, 587. See also user-defined data types

Distinct type privileges (iSeries/zSeries only), 135, 144–145

Distributed Computing Environment (DCE) security services, 98, 155, 573

Distributed Connection Services (DCS), 107. See also Database Connection Services

Distributed databases, 121. See also federation

Distributed Relational Database Architecture (DRDA), 43, 580. See also DRDA Application servers

Distributed units of work, 11

Division of business application and logic, 230

DML, 571

DMS, 3, 11

DOUBLE PRECISION, 199

Double-byte character large object (DBCLOB), 200, 270, 372, 455, 600

Double-byte character set (DBCS) data, 369

Double-precision (DOUBLE, FLOAT) data type, 199, 368, 600

DRDA Application servers, 568

Database Connection Services (DCS) directory in, 181–182, 249

DROP, 241–242, 359, 593

DELETE vs., 359

stored procedures and, 232

DROP PROCEDURE, 232

DROPIN privilege, 115, 128, 138–139

DWE Design Studio, 45–46

Dynamic Logical Partitioning, 49