The developer's handbook to DB2 for common servers

목차 (Table of Contents)

• CONTENTS
• Foreword = xvii
• Acknowledgments = xix
• Introduction = xxi
• PART 1 BASIC DATABASE CONCEPTS
• 1. DB2 DATABASE ARCHITECTURE = 3
• THE RELATIONAL DATABASE = 3
• RELATIONAL DATABASE OBJECTS = 4
• Databases = 4
• Table Spaces = 4
• Tables = 6
• Indexes = 8
• Views = 9
• Packages (Access Plans) = 10
• Triggers = 11
• Aliases = 11
• Event Monitors = 12
• Schemas = 12
• System Catalog Views = 13
• Recovery Log File = 13
• Configuration File = 14
• DB2 DATABASE DIRECTORIES = 14
• Physical Database Directory = 14
• Volume Directory = 14
• System Directory = 15
• Workstation Directory = 16
• Database Connection Services Directory = 16
• SUMMARY = 17
• 2. DATABASE CONSISTENCY MECHANISMS = 18
• WHAT IS DATABASE CONSISTENCY? = 18
• TRANSACTIONS = 19
• CONCURRENCY AND ISOLATION LEVELS = 19
• Repeatable Read = 20
• Read Stability = 20
• Cursor Stability = 21
• Uncommitted Read = 21
• Specifying the Isolation Level = 21
• LOCKING = 21
• Lock Attributes = 22
• Lock States = 22
• Locks and Application Performance = 24
• Transaction Logging = 27
• SUMMARY = 29
• PART 2 APPLICATION DEVELOPMENT FUNDAMENTALS
• 3. THE APPLICATION DEVELOPMENT PROCESS = 33
• WHAT IS A DB2 DATABASE APPLICATION? = 33
• DESIGNING DB2 DATABASE APPLICATION = 35
• ELEMENTS OF A DB2 DATABASE APPLICATION = 35
• High-Level Programming Language = 35
• Structured Query Language (SQL) Statements = 36
• Call-Level Interface (CLI) Function Calls = 37
• Application Programming Interface (API) Function Calls = 39
• ESTABLISHING THE DB2 DATABASE APPLICATION DEVELOPMENT ENVIRONMENT = 40
• ESTABLISHING THE DB2 DATABASE APPLICATION TESTING ENVIRONMENT = 42
• Creating a Testing Database = 42
• Creating Testing Tables and Views = 42
• Generating Test Data = 43
• MANAGING TRANSACTIONS = 43
• CREATING AND PREPARING SOURCE-CODE FILES = 44
• SUMMARY = 44
• 4. WRITING EMBEDDED SQL APPLICATIONS = 45
• PROTOTYPING EMBEDDED SQL STATEMENTS = 45
• THE MAIN PARTS OF AN EMBEDDED SQL SOURCE-CODE FILE = 46
• Creating the Prolog = 47
• Creating the Body = 49
• Creating the Epilog = 50
• CREATING AND USING A COMMON ERROR-HANDLING ROUTINE = 52
• USING EXCEPTION, SIGNAL, AND INTERRUPT HANDLERS = 53
• CREATING EXECUTABLES AND PACKAGES = 53
• Precompiling Source-Code Files = 53
• Compiling Source-Code Files = 55
• Linking Object Modules = 56
• Creating and Binding Package = 56
• A WORD ABOUT TIMESTAMPS = 56
• RUNNING, TESTING, AND DEBUGGING EMBEDDED SQL APPLICATIONS = 57
• SUMMARY = 57
• 5. WRITING CLI APPLICATIONS = 59
• DIFFERENCES BETWEEN EMBEDDED SQL AND CLI FUNCTION CALLS = 59
• PROTOTYPING CLI FUNCTION CALLS = 60
• THE MAIN PART SOF A CLI SOURCE-CODE FILE = 61
• The Initialization Task = 61
• The Transaction Processing Task = 62
• The Termination Task = 67
• ERROR HANDLING = 68
• Evaluating Return Codes = 68
• Evaluating SQLSTATEs (Diagnostic Messages) = 68
• Evaluating SQLCA Return Codes = 69
• CRATING EXECUTABLE APPLICATIONS = 69
• RUNNING, TESTING, AND DEBUGGING CLI APPLICATIONS = 70
• ADAVANTAGES AND DISADVANTAGES OF USING CLI FUNCTION CALLS = 70
• SUMMARY = 72
• 6. WRITING API APPLICATIONS = 73
• THE BASIC STRUCTURE OF AN API SOURCE-CODE FILE = 73
• TYPES OF API FUNCTION CALLS = 74
• API NAMING CONVENTIONS = 76
• API DATA STRUCTURES = 77
• ERROR HANDLING = 78
• Evaluating Return Codes = 79
• Evaluating SQLCA Return Codes = 79
• Evaluating SQLSTATEs = 79
• CREATING EXECUTABLE APPLICATIONS = 79
• RUNNING, TESTING, AND DEBUGGING API APPLICATIONS = 80
• SUMMARY = 81
• 7. IMPROVING APPLICATION PERFORMANCE = 83
• TUNING EMBEDDED SQL QUERIES = 83
• Queries Containing SELECT SQL Statements = 83
• Queries That Access Two of More Tables = 84
• Compound Queries = 85
• INDEX MANAGEMENT = 85
• Guidelines for Creating Indexes = 85
• Optimizing Index Performance = 86
• TABLE-SPACE MANAGEMENT = 86
• USING THE PRECOMPILER OPTIMIZATION FEATURE = 87
• Specifying the Optimization Class = 88
• Determining Which Optimization Class to Use = 88
• ROW BLOCKING = 89
• LOCKS AND APPLICATION PERFORMANCE = 89
• CODE-PAGE SELECTION = 90
• SUMMARY = 90
• PART 3 STRUCTURED QUERY LANGUAGE (SQL) STATEMENTS
• 8. APPLICATION PROGRAMMING LANGUAGE CONSTRUCT STATEMENT = 101
• SQL Precompiler Directives = 101
• Dynamic SQL Support = 101
• Dynamic SQL and Parameter Markers = 103
• Caching = 103
• BEGIN DECLARE SECTION = 108
• END DECLARE SECTION = 112
• INCLUDE = 112
• WHENEVER = 116
• BEGIN COMPOUND = 119
• END COMPOUND = 123
• PREFACE = 123
• DESCRIBE = 131
• EXECUTE = 138
• EXECUTE IMMEDIATE = 146
• SET CURRENT QUERY OPTIMIZATION = 148
• SET CURRENT PACKAGESET = 151
• SET CURRENT EXPLAIN SNAPSHOT = 154
• 9. DATA CONTROL STATEMENTS = 159
• Connection Management = 159
• States of an Application = 159
• States of a Connection = 162
• SQL Precompiler Options That Affect Connection Management = 163
• DB2's SQL Authorization Facility = 165
• CONNECT = 166
• SET CONNECTION = 176
• RELEASE = 177
• DISCONNECT = 181
• COMMIT = 182
• ROLLBACK = 184
• LOCK TABLE = 187
• GRANT (database authorities) = 189
• GRANT (index privileges) = 190
• GRANT (package privileges) = 191
• GRANT (table / view privileges) = 192
• REVOKE (database authorities) = 194
• REVOKE (index privileges) = 199
• REVOKE (package privileges) = 203
• REVOKE (table / view privileges) = 208
• 10. DATA DEFINITION STATEMENTS = 214
• Creating Table Spaces = 215
• Creating Tables = 215
• Creating Indexes = 215
• Creating views = 216
• Creating Aliases = 217
• Creating Distinct Types = 217
• Creating User-Defined Functions = 218
• CREATE TABLESPACE = 218
• ALTER TABLESPACE = 225
• CREATE TABLE = 230
• ALTER TABLE = 241
• CREATE INDEX = 251
• CREATE VIEW = 256
• CREATE ALIAS = 262
• CREATE DISTINCT TYPE = 267
• SET CONSTRAINTS = 274
• COMMETON = 280
• DROP = 287
• CREATE FUNCTION = 291
• SET CURRENT FUNCTION PATH = 302
• 11. DATA MANIPULATION STATEMENTS = 305
• Adding Data = 306
• Changing Data = 306
• Deleting Data = 306
• Retrieving Data = 306
• The Basic SELECT = 306
• Retrieving Multiple Rows Using a Cursor = 308
• Types of Cursors = 308
• INSERT = 309
• UPDATE = 316
• DELETE = 323
• DECLARE CURSOR = 329
• OPEN = 331
• FETCH = 333
• CLOSE = 342
• SELECT INTO = 342
• VALUES INTO = 344
• FREE LOCATOR = 346
• 12. EVENT MONITOR, TRIGGER, AND STORED PROCEDURE PROCESSING STATEMENTS = 349
• Using Event Monitors = 349
• Event Counting = 351
• Defining and Using Triggers = 352
• Using Stored Procedures = 355
• CREATE EVENT MONITOR = 356
• SET EVENT MONITOR STATE = 366
• CREATE TRIGGER = 368
• SET = 378
• SIGNAL SQLSTATE = 381
• CALL = 384
• PART 4 CALL LEVEL INTERFACE (CLI) FUNCTIONS
• 13. CLE / ODBC DATA SOURCE CONNECTION CONTROL FUNCTIONS = 397
• Data Sources = 398
• Environment andConnection Handles = 398
• Connecting to a Data Source = 398
• SQLAOOLeNV = 399
• SQLAllocConnect = 402
• SQLConnect = 403
• SQLDriverConnect = 405
• SQLSerConnection = 412
• SQLDisconnect = 416
• SQLFreeConnect = 416
• SQLFreeEnv = 417
• 14. CLI/ODBC DRIVER AND DATA SOURCE OPTION CONTROL FUNCTIONS = 419
• Obtaining Information About a Data Source = 420
• Environment, Connection, and SQL Satement Options = 420
• SQLDataSources = 421
• SQLGetInfo = 425
• SQLGetFunctions = 445
• SQLGetTypeInfo = 450
• SQLGetEnvAttr = 458
• SQLSetEnvAttr = 463
• SQLGetConnectOption = 467
• SQLSetConnectOption = 476
• SQLGetStmtOption = 480
• SQLSetStmtOption = 489
• 15. SQl STATEMENT PROCESSING FUNTIONS = 494
• Allocating a SQL Statement Handle = 494
• Setting Parameter Values = 494
• Executing SQL Statements = 496
• Data-at-Execution Values = 496
• Completing Transactions = 497
• SQLAllocStmt = 497
• SQLPrepare = 502
• SQLBindParameter = 504
• SQLBindFileToParam = 514
• SQLSetParam = 518
• SQLParamOptions = 526
• SQLGetCursorName = 532
• SQLSetCursorName = 534
• SQLExecDirect = 537
• SQLExecDirect = 538
• SQLNativeSql = 540
• SQLNumParms = 543
• SQLParamData = 545
• SQLPutData = 551
• SQLCancel = 553
• SQLTransact = 554
• SQLFreeStmt = 558
• 16. SQL STATEMENT RESULTS RETRIEVAL FUNCTIONS = 561
• Result Data Sets = 561
• Assigning Application Storage Areas to Result Data Set Columns (Binding) = 562
• Determining the Characteristics of a Result Data Set = 562
• Assiging Application Storage Areas for Rowsets (Bindeng) = 563
• Fetching Result Data Set Data = 564
• Retrieving Data From Unbound Columns = 564
• Retrieving Status, Warning, and Error Information = 564
• SQLRowCount = 565
• SQLNumResultCols = 570
• SQLDescribeCol = 572
• SQLColAttributes = 575
• SQLSetColAttributes = 584
• SQLBindCol = 590
• SQLBindFileToCol = 598
• SQLFetch = 604
• SQLExtendedFetch = 606
• SQLGetData = 612
• SQLMoreResults = 619
• SQLError = 623
• SQLGetSQLCA = 628
• SQLGetLength = 632
• SQLGetPosition = 637
• SQLGetSubString = 639
• 17. CLI/ODBC DATA SOURCE SYSTEM CATALOG QUERY FUNCTION = 641
• Obtaining System Catalog Information = 641
• Controlling Catalog Functions Results with Parameter Values = 642
• Obtaining Information Aboue Existing Stored Procedures = 643
• Stored Procedures Catalog Table = 643
• SQLTables = 644
• SQLTablePrivileges = 650
• SQLColumns = 656
• SQLColumnPrivileges = 665
• SQLSpecialColumns = 671
• SQLStatistics = 679
• SQLPrimaryKeys = 687
• SQLForeignKeys = 692
• SQLProcedures = 699
• SQLProcedureColumns = 705
• PART 5 APPLICATION PROGRAMMING INTERFACE (API) FUNCTIONS
• 18. PROGRAM PREPARATION AND GENERAL APPLICATION PROGRAMMING API'S = 719
• Embedded SQL Application Preparation = 719
• Exception, Signal, m and Interrupt Handlers = 720
• Pointer Manipulation and Memory Copy Functions = 721
• Evaluating SQLCA Return Codes and SQLSTATE Values = 721
• PRECOMPILE PROGRAM = 721
• BIND = 731
• REBIND = 734
• GET INSTANCE = 738
• INSTALL SIGNAL HANDLER = 739
• INTERRUPT = 71
• GET ADDRESS = 745
• COPY MEMORY = 746
• DEREFERENCE ADDRESS = 747
• GET ERROR MESSAGE = 748
• GET SQLSTATE MESSAGE = 750
• GET AUTHORIZATIONS = 752
• 19. DB2 DATABASE MANAGER CONTROL AND DATABASE CONTROL API's = 755
• The DB2 Database Manager Server Processes = 755
• Creating, Deleting, and Restarting DB2 Databases = 756
• Specifying Connection Accounting Strings = 756
• Retrieving and Setting Other Connection Setting Values = 757
• Controlling DB2 Database Manager Connection Instances = 758
• START DATABASE MANAGER = 758
• STOP DATABASE MANAGER = 759
• CREATE DATABASE = 761
• RESTART DATABASE = 767
• DROP DATABASE = 769
• ATTACH = 770
• DETACH = 773
• SET ACCOUNTING STRING = 773
• QUERY CLIENT = 775
• SET CLIENT = 778
• FORCE APPLICATION = 779
• 20. DB2 DATABASE MANAGER AND DATABASE CONFIGURATION API'S = 783
• Configuring DB2 = 783
• DB2 Database Manager Configuration Parameters = 784
• DB2 Database Configuration Parameters = 785
• GET DATABASE MANAGER CONFIGURATION = 785
• GET DATABASE MANAGER CONFIGURATION DEFAULTS = 793
• UPDATE DATABASE MANAGER CONFIGURATION = 795
• RESET DATABASE MANAGER CONFIGURATION = 798
• GET DATABASE CONFIGURATION = 801
• GET DATABASE CONFIGURATION DEFAULTS = 808
• UPDATE DATABASE CONFIGURATION = 811
• RESET DATABASE CONFIGURATION = 814
• 21. DATABASE, NODE, AND DCS DIRECTORY MANAGEMENT API'S = 817
• DB2 Directories = 817
• The System Database Directory = 818
• Volume Directories = 819
• The Workstation (Node) Directory = 819
• The Database Connection Services (DCS) Directory = 819
• Registering/Detegistering DB2 Database Servers with NetWare Servers = 820
• CALOG DATABASE = 820
• UNCATALOG DATABASE = 823
• CHANGE DATABASE COMMENT = 825
• OPEN DATABASE DIRECTORY SCAN = 826
• GET NEXT DATABASE DIRECTORY ENTRY = 828
• CLOSE DATABASE DIRECTORY SCAN = 830
• CATALOG NODE = 831
• UNCATALOG NODE = 835
• OPEN NODE DIRECTORY SCAN = 836
• GET NEXT NODE DIRECTORY ENTRY = 838
• CLOSE NODE DIRECTORY SCAN = 840
• CATALOG DCS DATABASE = 840
• UNCATALOG DCS DATABASE = 842
• OPEN DCS DIRECTORY SCAN = 844
• GET DCS DIRECTORY SCAN = 846
• GET DCS DIRECTORY ENTRY FOR DATABASE = 847
• CLOSE DCS DIRECTORY SCAN = 848
• REGISTER = 848
• DEREGISTER = 851
• 22. TABLE AND TABLE SPACE MANAGEMENT API'S = 853
• Table Spaces and Table Space Containers = 853
• Reorganizing Table Data = 854
• Updating Table Statics = 855
• OPEN TABLESPACE QUERY = 855
• FETCH TABLESPACE QUERY = 858
• CLOSE TABLESPACE QUERY = 860
• TABLESPACE QUERY = 860
• SINGLE TABLESPACE QUERY = 863
• GET TABLESPACE STATISTICS = 865
• OPEN TABLESPACE CONTAINER QUERY = 868
• FETCH TABLESPACE CONTAINER QUERY = 870
• CLOSE TABLESPACE CONTAINER QUERY = 871
• TABLESPACE CONTANIZE QUERY = 872
• FREE MEMORY = 874
• REORGANIZE TABLE = 875
• RUN STATISTICS = 878
• 23. DATABAE MIGRATION AND RECOVERY API'S = 881
• Database Migration = 881
• Creating Backup Images = 881
• Restoring from Backup Images = 883
• Roll-forward Recovery = 883
• Recovery History Files = 883
• MIGRATE DATABASE = 885
• BACKUP DATABASE = 886
• RESTORE DATABASE = 894
• SET TABLESPACE CONTAINERS = 901
• ROLLFORWARD DATABASE = 908
• OPEN RECOVERY HISTORY FILE SCAN = 917
• GET NEXT RECOVERY HISTORY FILE ENTRY = 920
• CLOSE RECOVERY HISTORY FILE SCAN = 924
• UPDATE RECOVERY HISTORY FILE = 925
• PRUNE RECOVERY HISTORY FILE = 928
• 24. DATA HANDLING API'S = 933
• Exporting Data = 933
• Importing Data = 934
• Loading Data = 934
• Supported Export, Import, and Load File Formats = 936
• EXPORT = 936
• IMPORT = 943
• LOAD = 954
• LOAD QUERY = 966
• QUIESCE TABLESPACES FOR TABLE = 967
• 25. DATABASE MONITOR AND INDUOBT TRANSACTION PROCESSING API'S = 970
• The DB2 Snapshot Monitor = 970
• Working with Multiple Databases = 973
• Understanding the Two-Phase Commit Process = 973
• GET / UPDATE MONITOR SWITCHES = 976
• RESET MONITOR = 979
• ESTIMATE DATABASE SYSTEM MONITOR BUFFER SIZE = 981
• GET SNAPSHOT = 983
• LIST INDOUBT TRANSACTIONS = 989
• COMMIT AN INDOUBT TRANSACTION = 983
• ROLLBACK AN INDOUBT TRANSACTION = 995
• FORGET TRANSACTION STATUS = 997
• A. SQL DATA STRUCTURES = 999
• The SQL Communications Area (SQLCA) Structure = 999
• The SQL Descriptor Area (SQLDA) Structure = 1001
• The SQLCHAR Structure = 1006
• B. SQL FUNCTIONS = 1007
• C. SQLSTATE CROSS-REFERENCE = 1016
• Bibliography = 1029
• Index = 1031
• About the Author = 1046