Previous Topic: Specifying Distributed FilesNext Topic: Editing Access Path Details

Modifying Access Paths › Modifying Access Path Details

Modifying Access Path Details

This topic discusses use of the Edit Access Path Details panel including specifying unique/duplicate key retrieval sequence, access path maintenance, alternate collating sequence, select/omit criteria, generation mode, and changing source member text and names.

Access paths are implemented as separate i OS objects. You can specify various implementation options for each access path such as the i OS object name for the logical file and whether the access path maintenance will be Rebuild, Delay, or Immediate. CA 2E provides defaults for those options and protects the values that should not be changed.

For instance, i OS requires that immediate access path maintenance be specified if you specify the DDS UNIQUE keyword. The values allowed for the implementation details depend on the access path. A table of i OS access path implementation attributes follows.

 

Access Path Type

(1) (2)

Unique or Dup Key Sequence (DDS only)

(3)

Access Path Maint.

(4)

Alt Col (DDS only)

(5)

Selection

PHY Physical

UPD Update (default)

UPD Update

RTV Retrieval (default)

RTV Retrieval

RSQ Resequence

QRY Query

SPN Span

  -

U

U/L/F/ /C

U

U/L/F/ /C

U/L/F/ /C

F

U/L/F/ /C

-

I

I,D,R

I

I,D,R

I,D,R

I,D,R

I,D,R

-

-

-

-

-

Yes

-

Yes

-

-

-

S/D

S/D

S/D

D

S/D

The following legend applies for the access path types:

(1) Unique key status (DDS unique keyword or SQL unique keyword with create index statement). U indicates unique; if not unique, see note (2). The default UPD and RTV access paths must be unique.

(2) Duplicate key sequence for DDS only (L=LIFO, F=FIFO,' '=undefined, C=FCFO)

(3) i OS access path maintenance (I=*IMMED, R=*REBLD, D=*DLY);

  for QRY access paths (I=*FIRSTIO, D=*MINWAIT, R=*ALLIO). See the following table.

(4) Alternative collating sequence table for DDS only (DDS ALTCOL keyword)

(5) Selection type (S=static, D=dynamic) (DDS DYNSLT keyword)

 

The following table shows the effect of each of the Access Path Maintenance options depending on whether this option is implemented in DDS, CL, SQL or DDL.

Edit Access Path Details Panel Maintenance Option

Method Used to Implement the Access Path Maintenance Option

 

 

 

DDS
(Non-QRY Access Paths)

(1)

OPNQRYF
command
(QRY Access Paths)

 

SQL


DDL

I (Immediate)

*IMMED

*FIRSTIO

Create Index

Create Index

D (Delay)

*DLY

*MINWAIT

No Index

No Index

 

 

 

 

 

R (Rebuild)

*REBLD

*ALLIO

No Index

No Index

(1) For QRY access paths the access path maintenance options are implemented using OPTIMIZE parameter values on the i OS

OPNQRYF command.

For more information about parameters for the OPNQRYF command, see IBM i Control Language Reference.