Beginning DB2 - From Novice to Professional

ction, 249–250 db2_conn_errormsg function, 250 db2evmon utility, 475 db2_exec function, 252–253 db2_execute function, 255–257 db2_fetch_both function, 253–254 db2_fetch_object function, 253 db2_fetch_row function, 253 db2_field_* functions, 258 db2fmcu command-line tool, 72 db2-fn:sqlquery function, 199–201 db2-fn:xmlcolumn function, 199–201 db2gds process, 70 DB2_GRP_LOOKUP registry, 173 DB2_GRP_LOOKUP registry setting, 182 DB2ILIST (Instance Listing Utility), 83 db2_install script, 32 db2ipccm process, 70 DB2LEVEL (Version Information Utility), 82 db2licm utility, 35 db2look utility overview of, 426, 441–443 using from Control Center, 445–446 db2move utility, 426, 441, 443–445 db2nmpcfg.exe, 281 db2nmpsetup.exe utility, 281 db2_pconnect function, 252 DB2PD (Problem Determination tool), 84–85 db2_prepare function, 255–257 db2_procedures function, 258 db2profile script, invoking, 42 db2_result function, 253 db2resyn process, 70 db2_rollback function, 258 DB2SECURITYLABEL data type, 192 db2_server_info function, 258 DB2SET (Registry Utility), 83 db2start command, 69–70 db2stop command, 69–70 db2sysc process, 70 db2_tables function, 258 db2tcpcm process, 70 db2usr1 (Linux), 14 db2wdog process, 70 DBADM authority, 177 DBCLOB data type, 129 DBD::DB2 modules compiling from source code, 326–327 connecting with, 327–328 executing SQL statements, 328–330 installing, 324–326 DBI->connect method, 328 DDL (data definition language) of database objects, extracting, 441 Debug dialog box (JDBC), 306 DECFLOAT data type, 128 DECIMAL/NUMERIC data type, 128 declarative referential integrity, 134 declaring primary keys, 133 tablespaces at table creation, 140 deferring constraints, 137 delete statements (SQL), 126 deleting XML data, 213 delimited format, 407 delta backups, 373–374 denying privileges, 178 Deploy option (Data Studio), 154 Deploy Options dialog box (New Stored Procedure Wizard), 107 Deploy Wizard (Data Studio), 155 descending indexes, 219 describe table table-name command, 114 Design Advisor feature selection in, 222 index advisor facility, 221–227 recommendation summary, 226 scheduling workload for, 226 Show button, 226 space for recommendations in, 224 Update Catalog Statistics dialog box, 224 workflow specification in, 222–224 ■INDEX498 942XCh25IndexCMP2 7/13/08 8:55 AM Page 498 design process analysis of use cases and data requirements, 432–433 database layer of Employee of the Year application, 427–428 finding relationships in data, 433–434 identification of users and use cases, 430–432 logical data model, building, 435–436 model review, 440 physical model, building, 436–439 roadmap for, 428–429 statement of requirements, 429–430 translating logical model to physical model, 436 designing tables in Rails, 269–271 DETERMINISTIC modifier (SQL PL), 145 developer tools. See Data Studio (IBM) development options C and C++, 333 COBOL, 333 DB2 on Rails toolkit, 267 Perl DBI compiling from source code, 326–327 connecting to DB2, 327–328 executing SQL statements, 328–330 installing, 324–326 overview of, 323 resources on, 330 Python, 330–332 Rexx, 333 Visual Basic and Visual Basic .NET, 334 DIAGLEVEL parameter, 80 dictionaries, compression, 455 Dimension Organization, 141 directories, database creation and, 348 disable query optimization keyword, 137 disabling constraints, 137 disallowing reverse scans of indexes, 220 disconnect command, 60–61 discoverability parameters, 404 Discovery Kit, 7–8 disk level of storage, 337 DISK setting (archive logging), 363 disk space, preinstallation requirements for, 13 Distributed Relational Database Architecture (DRDA), 165 DLCHKTIME parameter, 81 DMB (Instance) Configuration dialog box, 76 DML (data manipulation language) delete statements, 126 insert statements, 124–125 overview of, 111 select statements clauses, 114–117 joins, 118 overview of, 112 subqueries, 119 union operations, 119–120 update statements, 125 DMS (database managed storage) tablespace overview of, 343–344 using, 345 Document Viewer XML and, 196–198 XML schema in, 211 dollar sign ($), alias and (XQuery), 201 double-byte string data types, native, 129 DOUBLE/FLOAT data type, 128 download page, IBM DB2, 6 downloading Data Studio, 90–91 DB2 on Rails toolkit, 263 Zend, 241 DRDA (Distributed Relational Database Architecture), 165 driver types (JDBC), 302–303, 405 DROP DATABASE command, 356 drop restriction, 141 DROP TABLESPACE command, 350 DropNominationTable script, 103 DROPPED TABLE RECOVERY parameter (tablespaces), 348 dropping databases, 356 stored procedures, 152–154 user-defined functions, 161 durability framework, 360 DYNAMIC keyword, 145 DYNAMIC RESULT SETS n modifier (SQL PL), 145 ■E -e option (db2look utility), 442 Eclipse framework, 92–94 editions of DB2, 5 elements in create table statement, 132 emp_current_votes function, 158–159 emp_current_votes_results function, 159 emp_current_votes_row function, 158 employee, modeling relationship between nomination and, 434 Employee of the Year application analysis of use cases and data requirements, 432–433 designing database layer of, 427–428 finding relationships in data, 433–434 identification of users and use cases, 430–432 logical data model, building, 435–436 model review, 440 physical model, building, 436–439 statement of requirements, 429–430 translating logical model to physical model, 436 ■INDEX 499 942XCh25IndexCMP2 7/13/08 8:55 AM Page 499 employee table adding new employee to, 122 modeling, 432 physical model for, 121 employee_vote procedure first example of, 146 second example of, 149–150 emp_vote_standings procedure, 147 enable query optimization keyword, 137 enforced mode, constraints and, 137 enforcing uniqueness with index, 217–218 Enterprise Developer edition, 5 Enterprise edition, 5 enumerating columns in SQL, 298 environment, major components of, 64 error management, stored procedures and, 151–152 error messages SQL1043C, 346 SQL1088W, 346 SQL20189W, 348 SQL2539W, 375 estimating compression savings, 456–457 event monitors description of, 472 using, 473–478 Event Publishing, 446 Everyplace 9 edition, 5 evolving project schema with Ruby, 272 examining pureQuery results, 321–322 except operation, 120 executing migrations in Ruby, 271–272 statements with Perl DBI and DBD::DB2 modules, 328–330 with Python, 332 with Zend Core framework, 260–262 stored procedure via PHP, 255–257 EXECUTIVE privilege, 186 explicit schema, specifying, 144 Export Table dialog box Columns tab, 413 Target tab, 412 exporting data Control Center, using, 412–414 LOBs, 409–411 overview of, 408 simple export, performing, 409 XML objects, 411–412 Express edition, 5, 18 Express-C edition, 5 Express-C edition installer, using to install DB2 Client, 386 expressions (XPath), 205 EXTENTSIZE parameter (tablespaces), 346 EXTERNAL ACTION modifier (SQL PL), 146 external procedures, drawbacks to, 314–315 extracting DDL of database objects, 441 ■F Fault Monitor Coordinator, 72–73 feature selection screen (DB2 Express-C edition installer), 22, 386 Feuerstein, Steven, 427 file extensions .php, 244 .rsp, 21 .yml, 268 file formats for bulk data movement ASCII, 407 delimited, 407 Integration Exchange, 408 Lotus 1-2-3 Worksheet, 408 File tab (Import Table dialog box), 421 files installation log, 34 mirroring log, 366 php.ini master configuration, 259 primary log, 361 response, 20–21 secondary log, 361 filesystem caching, 352 finding relationships in data, 433–434 First Steps component (DB2 Client), 391 flow of data and logging during unit of work, 360 F-L-W-O-R clauses (XQuery), 200 for clause (XQuery), 201 foreign key columns, indexes for, 218 foreign key constraint, 134–136 function keyword, 161 functions See also user-defined functions (UDFs) abs, 115 column, 123 db2_autocommit, 258 db2_bind_param, 257 db2_client_info, 258 db2_close, 250 db2_commit, 258 db2_connect, 249–250 db2_conn_errormsg, 250 db2_exec, 252–253 db2_execute, 255–257 db2_fetch_both, 253–254 db2_fetch_object, 253 db2_fetch_row, 253 db2_field_*, 258 db2-fn:sqlquery, 199–201 db2-fn:xmlcolumn, 199–201 db2_pconnect, 252 db2_prepare, 255–257 db2_procedures, 258 ■INDEX500 942XCh25IndexCMP2 7/13/08 8:55 AM Page 500 db2_result, 253 db2_rollback, 258 db2_server_info, 258 db2_tables, 258 object privileges, 186 row, 123 scalar, 123 seclabel_by_name(), 193 SNAP_GET_LOCK, 480 table, 123 text(), XQuery, 204 upper(), 123 XMLEXISTS, 213 XMLPARSE, 212 XMLTable, 207–208 ■G Generate DLL dialog box, 445 Generate pureQuery Code For A Table Wizard, 319–320 generated modifier for columns, 138–140 generating scaffolding (Ruby on Rails), 275 get Connection method, 303 GET DBM CFG command, 172 GET SNAPSHOT command, 479 Getting Started components (DB2 Client), 391 “Getting Started with DB2 Installation and Administration on Linux and Windows” (IBM), 31 global daemon spawner, 70 global group authentication, configuring to use, 173 goto statement, 150 Governor, 71 grand security label command, 193 granting group privileges, 181 object privileges, 187–190 GRAPHIC data type, 129 graphical installation under Windows and Linux, 15–30 graphical tools See also Control Center; DB2 First Steps Command Editor, 55–57 group authentication, 173–174 group behavior looking for groups in Windows, 182–183 PUBLIC group, 183 roles compared to, 184–185 group by clause of select statement, 116 Group tab (Database Authorities dialog box), 183 GROUP_PLUGIN parameter, 182 groups, preinstallation requirements for, 14 GSSPLUGIN authentication type, 167 GSS_SERVER_ENCRYPT authentication type, 167 ■H -h option (db2look utility), 443 having clause of select statement, 117 help information for db2admin, 68 historic profiling, event monitors for, 474 history of DB2, 4 home page, IBM DB2, 6 ■I IBM, and history of DB2, 4 IBM Data Server Client. See DB2 Client IBM Data Server Data Studio V9.5 folder, 94 IBM data server provider for .NET registering, 281–282 testing connectivity, 282–283 IBM Data Studio. See Data Studio (IBM) IBM Database Add-Ins for Microsoft Visual Studio installing, 279–280, 283 testing, 283–286 IBM DB2 home page, 6 IBM DB2 permalink website, 143 IBM Installation Manager, 92–94 IBM Software Development Kit for Java (DB2 Client), 392 IBM_DB2 extensions ad hoc SQL statements, working with, 252–255 connection management functions, 248–251 functions, 257–258 managing connections, 252 prepared statements and stored procedures, working with, 255–257 verifying installation of, 247–248 identification of users and use cases, 430–432 identity technique, and columns, 138–140 IDENTITY value (collation option), 340 IDENTITY_16BIT value (collation option), 340 implicit joining technique, 118 IMPLICIT_SCHEMA authority, 178 implied authorities, 178 Import Table dialog box Columns tab, 422 File tab, 421 Options tab, 422 import utility load utility compared to, 423–425 options, 416 statistical summary, 415 syntax, 414 warning messages, 417–418 XML parsing and validation options, 421 ■INDEX 501 942XCh25IndexCMP2 7/13/08 8:55 AM Page 501 importing data complex imports, performing, 419–421 Control Center, using, 421–422 insert_update process compared to, 416–418 mix of new and updated data, 418–419 simple import, performing, 414–416 include clause, indexes and, 220 including columns in indexes, 220 incremental backups, 373–374 indexes clustering feature, 221 creating, 215–216 description of, 215 Design Advisor and, 221–227 for foreign key columns, 218 included columns, 220 ordering semantics, 219 reorganization for, 488 reverse scans, 219–220 unique values, enforcing, 217–218 infinite logging, 365 Information Center configuration screen, 24 launching, 68 INHERIT ISOLATION LEVEL {WITH | WITHOUT} LOCK REQUEST option (user-defined functions), 158 INHERIT SPECIAL REGISTERS modifier (SQL PL), 145 inline technique for declaring primary keys, 133 inner joins, 118 insert into ... select ... technique, 124 insert statements (SQL), 124–125, 232 inserting XML data, 208–210 insert_update option (import utility), 420 insert_update process, import utility compared to, 416–418 inspect command (Data Row Compression), 456 Install A Product launch screen, 16 installation log file, 34 Installation Welcome screen, 16 installing command-line installation under Linux, 32–34 Data Studio Eclipse-based IDE installations, 92–94 IBM Installation Manager, 92 installation dialog box, 91 DB2 9 Discovery Kit DVD, using, 12 DB2 Client deciding which components to install, 393–394 dedicated installers, using, 387–393 Express-C edition installer, using, 386 overview of, 385 DB2 on Rails toolkit, 263–267 DBD::DB2 modules, 324–326 graphical installation under Windows and Linux, 15–30 IBM Database Add-Ins for Microsoft Visual Studio, 279–280, 283 on Linux, 11 Perl DBI, 324–326 preinstallation requirements, checking, 13–15 Python driver, 330–331 resources on, 31 reviewing installation, 35 unpacking installation downloads, 12 on Windows, 11 Zend Core, 242–248 instance communication and startup screen, 27–28 Instance Listing Utilities (DB2ILIST and DASLIST), 83 instance owner description of, 64 logging in as under Linux, 42 under Windows, 41 write permissions, and storage options, 46 instance-level configuration, 74 instance-level privileged groups, 168–169 instances attaching, 66 configuring from CLP, 79–80 from Control Center, 75–77 overview of, 73 DAS, 70–71 DB2, 70–71 quiescing and unquiescing, 66 starting and stopping from Control Center, 64–66 DAS, 67–69 database, 69–70 traditional memory structure of, 468–469 instantaneous profiling, snapshot monitors for, 478 INTEGER data type, 128 Integration Exchange Format (IXF), 408 interface for DB2 First Steps, 44 Interfaces component (DB2 Client), 390 intersect operation, 120 intersection table, physical design of, 438 IPC communications manager, 70 iSeries, DB2 for, 6 Isolation dialog box (Configuration Advisor), 463–464 isolation levels, 464 ■INDEX502 942XCh25IndexCMP2 7/13/08 8:55 AM Page 502 issuing commands to change database parameters, 81–82 to change instance parameters, 80 IXF (Integration Exchange Format), 408 ixscan access method, 217 ■J Java Software Development Kit for, 392 writing stored procedures in, 105 Java development See also JDBC (Java Database Connectivity) applications environment for, 301 pureQuery autogenerating mapping classes, 318–320 examining results, 321–322 project, creating, 315–316 stored procedures class, deploying, 312 class, writing, 311–312 drawbacks to, 314–315 testing, 314 writing, 313–314 Java Support component (DB2 Client), 390 JavaDB2ex1 class, 303 JDBC (Java Database Connectivity) applications data, working with, 307–309 driver types, 302–303 features, working with, 309–311 refactoring connection code, 305–307 writing, 303–305 JDBC-ODBC Bridge Driver, 302 joins, 118 ■K KERBEROS authentication type, 166 key attributes, 437 keywords atomic, 147 disable query optimization, 137 DYNAMIC, 145 enable query optimization, 137 enforced, 137 function, 161 not enforced, 137 specific, 153 xquery XPath, 204 XQuery, 200 killing processes, 67 knowing system, performance tuning and, 459 KRB_SERVER_ENCRYPT authentication type, 166 ■L Label-Based Access Control (LBAC) example of, 192–194 label component, defining, 191 label, defining, 192 parts of, 190–191 policy, defining, 191 resources, 194 language handling, 340–341 language selection screen, 22 LANGUAGE SQL modifier (SQL PL), 146 large tablespaces, 342 launching See also running; starting Command Line Processor, 58 Data Studio, 94–95 DB2 Command Editor, 55 DB2 Control Center from First Steps, 47 from shortcuts, 48 DB2 First Steps under Linux, 41–43 under Windows, 40–41 Design Advisor, 222 Information Center, 68 Replication Center, 447–448 Launchpad for DB2 Client, 388 IBM DB2 Express-C installation screen, 16, 280 LBAC (Label-Based Access Control) example of, 192–194 label component, defining, 191 label, defining, 192 parts of, 190–191 policy, defining, 191 resources, 194 Lempel Ziv algorithm, 455, 458 let clause (XQuery), 202 levels of abstraction containers, 337 disk, 338 tablespaces, 337 License Agreement screen, 18 License Server, 71 Linux command-line installation under, 32–34 DB2 Administration Instance and, 26 DB2 First Steps, launching under, 41–43 db2admin tool under, 68 graphical installation under, 15–30 installing on, 11 installing Zend Core under, 243 reviewing installation under, 35 Ruby, installing under, 266 users and groups under, 14 ■INDEX 503 942XCh25IndexCMP2 7/13/08 8:55 AM Page 503 LIST ACTIVE DATABASES command, 365 list command, 400 LIST DATABASE DIRECTORY command, 401 LIST NODE DIRECTORY command, 401 LIST TABLESPACE CONTAINERS command, 353 LIST TABLESPACES command, 352 LIST TABLESPACES SHOW DETAIL command, 353 LLS (LOB Location Specifier), 410 LOAD authority, 178 load utility, performance optimization options, 423 loading data with load utility, 423–426 LOB Location Specifier (LLS), 410 LOBs, exporting, 409–411 local group authentication, configuring to use, 173 Local System account (Windows, 174 lock snapshot monitor, 479–481 lock types, 464–466 locking features, optimistic, 466 Log Buffer, role of, 361 LOGBUFSZ parameter, 361 LOGFILSIZ parameter, 361 logging archive, 362–365 archiving log manually, 366 circular, 361–362 file types, 361 history of, 364 infinite, 365 mirroring log files, 366 principles of, 360–361 logging control, 141 logic, three-valued, 438 logical data model building, 435–436 for skills table, 52 translating to physical model, 436 logical storage, 140–141, 342 LOGPRIMARY parameter, 361 LOGRETAIN setting (archive logging), 363 LOGSECOND parameter, 361 LONG VARCHAR data type, 129 LONG VARGRAPHIC data type, 129 looking for groups in Windows environment, 182–183 Lotus 1-2-3 Worksheet format, 408 ■M -m option (db2look utility), 443 machine.config (.NET framework), 281 Manage Passwords and Connectivity option (Replication Center dialog box), 448 Management Service, 71 managing authentication parameters in CLP, 172–173 in Control Center, 170–171 connections, IBM_DB2 PHP, 252 credentials, 96 objects in Visual Studio building project, 289–290 creating new view object, 287–289 Server Explorer, 286–287 roles, 184 stored procedures, 154–155 user-defined functions, 161 manually running RUNSTATS, 484–485 mapping classes, autogenerating pureQuery Java, 318–320 mapping logical model to physical model, 436 marker messages SQL 3500W and SQL3515W, 425 SQL 3519W and SQL3520W, 425 materialization features, 142 MAX_LOG parameter, 365 MDC (multidimensional clustering), 141 memory preinstallation requirements for, 13 Self-Tuning Memory Management (STMM), 347, 469–470 traditional structures of instance and database, 468–469 Memory Usage Plot, 472 Memory Visualizer, 471–472 menus context, when initiating pureQuery support for Java project, 315 DB2 Control Center, 50–52 metadata, 112 methods DBI->connect, 328 getConnection, 303 ixscan, 217 openConnection, 306 tbscan, 216 Microsoft Visual Studio.NET See also Windows building .NET Web applications binding objects to controls, 296–297 building and running projects, 298–299 overview of, 295 building .NET Windows applications binding objects to controls, 291 exploring connection’s related objects, 294 overview of, 290 running projects, 294 specifying connection for, 292–293 IBM data server provider for .NET, registering, 281–282 ■INDEX504 942XCh25IndexCMP2 7/13/08 8:55 AM Page 504 IBM Database Add-Ins for installing, 279–280, 283 testing, 283–286 integration with, 279 managing objects building project, 289–290 creating new view object, 287–289 Server Explorer, 286–287 testing DB2 .NET connectivity, 282–283 migrations description of, 269 executing in Ruby, 271–272 mirroring log files, 366 MIRRORLOGPATH parameter, 366 modifiers for stored procedures, 145–146 for user-defined functions, 157–158 MODIFIES SQL DATA modifier (SQL PL), 145 monitor switches, activating, 473 monitors event, using, 473–478 overview of, 472 snapshot, using, 478–481 moving data in bulk. See bulk data movement Mullins, Craig, 427 multidimensional clustering (MDC), 141 multirow insert statement, 125 myself or any of my descendants placeholder (//), XPath, 207 ■N named copy approach, 23 namespace declaration, 199 naming conventions backups, 367 event monitors, 474 LOB export files, 410 log files, 361 tables, 131 native data types binary, 129 double-byte string, 129 numeric, 128 overview of, 127, 130 string, 128–129 temporal, 129–130 .NET framework See also Microsoft Visual Studio.NET IBM data server provider for .NET registering, 281–282 testing connectivity, 282–283 machine.config, 281 Network Service account (Windows), 174 New Data Development Project Wizard (Data Studio), 98–100 New Project dialog box (Visual Studio), 284 New SQL Statement Wizard (Data Studio), 100–104 New Stored Procedure Wizard (Data Studio), 104–109 nextval operator (sequences), 228, 231 NLSCHAR value (collation option), 341 no cache option (sequences), 230 NO EXTERNAL ACTION modifier (SQL PL), 146 NODE0000 directory, 348 nominated ID, auth ID compared to, 96 nominating index as clustering index for table, 221 nomination, adding to NOMINATION table, 105–109 nomination table example index for, 215–216 modifying to incorporate CategoryID field, 134 performing default statistics collection against, 484–485 physical model for, 439 reorganizing, 487–488 table creation, 130–131 normalization, 433 NOT DETERMINISTIC modifier (SQL PL), 145 not enforced keyword, constraints and, 137 not null constraint, 132 notification configuration screen, 29 notimeout option (import utility), 418 null data, columns for tables and, 132 Null value, 438 NULLID schema, 144 numeric data types, native, 128 NUM_LOG_SPAN parameter, 365 ■O object privileges granting, 187–190 working with, 185–186 objects adding to project in Data Studio New SQL Statement Wizard, 100–104 New Stored Procedure Wizard, 104–109 binding to Web controls, 296–297 to Windows controls, 291 extracting DDL of, 441 JDBC, Properties, 309, 311 managing in Visual Studio building project, 289–290 creating new view object, 287–289 Server Explorer, 286–287 obtaining DB2, 6–8 ODBC (Open Data Base Connectivity) standard, 398 ■INDEX 505 942XCh25IndexCMP2 7/13/08 8:55 AM Page 505 OFF setting (archive logging), 363 offline backups, 373 {OLD | NEW} SAVEPOINT LEVEL modifier (SQL PL), 146 “on change” rules, 135 on delete ... rules, 136 on update ... rules, 136 online backups, 373 Open Data Base Connectivity (ODBC) standard, 398 Open Source Application Enablement Team (Python), 330 openConnection method, 306 operating systems See also Linux; Windows security context of, 174–175 optimistic locking features, 466 optimizer, select statement and, 114 optional operating system security for DB2 objects screen, 30 options db2look utility, 443 db2move utility, 445 options screen, DB2 First Steps, 45 Options tab (Import Table dialog box), 422 order by clause select statement, 117 XQuery, 204 order clause (sequences), 230 Other Systems (Search The Network) option (Client Configuration Assistant), 396 outer joins, 118 Output message pane (Visual Studio), 289 OVERHEAD parameter (tablespaces), 346 ■P package groups, 93 packaging for redeployment, 21 parallelism, 372 PARAMETER CCSID {ASCII | UNICODE} modifier (SQL PL), 146 parameters authentication, changing in CLP, 172–173 in Control Center, 170–171 overview of, 169 changing through Control Center, 76–77 database, issuing commands to change, 81–82 discoverability, 404 GROUP_PLUGIN, 182 instance, issuing commands to change, 80 memory, 468–469 self-tuning memory, 469–470 for stored procedures, 144–145 for tablespaces, 346–347 parentheses, stored procedures and, 144 parsing capabilities of Document Viewer, 197 partition groups, 348 pdksh Korn Shell package, preinstallation requirements for, 15 performance. See indexes performance tuning approach to, 459–460 Configuration Advisor Connections dialog box, 462 Isolation dialog box, 463–464 Populated dialog box, 462 Priority dialog box, 462 Results dialog box, 466–467 Server dialog box, 460 Transactions dialog box, 461 using, 460 Workload dialog box, 461 as hot topic, 459 Memory Visualizer, 471–472 monitor switches, activating, 473 monitors event, using, 473–478 overview of, 472 snapshot, using, 478–481 physical data-storage issues and, 485 REORG utility, using, 487–488 REORGCHK utility, using, 485–487 RUNSTATS automated statistics generation, 481–483 manually running, 484–485 self-tuning memory, 468–469 Self-Tuning Memory Management, 469–470 Perl DBI (database interface module) compiling from source code, 326–327 connecting to DB2, 327–328 executing SQL statements, 328–330 installing, 324–326 overview of, 323 resources on, 330 Perl Package Manager (ppm, Windows), 325–326 permissions for backups and recovery, 359 Personal edition, 5 PHP coding best practice, 251 IBM_DB2 extensions ad hoc SQL statements, working with, 252–255 connection management functions, 248–251 functions, 257–258 managing connections, 252 overview of, 248 prepared statements and stored procedures, working with, 255–257 ■INDEX506 942XCh25IndexCMP2 7/13/08 8:55 AM Page 506 overview of, 241 Zend Core framework classes, 262 configuring, 258–259 connection management, 259–260 developing with, 258 executing statements, 260–262 installing, 242–247 overview of, 241 post-installation checks, 247–248 .php file extension, 244 php.ini master configuration file, 259 physical data-storage issues and performance tuning, 485 physical model building, 436–439 for employee table, 121 for skills table, 52 translating logical model to, 436 physical storage for tables, 140–141 pipe symbol (|), XML, 199 placeholders myself or any of my descendants (//), 207 question mark as, 148 policy (LBAC), defining, 191 Populated dialog box (Configuration Advisor), 462 positional predicate (XPath), 206 post-installation checks of Zend Core, 247–248 ppm (Perl Package Manager, Windows), 325–326 practicing backups and recovery, 383 predicates (XPath), 205–206 PREDICATES option (user-defined functions), 158 PREFETCHSIZE parameter (tablespaces), 346 preinstallation requirements, checking, 13–15 prepared statements, working with, 255–257 PreparedStatement class (JDBC), 307 preparing stored procedure via PHP, 255, 257 prevval operator (sequences), 228, 231–232 primary key constraint, 133–134 primary log files, 361 Priority dialog box (Configuration Advisor), 462 privileged groups, instance-level, 168–169 privileges database-level changing, 179–181 overview of, 177–178 viewing, 179 group behavior, 182–183 Label-Based Access Control example of, 192–194 label component, defining, 191 label, defining, 192 parts of, 190–191 policy, defining, 191 object granting, 187–190 working with, 185–186 preparing to use, 177 roles, 184–185 prize table example modeling, 432 physical model for, 439 Problem Determination tool (DB2PD), 84–85 procedures, object privileges, 186 processes killing, 67 server components DB2 Fault Monitor Coordinator, 72–73 DB2 Governor, 71 DB2 instances and DAS instance, 70–71 DB2 License Server, 71 DB2 Management Service, 71 DB2 Remote Command Server, 72 DB2 Security Server, 72 starting and stopping from command line, 66–70 from Control Center, 64–66 PRODUCT table (SAMPLE database), XML and, 196 progress dialog box of Database Creation Wizard, 47 projects adding objects to, in Data Studio New SQL Statement Wizard, 100–104 New Stored Procedure Wizard, 104–109 building in Visual Studio, 289–290 connection management in Data Studio, 97–100 creating with Rails, 267–268 in Visual Studio, 284 evolving schema with Ruby, 272 pureQuery-enabled, creating, 315–316 Rails, connecting to DB2, 268–269 reverting to earlier schema with Ruby, 274 starting in Data Studio, 95–97 Web, building and running, 298–299 Windows C#, specifying connection for, 292–293 Properties object (JDBC), 309–311 PUBLIC group, 183 pureQuery for Java autogenerating mapping classes, 318–320 examining results, 321–322 project, creating, 315–316 ■INDEX 507 942XCh25IndexCMP2 7/13/08 8:55 AM Page 507 pureXML technology features of, 207–208 inserting XML data and, 209 overview of, 4, 198 Unicode and, 340 XML storage and, 208 XMLPARSE function, 212 XPath and overview of, 204–205 predicates, 205–206 wildcards, 206 XQuery and, 199–204 Python connecting with, 332 driver, installing, 330–331 executing SQL statements, 332 resources, 332 ■Q Q Replication, 446 query access plan with index, 217 without index, 216 query language pureXML and, 198 XPath overview of, 204–205 predicates, 205–206 wildcards, 206 XQuery, 199–204 query optimizer, tuning with RUNSTATS, 481–485 question mark, as placeholder, 148 question mark (?) command, 59 QUIESCE_CONNECT authority, 178 quiescing instances, 66 ■R Rails connecting project to DB2, 268–269 creating project with, 267–268 DB2 on Rails toolkit, 263–267 designing tables in, 269–271 schema evolution feature, 267 rake command (Ruby), 271 READS SQL DATA modifier (SQL PL), 145 REAL data type, 128 recovery command-line options full restore tweaked for performance, 382 full restore with redirection, 382 individual tablespace restore, 382–383 overview of, 381 Control Center, using, 377–381 for free, 375 overview of, 375 permissions for, 359 practicing, 383 restoring from backups, 375–376 roll forward, from backups, 376–377 redeployment, packaging for, 21 refactoring connection code (JDBC), 305–307 REFERENCES privilege, 185 referential constraint, 132–136 referential integrity, impact of, 218 refreshing cached classes, 313 registering IBM data server provider for .NET, 281–282 source table, 451 XML schema, 211 Registers, using, 120–122 Registry Utility (DB2SET), 83 regular tablespaces, 342 relational databases, XML and, 195, 198 relationships, finding in data, 433–434 Remote Command Server, 72 reorg command (Data Row Compression), 457 REORG (reorganization) utility, 487–488 REORGCHK (reorganization check) utility, 485–487 replication See also Replication Center configuring Apply control tables, creating, 451 Apply process, starting, 452 Capture control tables, creating, 450–451 Capture process, starting, 452 launching Replication Center, 447–448 Replication Center dialog box, 448–449 source table, registering, 451 subscription set, creating, 452 preparing for, 446–447 testing, 452–454 Replication Center Apply control tables, creating, 451 Apply process, starting, 452 Capture control tables, creating, 450–451 Capture process, starting, 452 launching, 447–448 source table, registering, 451 subscription set, creating, 452 Replication Center dialog box, 448–449 Replication Tools (DB2 Client), 391 reserved words, 55 resources for design, 427 installation topics, 31 Label-Based Access Control, 194 Perl DBI and DBD::DB2 modules, 330 Python, 332 ■INDEX508 942XCh25IndexCMP2 7/13/08 8:55 AM Page 508 stored procedures, 155 response file, 20–21 restart clause (alter sequence command), 231 restartcount option (import utility), 419 restore and recovery. See recovery restore database command, 375 Results dialog box (Configuration Advisor), 466–467 ResultSet class (JDBC), 307 resync manager, 70 return clause (XQuery), 201 Return Status (Data Studio), 109 RETURNS option (user-defined functions), 157 reverse scans of indexes, 219–220 reverting to earlier project schema with Ruby, 274 reviewing installation, 35 Rexx, 333 roles, 184–185 roll forward recovery from backups, 376–377 rollforward command, 376 root user, security context and, 174 row functions, 123 row-typed user-defined functions, 156 rowcount option (import utility), 419 .rsp extension, 21 Ruby DB2 on Rails toolkit, 263–267 evolving schema with, 272 executing migration in, 271–272 reverting to earlier schema with, 274 scaffolding feature in action, 275–277 generating scaffolding, 275 overview of, 274 schema_info table for, 273 Ruby, Rails, and DB2 Adapter installation page, 264 Run dialog box, starting DB2 First Steps from, 40 Run successful message (Data Studio), 102 running See also launching; starting Data Development Projects, 102 Web projects in Visual Studio, 298–299 Windows project in Visual Studio, 294 RUNSTATS automated statistics generation, 481–483 manually running, 484–485 ■S SAMPLE database Create Table Wizard, using, 52–55 creating with DB2 First Steps, 45–47 XML in, 195–199 samples of code, accessing, 333–334 scaffolding feature, Ruby on Rails in action, 275–277 generating scaffolding, 275 overview of, 274 scalar functions, 123 scalar user-defined functions, 156 schema evolution feature (Rails), 267, 272–274 schema registration, XML, 210–212 schemas, 53, 144, 185 schema_info table for Ruby, 273 script option comments (Visual Studio), 290 SECADM authority, 178 seclabel_by_name() function, 193 secondary log files, 361 security context of operating system, 174–175 security issues. See authorities; privileges Security Server, 72 select ... into ... approach, stored procedures and, 147 select clause of select statement, 114 select statements (SQL) clauses, 114–117 count() function, 158 creating tables based on, 142 joins, 118 overview of, 112 sequences and, 231–232 subqueries, 119 union operations, 119–120 Selected menu (Control Center), 51 self-tuning memory, 468–469 Self-Tuning Memory Management (STMM), 347, 469–470 SELF_TUNING_MEM parameter, 469 separate clause technique for declaring primary keys, 133 sequences altering, 230–231 attributes for, 228–229 cache clause, 230 creating, 228 description of, 215, 228 example of, 230 no cache option, 230 order clause, 230 using, 231–233 SERVER authentication type, 165 server components DB2 Fault Monitor Coordinator, 72–73 DB2 Governor, 71 DB2 instances and DAS instance, 70–71 DB2 License Server, 71 DB2 Management Service, 71 DB2 Remote Command Server, 72 DB2 Security Server, 72 ■INDEX 509 942XCh25IndexCMP2 7/13/08 8:55 AM Page 509 Server dialog box (Configuration Advisor), 460 Server Explorer (Visual Studio), 286–287 Server Support Tools (DB2 Client), 392–393 SERVER_ENCRYPT authentication type, 165 set integrity command, 425 set-based policy components (LBAC), 190 Set-up Tools program folder, 40 Show button (Design Advisor), 226 Show SQL button, 356 shredding XML, 208 signup page, IBM DB2, 8 silent installation, 21 size of system tablespace, 346 skipcount option (import utility), 419 slash character (/), XQuery, 199 SMALLINT data type, 128 SMS (system managed storage) tablespace, 343–345 snapshot monitors, 472, 478–481 SNAP_GET_LOCK function, 480 source table, registering, 451 Spatial Extender Client component (DB2 Client), 390 specific keyword, 153 SPECIFIC name modifier (SQL PL), 145 specifying alias, 339 SQL See also statements data manipulation language (DML) delete statements, 126 insert statements, 124–125 overview of, 111 select statements, 112–120 update statements, 125 DB2 Registers, using, 120–122 enumerating columns in, 298 statements, executing with Perl DBI and DBD::DB2 modules, 328–330 with Python, 332 SQL Assist Wizard, 56–57 SQL Builder Wizard (Data Studio) Add Table dialog box, 103 column selection, 104 SQL Persistent Modules (SQL PM), 143 SQL PL (SQL Procedural Language) description of, 143 stored procedures dropping, 152–154 example of, 146–152 managing, 154–155 modifiers for, 145–146 parameters for, 144–145 resources, 155 syntax for, 143 SQL PM (SQL Persistent Modules), 143 SQL Procedural Language. See SQL PL SQL Procedures component (DB2 Client), 390 SQL Replication, 446 SQL Replication Wizard, 447 SQL Statements step of New Stored Procedure Wizard, 106–107 SQL0803N warning message, 418 SQL1043C error message, 346 SQL1088W error message, 346 SQL20189W error message, 348 SQL2539W error message, 375 SQL27984W warning message, 411 SQL3148W warning message, 418 SQL3185W warning message, 418 SQL3500W marker message, 425 SQL3515W marker message, 425 SQL3519W marker message, 425 SQL3520W marker message, 425 SQLCODE values, 67–68 SQLJ.REFRESH_CLASSES() procedure, 313 sql-query command, 114 SQLSTATEs, 67–68 square bracket notation in tablespace assignment, 141 ssh server and client, preinstallation requirements for, 15 Starter Toolkit for DB2 on Rails, 263–267 starting See also launching; running; stopping event monitors, 474 new project in Data Studio, 95–97 processes from command line, 66–70 from Control Center, 64–66 Statement class (JDBC), 307 statement event monitor, 474–477 statement of requirements, 429–430 statement terminator in CLP, 341 statements See also select statements (SQL) alter index, 219 alter table, compress {yes | no} clause, 457 body, writing for stored procedures, 147 create table, 130–132, 457 delete, 126 executing with Perl DBI and DBD::DB2 modules, 328–330 with Python, 332 with Zend Core framework, 260–262 goto, 150 insert, 124–125, 232 prepared, working with, 255–257 update, 125, 232 STATIC DISPATCH option (user-defined functions), 158 ■INDEX510 942XCh25IndexCMP2 7/13/08 8:55 AM Page 510 statistical summary of import utility, 415 statistics generation in Design Advisor, 224 STMM (Self-Tuning Memory Management) 347, 469–470 stopping See also starting event monitors, 475 processes from command line, 66–70 from Control Center, 64–66 storage automatic, adding with ALTER DATABASE command, 349–350 disk level of, 337 logical, 140–141, 342 overview of, 342 for SAMPLE database, choosing options, 46 for tables, specifying characteristics, 140–141 tablespace use DMS, 345 overview of, 342–343 parameters, 346–347 SMS, 344–345 type, choosing, 343–344 stored procedures dropping, 152–154 error management and, 151–152 example of, 146–152 Java class, deploying, 312 class, writing, 311–312 drawbacks to, 314–315 overview of, 311 testing, 314 writing, 313–314 managing, 154–155 modifiers for, 145–146 New Stored Procedure Wizard (Data Studio), 104–109 parameters for, 144–145 resources, 155 syntax for, 143 user-defined functions compared to, 156 working with, 255–257 writing, 105 string data types, native, 128–129 subqueries, 119 subscription set, creating, 452 subselect-to-join conversions, 119 SUPPLIERS table (SAMPLE database), XML and, 196 surrogate key, adding, 437 syntax ANSI join, 118 export utility, 408 declaring primary keys, 133 foreign key clause, 135 generated clause, 138 import utility, 414 index creation, 215 sequence creation, 228 stored procedures, 143 table creation, 127 tablespace storage characteristics of tables, 141 user-defined functions, 157 view creation, 233 synthetic key, adding, 437 SYSADM group, 168 SYSADM_GROUP parameter, changing, 169 SYSCATSPACE, 342 SYSCTRL group, 168 sysibm.sysdummy1 table, 121 SYSMAINT group, 168 SYSMON group, 168 SYSMON_GROUP parameter, changing, 171 system catalog tables, 112 system catalog views, 113 system managed storage (SMS) tablespace, 343–345 system tablespaces allocating size for, 346 description of, 342 system temporary tablespaces, 342 System Tray Tool, 82 SYSTEM value (collation option), 340 ■T -t option (db2 executable), 341 table() function, 160 table-typed user-defined functions, 156 TableAdapter object (Visual Studio), 294 tables Apply control, creating, 451 award_result, 217 Capture control, creating, 450–451 catalog, 112 category identity columns for, 140 logical model for, 134 nomination table and, 134–135 physical model for, 134 clustering data in, 485 columns for automatic value generation, 138–140 check constraints, 136–137 disabling and deferring constraints, 137 null data, working with, 132 primary key constraints, 133–134 referential constraints, 134–136 compressing, 457–458 Create Table Wizard, using, 52–55 ■INDEX 511 942XCh25IndexCMP2 7/13/08 8:55 AM Page 511 tables (continued) creating and managing data types, 127–130 nomination table example, 130–131 designing in Rails, 269–271 employee adding new employee to, 122 modeling, 432 physical model for, 121 features specific to, 141 functions, 123 intersection, physical design of, 438 lock types supported for, 464–466 naming, 131 nominating index as clustering index for, 221 nomination index for, 215–216 modifying to incorporate CategoryID field, 134 performing default statistics collection against, 484–485 physical model for, 439 reorganizing, 487–488 table creation, 130–131 object privileges, 185 options for creating, 142 prize modeling, 432 physical model for, 439 PRODUCT (SAMPLE database), 196 reorganization for, 488 schema_info, for Ruby, 273 source, registering, 451 storage characteristics, specifying, 140–141 syntax for creating, 127 sysibm.sysdummy1, 121 system catalog, 112 votes cast, 146–152 tablespace backups, 373 tablespaces buffer pools, 347–348 declaring at table creation, 140 description of, 337 DMS, 343–345 DROPPED TABLE RECOVERY parameter, 348 parameters for, 346–347 SMS, 344–345 type, choosing, 343–344 use of, 342–343 working with, 350–356 Target tab (Export Table dialog box), 412 tbscan access method, 216 -tc option (db2move utility), 444 TCP communications manager, 70 TCP port 523, DAS and, 25 -td option (db2 executable), 341 temporal data types, native, 129–130 TEMPSPACE, 342 terminate command, 61 territory values, 340 Test Connection button (Visual Studio), 286 testconn20.exe utility, 282–283 testing connection problem, 250 DB2 .NET connectivity, 282–283 to determine correctness of XML, 209 IBM Database Add-Ins for Microsoft Visual Studio, 283–286 Java stored procedures, 314 replication, 452–454 user privileges, 181 text() function, XQuery, 204 text handling, 340–341 Thin Client Code Server (DB2 Client), 392 third-party connectivity options, 405 three-valued logic, 438 TIME data type, 129 TIMESTAMP data type, 130 Timing tab (RUNSTATS), 482 tools See also command-line tools; Control Center; Data Studio (IBM); DB2 First Steps; utilities Configuration Assistant, 391 DB2 Client, 391–393 DB2 Command Editor, 55–57 DB2 on Rails toolkit, 263–267 System Tray Tool, 82 tools catalog configuration screen, 28 TOOLSDB database, 28 Transactions dialog box (Configuration Advisor), 461 TRANSFERRATE parameter (tablespaces), 347 translating logical model to physical model, 436 tree-based policy components (LBAC), 190 TRUST_ALLCLNTS modifier, 165 TRUST_CLNTAUTH modifier, 165 TSM setting (archive logging), 363 Type 4 JDBC driver, 405 Typical installation, 19 ■U UCA400_NO, UCA400_LSK, and UCA400_LTH values (collation option), 340 UDFs. See user-defined functions (UDFs) Unicode, 340 Unified Modeling Language (UML), 430 uninstalling DB2, 34 ■INDEX512 942XCh25IndexCMP2 7/13/08 8:55 AM Page 512 union operations, 119–120 unique values, enforcing with index, 217–218 unpacking installation downloads, 12 unquiescing instances, 66 Update Catalog Statistics dialog box (Design Advisor), 224 UPDATE MONITOR SWITCHES command, 473 update statements (SQL), 125, 232 updating views, 235 XML data, 212–213 upper() function, 123 URL, JDBC connection, 304–305 use cases for Employee of the Year application analysis of, 432–433 basic, 430 identification of, 432 owner, 431 powerful personal activities, 430 user nomination for DB2 Administration Instance, 26 user temporary tablespaces, 343 user-defined functions (UDFs) calling, 159–160 example of, 158–159 managing, 161 modifiers for, 157–158 syntax for, 157 types of, 156 USEREXIT setting (archive logging), 363 users See also authentication adding, express technique for, 180 granting object privileges to, 187–190 preinstallation requirements for, 14 USERSPACE1, 342 utilities db2evmon, 475 DB2LEVEL (Version Information), 82 db2licm, 35 db2look, 441–443, 445–446 db2move, 441, 443–445 DB2PD (Problem Determination), 84–85 DB2SET (Registry Utility), 83 import load utility compared to, 423–425 options, 416 statistical summary, 415 syntax, 414 warning messages, 417–418 XML parsing and validation options, 421 load, 423–426 REORG, 487–488 REORGCHK, 485–487 testconn20.exe, 282–283 vsai.exe, 283 ■V validation of XML, 209 van Rossum, Guido, 330 VARCHAR data type, 129 VARGRAPHIC data type, 129 VENDOR setting (archive logging), 363 Venn diagrams, 119 Version Information Utility (DB2LEVEL), 82 versions of DB2, 9 view template (Visual Studio), 287–288 viewing database authorities, 179 views catalog, 113 creating, 233–235 description of, 215, 233, 237 object privileges, 185 options for, 235–237 selecting for Control Center, 49 system catalog, 113 virtual machines, download package for, 6 Vista (Windows) support, 11 Visual Basic and Visual Basic .NET, 334 Visual Studio Add-In installer (vsai.exe), 283 Visual Studio.NET (Microsoft) building .NET Web applications binding objects to controls, 296–297 building and running projects, 298–299 overview of, 295 building .NET Windows applications binding objects to controls, 291 exploring connection’s related objects, 294 overview of, 290 running projects, 294 specifying connection for, 292–293 IBM data server provider for .NET, registering, 281–282 IBM Database Add-Ins for installing, 279–280, 283 testing, 283–286 integration with, 279 managing objects building project, 289–290 creating new view object, 287–289 Server Explorer, 286–287 testing DB2 .NET connectivity, 282–283 votes cast table example, 146–152 vsai.exe (Visual Studio Add-In installer), 283 ■INDEX 513 942XCh25IndexCMP2 7/13/08 8:55 AM Page 513 ■W Wall, Larry, 323 warning messages import utility, 417–418 import utility output, 415 SQL27984W, 411 warningcount n option (import utility), 418 watchdog process, 70 WEBrick web server (Ruby) starting, 275 Welcome Aboard page, 276 websites DB2 on Rails toolkit, 263 IBM DB2 home page, 6 IBM DB2 permalink, 143 Information Center, 68 Python Package Index repository, 330 Zend, 241 welcome dialog box (Data Studio), 95 well-formed test of XML, 209 where clause select statement, 115–116 XQuery, 203 wildcards (XPath), 206 Windows AD domain features, 174 building .NET applications for with Visual Studio binding objects to controls, 291 exploring connection’s related objects, 294 overview of, 290 running projects, 294 specifying connection for, 292–293 DB2 Administration Instance and, 26 DB2 First Steps, launching under, 40–41 graphical installation under, 15–30 installing DB2 on, 11 installing Zend Core under, 242 Local System and Network Service accounts, 174 looking for groups in, 182–183 reviewing installation under, 35 Ruby, installing under, 266 users and groups under, 14 with check option (views), 235 Workgroup edition, 5 workload, 223 Workload dialog box (Configuration Advisor), 461 write-ahead logging, 360 writing body statements for stored procedures, 147 Java stored procedures, 313–314 JDBC applications connection URL, 304–305 data, working with, 307–309 features, working with, 309–311 overview of, 303–304 refactoring connection code, 305–307 required CLASSPATH inclusions or imports, 304 stored procedures, 105 ■X X server preinstallation requirements for, 15 security settings, and DB2 First Steps, 41 xhost command, 41 XML See also pureXML technology case sensitivity of, 201 delete statement, 213 insert statement, 208–210 predictions about, 195 in SAMPLE database, 195–199 schema registration, 210–212 Unicode and, 340 update statement, 212–213 XPath queries for, 204–206 XQuery for, 199–204 XML data type, 130 XML Extender component (DB2 Client), 390 XML Extender Samples component (DB2 Client), 391 XML objects, exporting, 411–412 XML Schema Repository (XSR), 210 XMLEXISTS function, 213 xmlns namespace attribute, 199 XMLPARSE function, 212 xmlparse preserve whitespace option (import utility), 421 XMLTable function, 207–208 XPath queries for XML overview of, 204–205 predicates, 205–206 wildcards, 206 XQuery for XML, 199–204 XPath compared to, 205 xquery keyword XPath, 204 XQuery, 200 XSR (XML Schema Repository), 210 xs:string notation, 202 ■INDEX514 942XCh25IndexCMP2 7/13/08 8:55 AM Page 514 ■Y .yml file extension, 268 ■Z z/OS, DB2 for, 6 Zend Core framework administrative login interface, 246 administrative summary dialog box, 247 administrator password dialog box, 246 classes, 262 configuring, 258–259 connection management, 259–260 custom component selection, 244 developing with, 258 executing statements, 260–262 file extension specification dialog box, 244 installing, 242–247 overview of, 241 post-installation checks, 247–248 summary dialog box, 246 web server selection, 244 ■INDEX 515 942XCh25IndexCMP2 7/13/08 8:55 AM Page 515