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