Index

Note: Boldface numbers indicate illustrations and code samples; t indicates a table.

AABEND, 66tABEXP, 54tABIND, 54t, 569access control, ii, 95–144, 96. See also security

Advanced Access Control and, 9, 13, 16, 17Application Transparent–Transport Layer Security (AT–

TLS) and, 99auditing, 132–140. See also auditing, 132authorities in, 114–125authorization control through exit routines and, 98authorization IDs for, 101–102catalog table, 131CICS and, 99column, 6column masks in, 102, 104–105, 104, 105Communications Database (CDB) and, 99data set protection in, 100DB2_SECURE_VAR security function for, 132, 132denial of service attacks and, 100direct row access and ROWID in, 168encryption and, 100environments and, 97exit routines and, for authorization control, 98explicit privileges in, 109–114, 110–113tGRANT and, 101hash, iii, viIDs for objects and, 100–101IMS and, 99Kerberos security and, 6, 99, 133Label Based Access Control (LBAC) and, 9local DB2, 98Logical Terminals (LTERMs) and, 99object, 100–131. See also specifi c objectsownership in, 125–130, 125performing tasks on behalf of others in, DBADM authority,

108–109, 109

PERMIT in, 97, 97privileges in, 109–114, 110–113tremote access, 98Resource Access Control Facility (RACF) and, 97, 98, 100,

101, 132REVOKE and, 101roles in, 102row, 6row permissions in, 102–103, 103, 104–105, 105Secure Sockets Layer (SSL) and, 99–100SQLID in, 102, 102subsystem, 97–100trust attributes and, 107–108ttrusted contexts in, 102, 105–108TSO and, 98views, 131, 131

ACCESS DATABASE(DB2), 67tAccess Method Services (AMS), REORG, 381Access Path Advisor, 30access paths, vi, 30, 745–756

Access Path Advisor for, 30Access Plan Graph for, 30ACCESSCREATOR and, 756ACCESSNAME and, 756ACCESSTYPE and, 756, 757Cartesian joins and, 767–768direct row access in, 761DISTINCT and, 759DSN_COLDIST_TABLE and, 802, 802–803tDSN_DETCOST_TABLE and, 790, 791–792tDSN_FILTER_TABLE, 789, 789–790tDSN_FUNCTION_TABLE and, 773, 773–775tDSN_GROUP_TABLE, 784, 784–786tDSN_KEYTGTDIST_TABLE and, 803, 804–805tDSN_PGRANGE_TABLE and, 797, 797–798tDSN_PREDICATE_TABLE and, 778, 778–781tDSN_PTASK_TABLE, 787, 787–789t

942 Index

DSN_QUERY_TABLE and, 800, 800–801tDSN_SORT_TABLE and, 793, 793–794tDSN_STATEMENT_CACHE_TABLE and, 775, 775–778tDSN_STATEMENT_TABLE and, 770, 771–773tDSN_STRUCT_TABLE and, 782, 782–784tDSN_VIEWREF_TABLE and, 798, 798–799tevaluation of, 756–806EXPLAIN output and, guidelines for use of, 806–808EXPLAIN table queries and Optim products in, 805–806global optimization and, 769GROUP BY and, 759hash access and, 761hybrid joins in, 764–765index access and, 756–758, 757, 819–824INDEXONLY and, 758limited partition scanning in, 760–761, 761list prefetch in, 760MATCHCOLS and, 757merge scan join in, 763–764MIXOPSEQ and, 756nested loop joins in, 762–763non-matching index scan and, 757Optim Data Studio and, 770Optim Query Tuner and, 770optimization hints for, 808–809optional tables populated by EXPLAIN in, 770–805parallelism usage and, 768prefetching in, 760SELECT and, 758, 759, 759sequential detection (dynamic prefetch) in, 760sequential prefetch in, 760SORT and, 761–762star joins in, 765–768subqueries and, interpreting access for, 768–770, 769t, 769table, 758–770TNAME and, 756topology check in, 766unique index check, 765

Access Plan Graph, 30access plans, 746

EXPLAIN and, 746ownership of, and privileges, 126, 128tprivileges for, 110t

ACCESS(FORCE), 430, 430ACCESS_CNTL_MODULE, 54tACCESS_DEGREE, 752t, 768ACCESS_PGROU_ID, 752tACCESSCREATOR, 750t, 756ACCESSCTRL, 115, 117t, 121tACCESSNAME, 750t, 756ACCESSTYPE, 749–750t, 756, 757accounting trace, 835–837, 840–842tACCSSCTRL, 123ACCUMACC, 54t, 658ACCUMUID, 54t, 658ACHKP, 426tACQUIRE, 562t, 725

ACTION, 562tADD VERSIONING, 217, 218ADD_MONTHS, 167address spaces, 38–41, 39

Advanced Database Management Facility (ADMF) and, 40allied, 38, 41CICS and, 38database services (DSAS) and, 38, 40Distributed Database Facility (DDF), 38, 40–41IMS and, 38Internal Resource Lock Manager (IRLM), 38, 39relationships among, 39stored procedures and, 38, 616system services (SSAS) and, 38, 39Workload Manager (WLM), 41, 629

ADDRESS, 107tADMIN_TASK_ADD, 816ADMIN_UTL_EXECUTE, 816ADMIN_UTL_MODIFY, 816ADMIN_UTL_MONITOR, 816Administration Console, Data Studio, 27administrative authorities, 114–125. See also authoritiesADMTPROC, 54tADO.NET, DB2 Connect, 25Advanced Access Control for LUW, 9, 13, 16, 17Advanced Copy Services, 9, 13–17Advanced Database Management Facility (ADMF), 40Advanced Enterprise Server, DB2, 4, 17–18. See also DB2 for

Linux, UNIX, and Windows (LUW)advanced functionality, 667–714Advanced Program-to-Program Communications (APPC)

distributed data access and, 646Advisory Reorg-Pending (AREO) status, 189

indexes and, 234table spaces, 210

advisory status, ivresolving, using DISPLAY DATABASE, 424–426, 424,

425, 426, 426–429tAEXITLIM, 54taffi nity processing, data sharing, 506AFTER, 316after trigger, 669, 671–672, 671–672AGCCSID, 54tagent services manager, 40aggregate functions. See column functionsAIX, 2, 15, 18ALCUNIT, 54taliases, 148

creating, using CREATE, 153distributed data access and, 646–647ownership of, and privileges, 126–127, 128t

ALL, 304ALL/DBNAME, 55tallied address spaces, 38, 41ALLOCATE, 725allocation, table space, using PRIQTY and SECQTY, 206–207ALLOW DEBUG MODE, 628

Index 943

ALTER, 110t, 113t, 116t, 117t, 120t 121t, 128t, 129, 135t, 136, 152, 154–155, 474, 855, 858binding/rebinding and, 572coordinating updates to distributed data with, 653sequence objects and, 600user-defi ned data types and, 686

ALTER BUFFERPOOL, 67t, 87ALTER DATABASE, 235, 235ALTER FUNCTION, 722ALTER GROUPBUFFERPOOL, 67t, 500–501, 501ALTER INDEX, 233–234, 234, 396, 453, 855

freespace and LOAD in, 366table- vs. index controlled partitioning and, 200

ALTER PROCEDURE, 617, 626, 626, 722COMMIT ON RETURN in, 623–624, 625

ALTER SEQUENCE, 193ALTER STOGROUP, 236, 236ALTER TABLE, 12, 151, 152, 180, 189–191, 191, 392–393,

681, 709, 709adding partitions with, 197, 197catalog and directory use of, 190check constraints and, 182–183clone tables and, 215, 215fullselects and, 256–257Materialized Query Tables (MQTs) and, 215rotating partitions and, 198–199, 199system-period temporal tables, 218, 218–219table- vs. index controlled partitioning and, 200traces and, 838versioning with, ADD VERSIONING and, 217, 218

ALTER TABLESPACE, 201, 209–210, 210, 855binding/rebinding and, 569freespace and LOAD in, 366

ALTER UTILITY, 67tALTERDTS, 137t, 137ALTERIN, 113t, 116t, 120tALWAYS, 175tAMCCSID, 55tAND, 822anomalies in data, 242, 243tANY, 304APPENSCH, 55tapplication development, 30–31application drivers, DB2, 20–21application period, 151–152, 216–219, 285–286, 286, 310–311,

311, 605, 607–608, 607, 608Application Programming Interfaces (APIs), 2, 23, 25, 593application programs, 577–612. See also application programs

and SQL, belowbacking out of uncommitted changes in, 579bi-temporal management/tables in, 608commit in, 577–579concurrency and, 578deadlocks or timeouts in, 586Distributed Data Facility (DDF) and, 586distributed environments and sharepoints in, CONNECT

statement for, 586

fetch for limited rows in, 591–592, 591, 592global temporary tables and, 586–591. See also temporary

tablesglobal transactions in, 586identity columns and, 596–599. See also identity columnsIndependent Software Vendors (ISVs) and, 578Information Management System (IMS) and, 586LOCK TABLE and, 586logic UOWs and, 579multi-row FETCH in, 593–594, 593, 594multi-row INSERT in, 594–595, 595multi-row MERGE in, 595–596, 596multi-row operations in, 593–595object-oriented (OO) interfaces and, 586online transactions and, 578optimistic locking in, 603–604points of consistency in, 579Recoverable Resource Services (RRS) and, 586releasing a savepoint in, using RELEASE SAVEPOINT,

585, 585, 586restoring to a savepoint in, 582–585, 582–585rollback of transaction in, using ROLLBACK, 577–579,

580, 580, 582–585, 582–585SAVEPOINT statement in, 580–586, 580, 581, 582tsavepoints in, 577, 580–586, 580. See also savepointssequence objects in, 599–603. See also sequence objectsSET SAVEPOINT in, 586temporal data management and, 605–608. See also

temporal tablesTO SAVEPOINT clause in, 580, 582–585, 582–585unit of recovery (UR) and, 580unit of work (UOW) in, 578, 579, 586

application programs and SQL, 517–554. See also application programs; SQLCLOSE CURSOR in, 541, 541communicating with DB2 in, methods for, 517concentration of literals in, 548CURSOR WITH HOLD in, 541–542, 542, 552, 554cursors in, 536–538, 536, 537, 538, 542–546, 542t, 552.

See also cursorsDCLGEN and, 519, 524–526, 524, 525–526DECLARE CURSOR in, 536, 536deferred embedded SQL in, 546–547DELETE in, 553delimiting, using EXEC SQL and END-EXEC, 518, 518dynamic SQL in, 517, 546–549, 552. See also dynamic

SQLembedded dynamic SQL in, 546EXECUTE in, 520executing SQL statements in, 538–546execution validation in, 528–535FETCH in, 523, 538–540, 539, 540GET DIAGNOSTICS in, 532–535, 532–535t, 546, 554HAVING in, 520host arrays in, 523–524, 523, 524host structures in, 520, 523, 523host variables and, 520–521, 521

944 Index

indicator variables in, 521–522, 522INSERT in, 523inserting data in, using SET clause with INSERT in, 527,

527INSTEAD OF in, 537interactive SQL in, 546Java Database Connectivity (JDBC) for, 517–518, 549–550MERGE in, 523OPEN CURSOR in, 537–538, 538Open Database Connectivity (ODBC) for, 517–518,

548–549OPEN in, 520positioned delete in, 540, 540, 543positioned update in, 539–540, 540, 543PREPARE in, 520, 521PureQuery and, 549–550retrieving multiple rows of data in, using SELECT INTO,

527, 552retrieving single row of data in, using SELECT, 526–527,

527REXX and, 518rowset cursors in, 545–546, 546SCROLL in, scrollable/nonscrollable cursors and, 542–545,

543, 544, 544t, 545searching data in, using SELECT, 528, 528SQL communication areas (SQLCA) in, 528, 528–531tSQLCODE in, 531SQLJ for, 517–518, 549–550SQLSTATE in, 531static SQL in, 517, 546, 554table defi nitions in, DECLARE, 518–528, 519UPDATE COLUMN in, 536–537, 536, 537updating data in, using SET clause with UPDATE, 527, 527view defi nitions in, DECLARE, 518–528, 519WHERE clause in, 520WHERE CURRENT in, 537

application requestors, 84, 641Application Server Edition, DB2 Connect, 23, 25application servers, 83, 641Application Transparent–Transport Layer Security (AT–TLS),

99application-period temporal tables, 219, 219applications, checking access to, with EXPLAIN, 807–808APPLNAME, 748tapproximate data type, 156tAPRETAINDUP, 562tARC2FRST, 55tarchive log data sets, 439ARCHIVE LOG, 67tarchive logs, 470ARCHIVE, 111t, 118tARCPFX/ARCPFX2, 55tARCRETN, 55tARCWRTC, 55tARCWTOR, 55tARDBP, 428tAREO, 429t

AREOR, 429tAREST, 430arithmetic expressions, 338ARM/XSCALE, 19arrays, host, 523–524, 523, 524AS IDENTITY, 175tAS keyword, 274AS SECURITY LABEL, 132ASCCSID, 55tASCII, 164, 164, 171, 253, 268, 273, 366. See also CCSIDsAssembler, 616ASSIST, 55tasterisk (*) command, 66t, 257–258ASUTIME parameter, 630asynchronous read/write, 858ATOMIC, 595atomic key, 227, 246attachment

data sharing and, 507attachments/attachment facilities, 41–46

call, 42, 43, 45CICS, 41, 42, 42IMS, 42–43, 43Recoverable Resource Manager Services, 42Resource Recovery Services (RRS) and, 43, 45–46SQL and, 42TSO, 41, 43–44, 44Universal Language Interface, 42, 46

attributes, 238–239AUDIT, 568audit policies, 137–140, 137–140taudit trace event classes, 134, 134–135tauditing, v, 5, 132–140, 142

audit trace event classes in, 134, 134–135tcategories of, 139, 139–140tIDs, specifi c, 135policies for, 137–140, 137–140troles, specifi c, 135starting/stopping trace in, 136, 136table, 136, 137trace in, 133–135, 134–135ttrace, audit trace, 838, 842t

AUDITPOLICYNAME, 137tAUDITST, 55tAUTH, 55tAUTHCACH, 55tauthorities, ii, 114–125

GRANT, 124, 124managing, 122–125REVOKE, 124, 124separating, SEPARATE_SECURITY and, 122–125WITH GRANT OPTION, 124, 125

authorizations, v, 98, 101–102, 144binding/rebinding and, 568column level, vexit routines to control, 98IDs for, 101–102

Index 945

plan execution, 130primary vs. secondary ID for, 101, 133roles in, 102row level, vSQLID in, 102, 102SYSADM and, 102trusted contexts in, 102

AUTOESTSPACE, 377automatic rebinding, 567–569Automatic Restart Manager (ARM), 492autonomic computing, 7autonomic statistics, 814–816auxiliary tables, 147, 153, 185–188, 187–188auxiliary warning (AUXW) status, 426–427t, 472availability, in DB2 for z/OS, 5available pages, 855AVG, 276–277, 277avoidance, lock, 728–729, 728, 729t

Bbacking out of uncommitted changes, 579BACKODUR, 55tBACKUP SYSTEM, 464, 466backups, iv, 482. See also recovery and restart

BACKUP SYSTEM in, 464, 466compression and, 8, 13–17COPYPOOL in, 464DUMP, FROM DUMP in, 466Hierarchical Storage Management (HSM) in, 464LIST COPYPOOL in, 466LOAD REPLACE, 467object-level recovery, with system-level backups,

466–467REBUILD INDEX, 467RECOVER in, 466–467REORG in, 467RESTORE SYSTEM in, 464–466Storage Management Subsystem (SMS) in, 464system-level, 464–467

base (permanent) tables, 147, 269maximum number of, 269

basic row format, 161Basic Sequential Access Method (BSAM), 371BATCH, 97batch performance monitors, 831–833batch processes, 6, 37, 38

distributed data access and, 653–654PERMIT, 97, 97

before trigger, 669, 672–673, 672–673BETWEEN/NOT BETWEEN, 262, 282, 282, 687BI, DB2 Connect and, 23bi-temporal tables, 219–220, 220, 608BIGINT, 157, 158, 172t, 174BINARY/binary data type, 156t, 159, 162, 190

binding/rebinding and, 568binary large object (BLOB), 159, 163, 172t. See also large

objects (LOBs)

BIND/REBIND, 47, 66t, 110t, 116t, 117t, 120t, 123, 128t, 129, 130, 558, 561, 566–567, 566, 571–572, 574, 746, 828data maintenance and, 411locks and, 721, 724

BIND/REBIND PACKAGE, 560, 565, 573, 651–652, 685, 722

BIND/REBIND PLAN, 558, 558, 565, 573, 652–653, 685, 722BIND QUERY, 808BIND(ADD), 561BIND(REPLACE), 561BIND_EXPLAIN_ONLY, 755tBINDADD, 111t, 116t, 119tBINDAGENT, 111t, 116t, 119t, 129, 572binding and rebinding, 555–576

ABIND subsystem parameter and, 569ALTER TABLESPACE and, 569AUDIT and, 568authorizations and, 568automatic, 567–569BINARY, VARBINARY and, 568BIND/REBIND PACKAGE and, 560, 565, 573, 651–652,

685, 722BIND/REBIND PLAN and, 558, 558, 565, 573, 652–653,

685, 722bind process in, 555, 557BIND/REBIND statement in, 47, 66t, 110t, 116t, 117t,

120t, 123, 128t, 129, 130, 558, 561, 566–567, 566, 571–572, 574, 746, 828

BIND(ADD) in, 561BIND(REPLACE) in, 561BINDAGENT privilege for, 572buffer pools and, 569, 576CALL in, 556CASCADE and, 568CHAR, VARCHAR and, 568CICS and, 558COEXIST parameter and, 569collections in, 558–559, 570columns and, 568coprocessor use in, 557CURRENT PACKAGE PATH in, 559CURRENT PACKAGESET in, 559, 559data types and, 568Database Request Module (DBRM) for, 555–558, 560,

566, 568DCLGEN and, 556DECLARE TABLE and, 556DEFER in, 576DISCONNECT bind parameter and, 651distributed data access and, 651Environmental Descriptor Manager (EDM) pool and, 558EXEC SQL in, 556EXECUTE and, 571–572EXPLAIN and, 569, 747GRAPHIC, VARGRAPHIC and, 568in-use packages/plans and, 560–561indexes, 568

946 Index

inoperative plans/packages and, 569invalidations in, 566, 574ISOLATION and, 561isolation levels and, 575, 576load modules for, 555locks and, 716migration of plans and packages and, 570–571naming conventions and, 559, 566options for, 561–565, 562–565townership of plan/package and, using OWNER, 571–572package lists and, using REBIND, 567, 567packages and, using BIND/REBIND, 558, 566–570, 566PADDED/NOT PADDED and, 568Partitioned Data Sets (PDS) and, 556plan execution authorization and, using VALIDATE, 573plan-to-package ratio in, 558PLANMGMT options for, 570–571, 570t, 571plans and, 558, 568, 570precompile process in, 555, 556–557preliminary steps in, 566program preparation for, using PREPARE, 555, 556, 558REBIND in, 561removing a plan or package in, 571, 571REOPT in, 574, 576RUN in, 559RUNSTATS and, 561schemas and, 685SET NULL and, 568SQL and, 555–558, 560SQLCA and, 556, 557static binding in, 558SYSADM and, 567SYSPLAN and SYSPACKAGES for, 561, 569table spaces and, 568–569tables and, 568TIME, TIMESTAMP, DATE columns, 568trigger packages and, 561triggers and, using REBIND TRIGGER PACKAGE, 576,

678unqualifi ed objects and QUALIFIER, 572user-defi ned functions and, 568versioning in, 559views and, 568

BINDNV, 55tBLKSIZE, 55tBLOB. See binary large object (BLOB); large objects (LOBs)block fetch, viBMPTOUT, 55tBoolean terms and predicates, 344boot strap data set (BSDS), 112t, 116t, 440–441

data sharing and, 494, 509distributed data access and, 646buffer pools and, 855DSNJU003 (Change Log Inventory) and, 419, 465, 475

bottom-up vs. top-down approach to design, 238buffer manager, 40buffer pools, v, 9–10, 86–87, 855–871

ALTER/DISPLAY GROUPBUFFERPOOL in, 500–501, 501

asynchronous read/write in, 858binding/rebinding and, 569, 576BUFFERPOOL command and, 116t, 120t, 204, 222checkpoints and page externalization in, 858–859creating, using ALTER BUFFERPOOL, 87Data Manager Threshold (DMTH) in, 863data sharing and, 494Deferred Write Threshold (DWQT) and, 860–861design strategies for, 864–865, 864–865tDSVCI and, 856dynamic SQL caching in, 870–871, 870, 871Environmental Descriptor Manager (EDM), 869–871First-In First-Out (FIFO) queuing in, 857group buffer pool dependent data in, 495–496group buffer pools (GBPs, cache structures) in, 489–490,

499–502, 500I/O requests and externalization of, 857–858Immediate Write Threshold (IWTH) and, 858, 863–864inter-DB2 read/write (R/W) interest and, 495–496internal thresholds in, 863–864page fi xing in, with PGFIX, 862–863page size in, 856pages in, 855–856parallelism and, using VPPSEQT and VPXPSEQT, 862queue management in, 857Row Identifi er (RID), 866–868Sequential Prefetch Threshold (SPTH) in, 863sequential vs. random processing in, using VPSEQT, 860size of, 501, 859, 866, 868, 869–870sort and, 868statistics on, 867–868stealing method in, using PGSTEAL, 862synchronous reads/writes in, 857–858tuning, using DISPLAY BUFFERPOOL, 865, 865virtual, 856–857work fi le overfl ow in, 867writes to, 860–861

BUFFERPOOL, 116t, 120t, 204, 222buffers, FLA, 460BUILD phase, 375

in REORG, 379, 388in LOAD, 356

built-in functions, 275user-defi ned data types and, 689, 689

BY DEFAULT, 175t

CC/C++, 1, 2, 14, 593, 616, 710

DB2 Connect and, 25user-defi ned functions (UDFs) and, 690

cache, vCACHE DYNAMIC SQL, 87cache invalidation, SQL, using RUNSTATS, 405CACHE option, 174cache structures, group buffer pools, 489–490

Index 947

cache tables, EXPLAIN and, 746–747CACHE, 175tCACHEDYN, 55tCACHEDYN_FREELOCAL, 55tCACHEPAC, 55tCACHERAC, 55tCACHESIZE, 562tCAF. See Call Attachment Facility (CAF)CALL, 614, 614, 617, 617, 634

binding/rebinding and, 556distributed data access and, 659stored procedures and, 623, 624, 627, 629

Call Attachment Facility (CAF), 41–43, 45, 97CURSOR WITH HOLD in, 541–542, 542IMS and, 45interfacing with DB2 through, 47locks and, 727, 728traces and, 834TSO and, 43, 45

calls, ii, 612CANCEL THREAD, 67tcardinality, 809Cartesian joins, 767–768Cartesian products, 262–265, 263–264, 301CASCADE, in binding/rebinding, 568cascading triggers, 669CASE, 334–335, 334, 335, 350, 634

column masks and, 104triggers and, 671

CASE WHEN, 104casting, 714

large objects (LOBs) and, 707user-defi ned data types and, 687–688, 688

castout process, 501–502, 502CATALOG, 55tcatalog, v, 74–82, 74–81t, 82, 92, 94. See also catalog statistics,

belowaccess control and, 131ALTER TABLE and, 190check constraints and, 182Communications Database (CDB) and, 74consistency queries to, 82, 82copying, 453–454DATA CAPTURE CHANGE and, 83Data Control Language (DCL) and, 74Data Defi nition Language (DDL) and, 74data sharing and, 494, 505DSN1CHKR and, 74DSN1COPY and, 74ICF catalog in, 470indexes and, restrictions on, 223, 233LISTCAT in, 470locks and locking in tables of, 74recovery and, 467REORG and, 389–391, 395–396RUNSTATS and, 399, 399, 404SQL access to, 74

statistics on. See catalog statistics, belowSYSDBASE to reorganize, 396UPDATE, 399, 399

catalog statistics, 809–816autonomic statistics and, 814–816cardinality in, 809fi lter factors and, 809–811histogram statistics and, 811–812, 812partitioned table spaces, 812–813, 812tproduction environment modeling using, 411, 813–814,

813–814tCATDDACL, 55tCATDMGCL, 55tCATDSTCL, 55tCATENFM, 420CATMAINT, 420CATXDACL, 55tCATXMGCL, 56tCATXSTCL, 56tCCSIDs, 162, 164, 171, 203

large objects (LOBs) and, 703LOAD and, 366

CDSSRDEF, 56tCEEDUMP, 632CHANGELIMIT, 448–449, 448CHAR/character data type, 156t, 159, 160, 171, 172t, 190, 568character large object (CLOB), 159, 161, 163, 171, 172t. See

also large objects (LOBs)CHARDEL, 362, 372–373CHARSET, 56tCHECK, 355, 411–414, 484check constraints, iv, 178, 182–183, 183, 681–682CHECK DATA, 183, 365, 412–413, 413, 461–464CHECK INDEX, 413–414, 414, 434, 461–462Check Pending (CHKP) status, 182, 461–462check utilities, ivCHECK_FASTREPLICATION, 56tCHECK_SETCHKP, 56tCHECKING, 137t, 139t, 144checkpoints, iv

intervals for, using SET LOG, 477, 478page externalization and, 858–859

CHGDC, 56tCHKFREQ, 56t, 858–859CHKLOGR, 56tCHKMINS, 56tCHKP, 427tCHKTYPE, 56t, 858–859CICS. See Customer Information Control System (CICS)CICS attachment facility, 41, 42claims, locks and, 731, 731tCLASST threshold, 502CLI, 19–22

DB2 Connect and, 25traces and, 834trusted contexts and, 106

CLIST. See Command List (CLIST)

948 Index

CLOB. See character large object (CLOB)clone tables, iii, 147, 151, 215–216

limitations on, 215–216recovery of, 463

CLOSE, 541CLOSE CURSOR, 541, 541Cloudscape, 18CLUSTER, 222clustering, vi, 10, 15–16

indexes and, 224–225, 225Multidimensional Clustering (MDC) tables and, 12, 13,

16, 17pureScale and, 10

CLUSTERRATIO, 395, 411CMTSTAT, 56t, 658, 664COALESCE, 330–332COBOL, 1, 2, 7, 593, 616, 690, 710CODESET default, 171COEXIST parameter, 569Cognos, 2coherency control, in data sharing, 496COLDEL, 362, 362, 372–373COLGROUP, 401–402collections, 558–559

binding into, 570privileges for, 111t

COLLID, 139t column distribution tables, 802, 802–803tcolumn function, 275, 276–277, 276, 277

COUNT, 279, 279GROUP BY and, 277–278, 277, 278SUM, 279, 279

COLUMN_FN_EVAL, 751tcolumns, 145

access control for, 6atomic key in, 246authorization at level of, vbinding/rebinding and, 568cardinality of, 809composite key in, 246correlation names in, 269–270, 269defi ning, with CREATE TABLE, 245–246, 246derived, 273–274, 274EMPNO fi elds and, 172–176foreign key in, 246, 248–249functions on, 275, 276–277, 276, 277GENERATED ALWAYS in, 173GROUP BY and, 277–278, 277, 278identity, iii, 172–176, 173, 174, 175–176tinclude, vi, 228, 228INSERT into specifi c, 289, 289join, 266–267, 266–267LOAD and ROWID, 365–366masks on, for access control, 102, 104–105, 104, 105maximum of, 184, 184tpermutation, changing order using SELECT in, 260,

260–261

primary key in, 246, 247projection, projecting columns from table using SELECT

for, 259–260, 259–260renaming, using SELECT ORDER BY, 272, 273selecting from multiple tables using SELECT, 262–269transaction start ID, 177–178unique key in, 246, 248UNLOAD and, 372

combined outer joins, 331, 331–332, 332COMMAND, 652Command Center, Control Center, DB2 and, 33Command Editor, Control Center, DB2 and, 34Command Line Processor (CLP), 19, 21Command List (CLIST)

installation, migration and, 50stored procedures and, 634utilities and, 70

commands, 65–68, 67–68t, 91DB2 Interactive (DB2I) and, 47DSN, 65–66, 66t

COMMENT, 128t, 653COMMENT ON, 129, 572commit, ii, 554, 577–579

two-phase, 653–654COMMIT, 49, 128t, 609

claims and, 731, 731t, 731distributed data access and, 659locks and, 719, 725, 727, 728, 734ODBC and, 549scope of, 438–439, 715sequence objects and, 601stored procedures and, 623, 624

COMMIT ON RETURN, 623–624, 625communicating with DB2, using SQL methods, 517Communications Database (CDB)

access control and, 99catalog and, 74distributed data access and, 85–86, 641, 644–646, 663

communications protocols, 84–85, 643–644communications. See distributed dataCOMPACT, 56tcomparison expressions, 338comparison operators, 261, 304, 687COMPAT, 56tCOMPOSITE, 652composite key, 227, 246compound predicates, 344COMPRESS, 204, 207–208, 208, 222, 224COMPRESS_SPT01, 56tcompression, v, 6, 714

backup and, 8, 13–16in DB2 for z/OS, 6in DB2 for Linux, UNIX, Windows (LUW) and, 8, 9–10,

13–17DSN1COMP and, 420–421, 421indexes and, 224large objects (LOBs) and, 704

Index 949

Lempel-Ziv (LZ), 8row-level, 9–10, 13, 16, 17RUNSTATS and, 403–404

concentration of literals, 548concurrency, iii, 578, 715–743. See also locks and locking

claims on, 731, 731tcommit scope in, 715data sharing and, 496database design for, 736–737, 737drains on, 732in DB2 for z/OS, 4serialization in, 715

CONCURRENT ACCESS RESOLUTION, 562tconcurrent copies, DFSMS, 450CONDBAT, 56t, 660Conditional Restart Control Record (CRCR), 475conditional restart of DB2, 475CONNECT, 84, 541, 586, 642–643, 649–651, 649Connection Concentrator, 10, 16, 17connection pooling, vi, 659–661CONNECTIONS, 665connectivity, v, 10, 16–17, 19–25, 19

Data Server Clients for, 19–22Data Server Drivers for, 19Data Server Driver for JDBC and SQLJ in, 20, 21Data Server Driver for ODBC and CLI in, 20–22Data Server Driver Package and, 20–22Data Server Runtime Client for, 19–22DB2 application drivers in, 20–21DB2 Connect and, 19, 22–25instance merge modules in, DB2, 21non-DB2 instance merge modules in, 21trusted connections in, 106

consistency queries, catalog, 82, 82consistency, points of, 579console, z/OS, 93constants, 688constraints, iii, 178–183, 253

check, iv, 178, 182–183, 183foreign key, 181INSERT and, 287keys and, 178–180LOAD and, 363–365, 364primary key, 180, 180referential, 178–180, 179triggers and vs., 681–682unique, 178UPDATE and, 291

Content Management (CM), 2, 3contention, 499, 721, 742CONTEXT, 138t, 140tcontexts, trusted. See trusted contextscontinuous availability, in DB2 for z/OS, 4Control Center, 30, 32–35, 70, 685CONTSTOR, 56tconversion, 50–51, 91. See also installation and migrationCOORDNTR, 56t

coprocessor, in binding/rebinding, 557COPY, 110t, 116t, 117t, 120t, 128t, 134, 194, 222, 427t, 463,

469, 474, 562tDSN1COPY and, 421–422image copies and, 442, 444, 444, 447recovery and, 438statistics and, 408, 410

copy pools, 484copying, v. See also inline copies; recovery and restart

Advanced Copy Services, 9, 13, 14, 15, 16, 17COPYDDN, in LOAD and REOGR, 357, 360, 380COPYPOOL, 464COPYTOCOPY, 444–445, 445, 457correlated reference (correlation predicate), 304correlated subquery, 305–307, 306, 307correlation names, 269–270, 269CORRELATION_NAME, 753tcost tables, 790, 791–792tCOUNT, 279, 279, 280, 280, 300couple data sets, 490coupling, v, vi, 4Coupling Facility (CF), 487–492, 488, 513, 514

Automatic Restart Manager (ARM) in, 492couple data sets in, 490Coupling Facility Control Code (CFCC) in, 487Coupling Facility Resource Management (CFRM) in, 490,

490–491, 501Cross System Coupling Facility (XCF) and, 490, 493–494,

493, 499failure of, 511group buffer pools (GBPs, cache structures) in, 489–490Internal Coupling Facilities (ICFs) in, 487–488Internal Resource Lock Manager (IRLM) in, 489IXCL1DSU utility for, 490list structure (SCA) in, 488–489, 509, 511, 512lock structure in, 489Parallel Sysplex and, 490, 491Shared Communication Area (SCA) in, 488–489, 509, 511,

512structures and policies in, 490Sysplex Failure Management (SFM) in, 491, 492

Coupling Facility Control Code (CFCC), 487Coupling Facility Resource Management (CFRM), 490,

490–491, 501C#, DB2 Connect and, 25CPU usage, 35

DB2 for z/OS, 5parallelism in, 827–830wait time in, vi

CREAT AUXILIARY TABLE, 148CREATALIAS, 112tCREATE, 107, 108, 117t, 128t, 129, 135t, 136, 152, 153, 153,

474binding/rebinding and, 572coordinating updates to distributed data with, 653ownership and, 125–127sequence objects and, 599, 600

950 Index

user-defi ned data types and, 686CREATE DATABASE, 171, 171, 235, 235CREATE FUNCTION, 689–691, 689, 691, 693, 693, 722CREATE GLOBAL TEMPORARY TABLE, 148CREATE INDEX, 366, 453, 456CREATE LIKE, 188, 189CREATE PERMISSION, 103, 103CREATE PROCEDURE, 617–620, 618, 619, 624–627, 625,

626 631, 631, 633, 722COMMIT ON RETURN in, 623–624, 625

CREATE SEQUENCE, 192, 192CREATE STOGROUP, 236, 236CREATE TABLE, 148, 150, 184–185, 184–186, 709, 709

clone tables and, 215column defi nition using, 245–246, 246constraints and, 178copying table defi nitions for, using LIKE, 188, 189, 464,

590encoding scheme setting with, 164fullselects and, 256–257hash tables, ORGANIZED BY HASH and, 220–221, 221Materialized Query Tables (MQTs) and, 213–214, 214null values and NOT NULL with, 170permutation and, 260primary key constraints with, 180, 180projection and, 260system-period temporal tables, 218, 218–219traces and, 838VARCHAR and, 161

CREATE TABLESPACE, 163, 201, 203–209, 366, 734CREATE TRIGGER, 669–671, 671CREATE TYPE, 686–687, 686CREATE VIEW, 135t, 210, 211

fullselects and, 256–257table expressions and, 315–316

CREATE_SECURE_OBJECT, 112tCREATEALIAS, 116t, 119tCreated Temporary Tables (CTTs), 587–588, 587CREATEDBA, 112t, 116t, 119tCREATEDBC, 112t, 116t, 119tCREATEDTS, 137tCREATEIN, 111t, 113t, 116t, 119t, 120tCREATESG, 112t, 116tCREATETAB, 111t, 121tCREATETMTAB, 112t, 116t, 119tCREATETS, 111t, 119t, 121tCREATOR, 749tCRESTART, 475, 475CRM. See Customer Relationship Management (CRM)Cross System Coupling Facility (XCF), 490, 493–494, 493,

499, 830Cross System Extended Service (XES), 492, 493cross-invalidation, 499, 500CTHREAD, 56tCTREF, 755tCURRENT DATA, 737CURRENT EXPLAIN MODE register, 747

CURRENT PACKAGE PATH, 559CURRENT PACKAGESET, 559, 559CURRENT PATH, 572, 627, 685–686CURRENT RULES, 659CURRENT SCHEMA, 686CURRENT SERVER, 562t, 652, 654CURRENT SQLID, 572CURRENTDATA, 562t, 652, 653, 678, 728–729, 729tCURRENTLY COMMITTED, 721–722, 737Cursor Tables (CTs), 87CURSOR WITH HOLD, 541–542, 542, 552, 554cursors, 552

claims and, 731, 731tDESCRIBE CURSOR in, 623, 623dynamic, 554dynamic scrollable, 545, 545FETCH in, 543–545inactive threads and, 657LOAD and, 370–371, 371locks and, stability of, 725–726, 727multi-row FETCH and, 594, 594parallelism and, 830rowset, 545–546, 546SCROLL in, scrollable/nonscrollable cursors and, 542–545,

543, 544, 544t, 545SELECT and, 536–538, 536, 537, 538sensitivity in, INSENSITIVE/SENSITIVE, 543–544, 543,

544tsequence objects and, 601, 601, 602in SQL, 542–546temporary tables and, 587

Customer Information Control System (CICS), 4, 6, 37, 97, 242access control and, 99address spaces and, 38attachment/attachment facility, 41, 42, 42binding/rebinding and, 558commands for attachment facility in, 65CURSOR WITH HOLD in, 541–542, 542data sharing and, 507DB2 Connect and, 24DB2 and, relationship between, 42distributed data access and, 653, 655, 656interfacing with DB2 through, 47locks and, 727, 728performance and, 852, 853security in, 99stored procedures and, 615traces and, 834

Customer Relationship Management (CRM), 2–4, , 16, 23, 242CYCLE, 176t

DDAS, 8DASD

data sharing and, 485, 510REORG and, 390utilities and, 72

Index 951

data access. See access controlDATA CAPTURE CHANGE, 83data change tables, 316–317, 317Data Communications Resource Manager (DCRM), 41data concurrency. See concurrencydata conditioning, 668Data Control Language (DCL), 145

catalog and, 74Data Defi nition Language (DDL), ii, 28, 145, 152, 255

catalog and, 74locks and, 716stored procedures and, 634

Data Facility Storage Management System (DFSMS), 450data integrity, 668data maintenance, iv, 355–435. See also CHECK; LOAD;

REORG; RUNSTATS; UNLOADCHECK for, 411–414DIAGNOSE for, 418, 418MODIFY for, 414–417MODIFY RECOVERY for, 414–416, 415MODIFY STATISTICS for, 414, 416–417, 417REBIND for, 411REORG for, 411REPAIR for, 417–418restrictions on starting table space or index in, 430, 430restrictive and advisory status in, 424–426, 424, 425, 426,

426–429tRUNSTATS for, 411utilities for, 419–424

Data Manager, 40Data Manager Threshold (DMTH), 863Data Manipulation Language (DML), 145, 255–354data mining, DB2 Connect and, 23data model, ivData Partitioned Secondary Indexes (DPSI), 229–231, 230, 231Data Propagator Relational Capture, 7Data Protection for Snapshot Devices, 9Data Server Clients, 19–22, 19Data Server Drivers, 19

for JDBC and SQLJ, 20, 21for ODBC and CLI, 20–22

Data Server Driver Package, 20–22Data Server Runtime Client, 19–22data sets

coupled, in data sharing, 490data sharing and, 494DSMAX and, 872–873image copies and, 447OPEN/CLOSE, 872–873performance and, 872–873protection of, 100recovery and, 437security and, 100

data sharing, v, vi, 485–516, 486. See also distributed dataaffi nity processing for, 506ALTER/DISPLAY GROUPBUFFERPOOL in, 500–501,

501

application analysis before beginning, 504Automatic Restart Manager (ARM) in, 492benefi ts of, 486–487, 514castout process in, 501–502, 502catalog merging in, 505CICS and, 507coherency control in, 496communicating with group in, 508concurrency control in, 496couple data sets in, 490Coupling Facility (CF) in, 487–492, 488, 511, 513, 514Coupling Facility Control Code (CFCC) in, 487Coupling Facility Resource Management (CFRM) in, 490,

490–491, 501Cross System Coupling Facility (XCF) and, 490, 493–494,

493Cross System Extended Service (XES) in, 492, 493, 499cross-invalidation and, 499, 500current environment evaluation before beginning, 504–505DASD and, 485, 510DB2 for z/OS and, 5DB2 failure and, 511deciding what to share in, 495Distributed Data Facility (DDF) in, 485DRDA and, 509duplexing of structures in, 512, 516environment for, 485, 486, 504–505Explicit Hierarchical Locking (EHL) in, 496–497, 496group attachment in, 507group buffer pool–dependent data in, 495–496group buffer pools (GBPs, cache structures) in, 489–490,

499–502, 500, 512, 513group XCF services in, 493groups for, 485groups for, communicating with, 646image copies and, 516IMS and, 507integrity of data in, 495–502. See also integrity of shared

data; locks and lockinginter-DB2 read/write (R/W) interest and, 495–496Internal Coupling Facilities (ICFs) in, 487–488Internal Resource Lock Manager (IRLM) in, 489IXCL1DSU utility for, 490links in, 492list structure (SCA) in, 488–489, 509, 511, 512lock structure in, 489LOCK/UNLOCK and, 502locks and locking in, 496–499, 511, 516. See also locks and

lockingLog Record Sequence Number (LRSN) in, 509–510, 510logging and, 509–510, 510members in, 485, 513migration issues in, 505–506moving to, 503–506naming conventions and, 505–506non-shared objects in, 494–495, 495Parallel Sysplex and, 485, 490

952 Index

performance and, 502–503processing costs of, 503recovery and, 471, 480, 509, 515restarting DB2 and, 476savepoints and, CONNECT statement for, 586Shared Communication Area (SCA) in, 488–489, 509, 511,

512Shared Data Architecture (SDA) and, 487shared data in, 494–495signaling XCF services in, 493–494START DATABASE in, 512status monitoring XCF services in, 494structure failure in, 511structures and policies in, 490subgroup attachment in, 507submitting work for, 507–508subsystem members in, 485Sysplex Failure Management (SFM) in, 491, 492Sysplex query parallelism and, 508–509, 508Sysplex Timer in, 492, 493utilities use in, 507–508virtual buffer pools and, 515workload management in, 506

data space managers, 40Data Space Support for VSE/VM, 7, 8data structures, 146–152Data Studio. See Optim Data StudioData System Control Facility (DSCF), 39data types, iii, 684, 685, 712

approximate, 156tASCII and, 164, 164BIGINT, 157, 158, 172t, 174BINARY, 159, 162binary large object (BLOB), 159, 163, 172t. See also large

objects (LOBs)binary, 156tbinding/rebinding and, 568built-in vs. user-defi ned, 155, 156tCACHE option and, 174CCSIDs and, 162, 164CHAR, 159, 160, 171, 172tcharacter large object (CLOB) 159, 161, 163, 171, 172t.

See also large objects (LOBs)character, 156tchoosing, 171–172, 172tCODESET default for, 171constants as, 688CREATE for, 153DATE, 165date and time, 156t, 165–168DATE, 166–167, 167tDBCLOB, 159, 162, 171, 172tDECFLOAT, 156t, 157, 158–159, 172tdecimal fl oating point. See DECFLOATDECIMAL, 172t, 172DECIMAL/DEC/NUMERIC, 157, 158, 174distinct types and, 169

DOUBLE, 172tDOUBLE/FLOAT, 157, 158double-byte character large object. See DBCLOBdouble-byte character. See GRAPHICdouble-precision fl oating point. See DOUBLE/FLOATEBCDIC and, 164, 164encoding schemes and, for string types, 163–164exact, 156tfi xed-length binary string. See BINARYfi xed-length character. See CHARFLOAT, 172tGRAPHIC, 156t, 159, 162, 171identity columns and, 172–176, 173, 174, 175–176tINSERT and, 287INTEGER, 157–158, 172t, 174large objects (LOBs) and, 163, 703–704LOAD and, 366LONG VARCHAR, 160, 171LONG VARGRAPHIC, 160, 171null values and, 170, 170NUMERIC, 156t, 157–159, 172townership of, 127REAL, 157, 158, 172trow begin/end timestamps and, 177row change timestamps and, 177, 177row identifi er (ROWID), 156t, 168–169signed numeric, 156tsingle-precision fl oating point. See REALSMALLINT, 157, 172t, 174string, 156t, 159–164strong typing and, 170TIME, 156t, 165, 167–168, 167TIMESTAMP, 156t, 165, 166, 168TIMESTAMP WITH/WITHOUT TIME ZONE, 165transaction start ID columns and, 177–178Unicode and, 164, 171user-defi ned, 169–170, user-defi ned, 686–689. See also

user-defi ned data typesVARBINARY, 159, 162, 172tVARCHAR, 159, 160–161, 171, 172tVARGRAPHIC, 159, 160, 171varying-length binary string. See VARBINARYvarying-length character. See VARCHARXML, 156t, 172t, 169, 707–711. See also XML

data validation, 668data warehousing, 12, 15–16, 23DATAACCESS, 115, 122tDatabase Access Threads (DBATs), vi, 658, 662Database Administrators (DBA), 7, 29database copy pool, 457–458, 457Database Descriptor (DBD), 198, 716Database Exception Table (DBET), 418Database Management Systems (DBMS)

local DB2 vs. application servers in, 83Database Request Module (DBRM), 746

binding/rebinding and, 555, 557, 558, 560, 566, 568distributed data access and, 647

Index 953

user-defi ned functions (UDFs) and, 690Database Services Address Space (DSAS), 38, 40databases, ii, iii

accessing data in, 145aliases in, 148ALTER/ALTER DATABASE and, 152, 154–155, 235,

235, 235anomalies in, 242, 243tattributes of, 238–239batch performance monitors for, 831–833bottom-up vs. top-down approach to design of, 238clone tables in, 151, 215–216columns in, 145communications in, in distributed data, 85–86concurrency and, designing for, 736–737, 737CREATE/CREATE DATABASE for, 152, 153, 153, 235,

235Data Control Language (DCL) in, 145Data Defi nition Language (DDL) in, 145, 152Data Manipulation Language (DML) in, 145data retrieval in, 256–287. See also SELECT data sharing in. See data sharingdata structures in, 146–152data types in, 155–170. See also data typesDECLARE in, 152, 154, 154deleting data from, 371–374. See also UNLOADdesign of, 237–243, 237DROP/DROP DATABASE for, 152, 154, 154, 236,

236entities in, 238fi rst normal form (1NF) in, 240, 240foreign keys in, 248–249functions in, 275–277grouping values in, 277–278hash spaces/tables in, 150, 220–221hierarchy of structures in, 146, 147history tables in, 152identity columns in, 172–176, 173, 174, 175–176timplementation of, 243–249indexes/index spaces in, 149, 150, 222–235inserting data in, using INSERT, 287–290, 287–290. See

also INSERTinserting large amounts of data into, using LOAD,

355–371. See also LOADkeys in, 149, 246logical design in, 237–242logical design of, transforming to physical, 243maintaining data in, 355–435. See also CHECK; data

maintenance; LOAD; REORG; RUNSTATS; UNLOAD, 355

Materialized Query Tables (MQTs) in, 151, 213–215merging data in, using MERGE, 292normal forms in, 239–241, 240, 241normalization of, 239–241, 240, 241null values in, 170, 170object management in, 152–155objects in, 145–253. See also specifi c objects

OMEGAMON DB2 Performance Monitor for, 831, 832, 833

online performance monitors for, 831–833Optim Database Administrator for, 27–28ownership of, and privileges, 128tperformance monitoring for, 830–834physical design of, 243physical organization of data in, 374primary key defi nition in, 247, 247privileges for, 111trelationships in, 238removing data from, using DELETE, 293–295, 294–295.

See also DELETEREORG to move/remove data from, 374–397. See also

REORGresource limit facility (governor) for, 834retrieving and manipulating objects in, 255–354. See also

specifi c objects; SQLrows in, 145sample of, 878–891schemas in, 151second normal form (2NF) in, 240–241, 241SELECT statement in, 256–287. See also SELECT sequence objects in, 152, 191–193SQL and, 145. See also SQLstatistics on data in, 397–411. See also RUNSTATS;

statisticsstorage groups in, 151, 236–237, 236–237synonyms in, 148table spaces in, 149–150, 193–210. See also table spacestables in, 147–148, 178–191, 244, 256. See also tablestemporal tables in, 151–152, 216–220third normal form (3NF) in, 241, 241Tivoli OMEGAMON XE for, 831, 832, 833Unicode support and, 171unique key defi nition in, 248updating records in, using UPDATE, 290–292, 291, 292.

See also UPDATEviews in, 148, 210–213, 256, 295–297, 296, 297. See also

viewsDATE, 56t, 156t, 165, 166–167, 167t, 190

binding/rebinding and, 568date and time data types, 156t, 165–168DATELEN, 56tDAY, 167DAYOF WEEK, 167DAYOFMONTH, 167DAYOFWEEK_ISO, 167DAYOFYEAR, 167DAYS, 167DB2, overview of, 1–36DB2 10 for z/OS Database Administration (exam 612), i,

iii–vi, 1DB2 Advanced Enterprise Server Edition, 4, 17–18, 242. See

also DB2 for Linux, UNIX, and Windows (LUW)DB2 application drivers, 20–21DB2 command, 66t

954 Index

DB2 Connect, 19, 22–25, 32, 659–661distributed data access and, 646idle agent pool and, 660target database in, 85

DB2 Control Center, 30, 32–35, 70, 685DB2 Enterprise Server Edition, 4, 242DB2 Enterprise, 15–16. See also DB2 for Linux, UNIX, and

Windows (LUW)DB2 Everyplace, 4, 18–19. See also DB2 for Linux, UNIX, and

Windows (LUW)DB2 Express, 4, 13–14. See also DB2 for Linux, UNIX, and

Windows (LUW)DB2 Express-C, 4, 14DB2 Extenders. See Extenders, DB2DB2 failure, data sharing and, 511DB2 for i, 7DB2 for Linux, UNIX, Windows, 3, 8–19, 340–341DB2 for VSE/VM, 3, 7–8DB2 for z/OS, 3, 4–6, 36, 38–46DB2 Governor, 12DB2 inactive connection support, 658DB2 Information Center, 30DB2 instance merge modulesDB2 Interactive (DB2I) interface, 47–48, 48

BIND/REBIND/FREE and, 47DB2 commands and, 47DB2 programming and, 47DCLGEN and, 47defaults of, 47defaults of, 48main menu of, 48precompiling with, 47SPUFI and, 47SQL and, 47utilities and, 47utility execution using, 69, 69

DB2 Personal, 4, 12–13 See also DB2 for Linux, UNIX, and Windows (LUW)

DB2 private protocol, distributed data access and, 643DB2 Technical Library, 7DB2 Workgroup Server Edition, 4, 242DB2 Workgroup, 15. See also DB2 for Linux, UNIX, and

Windows (LUW)DB2_SECURE_VAR security function, 132, 132DB2SORT, 57tDB2START, 139tDB2SUPLD, 57tDBA. See Database AdministratorDBACRV2, 56tDBADM, 115, 119–120t, 121t, 128t, 125, 129, 293

managing and separating authorities for, 123performing tasks on behalf of others and, 108–109, 109row and column access control in, 105

DBADMIN, 139t, 140tDBCLOB, 159, 162, 171, 172tDBCTRL, 115, 121t, 128tDBD01 directory table, 83t

DBETE, 427tdbextents, 8DBINFO, 620–621, 620–621tDBM1, 40DBMAINT, 115, 121tDBNAME, 139tDBPROTOCOL, 562tDBRM. See Database Request Module (DBRM)DCLGEN, 47, 66t, 519, 524–526, 524, 525–526

binding/rebinding and, 556DDF. See Distributed Data Facility (DDF)DDL. See Data Defi nition Language (DDL)DEADLINE, REORG and, 386DEADLOCK, 730t, 732–734deadlocks, vi, 586, 732–734DEALLCT, 57tDEALLOCATE, 725debugging

ALLOW DEBUG MODE and, 628stored procedures and, 635Unifi ed Debugger for, 635

DEBUGSESSION, 112t, 117t, 122t, 144DECARTH, 57tDECDIV3, 57tDECFLOAT, 156t, 157, 158–159, 172t, 179, 228, 346DECIMAL/DEC/NUMERIC, 57t, 157, 158, 172t, 174decimal fl oating point. See DECFLOATDecision Support Systems (DSS), 38declarative referential integrity, 249DECLARE, 152, 154, 154, 518–528, 519, 588–591, 589,

591DECLARE CURSOR, 257, 536, 536DECLARE GLOBAL TEMPORARY TABLE, 256–257DECLARE TABLE, 556Declared Temporary Tables (DTTs), 588–591, 589decomposition, 341DECPT, 362, 372–373DEF_DECFLAOT_ROUND_MODE, 57tDEFAULT, INSERT and, 288, 289DEFER, 576, 562tdeferred embedded SQL, 546–547Deferred Write Threshold (DWQT), 860–861, 875, 877DEFINE, 204, 222DEFINEBIND, 563tDEFINERUN, 563tDEFLANG, 57tDEFLTID, 57tDEGREE, 562tDEL_CRSTRUCTS_ON_RESTART, 57tDELAY, 385, 386DELETE, ii, 110t, 116t, 117t, 118t, 120t, 121t, 122t, 128t, 135t,

192, 255, 293–295, 294–295, 474, 553, 611, 713access paths and, 758, 761all rows in table, using TRUNCATE, 294, 295check constraints and, 182–183coordinating updates to distributed data with, 653data change tables and, 316–317, 317

Index 955

DROP commands and, 293DSNTIAUL vs., 371EXPLAIN and, 746foreign key constraints and, 181–182indexes and, 149LOAD/REPLACE and, 293locks and, 718, 720, 721, 724mass, 293mass delete and table spaces, 201multi-row FETCH in, 594, 594ODBC and, 549positioned, 554positioned delete and, 293, 540, 540, 543referential constraints and, 180resource limit facility (governor) and, 834scalar-fullselect and, 257searched delete and, 293–294, 294SELECT and, 294temporal tables and, 607temporary tables and, 587, 590triggers and, 667, 668TRUNCATE and, 293, 294, 295UNLOAD vs., 371–374views and, 148, 211, 295, 296WHERE clause and, 294

DELIM, 57tdelimited LOAD (using COLDEL, CHARDEL, DECPT), 362,

362delimited UNLOAD (COLDEL, CHARDEL, DECPT options),

372–373denial of service attacks, 100denormalization vs. joins, 331–332dependent tables, 179, 190DEPLOY, 562tderived columns, 273–274, 274DESCRIBE CURSOR, 623, 623DESCRIBE PROCEDURE, 622–623, 623DESCSTAT, 57tDesign Advisor, 33, 36designing databases, 237–243, 237detailed cost tables, 790, 791–792tdetailed performance monitoring, 853–854deterministic user-defi ned functions (UDFs), 697Developer Workbench, Optim Development Studio and, 28–29DFSORT, 375, 379, 382DIAGNOSE, 418, 418, 434dimension tables, 765direct row access, 168, 761directory, v, 74, 82, 82–83t, 92

ALTER TABLE and, 190copying, 453–454DATA CAPTURE CHANGE and, 83data sharing and, 494recovery and, 467REORG and, 395–396SYSPLAN and SYSPACKAGE to reorganize, 396

DISABLE, 766

DISABSCL, 57tdisaster recovery, 468–471, 483, 484. See also recovery and

restartarchive logs/ARCHIVE LOG in, 470, 471COPY in, 469data sharing environments and, 471DSNTIJUZ in, 470ICF catalog in, 470image copies in, 469items needed for, 469–470libraries in, 470LISTCAT in, 470minimizing data loss through, 471objects to be copied for, 468preparing for, 468RECOVERYDDN and RECOVERYSITE in, 469SYSCOPY in, 469table spaces offl ine in, 471

DISCARD phaseLOAD and, 356REORG and, 387–388, 388

DISCONNECT, 562t, 651, 652, 659DISPLAY, iv, 112t, 118t, 119t, 663, 855DISPLAY ARCHIVE, 67t, 854DISPLAY BUFFERPOOL, 67t, 854, 865, 865DISPLAY DATABASE LOCKS, 739, 739, 743DISPLAY DATABASE, 67t, 424–426, 424, 425, 426,

426–429t, 854DISPLAY DDF, 67t, 645–646, 645, 647, 664, 854DISPLAY FUNCTION SPECIFIC, 67t, 702, 702, 854DISPLAY GROUP, 52–53, 53, 67t, 854DISPLAY GROUPBUFFERPOOL, 67t, 500, 854DISPLAY LOCATION, 67t, 854DISPLAY LOG, 67t, 440, 854DISPLAY PROCEDURE, 67t, 628, 628, 854DISPLAY PROFILE, 67tDISPLAY RLIMIT, 67t, 854DISPLAY THREAD, 67t, 479–480, 480, 628, 658, 658, 854DISPLAY TRACE, 67t, 136, 136, 839, 854DISPLAY UTILITY, 67t, 73, 73, 423–424, 424, 854DISPLAYDB, 111t, 119t, 121tDIST, 97DISTINCT, 257, 279–280, 280, 301, 824

access paths and, 759, 762IS DISTINCT FROM/IS NOT DISTINCT FROM, 284,

285views and, 212, 295, 296

distinct types, 169ownership of, and privileges, 113t, 127, 128t

distributed data, 83–86, 641–665. See also data sharingACCUMACC and ACCUMID in, 658Advanced Program-to-Program Communications (APPC)

and, 646aliases in, 646–647application design options for, 655–656application requesters in, 84, 641application servers and, 83, 641

956 Index

batch processing and, 653–654BIND options for, 651BIND PACKAGE and, 651–652BIND PLAN and, 652–653BSDS and, 646CALL and, 659CICS and, 653, 655, 656CMTSTAT and, 658, 664coding methods for, 647–654COMMIT and, 659Communications Database (CDB) and, 85–86, 641,

644–646, 663communications protocols for, 84–85, 643–644confi guration settings for connection pooling in, 660–661CONNECT and, 84, 642–643, 649–651, 649connection pooling for, 659–661CONNECTIONS catalog and, 665coordinating updates in, 653–654CURRENT RULES and, 659CURRENTSERVER and, 654data sharing groups and, communicating with, 646Database Access Threads (DBATs) and, 658, 662Database Management Systems (DBMSs) and, 83Database Request Module (DBRMs) and, 647DB2 Connect and, 646, 659–661DB2 inactive connection support and, 658DB2 private protocol and, 643DISCONNECT and, 651, 659DISPLAY and, 663DISPLAY DDF and, 664DISPLAY THREADS in, 658, 658Distributed Data Facility (DDF) and, 641distributed request in, 642, 664distributed unit of work (DUW) in, 642DRDA and, 84, 642–643, 645, 648, 651–653, 655–657, 662DSNJU003 (Change Log Inventory) and, 646, 647DSNJU004 (Print Log Map) and, 647DSNTIP5 and, 645DSNTIPR and, 645DSNTP2DP and, 643FETCH and, 662, 664gathering confi guration information for, using DISPLAY

DDF, 645–646, 645, 647idle agent pool and, 660IMS and, 653, 656inactive threads and, 657INSERT and, 648, 649, 650IPLIST and, 641, 644, 665IPNAMES and, 641, 644, 645, 665LINKNAME in, 86, 644, 645local DB2 and, 641location name and, 85LOCATION and, 641, 644, 645, 665Logical Unit name (LU name) and, 85LULIST and, 644, 665LUMODES and, 644, 665LUNAME in, 86, 644, 645, 665

member-specifi c routing in, 646–647, 647MODESELECT and, 644, 665MODIFY DDF and, 647naming conventions for, 641non–DB2 relational database names (RDBNAM), 85OPTIMIZE FOR n ROWS in, 656–657, 659, 662, 664pooled threads and, 658precompiler options for, 651programming considerations when using, 654–655releasing connections in, using RELEASE, 650–651, 659remote query performance in, 656–661remote requests in, 642remote servers and, 83, 641remote unit of work (RUW) in, 642restricted systems and, 653REXX and, 643SET CONNECTION and, 654SQL and, 84, 641SQL(xxx) and, 651, 655SQLERROR and, 655SQLRULES and, 654stored procedures and, 655–656System Management Facility (SMF) and, 658System Network Architecture (SNA) and, 84–86, 643, 646target database name in, 85TCP/IP and, 84–86, 641, 643, 644–646temporary tables and, accessing, 649, 649three-part names in, 647–649TSO and, 653–654tuning applications for, 659two-phase commit and, 653–654USERNAMES and, 644, 665Virtual IP Address (VIPA) and, 646Virtual Terminal Access Method (VTAM) and, 85, 86, 643,

644, 645Workload Manager (WLM) and, 655, 656

Distributed Data Facility (DDF), vi, 57t, 97, 485, 641address space for, 38, 40–41data sharing and, 485DISPLAY DDF for, 647, 645–646, 645, 664global transactions and, 586MODIFY DDF and, 647multi-row FETCH in, 594

Distributed Data Interchange Services (DDIS), 41Distributed Relational Database Architecture (DRDA), 27, 84,

85, 164data sharing and, 509distributed data access and, 84, 642–643, 645, 648,

651–653, 655, 656–657, 662result sets and, 621stored procedures and, 621

Distributed Relational Data System Manager (DRDS), 41distributed requests, 642, 664distributed threads, 639Distributed Transaction Manager (DTM), 40, 41Distributed Unit of Work (DUW), 642DLDFREQ, 57t

Index 957

DLITOUT, 57tDM component, predicates, 818DML. See Data Manipulation Language (DML)Document Type Defi nition (DTD), 708Domino, 18DOUBLE/FLOAT, 157, 158, 172tdouble-byte character large object (DBCLOB). See large

objects (LOBs)double-byte character. See GRAPHICdouble-precision fl oating point. See DOUBLE/FLOATDPSEGSZ, 57tDragonball, 19drains, locks and, 732DRDA. See Distributed Relational Database Architecture

(DRDA)DROP, 108, 111t, 117t, 119t, 121t, 128t, 129, 135t, 152, 154,

154, 474binding/rebinding and, 572coordinating updates to distributed data with, 653sequence objects and, 600

DROP DATABASE, 236, 236, 293DROP INDEX, 234, 234, 251DROP PACKAGE, 571, 571DROP PROCEDURE, 627, 627DROP SEQUENCE, 193DROP STOGROUP, 236, 237DROP TABLE, 191, 194, 293, 376DROP TABLESPACE, 210, 210, 293DROP TRIGGER, 678, 684DROP VIEW, 213, 213DROPIN, 116t, 120tDRPIN, 113tDSCVI, 57tDSHARE, 57tDSMAX, 57t, 872–873DSN commands, 65–66, 66tDSN_COLDIST_TABLE, 802, 802–803tDSN_FILTER_TABLE, 789, 789–790tDSN_FUNCTION_TABLE, 773, 773–775tDSN_GROUP_TABLE, 784, 784–786tDSN_KEYTGTDIST_TABLE, 803, 804–805tDSN_PTASK_TABLE, 787, 787–789tDSN_QUERY_TABLE, 800, 800–801tDSN_SORTKEY_TABLE, 795, 795–796tDSN_STATEMENT_CACHE_TABLE, 775, 775–778t, 827DSN_STATEMENT_TABLE, 770, 771–773tDSN_STRUCT_TABLE, 782, 782–784tDSN_USERQUERY_TABLE, 808DSN_VIEWREF_TABLE, 798, 798–799tDSN1CHKR, 74, 93, 134, 420DSN1COPY, 74, 185, 371, 484DSN1DBM1, 40DSN1DIST, 41DSN1LOGP, 422DSN1MSTR, 39DSN1PRNT, 134, 423DSN1SDMP (IFC Selective Dump), 423

DSNACCOR, 410DSNACCOR(X), vDSNACCOX, 636, 410DSNACICS, 636DSNAEXP, 636, 747DSNAIMS, 636DSNALI, 45, 46DSNCLI, 46DSNDB01, 88DSNDB01SYSUTILX, 467DSNELI, 43, 46DSNJLOGF (Preformat Active Log), 419DSNJU003 (Change Log Inventory), 419, 440, 441, 465, 475,

646, 647, 646DSNJU004 (Print Log Map), 419–420, 441, 470–471, 647DSNRLI, 46, 629DSNTEP2, 82DSNTIAUL, 371DSNTIJUZ, 470DSNTIP5, 645DSNTIPP1, 122DSNTIPR, 645DSNTP2DP, 643DSNTPSMP, 634, 635DSNU, 70DSNULI, 46DSNUTILB, 70DSNUTILU, 70–71, 71DSNZPARMs, vi, 37, 53–54, 54–65t, 89, 93

locks and, 730, 741, 743online, 53–54SET SYSPARM for, 53–54, 54

DSQLDELI, 57tDSSIZE, 57t, 203, 205–206, 205t, 209, 221DSSTIME, 57tDSVCI, 856DUMP, FROM DUMP, 466DUMPCLASS, 457–458dumps, DSN1SDMP (IFC Selective Dump), 423duplexing structures, data sharing and, 512, 516dynamic cursors, 554dynamic logical partitioning, 7dynamic prefetch, 760dynamic scrollable cursors, 545, 545dynamic SQL, vi, 517, 546–549, 552, 576, 825–827, 870–871,

870, 871caching in, 826–827, 827, 870–871, 870, 871concentration of literals in, 548data sharing and, 494ODBC/JDBC execution of, 547

dynamic statement cache, 826–827, 827DYNAMICRULES, 563t, 572, 581DYNRULS, 57t

EEBCDIC, 164, 164, 171, 252, 268, 273, 366Eclipse, 25–26

958 Index

EDM. See Environmental Descriptor Manager (EDM)EDM_SKELETON_POOL, 57t, 87EDMDBDC, 57tEDMPOOL, 87EDMSTMTC, 57t, 87EDPROP, 57telectronic commerce/e-commerce, 2, 3embedded dynamic SQL, 546

DB2 Connect and, 25IMS and, 43

EMPNO fi elds, 172–176EN_PJSJ, 58tENABLE, 766Enable New Function Mode (ENFM), 51, 52, 420ENABLE QUERY OPTIMIZATION, 214–215ENABLE/DISABLE, 563tEncina, 24encoding schemes (EBCDIC, ASCII, and Unicode), iv, 242,

253, 273joins and, 268SELECT and, 273string data types, 163–164

ENCODING, 563tencryption, ii, 100

DB2 for z/OS, 6InfoSphere Guardium Data Encryption, 6Integrated Cryptographic Services Facility (ICSF) and, 100

ENCRYPTION, 108tend of log recovery, 438END, 66tEND-EXEC, 518, 518ENFORCE, 356, 435ENFORCE CONSTRAINTS, 365ENFORCE NO, 364, 364ENSCHEME, 58tEnterprise Edition, DB2 Connect, 23, 24Enterprise Resource Planning (ERP), 2–4, 6, 16, 23, 242Enterprise Server Edition, 4, 242Enterprise, DB2, 15–16. See also DB2 for Linux, UNIX, and

Windows (LUW)entities, in databases, 238environment, 37–94

access control and, 97address spaces in, 38–41, 39attachments/attachment facilities in, 41–46catalog in, 74–82, 74–81t, 82commands in, 65–68, 67–68tconversion and, 50Data Facility Storage Management Subsystem (DFSMS)

and, 47directory in, 74distributed data in, 83–86installation and migration in, 50–65, 53, 54–65interfaces with DB2 and, 47–50Parallel Sysplex support and, 46Resource Access Control Facility (RACF) and, 46, 132security and, 46

utilities in, 69–73Environmental Descriptor Manager (EDM) pool, v, 87–88,

869–871binding/rebinding and, 558data sharing and, 494DSNZPARMs and, 53

equal-to operator, 261equal unique index, 824ERP. See Enterprise Resource Planning (ERP)error messages, verror pages, recovery and, 456escalation of locks, 734–735EUR date/time format, 167t, 168tEVALUNC, 58t, 730tEveryplace, DB2, 4, 18–19, 242. See also DB2 for Linux,

UNIX, and Windows (LUW)exact data type, 156texams for IBM DB2 10 for z/OS database administrator

certifi cation, i–viEXCEPT, 257, 354exception performance monitoring, 854exception tables, 464excepts/EXCEPT/EXCEPT ALL, 311, 312EXCHANGE, 151exclusive (X) locks, 722, 724, 728EXEC SQL, 518, 518, 556EXECUTE, 110t, 117t, 120t, 122t, 128t, 138t, 140t, 144, 520,

546, 549, 571–572, 689EXECUTE ON FUNCTION, 113tEXECUTE ON PROCEDURE, 113texecuting SQL statements, 538–546execution environments, for stored procedures, 628–632execution validation, SQL, 528–535existence checking, FETCH for, 592, 593EXISTS, 304–307, 307exit routines, authorization control, 98EXPLAIN, vi, 112t, 117t, 118t, 119t, 563t, 569, 678, 745–756,

876, 877access plans and, 746binding and, 747cache tables in, 746–747checking application access with, 807–808cost of execution in, 746CURRENT EXPLAIN MODE register for, 747DELETE and, 746DSN_COLDIST_TABLE and, 802, 802–803tDSN_DETCOST_TABLE and, 790, 791–792tDSN_FILTER_TABLE, 789, 789–790tDSN_FUNCTION_TABLE and, 773, 773–775tDSN_GROUP_TABLE, 784, 784–786tDSN_KEYTGTDIST_TABLE and, 803, 804–805tDSN_PGRANGE_TABLE and, 797, 797–798tDSN_PREDICATE_TABLE and, 778, 778–781tDSN_PTASK_TABLE, 787, 787–789tDSN_QUERY_TABLE and, 800, 800–801tDSN_SORT_TABLE and, 793, 793–794tDSN_STATEMENT_CACHE_TABLE and, 775, 775–778t

Index 959

DSN_STATEMENT_TABLE and, 770, 771–773tDSN_STRUCT_TABLE and, 782, 782–784tDSN_VIEWREF_TABLE and, 798, 798–799tDSNAEXP stored procedure for, 747dynamic statement cache and, 826–827, 827EXPLAIN table queries and Optim products in, 805–806fi ltering and, 747function tables in, 746gathering data from, 747INSERT and, 746locks and, 735, 738Optim Data Studio and, 747Optim Development Studio and, 747Optim Query Tuner and, 747optional tables populated by, 770–805output from, 806–808packages and, 746parallel operations and, 747partitioning and, 747plan tables and, 746, 747, 748, 748–756tplans and, 746predicates and, 747Query Management Facility (QMF) and, 747SELECT and, 746sorting and, 747SPUFI and, 747SQL statements and, 746, 747SQL tuning with, 825–827statement table for, 746subqueries and, interpreting access for, 768tables for, 25UPDATE and, 746user-defi ned functions and, 746verifying index use with, 806–807

Explicit Hierarchical Locking (EHL), 496–497, 496explicit privileges, 109–114, 110–113tExpress, DB2, 4, 13–14. See also DB2 for Linux, UNIX, and

Windows (LUW)Express-C, DB2, 4, 14. See also DB2 for Linux, UNIX, and

Windows (LUW)expressions, 315–316, 315, 354

arithmetic, 338AVG, 276–277, 277CASE, 334–335, 334, 335, 350comparison, 338COUNT, 279, 279, 280DISTINCT, 279–280, 280fi lter, 338HAVING clause and, 279, 279indexes and, 228–229LIKE and, 280–281, 280, 281logical, 338MAX, 276–277, 276MIN, 277nested table expressions in, 313–315, 313, 314, 315path, 338primary, 338

row, 301, 336, 336STDDEV, 277SUM, 279, 279VARIANCE, 277XPath, 337–341

EXTENDED INDICATOR, 563tExtended Recovery Facility (XRF), 43Extenders, DB2, ii, 30, 31Extensible Markup Language. See XMLextensions, 684–689EXTENTS, 410EXTERNAL, 617, 634, 639external scalar user-defi ned functions (UDFs). See user-defi ned

functions (UDFs)external SQL stored procedures, 634–635external table functions, 696–697, 696, 697external user-defi ned functions (UDFs). See user-defi ned

functions (UDFs)externalization, I/O, 857–858externalizing statistics, 409EXTRAREQ, 58t, 657EXTRASRV, 58t, 657EXTSEC, 58t

Ffact table, 765failback, 11fallback recovery, 458–459false lock contention, 499Family Fundamentals (exam 610), i–iiiFARINDREF, 395FAROFFPOS, 395Fast Log Apply (FLA), 459–460FASTSWITCH, REORG and, 381–382FCCOPYDDN, 58tfederation

DB2 Connect and, 24, 25DB2 for Linux, UNIX, Windows (LUW) and, 8, 16, 17homogeneous, 8, 13–17

FENCED, 617, 634, 638, 639FETCH, 523, 538–540, 539, 540, 591–592, 591, 592, 610,

710–711cursors and, 543–545distributed data access and, 662, 664existence checking with, 592, 593FETCH FIRST and, 592, 592FIRST, 257, 333, 333, 334large objects (LOBs) and, 706, 710–711locks and, 724, 727multi-row, 593–594, 593, 594OPTIMIZE FOR and, 592, 592sequence objects and, 601, 602streaming data using, 706, 710–711WITH CONTINUE, 336

fi le reference variable, LOBs and, 704–705fi lter expressions, 338fi lter factors, 809–811

960 Index

fi lter tables, 789, 789–790tFILTER, 563tfi ltering, 343–346

EXPLAIN and, 747First-In First-Out (FIFO) queuing, 857fi rst normal form (1NF), 240, 240fi xed-length binary string. See BINARYfi xed-length character. See CHARFLAG, 563t, 678FlashCopy, v

image copies and, 442recovery from a backup by, 462–463UNLOAD and, 371

FLASHCOPY, 58t, 367, 451, 484FLASHCOPY_COPY, 58tFLASHCOPY_LOAD, 58tFLASHCOPY_PPRC, 58tFLASHCOPY_REBUILD_INDEX, 58tFLASHCOPY_REORG_INDEX, 58tFLASHCOPY_REORG_TS, 58tFLOAT, 172tFOR, 634FOR EACH ROW, 674FOR EACH STATEMENT, 674, 675FOR FETCH ONLY, 297FOR READ ONLY, 297FOR SYSTEM TIME, 286, 286FORCE, 474foreign keys, 179, 181, 227, 246, 248–249FORTRAN, 531FREE, 47, 66t, 128t, 571, 571 free space, FREEPAGE/PCTFREE, 203, 206, 222, 366, 375FREEPAGE, 203, 206, 366frequency distribution statistics, RUNSTATS, 400–402, 401t,

401, 402, 402tFREQVAL, 402, 402FROM, 147, 257, 264, 267, 268, 275, 285–286, 286, 301,

696–697, 696, 697joins and, 326subqueries and, 304views and, 212, 295, 296

FROM DUMP, 457–458full copy, 438, 445–447full outer join, 327–331, 327–329fullselects, 256–257, 292function tables, EXPLAIN and, 746functions, ii, 275–277

aggregate. See column functionsALTER for, 155built-in, 275, 689, 689CASE expression in, 335, 335column, 275, 276–277, 276, 277DSN_FUNCTION_TABLE and, 773, 773–775tGROUP BY and, 277–278, 277, 278row. See scalar functionsscalar, 275–276, 275, 276set. See column functions

string, 276table, 275user-defi ned, iv, 275, 689–703. See also user-defi ned

functionsvector. See column functions

fuzzy copy, 449

GGBP, viGBPCACHE, 204, 223GBPOOLT threshold, 502GCCSID, 58tgeneral command processor, 40Generalized Trace Facility (GTF), 834GENERATED, 175t, 176GENERATED ALWAYS, 173, 365–366, 596, 597, 598GENERATED BY DEFAULT, 365–366, 596, 597, 598Generation Data Groups (GDGs), 443GENERIC, 563tGeodetic Extender, 10, 16, 17, 31GET DIAGNOSTICS, 532–535, 532–535t, 546, 554, 595, 634global lock contention, 499Global Lock Manager (GLM), 498global locks, 497–498global optimization, 769global temporary tables, 639global transactions, 586governor, resource limit facility, 834GRANT, 98, 101, 108, 113–114, 113, 114, 115t, 117t, 121t,

129, 133, 134t, 474authorities, 124, 124binding/rebinding and, 572coordinating updates to distributed data with, 653

GRANT ALL, 110tgranularity of statements, 674GRAPHIC, 156t, 159, 162, 171, 190, 568greater-than operator, 261greater-than/equal-to operator, 261GRECP, 427tgroup buffer pool (GBPs), 499–502, 400, 513

ALTER/DISPLAY GROUPBUFFERPOOL in, 500–501, 501

castout process in, 501–502, 502coherency control in, 496concurrency control in, 496Coupling Facility Resource Management (CFRM) in, 501data sharing and, 512dependent data, 495–496, 495Explicit Hierarchical Locking (EHL) in, 496–497, 496inter-DB2 read/write (R/W) interest and, 495–496locks and locking in, 496–499. See also locks and lockingsizing of, 501

GROUP BY, 256, 257, 275, 277–278, 277, 278, 759, 806, 824ORDER BY and, 278views and, 212, 295

group manager, 40group tables, parallel, 784, 784–786t

Index 961

group XCF services, in data sharing, 493GROUP_MEMBER, 753tgrouping values, 277–278, 277, 278. See also GROUP BYgroups, data sharing, 485GRPNAME, 58tGuest Sharing Environment, 8

HHandheld PC, 18hash access, iii, vi, 761hash key, 253hash-organized table spaces, 377hash spaces, 150hash tables, 220–221, 251, 253HAVING, 279, 279, 343, 520

CASE expression in, 334–335, 334, 335views and, 212, 295

Health Center, Control Center, DB2 and, 33health checks, iv, viHealth Monitor, Data Studio, 27held cursor, 542, 542Hierarchical Storage Management (HSM), 464hierarchy of database structures, 146, 147High Availability Disaster Recovery (HADR), 10–11, 13–17HIGH2KEY, 411HIGHVALUE, 403HINT_USED, 754thistogram statistics, 402–403, 811–812, 812historical statistics, 405–406, 406history tables, 148, 152homogeneous federation, 8, 13–17homogeneous Q replication, 11, 16, 17homogeneous replication, 8, 16, 17HONOR_KEEPDICTIONARY, 58thost arrays, 523–524, 523, 524host structures, SQL application programming and, 520, 523,

523host variables, SQL application programming and, 520–521,

521HOUR, 168HP-UX, 2HTML, Net Search Extender and, 8–9, 13–17hybrid joins, 764–765

Ii. See System iI/O, v–vi, 8, 857–858

asynchronous read/write in, 858Immediate Write Threshold (IWTH) and, 858parallelism in, 827–830synchronous reads/writes to, 857–858writes to, 860–861

IBM Developer Kit, 24IBM i, 2IBM_SERVICE_DATA, 753tIBMREQD, 139tICF catalog, 470

ICOPY, 427tIDAUTH_MODULE, 58tIDBACK, 58tidentity columns, iii, 172–176, 173, 174, 175–176t, 596–599,

596GENERATED ALWAYS in, 596, 597GENERATED BY DEFAULT in, 596, 597, 598INSERT in, 597–598, 597OVERRIDING USER VALUE in, 597sequence objects vs., 192, 602–603, 603tUPDATE in, 597–598values in, obtaining with INSERT with SELECT, 598–599,

599IDFORE, 58tidle agent pool, 660IDs, auditing, 135IDTHTOIN, 58tIDXBPOOL, 59tIF, 634IFCID, 826IGNSORTN, 59tIMAGCOPY, 111t, 119t, 121timage copies, 442–454, 469

access during, using SHRLEVEL, 449, 450catalog copying in, 453–454CHANGELIMIT in, 448–449, 448COPY in, 442, 444, 444, 447COPYTOCOPY in, 444–445, 445data sets, 447data sharing and, 516DFSMS concurrent copy and, 450directory copying in, 453–454dual, 443–444FlashCopy in, 442FLASHCOPY option for, 451frequency of, 443full, 445–447fuzzy copy in, 449Generation Data Groups (GDGs) in, 443incremental, 445–447index copies in, 453inline copies and, 449–450LOAD REPLACE and, 449–450, 450MERGECOPY and, 446multiple objects in, 447–448, 448partitions, 447REPORT in, 453REPORTONLY in, 448–449, 448retention periods in, 443SHRLEVEL in, 454SYSCOPY and, 445, 447, 452, 453SYSLGRNX and, 453TABLESPACESET in, 453tape vs. disk, 445TEMPLATE in, 445TRACKMOD in, 446utility mode and, 449

962 Index

Immediate Write Threshold (IWTH), 858, 863–864IMMEDIATE, 563tIMMEDWRI, 59tIMPDSDEF, 59tIMPDSSIZE, 59timplementing a database, 243–249IMPLICIT_TIMEZONE, 59timplicit joins, 264, 264–265IMPTKMOD, 59tIMPTSCMP, 59t

IMS. See Information Management System (IMF)IMS attachment facility, 41IN, 262, 285, 285, 304–307, 307, 757, 758, 817IN EXCLUSIVE MODE, 719–720, 720, 719IN SHARE MODE, 719–720, 720IN/NOT, 687IN-list direct table access, 758IN-list index scan, 821–822, 821in-memory objects, 862in-use pages, 856inactive connection support, 658inactive threads, 657include columns, vi, 228, 228INCREMENT BY n, 175tincremental copy, 438, 445–447Independent Software Vendors (ISV), 19

application programs and, 578DB2 Data Server Driver and, 21–22

Index Advisor, 30index copies, 453index matching predicates, 819index spaces, 150, 437INDEX, 110t, 116t, 120t, 121tINDEX_IO_PARALLELISM, 59tindex-only access, 823indexable predicates, 344, 345, 346, 346–348t, 817–818, 817,

818indexes, ii–iii, 30, 35, 149, 222–235, 251, 819–825

access path evaluation for, 756–758, 757access to, 819–824advisory reorg-pending (AREO) status, 234modifying, using ALTER INDEX, 155, 233–234, 234, 396atomic key in, 227avoiding sorts by using, 824–825, 824, 825binding/rebinding and, 568BUFFERPOOL in, 222catalog restrictions for, 223CHECK, 413–414, 414, 434clustering, and CLUSTER in, 222, 224–225, 225composite key in, 227compression of, using COMPRESS, v, 222, 224COPY in, 222creating, using CREATE INDEX, 153, 222–223Data Partitioned Secondary (DPSI), 229–231, 230, 231DB2 for z/OS and, 6deferring physical defi nition in, 224DEFINE in, 222

DELETE in, 149DROP INDEX to remove, 234, 234equal unique, 824expressions and, 228–229foreign key in, 227free space for, FREEPAGE/PCTFREE in, 222GBPCACHE in, 223generating, guidelines for, 233GROUP BY in, 278image copies and, 453IN-list direct table access for, 758IN-list index scan and, 821–822, 821INCLUDE columns in, 228, 228Index Advisor for, 30index matching predicates and, 819index spaces and, 150index-only access in, 758, 823indexable predicates and, 344, 345, 346, 346–348t,

817–818, 817, 818INSERT in, 149join columns and, 267keys in, 227large objects (LOBs) and, 231locks and, 716matching index scan and, 819, 820multiple access to, 756, 822nested loop join with sparse index in, 763non-matching index scan and, 757, 821Non-Partitioning Secondary (NPSI), 198, 202, 229, 229,

370non-unique, 226–227NOT PADDED in, 223null values and, 227number of matching indexes, in accessing, 757one-fetch access to, 823ORDER BY and, 271ownership of, and privileges, 126–127, 128tPage Set Rebuild-Pending (PSRBD) status, 234parallel index build using REORG, 379–380parallel index builds using LOAD, 369partitioning controlled by, 199–200, 199, 200tpartitioning of, using PART/PARTITIONED, iv, 223,

225–226, 225–226performance and, 819–825primary key in, 227range-list index access in, 758, 822–823, 823Rebuild-Pending (RBDP) status, 234rebuilding, using REBUILD INDEX, 397, 404recovery and, 437, 456RENAME INDEX in, 235, 235REORG and, 388–389, 389, 390, 390, 391, 394, 396restrictions start on, using ACCESS(FORCE), 430,

430restrictions to defi ning, 223screening of, 820, 820storage groups in, 236–237, 236–237system catalog, 233

Index 963

unique and UNIQUE, 223, 226–227unique index check, 765unique key in, 227unique where not null, 227UPDATE in, 149USING STOGROUP in, 223USING VCAT in, 223VALUES in, 223verifying use of, with EXPLAIN, 806–807Virtual Storage Access Method (VSAM) and, 150XML, 231–232, 232

INDEXONLY, 750t, 758INDEXVAL phase, LOAD, 356indicator variables, 521–522, 522indirect reference rows, 160INDREFLIMIT, 391–392, 391, 392Information Center, DB2, 30Information Management, 1Information Management System (IMS), 2, 4, 6, 37, 97

access control and, 99address spaces and, 38attachment facility and, 41, 42–43, 43call attachment facility and, 45commands for, 65CURSOR WITH HOLD in, 541–542, 542data sharing and, 507DB2 and, relationship between, 43, 43distributed data access and, 653, 656embedded SQL and, 43Extended Recovery Facility (XRF), 43global transactions and, 586locks and, 727, 728performance and, 853security in, 99stored procedures and, 615traces and, 834

Informix, 2, 8, 16, 18DB2 Connect and, 24, 25Optim Development Studio and, 29

InfoSphere Guardium Data Encryption, 6InfoSphere Optim, 2InfoSphere Optim Data Lifecycle Management for DB2, 26inherited privileges, 114initialization procedures, 40inline copies, 366–367, 449–450inline statistics

LOAD and, 367, 367REORG and, 386–387RUNSTATS and, 404–405, 405

inline views, 313INLISTP, 59tinner join, 269, 318–319, 318, 319, 354INNER, 318–319, 318, 319inoperative plans/packages, 569INSENSITIVE/SENSITIVE, 543–544, 543, 544tINSERT, ii, 104, 110t, 116t, 117t, 118t, 120t, 121t, 122t, 128t,

135t, 192, 173, 185, 255, 287–290, 287–290, 474, 523

check constraints and, 182–183constraints and, 180–183, 287coordinating updates to distributed data with, 653data change tables and, 316–317, 317data types and, 287DEFAULT keyword and, 288, 289distributed data access and, 648–650EXPLAIN and, 746foreign key constraints and, 181fullselects and, 256–257identity columns and, 597–599, 597, 599indexes and, 149large amounts of data with, using LOAD instead, 290, 355.

See also LOADlarge objects (LOBs) and, 704–705, 704LOCATION column and, 288locks and, 718, 721, 724, 743MERGE and, 292multi-row, 594–595, 595NOT ATOMIC and, 595null values and, 288referential constraints and, 180resource limit facility (governor) and, 834sequence objects and, 599, 602, 602SET clause and, 527, 527set of values, from SELECT, 289–290, 290specifi c column, 289, 289statistics and, 410table expressions and, 315–316temporary tables and, 587–588, 587, 590triggers and, 667, 668VALUES and, 288views and, 148, 211, 295, 296with SELECT, 598–599, 599XML and, 341

installation and migration, vi, 50–65, 53, 54–65command list (CLIST) for, 50conversion, conversion mode in, 51, 52data sharing and, 505–506DISPLAY GROUP to see mode for, 52–53, 53DSNZPARMs for, 53–54, 54–65tenabling new function mode in, 51, 52Environmental Descriptor Manager (EDM) and, 53ISPF and ISPF/Program Development Facility (ISPF/PDF),

50JCL for, 50jobs in, 50modes of migration for, 51new function mode in, 52packages and, testing, 570–571plans and, testing, 570–571SET SYSPARM for, 53–54, 54storage and, 50

Installation Verifi cation Procedure (IVP), 371instance merge modules, DB2 and non-DB2, 21INSTEAD OF, 316, 537, 667, 669, 673–674, 673, 674, 684Instrument Facility Interface (IFI), 45, 690, 837

964 Index

instrumentation facilities, 40instrumentation facility ID (IFCID), 834, 838, 839, 840,

840–852tINTEGER, 157–158, 172t, 174Integrated Catalog Facility, 494Integrated Cryptographic Services Facility (ICSF), 100Integrated Development Environment (IDE), 21, 26integrity of shared data, iv, 495–502, 668. See also locks and

lockingdeclarative referential, 249

Intel platforms, 1, 2Intent Exclusive (IX) locks, 722, 723Intent Share (IS) locks, 722, 723inter-DB2 read/write (R/W) interest, 495–496interactive SQL, 546. See also dynamic SQL; SQLInteractive System Productivity Facility (ISPF), 47interfaces with DB2, 47–50

Call Attachment Facility (CAF) and, 47CICS and, 47DB2 Interactive (DB2I), 47–48, 48, 69Interactive System Productivity Facility (ISPF) and, 47SQL Processing Using File Input (SPUFI) and, 49–50, 49TSO and, 47

Internal Coupling Facilities (ICFs), 487–488Internal Resource Lock Manager (IRLM), 489

commands in, 65data sharing and, 494locks and, 716, 728, 730traces and, 834

intersects/INTERSECT, 257, 312, 313invalidations, binding/rebinding and, 566, 574INVOKEBIND, 563tINVOKERUN, 563tIPLIST, 74t, 641, 644, 665IPNAMES, 74t, 85, 641, 644, 645, 665IRLM. See Internal Resource Lock Manager (IRLM)IRLMAUT, 59tIRLMPRC, 59tIRLMPROC, 39IRLMRWT, 59t, 93, 730t, 743IRLMSID, 59tIRLMSWT, 59tIS DISTINCT FROM/IS NOT DISTINCT FROM, 284,

285IS NULL/IS NOT NULL, 282–283, 283, 687, 817iSeries, DB2 Connect and, 25ISO date/time format, 167t, 168tISOLATION, 561, 563t, 678, 737isolation levels, iii, 575, 576, 725–726, 726–727t, 731, 731. See

also claimsISPF and ISPF/Program Development Facility (ISPF/PDF), 50IXCL1DSU utility, 490IXQTY, 59t

JJ2EE, 7, 25JAR, 126, 128t

Java, 1, 7, 14, 18, 21, 142, 613, 616DB2 Connect and, 23–25Optim Development Studio and, 28–29stored procedures and, 619, 635user-defi ned functions (UDFs) and, 690

Java Database Connectivity (JDBC), 18–21, 517–518, 825DB2 Connect and, 25dynamic SQL executed through, 547Optim Development Studio and, 28–29SQL application programming and, 549–550temporary tables and, 590traces and, 834trusted contexts and, 106

Java Technology Edition, 24JCL. See Job Control Language (JCL)JDBC. See Java Database Connectivity (JDBC)JIS date/time format, 167t, 168tJob Control Language (JCL), 46, 629, 630. See also Workload

Manager (WLM) installation, migration and, 50stored procedures and, 634utility execution and, 70

JOBNAME, 108tjobs, installation, migration and, 50join predicates, 267, 344JOIN_DEGREE, 752t, 768JOIN_PGROUP_ID, 752tJOIN_TYPE, 753tjoins, vi, 264–269, 264–265, 266–269, 317–333, 349, 354

Cartesian, 767–768COALESCE function and, 330, 331, 332combining outer, 331, 331–332, 332denormalization vs. 331–332encoding schemes (EBCDIC, ASCII, and Unicode) with,

268FROM clause and, 267, 268, 326full outer, 327–331, 327–329hybrid, 764–765implicit, 264, 264–265inner, 269, 318–319, 318, 319, 354join columns and, 266–267, 266–267left outer, 320–326, 321, 321–322, 323, 324, 325merge scan, 763–764nested loop, 762–763null-supplying table in, 320ON clause in, 319, 325–326outer, 320–332, 320predicate, join predicate, 267, 344predicates and, 346preserved row table in, 320right outer, 326–327, 326star, 765–768VALUE function and, 330WHERE clause and, 268, 323–325, 324, 325

Journal, 33, 36JSON, Optim Development Studio and, 29JULIAN_DAY, 167

Index 965

KKEEPDYNAMIC, 563t, 825Kerberos security, 6, 99, 133key target distribution tables, 803, 804–805tkeys, 149, 178–180, 227, 246

atomic key in, 227, 246composite key in, 227, 246dependent tables and, 179foreign, 179, 181, 227, 246, 248–249foreign key constraints, 181hash, 253parent, 179primary, 178–180, 190, 227, 246, 247, 247primary key constraints, 180, 180, 180sort key tables and, 795, 795–796tunique, 227, 246, 248

LL locks, 498Label-based Access Control (LBAC), 9LABEL, 128t, 653labeled duration UPDATE, 291Language Environment, 616, 639Large Object Manager (LOBM), 40large objects (LOBs), iii–vi, 40, 163, 179, 228, 684, 703–707,

713auxiliary tables and, 186–188, 187–188auxiliary warning (AUXW) status in, 472binary (BLOB). See binary large object (BLOB)casting and, 707CCSIDs and, 703character (CLOB). See character large object (CLOB)CHECK, 414, 414compression and, 704data types of, 703–704DB2 for z/OS and, 4double-byte character (DBCLOB), 703fi le reference variable and, 704–705implementation of, 704indexes, 231, 231INLINE option for, 186–188, 187–188insert, using INSERT, 704–705, 704LOAD and UNLOAD with, 705locks and, 717, 718–719logging and, 472LONG VARCHAR and, 703point-in-time recovery and, 472reading, 705–706, 705, 706recovery and, 472REORG and, 376, 377–378REPAIR and, 418SELECT and, 336SET command with, 705streaming, using FETCH WITH CONTINUE, 706,

710–711table spaces for. See LOB table spacestemporary tables and, 590

UNLOAD and, 373, 373user-defi ned data types and, 706–707, 707XML and, 710

LAST_DAY, 167LBACKOUT, 59t, 478–479, 478, 479LC_CTYPE, 59tLEAFDIST, 395LEAFDISTLIMIT, 392, 392Least Recently Used (LRU) pages, 857LEAVE, 634left outer join, 320–326, 321, 321–322, 323, 324, 325LEMAX, 59tLempel-Ziv (LZ) compression, 8less-than operator, 261less-than/equal-to operator, 261levels of security, 132libraries, recovery of, 470LIBRARY, 563tLIKE/NOT LIKE, 188, 262, 280–281, 280, 281, 687limited partition scanning, 760–761, 761LINKNAME, 86, 644, 645links, in data sharing, 492Linux, 1, 2, 3, 8–19

Data Studio, Optim Data Studio and, 27DB2 Connect and, 22–25Optim Database Administrator for, 27–28Optim pureQuery Runtime and, 29Optim Query Tuner and, 29–30performance and, 853

LIST COPYPOOL, 466list prefetch, 760list structure (SCA), data sharing and, 488–489, 509, 511, 512LISTCAT, 470literals, concentration of, 548LOAD, 111t, 121t, 122t, 128t, 134, 135t, 182, 185, 290, 293,

355–371, 357, 358, 705BUILD phase in, 356CCSID incompatibility in, 366CHECK DATA utility and, 365concurrent access during, 362–363COPYDDN keyword in, 357, 360cursors and, 370–371, 371data type incompatibility in, 366delimited (using COLDEL, CHARDEL, DECPT), 362, 362DISCARD phase in, 356ENFORCE CONSTRAINTS in, 365ENFORCE NO option in, 364, 364ENFORCE phase in, 356FLASHCOPY and, 367freespace (FREEPAGE/PCTFREE) and, 366GENERATED options and, 365–366historical statistics with, 405–406, 406INDEXVAL phase in, 356inline copies using, 366–367inline statistics with, 367, 367input and target for, 357–358, 357, 358load parallelism for partitioned table spaces and, 370, 370

966 Index

LOAD SHRLEVEL in, 356LOGAPPLY phase in, 356logging with, 360LOGSCR phase in, 356LOGUNDO phase in, 356ordered rows and, 361parallel index builds and, 369partitions and, using PART, 361–362, 361, 362phases of, 356–357PREFORMAT and, 357, 368–369REBALANCE partitions and, 367, 367RECOVERYDDN keyword in, 357REDO phase in, 357referential integrity during, 363–365, 364REPLACE and, to add to or replace data using, 358–360,

359REPORT phase in, 357RESUME in, 359rotating partitions and, 198–199, 199ROWID columns and, 365–366rules added to, 358, 358RUNSTATS and, 367SHRLEVEL in, 362SORT phase in, 357SORTBLD phase in, 357SORTKEYS and, 368, 369statistics and, 408, 410table spaces and, 206, 210, 393tables and, 393triggers and, 667UTILINIT phase in, 357UTILTERM phase in, 357XML and, 709

load modules, binding/rebinding and, 555load parallelism for partitioned table spaces, 370, 370LOAD REPLACE, 408, 443, 449–450, 450, 464, 467loading/unloading data, iv. See LOADLOB data type. See large objects (LOBs)LOB table spaces, 150, 193, 202, 202, 203

DSN1COMP and, 420–421recovery and, 458REORG and, 394statistics and REORG in, 406

LOB_INLINE_LENGTH, 59tLOBVALA/LOBVALS, 59tLocal Area Networks (LANs), 24local DB2, 83

access control and, 98distributed data access and, 641

Local Lock Manager (LLM), 498local locks, 497–498local predicates, 344LOCATION column, INSERT, 288location name, 85LOCATIONS, 74t, 85, 641, 644, 665LOCK, 128t, 502lock structure, data sharing and, 489

LOCK TABLE, 135t, 586, 717, 719–720, 720LOCK TABLESPACE, 717, 719lock-latch wait, viLOCKMAX, 203, 734–736locks and locking, iii–v, 496–499, 715–743. See also

concurrencyaccounting and statistics reports on, 738–739, 738–739ACQUIRE in, 725address spaces and, 38, 39ALLOCATE/DEALLOCATE in, 725attributes of, 716–717avoiding, 728–729, 728, 729tbinding and, 716catalog and tables, 74claims on, 731, 731tcoding retry logic for errors in, 734COMMIT in, 725, 734commit scope in, 715contention in, 499, 721, 742CURRENTDATA and, 728–729, 729t, 737currently committed data access and, 721–722CURRENTLY COMMITTED and, 737cursor stability and, 725–726data sharing and, 489, 511, 516Database Descriptor (DBD) and, 716DDL and, 716deadlocks in, 732–734displaying, using DISPLAY DATABASE LOCKS, 739,

739, 743drains on, 732duration of, 716, 724–728escalation of, 734–735exclusive (X), 722, 724, 728EXPLAIN and, 738Explicit Hierarchical Locking (EHL) in, 496–497, 496false lock contention in, 499FETCH with, 727global, 497–498global lock contention in, 499Global Lock Manager (GLM) and, 498IN EXCLUSIVE MODE and, 719–720, 720IN SHARE MODE and, 719–720, 720indexes and, 716Intent Exclusive (IX), 722, 723Intent Share (IS), 722, 723Internal Resource Lock Manager (IRLM) and, 38, 39, 489,

716, 728, 730IRLMRWT and, 743ISOLATION and, 737isolation levels for, 725–726, 726–727tLarge Object (LOB), 717, 718–719local, 497–498Local Lock Manager (LLM) and, 498LOCK TABLE in, 586LOCK/UNLOCK and, 502lockable objects for, 716LOCKMAX and, 734–735, 736

Index 967

LOCKSIZE and, 204, 736Logical (L), 498modes for, 716, 722–724, 722t, 723t, 724t, 742, 743modifi ed resource list for, 498modify, 498monitoring, 738–740, 738–739NUMLKTS and, 735optimistic, 603–604, 737Page (P), 497–498, 718, 724, 725Page Set (P), 497–498partitions and, 725performance issues and, 715Physical (P), 497–498promotion of, 734, 735read stability with, 726RELEASE in, 725repeatable read isolation with, 726RESTART LIGHT and, 499retained, 498–499ROLLBACK and, 734row, 718, 724, 725SELECT and, 727, 728–729, 728, 741serialization in, 715Share (S), 722, 724, 727Share with Intent Exclusive (SIX), 722, 723size of, 716, 717–719Skeleton Cursor Table (SKCT) and, 716Skeleton Package Table (SKPT) and, 716SKIP LOCKED DATA option for, 720, 721, 737, 743SKIP LOCKED ROWS and, 721system parameters for, 730, 730t, 741, 743table spaces and, using LOCK TABLESPACE, 717, 719,

723–725, 741tables and, using LOCK TABLE, 717, 719–720, 720,

723–725temporary tables and, 590timeouts in, 732–734, 732ttraces for, 740, 743TSLOCKMODE and, 735, 738uncommitted read with, 726Update (U), 722, 724, 727XES contention in, 499XML and, 719

LOCKSIZE, 204, 736LOG, 203, 375, 388log data sets, archive, 439Log Record Sequence Number (LRSN), v, 439, 509–510, 510LOGAPPLY, 356, 455, 456, 457, 459LOGGED/NOT LOGGED, 204logs and logging, 439–442, 611, 871–872. See also recovery

and restartarchive log data sets in, 439, 470Bootstrap Data Set (BSDS) and, 440–441checkpoint intervals in, using SET LOG, 477, 478data sharing and, 494, 509–510, 510DISPLAY LOG in, 440DSN1LOGP and, 422

DSNJLOGF (Preformat Active Log) and, 419DSNJU003 (Change Log Inventory) and, 419, 440, 441,

465, 475DSNJU004 (Print Log Map) and, 419–420, 470–471, 441,

647LOAD and, 360LOBs and, 472log data sets in, archive, 439Log Record Sequence Number (LRSN) in, 439, 509–510,

510MODIFY and, 442performance and, 871–872reads/writes in, 871–872redo records in, 439Relative Byte Address (RBA) in, 439REORG and, 380, 384–386SET LOG SUSPEND/RESUME in, 476SYSCOPY and, 442SYSLGRNX in, 441–442undo records in, 439

Logical (L) locks, 498logical database design, 237–242logical expressions, 338logical operators

NOT, 283–285, 283, 284–285OR, 285, 285

Logical Terminal (LTERM), 99Logical Unit name (LU name), 85Logical Unit of Work (UOW), 579LOGSCR phase, LOAD and, 356LOGUNDO phase, LOAD and, 356long log conditions, REORG and, controlling, 385, 386LONG VARCHAR, 160, 171, 703LONG VARGRAPHIC, 160, 171LOOKUP, 564tLOOP, 634LOW2KEY, 411LOWVALUE, 403LPL, 427tLRDRTHLD, 59t, 730tLRSN. See Log Record Sequence Number (LRSN)LULIST, 75t, 85, 644, 665LUMODES, 75t, 85, 644, 665LUNAMES, 75t, 85, 86, 644, 645, 665

MMAINTAINED options, 214maintaining data. See data maintenanceMAINTYPE, 59tmanagement, 30–31mapping table, REORG and, 382–384, 383, 384masks

ALTER for, 155column, 102, 104–105, 104, 105CREATE for, 153

MASS, 97mass deletes, 293

968 Index

massively parallel, 15–16MATCHCOLS, 750t, 757matching index scan, 819, 820Materialized Query Tables (MQT), iii, 11, 13, 16, 17, 35, 147,

148, 151, 213–215altering, using ALTER TABLE, 215creating, using CREATE TABLE for, 153, 213–214, 214DB2 for Linux, UNIX, Windows (LUW) and, 11, 16, 17defi ning, 213ENABLE QUERY OPTIMIZATION option for, 214–215MAINTAINED options for, 214OLAP and, 213options for, 214queries and, 213REFRESH options for, 214

materializing a temporary table, 588, 588, 591mathematical operations, derived columns and, 273–274, 274MAX, 276–277, 276, 823MAX_CONCURRENT_PKG_OPS, 59tMAX_COORDAGENTS, 660MAX_NUM_CUR, 60tMAX_ST_PROC, 60tMAXAGENTS, 660MAXARCH, 59t, 441MAXBLKEXT, 657MAXDBAT, 59t, 660MAXKEEPD, 60tMAXOFILR, 60tMAXPARTITIONS, 201, 204, 208, 209t, 221, 253MAXRBLK, 60tMAXROWS, 204, 221, 384–385MAXRTU, 60tMAXTEMPS, 60tMAXTEMPS_RID, 60tMAXTYPE1, 60tMAXVALUE, 176tMCCSID, 60tMEMBER CLUSTER, 209MEMBER, 564tmember-specifi c routing, in distributed data access, 646–647,

647members, data sharing and, 485, 513MEMBNAME, 60tmemory/memory management, v, 86–89. See also buffer pools;

Environmental Descriptor Manager (EDM) poolsbuffer pools in, 86–87Environmental Descriptor Manager (EDM) pools in, 87–88memory and memory management in, 86–89Row Identifi er (RID) pool in, 88skeleton pools in, 87sort pool in, 88–89STAY RESIDENT option for, 616stored procedures and, 616

MERG_JOIN_COLS, 753tMERGC, 756tMERGE, 104, 255, 292, 523

coordinating updates to distributed data with, 653

data change tables and, 316–317, 317multi-row, 595–596, 596scalar-fullselect and, 257triggers and, 667

merge scan join, 763–764MERGECOPY, 446, 451–452, 452t, 452MERGN, 756tmessage generator, 40, 41METHOD, 749tMGEXTSZ, 60tMICROSECOND, 168Microsoft.NET, 7Microsoft Transaction Server, DB2 Connect and, 24Microsoft Windows, 2, 3, 8–19

DB2 Connect and, 22–25Optim Data Studio and, 27Optim Database Administrator for, 27–28Optim pureQuery Runtime and, 29Optim Query Tuner and, 29–30performance and, 853

middleware, 19–25Data Server Clients as, 19–22Data Server Drivers as, 19–21Data Server Driver for JDBC and SQLJ, 20, 21Data Server Driver for ODBC and CLI, 20–22Data Server Driver Package as, 20–22Data Server Runtime Client as, 19–22DB2 application drivers and, 20–21DB2 Connect as, 19, 22–25instance merge modules, DB2, 21non-DB2 instance merge modules, 21

migration. See installation and migration, 50MIN, 277, 823MINSTOR, 60tMINUTE, 168MINVALUE, 176tMIPS, 18, 19mirroring, 9MIXED, 60tMIXOPSEQ, 751t, 756mobile users, DB2 Connect and, 23modeling production environments, 411, 813–814, 813–814tmodels, data, ivmodes, for locks, 722–724, 722t, 723t, 724tMODESELECT, 75t, 85, 644, 665modifi ed resource list, locks, 498MODIFY, 414–417, 442MODIFY DDF, 67t, 647modify locks, 498MODIFY RECOVERY, 414, 415–416, 415MODIFY STATISTICS, 406, 414, 416–417, 417MODIFY TRACE, 67t, 839Module Entry Point Lists (MEPLs), 418MON, 60tmonitor trace, 838–839, 843–844tMONITOR1/MONITOR2, 112t, 116t, 118t, 119tmonitoring performance. See performance and tuning

Index 969

MONSIZE, 60tMONTH, 167MONTHS_BETWEEN, 167MQSeries, stored procedures and, 635MSTR, 39MSU, DB2 Connect and, 24multi-row operations, 593–595Multibyte Character Set (MBCS), 161Multidimensional Clustering (MDC) tables, 12, 13, 16, 17Multidimensional Online Analytical Processing (MOLAP),

temporary tables and, 589multiple index access, 822Multiple Virtual Storage (MVS), 38, 834MXDTCACH, 60t

NNACTIVE, 411naming conventions

aliases and, 148binding/rebinding and, 559, 566correlation names in, 269–270, 269data sharing and, 505–506distributed data access and, 641schemas, 685three-part names in, 647–649, 648unqualifi ed names and, 129–130

NEARINDREF, 395NEAROFFPOS, 395negative conditions

IS DISTINCT FROM/IS NOT DISTINCT FROM, 284, 285

NOT, 283–285, 283, 284–285nested loop joins, 762–763nested stored procedures, 624–625nested table expressions, 313–315, 313, 314, 315nested views, 212.NET, 7, 14, 19, 21, 25Net Search Extender, 8–9, 13–17, 31Netezza, 2network improvement using stored procedures, 614–615, 614networks, iiNeutrino, 18New Function Mode (NFM), 52, 93, 420NEXT VALUE FOR (NEXTVAL), 192–193, 599–603, 600,

601, 610, 611NEXT_DAY, 167NLEAF, 411NO CACHE, 175tnon-deterministic UDFs, 697non-line SQL scalar UDF. See user-defi ned functions (UDFs),

693non-matching index scan, 757, 821non-read-only views, 295, 296–297non-unique index, 226–227Nonpartitioned Secondary Indexes (NPSI), 198, 202, 229, 229,

370nonrelational data, ii

nonsargable predicates, 344–345NOPKLIST, 564tnormal forms, database, 239–241, 240, 241normalization, iv, 239–241, 240, 241

joins, denormalization and, 331–332NOT, 283–285, 283, 284–285NOT ATOMIC, 595not-equal-to operator, 261NOT LOGGED, 427t

recovery and, 458restarting DB2 and, 476

NOT NULL, 170NOT NULL WITH DEFAULT, 289NOT PADDED, 223NPAGES, 411NPGTHRSH, 60tnull values, 170, 170

indexes and, 227INSERT and, 288IS NULL, 282–283, 283null-supplying table and, in joins, 320

null-supplying table, in joins, 320NUM_INITAGENTS, 660NUM_POOLAGENTS, 661NUMERIC, 156t, 157–159, 172tNUMLKTS, 60t, 730t, 735NUMLKUS, 60t, 730tNUMPARTS, 201, 204, 208, 209tNUMTCB, 639

OObject Descriptors (OBDs), 432

DIAGNOSE, 418REPAIR and, 418

object identifi ersDSN1COPY and, 422logging and, 441

object-level recovery with system-level backups, 466–467object-oriented (OO) interfaces, global transactions and, 586object-relational extensions, 684–689. See also extensionsOBJECTNAME, 137tobjects, iii. See also specifi c objects

access control and, 100–131. See also specifi c objects, 100IDs for, 100–101in-memory, 862managing, in database, 152–155ownership of, 100–101privileges associated with, by owner, 128qualifi ed, 126–127recovery of, 468sequence, 152, 599–603. See also sequence objectstrusted context, 127unqualifi ed, 125–126, 572

OBJECTSCHEMA, 137tOBJECTTYPE, 137tOBJMAINT, 138t, 140tODBC. See Open Database Connectivity (ODBC)

970 Index

offl ine utilities, ivOFFPOSLIMIT, 391–392, 391, 392OJPERFEH, 60tOLAP. See Online Analytical Processing (OLAP)OLD TABLE, 316–317, 317OLE DB, 19, 21, 25OLTP. See Online transaction processing (OLTP)OMEGAMON DB2 Performance Monitor, 831, 832, 833ON, 319, 325–326, 343, 354

CASE expression in, 334–335, 334, 335, 334On/Off Capacity Upgrade on Demand, 7On-Demand business capabilities 2–3, 7one-fetch index access, 823Online Analytical Processing (OLAP)

DB2 Connect and, 23Materialized Query Tables (MQTs) and, 213SQL and, 341–343, 342, 343

online DSNZPARMs, 53–54, 53online performance monitors, 831–833online reorganization, 12–17Online Transaction Processing (OLTP), ii, 6, 23, 38, 578, 807OPEN CURSOR, 537–538, 538Open Database Connectivity (ODBC), 19–22, 517–518

DB2 Connect and, 25dynamic SQL executed through, 547result sets and, 621SQL application programming and, 548–549stored procedures and, 621temporary tables and, 589, 590traces and, 834trusted contexts and, 106

open development environments, DB2 for iOPEN, 520Open Transaction Environment (OTE), 852operating systems, 2operators

comparison, 304, 687logical, 283–285, 283, 284–285NOT, 283–285, 283, 284–285OR, 285, 285

OPTHINT, 60t, 564t, 754tOptim Data Lifecycle Management for DB2, 26Optim Data Studio, 17, 26–27, 805–806

access paths and, 770EXPLAIN and, 747

Optim Database Administrator, 9, 13–17, 26–28Optim Development Studio, 9, 13, 14, 15, 16, 17, 21, 26,

28–29, 805–806DB2 Connect and, 19, 23DB2 for Linux, UNIX, Windows (LUW) and, 9, 13–17EXPLAIN and, 747stored procedures and, 617, 632, 633, 634, 635

Optim Performance Manager, 16, 17 Optim pureQuery Runtime, 26, 29Optim Query Tuner, 29–30, 747, 770, 805–806Optim Query Workload Tuner, 26, 805–806Optim. See also InfoSphere Optim

optimistic locking, 603–604, 737optimization, 745–756

Cartesian products and, 263–265, 263–264global, 769hints for improving, 808–809query, 214–215

OPTIMIZE, 592, 592, 807, 824OPTIMIZE FOR n ROWS, 656–657, 659, 662, 664optimizers, 746OPTIONS, 71–72, 652, 564tOPTIOWGT, 60tOR, 285, 285, 758, 766, 822, 823Oracle, 18, 25ORDER BY, 256, 257, 270–274, 271, 272, 273, 278, 300, 301,

333, 333, 334, 343, 806, 824access paths and, 762indexes and, 271

ORDER OF, 333, 333, 334ORDER/NO ORDER, 176tordered rows, LOADing, 361ORGANIZED BY HASH, 220–221, 221OS/400, 2OTC_LICENSE, 60toutages, iv, 5, 478OUTBUFF, 60touter joins, 320–332, 320overloading, user-defi ned functions (UDFs) and, 700OVERRIDING USER VALUE, 597OWNER, 564t, 571–572ownership of objects, 100–101, 125–130

access plans, 126CREATE and, 125–127package, 126, 129, 571–572plan, 129, 571–572privileges of, by object, 128qualifi ed objects and, 126–127QUALIFIER option and, 125–126, 129–130ROLE AS OBJECT OWNER and, 127, 130trusted contexts and, 127, 130unqualifi ed names, 129–130unqualifi ed objects, 125–126

PP locks, 497–498PACKADM, 115, 120tpackage lists, REBIND, 567, 567Package Tables (PT), 87PACKAGE, 564tpackages, 558

BIND/REBIND in, 560, 561, 565, 566–571, 566, 651–652binding when in use, 560–561collections of, binding into, 570CURRENT PACKAGE PATH register in, 559CURRENT PACKAGESET register in, 559, 559EXPLAIN and, 746inoperative, 569invalidation of, 566, 574

Index 971

migration of, 570–571ownership of, 126, 128t, 129, 571–572plan-to-package ratio in, 558privileges for, 110t, 128tremoving, using FREE or DROP PACKAGE, 571, 571triggers forming, 678

PADDED/NOT PADDED, 568PADIX, 60tPADNTSTR, 61tPage (P) locks, 497–498page range tables, 797, 797–798tPage Set (P) locks, 497–498Page Set Rebuild-Pending (PSRBD) status, 234, 428tPAGE_RANGE, 753tpages

asynchronous read/write in, 858available, 855buffer pool, 855–856checkpoints and page externalization in, 858–859fi xing, with PGFIX, 862–863in-use, 856Least Recently Used (LRU), 857locks and, 718, 724, 725recovery and, 437, 456Sequential Least Recently Used (SLRU), 857size of, in table space, 205, 205tstealing, 862synchronous reads/writes in, 857–858updated, 856

PAGESAVE, 404Palm OS, 18, 19PARA_EFF, 61tparallel group tables, 784, 784–786tparallel index build, 369, 379–380parallel operations, EXPLAIN and, 747Parallel Sysplex, 46, 830

data sharing and, 485, 490, 491, 508–509, 508DB2 for z/OS and, 5, 6logging and, 439recovery and, 439Sysplex Failure Management (SFM) in, 491, 492Sysplex Timer in, 492, 493temporary tables and, 590

parallel task tables, 787, 787–789tparallelism, 7, 827–830

buffer pools and, using VPPSEQT and VPXPSEQT, 862cursors and, 830data sharing and, 508–509, 508massive, 15–16partitioned table spaces and, 370, 370query, 12query, vitemporary tables and, 590UNLOAD and, 373usage of, 768Virtual Pool Parallel Sequential Threshold (VPPSEQT)

and, 862

Virtual Pool Sysplex Parallel Sequential Threshold (VPXPSEQT) and, 862

PARALLELISM_MODE, 753tPARAMDEG, 61tPARAMETER STYLE, 618–620, 618, 619parameters. See also DSNZPARMs

DBINFO for, 620–621, 620–621tPARAMETER STYLE in, 618–620, 618, 619passing, 691–692stored procedures and, 617–621, 617, 618, 619

parent keys, 179PARENT_PLANNO, 755tPARENT_QBLOCK, 754tPART, 223, 361–362, 361, 362PARTITION BY, 343PARTITIONED, 223Partitioned Data Sets (PDS), 556partitioned table spaces, iv, 150, 193, 196–200, 812–813, 812t

adding, 197, 197, 198creating, 196, 196–197REORG and, 376–377, 377rotating, 198–199, 199table-controlled, 199–200, 199, 200t

partitioned tables, locks and, 717partitioning, 251, 253

Dynamic Logical Partitioning, 7EXPLAIN and, 747image copies and, 447indexes and, iv, 225–226, 225–226limited partition scanning in, 760–761, 761load parallelism for table spaces and, 370, 370LOAD for, 361–362, 361, 362locks and, 725MAXPARTITIONS and, 201REBALANCE, 367, 367rebalancing, using REORG, 396–397, 396recovery and, 437, 456REORG and, 396–397, 396rotating partitions and, 198–199, 199table, 12, 16, 18table space, iv, 150, 193, 196–200, 812–813, 812t. See also

partitioned table spacestable- vs. index-controlled, 199–200, 199, 200tUNLOAD and, 372

passing parameters, 691–692PATH, 126, 685, 564t, 572path expressions, 338PATHDEFUALT, 564tPCLOSEN/PCLOSET, 61tPCTFREE, 203, 206, 366PCTROWCOMP, 404pending conditions, 461–462pending defi nition change, 393performance and tuning, iv–v, 93, 745–877

access paths in, 745–756. See also access pathsaccess plans in, 746. See also access plansbatch processes and, 831–833

972 Index

buffer pools and, 855–871catalog statistics in, 809–816. See catalog statisticscheckpoints and page externalization in, 858–859CICS and, 852, 853continuous, 852–853data set opening and closing in, 872–873data sharing and, 502–503database monitoring and, 830–834Database Request Module (DBRM) and, 746DB2 for z/OS and, 5detailed, 853–854DISPLAY commands for, 854–855DSN_COLDIST_TABLE and, 802, 802–803tDSN_DETCOST_TABLE and, 790, 791–792tDSN_FILTER_TABLE, 789, 789–790tDSN_FUNCTION_TABLE and, 773, 773–775tDSN_GROUP_TABLE, 784, 784–786tDSN_KEYTGTDIST_TABLE and, 803, 804–805tDSN_PGRANGE_TABLE and, 797, 797–798tDSN_PREDICATE_TABLE and, 778, 778–781tDSN_PTASK_TABLE, 787, 787–789tDSN_QUERY_TABLE and, 800, 800–801tDSN_SORT_TABLE and, 793, 793–794tDSN_STATEMENT_CACHE_TABLE and, 775, 775–778tDSN_STATEMENT_TABLE and, 770, 771–773tDSN_STRUCT_TABLE and, 782, 782–784tDSN_VIEWREF_TABLE and, 798, 798–799tdynamic SQL and, 825–827exception, 854EXPLAIN and, 745–756, 806–808GET DIAGNOSTICS in, 532–535, 532–535t, 546, 554global optimization and, 769Health Monitor, Data Studio for, 27I/O requests and externalization of, 857–858IMS and, 853indexes and, 819–825locks and, 715logging and, 871–872OMEGAMON DB2 Performance Monitor for, 831, 832,

833online, 831–833Open Transaction Environment (OTE) and, 852Optim Performance Manager and, 16–17optimization and optimizers in, 745–756periodic, 853predicate types and, 817–819, 817query parallelism and, 827–830resource limit facility (governor) for, 834SQL and, 746Statistics Advisor and, 30Tivoli OMEGAMON XE for, 831, 832, 833trace for, 834–854, 845–851t. See also tracestriggers and, 669, 680WebSphere and, 853

period specifi cationFROM, 285–286, 286UNION, 310–311, 311

periodic performance monitoring, 853periods, application and system, 151–152, 216–219, 285–286,

286, 310–311, 311permanent (base) tables, 147permissions

ALTER, 155CREATE PERMISSION in, 103, 103, 153row, 102–103, 103, 104–105, 105

PERMIT, 97, 97permutation, changing column order using SELECT, 260,

260–261Personal Edition, DB2 Connect, 4, 12–13, 23–24. See also DB2

for Linux, UNIX, and Windows (LUW)PGFIX, 862–863PGSTEAL, 862PHP, 14, 19, 25Physical (P) locks, 497–498physical model, ivphysical organization of data, 374PKLIST, 564tPL/1, 593, 616, 710

stored procedures and, 638user-defi ned functions (UDFs) and, 690

plan tables, in EXPLAIN, 746, 747, 748, 748–756tPLAN, 564tPLAN_TABLE, 808, 876, 877PLANMGMT, 61t, 564t, 570–571, 570t, 571PLANMGMTSCOPE, 61tPLANNO, 748tplans, 143, 558

authorization to execute, 573binding/rebinding of, using BIND/REBIND PLAN, 558,

558, 561, 561, 565, 568, 570–571, 652–653, 652binding when in use, 560–561collections of, binding into, 570execution of, authorization for, 130EXPLAIN and, 746inoperative, 569invalidation of, 566, 574migration of, 570–571migration testing for, 570–571ownership of, 129, 571–572plan-to-package ratio in binding, 558PLANMGMT options for, 570–571, 570t, 571removing, using FREE or DROP PACKAGE command,

571, 571stability of, 570

PLslash1, 1, 2PocketPC, 18, 19point of consistency, using QUIESCE, 438, 454–455, 454, 579Point-in-Time (PIT) recovery, iv, 382, 460–462

LOBs and XML objects, 472policies, audit, 137–140, 137–140tpolymorphism, user-defi ned functions (UDFs) and, 700–701,

700–701pooled threads, 658POOLINAC, 61t, 660

Index 973

pools, v–vi, 9–10buffer. See buffer poolscopy, 484data sharing and, 494database copy pool, 457–458Environmental Descriptor Manager (EDM). See

Environmental Descriptor Manager (EDM) poolgroup buffer pools (GBPs, cache structures) in, 489–490idle agent, 660virtual buffer. See virtual buffer pools

positioned delete, 293, 540, 540, 543, 554positioned update, 291, 539–540, 540, 543precompile, 47, 555, 556–557, 651predicates, vi, 261–262, 343–346, 346–348t, 817–819, 817

BETWEEN, 282, 282Boolean terms and, 344compound, 344correlation (correlated reference), 304DM component and, 818DSN_PREDICATE_TABLE and, 778, 778–781tEXPLAIN and, 747IN, 285, 285, 817index matching, 819indexable, 344, 345, 346, 346–348t, 817–818, 817, 818IS DISTINCT FROM/IS NOT DISTINCT FROM, 284,

285IS NOT NULL, 817IS NULL, 282–283, 283join, 267, 344, 346LIKE, 280–281, 280, 281local, 344nonsargable or residual, 344–345quantifi ed, 304RDS component and, 818sargable, 344–345simple, 344stage 1, 344–345, 346–348t, 818, 818stage 2, 344–345, 818–819, 819XMLEXISTS, 340–341, 340

predictive governing, 834PREFETCH, 751t, 760PREFORMAT, 357, 368–369PREPARE, 520, 521, 546, 558, 720–721, 828, 871preserved row tables, 320PREVIEW, 72–73PREVIOUS VALUE FOR (PREVVAL), 192–193, 599–603,

600, 601, 610, 611primary expressions, 338primary keys, 178–180, 180, 190, 227, 246, 247, 247primary, in recovery, 10PRIMARY_ACCESSTYPE, 754tprinting

DSN1PRNT and, 423DSNJU004 (Print Log Map), 419–420, 419, 470–471,

647PRIQTY, 61t, 206–207private protocol, distributed data access and, 643

privileges, ii, v, 144. See also authoritiescollection, 111tdatabase, 111tdistinct type, 113texplicit, 109–114, 110–113tGRANT, 113–114, 113, 114inherited, 114object ownership, 128package, 110tplan, 110trelated, 114REVOKE, 113–114, 113, 114routine, 113tschema, 113t, 685sequence object, 113tsubsystem, 111–113ttable, 110tusage, 113tuser-defi ned data types and, 689

procedures. See stored proceduresprocessing, data sharing and, cost of, 503. See also performance

and tuningproduction environment modeling, 411, 813–814,

813–814tprofi les, RUNSTAT, 406–407, 407, 815–816PROGNAME, 748tPROGRAM SUB TYPE, 631, 631programming, for distributed data access, 654–655projection, projecting columns from table using SELECT,

259–260, 259–260promotion of locks, 734, 735PROTECT, 61tpruning of subqueries, 310, 310PSRBD. See Page Set Rebuild-Pending (PSRBD) statusPTASKROL, 61tpureQuery, 21, 26, 29

DB2 Connect and, 24, 25Optim Development Studio and, 29SQL application programming and, 549–550

pureScale, 10, 15–17, 27pureXML, 336–341

DB2 Connect and, 23DB2 for z/OS and, 6DB2 for Linux, UNIX, Windows (LUW) and, 9,

13–17

QQ replication, 11, 16, 17QBLOCK_TYPE, 754tQBLOCKNO, 748tQNX Neutrino, 18qualifi ed objects, ownership of, 126–127QUALIFIER, 129–130, 564t, 572, 627, 678QUALIFIER BIND, 590qualifying rows, 262, 262quantifi ed predicates, 304QUANTILENO, 403

974 Index

QUARTER, 167queries

aggregating/grouping data in, using GROUP BY, 277–278, 277, 278

consistency, for catalog, 82, 82data sharing and, parallelism in, 508–509, 508DSN_QUERY_TABLE and, 800, 800–801tduplicate data in, DISTINCT clause in, 279–280, 280fi ltering in, 343–346health check and, ivMaterialized Query Tables (MQTs) and, 213negative conditions in, using IS DISTINCT FROM/IS NOT

DISTINCT FROM, 284, 285negative conditions in, using NOT, 283–285, 283, 284–285null values in, using IS NULL, 282–283, 283number of tables supported in, 318Optim pureQuery Runtime, 26, 29Optim Query Tuner, 26, 29–30OPTIMIZE FOR n ROWS in, 656–657, 662, 664optimizing, 214–215parallelism in, vi, 12, 827–830patterns of strings in, using LIKE, 280–281, 280, 281performance of, viperiod specifi cation in, using FROM, 285–286, 286predicates and, 343–346, 346–348tPureQuery and, 21, 549–550Query Advisor for, 30Query Annotation for, 30Query Management Facility (QMF) and, 31–32, 125, 467,

747Query Report for, 30ranges of data in, using BETWEEN, 282, 282remote, with distributed data, 656–661REORG and, in catalog, 389–391restricting use of sets in, HAVING clause and, 279, 279SELECT statement in, 256–287. See also SELECT set of values, using IN, 285, 285set of values, using OR, 285, 285SQL Processing Using File Input (SPUFI) and, 49–50, 49stacking, using semicolon delimiter, 49sub-, 257, 303–307, 304, 305, 306, 307temporal tables and, 606user-defi ned functions (UDFs) and, 697views and subqueries in, 212wildcard characters in, 280–281XMLQUERY and, 336–341XQuery and, 340–341

Query Advisor, 30Query Annotation, 30Query Management Facility (QMF), 31–32, 125, 467, 747Query Patroller, 12Query Report, 30query tables, 800, 800–801tQUERYID, 564tQUERYNO, 748tquestions and answers, sample, 892–940QUIESCE, 61t, 454–455, 454, 472, 474, 474

RRAISE_ERROR, 679RAND function, 759RANDOMATT, 61trange-list index access, 758, 822–823, 823Rational Application Developer, 1, 25Rational Data and Application Modeling, 1, 2Rational Developer for zEnterprise, 1, 2RBDP. See Rebuild-Pending (RBDP) statusRDBNAM, 85RDS component, predicates, 818read stability, locks and, 726read/write online reorgs, 381, 382read-only views, 212–213, 295–296, 299REAL, 157, 158, 172tReal Time Statistics (RTS), v, 395, 408–410, 409realm, in Kerberos, 99REBALANCE, 197, 367, 367REBIND. See also BIND/REBINDREBIND PACKAGE, 685, 722REBIND PLAN, 685, 722REBIND TRIGGER PACKAGE, 66t, 678rebinding. See binding and rebindingREBUILD, 404, 405–406, 406, 408REBUILD INDEX, 397, 456, 461–462, 467Rebuild-Pending (RBDP) status, 234, 428t, 461–462REC_FASTREPLICATION, 61tRECALD, 61tRECALL, 61tRECORD TEMPORAL HISTORY, 565tRECOVER, iv, 112t, 118t, 134, 455–459, 457, 460, 462, 463,

464, 466–467RECOVER BSDS, 67tRECOVER INDOUBT, 67tRecover Pending (RECP), 428t , 464RECOVER POSTPONED, 68t, 430, 478–479, 478, 479, 482Recoverable Resource Manager Services Attachment Facility

(RRSAF), 41–42, 629Recoverable Resource Services (RRS), global transactions and,

586RECOVERDB, 111t, 119t, 121t, 122trecovery and restart, 437–484

access during, using SHRLEVEL, 449, 450ARCHIVE LOG in, 470, 471Auxiliary Warning (AUXW) status in, 472backing out work to a point-in-time in, 462BACKUP SYSTEM in, 464, 466Bootstrap Data Set (BSDS) and, 440–441catalog, catalog copying for, 453–454, 467CHANGELIMIT in, 448–449, 448CHECK DATA and, 461–464CHECK INDEX and, 461–462check pending in, 461–462checkpoint intervals in, SET LOG in, 477, 478clone tables and, 463commit scope in, 438–439concepts in, 437–438

Index 975

COPY in, 438, 463, 469COPYPOOL in, 464COPYTOCOPY in, 457CREATE TABLE LIKE and, 464data only, not layout in, 438data sharing and, 471, 480, 509, 515database copy pool for, using FROM DUMP,

DUMPCLASS in, 457–458DFSMS concurrent copy and, 450directory copying for, 453–454directory, 467disaster, 468–471. See also disaster recoveryDISPLAY LOG in, 440DSN1LOGP and, 422DSNTIJUZ in, 470DUMP, FROM DUMP in, 466error pages, 456exception tables and, 464failback and, 11, 458–459Fast Log Apply (FLA) in, with RECOVER, 459–460FlashCopy backup and, 462–463FLASHCOPY option for, 451full copy in, 438Hierarchical Storage Management (HSM) in, 464High Availability Disaster Recovery (HADR), 10–11,

13–17ICF catalog in, 470image copies in, 442–454, 469. See also image copiesincremental copy, 438index copies in, 453index, 456inline copies and, 449–450items needed for, 469–470large objects (LOBs), 472libraries in, 470LIST COPYPOOL in, 466LISTCAT in, 470LOAD REPLACE, 464, 467LOB tables, 458log data sets in, archive, 439log for, DSN1LOGP and, 422, 439–442Log Record Sequence Number (LRSN) in, 439, 509–510,

510LOGAPPLY in, 455, 456, 457, 459MERGECOPY in, 451–452, 452t, 452minimizing data loss through, 471MODIFY and, 442MODIFY RECOVERY and, 414, 415–416, 415multiple object, 456–457, 457NOT LOGGED tables and, 458object-level, with system-level backups, 466–467objects to be copied for, 468outage minimization and, URCHKTH and URLGWTH,

478pages, 456Parallel Sysplex and, 439partitions, 456

pending conditions and, 461–462Point-in-Time (PIT), 382, 460–462, 472point of consistency in, using QUIESCE, 438, 454–455,

454postponed units of recovery, with RECOVER

POSTPONED and LBACKOUT, 478–479, 478, 479

preparing for recovery plan, 468QUIESCE in, 472REBUILD INDEX and, 461–462, 467rebuild pending in, 461–462RECOVER in, 455–460, 457, 462–467Recover Pending (RECP) in, 464recovery to current in, 438recovery to end of log in, 438RECOVERYDDN and RECOVERYSITE in, 469redo records in, 439Relative Byte Address (RBA) in, 439REORG in, 467REPAIR in, 464REPORT in, 453REPORT RECOVERY in, 459RESET INDOUBT in, 480, 480restarting DB2 in, 474–480. See also restarting DB2RESTORE in, 455, 457RESTORE SYSTEM in, 464, 465, 466RESTOREBEFORE in, 461ROLLBACK and, 458SHRLEVEL in, 454, 455, 472Storage Management Subsystem (SMS) in, 464SYSCOPY and, 442, 452, 453, 455, 459, 460, 469SYSLGRNX and, 441–442, 453, 459system-level backup and, 464–467system-period temporal tables and, 463table space, 455–456table spaces offl ine in, 471TABLESPACESET in, 453threads affected by failure and, viewing, DISPLAY

THREAD, 479–480, 480TOLOGPOINT in, 460–462Tracker Site and, 473–474, 473undo records in, 439Unit of Recovery (UR) and, 438–439, 580Unit of Recovery ID (URID) and, 422unit of work in, 438–439VOL-SER numbers and, 459XML objects and, 472XML table spaces and, 458

recovery log manager, 40recovery manager, 40recovery to current, 438recovery to end of log, 438RECOVERYDDN, 357, 380, 469RECOVERYSITE, 469RECP. See Recover Pending (RECP)REDO, 357redo records, 439

976 Index

reference, correlated (subqueries), 304REFERENCES, 110t, 116t, 120t, 121treferences/sources for book, 940REFERENCING, 675–676, 675referential constraints, 178–180, 179referential integrity, ii, iv

LOAD and, 363–365, 364temporary tables and, 590triggers and, 680, 682UPDATE and, 291

referential relationships, sequence objects and, 602, 602REFP, 428t, 484REFRESH, 214, 632REFRESH DB2, EARLY, 68tREFSHAGE, 61trelated privileges, 114Relational Data System (RDS), 40Relational Database Management System (RDBMS), 4, 18, 242Relational Online Analytical Processing (ROLAP), 589relationships, 238, 251Relative Byte Address (RBA), 439REEASE, 565t, 581, 650–651, 678, 725

distributed data access and, 659locks and, 719

RELEASE SAVEPOINT, 585, 585, 586RELOAD, 375, 379, 381, 384REMARKS, 751tremote access

access control, 98Resource Access Control Facility (RACF) and, 98, 100,

101remote queries, 656–661remote requests, 642remote servers, 83, 641Remote Unit of Work (RUW), 642RENAME, 653RENAME INDEX, 235, 235RENAME TABLE, 184renaming columns using SELECT ORDER BY, 272, 273REOPT, 565t, 574, 576, 825REOPTEXT, 61treordered row format, iii, 161REORG, v, 111t, 121t, 122t, 194, 197, 355, 374–397, 420, 433,

435, 443, 467Access Method Services (AMS) and, 381ALTER TABLE and, 392–393analyzing physical organization of data for, 374AUTOESTSPACE and, 377BUILD in, 375, 379, 388catalog/catalog queries and, 389–391, 395–396CLUSTERRATIO and, 395COPYDDN and, 380DASD space, 390data maintenance and, 411DBA analysis to determine, 395DEADLINE parameter for, 386DELAY in, 385, 386

DFSORT and, 375, 379, 382directory, 395–396DISCARD in, 374, 387–388, 388DROP TABLE and, 376FAROFFPOS/NEAROFFPOS and, 395FASTSWITCH in, 381–382freespace (FREEPAGE/PCTFREE) in, 375hash-organized table spaces and, 377indexes and, 394, 388–391, 389, 390, 391INDREFLIMIT for, 391–392, 391, 392inline statistics during, 386–387LEAFDIST and, 395LEAFDISTLIMIT for, 392, 392LOB table spaces and, 377–378, 394LOBs and, 376logging, log iterations and, controlling, 380, 384–386,

388LOG phase in, 375, 388long log conditions and, controlling, 385, 386mapping table in, 382–384, 383, 384MAXRO in, 384–385NEARINDREF/FARINDREF and, 395OFFPOSLIMIT trigger for, 391–392, 391, 392Online Reorganization (OLR) with, 380–381parallel index build and, 379–380partition rebalancing using, 396–397, 396partitioned table spaces and, 376–377, 377pending defi nition change and, 393phases in, 375–376Point-in-Time (PIT) recovery and, 382read/write online reorgs and, 381, 382read-only OLR and, 381Real Time Statistics (RTS) and, 395RECOVERYDDN and, 380RELOAD in, 375, 379, 381, 384RUNSTATS and, 374, 386–387segmented table spaces and, 376SHRLEVEL and, 375–378, 378, 380–381, 393SORT phase in, 375, 379SORTBLD phase in, 375, 379, 384SORTDATA in, 378–379, 379, 382SORTKEYS in, 379statistics and, 408, 410STATISTICS and, 380, 386–387, 386SWITCH phase in, 376, 381, 382, 388SYSINDEXPART catalog and, 394SYSTABLEPART catalog and, 393table spaces and, 201, 206, 210, 375–378, 389–391, 389,

390, 391, 392–394tables and, 390, 390, 392–393triggering, 389–395UNLOAD EXTERNAL option in, 374UNLOAD phase in, 376, 381, 384, 387–388, 388UTILINT phase in, 376, 388UTILTERM phase in, 376, 388when to run, 395

reorganization, online, 12–17. See also REORG

Index 977

REORP, 429tREPAIR, 72, 111t, 121t, 122t, 134, 417–418, 464, 474REPEAT, 634repeatable read isolation, 726REPLACE, 128t, 293, 358–360, 359replication, 8, 16, 17

DB2 Connect and, 24, 25DB2 for Linux, UNIX, Windows (LUW) and, 11homogenous, 8, 16, 17homogenous Q, 11, 16, 17homogenous SQL, 13, 14, 15

Replication Center, 33REPORT, 357, 453REPORT RECOVERY, 459REPORTONLY, 448–449, 448RESET, rotating partitions and, 198–199, 199RESET GENERICLU, 68tRESET INDOUBT, 68tRESET INDOUBT, 480, 480residual predicates, 344–345Resource Access Control Facility (RACF), 46, 97, 98, 100,

101, 132resource limit facility (governor), 834Resource Recovery Services (RRS), 43–46Resource Recovery Services Attachment Facility (RRSAF)

CURSOR WITH HOLD in, 541–542, 542RESOURCE TIMEOUT, 732–734response time, vRESTART, iv, 61tRESTART LIGHT, 499restart. See recovery and restartrestarting DB2, 474–480

Automatic Restart Manager (ARM) in, 492checkpoint intervals in, SET LOG in, 477, 478conditional, 475CRESTART in, 475, 475data sharing and, 476NOT LOGGED tables and, 476outage minimization and, using URCHKTH and

URLGWTH, 478postponed units of recovery, using RECOVER

POSTPONED and LBACKOUT, 478–479, 478, 479RESET INDOUBT in, 480, 480SET LOG SUSPEND/RESUME in, 476stopping, 474threads affected by failure and, viewing, DISPLAY

THREAD, 479–480, 480RESTORE, 8, 455, 477RESTORE SYSTEM, 464–466RESTORE_RECOVER_FROMDUMP, 61tRESTORE_TAPEUNITS, 61tRESTOREBEFORE, 461RESTP, 429t, 430RESTRICT, 410, 627RESTRICT ON DROP, 191restricted systems, distributed data access and, 653restriction

restricting rows from table using SELECT, 261–262, 261, 262

restricting rows using multiple conditions, 262, 262restrictive status, iv

resolving, using DISPLAY DATABASE, 424–426, 424, 425, 426, 426–429t

result sets/result tables, 257, 536DRDA and, 621dynamically identifying, using DESCRIBE CURSOR, 623,

623dynamically identifying, using DESCRIBE PROCEDURE,

622–623, 623ODBC and, 621SQLDA and, 623stored procedures and, 621–623, 622, 623WITH RETURN clause and, 621, 622, 622

RESUME, 359RESYNC, 61tretained locks, 498–499RETLWAIT, 61tRETURN, 634RETURNS clause, 693, 695RETVLCFK, 61tREVOKE, 101, 113–114, 113, 114, 115t, 117t, 121t, 128t, 129,

133, 134t, 474authorities, 124, 124binding/rebinding and, 572coordinating updates to distributed data with, 653

REVOKE_DEP_PRIVILEGS, 61tREXX, 616

distributed data access and, 643host structures and, 520SQL application programming and, 518stored procedures and, 619, 630, 634Workload Manager (WLM) and, 630

RGFCOLID, 61tRGFDBNAM, 62tRGFDEDPL, 62tRGFDEFLT, 62tRGFESCP, 62tRGFFULLQ, 62tRGFINSTL, 62tRGFNMORT, 62tRGFNMPRT, 62tRID. See Row Identifi er (RID) poolright outer join, 326–327, 326RLF, 62tRLFAUTH, 62tRLFERR, 62tRLFERRD, 62tRLFTBL, 62tROLB, 727, 728ROLE AS OBJECT OWNER, 106–107, 127, 130roles, v, 144, 684

auditing, 135CREATE, 153ownership of, and privileges, 128t

978 Index

trusted contexts and, 102, 106rollback and ROLLBACK, ii, iv, 458, 541, 577–579, 580–585,

580, 582–585locks and, 727, 728, 734ODBC and, 549sequence objects and, 600, 601stored procedures and, 624triggers and, 679

rotating partitions, 198–199, 199ROUNDING, 565tROUTCDE, 62troutines, privileges for, 113tROUTINE_ID, 755tROW CHANGE TIMESTAMP, 604row functions. See scalar functionsRow Identifi er (RID) pool, v, 88, 866–868row identifi er data type, 156tROWID, 156t, 168–169, 179, 822

access paths and, 761, 762LOAD and, 365–366temporary tables and, 590

rows, iii, 145access control for, 6ATOMIC/NOT ATOMIC and, 595authorization at level of, vbasic row format for, 161begin/end timestamps for, 177change timestamps for, 177, 177compression of, 9–10, 13, 16, 17DDF and, for multi-row FETCH, 594deleting all, using TRUNCATE, 294, 295direct access in, 168, 761expressions in, 301FETCH in, 591–592, 591, 592FOR EACH ROW and, 674fullselect of, 292functions in. See scalar functionsID, iii. See also ROWIDindirect reference, 160LOAD ordered, 361locks and, 718, 724, 725maximum of, 184, 184tmulti-row FETCH in, 593–594, 593, 594multi-row INSERT in, 594–595, 595multi-row MERGE in, 595–596, 596multi-row operations on, 593–595OPTIMIZE FOR n ROWS in, 656–657, 662, 664permissions for, for access control, 102–103, 103, 104–105,

105preserved row table and, in joins, 320qualifying, 262, 262reordered format for, iii, 161restriction, restricting rows from table using SELECT,

261–262, 261, 262restriction, restricting rows using multiple conditions, 262,

262retrieving multiple, using SELECT INTO, 527, 552

retrieving single, using SELECT, 526–527, 527row expressions in, 336, 336Row Identifi er (RID) pool and, 88ROWID and, 168–169, 365–366rowset cursors and, 545–546, 546SKIP LOCKED ROWS and, 721temporal tables and, 606triggers and, 674UNLOAD and, 372

rowset cursors, 545–546, 546RPG, 7RRF, 62tRRS, 834RRSAF, 97RRULOCK, 62t, 730tRuby, 19RUN, 66t, 559RUNSTATS, v, 82, 355, 374, 397–411, 399, 432, 433, 434, 474,

561, 806, 807, 809, 811, 812, 825. See also statisticsaccess during, 399autonomic statistics and, 814–816catalog and, 404catalog updates and, 399, 399COLGROUP and, 401–402compression, 403–404data maintenance and, 411frequency distribution and, 400–402, 401t, 401, 402, 402tFREQVAL and, 402, 402HIGHVALUE, LOWVALUE, QUANTILENO in, 403histograms and, 402–403historical statistics with, 405–406, 406inline statistics and, 404–405, 405LOAD and, 367LOB table spaces and, 406MODIFY STATISTICS utility and, 406PAGESAVE in, 404PCTROWCOMP in, 404profi les in, 406–407, 407, 815–816REORG and, 386–387sampling with, 400, 400SHRLEVEL and, 399SQL cache invalidation using, 405statistics and, 408, 410SYSCOLDIST and, 400–402, 401SYSCOLDISTSTATS and, 402, 402tSYSCOLUMNS and, 400–402, 401XML table spaces, 406

Ssampling, RUNSTATS and, 400, 400SAP, 18sargable predicates, 344–345SASS, 97SAVEPOINT, 580–586, 580, 581, 582tsavepoints, 577, 580–586, 580, 609, 611

distributed environments and, CONNECT statement for, 586

Index 979

DYNAMICRULES and, 581establishing, using UNIQUE, 581–582, 581RELEASE and, 581releasing, using RELEASE SAVEPOINT, 585, 585, 586restoring to, 582–585, 582–585ROLLBACK and, 581, 582–585, 582–585SET SAVEPOINT in, 586TO SAVEPOINT in, 582–585, 582–585

scalability, 7pureScale and, 10, 15–17

scalar functions, 275scalar-fullselect, 257scans

IN-list index scan and, 821–822, 821limited partition, 760–761, 761matching index, 819, 820merge scan join in, 763–764non-matching index, 757, 821range-list index, 758

SCCSID, 62tschemas, iii, 151, 684, 685–686

CURRENT PATH register and, 685–686CURRENT SCHEMA, 686dimension tables in, 765fact table in, 765names for, 685PATH bind option and, 685privileges for, 113t, 685snowfl ake, 766star joins and, 765–768stored procedures and, using CREATE PROCEDURE, 627SYSFUN, 685SYSTOOLS, 685topology check in, 766validation of, in XML, 708–709, 709XML Schema Defi nition (XSD) and, 708

SCM, 16scope, commit scope, 715screening, index, 820, 820SCROLL, scrollable/nonscrollable cursors and, 542–545, 543,

544, 544t, 545SCTO2 directory table, 83tSDSNSAMP, 82search, Net Search Extender, 8–9, 13–17searched delete, 293–294, 294searched updates, 291SECADM, 62t, 114, 117t

managing and separating authorities for, 123–124row and column access control in, 103–105

SECADM1_TYPE, 62tSECADM2, 62tSECADM2_TYPE, 62tSECMAINT, 138t, 140tSECOND, 168second normal form (2NF), 240–241, 241SECQTY, 62t, 206–207SECTNOI, 756t

Secure Sockets Layer (SSL), 6, 99–100security, ii, v, 5, 46, 95–144. See also access control

Application Transparent–Transport Layer Security (AT–TLS) and, 99

AS SECURITY LABEL in, 132CICS, 99DB2 for z/OS, 6DB2_SECURE_VAR security function for, 132, 132denial of service attacks and, 100encryption and, 100IMS and, 99Integrated Cryptographic Services Facility (ICSF) and, 100Kerberos security and, 6, 99, 133logical terminals (LTERMs) and, 99multilevel, 132Resource Access Control Facility (RACF) and, 132Secure Sockets Layer (SSL) and, 99–100SEPARATE_SECURITY and, 122–125trusted contexts in, 105–108

SEECT, 121tsegmented table spaces, 149, 193, 194–196, 195t, 195, 376SEGSIZE, 195–196, 195t, 201, 204, 208, 209tSELECT, ii, 27, 110t, 116t, 117t, 118t, 120t, 121t, 122t, 128t,

192, 255–287, 300, 349, 351–353, 474, 528, 528, 696–697, 696, 697, 807, 823access paths and, 758, 759, 759, 761, 762application programs and, INSERT with, 598–599, 599AS keyword and, 274asterisk (*) in, 257–258BETWEEN and, 262, 282, 282Cartesian products and, 262–265, 263–264CASE expression in, 334–335, 334, 335column renaming and, 272, 273columns, selecting from multiple tables using, 262–269comparison operators with, 261correlation names in, 269–270, 269COUNT, 300cursors use and, 536–538, 536, 537, 538data change tables and, 316–317, 317DECLARE CURSOR clause and, 257DELETE and, 294derived columns and, 273–274, 274DISTINCT and, 257, 279–280, 280dynamic SQL and, 547EXCEPT clause and, 257EXPLAIN and, 746FETCH FIRST and, 257, 333, 333, 334FOR SYSTEM TIME, 286, 286FROM and, 257, 264, 267, 268, 267, 285–286fullselects in, 256–257functions and, 275–277GROUP BY and, 256, 257, 277–278, 277, 278grouping values in, 277–278, 277, 278. See also GROUP

BYHAVING and, 257, 279, 279IN and, 262, 285, 285INTERSECT and, 257

980 Index

IS DISTINCT FROM/IS NOT DISTINCT FROM, 284, 285

IS NULL, 282–283, 283joins and, 264, 264–265, 265–269, 266–269LIKE and, 262, 280–281, 280, 281LOB values and, 336locks and, 718, 720, 724, 727–729, 728, 735, 741major components of, 256–257nested table expressions in, 314–315, 313, 314, 315NOT and, 283–285, 283, 284–285ODBC and, 549optimistic locking and, 604, 604OR and, 285, 285ORDER BY and, 256, 257, 270–274, 271, 272, 273, 333,

333, 334ORDER OF clause in, 333, 333, 334permutation, changing column order using, 260, 260–261predicates in, 261–262projection, projecting columns from table using, 259–260,

259–260PureXML usage and, 336–341. See also PureXMLqualifying rows and, 262, 262resource limit facility (governor) and, 834restriction, restricting rows from table using, 261–262,

261, 262restriction, restricting rows using multiple conditions, 262,

262result set or result table from, 257, 536row change timestamps and, 177, 177row expressions in, 336, 336scalar functions (SUBSTR) and, 275–276, 275, 276scalar-fullselect and, 257SELECT INTO and, 316–317, 317, 527, 599, 720select list from, 259, 259select-statement in, 256–257sequence objects and, 599set of values and, using INSERT, 289–290, 290single row retrieval using, 526–527, 527sorting output using ORDER BY. See ORDER BY subqueries using, 257, 303–307, 304, 305, 306, 307subselects in, 256–257table expressions and, 315–316, 315table, retrieving entire table using, 257–259, 257–258, 259temporary tables and, 590TRUE, FALSE, UNKNOWN conditions and, 261UDATE and, 291–292, 292UNION/UNION ALL and, 257, 308–311, 310views and, 211, 295WHERE and, 257, 259, 261, 264, 268, 273–274XMLEXISTS predicate in, 340–341, 340XMLQUERY and, 336–341XPath expressions and, 337–341

SELECT INTO, 527data change tables, 316–317, 317locks and, 720sequence objects and, 599

select list, 259, 259

select-statement, 256–257selective dump, DSN1SDMP (IFC Selective Dump), 423semicolon delimiter, stacking queries, 49sensitivity in cursors, INSENSITIVE/SENSITIVE, 543–544,

543, 544tSEPARATE_SECURITY, 62t, 122, 144SEQCACH, 62tSEQPRES, 62tsequence, 684

ownership of, and privileges, 127, 128t sequence objects, iii, 152, 191–193, 599–603

ALTER and, 155, 193, 600COMMIT and, 601CREATE and, 153, 599, 600CREATE SEQUENCE and, 192, 192cursors and, 601, 601, 602DROP and, 193, 600FETCH and, 601, 602identity columns and, 192, 602–603, 603tINSERT and, 599, 602, 602NEXT VALUE FOR (NEXTVAL) in, 192–193, 599–603,

600, 601PREVIOUS VALUE FOR (PREVAL) in, 192–193,

599–603, 600, 601privileges for, 113tprogramming with, 599–602referential relationships and, populating, 602, 602ROLLBACK and, 600, 601SELECT and SELECT INTO with, 599SET and, 599UDPATE and, 599using, with NEXTVAL and PREVVAL, 192–193, 599–603,

600, 601VALUES/VALUES INTO and, 599

sequential detection (dynamic prefetch), 760Sequential Least Recently Used (SLRU) pages, 857sequential prefetch, 760Sequential Prefetch Threshold (SPTH), 863serialization, 715SERVAUTH, 105, 107tservers, 1–2, 35

application, 641application, vs. local DB2, 83Data Server Client, 19–22Data Server Driver for JDBC and SQLJ, 20, 21Data Server Driver for ODBC and CLI, 20–22Data Server Driver Package and, 20–22Data Server Driver, 19Data Server Runtime Client, 19–22DB2 application drivers, 20–21DB2 Connect, 19, 22–25DB2 for z/OS, 4, 242instance merge modules, DB2, 21local DB2 and, 83non-DB2 instance merge modules, 21remote, 83, 641

service controller, 40

Index 981

Service Oriented Architecture (SOA), 2, 25SESSION, 589–590SET, 292, 527, 527

large objects (LOBs) and, 705sequence objects and, 599

SET ARCHIVE, 68tSET CONNECTION, 654SET CURRENT DEGREE, 825, 830SET CURRENT PACKAGESET, 830SET CURRENT SQLID, 135tset functions. See column functionsSET LOG SUSPEND/RESUME, 476SET LOG, 68t, 477, 478SET NULL, 568SET SAVEPOINT, 586SET SYSPARM, 53–54, 54, 68tShare (S) locks, 722, 724, 727Share with Intent Exclusive (SIX) locks, 722, 723Shared Communication Area (SCA), 488–489, 509, 511, 512Shared Data Architecture (SDA), 487shredding, ii, 341SHRLEVEL, 362, 454–455, 472, 482

image copies and, 449, 450REORG and, 375, 376, 377–378, 378, 380–381, 393RUNSTATS and, 399

SIGNAL, 679SIGNAL SQLSTATE, 679SIGNAL/RESIGNAL, 634signaling XCF services, 493–494signed numeric data type, 156tsimple predicates, 344simple table space, 149, 193, 194Single-Byte Character Set (SBCS), 161single-precision fl oating point. See REALSITETYP, 62tSJTABLES, 63t, 766Skeleton Cursor Table (SKCT), 87, 716Skeleton Package Table (SKPT), 87, 716skeleton pool, 87SKIP LOCKED DATA, 720, 721, 737, 743SKIP LOCKED ROWS, 721SKIPUNCI, 63t, 730tslash (/) character, XPath expressions, 338–341slash slash (//) character, XPath expressions, 338–341SMALLINT, 157, 172t, 174SMF89, 63tSMFACCT, 63tSMFCOMP, 63tSMFSTAT, 63tSMSDCFL, 63tSMSDCIX, 63tsnowfl ake schemas, 766SOA. See Service Oriented Architecture (SOA)Solaris, 2, 14SolidDB, 2SOME, 304SORT, ii

access paths and, 761–762LOAD and, 357REORG and, 375, 379

SORT BY, 300sort key tables, 795, 795–796tsort pool, v, 88–89, 494, 868sort tables, 793, 793–794tSORTBLD, 357, 375, 379, 384SORTC_GROUPBY, 750tSORTC_JOIN, 750tSORTC_PGROUP_ID, 752tSORTC_UNIQ, 750tSORTDATA, 378–379, 379, 382sorting

using EXPLAIN, 747indexes to avoid, 824–825, 824, 825using ORDER BY, 271, 272, 273sort pool in, 88–89tournament, 88

SORTKEYS, 368, 369, 379SORTN_GROUPBY, 750tSORTN_JOIN, 750tSORTN_ORDERBY, 750tSORTN_PRGROUP_ID, 752tSORTN_UNIQ, 750tSORTTC_ORDERBY, 750tsourced UDFs. See user-defi ned functions (UDFs)sparse index, nested loop join with, 763Spatial Extender, 9, 13–18, 31SPT01 directory table, 82tSPUFI. See SQL Processing Using File Input (SPUFI)SPUFI command, 66tSQL, ii, vi, 2, 19, 26, 27, 142, 145, 192, 244, 303–354. See also

application programs and SQLALTER in. See ALTERapplication program use of, 517–554. See also application

programs and SQLattachment facility and, 42BIND in. See BINDbinding/rebinding and, 555–560cache invalidation in, using RUNSTATS, 405calls in, 612CASE expression in, 334–335, 334, 335catalog table access using, 74clone tables and, 151CLOSE CURSOR in, 541, 541communicating with DB2 through, methods for, 517concurrency, claims and drains for, 731–732, 731tCREATE in. See CREATECURSOR WITH HOLD in, 541–542, 542, 552, 554cursors in, 536–538, 536, 537, 538, 542–546, 542t, 552.

See also cursors, 542data change tables and, 316–317, 317Data Control Language (DCL) in, 145Data Defi nition Language (DDL) in, 145, 152Data Manipulation Language (DML) in, 145Database Request Module (DBRM) and, 746

982 Index

DB2 Connect and, 24DB2 for z/OS and, 4, 5DB2 Interactive (DB2I) and, 47DCLGEN and, 519, 524–526, 524, 525–526DECLARE CURSOR in, 536, 536DECLARE in. See DECLAREdeferred embedded, 546–547DELETE in. See also DELETE delimiting, using EXEC SQL and END-EXEC, 518, 518distributed data access and, 84, 641DROP in, 152, 154, 154dynamic. See dynamic SQLembedded dynamic, 546excepts/EXCEPT/EXCEPT ALL and, 311, 312EXEC SQL and END-EXEC in, 518, 518executing statements in, 538–546execution validation in, 528–535EXPLAIN and, 746, 747, 825–827expressions in, common table, 315–316, 315external stored procedures in, 634–635FETCH in. See FETCHfi ltering in, 343–346FROM clause in, 147functions and, 275GET DIAGNOSTICS in, 532–535, 532–535t, 546, 554homogeneous replication in, 13–15host arrays in, 523–524, 523, 524host structures in, 520, 523, 523host variables and, 520–521, 521indicator variables in, 521–522, 522INSERT in. See INSERTINSTEAD OF in, 537interactive, 546intersects/INTERSECT in, 312, 313Java Database Connectivity (JDBC) for, 517–518, 547,

549–550joins and, 317–333. See also joinslocks and, 727MERGE in. See MERGEnested table expressions in, 313–315, 313, 314, 315Net Search Extender and, 8–9, 13–17Online Analytical Processing (OLAP) and, 341–343, 342,

343OPEN CURSOR in, 537–538, 538Open Database Connectivity (ODBC) for, 517–518, 547,

548–549Optim Development Studio and, 28–29, 28Optim pureQuery Runtime and, 29ORDER BY in, 333, 333, 334ORDER OF clause in, 333, 333, 334orthogonality of, 257performance and, 746predicates and, 343–346, 346–348tPureQuery and, 549–550PureXML and, 336–341. See also PureXMLQuery Management Facility (QMF) and, 31–32replication, homogeneous and, 8, 16, 17

result sets in, 536result tables in, 536retrieving and manipulating objects with, 255–354row expressions in, 336, 336rowset cursors in, 545–546, 546scrollable/nonscrollable cursors and, 542–545, 543, 544,

544t, 545SELECT. See SELECTSET in, 527, 527SQL communication areas (SQLCA) in, 528, 528–531tSQL Procedure Language for, 632–635SQL Processing Using File Input (SPUFI) and, 49–50, 49SQLCODE in, 531SQLJ and, 517–518, 549–550SQLSTATE in, 531static, 517, 546, 554stored procedures and, 613, 632–635. See also stored

proceduressubqueries in, 303–307, 304, 305, 306, 307table defi nitions in, DECLARE, 518–528, 519table functions in, 695–696, 695, 696temporary tables and, 589, 590trusted contexts and, 106UNION/UNION ALL and unions in, 308–311, 310UPDATE in. See UPDATEuser-defi ned functions (UDFs) and, 690, 693–695, 693,

694–695. See also user-defi ned functions (UDFs) view defi nitions in, using DECLARE, 518–528, 519Visual Explain and, 26–27WHERE in. See WHEREXMLEXISTS predicate in, 340–341, 340XMLQUERY and, 336–341XQuery and, 340–341

SQL API, 2SQL Communication Areas (SQLCAs), 528, 528–531t, 556,

557SQL Procedure Language, 632–635, 632SQL Processing Using File Input (SPUFI), 49–50, 49, 125

commands in, 65DB2 Interactive (DB2I) and, 47EXPLAIN and, 747stored procedures and, 617

SQL scalar UDFs. See user-defi ned functions (UDFs)SQL Server, DB2 Connect and, 25SQL(xxx), 651, 655SQLADM, 114, 118t, 119t, 808, 826SQLCA. See SQL Communication Areas (SQLCAs)SQLCODE, 531

ODBC and, 549stored procedures and, 620

SQLDA, 623SQLDELI, 63tSQLERROR, 565t, 573, 652, 655SQLID, 102, 102, 123, 126, 135tSQLJ, 19, 20, 21, 517–518

DB2 Connect and, 25Optim Development Studio and, 28–29

Index 983

SQL application programming and, 549–550stored procedures and, 635traces and, 834

SQLRULES, 565t, 652–653, 654SQLSTA, 531SQLSTATE, 531

ODBC and, 549stored procedures and, 620user-defi ned functions (UDFs) and, 692

SRTPOOL, 63tSSID, 63tSSL. See Secure Sockets Layer (SSL)stacking queries, semicolon delimiter, 49stage 1 predicates, 344–345, 346–348t, 818, 818stage 2 predicates, 344–345, 818–819, 819standalone utilities, 419–424. See also utilitiesstandby, in recovery, 10star joins, 765–768STARJOIN, 63t, 766START, iv, 118t, 128t, 855START DATABASE/STARTDB, 68t, 111t, 118t, 119t, 121t,

430, 430, 512START DB2, 68t, 93START DDF, 68tSTART FUNCTION, 701START FUNCTION SPECIFIC, 68t, 701–702, 701START PROCEDURE, 68t, 628, 629, 631START PROFILE, 68tSTART RLIMIT, 68tSTART TRACE, 68t, 136, 136, 835, 837–839, 838, 840START WITH n, 175tSTATCLUS, 63tstatement table, EXPLAIN and, 746statement triggers, 674, 675statements

DSN_STATEMENT_CACHE_TABLE and, 775, 775–778tDSN_STATEMENT_TABLE and, 770, 771–773tgranularity of, 674

STATHIST, 63tstatic binding, 558static SQL, 517, 546, 554. See also dynamic SQL; SQLSTATIME, 63tstatistics, iv, 91, 397–411, 875, 877. See also RUNSTATS

autonomic, 814–816buffer pool, 867–868catalog. See catalog statisticscollection of, 408compression, 403–404COPY and, 408, 410DSNACCOR for, 410DSNACCOX for, 410EXTENTS and, 410externalizing, using STATSINST, 409frequency distribution, 400–402, 401t, 401, 402, 402thistogram, 402–403, 811–812, 812historical, 405–406, 406inline, 367, 367, 404–405, 405

INSERT and, 410LOAD and, 367, 367, 408, 410LOAD REPLACE and, 408LOB table space, 406locks and, 738–739, 738–739MODIFY STATISTICS and, 414, 416–417, 417production environment modeling using, 411, 813–814,

813–814tprofi les of, RUNSTATS and, 406–407, 407real-time, 408REBUILD and, 408REORG and, 408, 410RESTRICT and, 410RUNSTATS and. See RUNSTATSsampling, 400SQL cache invalidation and RUNSTATS in, 405STATSINST, 409STOSPACE utility for, 407, 407SYSINDEXSPACESTATS for, 408, 410SYSTABLESPACESTATS for, 408, 409table space, 434trace, statistics trace, 835, 851–852ttriggers and, 680user-defi ned functions (UDFs) and, 702, 702XML table space, 406

STATISTICS, in REORG, 380, 386–387, 386Statistics Advisor, 30STATROLL, 63tSTATS, 111t, 121tSTATSINT, 63t, 409status monitoring XCF services, 494STAY RESIDENT, 616STDDEV, 277STDSQL, 63tstealing method, buffer pools and, with PGSTEAL, 862STMTTOKEN, 755tSTOBDB, 121tSTOGROUP, 116t, 120tSTOP, iv, 118t, 128t, 429t, 855STOP DATABASE/STOPDB, 68t, 111t, 119tSTOP DB2, 68tSTOP DDF, 68tSTOP FUNCTION SPECIFIC, 68t, 702, 702STOP PROCEDURE, 68t, 628, 629, 631STOP PROFILE, 68tSTOP RLIMIT, 68tSTOP TRACE, 68t, 136, 136, 839STOPALL, 112t, 118t, 119tstopping DB2, 474STOPSPACE, 113tstorage

in DB2 for z/OS, 4, 242buffer pools in, 86–87Data Facility Storage Management Subsystem (DFSMS)

and, 47Hierarchical Storage Management (HSM) in, 464INLINE option for, 186–188, 187–188

984 Index

installation, migration and, 50storage groups and, 151, 236–237, 236–237Storage Management Subsystem (SMS) in, 464stored procedures and, 616system-managed (SMS), 236virtual, v

storage groups, 151, 236–237, 236–237ALTER for, 155CREATE for, 153ownership of, and privileges, 128t

Storage Management Subsystem (SMS), 7, 464storage manager, 40stored procedures, 613–639, 684

accessing external resources with, 625address space for, 38, 616ALLOW DEBUG MODE and, 628ALTER PROCEDURE in, 155, 617, 623–624, 626,

626ASUTIME parameter in, 630benefi ts of, 614–615CICS and, 615CLIST and, 634COMMIT in, 623, 624COMMIT ON RETURN in, 623–624CREATE PROCEDURE and, 153, 617–620, 618, 619,

623–626, 626, 627, 631, 631, 633CURRENT PATH and, 627DB2-supplied, 625, 636DBINFO for, 620–621, 620–621tDDL and, 634debugging, 635DEFINER, 625defi ning, using CREATE PROCEDURE, 626, 626DESCRIBE PROCEDURE in, 622–623, 623developing SQL type, 634diagnostic information on, using CEEDUMP, 632DISPLAY PROCEDURE and, 628, 628DISPLAY THREAD and, 628distributed data access and, 655–656DRDA and, 621DSNACCOX DB2-supplied, 636DSNACICS DB2-supplied, 636DSNAEXP DB2-supplied, 636DSNAIMS DB2-supplied, 636DSNTPSMP and, 634, 635execution environments for, 628–632EXTERNAL option and, 617, 634, 639external SQL, 613, 634–635external, 613FENCED option in, 617, 634, 638, 639IMS and, 615invoking, using CALL, 614, 614, 617, 617, 623, 624, 627,

629Java and, 619, 635JCL and, 634Language Environment for, 616, 639manager for, 40

managing environments for, using VARY WLM, 631–632, 631, 632

memory and, STAY RESIDENT option for, 616MQSeries and, 635native SQL, 613, 617–625nesting, 624–625network improvement using, 614–615, 614NUMTCB and, 639ODBC and, 621Optim Development Studio and, 617, 632, 633, 634,

635ownership of, and privileges, 127, 128t parameters for, using PARAMETER STYLE, 617–621,

617, 618, 619PL/1 and, 638program type specifi ed for, using PROGRAM SUB TYPE

in, 631, 631QUALIFIER and, 627removing, using DROP PROCEDURE, 627, 627RESTRICT keyword in, 627result sets and, 621–623, 622, 623REXX and, 619, 630, 634ROLLBACK in, 624schema qualifi cation and, using CREATE PROCEDURE,

627SECURITY keyword, external resources and, 625SPUFI and, 617SQL Procedure Language for, 632–635SQLCODE and, 620SQLJ and, 635SQLSTATE and, 620START/STOP PROCEDURE and, 628, 629, 631storage of, in SYSROUTINES and SYSPARMS, 626SYSFUN and, 627tables and, 637triggers and, 682, 683, 683Unit of Work (UOW) in, 623–625USER in, 625VSAM and, 615WLM_REFRESH DB2-supplied, 636WLM_SET_CLIENT_INFO DB2-supplied, 636Workload Manager (WLM) and, 616, 625, 628–632, 637.

See also Workload Managerwriting, 616XDBDECOMPXML as, 341XML and, 635, 708XSR_xxx DB2-supplied, 636

STORMXAB, 63tSTORTIME, 63tSTOSPACE, 116t, 407, 407streaming

FETCH WITH CONTINUE for, 706, 710–711large objects (LOBs) and, 706, 710–711XML and, 710–711

string data type, 156tstring functions, 276strong typing, 170

Index 985

STRONGARM/XSCALE, 18structure tables, DSN_STRUCT_TABLE and, 782, 782–784tStructured Query Language. See SQL, ii, 1subprograms, 639subqueries, 257, 303–307, 304, 305, 306, 307

correlated, 305–307, 306, 307EXISTS, 304–307, 307FROM clause and, 304global optimization and, 769IN, 304–307, 307interpreting access for, using EXPLAIN, 768number of tables supported in, 318pruning of, 310, 310quantifi ed predicates and, 304reference within, correlated (correlation predicate), 304WHERE clause and, 307

subselects, 256–257SUBSTR, 275–276, 275, 276subsystems, 40, 41

access control and, 97–100data sharing and, 485PERMIT in, 97, 97privileges for, 111–113tResource Access Control Facility (RACF) and, 97, 98, 100,

101SUM, 279, 279, 335, 335Sun Unix, 18SUPERRS, 63tSupply Chain Management (SCM), 2, 3SVOLARC, 63tSWITCH phase, in REORG and, 376, 381, 382, 388SWITCH, 565tSybase, 18, 25Symbian, 18, 19Symmetric Multiprocessing (SMP), 7SYNCH, locks and, 727, 728synchronous reads/writes, 857–858SYNCVAL, 63tsynonyms, 148

CREATE, 153ownership of, and privileges, 126–127, 128t

SYSABAUTH, 131tSYSADM, 63t, 98, 114, 115t, 117t, 129, 143, 144, 293, 808,

826authorizations and, 102binding/rebinding and, 567managing and separating authorities for, 122–124row and column access control in, 104, 105

SYSADM2, 63tSYSADMIN, 138t, 140tSYSAUDIPOLICIES, 75tSYSAUTOALERTS, 75tSYSAUTOALERTS_OUT, 75tSYSAUTORUNS_HIST, 75tSYSAUTORUNS_HISTOU, 75tSYSAUTOTIMEWINDOWS, 75t, 816SYSAUXRELS, 75t

SYSCHECKDEP, 75t, 182SYSCHECKS, 75t, 182SYSCHECKS2, 75tSYSCOLAUTH, 75tSYSCOLAUTH, 131tSYSCOLDIST, 76t, 400–402, 401, 416, 809, 812t, 813tSYSCOLDIST_HIST, 76tSYSCOLDISTSTATS, 76t, 402, 402t, 812t, 812, 813tSYSCOLSTATS, 76t, 812tSYSCOLUMNS, 76t, 400–402, 401, 406, 416, 809, 812t, 813t

table- vs. index-controlled partitioning and, 200SYSCOLUMNS_HIST, 76tSYSCONSTDEP, 76tSYSCONTEXT, 76t, 131tSYSCONTEXTAUTHIDS, 76t, 131tSYSCONTROLS, 131tSYSCOPY, 76t, 415, 452, 453, 455, 459, 460, 467, 469

image copies and, 445, 447logging and, 442

SYSCTRL, 114, 116t, 123–124, 129, 808SYSCTXTTRUSTATTRS, 76t, 131tSYSDATABASE, 76t, 82SYSDATATYPES, 76t, 689SYSDBASE, 396SYSDBAUTH, 76t, 131tSYSDBDXA, 467SYSDBRM, 76tSYSDEPENDENCIES, 76tSYSDUMMY1, 77tSYSDUMMYA, 77tSYSDUMMYE, 77tSYSDUMMYU, 77tSYSENVIRONMENT, 77tSYSFIELDS, 77tSYSFOREIGNKEYS, 77tSYSFUN, 627, 685SYSGROUP, 467SYSIBM, 415, 627SYSIBM.xxx tables. See table names (SYSKEYTARGETS,

SYSTABLES, etc.)SYSINDEXES, 77t, 406, 416, 812t, 813tSYSINDEXES_HIST, 77tSYSINDEXES_RTSECT, 77tSYSINDEXES_TREE, 77tSYSINDEXPART, 77t, 394, 406, 416SYSINDEXPART_HIST, 77tSYSINDEXSPACESTATS, 77t, 408, 410SYSINDEXSTATS, 77t, 406, 416, 812t, 813tSYSINDEXSTATS_HIST, 77tSYSJARCLASS_SOURCE, 77tSYSJARCONTENTS, 77tSYSJARDTAT, 77tSYSJAROBJECTS, 77tSYSJAVAOPTS, 77tSYSJAVAPATHS, 77tSYSKEYCOLUSE, 78tSYSKEYS, 78t

986 Index

SYSKEYTARGETS, 78t, 228, 406, 417, 812t, 814tSYSKEYTARGETS_HIST, 78tSYSKEYTARGETSSTATS, 78t, 812t, 814tSYSKEYTGTDIST, 78t, 228, 406, 417, 812t, 814tSYSKEYTGTDIST_HIST, 78tSYSKEYTGTDISTSTATS, 78t, 228, 812t, 814tSYSLGRNX, 83t, 415, 441–442, 453, 459, 467SYSLOBSTATS, 78t, 406, 416SYSLOBSTATS_HIST, 78tSYSOBJROLEDEP, 78tSYSOPR, 98, 114, 118–119t, 808SYSOPR1, 63tSYSOPR2, 63tSYSPACKAGE, 78t, 396, 561, 569, 668SYSPACKAUTH, 78t, 131tSYSPACKCOPY, 78tSYSPACKDEP, 78t, 694SYSPACKLIST, 78tSYSPACKSTMT, 78tSYSPACKSTMT_STMB, 78tSYSPACKSTMT_STMT, 78tSYSPARMS, 78t, 626SYSPENDINGDDL, 78tSYSPENDINGOBJECTS, 79tSYSPKSYSTEM, 79tSYSPLAN, 79t, 396, 561, 569SYSPLANAUTH, 79t, 131tSYSPLANDEP, 79t, 694Sysplex Failure Management (SFM), 491, 492Sysplex Timer, 492, 493sysplex. See Parallel SysplexSYSPLSYSTEM, 79tSYSPROC, 627SYSPROC.XSR_ADDSCHEMADOC, 708SYSPROC.XSR_COMPLETE, 708SYSPROC.XSR_REGISTER, 708SYSPROC.XSR_REMOVE, 708SYSPUNCH, 372SYSQUERY, 79tSYSQUERY_AUX, 79tSYSQUERYOPTS, 79tSYSQUERYPLAN, 79tSYSRELS, 79tSYSRESAUTH, 79t, 131t, 689SYSROLES, 79t, 131tSYSROUTEINAUTH, 79t, 131t, 689SYSROUTINES, 79t, 626, 689, 703, 814tSYSROUTINES_OPTS, 79tSYSROUTINES_PTREE, 79tSYSROUTINES_SRC, 79tSYSROUTINESTEXT, 79tSYSRTSTS, 467SYSSCHEMAAUTH, 80t, 131tSYSSEQUENCEAUTH, 80t, 131tSYSSEQUENCEDEP, 80tSYSSTAT, 702SYSSTMT, 80t

SYSSTOGROUP, 80tSYSSYNONYMS, 80tSYSTABAUTH, 80tSYSTABCONST, 80tSYSTABLEPART, 80t, 200, 393, 406, 416SYSTABLEPART_HIST, 80tSYSTABLES, 80t, 200, 812t, 814t, 406SYSTABLES_HIST, 80tSYSTABLES_PROFILES, 80tSYSTABLES_PROFILES_TEXT, 80tSYSTABLESPACE, 80tSYSTABLESPACESTATS, 80t, 408, 409SYSTABSTATS, 80t, 406, 417, 812t, 814tSYSTABSTATS_HIST, 80tSystem Catalog, indexing, 233System i, 1, 3, 7, 19, 23System Management Facility (SMF), 834

distributed data access and, 658System Network Architecture (SNA), 84–86

distributed data access and, 643, 646system parameter manager, 40system parameters, locks and, 730, 730t, 741system period, 151–152, 216–219, 285–286, 286, 310–311,

311, 605–607, 605–606System Services Address Space (SSAS), 38, 39System z, 1, 23SYSTEM_LEVEL_BACKUPS, 63tsystem-level backup, 464–467System-Managed Storage (SMS), 236system-period temporal tables, 218, 218–219, 463SYSTKEYTARGETSTATS, 228SYSTOOLS, 685SYSTRIGGERS, 80t, 668, 681, 681SYSTRIGGERS_STMT, 80tSYSTSIN, 44SYSTSSTG, 467SYSTVOL, 467SYSUSERAUTH, 80t, 131tSYSUSERNAMES, 81tSYSUTIL, 418SYSUTILX directory table, 83tSYSVIEWDEP, 81tSYSVIEWS, 81t, 694SYSVIEWS_STMT, 81tSYSVIEWS_TREE, 81tSYSVOLUMES, 81tSYSXMLRELS, 81tSYSXMLSTRINGS, 81tSYSXMLTYPMOD, 81tSYSXMLTYPSCHEMA, 81tSYSXSRCOMPONENT, 81tSYSXSROBJECT HIERARCHIES, 81tSYSXSROBJECTCOMPONENTS, 81tSYSXSROBJECTGRAMMER, 81tSYSXSROBJECTPROPERTY, 81tSYSXSROBJECTS, 81tSYSXSRPROPERTY, 81t

Index 987

Ttable functions, 275TABLE keyword, 314–315, 354table spaces, iv, 12, 149–150, 184–185, 193–210, 251, 467

access paths and, scan for, 758–759advisory reorg–pending (AREO) status in, 210allocation in, using PRIQTY and SECQTY, 206–207altering, using ALTER TABLESPACE to modify, 155, 201,

208, 209t, 209–210, 210benefi ts of, 201binding/rebinding and, 568–569BUFFERPOOL in, 204CHECK, 414, 414COMPRESS in, 204, 207–208, 208creating, using CREATE TABLESPACE, 153, 201,

203–209DEFINE in, 204DROP TABLESPACE to remove, 210, 210DSSIZE in, 203, 205–206, 205t, 209free space in, FREEPAGE and PCTFREE in, 203, 206GBPCACHE in, 204hash, 377LOAD and, load parallelism for partitioned, 370, 370, 393LOB, 150, 193, 202–203, 202, 377–378, 394, 406locks and, 717, 723–725, 741LOCKSIZE in, 204LOGGED/NOT LOGGED in, 204mass delete and, 201MAXPARTITIONS in, 201, 204, 208, 209tMAXROWS in, 204MEMBER CLUSTER in, 209NUMPARTS in, 201, 204, 208, 209toffl ine, 471ownership of, and privileges, 128tpage sizes in, 205, 205tparameters for CREATE TABLESPACE in, 203–204partitioned, iv, 150, 193, 196–200, 376–377, 377, 812–813.

See also partitioned table spaces, 196partitioned tables and, 201partitioning controlled by, 199–200, 199, 200tpending defi nition change and, 393recovery and, 437, 455–456, 471REORG and, 201, 210, 375–378, 389–394, 389, 390, 391restrictions start on, using ACCESS(FORCE), 430, 430segmented, 149, 193–196, 195t, 195, 376SEGSIZE in, 201, 204, 208, 209t, 209simple, 149, 193, 194statistics on, 434storage groups in, 236–237, 236–237TRACKMOD in, 205, 209type of, 208universal, 150, 193, 200–202USING STOGROUP in, 203USING VCAT in, 203, 209XML and, 150, 193, 203, 406

TABLE_DCCSID, 755tTABLE_ENCODE, 755t

TABLE_MCCSID, 755tTABLE_SCCSID, 755tTABLE_TYPE, 755ttable-controlled partitioning, 199–200, 199, 200ttables, ii–iii, 147–148, 256, 609

access paths and, 758–770Advisory Reorg–Pending (AREO) status in, 189aliases and, 148altering, using ALTER TABLE, 155, 189–191, 191,

392–393auditing, 136, 137auxiliary, 147, 186–188, 187–188base, 269binding/rebinding and, 568cache, 746–747Cartesian joins and, 767–768check constraints in, 178, 182–183, 183Check Pending (CHKP) status for, 182clone, iii, 147, 151, 215–216, 463column distribution, 802, 802–803tcolumns in, 245compression of, v, 6constraints in, 178–183, 681–682. See also constraintscopying table defi nitions for, 188, 189correlation names in, 269–270, 269CREATE AUXILIARY TABLE for, 148CREATE GLOBAL TEMPORARY TABLE for,

148creating, using CREATE TABLE, 148, 153, 184–185,

184–186cursor, 87data change, 316–317, 317DB2 for z/OS and, 5declarative referential integrity in, 249denormalization vs. joins in, 331–332dependent, 179, 190descriptions for, sample, 244–245detailed cost, 790, 791–792tdimension, 765direct row access in, 761DROP TABLE to remove, 191exception, 464EXPLAIN, 26expressions in, 315–316, 315external table functions and, 696–697, 696, 697fact, 765fi lter, 789, 789–790tforeign key constraints and, 181FROM clause and, 147function, 746functions in, 275hash access and, 761hash spaces in, 150hash, 220–221, 251history, 148, 152hybrid joins in, 764–765IN-list direct table access for, 758

988 Index

indexes and. See indexeskey target distribution, 803, 804–805tkeys and, 149, 178–180limited partition scanning in, 760–761, 761list prefetch in, 760loading data into, with INSERT, LOAD, DSN1 COPY,

185, 393loading/unloading data to, ivLOCK TABLE in, 586locks and, 717, 719–720, 720, 723–725mapping, in REORG, 382–384, 383, 384Materialized Query Tables (MQTs). See Materialized

Query Tables (MQTs)maximum columns and rows in, 184, 184tmerge scan join in, 763–764Multidimensional Clustering (MDC), 12, 13, 16, 17 nested loop joins in, 762–763nested table expressions in, 313–315, 313, 314, 315NOT LOGGED, 458, 476null-supplying, in joins, 320OLD TABLE clause in, 316–317, 317ownership of, and privileges, 126–127, 128tpackage, 87page range, 797, 797–798tparallel group, 784, 784–786tparallel task, 787, 787–789tparallelism usage and, 768parent-child relationships in, 249, 251partitioning of, 12, 16, 18, 717pending defi nition change and, 393permanent (base), 147permutation in, changing column order using SELECT in,

260, 260–261physical organization of data in, 374plan, 746–748, 748–756tprefetching in, 760preserved row table, in joins, 320primary key constraints and, using CREATE TABLE, 180,

180privileges for, 110tprojection, projecting columns from table using SELECT

for, 259–260, 259–260queries and, 318, 800, 800–801treferential constraints in, 178–180, 179relationships between, 251RENAME TABLE in, 184REORG and, 390, 390, 392–393reorganization of, 12–15restriction, restricting rows from, using SELECT, 261–262,

261, 262result table/result set and, 257, 536SELECT in, to retrieve entire, 257–259, 257–258, 259sequential detection (dynamic prefetch) in, 760sequential prefetch in, 760size of, 5–6skeleton cursor, 87skeleton package, 87

sort/SORT and, 761–762, 793, 793–794tsort key, 795, 795–796tSQL and, using DECLARE, 518–528, 519SQL table function for, 695–696, 695, 696star joins in, 765–768statement, 746stored procedures and, 637structure, 782, 782–784tsynonyms and, 148table space scan for, 758–759table spaces and. See table spacestemporal, iii, 5, 148, 151–152, 216–220, 463, 605–608temporary, iii, 147, 185, 586–591, 611, 639, 649. See also

temporary tablestransition, 676–677, 676, 677triggers and, 667, 670, 681–682unique constraints in, 178unique index check, 765universal, 717UNLOAD and, 372view reference, 798, 798–799tXML, 148

TABLESPACE, 116t, 120tTABLESPACESET, 453TABNO, 749tTAPE, 72target database name, 85Task Center, DB2 and, 33–34Task Control Block (TCB), 46, 629, 837task tables, parallel, 787, 787–789tTBSBP16K, 64tTBSBP8K, 63tTBSBPOOL, 64tTBSBPXML, 64tTBSP32K, 64tTCP/IP, 3, 84, 85, 86

Application Transparent–Transport Layer Security (AT–TLS) and, 99

DB2 Connect and, 24distributed data access and, 641, 643–646High Availability Disaster Recovery (HADR) and, 11Virtual IP Address (VIPA) and, 646

TCPALVER, 64tTCPKPALV, 64tTechnical Library, DB2, 7TEMPLATE, 373, 445templates, for utilities, 70–73, 72temporal tables, iii, 5, 148, 151–152, 216–220, 463, 605–608

application-period, 219, 605, 607–608, 607, 608bi-, 219–220, 220, 608CREATE, 153DELETE, 606, 607period in, 216–217queries to, 606rows in, 606system-period, 218, 218–219, 605–607, 605–606UPDATE, 607, 607

Index 989

versioning of data in, ADD VERSIONING and USE HISTORY, 217, 218

temporary tables, iii, 147, 185, 586–591, 611, 639accessing, with distributed data, 649, 649commit options for, 590–591, 591created (CTT), 587–588, 587creating, 588, 588DECLARE in, 590–591, 591declared (DTT), using DECLARE, 588–591, 589locks and locking in, 590materializing, 588, 588, 591Multidimensional Online Analytical Processing (MOLAP)

and, 589ODBC and, 589QUALIFIER BIND in, 590Relational Online Analytical Processing (ROLAP) and, 589SESSION qualifi er for, 589–590SQL and, 589unsupported objects in, 590VSAM and, 589

TERM UTILITY, 68tTerminal Monitor Program (TMP), 44TERMINATE THREAD, 541third normal form (3NF), 241, 241threads

ACCUMACC and ACCUMID in, 658CMTSTAT and, 658Database Access (DBATs), 658DB2 inactive connection support and, 658DISPLAY THREAD, 479–480, 480, 658, 658distributed, 639inactive, 657pooled, vi, 658temporary tables and, 590Workload Manager (WLM) and, 629, 637

three-part names, 647–649, 648thresholds, performance, vi

CLASST threshold, 502Data Manager Threshold (DMTH), 863Deferred Write Threshold (DWQT), 860–861, 875, 877GBPOOLT threshold, 502Immediate Write Threshold (IWTH), 858, 863–864Sequential Prefetch Threshold (SPTH), 863Vertical Deferred Write Threshold (VDWQT), 875, 877Virtual Pool Parallel Sequential Threshold (VPPSEQT),

860, 862Virtual Pool Sysplex Parallel Sequential Threshold

(VPXPSEQT), 862TIME, 64t, 156t, 165, 167–168, 167, 190, 568Time-Sharing Option (TSO), 6, 37, 43, 97

access control and, 98attachment facility and, 43–44, 44attachment, 41call attachment facility and, 43, 45commands/CLIST in, 65DB2 and, relationship between, 44distributed data access and, 653–654

Interactive System Productivity Facility (ISPF) and, 47interfacing with DB2 through, 47locks and, 727, 728Resource Recovery Services (RRS) and, 43, 45–46traces and, 834

TIMELEN, 64ttimeouts, vi, 91, 586, 732–734, 732ttimer, Sysplex Timer in, 492, 493, 492TIMESTAMP, 156t, 165, 166, 168, 568, 751tTIMESTAMP WITH/WITHOUT TIME ZONE, 165, 190TIMESTAMP_FORMAT, 168TIMESTAMP_ISO, 168TIMESTAMP_TZ, 168TIMESTAMPADD, 168TIMESTAMPDIFF, 168timestamps

row begin/end, 177row change, 177, 177temporal tables and, 605–608. See also temporal tables

Tivoli, 1, 2Tivoli OMEGAMON XE, 831, 832, 833Tivoli Storage Manager, 9Tivoli System Administration, 12Tivoli System Automation, 14–18TNAME, 749t, 756TO SAVEPOINT, 580, 582–585, 582–585TOLOGPOINT, 460–462tools, iitop-down approach to design, 238topology check, 766tournament sort, 88TP Monitor, DB2 Connect and, 24TRACE, 113t, 118t, 119ttraces, iv–vi, 142, 834–854

accounting type, 835–837, 840–842tAUDIT TRACE and, 838audit trace event classes in, 134, 134–135taudit, 133–135, 134–135t, 838, 842tclasses of, 840, 840continuous performance monitoring and, 852–853CREATE/ALTER TABLE and, 838DB2, 834–839detailed performance monitoring and, 853–854details of, 134DISPLAY TRACE and, 839DSN1SDMP (IFC Selective Dump) and, 423exception performance monitoring and, 854Generalized Trace Facility (GTF) and, 834Instrumentation Facility ID (IFCID) and, 834, 838, 839,

840–852tInstrumentation Facility Interface (IFI) and, 837invoking, 839–840locks and, 740, 743MODIFY TRACE and, 839monitor type, 838–839, 843–844tperformance type, 837–838, 845–851tperiodic performance monitoring and, 853

990 Index

START TRACE and, 835, 837–838, 838, 839, 840starting/stopping, 136, 136statistics and, 835statistics type, 851–852tSTOP TRACE and, 839System Management Facility (SMF) and, 834Task Control Block (TCB) and, 837triggers for, 680

Tracker Site recovery, 473–474, 473TRACKMOD, 205, 209, 446TRACSTR, 64tTRACTBL, 64ttransaction boundaries, iitransaction start ID columns, 177–178transition tables, triggers and, 676–677, 676, 677transition variables, triggers and, 675–676, 675Transmission Control Protocol/Internet Protocol. See TCP/IPtrigger packages, binding/rebinding and, 561TRIGGER, 110t, 116t, 120t, 121ttriggers, iii–iv, 667–684, 713, 714

activation (fi ring) of, 668–669after, 669, 671–672, 671–672ALTER for, 155before, 669, 672–673, 672–673binding/rebinding and, 576cascading activation of, 669CASE with, 671, 671catalog information about, in SYSTRIGGERS, 681, 681combinations of, 677–678, 677–678tcreating, using CREATE TRIGGER, 153, 669–671, 671data conditioning with, 668data integrity and, 668data validation and, 668DELETE as, 667, 668deleting packages of, using DROP TRIGGER, 678, 684DML statements and, execution of, 683–684dropping, with DROP TRIGGER, 678, 684granularity of statements and, 674INSERT as, 667, 668INSTEAD OF, 667, 669, 673–674, 673, 674, 684invalidation of, using SIGNAL SQLSTATE or RAISE_

ERROR, 679LOAD and, 667MERGE as, 667monitoring of, 680–681order of execution of, 667–668, 670outside a database use of, 682ownership of, 127packages of, with REBIND TRIGGER PACKAGE, 678performance issues and, 669, 680qualifi er for, with QUALIFIER, 678REFERENCING clause and, 675–676, 675referential integrity and, 680, 682rollbacks and, 679row, using FOR EACH ROW in, 674statement, using FOR EACH STATEMENT in, 674, 675storage of, in SYSPACKAGE and SYSTRIGGERS, 668

stored procedures and, 682, 683, 683table check constraints vs., 681–682tables and, 667, 670temporary tables and, 590text of, 681, 681transition tables and, 676–677, 676, 677transition variables and, 675–676, 675types of, 670UPDATE as, 667, 668user-defi ned functions and, 682–683, 682, 683uses for, 668violations of, 670–671

TRKRSITE, 64tTRUE/FALSE, 261TRUNCATE, 293, 294, 295, 653trust attributes, 107–108ttrusted connections, 106trusted contexts, v, 6, 105–108, 142

ALTER, 155attributes of, 107, 107–108tclient applications supporting, 106CREATE, 153defi ning, 107–108, 107–108townership of, 127, 128t, 130performing tasks on behalf of others in, 108–109, 109ROLE AS OBJECT OWNER in, 106–107roles in, 102, 105–107SERVAUTH and, 105SYSADM or SECADM authority for, 105trusted connections in, 106

TSLOCKMODE, 735, 738, 751tTSO. See Time-Sharing Option (TSO)TSO attachment facility, 41TSQTY, 64tTSTAMP, 64ttuning CPU, vtuning distributed applications, 659tuning. See performance and tuningtwo-phase commit and, 653–654TWOACTV, 64tTWOARCH, 64tTWOBSDS, 64t

UUDATE, 104, 192–193, 599UDT. See user-defi ned data typeUGCCSID, 64tUIFCIDS, 64tUMCCSID, 64tuncommitted read, 726undo records, 439Unicode, 164, 171, 268, 273, 366Unifi ed Debugger, 635UNION_COLNAME_7, 64tunions/UNION/UNION ALL, 257, 308–311, 310, 311, 762UNIQUE, 223, 581–582, 581unique constraints, 178, 253

Index 991

unique index, 226–227unique index check, 765unique key, 248, 227, 246unique where not null indexes, 227Unit of Recovery (UR), 438–439, 580

postponed, with RECOVER POSTPONED and LBACKOUT, 478–479, 478, 479

Unit of Recovery ID (URID), 422Unit of Work (UOW), 438–439, 578, 579, 623–625UNIT/UNIT2, 64tunits of work (UOW), 586Universal Language Interface attachment facility, 41, 42, 46universal table, 717universal table space, 150, 193, 200–202UNIX, 1–3, 6, 9–19

Data Studio, Optim Data Studio and, 27DB2 Connect and, 22–25Optim Database Administrator for, 27–28Optim pureQuery Runtime and, 29Optim Query Tuner and, 29–30performance and, 853Resource Recovery Services attachment facility (RRSAF)

and, 45–46UNIX System Services, 6UNKNOWN, 261Unlimited Edition for System i, DB2 Connect, 23, 25Unlimited Edition for System z, DB2 Connect, 23, 24UNLOAD, 355, 371–374, 372, 705

Basic Sequential Access Method (BSAM) and, 371delimited (COLDEL, CHARDEL, DECPT options),

372–373DSNTIAUL vs., 371FlashCopy and, 371large objects (LOBs) and, 373, 373locks and, 720parallelism and, 373REORG and, 376, 381, 384, 387–388, 388source of data for, 371SYSPUNCH in, 372TEMPLATE and, 373UTILINIT in, 372UTILTERM in, 372XML data and, 373, 373

UNLOCK, 502unqualifi ed names, ownership of, 129–130unqualifi ed objects

binding/rebinding and, 572ownership of, 125–126

UPDATE, ii, 110t, 116t, 117t, 118t, 120t, 121t, 122t, 128t, 135t, 255, 290–292, 291, 292, 299, 474access paths and, 758, 761catalog, 399, 399check constraints and, 182–183constraints and, 291coordinating updates to distributed data with, 653data change tables and, 316–317, 317EXPLAIN and, 746

foreign key constraints and, 182identity columns and, 597–598indexes and, 149labeled duration with, 291large amounts of data for, 292locks and, 718, 720, 721, 724MERGE and, 292multi-row FETCH in, 594, 594ODBC and, 549optimistic locking and, 604, 604positioned update using, 291, 539–540, 540, 543referential constraints and, 180resource limit facility (governor) and, 834row fullselect and, 292scalar-fullselect and, 257searched updates using, 291SELECT and, 291–292, 292SET clause and, 292, 527, 527temporal tables and, 607, 607temporary tables and, 587, 590triggers and, 667, 668views and, 148, 211, 295, 296, 297WHERE clause and, 291

Update (U) locks, 722, 724, 727UPDATE COLUMN, 536–537, 536, 537updated pages, 856upgrades, vi

On/Off Capacity Upgrade on Demand, 7URCHKTH, 64t, 478, 730tURLGWTH, 64t, 478, 730tUSA date/time format, 167t, 168tusage privileges, 113tUSAGE, 117t, 120t, 122t, 689USAGE ON DISTINCT TYPE, 113tUSAGE ON JAR, 113tUSAGE ON SEQUENCE, 113tUSCCSID, 64tUSE HISTORY, 217, 218USE OF BUFFERPOOL, 113tUSE OF STOGRPU, 113tUSE OF TABLESPACE, 113tuser-defi ned data type (UDT), ii–iii, 155, 169–170, 686–689

ALTER and, 686built-in functions for, using CREATE FUNCTION, 689,

689casting of, 687–688, 688catalog information on, 689comparison operators for, 687CREATE, 686CURRENT SCHEMA and, 686defi ning, using CREATE TYPE, 686–687, 686large objects (LOBs) and, 706–707, 707privileges with, 689

user-defi ned functions (UDFs), iv, 275, 684, 685, 689–703, 712, 714binding/rebinding and, 568C/C++, COBOL, Java, PL/1 and, 690

992 Index

catalog information on, 703, 703controlling, with START FUNCTION SPECIFIC, 701–702,

701cost information on, 703creating, using CREATE FUNCTION, 153, 690–693, 691,

693Database Request Module (DBRM) and, 690defi ning, 697deterministic, 697displaying statistics about, using DISPLAY FUNCTION

SPECIFIC, 702, 702EXPLAIN and, 746external, 690external execution of, 701external scalar, 690–692, 691external table, using FROM and SELECT, 696–697, 696,

697Instrument Facility Interface (IFI) and, 690invoking, 697–700, 697, 698, 699, 700monitoring of, 701–702non-deterministic, 697non-inline SQL scalar, 693–695, 694–695overloading in, 700ownership of, and privileges, 127, 128tparameter passing in, 691–692polymorphism and, 700–701, 700–701queries and, 697RETURNS clause in, 693, 695sourced, 690, 692–693, 693SQL and, 690SQL scalar, 693–695, 693, 694–695SQL table functions in, 695–696, 695, 696SQLSTATE and, 692starting, with START FUNCTION, 701statistics used by, in SYSSTAT, 702stopping, using STOP FUNCTION SPECIFIC, 702, 702storing code of, 694triggers and, 682–683, 682, 683Workload Manager (WLM) and, 701

USERNAMES, 85, 644, 665USING STOGROUP, 203, 223USING VCAT, 203, 209, 223UTF-8/UTF-16, 171UTILINIT, 357, 372, 376, 388utilities, iv, vi, 37, 40, 69–73, 91, 97, 419–424, 432, 482

activities performed by, 69address spaces for, 41CLIST command and, 70concurrency, claims and drains for, 731–732, 731tControl Center execution of, 70DASD and, 72data sharing and, 507–508DB2 Interactive (DB2I) and, 47DB2I execution of, 69, 69Display Utility dialog for, 70displaying, using DISPLAY UTILITY, 73, 73, 423–424,

424

DSN1CHKR, 420DSN1COMP, 420–421, 421DSN1COPY, 421–422DSN1LOGP, 422DSN1PRNT, 423DSN1SDMP (IFC Selective Dump), 423DSNJLOGF (Preformat Active Log), 419DSNJU003 (Change Log Inventory), 419, 440, 441, 465,

475DSNJU004 (Print Log Map), 419–420, 419, 441, 470–471,

647DSNU command to execute, 70DSNUTILU to execute, 70–71, 71executing, 69–73IDs for, 70OPTIONS control statement for. 71, 72performance of, vPERMIT, 97, 97PREVIEW of, 72–73REPAIR utility and, 72standalone, attachment facilities for, 41TAPE and, 72templates for, 70, 71–73, 72

utility mode, image copies and, 449UTILS_DUMP_CLASS_NAME, 64tUTILS_TEMP_STORCLAS, 64tUTILTERM, 357, 372, 376, 388UTIMOUT, 64tUTSORTAL, 65t

VVALIDATE, 137t, 139t, 565t, 573validation, 668VALUE function, 330VALUES/VALUES INTO, 223, 288, 599VARBINARY, 159, 162, 172t 190, 568VARCHAR, 159, 160–161, 171, 172t, 190

binding/rebinding and, 568VARGRAPHIC, 159, 160, 171, 190, 568variables

fi le reference, 704–705host, declaring, 520–521, 521indicator, 521–522, 522transition type, triggers and, 675–676, 675

VARIANCE, 277VARY WLM, 631–632, 631, 632varying-length binary string. See VARBINARYvarying-length character. See VARCHARvector functions. See column functionsVERSION, 751tversioning, 217, 218, 559Vertical Deferred Write Threshold (VDWQT), 875, 877view reference tables, 798, 798–799tviews, ii–iii, 148, 210–213, 256, 295–297, 296, 297, 299

access control and 131, 131ALTER for, 155authority for creating, 110

Index 993

binding/rebinding of, 568classifi cation of, 295–297creating, using CREATE VIEW, 153, 131, 210, 211deleting, using DELETE, 148, 211, 295, 296DISTINCT clause and, 212, 295, 296FOR FETCH ONLY/FOR READ ONLY and, 297FROM clause and, 212, 295, 296GROUP BY clause and, 212, 295HAVING clause and, 212, 295inline, 313INSERT and, 148, 211, 295, 296, 297Materialized Query Tables (MQT) and, 11modifying, 213nested table expressions (inline views) in, 313–315, 313,

314, 315nested, defi nitions for, 212non-read-only, 295, 296–297ownership of, and privileges, 126–127, 128tread-only, 212–213, 295–296, 299removing, using DROP VIEW, 213, 213SELECT and, 211, 295SQL and, using DECLARE, 518–528, 519subqueries and, 212updating, using UPDATE, 148, 211, 295, 296, 297WITH CHECK OPTION in, 211–212, 212

virtual buffer pools, 515, 856–857Virtual IP Address (VIPA), 646Virtual Pool Parallel Sequential Threshold (VPPSEQT), 860,

862Virtual Pool Sysplex Parallel Sequential Threshold

(VPXPSEQT), 862Virtual Storage Access Method (VSAM), 150

DSN1COPY and, 421–422DSN1PRNT and, 423DSNJLOGF (Preformat Active Log), 419DSNJU003 (Change Log Inventory) and, 419, 475index spaces and, 150stored procedures and, 615temporary tables and, 587–588, 587, 589

virtual storage, vVirtual Terminal Access Method (VTAM), 85, 86, 643, 644,

645Visual Basic, DB2 Connect and, 25Visual Explain, 26–27, 685Visual Studio, DB2 Connect and, 25VM Data Space Support (VMDSS), 3, 4, 8VOL-SER numbers, recovery and, 459VOLTDEVT, 65tVPPSEQT. See Virtual Pool Parallel Sequential Threshold

(VPPSEQT)VPXPSEQT. See Virtual Pool Sysplex Parallel Sequential

Threshold (VPXPSEQT)VSAM. See Virtual Storage Access MethodVSE/VM, 2, 3, 7–8, 19, 24

Vwait time, vi

warehousing. See data warehousingWeb services, 7WebSphere, 1, 7, 11, 853WebSphere Application Server, 2, 106WebSphere Information Integrator, 25WebSphere MQ, 24WebSphere Studio, 25WEEK, 167WEEK_ISO, 167WEPR, 429tWFDBSEP, 65tWHEN_OPTIMIZE, 753tWHERE, 257–261, 264, 268, 273–274, 276, 291, 294, 343,

354, 520, 824CASE expression in, 334–335, 334, 335joins and, 323–325, 324, 325subqueries and, 307

WHERE CURRENT, 537WHILE, 634wildcard characters, 280–281Windows. See Microsoft WindowsWITH, 724WITH CHECK OPTION, 211–212, 212WITH CONTINUE, 706, 710–711WITH DEFAULT, 289WITH GRANT OPTION, 124, 125WITH HOLD, 541, 552, 554, 587, 731, 830, 837WITH ISOLATION, 725WITH RETURN, 621, 622, 622WLM. See Workload Manager (WLM) and workload

managementWLM_REFRESH, 636WLM_SET_CLIENT_INFO, 636WLMENV, 65twork fi les

data sharing and, 494overfl ow of, in RID pool, 867

Workgroup Server Edition, DB2, 4, 36, 242Workgroup, DB2, 15. See also DB2 for Linux, UNIX, and

Windows (LUW)Workload Manager (WLM) and workload management, vi, 6,

12, 16, 18, 629–632address spaces within, 41, 629ASUTIME parameter in, 630CALL statement for stored procedures and, 629data sharing and, 506DB2 for z/OS and, 5, 6DB2 for Linux, UNIX, Windows (LUW) and, 12DB2 Governor and, 12diagnostic information in, using CEEDUMP, 632distributed data access and, 655, 656DSNRLI interface and, 629Job Control Language (JCL) and, 629, 630managing environments in, using VARY WLM, 631–632,

631, 632NUMTCB and, 639Optim Query Workload Tuner, 26

994 Index

program type and, 631, 631Query Patroller and, 12Recoverable Resource Manager Services Attachment

Facility (RRSAF) and, 629REFRESH in, 632REXX and, 630stored procedures and, 616, 625, 628–632, 637Task Control Blocks (TCBs) and, 629thread management in, 629, 637, 639user-defi ned functions (UDFs) and, 701

WRITE, 135twrites, 860–861

XXCF. See Cross System Coupling Facility (XCF)XDBDECOMPXML, 341XES. See Cross System Extended Service (XES)XLKUPDLT, 730tXLKUPDT, 65tXML, ii–iii, 1, 3, 14, 156t, 169, 172, 172t, 179, 352, 354, 467,

684, 707–711. See also PureXMLAuxiliary Warning (AUXW) status in, 472C/C++, COBOL, PL/1 and, 710CHECK DATA and, 412–413, 413CREATE/ALTER TABLE and, 709, 709DB2 for z/OS and, 4, 242decomposition and, 341Document Type Defi nition (DTD) and, 708functions and, 275indexes and, 231–232, 232INSERT and, 341inserting and loading, using LOAD, 709large objects (LOBs) and, 710locks and, 719Net Search Extender and, 8–9, 13–17Optim Development Studio and, 28–29point-in-time recovery and, 472

reads/reading in, 710recovery and, 472shredding and, 341stored procedures and, 635, 708streaming of, using FETCH WITH CONTINUE, 710–711table spaces and. See XML table spacesUNLOAD and, 373, 373updating, using XMLMODIFY, 709–710, 710validation of schema in, automatic vs. manual, 708–709,

709XML Schema Defi nition (XSD) and, 708

XML Schema Defi nition (XSD), 708XML tables/table spaces, 148, 193, 150, 203

recovery of, 458statistics and REORG in, 406

XMLEXISTS, 340–341, 340, 346XMLMODIFY, 709–710, 710XMLQUERY, 336–341, 352, 354XMLVALA/XMLVALS, 65tXPath expressions, 337–341, 352XQuery, ii, 26–29, 340–341, 354

Optim Data Studio and, 27Optim Development Studio and, 28–29, 28

XSR_xxx stored procedures, 636

YYEAR, 167

Zz/OS, 2–6, 19, 37, 38

commands, IRLM, 65console for, 93DB2 Connect and, 22–25Optim Development Studio and, 28–29Optim pureQuery Runtime and, 29

ZOSMETRICS, 65t