DB2 Application development Guide

install db2 application development client and mainframe db2 - application development and ibm db2 application development client pdf free download
Dr.LewisFinch Profile Pic
Published Date:15-07-2017
Your Website URL(Optional)
® ® IBM DB2 Universal Database  Application Development Guide Version 7 SC09-2949-01Chapter 1. Getting Started with DB2 Application Development About This Book...........3 Conventions ...........7 Who Should Use This Book .......4 Related Publications.........8 How to Use This Book.........4 About This Book This book discusses how to design and code application programs that access DB2 databases. It presents detailed information on the use of Structured Query Language (SQL) in supported host language programs. For information on language support for your specific operating system, see the Application Building Guide. This book also provides an overview of some of the DB2 utilities that you can use to help create DB2 applications. These utilities include “The IBM DB2 Universal Database Project Add-In for Microsoft Visual C++” on page 30 and “Chapter 9. IBM DB2 Stored Procedure Builder” on page 269. You can access data with: v SQL statements embedded in a host language, including embedded SQL for Java (SQLJ) v dynamic APIs including Java Database Connectivity (JDBC), Perl DBI, and DB2 Call Level Interface (DB2 CLI) This book discusses all these ways to access data except DB2 CLI, which is discussed in the CLI Guide and Reference. JDBC, SQLJ, and DB2 CLI provide some data access capabilities that are not available through embedded SQL. These capabilities include scrollable cursors and stored procedures that return multiple result sets. See the discussion in “Access to Data” on page 23 to help you decide which data access method to use. To effectively use the information in this book to design, write, and test your DB2 application programs, you need to refer to the SQL Reference along with this book. If you are using the DB2 Call Level Interface (CLI) or Open Database Connectivity (ODBC) interface in your applications to access DB2 databases, refer to the CLI Guide and Reference. To perform database manager administration functions using the DB2 administration APIs in your application programs, refer to the Administrative API Reference. You can also develop applications where one part of the application runs on the client and another part runs on the server. Version 7 of DB2 introduces © Copyright IBM Corp. 1993, 2001 3support for stored procedures with enhanced portability and scalability across platforms. Stored procedures are discussed in “Chapter 7. Stored Procedures” on page 193. You can use object-based extensions to DB2 to make your DB2 application programs more powerful, flexible, and active than traditional DB2 applications. The extensions include large objects (LOBs), distinct types, structured types, user-defined functions (UDFs), and triggers. These features of DB2 are described in: v “Chapter 10. Using the Object-Relational Capabilities” on page 275 v “Chapter 11. User-defined Distinct Types” on page 281 v “Chapter 12. Working with Complex Objects: User-Defined Structured Types” on page 291 v “Chapter 13. Using Large Objects (LOBs)” on page 349 v “Chapter 14. User-Defined Functions (UDFs) and Methods” on page 373 v “Chapter 15. Writing User-Defined Functions (UDFs) and Methods” on page 393 v “Chapter 16. Using Triggers in an Active DBMS” on page 483 References to DB2 in this book should be understood to mean the DB2 Universal Database product on UNIX, Linux, OS/2, and Windows 32-bit operating systems. References to DB2 on other platforms use a specific product name and platform, such as DB2 Universal Database for AS/400. Who Should Use This Book This book is intended for programmers who are experienced with SQL and with one or more of the supported programming languages. How to Use This Book This book is organized, by task, into the following parts, chapters, and appendices: v Part 1. DB2 Application Development Concepts contains information you need to use this book and an overview of the methods you can use to develop applications for DB2 Universal Database. – Chapter 1. Getting Started with DB2 Application Development describes the structure of this book and the conventions used in it. – Chapter 2. Coding a DB2 Application introduces the overall application development process using DB2. It discusses and compares the important application design issues you need to consider prior to coding 4 Application Development Guideyour applications. This chapter concludes with information to help you set up a test environment where you can begin to develop your applications. v Part 2. Embedding SQL in Applications describes how to embed static and dynamic SQL in your applications. This information includes a description of the utilities that you can use to help create your embedded SQL applications. – Embedding SQL Statements in a Host Language discusses the process of creating a DB2 application by embedding SQL in host languages such as C/C++, Java, and COBOL. It contains an overview of the DB2 precompiler, compiling and linking the application, and binding the embedded SQL statements to the database. – Chapter 4. Writing Static SQL Programs discusses the details of coding your DB2 embedded SQL application using static SQL statements. It contains detailed guidelines and considerations for using static SQL. – Chapter 5. Writing Dynamic SQL Programs discusses the details of coding your DB2 embedded SQL application using dynamic SQL statements. It contains detailed guidelines and considerations for using dynamic SQL. – Chapter 6. Common DB2 Application Techniques discusses DB2 features that help you with common application development problems. These features include the ability to automatically create unique row identifiers, to create columns that are dynamically derived from an expression, and to create and use declared temporary tables. v Part 3. Stored Procedures discusses how to use stored procedures to improve the performance of database applications that run in client/server environments. – Chapter 7. Stored Procedures describes how to write stored procedures and the client applications that call stored procedures using host languages. – Chapter 8. Writing SQL Procedures describes how to write stored procedures in SQL by issuing a CREATE PROCEDURE statement. SQL procedures encode their procedural logic using SQL in the body of the CREATE PROCEDURE statement. – Chapter 9. IBM DB2 Stored Procedure Builder describes the IBM DB2 Stored Procedure Builder, a graphical application that supports the rapid development of stored procedures for DB2. Stored Procedure Builder helps you create both SQL and Java stored procedures. v Part 4. Object-Relational Programming describes how to use the object-relational support provided by DB2. This information includes an introduction to and detailed instructions on how to use large objects, user-defined functions, user-defined distinct types, and triggers. Chapter 1. Getting Started with DB2 Application Development 5– Chapter 10. Using the Object-Relational Capabilities introduces the object-oriented capabilities of DB2. It explains how to extend your traditional application to one that takes advantage of DB2 capabilities such as large objects, user-defined functions, and user-defined distinct types in an object-oriented context. – Chapter 11. User-defined Distinct Types describes how to create and use your own data types in applications. It explains how to use distinct types as a foundation for object-oriented extensions to the built-in data types. – Chapter 12. Working with Complex Objects: User-Defined Structured Types describes how to create and use structured types in applications. It explains how to model objects as hierarchies of structured types, access instances of structured types as rows or columns in tables, and bind structured types into and out of your applications. – Chapter 13. Using Large Objects (LOBs) describes how to define and use data types that can store data objects as binary or text strings of up to two gigabytes in size. It also explains how to efficiently use LOBs in a networked environment. – Chapter 14. User-Defined Functions (UDFs) and Methods describes how to write your own extensions to SQL. It explains how to use UDFs to express the behavior of your data objects. – Chapter 15. Writing User-Defined Functions (UDFs) and Methods describes how to write user-defined functions that extend your DB2 applications. Topics include the details of writing a user-defined function, programming considerations for user-defined functions, and several examples that show you how to exploit this important capability. In addition, this chapter describes user-defined table functions, OLE DB table functions, and OLE automation UDFs. – Chapter 16. Using Triggers in an Active DBMS describes how to use triggers to encapsulate and enforce business rules within all of your database applications. v Part 5. DB2 Programming Considerations contains information on special application development considerations. – Chapter 17. Programming in Complex Environments discusses advanced programming topics such as national language support, dealing with ® Extended UNIX Code (EUC) code pages for databases and applications, accessing multiple databases within a unit of work, and creating multi-threaded applications. – Chapter 18. Programming Considerations in a Partitioned Environment describes programming considerations if you are developing applications that run in a partitioned environment. – Chapter 19. Writing Programs for DB2 Federated Systems describes how to create applications that transparently access data from DB2 family and Oracle data sources through a federated server. 6 Application Development Guidev Part 6. Language Considerations contains specific information about the programming languages that DB2 supports. – Chapter 20. Programming in C and C++ discusses host language specific information concerning database applications written in C and C++. – Chapter 21. Programming in Java discusses host language specific information concerning database applications written in Java using JDBC or SQLJ. – Chapter 22. Programming in Perl discusses host language specific information concerning database applications written in Perl using the DBD::DB2 database driver for the Perl Database Interface (DBI) Module. – Chapter 23. Programming in COBOL discusses host language specific information concerning database applications written in COBOL. – Chapter 24. Programming in FORTRAN discusses host language specific information concerning database applications written in FORTRAN. – Chapter 25. Programming in REXX discusses host language specific information concerning database applications written in REXX. v The Appendices contain supplemental information to which you may need to refer when developing DB2 applications. – Appendix A. Supported SQL Statements lists the SQL statements supported by DB2 Universal Database. – Appendix B. Sample Programs contains information on supplied sample programs for supported host languages and describes how they work. – Appendix C. DB2DARI and DB2GENERAL Stored Procedures and UDFs contains information you can use to create stored procedures and UDFs that are compatible with previous versions of DB2 Universal Database. – Appendix D. Programming in a Distributed Environment Programming in a Host or AS/400 Environment describes programming considerations for DB2 Connect if you access host or AS/400 database servers in your applications in a distributed environment. – Appendix E. Simulating EBCDIC Binary Collation describes how to collate DB2 character strings according to an EBCDIC, or user-defined, collating sequence. – Appendix F. Using the DB2 Library shows you where you can get more information for the DB2 Universal Database product. Conventions This book uses the following conventions: Directories and Paths This book uses the UNIX convention for delimiting directories, for example: sqllib/samples/java. You can convert these paths to Windows 32-bit operating system and OS/2 paths by changing the / to a \ and prepending the appropriate installation drive and directory. Chapter 1. Getting Started with DB2 Application Development 7Italics Indicates one of the following: v Introduction of a new term v Variable names or values that are supplied by the user v Reference to another source of information, for example, a book or CD-ROM v General emphasis UPPERCASE Indicates one of the following: v Abbreviations v Database manager data types v SQL statements Example Indicates one of the following: v Coding examples and code fragments v Examples of output, similar to what is displayed by the system v Examples of specific data values v Examples of system messages v File and directory names v Information that you are instructed to type v Java method names v Function names v API names Bold Bold text emphasizes a point. Related Publications The following manuals describe how to develop applications for international use and for specific countries: Form Number Book Title SE09-8001-03 National Language Design Guide, Volume 1 SE09-8002-03 NLS Reference Manual, Release 4 8 Application Development GuideChapter 2. Coding a DB2 Application Prerequisites for Programming ......9 Application Logic and Program Variable DB2 Application Coding Overview ....10 Types ............27 Declaring and Initializing Variables . . . 11 Data Relationship Control ......27 Declaring Variables that Interact with Referential Integrity Constraints . . . 28 the Database Manager.......11 Triggers ...........28 HandlingErrorsandWarnings....14 Application Logic........29 Using Additional Nonexecutable Application Logic at the Server ....29 Statements ..........16 StoredProcedures........29 Connecting to the Database Server . . . 16 User-Defined Functions ......29 Coding Transactions ........17 Triggers ...........30 Beginning a Transaction......18 The IBM DB2 Universal Database Project Ending a Transaction.......18 Add-InforMicrosoftVisualC++....30 Ending the Program ........19 Activating the IBM DB2 Universal Implicitly Ending a Transaction ....19 Database Project Add-In for Microsoft On Most Supported Operating Systems 20 VisualC++ ..........32 On Windows 32-bit Operating Systems 20 Activating the IBM DB2 Universal When Using the DB2 Context APIs . . 20 Database Tools Add-In for Microsoft Application Pseudocode Framework . . . 20 VisualC++ ..........33 Designing an Application For DB2 ....21 Supported SQL Statements.......33 Access to Data ..........23 AuthorizationConsiderations ......34 EmbeddedSQL.........23 Dynamic SQL ..........34 DB2 Call Level Interface (DB2 CLI) and StaticSQL............35 Open Database Connectivity (ODBC) . 24 UsingAPIs ...........35 JDBC ............24 Example ............36 Microsoft Specifications ......25 Database Manager APIs Used in Embedded PerlDBI ...........25 SQLorDB2CLIPrograms .......36 QueryProducts.........25 Setting Up the Testing Environment....37 Data Value Control.........25 Creating a Test Database.......37 DataTypes ..........26 CreatingTestTables ........37 Unique Constraints .......26 GeneratingTestData........38 Table Check Constraints......26 Running, Testing and Debugging Your Referential Integrity Constraints . . . 26 Programs.............40 ViewswithCheckOption .....27 PrototypingYourSQLStatements.....41 Prerequisites for Programming This chapter presents a model of the logical parts of a DB2 application and discusses the individual strengths of the supported DB2 programming APIs. Programmers who are new to developing a DB2 application should read the entire chapter closely. The application development process described in this book assumes that you have established the appropriate operating environment. This means that the following are properly installed and configured: v A supported compiler or interpreter for developing your applications. © Copyright IBM Corp. 1993, 2001 9v DB2 Universal Database, either locally or remotely. v DB2 Application Development Client. For details on how to accomplish these tasks, refer to the Application Building Guide and the Quick Beginnings books for your operating environment. You can develop applications at a server, or on any client, that has the DB2 Application Development Client (DB2 Application Development Client) installed. You can run applications with either the server, the DB2 Run-Time Client, or the DB2 Administrative Client. You can also develop Java JDBC programs on one of these clients, provided that you install the ″Java Enablement″ component when you install the client. That means you can execute any DB2 application on these clients. However, unless you also install the DB2 Application Development Client with these clients, you can only develop JDBC applications on them. DB2 supports the C, C++, Java (SQLJ), COBOL, and FORTRAN programming languages through its precompilers. In addition, DB2 provides support for the Perl, Java (JDBC), and REXX dynamically interpreted languages. For information on the specific precompilers provided by DB2, and the languages supported on your platform, refer to the Application Building Guide. Note: FORTRAN and REXX support stabilized in DB2 Version 5, and no enhancements for FORTRAN or REXX support are planned for the future. DB2 provides a sample database which you require when running the supplied sample programs. For information about the sample database and its contents, refer to the SQL Reference. DB2 Application Coding Overview A DB2 application program consists of several parts: 1. Declaring and initializing variables 2. Connecting to the database 3. Performing one or more transactions 4. Disconnecting from the database 5. Ending the program A transaction is a set of database operations that must conclude successfully before being committed to the database. With embedded SQL, a transaction begins implicitly and ends when the application executes either a COMMIT or ROLLBACK statement. An example of a transaction is the entry of a customer’s deposit, and the updating of the customer’s balance. 10 Application Development GuideCertain SQL statements must appear at the beginning and end of the program to handle the transition from the host language to the embedded SQL statements. The beginning of every program must contain: v Declarations of all variables and data structures that the database manager uses to interact with the host program v SQL statements that provide for error handling by setting up the SQL Communications Area (SQLCA) Note that DB2 applications written in Java throw an SQLException, which you handle in a catch block, rather than using the SQLCA. The body of every program contains the SQL statements that access and manage data. These statements constitute transactions. Transactions must include the following statements: v The CONNECT statement, which establishes a connection to a database server v One or more: – Data manipulation statements (for example, the SELECT statement) – Data definition statements (for example, the CREATE statement) – Data control statements (for example, the GRANT statement) v Either the COMMIT or ROLLBACK statement to end the transaction The end of the application program typically contains SQL statements that: v Release the program’s connection to the database server v Clean up any resource Declaring and Initializing Variables To code a DB2 application, you must first declare: v the variables that interact with the database manager v the SQLCA, if applicable Declaring Variables that Interact with the Database Manager All variables that interact with the database manager must be declared in an SQL declare section. You must code an SQL declare section with the following structure: 1. the SQL statement BEGIN DECLARE SECTION 2. a group of one or more variable declarations 3. the SQL statementENDDECLARESECTION Host program variables declared in an SQL declare section are called host variables. You can use host variables in host-variable references in SQL statements. Host-variable is a tag used in syntax diagrams in the SQL Reference. A program may contain multiple SQL declare sections. Chapter 2. Coding a DB2 Application 11The attributes of each host variable depend on how the variable is used in the SQL statement. For example, variables that receive data from or store data in DB2 tables must have data type and length attributes compatible with the column being accessed. To determine the data type for each variable, you must be familiar with DB2 data types, which are explained in “Data Types” on page 77. Declaring Variables that Represent SQL Objects: For DB2 Version 7, the names of tables, aliases, views, and correlations have a maximum length of 128 bytes. Column names have a maximum length of 30 bytes. In DB2 Version 7, schema names have a maximum length of 30 bytes. Future releases of DB2 may increase the lengths of column names and other identifiers of SQL objects up to 128 bytes. If you declare variables that represent SQL objects with less than 128 byte lengths, future increases in SQL object identifier lengths may affect the stability of your applications. For example, if you declare the variable char9schema_name in a C++ application to hold a schema name, your application functions properly for the allowed schema names in DB2 Version 6, which have a maximum length of 8 bytes. char9 schema_name; / holds null-delimited schema name of up to 8 bytes; works for DB2 Version 6, but may truncate schema names in future releases / However, if you migrate the database to DB2 Version 7, which accepts schema names with a maximum length of 30 bytes, your application cannot differentiate between the schema names LONGSCHEMA1 and LONGSCHEMA2. The database manager truncates the schema names to their 8-byte limit of LONGSCHE, and any statement in your application that depends on differentiating the schema names fails. To increase the longevity of your application, declare the schema name variable with a 128-byte length as follows: char129 schema_name; / holds null-delimited schema name of up to 128 bytes good for DB2 Version 7 and beyond / To improve the future operation of your application, consider declaring all of the variables in your applications that represent SQL object names with lengths of 128 bytes. You must weigh the advantage of improved compatibility against the increased system resources that longer variables require. To ease the use of this coding practice and increase the clarity of your C/C++ application code, consider using C macro expansion to declare the lengths of these SQL object identifiers. Since the include file sql.h declares SQL_MAX_IDENT to be 128, you can easily declare SQL object identifiers with the SQL_MAX_IDENT macro. For example: 12 Application Development Guideinclude sql.h charSQL_MAX_IDENT+1 schema_name; charSQL_MAX_IDENT+1 table_name; charSQL_MAX_IDENT+1 employee_column; charSQL_MAX_IDENT+1 manager_column; For more information on C macro expansion, see “C Macro Expansion” on page 613. Relating Host Variables to an SQL Statement: You can use host variables to receive data from the database manager or to transfer data to it from the host program. Host variables that receive data from the database manager are output host variables, while those that transfer data to it from the host program are input host variables. Consider the following SELECT INTO statement: SELECT HIREDATE, EDLEVEL INTO :hdate, :lvl FROM EMPLOYEE WHERE EMPNO = :idno It contains two output host variables, hdate and lvl, and one input host variable, idno. The database manager uses the data stored in the host variable idno to determine the EMPNO of the row that is retrieved from the EMPLOYEE table. If the database manager finds a row that meets the search criteria, hdate and lvl receive the data stored in the columns HIREDATE and EDLEVEL, respectively. This statement illustrates an interaction between the host program and the database manager using columns of the EMPLOYEE table. Each column of a table is assigned a data type in the CREATE TABLE definition. You must relate this data type to the host language data type defined in the Supported SQL Data Types section of each language-specific chapter in this document. For example, the INTEGER data type is a 32-bit signed integer. This is equivalent to the following data description entries in each of the host languages, respectively: C/C++: sqlint32 variable_name; Java: int variable_name; COBOL: 01 variable-name PICTURE S9(9) COMPUTATIONAL-5. FORTRAN: INTEGER4 variable_name Chapter 2. Coding a DB2 Application 13For the list of supported SQL data types and the corresponding host language data types, see the following: v for C/C++, “Supported SQL Data Types in C and C++” on page 627 v for Java, “Supported SQL Data Types in Java” on page 639 v for COBOL, “Supported SQL Data Types in COBOL” on page 695 v for FORTRAN, “Supported SQL Data Types in FORTRAN” on page 712 v for REXX, “Supported SQL Data Types in REXX” on page 726 In order to determine exactly how to define the host variable for use with a column, you need to find out the SQL data type for that column. Do this by querying the system catalog, which is a set of views containing information about all tables created in the database. The SQL Reference describes this catalog. After you have determined the data types, you can refer to the conversion charts in the host language chapters and code the appropriate declarations. The Declaration Generator utility (db2dclgn) is also available for generating the appropriate declarations for a given table in a database. For more information on db2dclgn, see “Declaration Generator - db2dclgn” on page 73 and refer to the Command Reference. Table 4 on page 74 shows examples of declarations in the supported host languages. Note that REXX applications do not need to declare host variables except for LOB locators and file reference variables. The contents of the variable determine other host variable data types and sizes at run time. Table 4 also shows the BEGIN and END DECLARE SECTION statements. Observe how the delimiters for SQL statements differ for each language. For the exact rules of placement, continuation, and delimiting of these statements, see the language-specific chapters of this book. Handling Errors and Warnings The SQL Communications Area (SQLCA) is discussed in detail later in this chapter. This section presents an overview. To declare the SQLCA, code the INCLUDE SQLCA statement in your program. For C or C++ applications use: EXEC SQL INCLUDE SQLCA; For Java applications: You do not explicitly use the SQLCA in Java. Instead, use the SQLException instance methods to get the SQLSTATE and SQLCODE values. See “SQLSTATE and SQLCODE Values in Java” on page 641 for more details. For COBOL applications use: EXEC SQL INCLUDE SQLCA END-EXEC. 14 Application Development GuideFor FORTRAN applications use: EXEC SQL INCLUDE SQLCA When you preprocess your program, the database manager inserts host language variable declarations in place of the INCLUDE SQLCA statement. The system communicates with your program using the variables for warning flags, error codes, and diagnostic information. After executing each SQL statement, the system returns a return code in both SQLCODE and SQLSTATE. SQLCODE is an integer value that summarizes the execution of the statement, and SQLSTATE is a character field that provides common error codes across IBM’s relational database products. SQLSTATE also conforms to the ISO/ANS SQL92 and FIPS 127-2 standard. Note: FIPS 127-2 refers to Federal Information Processing Standards Publication 127-2 for Database Language SQL. ISO/ANS SQL92 refers to American National Standard Database Language SQL X3.135-1992 and International Standard ISO/IEC 9075:1992, Database Language SQL. Note that if SQLCODE is less than 0, it means an error has occurred and the statement has not been processed. If the SQLCODE is greater than 0, it means a warning has been issued, but the statement is still processed. See the Message Reference for a listing of SQLCODE and SQLSTATE error conditions. If you want the system to control error checking after each SQL statement, use the WHENEVER statement. Note: Embedded SQL for Java (SQLJ) applications cannot use the WHENEVER statement. Use the SQLException methods described in “SQLSTATE and SQLCODE Values in Java” on page 641 to handle errors returned by SQL statements. The following WHENEVER statement indicates to the system what to do when it encounters a negative SQLCODE: WHENEVER SQLERROR GO TO errchk That is, whenever an SQL error occurs, program control is transferred to code that follows the label, such as errchk. This code should include logic to analyze the error indicators in the SQLCA. Depending upon the ERRCHK definition, action may be taken to execute the next sequential program instruction, to perform some special functions, or as in most situations, to roll back the current transaction and terminate the program. See “Coding Transactions” on page 17 for more information on a transaction and “Diagnostic Handling and the SQLCA Structure” on page 116 for more information about how to control error checking in your application program. Chapter 2. Coding a DB2 Application 15Exercise caution when using the WHENEVER SQLERROR statement. If your application’s error handling code contains SQL statements, and if these statements result in an error while processing the original error, your application may enter an infinite loop. This situation is difficult to troubleshoot. The first statement in the destination of a WHENEVER SQLERROR should be WHENEVER SQLERROR CONTINUE. This statement resets the error handler. After this statement, you can safely use SQL statements. For a DB2 application written in C or C++, if the application is made up of multiple source files, only one of the files should include the EXEC SQL INCLUDE SQLCA statement to avoid multiple definitions of the SQLCA. The remaining source files should use the following lines: include "sqlca.h" extern struct sqlca sqlca; If your application must be compliant with the ISO/ANS SQL92 or FIPS 127-2 standard, do not use the above statements or the INCLUDE SQLCA statement. For more information on the ISO/ANS SQL92 and FIPS 127-2 standards, see “Definition of FIPS 127-2 and ISO/ANS SQL92” on page 15. For the alternative to coding the above statements, see the following: v For C or C++ applications, see “SQLSTATE and SQLCODE Variables in C and C++” on page 635 v For COBOL applications, “SQLSTATE and SQLCODE Variables in COBOL” on page 699 v For FORTRAN applications, “SQLSTATE and SQLCODE Variables in FORTRAN” on page 714 Using Additional Nonexecutable Statements Generally, other nonexecutable SQL statements are also part of this section of the program. Both the SQL Reference and subsequent chapters of this manual discuss nonexecutable statements. Examples of nonexecutable statements are: v INCLUDE text-file-name v INCLUDE SQLDA v DECLARE CURSOR Connecting to the Database Server Your program must establish a connection to the target database server before it can run any executable SQL statements. This connection identifies both the authorization ID of the user who is running the program, and the name of the database server on which the program is run. Generally, your application process can only connect to one database server at a time. This server is called the current server. However, your application can connect to multiple database servers within a multisite update environment. In this case, only one server can be the current server. For more information on multisite updates, see “Multisite Update” on page 535. 16 Application Development GuideYour program can establish a connection to a database server either: v explicitly, using the CONNECT statement v implicitly, connecting to the default database server v for Java applications, through a Connection instance Refer to the SQL Reference for a discussion of connection states and how to use the CONNECT statement. Upon initialization, the application requester establishes a default database server. If implicit connects are enabled, application processes started after initialization connect implicitly to the default database server. It is good practice to use the CONNECT statement as the first SQL statement executed by an application program. This avoids accidentally executing SQL statements against the default database. After the connection has been established, your program can issue SQL statements that: v Manipulate data v Define and maintain database objects v Initiate control operations, such as granting user authority, or committing changes to the database A connection lasts until a CONNECT RESET, CONNECT TO, or DISCONNECT statement is issued. In a multisite update environment, a connection also lasts until a DB2 RELEASE then DB2 COMMIT is issued. A CONNECT TO statement does not terminate a connection when using multisite update (see “Multisite Update” on page 535). Coding Transactions A transaction is a sequence of SQL statements (possibly with intervening host language code) that the database manager treats as a whole. An alternative term that is often used for transaction is unit of work. To ensure the consistency of data at the transaction level, the system makes sure that either all operations within a transaction are completed, or none are completed. Suppose, for example, that the program is supposed to deduct money from one account and add it to another. If you place both of these updates in a single transaction, and a system failure occurs while they are in progress, then when you restart the system, the database manager automatically restores the data to the state it was in before the transaction began. If a program error occurs, the database manager restores all changes made by the statement in error. The database manager will not undo work performed in the transaction prior to execution of the statement in error, unless you specifically roll it back. You can code one or more transactions within a single application program, and it is possible to access more than one database from within a single transaction. A transaction that accesses more than one database is called a Chapter 2. Coding a DB2 Application 17multisite update. For information on these topics, see “Remote Unit of Work” on page 535 and “Multisite Update” on page 535. Beginning a Transaction A transaction begins implicitly with the first executable SQL statement and ends with either a COMMIT or a ROLLBACK statement, or when the program ends. In contrast, the following six statements do not start a transaction because they are not executable statements: BEGIN DECLARE SECTION INCLUDE SQLCA END DECLARE SECTION INCLUDE SQLDA DECLARE CURSOR WHENEVER An executable SQL statement always occurs within a transaction. If a program contains an executable SQL statement after a transaction ends, it automatically starts a new transaction. Ending a Transaction To end a transaction, you can use either: v The COMMIT statement to save its changes v The ROLLBACK statement to ensure that these changes are not saved Using the COMMIT Statement: This statement ends the current transaction. It makes the database changes performed during the current transaction visible to other processes. You should commit changes as soon as application requirements permit. In particular, write your programs so that uncommitted changes are not held while waiting for input from a terminal, as this can result in database resources being held for a long time. Holding these resources prevents other applications that need these resources from running. The COMMIT statement has no effect on the contents of host variables. Your application programs should explicitly end any transactions prior to terminating. If you do not end transactions explicitly, DB2 automatically commits all the changes made during the program’s pending transaction when the program ends successfully, except on Windows 32-bit operating systems. DB2 rolls back the changes under the following conditions: v A log full condition v Any other system condition that causes database manager processing to end On Windows 32-bit operating systems, if you do not explicitly commit the transaction, the database manager always rolls back the changes. 18 Application Development GuideFor more information about program termination, see “Ending the Program” and “Diagnostic Handling and the SQLCA Structure” on page 116. Using the ROLLBACK Statement: This statement ends the current transaction, and restores the data to the state it was in prior to beginning the transaction. The ROLLBACK statement has no effect on the contents of host variables. If you use a ROLLBACK statement in a routine that was entered because of an error or warning and you use the SQL WHENEVER statement, then you should specify WHENEVER SQLERROR CONTINUE and WHENEVER SQLWARNING CONTINUE before the ROLLBACK. This avoids a program loop if the ROLLBACK fails with an error or warning. In the event of a severe error, you will receive a message indicating that you cannot issue a ROLLBACK statement. Do not issue a ROLLBACK statement if a severe error occurs such as the loss of communications between the client and server applications, or if the database gets corrupted. After a severe error, the only statement you can issue is a CONNECT statement. Ending the Program To properly end your program: 1. End the current transaction (if one is in progress) by explicitly issuing either a COMMIT statement or a ROLLBACK statement. 2. Release your connection to the database server by using the CONNECT RESET statement. 3. Clean up resources used by the program. For example, free any temporary storage or data structures that are used. Note: If the current transaction is still active when the program terminates, DB2 implicitly ends the transaction. Since DB2’s behavior when it implicitly ends a transaction is platform specific, you should explicitly end all transactions by issuing a COMMIT or a ROLLBACK statement before the program terminates. See Implicitly Ending a Transaction for details on how DB2 implicitly ends a transaction. Implicitly Ending a Transaction If your program terminates without ending the current transaction, DB2 implicitly ends the current transaction (see “Ending the Program” for details on how to properly end your program). DB2 implicitly terminates the current transaction by issuing either a COMMIT or a ROLLBACK statement when the application ends. Whether DB2 issues a COMMIT or ROLLBACK depends on factors such as: v Whether the application terminated normally v The platform on which the DB2 server runs Chapter 2. Coding a DB2 Application 19v Whether the application uses the context APIs (see “Multiple Thread Database Access” on page 543) On Most Supported Operating Systems DB2 implicitly commits a transaction if the termination is normal, or implicitly rolls back the transaction if it is abnormal. Note that what your program considers to be an abnormal termination may not be considered abnormal by the database manager. For example, you may code exit(-16) when your application encounters an unexpected error and terminate your application abruptly. The database manager considers this to be a normal termination and commits the transaction. The database manager considers items such as an exception or a segmentation violation as abnormal terminations. On Windows 32-bit Operating Systems DB2 always rolls back the transaction regardless of whether your application terminates normally or abnormally, unless you explicitly commit the transaction using the COMMIT statement. When Using the DB2 Context APIs Your application can use any of the DB2 APIs to set up and pass application contexts between threads as described in “Multiple Thread Database Access” on page 543. If your application uses these DB2 APIs, DB2 implicitly rolls back the transaction regardless of whether your application terminates normally or abnormally. Unless you explicitly commit the transaction using the COMMIT statement, DB2 rolls back the transaction. Application Pseudocode Framework Pseudocode Framework for Coding Programs summarizes the general framework for a DB2 application program in pseudocode format. You must, of course, tailor this framework to suit your own program. Start Program EXEC SQL BEGIN DECLARE SECTION DECLARE USERIDFIXEDCHARACTER (8) DECLARE PW FIXED CHARACTER (8) Application (other host variable declarations) Setup EXEC SQL ENDDECLARE SECTION EXEC SQL INCLUDE SQLCA EXEC SQL WHENEVER SQLERROR GOTO ERRCHK (program logic) EXEC SQL CONNECT TO database A USER :userid USING :pw EXEC SQL SELECT ... EXEC SQL INSERT ... First Unit (more SQL statements) of Work EXEC SQL COMMIT (more program logic) 20 Application Development GuideEXEC SQL CONNECT TO database B USER :userid USING :pw EXEC SQL SELECT ... EXEC SQL DELETE ... Second Unit (more SQL statements) of Work EXEC SQL COMMIT (more program logic) EXEC SQL CONNECT TO database A EXEC SQL SELECT ... EXEC SQL DELETE ... Third Unit (more SQL statements) of Work EXEC SQL COMMIT (more program logic) EXEC SQL CONNECT RESET ERRCHK Application (check error information in SQLCA) Cleanup End Program Designing an Application For DB2 DB2 provides you with a variety of application development capabilities that you can use to supplement or extend the traditional capabilities of an application. As an application designer, you must make the most fundamental design decision: Which DB2 capabilities should I use in the design of my application? In order to make appropriate choices, you need to consider both the database design and target environments for your application. For example, you can choose to enforce some business rules in your database design instead of including the logic in your application. The capabilities you use and the extent to which you use them can vary greatly. This section is an overview of the capabilities available that can significantly affect your design and provides some reasons for why you might choose one over another. For more information and detail on any of the capabilities described, a reference to more detail is provided. The capabilities that you need to consider include: v Accessing the data using: – Embedded SQL, including embedded SQLJ for Java (SQLJ) – DB2 Call Level Interface (DB2 CLI), Open Database Connectivity (ODBC), and Java Database Connectivity (JDBC) – Microsoft Specifications – Perl DBI – Query products Chapter 2. Coding a DB2 Application 21

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.