2 Procedural Gateway Administration Utility

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:

Overview of PGAU

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 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 IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), and HP-UX Itanium or Oracle Database Gateway for APPC Installation and Configuration Guide for Microsoft Windows.

COMMIT/ROLLBACK Processing

The following sections provide information on COMMIT/ROLLBACK processing.

COMMIT 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.

ROLLBACK Processing

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.

Invoking PGAU

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 IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), and HP-UX Itanium.

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.

Definitions and Generation in PGAU

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.

Process to Define and Test a TIP

The general process for defining and testing a TIP for a given transaction is as follows:

  1. Define input and output using COBOL data definitions.

  2. Redefine the default datanames and PL/SQL variable names created by the above process (optional).

  3. Define PL/SQL FUNCTION calls to be generated as part of the PL/SQL package.

  4. Define a transaction that groups the above functions.

  5. Generate the TIP specifications from the previously stored TRANSACTION, CALL, and DATA definitions.

  6. Generate the TIP PL/SQL stored procedures.

  7. 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

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.

Definition Versioning

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.

Keywords

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 Commands

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.

CONNECT

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

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

Parameters

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.

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 tnsnames.ora is stored.

  • You do not need to place a semi colon (;) at the end of the command.

DEFINE CALL

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

Parameter Definition

CALL cname

is a mandatory parameter. It is the name of the call definition to be created.

PKGCALL (pcname)

is an optional parameter. It specifies the name of the PL/SQL package procedure or function by which the application might invoke the call. The default value, cname, is assumed if this operand is omitted, in which case cname must also be valid in PL/SQL syntax and unique within the transactions and TIPs referencing this call.

PARMS( (dname

{IN|OUT|IN OUT} [

VERSION(datavers)]), . . .)

is an optional parameter. It specifies a list of previously defined data input to and output from this PL/SQL function call, and the type of each parameter (input to the call, output from, or both). The order in which the parameters are specified determines the order in which they must appear in subsequent calls to the TIP from an application.

Each dname specifies a previously defined data item, and is mandatory. {IN | OUT | IN OUT} specifies the PL/SQL call mode of the parameter and indicates whether the dname data is sent, received, or both in the exchange with the remote transaction program. One must be chosen. VERS(datavers) is an optional specific version number of the dname data definition, if not the latest. If this operand is omitted, it is assumed that the call takes no parameters.

Examples

Refer to "Sample PGAU DEFINE CALL Statements" in Appendix F 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.

DEFINE DATA

Purpose

This command creates a new version of the data definition in the PG DD.

Syntax

DEFINE DATA dname 
   [PLSDNAME(plsdvar)]
   [USAGE({PASS|ASIS|SKIP})]
   [COMPOPTS ('options')]
   LANGUAGE(language) 
   {(definition)|INFILE("filespec")};

Parameters

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|

SKIP})

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

('options')

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

(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 filespec must be enclosed in double quotes.

Examples

Refer to "Sample PGAU DEFINE DATA Statements" in Appendix F 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.

DEFINE TRANSACTION

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")];

Parameters

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 cname in its left-to-right sequence is the seq# column in pga_trans_calls. For example:

CALL (cname1, cname2,cname3)

pga_trans_calls(seq#) = 1

2 3

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 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.

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.

  • For CICS, this must be the CICS Transaction ID and is 1 to 4 characters in length.

  • For IMS, this must be the IMS Transaction Name and is 1 to 8 characters in length.

  • For AS/400, this must be specified as "library/program" and cannot exceed 21 bytes.

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.

  • The IMS Transaction Name must be 1 to 8 characters in length.

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 SYNCLEVEL 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 Globalization Support name in the language_territory.charset format. It specifies the Globalization Support name in which the remote host data for all single-byte character set fields in the transaction are encoded.

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 Globalization Support name in the language_territory.charset format. It specifies the Globalization Support name in which the remote host data for all multi-byte character set fields in the transaction are encoded.

LOCAL_MBCS

("nlsname")

This is an optional parameter. The default value is "japanese_japan.jal6dbcs". It is an Globalization Support name in the language_territory.charset format. It specifies the Globalization Support name in which the local host data for all multi-byte character set fields in the transaction are encoded.

Examples

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.

DESCRIBE

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]

Parameters

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.

DISCONNECT

Purpose

Use this command to disconnect from an Oracle database.

Syntax

The DISCONNECT command has the following syntax:

DISCONNECT

Parameters

None

Examples

None

DISCONNECT Usage Notes

  • You do not need to place ";" at the end of the command.

EXECUTE

Purpose

Use this command to execute a one-line PL/SQL statement.

Syntax

The EXECUTE command has the following syntax:

EXECUTE pl/sql block

Parameters

pl/sql block is any valid pl/sql block. Refer to the Oracle Database PL/SQL Language 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

EXIT

Purpose

Use this command to terminate PGAU.

Syntax

The EXIT command has the syntax:

EXIT

Parameters

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.

GENERATE

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]})])};

Parameters

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 backslash (\) for Microsoft Windows and a slash (/) for UNIX based systems.

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 backslash (\) for Microsoft Windows and a slash (/) for UNIX based systems.

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 specpath or else the current directory. The file id defaults to specname, if specified, or else pname, if specified, or else pgau, and always has an extension of .doc.

Refer to the "GENERATE Usage Notes:" for more examples, and Appendix E, "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.

  • SE - Subroutine Entry/Exit

Messages are written tracing subroutine name and arguments upon entry, and subroutine name and conditions at exit.

  • IT - Initialization/Termination

Messages are written tracing PGAU initialization and termination functions.

  • QM - Queue Management

Messages are written tracing control block allocation, queuing, searching, dequeuing, and deletion.

  • IO - Input/Output

Messages are written tracing input, output, and control operations for .dat input files and .wrk and package output files.

 

  • DD - PG DD Definitions

Messages are written tracing the loading of transaction, call, data parameter, field, attribute, environment and compiler information from the PG DD.

  • OC - Oracle Calls

Messages are written tracing the Oracle UPI call results for SQL statement processing and SELECTs from the PG DD.

  • TG - TIP Generation

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.

  • DC - Data Conversion

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:

  • NUMBER_TO_RAW

  • RAW_TO_NUMBER

  • MAKE_NUMBER_TO_RAW_FORMAT

  • MAKE_RAW_TO_NUMBER_FORMAT

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, "Troubleshooting".

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.

  • DR - Dictionary Reference

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 F for examples of GENERATE commands.

GENERATE Usage Notes:

  • 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.

GROUP

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);

Parameters

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.

HOST

Purpose

Use this command to execute an operating system command without exiting PGAU.

Syntax

The HOST command has the syntax:

HOST host_command

Parameters

host_command is any valid operating system 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.

PRINT

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

Parameters

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.

REDEFINE DATA

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")>;

Parameters

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

|SKIP})

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:

  • 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.

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 Globalization Support name in the language_territory.charset format. It specifies the Globalization Support 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.

LOCAL_LANGUAGE ("nlsname")

is an optional parameter. The default value is initialized from the LANGUAGE variable of the local Oracle database when the TIP executes. It is an Globalization Support name in the language_territory.charset format. It specifies the Globalization Support 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.

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 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. 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 "filespec" must be enclosed in double quotes.

Examples

Refer to "Sample PGAU REDEFINE DATA Statements" in Appendix F 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.

REM

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

Parameters

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.

REPORT

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;

Parameters

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.

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.

SET

Parameters

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.

SHOW

Parameters

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.

SPOOL

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]

Parameters

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.

Examples

SPOOL log.outfile  
SPOOL out
SPOOL OFF

SPOOL Usage Notes

  • You do not need to place ";" at the end of the command.

UNDEFINE CALL

Purpose

Use this command to remove an occurrence of the CALL definition from PG DD.

Syntax

UNDEFINE CALL cname        [VERSion(callvers|ALL)];

Parameters

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|

callvers|

transvers|ALL})

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 F 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 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.

UNDEFINE DATA

Purpose

Use this command to remove an occurrence of the DATA definition in the PG Data Dictionary.

Syntax

UNDEFINE DATA dname        [VERSion(datavers|ALL)];

Parameters

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|

callvers|

transvers|ALL})

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 F 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 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.

UNDEFINE TRANSACTION

Purpose

This command removes an occurrence of the TRANSACTION definition in the PG Data Dictionary.

Syntax

UNDEFINE TRANSACTION tname [VERSion(tranvers|ALL)];

Parameters

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 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.

VARIABLE

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

Parameters

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.