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.