Table of Contents

Table of Contents

About DB2 UDB for AS/400 Database Programming

Who should read the DB2 UDB for AS/400 Database Programming book

What's new for V4R5 in the DB2 UDB for AS/400 Database Programming book

Setting Up Database Files

Setting up database files: general considerations

Describing database files

Dictionary-described data

Methods of describing data to the system

Describing a database file to the system

Describing database files using DDS

Describing the access path for a database file

Protecting and monitoring your database data

Creating database files: introduction

Moving an object

Database file and member attributes: introduction

Specifying file name and member name (FILE and MBR) parameters when creating database files

Specifying the physical file member control (DTAMBRS) parameter when creating database logical files

Specifying the source file and source member (SRCFILE and SRCMBR) parameters when creating database files

Specifying the database file type (FILETYPE) parameter when creating database files

Specifying the maximum number of members allowed (MAXMBRS) parameter when creating database files

Specifying where to store the data (UNIT) parameter when creating or changing database files

Specifying the frequency of writing data to auxiliary storage (FRCRATIO) parameter when creating, changing, or overriding database files

Specifying the frequency of writing the access path (FRCACCPTH) parameter when creating database files

Specifying the check for record format description changes (LVLCHK) parameter when creating or changing a database file

Specifying the current access path maintenance (MAINT) parameter when creating database files

Specifying the recover (RECOVER) parameter when creating database files

Specifying the file sharing (SHARE) parameter when creating a database file

Specifying the locked file or record wait time (WAITFILE and WAITRCD) parameters when creating a database file

Specifying the public authority (AUT) parameter when creating a database file

Specifying the system on which the file Is created (SYSTEM) parameter when creating a database file

Specifying the file and member text (TEXT) parameter when creating a database file

Specifying the coded character set identifier (CCSID) parameter when creating database files

Specifying the sort sequence (SRTSEQ) parameter when creating a database file

Specifying the language identifier (LANGID) parameter when creating database files

Setting Up Physical Files

Creating a physical file

Specifying physical file and member attributes when creating a physical file

Physical file and member attributes: Expiration date

Physical file and member attributes: Size of the physical file member

Physical file and member attributes: Storage allocation

Physical file and member attributes: Method of allocating storage

Physical file and member attributes: Record length

Physical file and member attributes: Deleted records

Physical file and member attributes: Physical file capabilities

Physical file and member attributes: Source type

Setting Up Logical Files

Describing Logical File Record Formats

Describing Field Use for Logical Files

Deriving New Fields from Existing Fields

Describing Floating-Point Fields in Logical Files

Describing Access Paths for Logical Files

Selecting and Omitting Records Using Logical Files

Using Existing Access Paths

Creating a Logical File

Creating a Logical File with More Than One Record Format

Logical File Members

Join Logical File Considerations

Basic Concepts of Joining Two Physical Files (Example 1)

Setting Up a Join Logical File

Using More Than One Field to Join Files (Example 2)

Reading Duplicate Records in Secondary Files (Example 3)

Using Join Fields Whose Attributes Are Different (Example 4)

Describing Fields That Never Appear in the Record Format (Example 5)

Specifying Key Fields in Join Logical Files (Example 6)

Specifying Select/Omit Statements in Join Logical Files

Joining Three or More Physical Files (Example 7)

Joining a Physical File to Itself (Example 8)

Using Default Data for Missing Records from Secondary Files (Example 9)

A Complex Join Logical File (Example 10)

Joining database files: Performance considerations

Joining database files: Data Integrity Considerations

Joining database files: Summary of Rules

Securing a Database

Granting file and data authority

Object Operational Authority

Object Existence Authority

Object Management Authority

Object Alter Authority

Object Reference Authority

Using Data Authorities to Grant Users Access to Physical and Logical Files

Specifying Public Authority

Using Database File Capabilities to Control I/O Operations

Limiting Access to Specific Fields of a Database File

Using Logical Files to Secure Data

Operations Navigator Database

Accessing Operations Navigator Database

Performing Tasks in Operations Navigator Database

Creating a Library in Operations Navigator Database

Creating a View in Operations Navigator Database

Creating a Table in Operations Navigator Database

Creating an Index in Operations Navigator Database

Creating an Alias for a Table or View in Operations Navigator Database

Creating an SQL Procedure in Operations Navigator Database

Creating an SQL Function in Operations Navigator Database

Creating a Distinct Type in Operations Navigator Database

Creating SQL Scripts in Operations Navigator Database

Creating an SQL Performance Monitor in Operations Navigator Database

Processing Database Files in Programs

Database file processing: Run time considerations

Database file processing: File and Member Name

Database file processing: File Processing Options

Database file processing: Specifying the Type of Processing

Database file processing: Specifying the Initial File Position

Database file processing: Reusing Deleted Records

Database file processing: Ignoring the Keyed Sequence Access Path

Database file processing: Delaying End of File Processing

Database file processing: Specifying the Record Length

Database file processing: Ignoring Record Formats

Database file processing: Determining If Duplicate Keys Exist

Database file processing: Data Recovery and Integrity

Protecting Your File with the Journaling and Commitment Control

Writing Data and Access Paths to Auxiliary Storage

Checking Changes to the Record Format Description

Checking for the Expiration Date of the File

Preventing the Job from Changing Data in the File

Locking shared data

Locking Records

Locking Members

Locking Record Format Data

Sharing Database Files in the Same Job or Activation Group

Open Considerations for Files Shared in a Job or Activation Group

Input/Output Considerations for Files Shared in a Job or Activation Group

Close Considerations for Files Shared in a Job or Activation Group

Sequential-only processing of database files

Open Considerations for Sequential-Only Processing

Input/Output Considerations for Sequential-Only Processing

Close Considerations for Sequential-Only Processing

Summary of run time considerations for processing database files

Storage Pool Paging Option Effect on Database Performance

Opening a Database File

Opening a Database File Member

Using the Open Database File (OPNDBF) Command

Using the Open Query File (OPNQRYF) Command

Creating a Query with the OPNQRYF Command

Using an Existing Record Format in the File

Using a File with a Different Record Format

OPNQRYF Examples

CL Program Coding with the OPNQRYF Command

The Zero Length Literal and the Contains (*CT) Function

Selecting Records without Using DDS

Considerations for Creating a File and Using the FORMAT Parameter

Considerations for Arranging Records

Considerations for DDM Files

Considerations for Writing a High-Level Language Program

Messages Sent When the Open Query File (OPNQRYF) Command Is Run

Using the Open Query File (OPNQRYF) Command for More Than Just Input

Date, Time, and Timestamp Comparisons Using the OPNQRYF Command

Date, Time, and Timestamp Arithmetic Using OPNQRYF CL Command

Using the Open Query File (OPNQRYF) Command for Random Processing

Open Query File command: Performance Considerations

Open Query File command: Performance Considerations for Sort Sequence Tables

Performance Comparisons with Other Database Functions

Considerations for Field Use

Considerations for Files Shared in a Job

Considerations for Checking If the Record Format Description Changed

Other Run Time Considerations for the OPNQRYF command

Typical Errors When Using the Open Query File (OPNQRYF) Command

Basic Database File Operations

Setting a Position in the File

Reading Database Records

Reading Database Records Using an Arrival Sequence Access Path

Reading Database Records Using a Keyed Sequence Access Path

Waiting for More Records When End of File Is Reached

Releasing Locked Records

Updating Database Records

Adding Database Records

Identifying Which Record Format to Add in a File with Multiple Formats

Using the Force-End-Of-Data Operation

Deleting Database Records

Closing a Database File

Monitoring database file errors in a program

Managing Database Files

Managing Database Members

Member Operations Common to All Database Files

Adding Members to Files

Changing Member Attributes

Renaming Members

Removing Members from Files

Physical File Member Operations

Initializing Data in a Physical File Member

Clearing Data from Physical File Members

Reorganizing a Table

Displaying Records in a Physical File Member

Changing Database File Descriptions and Attributes

Effect of Changing Fields in a File Description

Changing a Physical File Description and Attributes

Example 1: Changing a Physical File Description and Attributes

Example 2: Changing a Physical File Description and Attributes

Changing a Logical File Description and Attributes

Using Database Attribute and Cross-Reference Information

Displaying Information about Database Files

Displaying Attributes for a File

Displaying the Descriptions of the Fields in a File

Displaying the Relationships between Files on the System

Displaying the Files Used by Programs

Displaying the System Cross-Reference Files

Writing the Output from a Command Directly to a Database File

Example of Using a Command Output File

Output File for the Display File Description Command

Output Files for the Display Journal Command

Output Files for the Display Problem Command

Recovering and restoring your database

Recovering data in a database file

Managing journals

Ensuring data integrity with commitment control

Reducing time in access path recovery

Saving access paths

Restoring access paths

Journaling access paths

System-managed access-path protection (SMAPP)

Rebuilding access paths

The database recovery process after an abnormal system end

Database file recovery during the IPL

Database file recovery after the IPL

Effects of the storage pool paging option on database recovery

Database file recovery options table

Database save and restore

Database considerations for save and restore

Force-writing data to auxiliary storage

Using Source Files

Source File Concepts

Creating a Source File

IBM-Supplied Source Files

Source File Attributes

Creating Source Files without DDS

Creating Source Files with DDS

Working with Source Files

Using the Source Entry Utility (SEU)

Using Device Source Files

Copying Source File Data

Loading and Unloading Data from Non-AS/400 Systems

Using Source Files in a Program

Creating an Object Using a Source File

Creating an Object from Source Statements in a Batch Job

Determining Which Source File Member Was Used to Create an Object

Managing a Source File

Changing Source File Attributes

Reorganizing Source File Member Data

Determining When a Source Statement Was Changed

Using Source Files for Documentation

Controlling the integrity of your database with constraints

Setting up constraints for your database

Details: Setting up constraints

Removing constraints from your database

Details: Removing constraints

Working with a group of constraints

Details: Working with a group of constraints

Working with constraints that are in check pending status

Unique constraints

Primary key constraints

Check constraints

Ensuring data integrity with referential constraints

Adding a referential constraint

Before you add a referential constraint

Defining the parent file in a referential constraint

Defining the dependent file in a referential constraint

Specifying referential constraint rules

Details: Specifying referential constraint delete rules

Details: Specifying referential constraint update rules

Details: Specifying referential constraint rules--journaling requirements

Details: Adding a referential constraint

Details: Avoiding constraint cycles

Verifying a referential constraint

Enabling and disabling referential constraints

Details: Enabling or disabling a referential constraint

Removing referential constraints

Details: Removing a constraint with the CST parameter

Details: Removing a constraint with the TYPE parameter

Details: Ensuring data integrity with referential constraints

Example: Ensuring data integrity with referential constraints

Referential integrity terms

Referential integrity enforcement

Foreign key enforcement

Parent key enforcement

Constraint states

Check pending status in referential constraints

Dependent file restrictions in check pending

Parent file restrictions in check pending

Referential integrity and AS/400 functions

Triggering automatic events in your database

Creating trigger programs

Examples of trigger programs

Trigger buffer section

Recommendations for trigger programs

Precautions to take when coding trigger programs

Trigger and application programs that are under commitment control

Trigger and application programs that are not under commitment control

Trigger program error messages

Monitoring the Use of Trigger Programs

Adding a Trigger to a File

Required authorities and data capabilities for triggers

Displaying triggers

Removing a trigger

Triggers and their relationship to other AS/400 functions

Triggers and their relationship to referential integrity

Database Distribution

Appendix A. Database File Sizes

Examples: Database File Sizes

Appendix B. Double-Byte Character Set (DBCS) Considerations

DBCS Field Data Types

DBCS Field Mapping Considerations

DBCS Field Concatenation

DBCS Field Substring Operations

Comparing DBCS Fields in a Logical File

Using DBCS Fields in the Open Query File (OPNQRYF) Command

Using the Wildcard Function with DBCS Fields

Comparing DBCS Fields Through OPNQRYF

Using Concatenation with DBCS Fields through OPNQRYF

Using Sort Sequence with DBCS

Appendix C. Database Lock Considerations

Bibliography

Index

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]