Db2 for LUW - Development for Applications Programmers

What you will learn

On successful completion of this course you will be able to:

• set up a Db2 test environment, using correctly defined tables, views, indexes, synonyms and aliases
• code SQL statements to read and manipulate Db2 data
• import and export Db2 data
• develop, prepare and execute Java Db2 programs
• code Stored Procedures written in SQL-PL and Java
• use Query Result Sets within Stored Procedures
• use IBM Data Studio to generate Stored Procedures
• bind Stored Procedure packages
• use non-scrollable and scrollable cursors
• describe the locking process used by Db2
• use the Db2 for Windows GUI Toolset
• use the Command Line Processor
• populate tables using Db2 Utilities
• monitor application performance.

Who Should Attend

Db2Applications programmers working in a Db2 Database (LUW) environment.

Prerequisites

A familiarity with the host environment and a working knowledge of program development using Java.

Duration

5 days

Fee (per attendee)

£2250 (ex VAT)

This includes free online 24/7 access to course notes.

Hard copy course notes are available on request from rsmshop@rsm.co.uk

at £50.00 plus carriage per set.

Course Code

UD01

Contents

Db2 Instances

What is an Instance?; Setting up Instances.

Database Definition

The structure Of Db2 objects; Database definition; Default tablespaces; Database placement; Automatic storage databases; Database creation using IBM Data Studio; Database definition tasks; Altering a database; Database connectivity - Connect vs Activate; Catalog Tables.

Tablespace Definition

Tablespace organisation; Data placement - SMS or DMS?; Data placement considerations; Containers, Pages And Extents; Create Tablespace command; Create Tablespace parameters; SMS tablespace examples; DMS tablespaces; Automatic Storage tablespaces; DMS minimum space requirements; DMS maximum space.

TABLE / INDEX Definition

Table definition; Table / Column names; Copying table definitions; Table authority; Db2 Column Types; Null values; Null and default compression; Compression - row format; Has compression been switched on?; Lob data options; The Alter Table statement; Advanced GUI alter table functionality; The Rename Table statement; Global Temporary Tables; Declared Temporary Tables; Declared Temporary Table considerations; Declared Temporary Tables - comparisons; Listing table / tablespace information; Schema definition; View definition; Creating a view of two tables; Read Only views; View restrictions; Views - check options; Alias definition; Indexes; Index organisation - the B tree index; Index clustering; Index definition.

Running SQL and Commands

Connecting to the database; Using IBM Data Studio to run SQL scripts; The Db2 command window and command line processor; Command line syntax; On-line help; Interactive / non-interactive modes; Clp option flag; Clp termination.

Data Manipulation Language

SQL - Structured Query Language; SQL features; SQL query results; Db2 environments; The Select statement; The Where clause; Special operators; Not operand; In operand; Like operand; Between operand; User keyword; Statements using nulls; SQL built-in functions; Using 'Distinct'; Group By clause; Group by rollup; The Grouping function; Group by cube; Group by grouping sets; Having clause; Order by clause; Fetch First clause; Values statement; Update statement; Delete statement; Insert statement; Column functions (scalar functions); Working with date, time and timestamp columns; Db2 special date/time registers; Current date; Current time; Current timestamp; Db2 Join; Inner Joins; Outer Joins; Outer Join - Where clause; Nested table expression; SQL union / intersect / except; Subqueries; Subqueries using In; Exists; The 'All' subquery; The 'Any' or 'Some' subquery; Common table expressions; Recursive SQL.

Moving Data

Import / Export utilities; Import utility syntax; Import authorities; Export utility syntax; Export authorities; Load utility; Load utility features; Load examples; Load utility syntax; Recovering from load failure / load pending; Load utility and referential / check constraints; Load authorities; Load / Import differences.

Application Programming

Db2 environments; Single row selects; SQL Communication Area; Host variable declaration; Cursor selects; Declare cursor; Open cursor; Fetch a row; Row update; Row deletion; Close cursor; With hold option; Fetch First clause; Optimize statement; Precompiling a program; Binding a program; Levels of optimization; Run time reoptimization - Bind option REOPT.

JAVA - JDBC Programming

Database environments; Dynamic SQL - What is JDBC?; What are JDBC drivers?; JDBC driver types; The JDBC API; JDBC SQL statements; Using the statement class to select rows; The Db2 universal driver; Running SQL statements; Using the statement class to update rows; Processing result sets; JDBC cursor operations; Cursor scroll types; Cursor concurrency types; Cursor holdibility types; Using the prepared statement class; Handling NULL values; Handling SQL errors; Handling SQL warnings; Transaction control; Transaction example; Isolation levels; Calling a stored procedure from Java; Setting stored procedure input / inout parameters; Registering stored procedure output / input parameters; Stored Procedures - handling nulls; Java - handling result sets; Testing for optional result sets; DataSources; Java Naming and Directory Interface - JNDI; Setting up connection pooling using DataSources; Getting database connections via a DataSource.

JAVA SQLJ Programming (Optional)

SQLJ programming - development cycle; SQLJ support; Single row selects; Cursor selects; SQLJ cursor technique 1 - JDBC result set; SQLJ cursor technique 2 - SQLJ result set; Positioned updates using cursors; Positioned update restrictions; Fetch First clause; The Optimize statement; Table names In application programs; Precompiling an SQLJ program; Binding a package; Levels of optimisation; Run time reoptimization - Bind Option REOPT; Identifying a collection within a program.

Schemas and Paths

Schemas; The Grant Schema statement; Schema path - bind option; Current path - special register; Overriding the search path; Set current schema; Set current package path.

Stored Procedure Definition

The Create Procedure statement; Stored Procedure parameters; Allowable SQL statements; Create Procedure example; The Alter Procedure statement; Deleting a Stored Procedure definition; Defining a Java Stored Procedure; Java Stored Procedures - Jar Installation.

JAVA Stored Procedures

Calling a Stored Procedure from Java; Setting Stored Procedure Input / Inout parameters; Registering Stored Procedure Output / Inout parameters; Stored Procedures - handling nulls; Java - handling result sets; Testing for optional result sets; DataSources; Java Naming and Directory Interface - JNDI; Setting up connection pooling using DataSources; Getting database connections via a DataSource.

Coding Procedures in SQL/PL

The SQL procedures language; An SQL procedure example; Building SQL procedures?; JCL example; SQL procedure supported statements; Terminating statements in an SQL procedure; Simple DML statements; Selecting data - singleton selects; Selecting data - cursor operations; The Update statement; Update with subselect; The Delete statement; The Insert statement; The Mass Insert statement.

SQL/PL Reference

Begin and end statements (compound statements); Declaring host variables; Assigning values to variables - the SET statement; CASE statement; Comments; FOR statement; GET DIAGNOSTICS statement; GOTO statement; IF statement; Comparison operators; LEAVE and ITERATE statements; LOOP statement; REPEAT statement; RETURN statement; WHILE statement; Handling errors in an SQL stored procedure; Testing for errors - SQLCode and SQLState; The Declare Handler statement; Condition handler execution path; Declaring conditions for handlers; Dynamic SQL statements; Returning result sets; Processing result sets from other stored procedures; SIGNAL statement and message_text variable; RESIGNAL statement.

Dynamic Results Sets

Objects from which you can return result sets; Requirements for dynamic result sets; Cursor processing within the stored procedure; Dynamic result set; Embedded SQL statements; Query results sets example; Declare cursor with return; Definition of result-set-locator variables; Associate locators; The Allocate Cursor statement; Using global temporary tables; Declared temporary tables; Declared temporary table enhancement; Declared temporary table considerations; Declared temporary tables - comparisons.

IBM Data Studio

Building Db2 stored procedures and functions; Creating a new stored procedure or function; The SQL Assistant; Building the stored procedure; Debugging stored procedures; Setting breakpoints.

Materialized Query Tables

What are Materialized Query Tables?; MQT features; Creating an MQT; Create MQT - example; Altering an MQT; Alter MQT example; MQT Fullselect features / restrictions; Refresh table; Populating user maintained MQTs; Automatic Query Rewrite using MQT; Enabling Automatic Query Rewrite; Enabling Automatic Query Rewrite - DDL options; Enabling Automatic Query Rewrite - Special Registers; AQR - using both registers; Enabling Automatic Query Rewrite - system properties; Enabling Automatic Query Rewrite - query properties; Registering existing tables as MQTs; Maintenance of MQTs using staging tables.

Locking

Implications of concurrent processing; Database Manager Locks; Objects of locks; Lock modes; Lock example; Lock compatibility; Isolation levels; Lock escalation; The Lock Table statement; Commit points; Lock Wait and Deadlocks; Savepoints; Savepoints - considerations and restrictions; The Quiesce utility; SHARE.

Data Integrity

Referential Integrity; The Primary Key; The Foreign Key; Referential Integrity rules; Referential Integrity constraint names; Self referencing referential structures; Referential Integrity performance; Check constraints; Check constraint syntax; Allowable check constraints; When are check constraints enforced; Integrity pending (aka check pending) state; The Set Integrity command; Running Set Integrity via IBM Data Studio; Informational constraints.

Application Performance

The Db2 Optimizer; Levels of optimization; Operational utilities; Rebinding; The Runstats utility; Runstats parameters; Runstats - sampling options; Runstats - statistics profiling; Runstats - throttling; Runstats profiling examples; Automatic statistics collection; Automatic statistics profile generation; The Reorgchk utility; The Reorg utility; Offline / Online table reorg; Index reorg.

Db2 Tools

IBM Data Studio; IBM Data Studio Web Console.