|
|
Advanced DB2/SQL Application Programming
(Version 7)
| Duration: 5 days |
| Participants: Application programmers and developers. |
|
Objectives:
Upon successful completion of this course you will be able to:
- Code advanced SQL (Structured Query Language) queries, including complex inner and outer joins, subqueries, unions, and use the new Version 7 SQL enhancements. - Use application coding techniques such as new Version 7 CURSOR management and concurrency management. - Use techniques to code efficient SQL, including EXPLAINs. - Describe advanced DB2 capabilities including triggers, stored procedures, user-defined functions, additional built-in functions, etc. - Use Version 7 enhancements for SQL and DB2 application programming. |
|
Overview:
- This course teaches experienced programmers to use advanced SQL (Structured Query Language). |
| Prerequisites: Programming experience in COBOL and knowledge of TSO are required. |
| Format: Lecture and hands-on workshops. Numerous workshops enhance the learning process. Programming workshops use supplied COBOL skeletal programs. |
|
Topic Outline:
- Advanced SQL Join Cartesian product Equijoin Natural join Greater than join Joining rows of the same table Outer joins Right outer join Left outer join Union Union of two SELECTs Union with ORDER BY Union coding rules Subqueries Subqueries vs. joins Correlated subqueries EXISTS operator Static SQL Dynamic SQL Contrast with Static SQL ODBC Call Level Interface Mode Dynamic SQL cache Pre-compile requirements Type 2 indexes - Version 7 SQL Enhancements UNION, UNION ALL New column functions New scalar functions Self-referencing UPDATEs and DELETEs New SET expressions Row value expressions Limited FETCH ORDER BY enhancements Embedded SQL Enhancements Scrollable cursors Precompile during compile step Data Enhancements UNICODE support Efficiencies EXPLAIN Suggestions for coding efficient SQL Data Administration Enhancements Stored Procedures and e-business Enhancements Support Changes and Enhancements Availability Improvements Architecture changes Support for Coupling Facility Name Class Queue Group Attach process improved IMMEDWRITE bind/rebind options improvements "Restart Light" Persistent size of objects maintained - Concurrency Management Overview of DB2 locking Size of locks The need for locks Bind parameters that affect locking Effect of COMMIT and ROLLBACK on locks - Stored Procedures - Concepts and Features Overview Stored Procedure uses (SQL and non-DB2 uses) Stored procedures environment Setup requirements and installation considerations SPAS versus WLM address spaces Client/Server logic flow Advantages over embedding SQL in client programs - Additional Features - Overview (optional) Sysplex parallelism Universal database Client/Server enhancements Stored procedures Object oriented support Complex datatypes Bind option to reoptimize access paths UDB - Universal DB extensibility Object-relational extensions and multimedia capabilities User-defined data types User-defined functions Trigger support Increased sizes and reduced limits Performance and availability enhancements Network Computing Enhancements Universal DB Extensibility Object-relational extensions Multimedia large object data types User-defined data types Built-in SQL functions Trigger support for active data Object-relational extensors Additional SQL enhancements |
|
|
