DB10 - File-AID for DB2 - 1 Day

Course Description

This course provides the applications programmer with an in-depth knowledge of File-AID for DB2. It is aimed at programmers who need to code or support DB2 application programs written in COBOL, PL1 or C.

Pre-requisites

The delegate should be familiar with the z/OS host environment, together with a working knowledge of DB2, SQL and using TSO / ISPF.

Objectives

The aim of this course is to provide the programmer, familiar with DB2, with the necessary skills required to define, access and manipulate DB2 data, using File-AID for DB2.

On completion of this course the student will be able to:

  • define and set the user parameters for File-AID for DB2
  • set up a DB2 test environment, using correctly defined tables, views, indexes, synonyms and aliases using File-AID for DB2
  • define and maintain Referential Integrity using File-AID for DB2
  • define and maintain Table Check Constraints using File-AID for DB2
  • define and maintain DB2 security using File-AID for DB2
  • selectively copy data from one table to another
  • browse and edit the data on a table
  • use the Object List to display and maintain DB2 objects
  • use the Privileges Utility to display and maintain privileges against DB2 objects
  • use the File-AID for DB2 SQL LOAD utility to populate tables
  • use the IBM LOAD utility to populate tables
  • use the Impact Analysis Report to determine the effect of a change in column size or type
  • use the SQL Analysis and Development utility to improve SQL performance
  • produce reports of either data or audit information
  • maintain referential integrity and application integrity

Environment

Development will be performed using:

  • IBM Mainframe

Customisation

For on-site courses (i.e. at your premises), we are more than happy to tailor the course agenda to suit your exact requirements. In many cases, we are able to build your in-house standards and naming conventions into the delivered course.

Course Details

INTRODUCTION TO FILE-AID FOR DB2
File-AID for DB2 Invocation
The Primary Menu
Defaults and User Parameters
Browsing and Editing
Utilities
Creating, Dropping and Altering DB2 Objects
Copying Rows Between Tables
Object Lists
SQL Command Manager
Display, Grant, Revoke and Modify Privileges
Column Impact Analysis
SQL Analysis
Print Data
Referential and Application Relationships

SYSTEM PARAMETERS
An Overview of System Parameters
Browse Options
Edit Options
SQL Analysis Display Options
SQL Dataset Allocation
Batch Options
PF Keys
Database Parameters
Tablespace Parameters
Index Parameters
Logging Options
Print Options & Parameters

DB2 OBJECT CREATE, ALTER AND DROP
Object Selection

TABLESPACES
Tablespace Selection
Tablespace Definition
Tablespace Definition Parameters

TABLES
Table Selection
Table Parameters
Table Definition
Table Definition Parameters
Column Definition
Primary Key Definition
Foreign Key Definition
Default Column Value Definition
Column Check Constraints Definition

INDEXES
Index Selection
Index Definition
Index Definition Parameters

VIEWS
View Selection
View Definition
View Definition Parameters

SYNONYMS AND ALIASES
Synonym & Alias Selection
Synonyms
Synonym Definition
Aliases
Alias Definition

COPYING TABLE DATA
Table Selection
Table Copy Selection Template
Table Copy Selection Parameters
Column Selection
Column Selection Parameters
Selection Criteria
Copy Execution
SQL for Selection

BROWSING AND EDITING DATA
Browse Options
Browse Panel
Edit Options
Edit Panel
Selection Criteria
PF Keys
Row Navigation

PRIMARY COMMANDS
Autosave
Browse
Bounds
Cancel
Caps
Change
Char
Copy
Create
Cut
Delete
Display
Exclude
Filler
Find
Fmt
Graphic
Hex
Hide
Hold
Info
Locate
Max
Merge
Offset
Pad
Printp / Printds
Profile
Rbrowse
Redit
Repeat
Replace
Reset
Row
Show
Sort
SQLID
Table
Undo
Vfmt
Zero
Zoom

LINE COMMANDS
Insert
Repeat
Delete
Copy
Move
After
Before
Overlay
Exclude
First
Last
Show
Shift
Cols
Mask
Bounds
Protect
Unprotect
Overlay

SELECTION CRITERIA
Selection Criteria
Selection Criteria Parameters
Column Selection
Row Sequence
Where Clause
Relational Operators
Logical Operators
Conditional Operators

OBJECT LISTS
Object List Processing
Object List Utility
Object List Utility Parameters
Object Processing
Object List
Object Actions

SQL COMMAND MANAGER
SQL Command Manager
SPUFI Access

PRIVILEGES
Privilege Utility
Grant / Revoke Privileges
Grant / Revoke Privilege Parameters
Privilege Actions
Display Privileges
Grant Privileges
Revoke Privileges
Modify Privileges
Privilege List

EXTRACTING AND LOADING DATA
Extract and Load Utility
Data Extract and Load
Data Extraction
Extract Options
Extract Option Parameters
Extract Selection
Extract Execution
Extract Execution Parameters
Extract Dataset
Extract Dataset Parameters
Control Card Dataset
Control Card Dataset Parameters
Delimited File Options
Delimited File Option Parameters
Extract Confirmation
On-Line Extract
Batch Extract
Data Load
Load Options
Load Option Parameters
Load Dataset
Load Dataset Parameters
SQL Load
SQL Load Parameters
SQL Load Confirmation
SQL Load Report
DB2 Load Utility
DB2 Load Utility Parameters
DB2 Load Utility Confirmation
DB2 Load Utility Report

IMPACT ANALYSIS
Column Impact Analysis
Column Impact Analysis Parameters
Column Impact Analysis Table
Impact Selection
Impact Analysis Output Specification
Impact Analysis Output to Printer
Impact Analysis Output to Dataset
Column Impact Analysis Report

SQL PERFORMANCE
SQL Analysis and Development
PLAN_TABLE Maintenance
SQL Plan Analysis
Object Selection
F2PLAN_TABLE Table
Analysis Level
SQL Statement Analysis
SQL Statement Summary
Explain Results
Access Path Analysis
Explain Detail
Explain Detail - Table Information
Explain Detail - Column Information
Explain Detail - Index Information
Explain Detail - Related Table Information
Explain Detail - Table Partition Statistics
Explain Detail - Table Check Constraints
Explain Detail - Index Information
Explain Detail - Field Information
Predicate Analysis
Catalog Information
SQL Source Development and Analysis
Program / Source SQL Selection

PRINTING
Print Processing
Print Table Data
PRINTP Selection Template
Report Format
Report Format - Row Mode
Report Format - Table Mode
Audit Trail Report
Audit Trail Report Selection

TABLE RELATIONSHIPS
Relationship Options
Relationship Dataset
Relationship Table Maintenance
Relationship Summary
Relationship Details
RI Relationship Table Maintenance
Application Relationship Table Maintenance
Maintain Application Relationships

Course Format

The course contains many practical exercises to ensure familiarity with the product. Initially students create Tables and Indexes, and then proceed to load and access the data held on the DB2 Tables. They then proceed to perform various utilities against the data and DB2 objects. On completion of this course students will have the necessary skills required to define, access and manipulate DB2 data and DB2 objects, using File-AID for DB2.

The comprehensive Student Guide supplied is fully indexed serving as a useful reference tool long after the course has finished. Delegates will also be able to access a free help-line with technical questions relating to topics covered on the course.

Course Enquiry