Database Gateway for APPC Data Dictionary

A Database Gateway for APPC Data Dictionary

The Procedural Gateway Data Dictionary (PG DD) is maintained in a conventional Oracle database. It is installed by a SQL*Plus installation script (pgddcr8.sql in the %ORACLE_HOME%\dg4appc\admin directory on Microsoft Windows or $ORACLE_HOME/dg4appc/admin directory on UNIX based systems) and manipulated by PGAU statements and standard SQL statements.

The dictionary is divided into two sections:

the environment dictionary
the active dictionary

The environment dictionary is static and should not be changed. The contents of the environment dictionary support proper translation from the remote transaction's environment to the integrating server's environment, and is platform-specific. The active dictionary is updated at the user's location by the PGAU in response to definitions supplied by the user.

Topics:

PG DD Environment Dictionary

The PGAU uses some dictionary tables strictly as input. These dictionary tables define environmental parameters for PGAU. Both table and values are installed by a SQL*Plus script at gateway installation time and are not to be modified by the installation.

The environment dictionary does not reference the active dictionary, but the active dictionary does reference environment dictionary entries.

Environment Dictionary Sequence Numbers

The e nvironment dictionary requires unique identifying numbers in some columns to join environment dictionary entries together. Oracle sequence objects are therefore created by the Oracle Database Gateway for APPC to support this requirement.

Table A-1 presents the Oracle sequence objects and their descriptions.

Table A-1 Oracle Sequence Objects

Oracle Sequence Objects	Descriptions
`pga.envrseq`	Environment id tag
`pga.compseq`	Compiler id tag
`pga.eattrseq`	Environment Attribute id tag
`pga.dtypeseq`	Datatype id tag
`pga.dtattseq`	Datatype Attribute id tag

Environment Dictionary Tables

The environment dictionary tables contain constants that describe the following components of the operating environment:

pga_maint
pga_environments
pga_env_attr
pga_env_values
pga_compilers
pga_datatypes
pga_datatype_attr
pga_datatype_values
pga_usage
pga_modes

pga_maint

The pga_maint table stores the PG DD maintenance information, including version number and change history, as presented in Table A-2:

Table A-2 pga_maint

Column Type Contents

Column	Type	Contents
`version`	`number(10,4)`	PG DD version in format `VVRRFF.rrff`, where: `VV` - base version; `RR` - base release; `FF` - base fix; `rr` - port-specific release; `ff` - port-specific fix.
`mntdate`	`date`	Oracle date and time at which the PG DD was upgraded.
`change`	`varchar2(256)`	Description of the PG DD upgrade.

version

number(10,4)

PG DD version in format VVRRFF.rrff, where:

VV - base version;

RR - base release;

FF - base fix;

rr - port-specific release;

ff - port-specific fix.

mntdate

date

Oracle date and time at which the PG DD was upgraded.

change

varchar2(256)

Description of the PG DD upgrade.

pga_environments

The pga_environments table stores the defined environment keywords, as presented in Table A-3:

Table A-3 pga_environments

Column Type Content

Column	Type	Content
`name`	`varchar2(16)` not null	Environment. Primary key.
`env#`	`number (9, 0)` not null	Env id. Foreign key.

name

varchar2(16) not null

Environment.

Primary key.

env#

number (9, 0) not null

Env id.

Foreign key.

pga_env_attr

The pga_env_attr table stores the types of environmental attributes, as presented in Table A-4:

Table A-4 pga_env_attr

Column Type Content

Column	Type	Content
`name`	`varchar2 (16)` not null	Attribute. Primary key.
`attr#`	`number (9, 0)` not null	Attribute id. Foreign key.
`coltype`	`varchar2 (4)` not null	Attr value type. Foreign key.

name

varchar2 (16) not null

Attribute.

Primary key.

attr#

number (9, 0) not null

Attribute id.

Foreign key.

coltype

varchar2 (4) not null

Attr value type.

Foreign key.

pga_env_values

The pga_env_values table stores the values for environments, as presented in Table A-5:

Table A-5 pga_env_values

Column	Type	Content
`env#`	`number (9, 0)` not null	Env id. Primary key.
`attr#`	`number (9, 0)` not null	Attribute id. Primary key.
`numval`	`number (9, 0)`	Numeric attribute value.
`charval`	`varchar2 (64)`	Character attribute value.
`dateval`	`date`	Date attribute value.

pga_compilers

The pga_compilers table stores the compiler environment names, as presented in Table A-6:

Table A-6 pga_compilers

Column	Type	Content
`name`	`varchar2 (16)` not null	Compiler name. Primary key.
`plscomp`	`varchar2 (30)`	PLS compiler name. Secondary key.
`env#`	`number (9, 0)` not null	Env id. Foreign key.
`comp#`	`number (9, 0)` not null	Compiler env id. Foreign key.
`ddl_process`	`number (9, 0)` not null	PGADDL processor number.

pga_datatypes

The pga_datatypes table stores the datatype keywords, as presented in Table A-7:

Table A-7 pga_datatypes

Column Type Content

comp#

number (9, 0) not null

Compiler env id.

Primary key.

name

varchar2 (16) not null

Datatype keyword.

Primary key.

dt#

number (9, 0) not null

Datatype_values.

Foreign key.

pga_datatype_attr

The pga_datatype_attr table stores datatype attribute keywords, as presented in Table A-8:

Table A-8 pga_datatype_attr

Column Type Content

Column	Type	Content
`name`	`varchar2 (16)` not null	Attribute keyword. Primary key.
`attr#`	`number (9, 0)` not null	Attribute id. Foreign key.
`coltype`	`varchar2 (4)` not null	Type of attr. Foreign key.

name

varchar2 (16) not null

Attribute keyword.

Primary key.

attr#

number (9, 0) not null

Attribute id.

Foreign key.

coltype

varchar2 (4) not null

Type of attr.

Foreign key.

pga_datatype_values

The pga_datatype_values table stores the datatype attribute values, as presented in Table A-9:

Table A-9 pga_datatype_values

Column	Type	Content
`comp#`	`number (9, 0)` not null	Compiler env id. Primary key.
`dt#`	`number (9, 0)` not null	datatype_values. Foreign key.
`attr#`	`number (9, 0)` not null	Attribute id. Foreign key.
`dag#`	`number (9, 0)`	Datatype attr group no.
`numval`	`number (9, 0)`	Numeric attribute value.
`charval`	`varchar2 (40)`	Character attribute value.
`dateval`	`date`	Date attribute value.

pga_usage

The pga_usage table performs a referential integrity check of pga_data and pga_field column "usage" as presented in Table A-10:

Table A-10 pga_usage

Column Type Content

Column	Type	Content
`name`	`varchar2(6)`	Value for the "`usage`" field of data dictionary tables. For example: '`PASS`' '`SKIP` '`NULL`' '`ASIS`' Primary key. Max length => 4-char string length.

name

varchar2(6)

Value for the "usage" field of data dictionary tables. For example:

'PASS'

'SKIP

'NULL'

'ASIS'

Primary key.

Max length => 4-char string length.

pga_modes

The pga_modes table performs a referential integrity check of pga_call_parm column "mode", as presented in Table A-11:

Table A-11 pga_modes

Column Type Content

Column	Type	Content
`name`	`varchar2(6)`	Name of valid parameter call modes. For example: `IN` `OUT` `IN OUT` `Max length => 'IN OUT'` string length.

name

varchar2(6)

Name of valid parameter call modes. For example:

IN

OUT

IN OUT

Max length => 'IN OUT' string length.

PG DD Active Dictionary

The PG DD active data dictionary is created by pgddcr8.sql at installation, but maintained using PGAU. The active dictionary can refer to items (by ID number) in the environment dictionary.

Active Dictionary Versioning

The PG DD active dictionary tables contain the descriptions of transactions and data structures. There might be more than one version of a definition. Old versions are retained indefinitely.

In PGAU dictionary operations, a definition is referred to by its "name", which can be qualified by a specific version number. If omitted, the most recent version is assumed.

Active Dictionary Sequence Numbers

Because the active dictionary is constantly changing, the identifying numbers needed to join active dictionary entries together must also change. To support this requirement, PG DD installation creates the following Oracle sequence objects.

Table A-12 lists the Oracle sequence objects and their descriptions:

Table A-12 Active Dictionary Oracle Sequence Object Descriptions

Oracle Sequence Objects	Description
`pga.transeq`	Transaction id tag
`pga.tranvers`	Transaction Version id tag
`pga.tattrseq`	Transaction Attribute id tag
`pga.callseq`	APPC-Call id tag
`pga.callvers`	Call Version id tag
`pga.parmseq`	APPC-Call Parameter id tag
`pga.dataseq`	Data id tag
`pga.fieldseq`	Data subfield id tag
`pga.datavers`	Data Version id tag
`pga.dattrseq`	Data Attribute id tag

Active Dictionary Tables

Following is a list of active dictionary tables:

pga_trans
pga_trans_attr
pga_trans_values
pga_trans_calls
pga_call
pga_data
pga_fields
pga_data_attr
pga_data_values

pga_trans

One row exists in the PGA_TRANS table for each user transaction. The row is created by a PGAU DEFINE TRANSACTION statement and used by a PGAU GENERATE statement to create the PL/SQL package (TIP).

Table A-13 This 3-column table presents the column, type and content information for PGA_TRANS:

Table A-13 pga_trans

Column	Type	Content
`tname`	`varchar2(64)`	Transaction name as defined by the customer. Primary key. Max length => APPC TPname string length.
`version`	`number(9,0)`	Version identification of this entry; it exists in the table because multiple archived or invalid entries might exist and be kept for possible future reactivation. Primary key. Set from an Oracle sequence object for transaction version inserted into the PG DD.
`updtdate`	`date`	Audit-trail date/time record last updated.
`updtuser`	`varchar2(30)`	Audit-trail user ID/program which last updated this record.
`trans#`	`number(9,0)`	PGA Transaction number, used for the define call, define data and define transaction statements. Foreign key. `pga_trans_values(trans#)`, `pga_trans_calls(trans#)`. Set from an Oracle sequence object for transaction inserted into the PG DD.

pga_trans_attr

The pga_trans_attr table relates a character string defining the transaction attributes supported by PGA to pga_trans_values entries through an attribute id number and type.

The pga_trans_attr table is also used for integrity checks of transaction attributes when new transactions are being defined.

There is an entry in the pga_trans_attr table for each transaction attribute name. All possible transaction attribute names supported by PGA on any defined transaction are specified. There is one row for each attribute, and no duplicates are allowed.

Table A-14 This 3-column table presents the column, type and content information for pga_trans_attr:

Table A-14 pga_trans_attr

Column	Type	Content
`name`	`varchar2(16)`	Character string name of attribute. Primary key. Contains: "`ENVIRONMENT`", "`LUNAME`", "`TPNAME`", "`LOGMODE`", "`SIDEPROFILE`", "`SYNCLEVEL`", "`NLS_LANGUAGE`", "`REMOTE_MBCS`" "`LOCAL_MBCS`"
`attr#`	`number(9,0)`	Attribute id assigned. Foreign key. pga_data_values(attr#). Set from an Oracle sequence object for each supported transaction attribute inserted into the PG DD.
`coltype`	`varchar2(4)`	Type of Oracle column from which attribute value is retrieved from pga_tran_values. For example: '`NUM ' => pga_tran_values(numval)` '`CHAR' => pga_tran_values(charval)` '`DATE' => pga_tran_values(dateval)`
`required`	`char(1)`	If not null, required keyword for `DEFINE TRANSACTION`; if null, optional.

pga_trans_values

The pga_trans_values table describes the values of transaction attributes.

A row exists to specify the value of each attribute of each transaction defined in the data dictionary.

The column, type and content information for pga_trans_values is presented in Table A-15:

Table A-15 pga_trans_values

Column	Type	Content
`trans#`	`number(9,0)`	Transaction id from `pga_trans(trans#)`. Primary key. Set from an Oracle sequence object for transaction inserted into the PG DD.
`attr#`	`number(9,0)`	Attribute id from `pga_trans_attr(attr#)`, Primary key. Set from an Oracle sequence object for each supported transaction attribute inserted into the PG DD.
`numval`	`number(9,0)`	Attribute's numeric value, for example for a given transaction's `SYNCLEVEL` attribute `0`.
`charval`	`varchar2(64)`	Attribute's character value; for example, a given transaction's `TPNAME` attribute.
`dateval`	`date`	Attribute's date value. Probably always null; included for completeness.

pga_trans_calls

The pga_trans_calls table relates all calls available with any single transaction to each specific call definition through a call ID number.

An entry exists in the pga_trans_calls table for each PL/SQL call referenced in a transaction definition through the CALL(cname,...) operand. One row per transaction call; no duplicates.

The column, type and content information for pga_trans_calls is presented in Table A-16:

Table A-16 pga_trans_calls

Column Type Content

Column	Type	Content
`trans#`	`number(9,0)`	Transaction id number from `pga_trans(trans#)`. Primary key. Set from an Oracle sequence object for transaction inserted into the PG DD.
`seq#`	`number(9,0)`	Sequence number of this call. Primary key.
`call#`	`number(9,0)`	Call id number in `pga_call(call#)`. Foreign key. Copied from `pga_call.call#` for the referenced call when this transaction definition was inserted or updated.

trans#

number(9,0)

Transaction id number from pga_trans(trans#).

Primary key.

Set from an Oracle sequence object for transaction inserted into the PG DD.

seq#

number(9,0)

Sequence number of this call.

Primary key.

call#

number(9,0)

Call id number in pga_call(call#).

Foreign key.

Copied from pga_call.call# for the referenced call when this transaction definition was inserted or updated.

pga_call

The pga_call table relates all calls that are available for all defined transactions, to a unique call id number and PL/SQL remote procedural call (RPC) name. One entry exists in this table for each PL/SQL call (defined in a DEFINE CALL statement).

One row per call, duplicates are possible when multiple transactions make identical calls. The plsrpc specification must be unique within the Oracle database which makes the calls, and rows are uniquely distinguished by call#.

The column, type and content information for pga_call are presented in Table A-17:

Table A-17 pga_call

Column	Type	Content
`cname`	`varchar2(48)`	Call name for PGAU reference; Primary key. Max length => COBOL name string length
`plsrpc`	`varchar2(30)`	RPC call name for reference in PL/SQL (public procedure to be generated). Max length => PL/SQL RPC name length
`updtdate`	`date`	Audit trail date/time of record's last update.
`updtuser`	`varchar2(30)`	Audit trail user id/program which last updated this record.
`version`	`number(9,0)`	Version identification of this entry, because multiple archived or invalid entries might exist and be kept for possible future reactivation. Primary key. Set from an Oracle sequence object for call version inserted into PG DD.
`call#`	`number(9,0)`	Call id number. Foreign key. `pga_trans_calls(call#)`, `pga_call_parm(call#)`. Set from an Oracle sequence object for each call inserted into the PG DD.

pga_call_parm

The pga_call_parm table relates all parameters of any single transaction call to the data definitions describing each parameter.

One entry exists in the pga_call_parm table for each parameter on a call in the PARMS() operand of the PGAU DEFINE CALL statement. One row per parameter, duplicates allowed when multiple calls (in the pga_call table) refer to the same parameters.

Table A-18 This 3-column table presents the column, type and content information for pga_call_parm:

Table A-18 pga_call_parm

Column	Type	Content
`call#`	`number(9,0)`	Call number for the referencing call from `pga_calls`. Primary key. Set from an Oracle sequence object for each call inserted into the PG DD.
`parm#`	`number(9,0)`	Position in the `PARMS()` argument of `DEFINE CALL` operation (1,2,3...). Primary key.
`cmode`	`varchar2(6)`	Call mode of this parameter; one of the values in `pga_data_modes`. For example: '`IN`', '`OUT`', '`IN OUT`' Max length => '`IN OUT`' string length
`data#`	`number(9,0)`	Data definition # in `pga_data(data#)` of this item. Foreign key. `pga_data(data#)`,`pga_data_values(data#)`. Copied from `pga_data.data#` for the data item when this call/parm definition was inserted or updated.

pga_data

The pga_data table defines each data item used as a parameter in a call and relates the remote host data name to its PL/SQL variables and any component subfields or clauses within each data item (if the data item is an aggregate, such as a record). Each data item might have attributes related to it through its corresponding field definition. Even atomic data items have a single row in the pga_field table.

One row exists in the pga_data table for each data item defined by a PGAU DEFINE DATA or REDEFINE DATA statement.

Table A-19 This 3-column table presents the column, type and content information for pga_data:

Table A-19 pga_data

Column	Type	Content
`comp#`	`number(9,0)`	Compiler id number.; Foreign key. (`pga_compiler(comp#)`. Set from `pga_compiler`(comp#) based on the language parameter specified on the `DEFINE DATA` statement when the data definition is inserted.
`compopts`	`varchar2(100)`	Compiler options from the `COMPOPTS` keyword on the `DEFINE DATA` statement.
`dname`	`varchar2(255)`	Name from the DEFINE statement; Primary key. Max length => COBOL name length
`plsdvar`	`varchar(30)`	PL/SQL variable name of data item for reference in PL/SQL. Max length => PL/SQL variable length
`version`	`number(9,0)`	Version number of this entry. Set from an Oracle sequence object for data version inserted into the PGADD.
`updtdate`	`date`	Audit-trail date/time this control record last updated.
`updtuser`	`varchar2(30)`	Audit-trail user id/program which last updated this record.
`usage`	`varchar2(6)`	Default usage of this data item: `PASS`, `SKIP`, `NULL`, `ASIS`. Used primarily by PGAU `REPORT`. Max length => 4-char string length
`data#`	`number(9,0)`	Data definition number. Foreign key. (`pga_call_parm(data#)`, `(pga_field(data#)` Set from an Oracle sequence object.

pga_fields

The pga_fields table defines each field within a data item and relates the remote host data field to its PL/SQL variables or nested records. Each field item might have attributes related to it (by field#) in the pga_data_attr and pga_data_values tables.

One row exists in the pga_fields table for each atomic item, field, clause, or nested record defined by a PGAU DEFINE DATA statement. Several rows would exist (related by a single data# and incrementing fld#) to define an aggregate data item, one row per field or group.

Table A-20 This 3-column table presents the column, type and content information for pga_fields:

Table A-20 pga_fields

Column	Type	Content
`data#`	`number(9,0)`	Data definition number. Primary key. (`pga_data(data#), pga_call_parm(data#)`. Set from an Oracle sequence object.
`fname`	`varchar2(255)`	Extracted or derived name of a field if dname defines aggregate data. Max length => COBOL name length
`plsfvar`	`varchar2(30)`	PL/SQL variable name of subfield in aggregate data for reference in PL/SQL. Max length => PL/SQL variable length
`updtdate`	`date`	Audit-trail date/time this control record last updated.
`updtuser`	`varchar2(30)`	Audit-trail user id/program which last updated this record.
`fld#`	`number(9,0)`	Clause or field within data definition id no. Foreign key. `pga_data_values(fld#)`. Set from an Oracle sequence object.
`pos#`	`number(9,0)`	Relative position number of each field defined within an aggregate data item (for example, 1, 2 3, and so on) or `NULL` if data is atomic.
`usage`	`varchar2(6)`	Usage of this data field: '`PASS`', '`SKIP`', '`NULL`', '`ASIS`'. Max length => 4-char string length
`mask`	`varchar2(30)`	Datatype or Mask value. For example: '`S9(4)`' '`X(24)`' '`VARCHAR2(24)`' '`BINARY_INTEGER(16)`' `NULL` When `NULL`, item defined is assumed to be a COBOL group or PL/SQL nested record. Max length => arbitrarily chosen
`maskopts`	`varchar2(100)`	Datatype or Mask options value. For example: '`USAGE COMP-4`' '`DISPLAY`' `NULL` Max length => arbitrarily chosen

pga_data_attr

The pga_data_attr table defines all possible data attribute names allowed by PGA and relates each attribute name to a number and type, by which the value of this attribute for a specific data item can be selected from pga_data_values.

The pga_data_attr table is also used for integrity checks of data attributes when new data items are defined.

There is one entry in the pga_data_attr table for every possible attribute name to which any PGA supported data item might relate.

Table A-21 This 3-column table presents the column, type and content information for pga_data_attr:

Table A-21 pga_data_attr

Column	Type	Content
`name`	`varchar2(16)`	Character string name of attribute. Primary key. Contains: "LEVEL" "RENAMEMF" (renames member first) "RENAMEML" (renames member last) "REMAPSMF" (redefines member first) "REMAPSML" (redefines member last) "REMAPSWM" (redefines when member) "REMAPSWC" (redefines when char value) "REMAPSWN" (redefines when num value) "REPGRPFF" (occurs n) "REPGRPVF" (odo first n) "REPGRPVL" (odo last n) "REPGRPVM" (odo depending member) "REPGRPKA" (either Key Asc name) "REPGRPKD" (either Key Desc name) "REPGRPIX" (either index name) "PLSTYPE" "JUST" (justified char data) "SYNC" (aligned aggregate data) "LOCAL_LANGUAGE" "REMOTE_LANGUAGE" "LENGTH" (LENGTH IS variable) Max length => attr name string lengths
`attr#`	`number(9,0)`	Attribute id assigned. Foreign key. pga_data_values(attr#). Set from an Oracle sequence object for each supported data attribute inserted into the PG DD.
`coltype`	`varchar2(4)`	Type of Oracle column from which attribute value is retrieved from pga_data_values. For example: '`NUM ' => pga_data_values(numval)` '`CHAR'=> pga_data_values(charval)` '`DATE' => pga_data_values(dateval)`
`required`	`char(1)`	If not null, required keyword.

pga_data_values

A row exists in the pga_data_values table for each attribute of each data item defined by each data definition.

Table A-22 This 3-column table presents the column, type and content information for pga_data_values:

Table A-22 pga_data_values

Column	Type	Content
`fld#`	`number(9,0)`	Data Field Definition number from pga_data(fld#). Primary key.
`attr#`	`number(9,0)`	Attribute id from `pga_data_attr(attr#)`. Primary key.
`numval`	`number(9,0)`	Attribute's numeric value. For example: number for "LEVEL" number for "REMAPSWN" (redefines) number for "REPGRPFF" (occurs n) number for "REPGRPVF" (odo first n) number for "REPGRPVL" (odo last n) If a non-numeric attribute, this item is `NULL`.
`charval`	`varchar2(40)`	Attribute's character value. fname for "RENAMEMF (renames first) fname for "RENAMEML" (renames last) fname for "REMAPSMF" (redefines first) fname for "REMAPSML" (redefines last) fname for "REMAPSWM" (redefines when) fname for "REPGRPVM" (odo member) string for "REMAPSWC" (redefines) string for "REPGRPKA" (occurs key) string for "REPGRPKD" (occurs key) string for "REPGRPIX" (occurs index) string for "PLSTYPE" (PL/SQL data type) string for "JUST" string for "SYNC" string for "REMOTE_LANGUAGE" fname for "LENGTH" If a non-character attribute, this item is `NULL`. Max length => NLS_charset string length
`dateval`	`date`	Attribute's date value. Always null, included for completeness.
`qual`	`number (9,0)`	Qualified name number. Foreign key.