COOPERATIVE COURSEWARE: DB2 V4.X ESSENTIALS AND APPLICATION PROGRAMMING

Cooperative Courseware: DB2 V4.X Essentials and Application Programming

Software Announcement
August 26, 1997
Announcement Letter Number: 297-328

The first of these three courses introduces the functions and capabilities of DB2, the components that make up a DB2 database, and the DB2 environment. The two other courses are designed to be taken in sequence. They teach how to use embedded structured query language (SQL) in applications to retrieve and update information contained in a DB2 database.

The courseware in this announcement is available from Education and Training, as part of the IBM Cooperative Courseware Development Program. Courseware information has been supplied by the program supplier, FirstClass Systems Corporation, an IBM Business Partner and IBM Cooperative Courseware Development Program participant.

Intended Customers

For DB2 Essentials, end users, application developers, and managers

For DB2 Application Programming I and II, application developers

Key Prerequisites

An IBM-compatible PC with:

486DX 33MHz, or higher, microprocessor
SVGA graphics
Windows 3.1, or later

Availability Date: August 26, 1997

------------------------------

This announcement is provided for your information only. For additional information, contact your IBM representative, call 800-IBM-4YOU, or visit the IBM home page at: %+%Start_URL%+% http://www.ibm.com

DESCRIPTION

DB2 Essentials (V4.X) (5631-611)

Total Learning Time: 5 to 6 hours

Course Topics

DB2 Overview

What is a database?
What is DB2?
DB2 environment

DB2 Structures

DB2 objects
Navigating DB2

Structured Query Language (SQL)

The DB2 language
Data definition language (DDL)
Data manipulation language (DML)
Built-in functions

Query Management Facility (QMF)

What is QMF?
QMF features
How QMF works

DB2 Security

Security objectives
Authorization IDs
GRANT and REVOKE
Views

DB2 Application Programming I (V4.X) (5631-612)

Total Learning Time: 4 to 5 hours

Course Topics

Overview

What is a database?
Functions of a database
Increasing data independence
Decreasing data redundancy
Increasing data security
Maintaining data integrity
DB2 structure
Relational database system
DB2 table structure
Automatic navigation
Project tables and join tables
DB2 characteristics
DB2 features
DB2 access
Addressing data in a DB2 database
Query Management Facility (QMF)
DB2 interactive; SQL processor using file input (SPUFI)
Third-party tools
Host language

SQL Data Retrieval

Retrieving data using the SELECT statement
SELECT all data
Column selection and ordering
Eliminating duplications
Arithmetic calculations
The WHERE clause
Comparison operators
Other JCL syntactical rules
Multiple search criteria
OR and IN comparison operators
BETWEEN and AND operators
The LIKE clause
LIKE clause example
SELECT... ORDER BY clause
Ordering results (ASC, DESC)
Intersecting tables
Combining data from multiple tables
Joining two tables in a SELECT
Applying column functions to a group
The AVERAGE function
Application examples:
- COUNT function
- MAX function
- MIN function
- SUM function
Group function summary
Using the GROUP BY clause
GROUP BY in a summary-only query
The HAVING clause
HAVING clause usage rules
Complex queries with subqueries
SQL subqueries
Rationale of subqueries
Multiple levels of subqueries
Application example of subqueries

Data Manipulation

Data manipulation language (DML) statements
The INSERT statement
INSERT rows
VALUES list
INSERT using specified columns
INSERT from table
Multiple INSERT command
Application example using INSERT
UPDATE row information
Application example using UPDATE
The DELETE statement
DELETE one or more rows

DB2 Application Program Structure

Manipulating variables
COBOL program code
WORKING-STORAGE SECTION
SQL communication area
SQL information
SQL CODE
SQLERRM field
SQLERRD
Warning fields
Warning field events
Declaration of variables
INCLUDE statements
DCLGEN
REPLACE option
Embedded SQL statements
Host variables

Program Execution

DB2 precompiler
Precompile and compile processes
Precompile considerations
Precompile screen
Precompile options
Compiling and link-editing
PROGRAM PREPARATION screen
JCL procedures
Freeing the program of syntax errors
Link-editing
Application planning with BIND
BINDing the program
BIND screen
BIND functions
BINDing
Program execution
Running the program

DB2 Application Programming II (V4.X) (5631-613)

Total Learning Time: 4 to 5 hours

Course Topics

Data Modification

INSERT statement
INSERT single or multiple rows of data
INCLUDE PROJECT statement
Inserting multiple records
Referential integrity rule for INSERT
DELETE statement
DELETE a row of data
Referential integrity rule for DELETE
UPDATE statement
UPDATE data
Referential integrity rule for UPDATE
ERROR and WARNING conditions
Program and system errors
The WHENEVER statement
Error checking

Retrieval Cursors

Defining a cursor
Opening a cursor
Process overview
Accessing multiple rows
The OPEN cursor statement
Fetching a row
The FETCH statement
A FETCH example
Advancing the cursor
Stop fetching rows
Example of stop fetching rows
Closing the cursor
Using the cursor
Declaring a cursor
Data modification using cursors
Direct updating
Indirect updating
Column update process
Column updating using cursors
Declaring a cursor for update
UPDATE WHERE CURRENT OF cursor
NOFOR precompiler option
Update a fetched record
COMMIT statement
ROLLBACK statement
DELETE a fetched record
DELETE WHERE CURRENT OF
Deleting using cursors

Program Performance

BIND considerations
DB2 locking feature
Share versus exclusive
Latches
Controlling the DB2 locking feature
Lock table statement
The LINES parameter
RR versus CS isolation settings
COMMIT/ROLLBACK environments
Design principles
COMMIT
Using COMMIT in an application
Using COMMIT in an on-line application
Using COMMIT in a batch application
Using a final COMMIT
Restart Control processing scheme
Program design tips
Program performance issues
Table design performance factors
Index types

Year 2000

These products do not have date dependencies and are Year 2000 Ready.

SUPPLEMENTAL INFORMATION

TECHNICAL INFORMATION

Specified Operating Environment

Hardware Requirements: An IBM-compatible PC with:

486DX 33MHz, or higher, microprocessor
Minimum 8MB of RAM
Microsoft-compatible mouse
1.44MB 3.5-inch diskette drive
SVGA graphics capability

Disk Space Requirements

Program Disk Description Number Space

DB2 Essentials (V4.X) 5631-611 20MB DB2 Application Programming I 5631-612 22MB (V4.X) DB2 Application Programming II 5631-613 22MB (V4.X)

Software Requirements: Microsoft Windows 3.1, Windows 95, or Windows NT

Planning Information

Packaging: Each course program package contains:

Installation and removal instructions
Registration card
3.5-inch diskettes

Security, Auditability, and Control

The customer is responsible for evaluation, selection, and implementation of security features, administrative procedures, and appropriate controls in application systems and communication facilities.

ORDERING INFORMATION

These are vendor-supplied, computer-based training courses, and are available in perpetual or lease license versions. They are priced based on the number of users authorized in the license, or on the number of installs, if an unlimited use, site, or single-system license is ordered.

These are Cooperative Courseware Development Program vendor-supplied products marketed under an agency relationship between IBM and the vendor.

Products are shipped using UPS normal delivery service. Specify feature number 3446 for overnight delivery service. The program supplier will add shipping charges to the invoice.

You can place orders by calling IBM Education and Training at 800-IBM-TEACH (426-8322) or through the local IBM office. To order a product, specify the program number, quantity, feature number 9080 for asset registration, and the media feature number.

Program Package: 3.5-inch program diskette (#5895) with documentation

OTC* Feature Description Number

DB2 Essentials (V4.X) (5631-611)

Single-System License -- 1 User 4001 LAN License -- 1 to 5 Users 4002 6 to 10 Users 4003 11 to 20 Users 4004 1 Site Unlimited 4005 Single-System License -- 1 User 6-Month Term 4006 12-Month Term 4007 12-Month LAN Term License 1 to 5 Users 4008 6 to 10 Users 4009 1 Site Unlimited 4010

DB2 Application Programming I (V4.X) (5631-612)

DB2 Application Programming II (V4.X) (5631-613)

*     One-Time Charge

TERMS AND CONDITIONS

Licensing: Supplier's license terms apply

Limited Warranty: Not warranted by IBM. Warranty, if any, provided by supplier.

Volume Orders: Not applicable

CHARGES

The charges provided in this announcement are suggested retail prices for the U.S. only and are provided for your information only. Dealer prices may vary, and prices may also vary by country. Prices are subject to change without notice. For additional information and current prices, contact your local IBM representative.

Feature Description Number OTC

DB2 Essentials (V4.X) (5631-611)

Single-System License -- 1 User 4001 $ 1,295 LAN License -- 1 to 5 Users 4002 1,943 6 to 10 Users 4003 2,590 11 to 20 Users 4004 5,180 1 Site Unlimited 4005 10,360 Single-System 6-Month Term License 4006 518 12-Month Term License 4007 648 12-Month LAN Term License 1 to 5 Users 4008 971 6 to 10 Users 4009 1,295 1 Site Unlimited 4010 2,590

DB2 Application Programming I (V4.X) (5631-612)

Single-System License -- 1 User 4001 998 LAN License -- 1 to 5 Users 4002 1,497 6 to 10 Users 4003 1,995 11 to 20 Users 4004 3,990 1 Site Unlimited 4005 7,980 Single-System 6-Month Term License 4006 399 12-Month Term License 4007 499 12-Month LAN Term License 1 to 5 Users 4008 748 6 to 10 Users 4009 998 1 Site Unlimited 4010 1,995

DB2 Application Programming II (V4.X) (5631-613)

Share this page

Digg Facebook Twitter Linked In

Contact IBM

Feedback

Tell us what you think

-->