Oracle Procedural Gateway® for APPC User's Guide 10g Release 2 (10.2) for UNIX Part Number B16210-01 |
|
|
View PDF |
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 F, "Tip Internals" and, depending on 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 mandatory NLS_LANGUAGE and Side Profile Settings. Specify the appropriate ASCII character set in the DEFINE TRANSACTION command.This is because 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 Oracle NLS 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 Data Dictionary tables must already have been created. Refer to the gateway configuration chapters pertinent to your communications protocol in the Oracle Procedural Gateway for APPC Installation and Configuration Guide.
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 Integrating Server should already be set up. If it is not, refer to the chapter on configuring your Procedural Gateway for APPC, in the Oracle Procedural Gateway for APPC Installation and Configuration Guide.
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. For a list of supported languages and the syntax for the LANGUAGE setting, refer to Appendix E, "National Language Support".
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:
$ pgau @command_file $ pgau command=@command_file $ pgau command="@command_file"
The default extension is .sql
. Use the last form if the command file name contains non-alphanumeric characters.
To perform PG DD maintenance and PL/SQL package generation, you must connect to the Oracle Integrating Server 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, "Procedural Gateway for APPC Data Dictionary" and the pgddcr8.sql
file in the $ORACLE_HOME/pg4appc/admin
directory 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 enables 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.
Purpose
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:
Syntax
CONNECT [username|username/password|username@connect-string|username/password@connect-string
username/password
is the username and password used to connect to PGAU,
and
connect-string
specifies the service name of the remote database.
Refer to the Oracle Net Services Administrator's Guide for more information about specifying remote databases.
Examples
CONNECT CONNECT SCOTT/TIGER CONNECT SCOTT@OTHERSYS
CONNECT Usage Notes
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 tnsname
is stored.
You do not need to place a semi colon (;) at the end of the command.
Purpose
This command creates a new version of the PL/SQL call definition in the PG Data Dictionary.
Syntax
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
Examples
Refer to "Sample PGAU DEFINE CALL Statements" in Appendix G for examples of DEFINE CALL commands.
DEFINE CALL Usage Notes
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.
Purpose
This command creates a new version of the data definition in the PG Data Dictionary.
Syntax
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 |
---|---|
DATA dname |
is a mandatory parameter. It is the name of the data definition to be created. |
PLSDNAME ( plsdvar) |
is an optional parameter. It is the name of the PL/SQL variable associated with dname . It becomes the name of a PL/SQL variable if the dname item is atomic data, or a PL/SQL record variable if the dname item is aggregate data (such as a record or structure), when the TIP is generated. |
USAGE({PASS|ASIS|
|
is an optional parameter. It specifies the way the TIP handles the data items when exchanged in calls with the remote transaction.
PASS indicates that the item should be translated and exchanged with the transaction. ASIS indicates the item is binary and, though exchanged, should not be translated. SKIP indicates the item should be deleted from all exchanges. The default value, PASS, is assumed if this parameter is omitted. The USAGE(NULL) keyword on DEFINE or REDEFINE DATA PGAU statements is not supported. |
COMPOPTS
|
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 'TRUNC(BIN)'. Note that the options must be enclosed in apostrophes (') or quotes ("). TRUNC(BIN) is a COBOL option that affects the way halfword and fullword binary values are handled.
Refer to "DEFINE DATA Usage Notes" for further information on this option. |
LANGUAGE
|
is a mandatory parameter. It specifies the name of the programming language in the supplied definition. PGAU presently supports only COBOL. |
(definition) |
is mutually exclusive with the INFILE parameter. It is an inline description of the data. The description must be provided in COBOL syntax, as indicated above. This inline description must begin with an opening parenthesis and end with a closing parenthesis. The opening parenthesis must be the last non-blank character on the line and the COBOL data definition must start on a new line, following the standard COBOL rules for column usage and continuations. The closing parenthesis and terminating semicolon must be on a separate line following the last line of the COBOL data definition. In COBOL, the specification is a COBOL data item or structure, defined in accordance with COBOL. Margins are assumed to be standard, and explicit or implicit continuation is supported. Datanames containing invalid characters (for example, "-") for PL/SQL use are translated to their closest equivalent and truncated as required. |
INFILE ( "filespec") |
is mutually exclusive with the (definition ) parameter. It indicates that the definition is to be read from the user disk file described by filespec, instead of an inline definition described by (definition) .
Note that |
Examples
Refer to "Sample PGAU DEFINE DATA Statements" in Appendix G for examples of DEFINE DATA commands.
DEFINE DATA Usage Notes
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.
Purpose
This command creates a new version of the transaction definition in the PG Data Dictionary.
Syntax
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 |
---|---|
TRANSACTION tname |
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 GENERATE statement, the transaction name specified here will become the package name, by default. In that case, the tname must be unique and must be in valid PL/SQL syntax within the database containing the PL/SQL packages. |
CALL( cname [VERS( callvers)], ...) |
A mandatory parameter. It specifies a list of previously defined calls (created with DEFINE CALL) which, taken together, comprise this transaction. The order in which the calls are specified here determines the order in which they are created by GENERATE, but not necessarily the order in which they might be called by an application. VERS(callvers) is an optional specific version number of the call definition, if not the latest.
The relative position of each
|
ENVIRONMENT ( name) |
Specifies the name of the host environment for this transaction, for example, "IBM370". If this parameter is omitted, IBM370 is assumed. IBM370 is the only environment supported by this version of PGAU. |
SIDEPROFILE ( name) |
This parameter is optional for a gateway using SNA, but if omitted, the user must specify the parameters for LUNAME, TPNAME, and LOGMODE. It specifies the name of an SNA Side Information Profile which directs the APPC connection to the transaction manager. This name can be 1 to 8 characters in length. 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 mandatory for a gateway using the TCP/IP connection. It has no comparable SNA meaning. You need to run the pg4tcpmap tool to map this name to the hostname, port number, subsystem ID and any other desired attribute of IMS Connect. This name represents a group of IMS transactions with similar IMS Connect attributes. You can reuse 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. |
LUNAME (name) |
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, 3 to 17 characters in length, or an LU alias 1 to 8 characters in length (when the SNA software on your gateway system supports LU aliases). 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. |
TPNAME ( name) |
This parameter is optional on a gateway using SNA: Overrides the TPNAME within the Side Profile, if the Side profile was specified. It specifies the partner Transaction Program name to be invoked.
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.
|
LOGMODE( name) |
This parameter is optional on a gateway using SNA: Overrides the LOGMODE within the Side Information Profile, if the Side Information Profile was specified. It specifies the name of a VTAM logmode table entry to be used to communicate with this transaction, and is 1-8 characters in length.
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. |
SYNCLEVEL (0|1) |
This parameter is optional on a gateway using SNA: It specifies the APPC sync level of this transaction ('0' or '1'). The default value of 0 is assumed if this operand is omitted, indicating the remote transaction program does not support synchronization. A value of '1' indicates that CONFIRM is supported.
On a gateway using TCP/IP: The default of this parameter is '0', which is the only accepted value. |
NLS_LANGUAGE ("nlsname") |
This is an optional parameter. The default value is "american_america.we8ebcdic37c". It is an Oracle NLS name in the language_territory.charset format. It specifies the Oracle NLS name in which the remote host data for all single-byte character set fields in the transaction are encoded. Refer to Appendix E, "National Language Support" for more information.
Note that if you are using TCP/IP, make sure that you set this parameter to "american_america.us7ascii". |
REMOTE_MBCS ("nlsname") |
This is an optional parameter. The default value is "japanese_japan.jal6dbcs". It is an Oracle NLS name in the language_territory.charset format. It specifies the Oracle NLS name in which the remote host data for all multi-byte character set fields in the transaction are encoded.
Refer to Appendix E, "National Language Support" for more information. |
LOCAL_MBCS
|
This is an optional parameter. The default value is "japanese_japan.jal6dbcs". It is an Oracle NLS name in the language_territory.charset format. It specifies the Oracle NLS name in which the local host data for all multi-byte character set fields in the transaction are encoded.
Refer to Appendix E, "National Language Support" for more information. |
Examples
Refer to "Sample PGAU DEFINE TRANSACTION Statement" in Appendix G for examples of DEFINE TRANSACTIONS commands.
DEFINE TRANSACTION Usage Notes:
NLS_LANGUAGE and the Oracle Integrating Server'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.
Purpose
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.
Syntax
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 |
---|---|
table |
is the tablename |
view |
is the viewname |
proc | is the procedurename |
Examples
DESCRIBE PROCEDURE SCOTT.ADDEMP DESCRIBE SYS.DUAL DESCRIBE TABLE SCOTT.PERSONNEL DESCRIBE VIEW SCOTT.PVIEW
DESCRIBE Usage Notes
You do not need to place ";" at the end of the command.
Purpose
Use this command to disconnect from an Oracle database.
Syntax
The DISCONNECT command has the following syntax:
DISCONNECT
None
Examples
None
DISCONNECT Usage Notes
You do not need to place ";" at the end of the command.
Purpose
Use this command to execute a one-line PL/SQL statement.
Syntax
The EXECUTE command has the following syntax:
EXECUTE pl/sql block
pl/sql block
is any valid pl/sql block. Refer to the PL/SQL User's Guide and Reference for more information.
Examples
EXECUTE :balance := get_balance(333)
EXECUTE Usage Notes
You do not need to place ";" at the end of the command
Purpose
Use this command to terminate PGAU.
Syntax
The EXIT command has the syntax:
EXIT
None
Examples
None
EXIT Usage Notes
You do not need to place ";" at the end of the command.
The "quit" command is not a valid statement in PGAU.
Purpose
A PL/SQL package is built and written to the indicated output files. The PG Data Dictionary is not updated by this command.
Syntax
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 |
---|---|
tname |
is a mandatory parameter. It is the transaction name defined in a DEFINE TRANSACTION statement. |
VERSion( transvers) |
is an optional parameter. It specifies which transaction definition is to be used. The VERsion parameter defaults to highest numbered transaction if not specified. |
PKGNAME( pname) |
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. |
PGANODE ( dblink_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 dblink_name . |
OUTFILE |
is an optional parameter. If this parameter is specified, specname must also be specified. |
specpath |
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 forward slash (/). |
specname |
is the filename of the TIP specification and the TIP content documentation. It defaults to pname , if specified, or else pgau . |
spectype |
is the optional file extension of the TIP specification and defaults to pkh . |
bodypath |
is the optional directory path of the TIP body. It defaults to specpath , if specified, or else the current directory. The value must end with a forward slash (/). |
bodyname |
is the optional file name of the TIP body. It defaults to specname , if specified, or else pname , if specified, or else pgau . If bodyname defaults to specname, the leftmost period of specname is used to extract bodyname when specname contains multiple qualifiers. |
bodytype |
is the optional file extension of the TIP body and defaults to pkb .
The TIP Content output path defaults to Refer to the "GENERATE Usage Notes:" for more examples, and Appendix F, "Tip Internals" for more information. |
DIAGNOSE |
is an optional parameter with two options, TRACE and PKGEX. |
TRACE |
specifies that an internal trace of the execution of PGAU is written to output file pgau.trc in the user's current directory.
TRACE suboptions are delimited by commas. 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 SELECTs from the PG DD.
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. |
PKGEX |
causes additional TIP execution time diagnostic logic to be included within the generated PL/SQL package.
PKGEX suboptions are delimited by commas.
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 UTL_PG data conversion functions:
The additional logic checks for the existence of warnings and, if present, causes them to be displayed using DBMS_OUTPUT calls. 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 PKGEX(DC) enabled might provide additional information. Note: A runtime switch is also required to execute the warning logic. PKGEX(DC) only causes the warning logic to be included in the TIP. Refer to "Controlling TIP Runtime Conversion Warnings" in Chapter 8, "Problem Determination". Additional messages are written to a named pipe for tracing the data conversion steps performed by the TIP as it executes. |
This option causes only 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, "Problem Determination" 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. |
Examples
Refer to "Sample PGAU GENERATE Statement" in Appendix G for examples of GENERATE commands.
The following list describes the TIP output file ids resulting from various combinations of GENERATE parameters:
GENERATE tran GENERATE tran OUTFILE("dirpath/") dirpath/pgau.pkh - TIP specification dirpath/pgau.doc - TIP Content dirpath/pgau.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("dirpath/") dirpath/tipname.pkh - TIP specification dirpath/tipname.doc - TIP Content dirpath/tipname.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("sdirpath/", "bdir/path/") sdirpath/pgau.pkh - TIP specification sdirpath/pgau.doc - TIP Content bdir/path/pgau.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("spath/sname","bpath/bname") spath/sname.pkh - TIP specification spath/sname.doc - TIP Content bpath/bname.pkb - TIP body GENERATE tran PKGNAME tipname tipname.pkh - TIP specification tipname.doc - TIP Content tipname.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("fileid") fileid.pkh - TIP specification fileid.doc - TIP Content fileid.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("specname","bodyname") specname.pkh - TIP specification specname.doc - TIP Content bodyname.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("specname.spectype") specname.spectype - TIP specification specname.doc - TIP Content specname.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("sname.stype","bname") sname.stype - TIP specification sname.doc - TIP Content bname.pkb - TIP body GENERATE tran PKGNAME tipname OUTFILE("sname.stype","bname.btype") sname.stype - TIP specification sname.doc - TIP Content bname.btype - TIP body GENERATE tran PKGNAME tipname OUTFILE("spath/sname.sext","bpath/bname.bext") spath/sname.sext - TIP specification spath/sname.doc - TIP Content bpath/bname.bext - TIP body
GENERATE Usage Notes:
All PGAU GENERATE trace messages are designated PGU-39nnn. Refer to the $ORACLE_HOME/pg4appc/mesg/pguus.msg
file for more information about 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.
Purpose
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.
Syntax
GROUP (pgaustmt1; pgaustmt2; ... pgaustmtN);
pgaustmtN:
is a PGAU DEFINE, REDEFINE, or UNDEFINE statement
Examples
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"););
GROUP Usage Notes:
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.
Purpose
Use this command to execute an operating system command without exiting PGAU.
Syntax
The HOST command has the syntax:
HOST host_command
host_command
is any valid UNIX command.
Examples
HOST vi log.out HOST ls -la HOST pwd
HOST Usage Notes
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.
Purpose
Use this command to print the value of a variable defined with the VARIABLE command.
Syntax
The PRINT command has the syntax:
PRINT varname
varname
is a variable name which is defined by a variable command.
Examples
PRINT ename PRINT balance
PRINT Usage Notes
You do not need to place ";" at the end of the command.
Purpose
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.
Syntax
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 |
---|---|
DATA dname |
is a mandatory parameter. It is the name of the data definition to be modified. |
VERSion( datavers) |
is an optional parameter. It specifies which version of dname is to be modified, and if specified, the updated dname information retains the same version number; a new version is not created. It defaults to the highest version if omitted. |
PLSDNAME (plsdvar) |
is an optional parameter. It is the name of the PL/SQL variable associated with the dname above. It becomes the name of a PL/SQL variable if the dname item is atomic data, or a PL/SQL record variable if the dname item is aggregate data (such as a record or structure), when the TIP is generated. This name replaces any plsdvar name previously specified by DEFINE DATA into pga_data(plsdvar) of the PG DD. |
FIELD (fname) |
is an optional parameter. It is the name of a field or group within the dname item, if aggregate data is being redefined (such as changing a field within a record). |
PLSFNAME (plsfvar) |
is an optional parameter if FIELD is specified. It is the name of the PL/SQL variable associated with the fname above. It becomes the name of a PL/SQL field variable within a PL/SQL record variable when the TIP is generated. This name replaces any plsfvar name previously specified by REDEFINE DATA into pga_data(plsfvar ) of the PG DD. |
USAGE({PASS|ASIS
|
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 USAGE value. (Refer to the notes pertaining to single or multiple field redefinition, under FIELD). The USAGE(NULL) keyword on DEFINE or REDEFINE DATA PGAU statements is not supported. |
COMPOPTS ('options') |
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 'TRUNC(BIN)'. Note that the options must be enclosed in apostrophes (') or quotes ("). TRUNC(BIN) is a COBOL option that affects the way halfword and fullword binary values are handled. Refer to "REDEFINE DATA Usage Notes:" for further information on this option. |
REMOTE_LANGUAGE ( "nlsname" ) |
is an optional parameter. The default value is "american_america.we8ebcdic37c" or as overridden by the NLS_LANGUAGE parameter of DEFINE TRANSACTION. It is an Oracle NLS name in the language_territory.charset format. It specifies the Oracle NLS name in which the remote host data for the specific character field being redefined is encoded. The field can be single byte or multi-byte character data. Refer to Appendix E, "National Language Support" for more information. |
LOCAL_LANGUAGE ("nlsname") |
is an optional parameter. The default value is initialized from the LANGUAGE variable of the local Oracle server when the TIP executes. It is an Oracle NLS name in the language_territory.charset format. It specifies the Oracle NLS name in which the local Oracle data for the specific character field being redefined is encoded. The field can be single byte or multi-byte character data. Refer to Appendix E, "National Language Support" for more information. |
LANGUAGE ("language" ) |
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 IBMVSCOBOL II. |
(definition) |
is mutually exclusive with the INFILE parameter. It is an inline description of the data. The description must be provided in COBOL syntax. This inline description must begin with an opening parenthesis and end with a closing parenthesis. The opening parenthesis must be the last non-blank character on the line and the COBOL data definition must start on a new line, following the standard COBOL rules for column usage and continuations. The closing parenthesis and terminating semicolon must be on a separate line following the last line of the COBOL data definition. If in COBOL, the specification is a COBOL data item or structure, defined according to the rules for COBOL. Margins are assumed to be standard, explicit or implicit continuation is supported. Datanames containing invalid characters (for example, "-") for PL/SQL use are translated to their closest equivalent and truncated as required. |
INFILE ( "filespec") |
is mutually exclusive with the (definition) parameter. It indicates that the definition is to be read from the operating system file described by filespec, instead of an inline definition described by (definition) .
Note that " |
Examples
Refer to "Sample PGAU REDEFINE DATA Statements" in Appendix G for examples of REDEFINE commands.
REDEFINE DATA Usage Notes:
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.
Purpose
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.
Syntax
The REM command has the syntax:
REM Comment
Comment
is any strings.
Examples
REM This is a Comment....
REM Usage Notes
You do not need to place ";" at the end of the command.
Purpose
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.
Syntax
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 |
---|---|
TRANSACTION tname |
Reports the PG DD contents for the latest or selected versions of the transaction tname . |
CALL cname |
Reports the PG DD contents for the latest or selected versions of the call cname . |
DATA dname |
Reports the PG DD contents for the latest or selected versions of the data dname . |
VERSION( ver1, [ ver2 ...]) |
Reports selected versions of the indicated entry and is mutually exclusive with ALL. |
ALL TRANSACTIONS [tname] |
Reports the PG DD contents for all existing versions of every transaction entry or optionally a specific transaction tname , and is mutually exclusive with TRANSACTION. |
ALL CALLS [cname] |
Reports the PG DD contents for all existing versions of every call entry or optionally a specific call cname , and is mutually exclusive with CALL. |
ALL DATA [dname] |
Reports the PG DD contents for all existing versions of every data entry or optionally a specific data dname , and is mutually exclusive with DATA. |
WITH CALLS |
Reports call entries associated with the specified transactions. |
WITH DATA |
Reports data entries associated with the specified calls, and when specified for transactions, implies WITH CALLS. |
WITH DEBUG |
Reports PG DD column values for tran#, call#, parm#, data#, and attr# as appropriate, depending on the type of items being reported.
This report is useful with TIPs generated with PG DD Diagnostic references. Refer to the GENERATE DIAGNOSE PGEX(OR) option for more information. |
ISOLATED |
Mutually exclusive with all other parameters. All unreferenced CALL and DATA entries are reported along with TRANSACTIONs that reference missing CALLs and DATA and CALLs that reference missing DATA. |
Examples
Refer to "Sample PGAU REPORT Output" in Appendix G for sample REPORT commands.
REPORT Usage Notes:
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 |
---|---|
ARRAYSIZE [n] | Sets the number of rows fetched at a time from the database. The default is 20. |
CHARWIDTH [n] | Sets the column display width for CHAR data. If entered with no argument, it returns the setting to 9, which is the default. |
DATEWIDTH | Sets the column display width for DATE data. If entered with no argument, it returns the setting to 9, which is the default. |
ECHO {ON|OFF} | Sets echoing of commands entered from command files to ON or OFF. The default is OFF. |
FETCHROWS [n] | 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 n records that satisfy a certain criteria. |
LONGWIDTH [n] | Sets the column display width for LONG data. If entered with no argument, it returns the setting to 80, which is the default. |
MAXDATA [n] | Sets the maximum data size. It indicates the maximum data that can be received in a single fetch during a SELECT command. The default is 20480 bytes (20K). |
NUMWIDTH [n] | Sets the column display width for NUMBER data. If entered with no argument, it returns the setting to 10, which is the default. |
SERVEROUTPUT {OFF|ON [SIZE n|n]} | Sets debugging output from stored procedures that use DBMS_OUTPUT PUT and PUT_LINE commands to ON or OFF. You can specify the size in bytes of the message buffer using SIZE n. The size specified is the total number of bytes of all messages sent that can be accumulated at one time. The minimum is 2000 bytes. If the buffer fills before calls to the get-message routines make room for additional message bytes, an error is returned to the program sending the message. SERVEROUTPUT with no parameters is the same as SERVEROUTPUT ON. |
STOPONERROR {ON|OFF} | Indicates whether execution of a command file should stop if an error occurs. Specifying OFF disables STOPONERROR. |
TERMOUT {ON|OFF} | 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 OFF, which disables terminal output. |
TIMING {ON|OFF} | Enables or disables display of parse, execute, and fetch times (both CPU and elapsed) for each executed SQL statement. The default is OFF, which disables the TIMING display. |
Examples
PGAU> set arraysize 30 PGAU> set CHARWIDTH
SET Usage Notes
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 |
---|---|
ALL | Shows all valid SET parameters |
ARRAYSIZE | Shows the number of rows fetched at a time from the database. |
CHARWIDTH | Shows the column display width for CHAR data. |
DATEWIDTH | Shows the column display width for DATE data. |
ECHO | Shows echoing of commands entered from command files to ON or OFF. |
FETCHROWS | Shows the number of rows returned by a query. |
LONGWIDTH | Shows the column display width for LONG data. |
MAXDATA | Shows the maximum data size. |
NUMWIDTH | Shows the column display width for NUMBER data. |
SERVEROUTPUT | Shows debugging output from stored procedures that use DBMS_OUTPUT PUT and PUT_LINE commands. |
STOPONERROR | Indicates whether execution of a command file should stop if an error occurs. |
TERMOUT | Shows whether the terminal output for SQL commands is enabled or disabled. |
TIMING | Shows whether display of parse, execute, and fetch times (both CPU and elapsed) for each executed SQL statement is enabled or disabled. |
VAR | Is the same as the PRINT command; in addition, it shows all variables and their datatypes. |
Examples
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
SHOW Usage Notes
You do not need to place ";" at the end of the command.
Purpose
Use this command to specify a filename that captures PGAU output. All output is directed to the terminal unless TERMOUT is off.
Syntax
The SPOOL command has the syntax:
SPOOL [filename|OFF]
If a simple file name is specified, with no periods, then .log
is appended to the filename.
filename
is where the output of your executed commands is placed.
Examples
SPOOL log.outfile SPOOL out SPOOL OFF
SPOOL Usage Notes
You do not need to place ";" at the end of the command.
Purpose
Use this command to remove an occurrence of the CALL definition from PG DD.
Syntax
UNDEFINE CALL cname [VERSion(callvers|ALL)];
Table 2-10 describes the UNDEFINE CALL parameters:
Table 2-10 UNDEFINE CALL Parameter Descriptions
Parameter | Description |
---|---|
CALL cname| |
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. |
VERSion( {datavers |
|
An optional parameter. It specifies which singular version of a definition is to be removed, or if ALL , then all definitions are removed, for the given definition named. The default of the highest numbered version of the named definition is assumed if VERSION is omitted. |
Examples
Refer to "Sample PGAU UNDEFINE Statements" in Appendix G for examples of UNDEFINE CALL commands.
UNDEFINE CALL Usage Notes:
Removing definitions only prevents PL/SQL packages from being subsequently generated. TIPs can still be re-created if the .pkh
and .pkb
specification files exist and those previous TIPS can be invoked if they remain in the database of the Oracle Integrating Server. 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.
Purpose
Use this command to remove an occurrence of the DATA definition in the PG Data Dictionary.
Syntax
UNDEFINE DATA dname [VERSion(datavers|ALL)];
Table 2-11 describes the UNDEFINE DATA parameters:
Table 2-11 UNDEFINE DATA Parameter Descriptions
Parameter | Description |
---|---|
DATA dname| |
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. |
VERSion( {datavers|
|
An optional parameter. It specifies which singular version of a definition is to be removed, or if ALL , then all definitions are removed, for the given definition named. The default of the highest numbered version of the named definition is assumed if VERSION is omitted. |
Examples
Refer to "Sample PGAU UNDEFINE Statements" in Appendix G for examples of UNDEFINE DATA commands.
UNDEFINE DATA Usage Notes
Removing definitions only prevents PL/SQL packages (TIPs) from being subsequently generated. Previously generated TIPs can still be re-created 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 Integrating Server. 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.
Purpose
This command removes an occurrence of the TRANSACTION definition in the PG Data Dictionary.
Syntax
UNDEFINE TRANSACTION tname [VERSion(tranvers|ALL)];
Table 2-12 describes the UNDEFINE TRANSACTION parameters:
Table 2-12 UNDEFINE TRANSACTION Parameter Descriptions
Parameter | Description |
---|---|
TRANSACTION tname} |
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. |
VERSion({ datavers|
callvers transvers|ALL |
Optional parameter. It specifies which singular version of a definition is to be removed, or if ALL , then all definitions are removed, for the given definition named. The default of the highest numbered version of the named definition is assumed if VERSION is omitted. |
Examples
Refer to "Sample PGAU UNDEFINE Statements" in Appendix G 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 re-created 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 Integrating Server. 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.
Purpose
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.
Syntax
The VARIABLE command has the syntax:
VARIABLE name type
Table 2-13 describes the VARIABLE parameters.
Table 2-13 VARIABLE Parameter Descriptions
Parameter | Description |
---|---|
name |
Is a variable name. |
type |
Is the variable datatype |
Examples
VARIABLE balance NUMBER VARIABLE emp_name VARCHAR2
VARIABLE Usage Notes
You do not need to place ";" at the end of the command.