IBM DB2 Universal Database V6.1 Application Development

Audience

This course is intended for application developers. Students should have a fundamental understanding of relational database technology including concepts of databases tables rows/columns data types etc. prior to taking this course. Limited exposure to SQL is also assumed. Application Development students must also have solid programming skills using C C++ Java or equivalent programming language. In addition participants should have taken the first part in this series IBM DB2 UDB V6.1 Fundamentals (course13431) prior to taking this course. When coupled with appropriate on-the-job experience this course will help prepare students for the IBM Certified Solutions Expert Certification Application Development exam 511.

Objective

• Use SQL functions in DB2 Applications.
• Use different application objects in DB2 such as schema userdefined types userdefined functions and stored procedures.
• Use SQL within an application.
• Use dynamic SQL within an application.

Topics Include

Unit 1: SQL Application Development

• Combine data from two or more tables using a join.
• Combine two queries into a single result table using set operations.
• Retrieve data in a restricted condition using a subquery.
• Retrieve data with referenced values using a correlated subquery.
• Retrieve data using scalar functions.
• Retrieve summarized data for groups using grouping functions.
• Create different levels of aggregation data using GROUPING SETS operations.
• Identify characteristics of STAR schemas for table design and index design.
• Code combined SQL statements using compound SQL.
• Create a local temporary table that can be referenced in an SQL statement using a common table expression.
• Retrieve data with logical conditions using a CASE expression in a given scenario.

Unit 2: Application Development Objects

• Identify valid DB2 object names.
• Control a schema using a SET SCHEMA statement.
• Identify valid User-Defined Type (UDT) comparisons.
• Identify the User-Defined Function (UDF) type and its function.
• Register stored procedures using a CREATE PROCEDURE statement.

Unit 3: Embedded SQL Programming

• Identify the general tasks of an application containing embedded SQL statements.
• Declare the host variables that correspond to specific columns in a table.
• Code a statement that uses host variables in queries using an embedded SELECT.
• Code a statement that uses host variables to change data within a specific application.
• Analyze the content of host variables to determine whether a column contains null values.
• Identify situations in which to use cursors in an application that contains embedded SQL.
• Code a statement that uses a specific type of cursor to manipulate data using embedded SQL.
• Code a positioned update using embedded SQL.
• Match SQLCA elements with the error-handling information they contain.
• Identify the transaction that produces a specific effect on a database.
• Identify the characteristics of type 1 and type 2 connections to a database.
• Identify which isolation level is necessary to ensure a specific degree of data integrity.
• Identify the input and output of a PREP statement using deferred binding.
• Perform the steps to precompile and bind an application using specific parameters.
• Identify the privileges necessary to develop an application using embedded static SQL.
• Match the type of application development information with its system catalog name.
• Obtain configuration information using the correct tool.
• Analyze an SQL statement using EXPLAIN and VISUAL EXPLAIN.

Unit 4: Dynamic SQL Programming

• Identify the characteristics of dynamic embedded SQL.
• Match the SQLDA data structure elements with the information they contain.
• Code statements using SQL keywords specific to dynamic SQL applications.
• Code a dynamic SQL statement using parameter markers.
• Identify when to use various application interfaces.
• Identify the functionality of DB2 APIs.
• Code CLI functions in an application.
• Identify the tasks necessary to code JDBC applications and applets.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media