Foreword | p. xvii |
Acknowledgments | p. xix |
Introduction | p. xxi |
Basic Database Concepts | |
DB2 Database Architecture | p. 3 |
The Relational Database | p. 3 |
Relational Database Objects | p. 4 |
Databases | p. 4 |
Table Spaces | p. 4 |
Tables | p. 6 |
Indexes | p. 8 |
Views | p. 9 |
Packages (Access Plans) | p. 10 |
Triggers | p. 11 |
Aliases | p. 11 |
Event Monitors | p. 12 |
Schemas | p. 12 |
System Catalog Views | p. 13 |
Recovery Log File | p. 13 |
Configuration Files | p. 14 |
DB2 Database Directories | p. 14 |
Physical Database Directory | p. 14 |
Volume Directory | p. 14 |
System Directory | p. 15 |
Workstation Directory | p. 16 |
Database Connection Services Directory | p. 16 |
Summary | p. 17 |
Database Consistency Mechanisms | p. 18 |
What is Database Consistency? | p. 18 |
Transactions | p. 19 |
Concurrency and Isolation Levels | p. 19 |
Repeatable Read | p. 20 |
Read Stability | p. 20 |
Cursor Stability | p. 21 |
Uncommitted Read | p. 21 |
Specifying the Isolation Level | p. 21 |
Locking | p. 21 |
Lock Attributes | p. 22 |
Lock States | p. 22 |
Locks and Application Performance | p. 24 |
Transaction Logging | p. 27 |
Summary | p. 29 |
Application Development Fundamentals | |
The Application Development Process | p. 33 |
What is a DB2 Database Application? | p. 33 |
Designing a DB2 Database Application | p. 35 |
Elements of a DB2 Database Application | p. 35 |
High-Level Programming Language | p. 35 |
Structured Query Language (SQL) Statements | p. 36 |
Call-Level Interface (CLI) Function Calls | p. 37 |
Application Programming Interface (API) Function Calls | p. 39 |
Establishing the DB2 Database Application Development Environment | p. 40 |
Establishing the DB2 Database Application Testing Environment | p. 42 |
Creating a Testing Database | p. 42 |
Creating Testing Tables and Views | p. 42 |
Generating Test Data | p. 43 |
Managing Transactions | p. 43 |
Creating and Preparing Source-Code Files | p. 44 |
Summary | p. 44 |
Writing Embedded SQL Applications | p. 45 |
Prototyping Embedded SQL Statements | p. 45 |
The Main Parts of an Embedded SQL Source-Code File | p. 46 |
Creating the Prolog | p. 47 |
Creating the Body | p. 49 |
Creating the Epilog | p. 50 |
Creating and Using a Common Error-Handling Routine | p. 52 |
Using Exception, Signal, and Interrupt Handlers | p. 53 |
Creating Executables and Packages | p. 53 |
Precompiling Source-Code Files | p. 53 |
Compiling Source-Code Files | p. 55 |
Linking Object Modules | p. 56 |
Creating and Binding Packages | p. 56 |
A Word About Timestamps | p. 56 |
Running, Testing, and Debugging Embedded SQL Applications | p. 57 |
Summary | p. 57 |
Writing CLI Applications | p. 59 |
Differences Between Embedded SQL and CLI Function Calls | p. 59 |
Prototyping CLI Function Calls | p. 60 |
The Main Parts of a CLI Source-Code File | p. 61 |
The Initialization Task | p. 61 |
The Transaction Processing Task | p. 62 |
The Termination Task | p. 67 |
Error Handling | p. 68 |
Evaluating Return Codes | p. 68 |
Evaluating SQLSTATEs (Diagnostic Messages) | p. 68 |
Evaluating SQLCA Return Codes | p. 69 |
Creating Executable Applications | p. 69 |
Running, Testing, and Debugging CLI Applications | p. 70 |
Advantages and Disadvantages of Using CLI Function Calls | p. 70 |
Summary | p. 72 |
Writing API Applications | p. 73 |
The Basic Structure of an API Source-Code File | p. 73 |
Types of API Function Calls | p. 74 |
API Naming Conventions | p. 76 |
API Data Structures | p. 77 |
Error Handling | p. 78 |
Evaluating Return Codes | p. 79 |
Evaluating SQLCA Return Codes | p. 79 |
Evaluating SQLSTATEs | p. 79 |
Creating Executable Applications | p. 79 |
Running, Testing, and Debugging API Applications | p. 80 |
Summary | p. 81 |
Improving Application Performance | p. 83 |
Tuning Embedded SQL Queries | p. 83 |
Queries Containing SELECT SQL Statements | p. 83 |
Queries That Access Two or More Tables | p. 84 |
Compound Queries | p. 85 |
Index Management | p. 85 |
Guidelines for Creating Indexes | p. 85 |
Optimizing Index Performance | p. 86 |
Table-Space Management | p. 86 |
Using the Precompiler Optimization Feature | p. 87 |
Specifying the Optimization Class | p. 88 |
Determining Which Optimization Class to Use | p. 88 |
Row Blocking | p. 89 |
Locks and Application Performance | p. 89 |
Code-Page Selection | p. 90 |
Summary | p. 90 |
Structured Query Language (SQL) Statements | |
Application Programming Language Construct Statements | p. 101 |
SQL Precompiler Directives | p. 101 |
Dynamic SQL Support | p. 101 |
Dynamic SQL and Parameter Markers | p. 103 |
Caching | p. 103 |
Begin Declare Section | p. 108 |
End Declare Section | p. 112 |
Include | p. 112 |
Whenever | p. 116 |
Begin Compound | p. 119 |
End Compound | p. 123 |
Prepare | p. 123 |
Describe | p. 131 |
Execute | p. 138 |
Execute Immediate | p. 146 |
Set Current Query Optimization | p. 148 |
Set Current Packageset | p. 151 |
Set Current Explain Snapshot | p. 154 |
Data Control Statements | p. 159 |
Connection Management | p. 159 |
States of an Application | p. 159 |
States of a Connection | p. 162 |
SQL Precompiler Options That Affect Connection Management | p. 163 |
DB2's SQL Authorization Facility | p. 165 |
Connect | p. 166 |
Set Connection | p. 176 |
Release | p. 177 |
Disconnect | p. 181 |
Commit | p. 182 |
Rollback | p. 184 |
Lock Table | p. 187 |
Grant (database authorities) | p. 189 |
Grant (index privileges) | p. 190 |
Grant (package privileges) | p. 191 |
Grant (table/view privileges) | p. 192 |
Revoke (database authorities) | p. 194 |
Revoke (index privileges) | p. 199 |
Revoke (package privileges) | p. 203 |
Revoke (table/view privileges) | p. 208 |
Data Definition Statements | p. 214 |
Creating Table Spaces | p. 215 |
Creating Tables | p. 215 |
Creating Indexes | p. 215 |
Creating Views | p. 216 |
Creating Aliases | p. 217 |
Creating Distinct Types | p. 217 |
Creating User-Defined Functions | p. 218 |
Create Tablespace | p. 218 |
Alter Tablespace | p. 225 |
Create Table | p. 230 |
Alter Table | p. 241 |
Create Index | p. 251 |
Create View | p. 256 |
Create Alias | p. 262 |
Create Distinct Type | p. 267 |
Set Constraints | p. 274 |
Comment On | p. 280 |
Drop | p. 287 |
Create Function | p. 291 |
Set Current Function Path | p. 302 |
Data Manipulation Statements | p. 305 |
Adding Data | p. 306 |
Changing Data | p. 306 |
Deleting Data | p. 306 |
Retrieving Data | p. 306 |
The Basic SELECT | p. 306 |
Retrieving Multiple Rows Using a Cursor | p. 308 |
Types of Cursors | p. 308 |
Insert | p. 309 |
Update | p. 316 |
Delete | p. 323 |
Declare Cursor | p. 329 |
Open | p. 331 |
Fetch | p. 333 |
Close | p. 342 |
Select Into | p. 342 |
Values Into | p. 344 |
Free Locator | p. 346 |
Event Monitor, Trigger, and Stored Procedure Processing Statements | p. 349 |
Using Event Monitors | p. 349 |
Event Counting | p. 351 |
Defining and Using Triggers | p. 352 |
Using Stored Procedures | p. 355 |
Create Event Monitor | p. 356 |
Set Event Monitor State | p. 366 |
Create Trigger | p. 368 |
Set | p. 378 |
Signal SQLSTATE | p. 381 |
Call | p. 384 |
Call Level Interface (CLI) Functions | |
CLI/ODBC Data Source Connection Control Functions | p. 397 |
Data Sources | p. 398 |
Environment and Connection Handles | p. 398 |
Connecting to a Data Source | p. 398 |
SQLAllocEnv | p. 399 |
SQLAllocConnect | p. 402 |
SQLConnect | p. 403 |
SQLDriverConnect | p. 405 |
SQLSetConnection | p. 412 |
SQLDisconnect | p. 416 |
SQLFreeConnect | p. 416 |
SQLFreeEnv | p. 417 |
CLI/ODBC Driver and Data Source Option Control Functions | p. 419 |
Obtaining Information About a Data Source | p. 420 |
Environment, Connection, and SQL Statement Options | p. 420 |
SQLDataSources | p. 421 |
SQLGetInfo | p. 425 |
SQLGetFunctions | p. 445 |
SQLGetTypeInfo | p. 450 |
SQLGetEnvAttr | p. 458 |
SQLSetEnvAttr | p. 463 |
SQLGetConnectOption | p. 467 |
SQLSetConnectOption | p. 476 |
SQLGetStmtOption | p. 480 |
SQLSetStmtOption | p. 489 |
SQL Statement Processing Functions | p. 494 |
Allocating a SQL Statement Handle | p. 494 |
Setting Parameter Values | p. 494 |
Executing SQL Statements | p. 496 |
Data-at-Execution Values | p. 496 |
Completing Transactions | p. 497 |
SQLAllocStmt | p. 497 |
SQLPrepare | p. 502 |
SQLBindParameter | p. 504 |
SQLBindFileToParam | p. 514 |
SQLSetParam | p. 518 |
SQLParamOptions | p. 526 |
SQLGetCursorName | p. 532 |
SQLSetCursorName | p. 534 |
SQLExecute | p. 537 |
SQLExecDirect | p. 538 |
SQLNativeSql | p. 540 |
SQLNumParams | p. 543 |
SQLParamData | p. 545 |
SQLPutData | p. 551 |
SQLCancel | p. 553 |
SQLTransact | p. 554 |
SQLFreeStmt | p. 558 |
SQL Statement Results Retrieval Functions | p. 561 |
Result Data Sets | p. 561 |
Assigning Application Storage Areas to Result Data Set Columns (Binding) | p. 562 |
Determining the Characteristics of a Result Data Set | p. 562 |
Assigning Application Storage Areas for Rowsets (Binding) | p. 563 |
Fetching Result Data Set Data | p. 564 |
Retrieving Data From Unbound Columns | p. 564 |
Retrieving Status, Warning, and Error Information | p. 564 |
SQLRowCount | p. 565 |
SQLNumResultCols | p. 570 |
SQLDescribeCol | p. 572 |
SQLColAttributes | p. 575 |
SQLSetColAttributes | p. 584 |
SQLBindCol | p. 590 |
SQLBindFileToCol | p. 598 |
SQLFetch | p. 604 |
SQLExtendedFetch | p. 606 |
SQLGetData | p. 612 |
SQLMoreResults | p. 619 |
SQLError | p. 623 |
SQLGetSQLCA | p. 628 |
SQLGetLength | p. 632 |
SQLGetPosition | p. 637 |
SQLGetSubString | p. 639 |
CLI/ODBC Data Source System Catalog Query Functions | p. 641 |
Obtaining System Catalog Information | p. 641 |
Controlling Catalog Function Results with Parameter Values | p. 642 |
Obtaining Information About Existing Stored Procedures | p. 643 |
Stored Procedures Catalog Table | p. 643 |
SQLTables | p. 644 |
SQLTablePrivileges | p. 650 |
SQLColumns | p. 656 |
SQLColumnPrivileges | p. 665 |
SQLSpecialColumns | p. 671 |
SQLStatistics | p. 679 |
SQLPrimaryKeys | p. 687 |
SQLForeignKeys | p. 692 |
SQLProcedures | p. 699 |
SQLProcedureColumns | p. 705 |
Application Programming Interface (API) Functions | |
Program Preparation and General Application Programming API's | p. 719 |
Embedded SQL Application Preparation | p. 719 |
Exception, Signal, and Interrupt Handlers | p. 720 |
Pointer Manipulation and Memory Copy Functions | p. 721 |
Evaluating SQLCA Return Codes and SQLSTATE Values | p. 721 |
Precompile Program | p. 721 |
Bind | p. 731 |
Rebind | p. 734 |
Get Instance | p. 738 |
Install Signal Handler | p. 739 |
Interrupt | p. 741 |
Get Address | p. 745 |
Copy Memory | p. 746 |
Dereference Address | p. 747 |
Get Error Message | p. 748 |
Get SQLState Message | p. 750 |
Get Authorizations | p. 752 |
DB2 Database Manager Control and Database Control API's | p. 755 |
The DB2 Database Manager Server Processes | p. 755 |
Creating, Deleting, and Restarting DB2 Databases | p. 756 |
Specifying Connection Accounting Strings | p. 756 |
Retrieving and Setting Other Connection Setting Values | p. 757 |
Controlling DB2 Database Manager Connection Instances | p. 758 |
Start Database Manager | p. 758 |
Stop Database Manager | p. 759 |
Create Database | p. 761 |
Restart Database | p. 767 |
Drop Database | p. 769 |
Attach | p. 770 |
Detach | p. 773 |
Set Accounting String | p. 773 |
Query Client | p. 775 |
Set Client | p. 778 |
Force Application | p. 779 |
DB2 Database Manager and Database Configuration API's | p. 783 |
Configuring DB2 | p. 783 |
DB2 Database Manager Configuration Parameters | p. 784 |
DB2 Database Configuration Parameters | p. 785 |
Get Database Manager Configuration | p. 785 |
Get Database Manager Configuration Defaults | p. 793 |
Update Database Manager Configuration | p. 795 |
Reset Database Manager Configuration | p. 798 |
Get Database Configuration | p. 801 |
Get Database Configuration Defaults | p. 808 |
Update Database Configuration | p. 811 |
Reset Database Configuration | p. 814 |
Database, Node, and DCS Directory Management API's | p. 817 |
DB2 Directories | p. 817 |
The System Database Directory | p. 818 |
Volume Directories | p. 819 |
The Workstation (Node) Directory | p. 819 |
The Database Connection Services (DCS) Directory | p. 819 |
Registering/Deregistering DB2 Database Servers with NetWare Servers | p. 820 |
Catalog Database | p. 820 |
Uncatalog Database | p. 823 |
Change Database Comment | p. 825 |
Open Database Directory Scan | p. 826 |
Get Next Database Directory Entry | p. 828 |
Close Database Directory Scan | p. 830 |
Catalog Node | p. 831 |
Uncatalog Node | p. 835 |
Open Node Directory Scan | p. 836 |
Get Next Node Directory Entry | p. 838 |
Close Node Directory Scan | p. 840 |
Catalog DCS Database | p. 840 |
Uncatalog DCS Database | p. 842 |
Open DCS Directory Scan | p. 844 |
Get DCS Directory Entries | p. 846 |
Get DCS Directory Entry for Database | p. 847 |
Close DCS Directory Scan | p. 848 |
Register | p. 848 |
Deregister | p. 851 |
Table and Table Space Management API's | p. 853 |
Table Spaces and Table Space Containers | p. 853 |
Reorganizing Table Data | p. 854 |
Updating Table Statistics | p. 855 |
Open Tablespace Query | p. 855 |
Fetch Tablespace Query | p. 858 |
Close Tablespace Query | p. 860 |
Tablespace Query | p. 860 |
Single Tablespace Query | p. 863 |
Get Tablespace Statistics | p. 865 |
Open Tablespace Container Query | p. 868 |
Fetch Tablespace Container Query | p. 870 |
Close Tablespace Container Query | p. 871 |
Tablespace Container Query | p. 872 |
Free Memory | p. 874 |
Reorganize Table | p. 875 |
Run Statistics | p. 878 |
Database Migration and Recovery API's | p. 881 |
Database Migration | p. 881 |
Creating Backup Images | p. 881 |
Restoring from Backup Images | p. 883 |
Roll-forward Recovery | p. 883 |
Recovery History Files | p. 883 |
Migrate Database | p. 885 |
Backup Database | p. 886 |
Restore Database | p. 894 |
Set Tablespace Containers | p. 901 |
Rollforward Database | p. 908 |
Open Recovery History File Scan | p. 917 |
Get Next Recovery History File Entry | p. 920 |
Close Recovery History File Scan | p. 924 |
Update Recovery History File | p. 925 |
Prune Recovery History File | p. 928 |
Data Handling API's | p. 933 |
Exporting Data | p. 933 |
Importing Data | p. 934 |
Loading Data | p. 934 |
Supported Export, Import, and Load File Formats | p. 936 |
Export | p. 936 |
Import | p. 943 |
Load | p. 954 |
Load Query | p. 966 |
Quiesce Tablespaces for Table | p. 967 |
Database Monitor and Indoubt Transaction Processing API's | p. 970 |
The DB2 Snapshot Monitor | p. 970 |
Working with Multiple Databases | p. 973 |
Understanding the Two-Phase Commit Process | p. 973 |
Get/Update Monitor Switches | p. 976 |
Reset Monitor | p. 979 |
Estimate Database System Monitor Buffer Size | p. 981 |
Get Snapshot | p. 983 |
List Indoubt Transactions | p. 989 |
Commit an Indoubt Transaction | p. 983 |
Rollback an Indoubt Transaction | p. 995 |
Forget Transaction Status | p. 997 |
SQL Data Structures | p. 999 |
The SQL Communications Area (SQLCA) Structure | p. 999 |
The SQL Descriptor Area (SQLDA) Structure | p. 1001 |
The SQLCHAR Structure | p. 1006 |
SQL Functions | p. 1007 |
SQLSTATE Cross-Reference | p. 1016 |
Bibliography | p. 1029 |
Index | p. 1031 |
About the Author | p. 1046 |
Table of Contents provided by Syndetics. All Rights Reserved. |