|
|
DB2/SQL Application Development
| Duration: 5 days |
| Participants: Application programmers and developers. |
|
Objectives:
Upon successful completion of this course you will be able to:
- Design databases using simple ER (Entry Relationship) diagrams and normalize to the third form (3NF). - Code SQL (Structured Query Language) SELECT, INSERT, UPDATE, and DELETE statements to access and update tables. SQL includes joins, unions, subqueries, and common scalar and aggregate (column) functions. - Use SPUFI to interactively test and use SQL statements. - Create host variable copybook members using DCLGEN. - Prepare DB2 application programs for execution using client site facilities (JCL or DB2I), to pre-compile, compile, bind (packages/plans), and link modules. - Embed SQL statements in application programs to access and update data in DB2 tables. - Test SQLCODE and SQLWARNs appropriately. - Code applications to manage single and multiple CURSORs, control concurrency, issue COMMITs and ROLLBACKs, and handle program restarts. - Implement referential integrity and understand its advantages and potential dangers. - Describe enhancements and impacts of DB2 Version 8 and 9 (optional). |
|
Overview:
- This lecture and hands-on course teaches experienced programmers to code SQL (Structured Query Language), to embed SQL statements in application programs, to prepare programs for execution (precompile, compile, bind and link), execute these programs, and troubleshoot common problems. - This course includes an overview of Version 8 enhancements and differences, and highlights of Version 9. - To accommodate each student's programming background, we provide workshops based on COBOL, PL/I, and C Languages. |
| Prerequisites: Programming experience in COBOL, PL/I, or C, and working knowledge of TSO and JCL are required. No prior knowledge of SQL is required. |
|
Format:
- Lecture, discussion, and hands-on workshops. Numerous workshops enhance the learning process. If desired, programming workshops can use supplied COBOL skeletal programs. |
|
Topic Outline:
- Introduction to DB2 DB2 defined Concepts of the Relational Data Model DB2 data structures Implementing the Relational Data Model Table, columns, and rows Data types Example of creating a DB2 table Example of selecting data Overview of DB2 objects DB2 system structure DB2's relationship to other MVS subsystems Structural vs. value-driven tabular format Contrast with hierarchical database systems - Structured Query Language Basics of SQL Simple SELECT statement Executing interactive queries with SPUFI FROM, WHERE, ORDER BY AS CASE Common error codes Predicate logic BETWEEN, IN, LIKE Compound search conditions: AND, OR, Parentheses Aggregate functions (formerly called Column functions) Scalar functions Eliminating duplicate rows Handling NULLS Date and time considerations - SQL Data Manipulation INSERT statement Multi-row insert processing UPDATE statement Restrictions on updating primary key DELETE statement - Embedding Static SQL in Application Programs Supported languages SELECT INTO statement Host variables Indicator variables Declarative SQL statements DCLGEN facility INCLUDE statement Including and using the SQLCA SQLCA field description Testing SQL return codes Error routines and error determination Displaying SQLCA values CALL DSNTIAR GET DIAGNOSTICS COMMIT and ROLLBACK NON-CURSOR SQL operations Program preparation Preparation flowchart Pre-compile BIND, REBIND DB2 Interactive preparation Batch preparation Plans, packages, and collections Timestamp considerations Common reasons for -818s return codes Multi-row FETCH and INSERT/ROWSETS Array host variables GET DIAGNOSTICS for ROWSETS Positioned UPDATE/DELETE with ROWSETS - Basic Joins and Subqueries and Unions Introduction joins Inner joins Outer joins Introduction to subqueries UNION and UNION ALL - Programming with CURSORs Concept of cursor operations Cursor management DECLARE CURSOR HOLD option OPTIMIZE option OPEN/CLOSE FETCH Updating and deleting rows with a cursor WHERE CURRENT OF clause Processing with multiple cursors Introduction to scrollable cursors Static scrollable cursors Dynamic scrollable cursors - Coding for Recovery/Restartable Programs Units of recovery COMMIT and ROLLBACK Effect of COMMIT and ROLLBACK on open cursors Re-establish cursor position after COMMIT - Referential Integrity Constraints Components of Referential Integrity Primary and foreign keys Parent and dependent tables Relationship of tables Referential cycles Self-referencing tables Defining primary and foreign keys PRIMARY KEY clause Restrictions on updating primary key FOREIGN KEY clause ON DELETE clause NO ACTION Implications on SQL update operations Building a referential structure Creating the tables Enforcing uniqueness - DB2 Management of Data Description of and DDL syntax for: Storage groups Tablespaces Tables Indexes Views Synonyms Comments for metadata Views Purpose of views Defining views Restrictions on updating with views Relationship of DB2 objects Querying the DB2 catalog - DB2 Security Overview of authorization system Privileges DCL (Data Control Language) GRANT/REVOKE Authorization rules - Overview of Advanced SQL Nested Table Expressions Common Table Expressions Recursive SQL Generating unique values Identity column enhancements Sequence objects ROWID GENERATE_UNIQUE( ) Scalar fullselect SELECT inside an INSERT Comparing NULLs Miscellaneous topics - Some Highlights of DB2 Version 8 Architecture Scalability and Availability Puffing and BINDs Web and Network Support Enhancements Unicode in DB2 for z/OS, CCSIDs, encoding Schemas New integrated XML (Extensible Markup Language) publishing functions/generating HTML Java and WebSphere support Support for delimited files Performance New index support Distribution statistics Volatile tables Materialized Query Table Star Schema for Star Joins Support for DPSI (Data Partitioned Secondary Indexes) Data Sharing Installation and Migration – 3 phases - Highlights of DB2 UDB for z/OS Version 9.1 |
|
|
