Access Definitions

The following syntax is used to generate statements for exported Access Definitions:

CREATE AD identifier.name 
   [DESC //'description'//] 
   SRCQUAL srcqual  START starttable 
   ADDTBLS {Y|N} MODCRIT {Y|N} ADCHGS {Y|N} 
   USENEW {Y|N} 
   PNSSTATE { N | L [LOCALRL //'pkval1','pkval2',...'pkvaln'// ] | 
       F ROWLIST //'filname.pns'// } 
   [GRPCOL colname GRPROWS n GRPVALS n] 
   [VAR (VarName PRMPT //'promptstring'// [DFTL dfltval])] 
   TABLE (tablename  REF {Y|N} [EXTRFREQ n] [EXTRLIMIT n] 
      PREDOP{A|O} [VARDELIM {~ | ! | @ | $ | : | % | + | ? }]
      COLFLAG {Y|N} DAA {Y|N} UR {Y|N}
         [CORRELNAME corelname] 
         [ARCIDX {indexname columnname1 columnnamen}
      [COLUMN (colname  DISP {Y|N} HEADING {N|L}{L|C|R}  
         NATIVELOB {Y|N} EXTRACT {Y|N} 
         [ASSOCIATION {colname|.ext}][PRED //selcrit//])] 
      [SORT ({A|D}colname1[, {A|D}colname2][, {A|D}col...])] 
      [SQL //whereclause//]) 
      [ARCHACTS {ACTION {SEP | BEFRT | BER | AELRT |
             EEP | SDP | BDFRT | BDR | ADR | ADLRT | EDP | 
             SRP | BRFRT | BRR | ARR | ARLRT | ERP}  
          SQL //SQL statement//  
         [HOSTVAR {~ | ! | @ | $ | : | % | + | ? }]  |  
         SAMEAS  actionname}[DBALIAS dbalias]
         [ON_ERROR {STOP | SKIP | PROCESS}])
      [FILEATTACH (PREFIX prefix [TRIGGER colname]
         STOP {Y|N} DELFILE {Y|N} INCL_DEFPATH {Y|N}
         NAME_PARTS(name1, name2,...namen)
         SEARCH_PATHS(//'path1'// //'path2'//... //'pathn'//) )]
   [REL (relname STATUS {K | NEWK | UNK | NEWUNK}
      USAGE {D|I|S} 
      Q1 {Y|N} Q2 {Y|N} LIMIT n 
      TYPE {dbms | PST | PST_GENERIC | UNK}
      PAR ptable CHI ctable
      [PAR_ACCESS {D | K | S}]
      [CHI_ACCESS {D | K | S}]
      [PAR_ KEYLIMIT n]
      [CHI_ KEYLIMIT n])]
   [DEFPATHS (//'path1'// //'path2'//... //'pathn'//)]);

The keywords correspond to values that can be specified for an Access Definition. For more information about an individual parameter, see Using the Editor.

Note: Some keywords are automatically populated with values during Export. If these keywords are marked as optional, however, they are not required for Import. If you edit an MVS™ definition to import it, you can omit these keywords.

Keywords

The keywords correspond to values that can be specified for an Access Definition.

AD
The name of the Access Definition.
identifier.name
Two-part name of an Access Definition.
DESC
Specify the description for the Access Definition
//description//
The description for the Access Definition, delimited by double slashes.

Tables

SRCQUAL
The default qualifier.
dbalias. [ creatorid ]
The one or two-part qualifier (DB Alias and Creator ID) for the tables in the Access Definition.
START
The Start Table for the Access Definition.
[ [ dbalias. ] cid. ] tablename
One, two, or three-part table name.

Only specify the DB Alias and Creator ID if they differ from the Default Qualifier.

Parameters

ADDTBLS
Indicator for ability to add tables to the Access Definition during a browse or edit session is selected. This capability is available for a future enhancement.
Y
Tables can be added to the Access Definition.
N
Tables cannot be added to the Access Definition.
MODCRIT
Indicator for the ability to add or modify criteria in the Access Definition during a browse or edit session. This capability is available for a future enhancement.
Y
Selection Criteria for the Access Definition can be specified or modified.
N
Selection Criteria for the Access Definition cannot be specified or modified.
ADCHGS
Indicator for the ability to modify and save the Access Definition during a browse or edit session. This capability is available for a future enhancement.
Y
Access Definitions can be saved during a browse or edit session.
N
Access Definitions cannot be saved.

Relationships

USENEW
Indicator for automatic inclusion of new relationships during processing.
Y
New relationships are included in an Extract Process.
N
New relationships are not included in an Extract Process.

Point and Shoot

Point and Shoot parameters for the Access Definition include:

PNSSTATE
Indicator for a Row List:
N
No Row List is included.
L
A Local Row List is included
LOCALRL
Indicator for a Local Row List.
pkvaln
One or more primary key values, delimited by two forward slashes (//) with each value enclosed in single parentheses.
F
Indicator for an external, named, Row List.
ROWLIST
Indicator for the name of the Row List file.
The name of an external Point-and-Shoot file for the Access Definition.
//filename//
The name of the external Point-and-Shoot file for the Access Definition, enclosed in single parentheses. You must provide the full path, if the file does not reside in the default directory.

Group

GRPCOL
The name of a column for group selection processing.
colname
Name of the column in the Start Table for the set of data you want to extract.
GRPROWS
The number of rows to be selected for group selection processing.
n
Maximum number of rows to extract for each unique group based on the specified column in the Start Table.
GRPVALS
The number of unique column values for group selection processing.
n
Number of unique groups to extract based on a selected column in the Start Table.

Variables

Each variable definition has the following parameters:

VAR
The name of the variable.
varname
The 1 to 12 character name for the variable in the Access Definition.
PRMPT
The prompt string for the variable.
promptstring
Text that prompts for the variable value at run time. Type the prompt string exactly as you want it to appear in the process request dialog (up to 50 characters).
DFTL
The default value for the variable.
dfltval
Default value for the variable to be used when no value is specified for the variable at run time.

Tables Keywords

A TABLE entry occurs for each table on the Table List for the Access Definition. The set of keywords for each table is enclosed in parentheses following the keyword “TABLE.” The keywords correspond to values specified on the Tables tab of the Access Definition Editor and the tabs of the Table Specifications dialog

TABLE
The name of the table.
tablename
The name of the table in the Access Definition. The fully-qualified name is given only if the DB Alias and Creator ID differ from the Default Qualifier.
REF
Reference table indicator. If a table is a reference table, all rows are extracted regardless of relationships.
Y
The table is a reference table.
N
The table is not a reference table.
EXTRFREQ
Selection factor for sampling rows.
n
A value from 1 through 9999.
EXTRLIMIT
Maximum number of rows to extract from a table.
n
A value from 1 through 99999999.
PREDOP
Indicator for combining selection criteria for the table.
A
Match selection criteria for all columns.
O
Match selection criteria for any column.
VARDELIM
The variable delimiter as one of the following characters.
~ ! @ $ : % + ?
Character required to identify a column value or built-in variable in SQL statements for Selection Criteria. The VARDELIM value must differ from the HOSTVAR value.
COLFLAG
Indicator for modifications to column specifications.
Y
Column specifications were modified.
N
Column specifications were not modified.
DAA
Indicator for deletion of rows from the table in the database after they are extracted to an Archive File.
Y
Delete rows from the table.
N
Do not delete rows from the table.
UR
Indicator for extracting uncommitted rows from the table.
Y
Extract uncommitted rows.
N
Do not extract uncommitted rows.
CORRELNAME
The correlation name for the table, if any.
corelname
The abbreviation for the fully qualified table name. You can use this short name in the SQL WHERE Clause.
ARCIDX
The Archive Index name, if any, and the corresponding column(s) to index. Up to 16 indexes can be designated per table.
indexname
The name of the Archive Index.
columnnamex
Name of columns used to create the Archive Index.

Column Keywords

COLUMN keywords correspond to values for each column on the Columns and Selection Criteria tabs of the Table Specifications dialog, accessed using the right-click options on the Tables tab of the Access Definition Editor. This set of keywords is enclosed in parentheses following the keyword “COLUMN.”

COLUMN
The name of the column.
colname
The name of the column. Column names appear in order of precedence. For example, the first column is listed first, followed by the second column, etc.
DISP
Indicator for displaying the column during a Point-and-Shoot session.
Y
The column is displayed.
N
The column is not displayed.
HEADING
Heading options for positioning the column headings in a Point-and-Shoot session.
N
Display column names as the heading during a Point-and-Shoot or browse session.
L
Display column labels as the heading during a or browse Point-and-Shoot session.
C
Position column heading as center.
L
Position column heading as left.
R
Position column heading as right.
NATIVELOB
Indicator for LOB column display mode in the Table Editor.
Y
Display normal LOB data.
N
Display LOB data as VARCHAR or VARBIN data.
EXTRACT
Indicator to extract CLOB or BLOB data in an Archive or Extract Process.
Y
Extract data.
N
Do not extract data.
ASSOCIATION
Association to identify the type of file in a LOB column
colname
Name of a column to reference for association with the LOB-type column. The first three characters in the column are used as the file name extension.
.ext
A file name extension to associate with the LOB-type column.
PRED
Indicator that selection criteria apply for the column.
//selcrit//
The selection criteria for the specified column.

Sort Keyword

If a column has a sort order, a SORT keyword is added. Add a sort order using the Sort tab on the Table Specifications dialog.

SORT
Options for order of data in column:
A
Display data in the column in ascending order.
D
Display data in the column in descending order.
colname
Name of the column.

SQL Keyword

This keyword is included when an SQL WHERE clause applies to the table, as on the SQL tab of the Table Specifications dialog.

SQL
Indicator for an SQL WHERE clause for the column.
//whereclause//
The SQL WHERE clause for the specified column.

Archive Actions Keyword

The ARCHACTS keyword is used for any Archive Actions included in the Access Definition.

ARCHACTS
Indicator that Archive Actions are included with the table. The Archive Actions for each table are contained in the parentheses that follow this keyword.
ACTION
Identifier for the Archive Action phase. If an ACTION is specified, SQL parameters are required.
SEP
Start of Extract Process.
BEFRT
Before Extract of First Row from Table.
BER
Before Extract of Row.
AELRT
After Extract of Last Row from Table.
EEP
End of Extract Process.
SDP
Start of Delete Process.
BDFRT
Before Delete of First Row from Table.
BDR
Before Delete of Row.
ADR
After Delete of Row.
ADLRT
After Delete or Last Row from Table.
EDP
End of Delete Process.
SRP
Start of Restore Process.
BRFRT
Before Restore of First Row to Table.
BRR
Before Restore of Row.
ARR
After Restore of Row.
ARLRT
After Restore of Last Row to Table
ERP
End of Restore Process.
SQL
The SQL WHERE clause associated with the Archive Action.
//SQL statement//
An Insert, Update, Delete, or Stored Procedure Call SQL statement.
HOSTVAR
The variable delimiter as one of the following characters.
~ ! @ $ : % + ?
Character required to identify a column value or built-in variable in SQL statements for Archive Actions. The HOSTVAR value must differ from the VARDELIM value.
SAMEAS
Option to use an SQL statement for an Action Phase of the same class. If SAMEAS is specified, SQL parameters are not required.
actionname
The name of the Action Phase.
DBALIAS
The DB Alias needed to reference a table in a database other than that referenced by the Default Qualifier.
dbalias
The DB Alias name.
ON_ERROR
Processing if an error occurs when an SQL statement is executed.
PROCESS
Ignore the SQL statement for the row and continue processing.
SKIP
Do not process the row, but continue processing other rows.

SKIP is valid for the Action Phases: Before Extract of Row, Before Delete of Row, and Before Restore of Row. The process report notes the number of rows skipped.

STOP
Stop the process when an error occurs as a result of the SQL statement. (Default)

File Attachments Keyword

If a table will have file attachments extracted, the FILEATTACH keyword is added.

FILEATTACH
File attachments are included with the table. Parameters for each file extracted with the table follow this keyword, in parentheses.
PREFIX
The prefix for pseudocolumn names in the Archive or Extract File.
prefix
The prefix, unique to the table, used to name prefix_FILE_NAME, prefix_BLOB, and prefix_ATTRIB.
TRIGGER
The column that controls processing of a file attachment.
colname
The column name.
STOP
Indicator for processing if a file is not found.
Y
Stop processing.
N
Do not stop processing.
DELFILE
Indicator for processing the attached file during Delete Processing.
Y
Delete the file.

An attached file is deleted only if the associated row is successfully deleted.

N
Do not delete the file.
INCL_DEFPATH
Indicator to search the default paths for the file attachment.
Y
Search the paths in DEFPATHS.
N
Search paths in SEARCH_PATHS.
NAME_PARTS
The components in attached file names.
namen
The components that make up the name of the attached file. You can provide a combination of column names and literals, separated by commas and with literals enclosed in quotes. Values in the named column(s) are concatenated with any literals to generate the file name, which is combined with the search path to locate the appropriate file.
SEARCH_PATHS
One or more file paths to search, in the order listed. Once the file is found, any remaining paths are not searched.
//pathn//
The file path(s) searched for the file.

Relationships Keyword

A REL entry is provided for each relationship between tables referenced in the Access Definition. This set of keywords is enclosed in parentheses following the keyword “REL.” Keywords correspond to values specified for the use of a relationship by an Access Definition.

REL
The relationship name.
relname
The name of the relationship.
STATUS
The status of the relationship.
K
A known relationship.
NEWK
A known relationship that is new.
UNK
An unknown relationship.
NEWUNK
An unknown relationship that is new.
USAGE
The Select status for the relationship.
D
Dormant.
I
Initial
S
Selected
Q1
Option 1:
Y
Extract a parent row for every child row to satisfy referential integrity rules.
N
Do not extract a parent row for every child row.
Q2
Option 2:
Y
Extract additional child rows for each parent row extracted to satisfy Option 1.
N
Do not extract additional child rows.
LIMIT
The maximum number of rows to extract from a child table in any relationship.
n
The maximum number of rows.
TYPE
The type of the relationship.
type
Indicates whether the relationship is generic, explicit (Optim™), or defined to a specific DBMS (e.g., Oracle).
PAR
Name of the parent table in the relationship.
ptable
The parent table name.
CHI
Name of the child table in the relationship.
ctable
The child table name.
PAR_ACCESS
The default method of accessing the parent table in the relationship:
D
Default.
K
Force Key Lookup.
S
Force Scan.
CHI_ACCESS
The default method of accessing the child table in the relationship:
D
Default.
K
Force Key Lookup.
S
Force Scan.
PAR_ KEYLIMIT
The maximum number of key lookups performed at one time for the parent table.
n
Specify the maximum number (1 to 100) of key lookups.
CHI_ KEYLIMIT
The maximum number of key lookups performed at one time for the child table.
n
Specify the maximum number (1 to 100) of key lookups.

Default Paths Keyword

Default path for attached files.

DEFPATHS
One or more default paths to search for file attachments. Paths are searched in the order listed.

Once the file is found, any remaining paths are not searched.

//pathn//
The file path name used to search for the file.