DB2 Universal Database in Application Development Environments

Tetsuya Shirai and John Barber are software professionals at IBM's International Technical Support Organization, Austin, TX.

1. Overview.


DB2 Universal Database. Scalable Database. Storing Universal Data. DB2 UDB Products. DB2 UDB Clients. DB2 UDB GUI Tools. Microsoft Universal Data Access and DB2 UDB. OLE DB. ActiveX Data Objects (ADO). Universal Access of DB2 UDB. Microsoft Visual Studio. Add-In Tools. Using Microsoft Technology.



2. Set Up Application Development Environment.


Installing the DB2 UDB Product. Installing on Windows NT. Creating the Sample Database. Using First Steps. Microsoft Programming Tools. Microsoft Data Access Components. Setting Up Connections. Automated Configuration Using Discovery. DB2 Discovery. Configuring DB2 Clients. Modeling Your Production Database. Summary.



3. Introducing Programming Interfaces.


DB2 UDB Application Overview. Embedded SQL. Static SQL. Dynamic SQL. Call Level Interface and ODBC. A DB2 UDB CLI Application. Java Interfaces (JDBC and SQLJ). JDBC Application. A Sample JDBC Application. To Build the Application. Running the Application. SQLJ Applications. Compiling and Running SQLJ Programs. Native DB2 UDB APIs. Microsoft Data Objects (DAO, RDO, ADO). Development of Visual Basic Data Access Methods. Structure of Visual Basic Data Access Methods. Data Access Objects. Remote Data Objects. ActiveX Data Objects. DAO, RDO, ADO Summary.



4. DB2 Server-Side Features.


Constraints. Unique Constraints. Referential Constraint. Table-Check Constraints. User-defined Distinct Types (UDTs). Large Objects (LOBs). Character Large Objects (CLOB). Double-byte Character Large Objects (DBCLOB). Binary Large Object (BLOB). Manipulating Large Objects. User-Defined Functions (UDFs). Defining UDFs. Source Functions. SQL Functions. External Scalar Functions. External Table Functions. OLE DB External Table Functions. Performance Tips for UDFs. Triggers. Stored Procedures. Building Stored Procedures. SQL Procedures. Nested Stored Procedures. Keep Stored Procedure Processes. Stored Procedure Builder.



5. Building Applications Using Visual Basic.


Data Entry Forms. ODBC Data Source for Accessing Sample Database. Opening the Visual Basic Project. Setting Project References. Creating Forms Using the Data Form Wizard. Visual Data Manager Add-In. OpenSchema Recordsets. BookSale Schema. Run Booksales Sample Application on DB2 UDB. DB2 UDB Sample Application. Common Procedures in Main Display Forms. RecSet Module. Logon Form. Menu Form. Table Access Form. Department Information. Employee Information. Employee Photo. Employee Resume. Employee Activities. Projects by Employee. Calling DB2 UDB Stored Procedures. Calling a Stored Procedure Returning a Recordset. Using Commitment Control.



6. Building Applications Using Visual C++.


Visual C++ Applications with ADO. GetRows Sample. VarCHAR Sample. Building Applications Using DB2 UDB Add-Ins. Activating DB2 UDB Add-Ins. Developing Stored Procedures Using Add-Ins.



7. Using Microsoft Transaction Server.


Benefits of Microsoft Transaction Server. Transaction Processing Concepts. MTS Architecture. MTS and the Supporting Environment. MTS Concepts and Processes. Activities. Contexts. Server Process. Automatic Transactions. Just-In-Time Activation. Creating MTS Components. Requirements for MTS Components. Adding Transactional Support. Handling Errors. Creating Efficient Objects. Using MTS Explorer. Creating Packages. Setting Package Properties. Adding Components to a Package. Component Locations and Versioning. Setting Component Properties. Security Considerations. Declarative Security: Roles and Identities. Setting Package Security. Creating and Assigning Security Roles. Setting package identity. Getting DB2 UDB and MTS to Work Together. Software Prerequisites. Installation and Configuration. Sample Program - Using ADO with an MTS Component. Steps to Run the Sample Application. How MTS Handles DCOM. More About Client and Server Projects.



8. Application Development on IIS.


Creating ASP Applications. Starting-Point Directory. Global Data. Creating Server Script. Coding Active Server Pages. ASP Processing Directives. Handling Run Time Errors. ASP Built-In Objects. The Request Object. The Response Object. The Application Object. The Session Object. Using ActiveX Data Objects. Connecting to the Database. Creating a Recordset Object. Security Considerations in ASP. Web Application Security. Setting IIS Permissions. Allowing Anonymous Logon. Sample Web Application Using ASP. General Procedure for Installing Web Applications. Creating a Web Application: Microsoft Visual Interdev 6.0.



9. Tuning Application Performance.


Using SELECT Statements. Specify Only Needed Columns in the Select List. Limit the Number of Rows by Using Predicates. Specify the OPTIMIZE FOR n ROWS Clause. Specify the FETCH FIRST n ROWS ONLY Clause. Specify the FOR FETCH ONLY Clause. Avoid Data Type Conversions. Concurrency. Isolation Levels. LOCK TABLE Statement. LOCKSIZE Parameter of ALTER TABLE Statement. Declared Temporary Table. Defining a Declared Temporary Table. Create Table with NOT LOGGED INITIALLY Option. Convert ODBC into Static SQL. Considerations in a Partitioned Environment. Specify FOR FETCH ONLY. OLTP Applications in a Partitioned Environment. CLI/ODBC Trace Facility. Getting Started. CLI Trace File Contents. Analysis Objectives. Analyzing the Trace and Finding the Problem. Fixing the Performance Problem.



Appendix A: Supported Statements for SQL Procedures.


Appendix B: Example DB2 UDB Source Code.


Appendix C: Related Publications.


Index.