The Procedural Gateway Administration Utility (PGAU) is a utility that assists the PGA administrator or user to define the data which is to be exchanged with remote transaction programs. It generates the PL/SQL Transaction Interface Packages (TIPs) discussed in Chapter 3, "Creating a TIP", Appendix E, "Tip Internals" and, depending upon your communication protocol, either Chapter 4, "Client Application Development (SNA Only)" or Chapter 7, "Client Application Development (TCP/IP Only)".
This chapter contains the following sections:
Note:
If you have existing TIPs that were generated previously on a gateway using the SNA protocol and you want to utilize the new TCP/IP feature, then the TIPs will have to be regenerated by PGAU with mandatoryNLS_LANGUAGE and Side Profile Settings. Specify the appropriate ASCII character set in the DEFINE TRANSACTION command.
This is due to the fact that the gateway assumes that the appropriate "user exit" in IMS Connect is being used, which would translate between the appropriate ASCII and EBCDIC character sets.
PGAU maintains a data dictionary, PG DD, which is a collection of tables in an Oracle database. These tables hold the definitions of the remote transaction data and how that data is to be exchanged with the remote transaction program. Refer to "Ensuring TIP and Remote Transaction Program Correspondence" for a discussion of the correlation between TIPs and their respective remote transaction programs. The PG DD contents define this correlation.
The PGA administrator or user defines the correlation between TIPs and the remote transaction program using the following PGAU commands (also called "statements"):
PGAU DEFINE DATA statements, which describe the data to be exchanged.
PGAU DEFINE CALL statements, which describe the exchange sequences.
PGAU DEFINE TRANSACTION statements, which group the preceding CALL and DATA commands together and describe certain aspects unique to the remote transaction program, such as its network name or location.
PGAU GENERATE statement, which the PGA administrator or user uses to specify and create the TIP specifications, after the TIP/transaction correlation has been defined in the PG DD. Additional PGAU commands needed to alter and delete definitions in the PG DD are described in "PGAU Commands" later in this chapter.
The PGAU commands are known collectively as Procedural Gateway Definition Language (PGDL). Any references to PGDL are to the collection of PGAU commands defined in this chapter.
PGAU provides editing and spooling facilities and the ability to issue SQL commands.
Caution:
Do not use PGAU instead of SQL*Plus for general database administration.Alternatively, PGAU commands can be supplied in a control file. The control file contains one or more PGAU commands for manipulating the PG DD or generating TIP specifications.
PGAU issues status messages on each operation. The message text is provided through Globalization Support message support. PGAU processes each command in sequence. An error on a single command causes PGAU to skip that command.
To run PGAU, the PG DD tables must already have been created. Refer to the gateway configuration chapters corresponding to your communications protocol in the Oracle Database Gateway for APPC Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64 or Oracle Database Gateway for APPC Installation and Configuration Guide for Microsoft Windows.
The following sections provide information on COMMIT/ROLLBACK processing.
PGAU never issues COMMIT commands. As the user, it is your responsibility to COMMIT PG DD changes when all the changes are implemented. Otherwise Oracle issues a COMMIT command by default when you exit the PGAU session. If PG DD changes are not to be committed, you must run a ROLLBACK command before exiting.
PGAU sets a savepoint at the beginning of each PGAU command that alters the PG DD and at the beginning of a PGAU GROUP. PGAU rolls back to the savepoint upon any PGAU command or group failure.
You can code COMMIT or ROLLBACK commands within PGAU scripts, or interactively in PGAU, but not within a GROUP.
Neither COMMIT nor ROLLBACK is issued for PGAU GENERATE or REPORT commands.
For information about grouping PGAU commands together to roll back changes in case of failure, refer to the discussion of the PGAU "GROUP" command later in this chapter.
Before you can invoke PGAU, your Oracle database should already be set up. If it is not, refer to the chapter on configuring your Oracle Database Gateway for APPC, in the Oracle Database Gateway for APPC Installation and Configuration Guide for Microsoft Windows or Oracle Database Gateway for APPC Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64.
Before executing PGAU, you must set the ORACLE_HOME environment variable to the directory into which the gateway server was installed.
If you want to receive PGAU messages in a language other than English, set the LANGUAGE environment variable to the appropriate value.
PGAU is invoked by entering the pgau command. You can run prepared scripts of PGAU commands directly from the operating system prompt by specifying a command string on the command line using the following syntax:
For Microsoft Windows:
C:\> pgau @command_file C:\> pgau command=@command_file C:\> pgau command="@command_file"
For UNIX based systems:
$ pgau @command_file $ pgau command=@command_file $ pgau command="@command_file"
The default extension is .sql. Use the last form if the command filename contains non-alphanumeric characters.
To perform PG DD maintenance and PL/SQL package generation, you must connect to the Oracle database from PGAU as user PGAADMIN, using the CONNECT command. The "PGAU Commands" section discusses how to use the "CONNECT" command.
This version of PGAU supports the definition of remote transaction data in COBOL, entered interactively or in a file. File input is supported for the DEFINE and REDEFINE DATA commands, and standard COBOL data division macros or "copybooks" can be supplied.
PGAU and the PG DD support different versions of user data and remote transaction definitions. This facilitates alteration and testing of data formats and transactions without affecting production usage.
Multiple versions of any data or transaction definitions might exist. You must ensure that versions stored and used in the PG DD are synchronized with the remote transactions. Neither the gateway, PGAU, nor generated TIPs provide this synchronization, but they will issue messages as error conditions are detected.
Data definitions must exist before being referenced by call definitions. Call definitions must exist before being referenced by transaction definitions.
Note:
It is your responsibility to ensure that the data transaction definition versions that are stored and used in the PG DD are synchronized with the remote transactions. The gateway, PGAU and generated TIPs do not provide this synchronization, but issue messages as error conditions are detected.The general process for defining and testing a TIP for a given transaction is as follows:
Define input and output using COBOL data definitions.
Redefine the default datanames and PL/SQL variable names created by the above process (optional).
Define PL/SQL FUNCTION calls to be generated as part of the PL/SQL package.
Define a transaction that groups the above functions.
Generate the TIP specifications from the previously stored TRANSACTION, CALL, and DATA definitions.
Generate the TIP PL/SQL stored procedures.
Test the TIP by calling it from a high-level application.
Refer to Chapter 3, "Creating a TIP" for more information about TIPs.
Definition names are unique identifiers that you designate through PGAU. The name is a string of 1 to 30 bytes. If punctuation or white space is included, the name must be specified within double quotes.
Names are assumed to be unique within the PG DD, except when duplicate names are intentionally distinguished by a unique version number. It is your responsibility to ensure name uniqueness.
Valid characters for PG DD definition names are:
A through Z
a through z
0 through 9
#
$
_ (underscore)
Note that unless defaults are overridden, transaction definition names might be PL/SQL package names, and transaction call names might be PL/SQL procedure names. Therefore, choose names that are syntactically correct for PL/SQL, making certain that they are also unique names within that system. As the user, it is your responsibility to ensure PL/SQL name compatibility.
The PG Data 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 all PG DD operations, a definition or package is referred to by its name. That name can be qualified by a specific version number.
All version numbers:
are supplied by Oracle Sequence Objects
are purely numeric
must be free from user alteration, suffixing, or prefixing
Refer to Appendix A, "Database Gateway for APPC Data Dictionary" and the pgddcr8.sql file in the %ORACLE_HOME%\dg4appc\admin directory on Microsoft Windows or $ORACLE_HOME/dg4appc/admin directory on UNIX based systems for the specific names of the Oracle Sequence Objects used for version number generation.
If an explicit version number is specified, it is presumed to be the version number of an existing definition, not a new definition. Such explicit references are used when:
generating a TIP from a specific remote transaction version
defining a remote transaction based on a specific data version
If no explicit version is specified:
The latest (highest number) is assumed when a definition is being referenced. This is the MAX value selected from the VERSION column for all rows with the same definition name, not the CURRVAL number.
The next (NEXTVAL number) is assumed when a definition is being added.
Version numbers might not be contiguous. Although version numbers are always increasing, multiple versions of a given definition might skip numbers. This is because the sequence object is shared for all definitions of the same type (TRANSACTION, CALL, or DATA), and sequence object NEXTVAL is not restored in event of an Oracle database transaction ROLLBACK. Thus, NEXTVAL might be assigned to a different definition before the next version of the same definition.
Examples of valid definition names:
DEFINE TRANSACTION|CALL|DATA
payroll (new or latest definition)
payroll_xaction (new or latest definition)
payroll_xaction VERSION(3)...(an existing definition)
No attempt is made by PGAU to synchronize versions. Although the existence of dependent items is assured at definition time, deletion is done without reference to dependencies. For example, generating a TIP requires prior definition of the transaction, which requires prior definition of the calls, which require prior definition of the data. But nothing prevents PGAU from deleting an active data definition while a call definition still references it.
All PGAU keywords can be specified in upper or lower case and are not reserved words. Reservation is not necessary because all keywords have known spelling and appear in predictable places, and because all data is delimited by parentheses, apostrophes, quotes, or blanks.
Note that all unquoted values specified by keywords are stored in the PG Data Dictionary in uppercase unless otherwise specified in the keyword description.
PGAU allows you to enter Procedural Gateway Administration commands (commands), such as DEFINE, UNDEFINE, REDEFINE, and GENERATE, in addition to normal SQL commands. The SET and SHOW commands are also implemented. In addition, the PGAU commands listed in the following section are available to you.
This command enables you to make a connection to PGAU. Use the CONNECT command to log on to an Oracle database, optionally specifying the user ID and password in addition to the Oracle instance. The CONNECT command has the following syntax:
For Microsoft Windows:
CONNECT [username|username/password|username@connect-string|username\password@connect-string
For UNIX based systems:
CONNECT [username|username/password|username@connect-string|username/password@connect-string
username\password for Microsoft Windows and username/password for UNIX based systems are the usernames and passwords used to connect to PGAU,
and
connect-string specifies the service name of the remote database.
Refer to the Oracle Database Net Services Administrator's Guide Services Administrator's Guide for more information about specifying remote databases.
CONNECT CONNECT SCOTT/TIGER CONNECT SCOTT@OTHERSYS
Before connecting, you must set ORACLE_SID to the database SIDname.
If you want to connect to a remote database, you must set TNS_ADMIN to the full pathname of the directory in which the file tnsnames.ora is stored.
You do not need to place a semi colon (;) at the end of the command.
This command creates a new version of the PL/SQL call definition in the PG Data Dictionary.
DEFINE CALL cname [PKGCALL(pcname)] [PARMS( (dname {IN | OUT | IN OUT} [VERSion(datavers)]), ...)];
Where Table 2-1 describes the parameters in this syntax:
Table 2-1 DEFINE CALL Parameter Descriptions
Refer to "Sample PGAU DEFINE CALL Statements" in Appendix F for examples of DEFINE CALL commands.
Version of the CALL definition is not specified and defaults to NEXTVAL of the Oracle Sequence Object for CALL.
PKGCALL and PARMS can be specified in either order.
You need to place a semi colon (;) at the end of the command.
This command creates a new version of the data definition in the PG DD.
DEFINE DATA dname [PLSDNAME(plsdvar)] [USAGE({PASS|ASIS|SKIP})] [COMPOPTS ('options')] LANGUAGE(language) {(definition)|INFILE("filespec")};
Table 2-2 describes the DEFINE DATA parameters:
Table 2-2 DEFINE DATA Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
is a mandatory parameter. It is the name of the data definition to be created. |
|
|
is an optional parameter. It is the name of the PL/SQL variable associated with |
|
|
is an optional parameter. It specifies the way the TIP handles the data items when exchanged in calls with the remote transaction.
The default value, The |
|
|
is an optional parameter. It specifies the compiler options used when compiling the data definition on the remote host. The only option currently supported is ' Refer to "DEFINE DATA Usage Notes" for further information on this option. |
|
|
is a mandatory parameter. It specifies the name of the programming language in the supplied definition. PGAU presently supports only COBOL. |
|
|
is mutually exclusive with the |
|
|
is mutually exclusive with the ( Note that |
Refer to "Sample PGAU DEFINE DATA Statements" in Appendix F for examples of DEFINE DATA commands.
Version of the DATA definition is not specified and defaults to NEXTVAL of the Oracle Sequence Object for DATA.
PLSDNAME, USAGE, and LANGUAGE can be specified in any order.
INFILE ("filespec") is a platform-specific designation of a disk file.
COMPOPTS ('TRUNC(BIN)') should be used only when the remote host transaction was compiled using COBOL with the TRUNC(BIN) compiler option specified. When this option is used, binary data items defined as PIC 9(4) or PIC S9(4) can actually contain values with 5 digits, and binary data items defined as PIC 9(9) or PIC S9(9) can actually contain values with 10 digits. Without COMPOPTS ('TRUNC(BIN)'), PGAU generates NUMBER(4,0) or NUMBER(9,0) fields for these data items, resulting in possible truncation of the values.
When COMPOPTS ('TRUNC(BIN)') is specified, PGAU generates NUMBER(5,0) or NUMBER(10, 0) fields for these data items, avoiding any truncation of the values. Care must be taken when writing the client application to ensure that invalid values are not sent to the remote host transaction.
For a PIC 9(4) the value must be within the range 0 to 32767, for a PIC S9(4) the value must be within the range -32767 to +32767, for a PIC 9(9) the value must be within the range 0 to 2,147,483,647, and for a PIC S9(9) the value must be within the range -2,147,483,647 to +2,147,483,647. COBOL always reserves the high-order bit of binary fields for a sign, so the value ranges for unsigned fields are limited to the absolute values of the value ranges for signed fields. For further information, refer to the appropriate IBM COBOL programming manuals.
Refer to "USAGE(PASS)" in Appendix D, "Datatype Conversions" for information about how PGAU converts COBOL statements.
You need to place a semi colon (;) at the end of the command.
This command creates a new version of the transaction definition in the PG Data Dictionary.
DEFINE TRANSACTION tname CALL(cname [VERS(callvers)], ... [ENVIRONMENT(name)] {SIDEPROFILE(name) [LUNAME(name)] [TPNAME(name)] [LOGMODE(name)] | LUNAME(name) TPNAME(name) LOGMODE(name)} [SYNCLEVEL(0|1|2)] [NLS_LANGUAGE("nlsname")]; [REMOTE_MBCS("nlsname")] [LOCAL_MBCS("nlsname")];
Table 2-3 describes the DEFINE TRANSACTION parameters:
Table 2-3 DEFINE TRANSACTION Parameter Descriptions
| Parameter | Description |
|---|---|
|
A mandatory parameter. It is the name of the transaction definition to be created. If you do not specify a package name (TIP name) in the |
|
|
|
A mandatory parameter. It specifies a list of previously defined calls (created with The relative position of each
|
|
|
Specifies the name of the host environment for this transaction, for example, |
|
This parameter is optional for a gateway using SNA, but if omitted, the user must specify the parameters for This parameter is mandatory for a gateway using the TCP/IP connection. It has no comparable SNA meaning. You need to run the This name represents a group of IMS transactions with similar IMS Connect attributes. You can re-use the same name as long as they share the same IMS Connect attributes, such as subsystem ID, TIME delay or socket type. Refer to Chapter 6, "PG4TCPMAP Commands (TCP/IP Only)" for details. |
|
|
|
This parameter is optional on a gateway using SNA: Overrides the LUNAME within the Side Information Profile, if the Side Information Profile was specified. It specifies the SNA Logical Unit name of the transaction manager (OLTP). This is either the fully-qualified LU name, Name values can be alphanumeric with'@', '#', and'$' characters and a single period '.', to delimit the network from the LU, as in netname.luname, if fully qualified. Quoted values can contain any character, and delimited by quotes ("), or apostrophes ('). Case is preserved for all values. This parameter is not applicable when using the TCP/IP communication protocol. |
|
|
This parameter is optional on a gateway using SNA: Overrides the
Name values can be alphanumeric with'@', '#', and'$' characters only if unquoted. Quoted values can contain any character, and delimited by quotes ("), or apostrophes ('). Case is preserved for all values. This parameter is required for a gateway using TCP/IP support for IMS Connect. It must be the IMS Transaction Name.
|
|
|
This parameter is optional on a gateway using SNA: Overrides the Name values can be alphanumeric with '@', '#', and '$' characters only. Values cannot be quoted. Case is not preserved and always translated to upper case. This parameter is not applicable when using the TCP/IP communication protocol. |
|
|
This parameter is optional on a gateway using SNA: It specifies the APPC On a gateway using TCP/IP: The default of this parameter is '0', which is the only accepted value. |
|
|
This is an optional parameter. The default value is " Note that if you are using TCP/IP, make sure that you set this parameter to "american_america.us7ascii". |
|
|
This is an optional parameter. The default value is " |
|
|
This is an optional parameter. The default value is " |
Refer to "Sample PGAU DEFINE TRANSACTION Statement" in Appendix F for examples of DEFINE TRANSACTIONs commands.
DEFINE TRANSACTION Usage Notes:
NLS_LANGUAGE and the Oracle database's LANGUAGE specify default character sets to be used for conversion of all single-byte character fields for the entire transaction. These defaults can be overridden for each SBCS field by the REDEFINE DATA REMOTE_LANGUAGE or LOCAL_LANGUAGE parameters.
The version of the TRANSACTION definition is not specified and defaults to NEXTVAL of the Oracle Sequence Object for TRANS.
REMOTE_MBCS and LOCAL_MBCS specify the default multi-byte character sets to be used for conversion of all DBCS or MBCS fields for the entire transaction. This default can be overridden for each DBCS or MBCS field by the REDEFINE DATA REMOTE_LANGUAGE or LOCAL_LANGUAGE parameters.
You must place ";" at the end of the command.
Use this command to describe a table, view, stored procedure, or function. If neither TABLE, VIEW, nor PROCEDURE are explicitly specified, the table or view with the specified name is described.
The DESCRIBE command has the following syntax:
DESCRIBE [TABLE table|VIEW view|PROCEDURE proc|some_name]
Table 2-4 describes the DESCRIBE parameter:
Table 2-4 DESCRIBE Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
is the tablename |
|
|
is the viewname |
|
|
is the procedurename |
DESCRIBE PROCEDURE SCOTT.ADDEMP DESCRIBE SYS.DUAL DESCRIBE TABLE SCOTT.PERSONNEL DESCRIBE VIEW SCOTT.PVIEW
You do not need to place ";" at the end of the command.
Use this command to disconnect from an Oracle database.
The DISCONNECT command has the following syntax:
DISCONNECT
None
None
You do not need to place ";" at the end of the command.
Use this command to execute a one-line PL/SQL statement.
The EXECUTE command has the following syntax:
EXECUTE pl/sql block
pl/sql block is any valid pl/sql block. Refer to the Oracle Database PL/SQL Language Reference for more information.
EXECUTE :balance := get_balance(333)
You do not need to place ";" at the end of the command
Use this command to terminate PGAU.
The EXIT command has the syntax:
EXIT
None
None
You do not need to place ";" at the end of the command.
The "quit" command is not a valid statement in PGAU.
A PL/SQL package is built and written to the indicated output files. The PG Data Dictionary is not updated by this command.
GENERATE tname [VERSion(tranvers)] [PKGNAME(pname)] [PGANODE(dblink_name)] [OUTFILE("[specpath]{specname}[.{spectype}]")] [,"[bodypath]{bodyname}[.{bodytype}]]") [DIAGNOSE ({[TRACE({[SE] [,IT] [,QM] [,IO] [,OC] [,DD] [,TG] })] [PKGEX({[DC][,DR]})])};
Table 2-5 describes the GENERATE parameters:
Table 2-5 GENERATE Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
is a mandatory parameter. It is the transaction name defined in a |
|
|
is an optional parameter. It specifies which transaction definition is to be used. The |
|
|
is an optional parameter. It specifies the name of the PL/SQL package to be created. If this operand is omitted, the package name is assumed to be the same as the transaction name. |
|
|
is an optional parameter. It specifies the Oracle database link name to the gateway server. If this operand is omitted, "PGA" is assumed to be the |
|
|
is an optional parameter. If this parameter is specified, |
|
|
is the optional directory path of the TIP specification and the TIP content documentation. It defaults to the current directory. The value must end with a backslash (\) for Microsoft Windows and a slash (/) for UNIX based systems. |
|
|
is the filename of the TIP specification and the TIP content documentation. It defaults to |
|
|
is the optional file extension of the TIP specification and defaults to |
|
|
is the optional directory path of the TIP body. It defaults to |
|
|
is the optional file name of the TIP body. It defaults to |
|
|
is the optional file extension of the TIP body and defaults to The TIP Content output path defaults to Refer to the "GENERATE Usage Notes:" for more examples, and Appendix E, "Tip Internals" for more information. |
|
|
is an optional parameter with two options, |
|
|
specifies that an internal trace of the execution of PGAU is written to output file
Trace messages are provided as a diagnostic tool to Oracle Support Services and other Oracle representatives to assist them in diagnosing customer problems when generating TIPs. They are part of an Oracle reserved function for which the usage, interface, and documentation might change without notice at Oracle's sole discretion. This information is provided so customers might document problem symptoms.
Messages are written tracing subroutine name and arguments upon entry, and subroutine name and conditions at exit.
Messages are written tracing PGAU initialization and termination functions.
Messages are written tracing control block allocation, queuing, searching, dequeuing, and deletion.
Messages are written tracing input, output, and control operations for |
|
|
Messages are written tracing the loading of transaction, call, data parameter, field, attribute, environment and compiler information from the PG DD.
Messages are written tracing the Oracle UPI call results for SQL statement processing and
Messages are written tracing steps completed in TIP Generation, typically a record for each call, parameter, and data field for which a PL/SQL code segment has been generated. |
|
|
causes additional TIP execution time diagnostic logic to be included within the generated PL/SQL package.
Enables runtime checking of repeating group limits and the raising of exceptions when such limits are exceeded. Enables warning messages to be passed from the
The additional logic checks for the existence of warnings and, if present, causes them to be displayed using The TIP generation default is to suppress such warnings on the presumption that a TIP has been tested with production data and that data conversion anomalies either do not exist, or are known and to be ignored. If errors occur which might be due to data conversion problems, regeneration of the TIP with Note: A runtime switch is also required to execute the warning logic. Additional messages are written to a named pipe for tracing the data conversion steps performed by the TIP as it executes. |
|
This option only causes the trace logic to be generated in the TIP. It must be enabled when the TIP is initialized. Refer to"Controlling TIP Runtime Conversion Warnings" in Chapter 8, "Troubleshooting" for more information.
PL/SQL single line Comments are included in TIPs which reference the PG DD id numbers for the definitions causing the TIP function calls and conversions. |
Refer to "Sample PGAU GENERATE Statement" in Appendix F for examples of GENERATE commands.
All PGAU GENERATE trace messages are designated PGU-39nnn. Refer to the %ORACLE_HOME%\dg4appc\mesg\pguus.msg file on Microsoft Windows or $ORACLE_HOME/dg4appc/mesg/pguus.msg on UNIX based systems for further information on any given trace message.
The pgau.trc trace message output file is overwritten by the next invocation of GENERATE, regardless of the TRACE specification. A trace header record is always written to the pgau.trc file. If a particular trace file is to be saved, it must be copied to another file before the next invocation of GENERATE.
TRACE options can be specified in any order or combination, and can also be specified with PKGEX operand on the same GENERATE statement.
You must place ";" at the end of the command.
Multiple PGAU commands can be grouped together for purposes of updating the PG DD, and for rolling back all changes resulting from the commands in the group, if any one statement fails.
No COMMIT processing is performed, even if all commands within the group succeed. You perform the COMMIT either by coding COMMIT commands in the PGAU script, outside of GROUPs, or by issuing COMMIT interactively to PGAU.
PGAU issues a savepoint ROLLBACK to conditions before processing the group if any statement within the group fails.
GROUP (pgaustmt1; pgaustmt2; ... pgaustmtN);
pgaustmtN: is a PGAU DEFINE, REDEFINE, or UNDEFINE statement
GROUP (
DEFINE DATA EMPNO
PLSDNAME (EMPNO)
USAGE (PASS)
LANGUAGE (IBMVSCOBOLII)
(
01 EMP-NO PIC X(6).
);
DEFINE CALL DB2IMAIN
PKGCALL (PGADB2I_MAIN)
PARMS ( (EMPNO IN ),
(EMPREC OUT) );
DEFINE TRANSACTION DB2I
CALL ( DB2IMAIN,
DB2IDIAG )
SIDEPROFILE(CICSPROD)
TPNAME(DB2I)
LOGMODE(ORAPLU62)
SYNCLEVEL(0)
NLS_LANGUAGE("AMERICAN_AMERICA.WE8EBCDIC37C");
GENERATE DB2I
PKGNAME(PGADB2I)
OUTFILE("pgadb2i"););
No non-PGAU commands, such as ORACLE or SQL, can be placed inside the parentheses delimiting the group.
A PGAU script can contain multiple GROUPs. Each GROUP can be interspersed with SQL commands, such as COMMIT or SELECT or with PGAU commands, such as GENERATE or REPORT.
The first failing PGAU statement within the group causes a savepoint ROLLBACK to conditions at the beginning of the group. All subsequent commands within the group are flushed and not examined. PGAU execution resumes with the statement following the group. If that statement is a COMMIT, all PG DD changes made before the failing group are committed.
You must place ";" at the end of the command.
Use this command to execute an operating system command without exiting PGAU.
The HOST command has the syntax:
HOST host_command
host_command is any valid operating system command.
HOST vi log.out HOST ls -la HOST pwd
Using the HOST command starts a new command shell under which to execute the specified operating system command. This means that any environment changes caused by the executed command affect only the new command shell started by PGAU, and not the command shell under which PGAU itself is executing. For example, a "cd" command executed by the HOST command does not change the current directory in the PGAU execution environment.
You do not need to place ";" at the end of the command.
Use this command to print the value of a variable defined with the VARIABLE command.
The PRINT command has the syntax:
PRINT varname
varname is a variable name which is defined by a variable command.
PRINT ename PRINT balance
You do not need to place ";" at the end of the command.
The existing data definition in the PG Data Dictionary is modified. PG DD column values for DATA#, FLD#, and POS remain the same for redefined data items. This permits existing CALL and DATA definitions to utilize the redefined data. REDEFINE does not create a different version of a data definition and the version number is not updated.
REDEFINE DATA dname [VERSion(datavers)] [PLSDNAME(plsdvar)] [FIELD(fname) [PLSFNAME(plsfvar)]] [USAGE({PASS|ASIS|SKIP})] [COMPOPTS ('options')] [REMOTE_LANGUAGE("nlsname")] [LOCAL_LANGUAGE("nlsname")] LANGUAGE(language) <(definition) | INFILE("filespec")>;
Table 2-6 describes the REDEFINE DATA parameters:
Table 2-6 REDEFINE DATA Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
is a mandatory parameter. It is the name of the data definition to be modified. |
|
|
is an optional parameter. It specifies which version of |
|
|
is an optional parameter. It is the name of the PL/SQL variable associated with the |
|
|
is an optional parameter. It is the name of a field or group within the |
|
|
is an optional parameter if |
|
|
is optional. If omitted, the last usage specified is retained. It specifies the way the TIP handles the data items when exchanged in calls with the remote transaction:
If specified, all affected fields are updated with the same The |
|
|
is optional. If omitted, the last options specified are retained. If specified as a null string ('') then the last options specified are removed. If a non-null value is specified, then the last options specified are all replaced with the new options. The only option currently supported is ' |
|
|
is an optional parameter. The default value is " |
|
|
is an optional parameter. The default value is initialized from the |
|
|
is a mandatory parameter if definition input is specified. It specifies the name of the programming language in the supplied definition. PGAU presently supports only COBOL. |
|
|
is mutually exclusive with the |
|
|
is mutually exclusive with the Note that " |
Refer to "Sample PGAU REDEFINE DATA Statements" in Appendix F for examples of REDEFINE commands.
Specification of either PLSDNAME, FIELD, or PLSFNAME allows redefinition of a single data item's names while the (definition) parameter redefines the named data item's content.
The presence of FIELD denotes only a single data field (single PG DD row uniquely identified by dname, fname, and version) is updated. The absence of FIELD denotes that multiple data fields (multiple PG DD rows identified by dname and version) are updated or replaced by the definition input.
REMOTE_LANGUAGE and LOCAL_LANGUAGE override the character sets used for conversion of any individual SBCS, DBCS, or MBCS character data field.
LANGUAGE (language) and (definition)|INFILE("filespec") are mandatory as a group. If data definitions are to be supplied, then a LANGUAGE parameter must be specified and then either the inline definition or INFILE must also be specified.
The presence of (definition) | INFILE("filespec") denotes that multiple data fields (those PG DD rows identified by dname and version) are updated or replaced by the definition input. Fewer, equal, or greater numbers of fields might result from the replacement.
INFILE("filespec") is a platform-specific designation of a disk file.
COMPOPTS ('TRUNC(BIN)') should be used only when the remote host transaction was compiled using COBOL with the TRUNC(BIN) compiler option specified. When this option is used, binary data items defined as PIC 9(4) or PIC S9(4) can actually contain values with 5 digits, and binary data items defined as PIC 9(9) or PIC S9(9) can actually contain values with 10 digits. Without COMPOPTS ('TRUNC(BIN)'), PGAU generates NUMBER(4,0) or NUMBER(9,0) fields for these data items, resulting in possible truncation of the values. When COMPOPTS ('TRUNC(BIN)') is specified, PGAU generates NUMBER(5,0) or NUMBER(10, 0) fields for these data items, avoiding any truncation of the values. Care must be taken when writing the client application to ensure that invalid values are not sent to the remote host transaction. For a PIC 9(4) the value must be within the range 0 to 32767, for a PIC S9(4) the value must be within the range -32767 to +32767, for a PIC 9(9) the value must be within the range 0 to 2,147,483,647, and for a PIC S9(9) the value must be within the range -2,147,483,647 to +2,147,483,647. COBOL always reserves the high-order bit of binary fields for a sign, so the value ranges for unsigned fields are limited to the absolute values of the value ranges for signed fields. For further information, refer to the appropriate IBM COBOL programming manuals.
Refer to "USAGE(PASS)" in Appendix D, "Datatype Conversions" for information about how PGAU converts COBOL statements.
You must place ";" at the end of the command.
Comments can either be introduced by the REM command or started with the two-character sequence /* and terminated with the two-character sequence */.
Use the REM command to start a Comment line.
The REM command has the syntax:
REM Comment
Comment is any strings.
REM This is a Comment....
You do not need to place ";" at the end of the command.
This command produces a report of selected data from the PG Data Dictionary. Selection criteria might determine that:
a single TRANSACTION, CALL, or DATA entity (with or without an explicit version) is reported, or
that all TRANSACTION, CALL, or DATA entities with a given name be reported or that all entities in the PG DD be reported, or
that all invalid TRANSACTIONs or CALLs and all unreferenced CALLs, or DATA entities be reported.
REPORT { { TRANSACTION tname | CALL cname | DATA dname } [VERSION(ver1...)]
| ALL { TRANSACTIONS [tname] | CALLS [cname] | DATA [dname] } }
[WITH { CALLS | DATA | DEBUG } ... ]
| ISOLATED;
Table 2-7 describes the REPORT parameter:
Table 2-7 REPORT Parameters Descriptions
| Parameter | Description |
|---|---|
|
|
Reports the PG DD contents for the latest or selected versions of the transaction |
|
|
Reports the PG DD contents for the latest or selected versions of the call |
|
|
Reports the PG DD contents for the latest or selected versions of the data |
|
|
Reports selected versions of the indicated entry and is mutually exclusive with |
|
|
Reports the PG DD contents for all existing versions of every transaction entry or optionally a specific transaction |
|
|
Reports the PG DD contents for all existing versions of every call entry or optionally a specific call |
|
|
Reports the PG DD contents for all existing versions of every data entry or optionally a specific data |
|
|
Reports call entries associated with the specified transactions. |
|
|
Reports data entries associated with the specified calls, and when specified for transactions, implies |
|
|
Reports PG DD column values for This report is useful with TIPs generated with PG DD Diagnostic references. Refer to the |
|
|
Mutually exclusive with all other parameters. All unreferenced |
Report output is to the terminal and can be spooled, saved, and printed.
Data reports are formatted according to their original compiler language, and preceded by a PGAU DEFINE DATA command which defines the data to the PG DD.
CALL and TRANSACTION reports are formatted as PGAU DEFINE CALL or TRANSACTION commands (also called "statements"), which effectively define the entry to the PG DD.
The following command reports the single most recent data definition specified by data name dname, or optionally, for those specific versions given.
REPORT DATA dname; REPORT DATA dname VERSION(version#1,version#2);
This command reports all data definitions specified by data name dname:
REPORT ALL DATA dname;
The following command reports the single most recent call definitions specified by call name cname, or optionally for those specific versions given.
REPORT CALL cname; REPORT CALL cname VERSION(version#1,version#2) WITH DATA;
This command reports all call definitions specified by call name cname:
REPORT ALL CALLS cname WITH DATA;
This command reports all call definitions in the PG DD:
REPORT ALL CALLS WITH DATA;
When WITH DATA is specified, all the data definitions associated with each selected call are also reported. The data definitions precede each corresponding selected call in the report output.
The following command reports the single most recent transaction definitions specified by transaction name tname, or optionally for those specific versions given.
REPORT TRANSACTION tname REPORT TRANSACTION tname VERSION(version#1,version#2) WITH DATA WITH CALLS;
This command reports all transaction definitions specified by transaction name tname:
REPORT ALL TRANSACTIONS tname WITH DATA WITH CALLS;
This command reports all transaction definitions in the PG DD:
REPORT ALL TRANSACTIONS WITH DATA WITH CALLS;
When WITH CALLS option is specified, all call definitions associated with each selected transaction are also reported (the call definitions precede each corresponding selected transaction in the report output).
When WITH DATA is specified, all the data definitions associated with each selected call are also reported (the data definitions precede each corresponding selected call in the report output).
For transaction reports, specification of WITH DATA implies specification of WITH CALL.
The following command reports any unreferenced CALL or DATA definitions. It also reports any TRANSACTION or CALL definitions that reference missing CALL or DATA definitions respectively.
REPORT ISOLATED;
The following command reports all definitions in the PG DD.
REPORT ALL;
Data definitions are reported, followed by their associated call definitions, followed by the associated transaction definition.
This sequence is repeated for every defined call and transaction in the PG DD.
You must place ";" at the end of the command.
Table 2-8 describes the SET parameters:
Table 2-8 SET Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
Sets the number of rows fetched at a time from the database. The default is |
|
|
Sets the column display width for |
|
|
Sets the column display width for |
|
|
Sets echoing of commands entered from command files to |
|
|
Sets the number of rows returned by a query. This is useful with ordered queries for finding a certain number of items in a category, the top ten items for example. It is also useful with unordered queries for finding the first |
|
|
Sets the column display width for |
|
|
Sets the maximum data size. It indicates the maximum data that can be received in a single fetch during a |
|
|
Sets the column display width for |
|
|
Sets debugging output from stored procedures that use |
|
|
Indicates whether execution of a command file should stop if an error occurs. Specifying |
|
|
Enables or disables terminal output for SQL commands. It is useful for preventing output to the terminal when spooling output to files. The default is |
|
|
Enables or disables display of parse, execute, and fetch times (both CPU and elapsed) for each executed SQL statement. The default is |
PGAU> set arraysize 30 PGAU> set CHARWIDTH
You do not need to place ";" at the end of the command.
Table 2-9 describes the SHOW parameters:
Table 2-9 SHOW Parameter Descriptions
| Parameters | Description |
|---|---|
|
|
Shows all valid |
|
|
Shows the number of rows fetched at a time from the database. |
|
|
Shows the column display width for |
|
|
Shows the column display width for |
|
|
Shows echoing of commands entered from command files to |
|
|
Shows the number of rows returned by a query. |
|
|
Shows the column display width for |
|
|
Shows the maximum data size. |
|
|
Shows the column display width for |
|
|
Shows debugging output from stored procedures that use |
|
|
Indicates whether execution of a command file should stop if an error occurs. |
|
|
Shows whether the terminal output for SQL commands is enabled or disabled. |
|
|
Shows whether display of parse, execute, and fetch times (both CPU and elapsed) for each executed SQL statement is enabled or disabled. |
|
|
Is the same as the |
Note that when you issue a SET command, there will be no output if it is successful. If you want to check whether your statement was executed successfully, issue a SHOW command like the following:
PGAU> show arraysize Arraysize 30 PGAU> show CHARWIDTH Charwidth 80 PGAU> show all Instance local Spool OFF Timing OFF Termout ON Echo OFF Stoponerror OFF Maxdata 20480 Arraysize 20 Fetchrows 100 Numwidth 10 Charwidth 80 Longwidth 80 Datewidth 9 ServerOutput OFF
You do not need to place ";" at the end of the command.
Use this command to specify a filename that captures PGAU output. All output is directed to the terminal unless TERMOUT is off.
The SPOOL command has the syntax:
SPOOL [filename|OFF]
If a simple filename is specified, with no periods, then .log is appended to the filename.
filename is where the output of your executed commands is placed.
SPOOL log.outfile SPOOL out SPOOL OFF
You do not need to place ";" at the end of the command.
Use this command to remove an occurrence of the CALL definition from PG DD.
UNDEFINE CALL cname [VERSion(callvers|ALL)];
Table 2-10 describes the UNDEFINE CALL parameters:
Table 2-10 UNDEFINE CALL Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
A mandatory parameter. It specifies the name associated with the item to be dropped; if no version is specified only the latest (highest numbered) version is removed. |
|
|
An optional parameter. It specifies which singular version of a definition is to be removed, or if |
Refer to "Sample PGAU UNDEFINE Statements" in Appendix F for examples of UNDEFINE CALL commands.
Removing definitions only prevents PL/SQL packages from being subsequently generated. TIPs can still be recreated if the .pkh and .pkb specification files exist and those previous TIPS can be invoked if they remain in the database of the Oracle database. Whether such TIPs execute successfully depends on whether the corresponding remote transaction programs are still active.
Remove a CALL definition only after all TRANSACTIONs which reference it are removed. No integrity checking is done.
You must place ";" at the end of the command.
Use this command to remove an occurrence of the DATA definition in the PG Data Dictionary.
UNDEFINE DATA dname [VERSion(datavers|ALL)];
Table 2-11 describes the UNDEFINE DATA parameters:
Table 2-11 UNDEFINE DATA Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
A mandatory parameter. It specifies the name associated with the item to be dropped. If no version is specified, only the latest (highest numbered) version is removed. |
|
|
An optional parameter. It specifies which singular version of a definition is to be removed, or if |
Refer to "Sample PGAU UNDEFINE Statements" in Appendix F for examples of UNDEFINE DATA commands.
Removing definitions only prevents PL/SQL packages (TIPs) from being subsequently generated. Previously generated TIPs can still be recreated if the .pkh and .pkb specification files remain in existence. Previously created TIPs can still be invoked if they remain in the database of the Oracle database. Whether such TIPs execute successfully depends on whether the corresponding remote transaction programs are still active.
Remove a DATA definition only after all CALLs and all TRANSACTIONs which reference it are removed. No integrity checking is done.
You must place ";" at the end of the command.
This command removes an occurrence of the TRANSACTION definition in the PG Data Dictionary.
UNDEFINE TRANSACTION tname [VERSion(tranvers|ALL)];
Table 2-12 describes the UNDEFINE TRANSACTION parameters:
Table 2-12 UNDEFINE TRANSACTION Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
Mandatory parameter. It specifies the name associated with the item to be dropped. If no version is specified, only the latest (highest numbered) version is removed. |
|
|
Optional parameter. It specifies which singular version of a definition is to be removed, or if |
Refer to "Sample PGAU UNDEFINE Statements" in Appendix F for examples of UNDEFINE TRANSACTION commands.
UNDEFINE TRANSACTION Usage Notes
Removing definitions only prevents PL/SQL packages from being subsequently generated. TIPs can still be recreated if the .pkh and .pkb specification files remain in existence. Previously created TIPs can be invoked if they remain in the database of the Oracle database. Whether such TIPs execute successfully depends on whether the corresponding remote transaction programs are still active.
A TRANSACTION definition can be removed at any time.
You must place ";" at the end of the command.
Use this command to declare a bind variable for use in the current session with the EXECUTE or PRINT command, or for use with a PL/SQL block.
The VARIABLE command has the syntax:
VARIABLE name type
Table 2-13 describes the VARIABLE parameters.
Table 2-13 VARIABLE Parameter Descriptions
| Parameter | Description |
|---|---|
|
|
Is a variable name. |
|
|
Is the variable datatype |
VARIABLE balance NUMBER VARIABLE emp_name VARCHAR2
You do not need to place ";" at the end of the command.