COMPILE - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

Defines (creates or replaces) an SQL stored procedure in the database using an SPL input file.

Syntax

-



where the following is true:

filename
Name of a file containing source text for creating SQL stored procedures on workstation-attached systems, or a DD name on mainframe-attached systems.

For workstation-attached systems, if the name of the file includes a comma, semicolon, or space character, enclose the entire file name in single or double quotation marks.

In the following example, myproc.spl is the file name:

.COMPILE FILE 'myproc.spl'

Also, when text follows a file name, that file name must be enclosed in quotation marks. For example:

.COMPILE FILE 'myproc.spl' WITH NOSPL
NOSPL
Specifies that the SQL stored procedure source text is not to be stored in the Teradata Database server.
SPL
Specifies that the SQL stored procedure source text is to be stored in the Teradata Database server. If no argument is specified in the “With” part of the command, BTEQ sets the source text storage option to SPL.
WITH
Required syntax to specify the SPL storing option.
xxx
Name of the z/OS JCL DD statement that defines the file from which SPL statements are read on mainframe-attached systems.
Specify the dot (.) at the beginning, or the semicolon (;) at the end, or both, in the COMPILE command. If the dot character is missing, BTEQ generates a warning message but executes the command.

Usage Notes

The COMPILE command is used only for SQL (internal) stored procedures. For differences between external and SQL stored procedures, see Creating and Using Stored Procedures.

CREATE PROCEDURE privilege is required for access to the database specified in the SQL stored procedure. For replacing or modifying an existing SQL stored procedure using the REPLACE PROCEDURE statement in the SQL stored procedure source text, DROP privilege are required on the particular SQL stored procedure or DROP PROCEDURE privilege on the database containing it.

The SQL stored procedure definition in the input file begins with the keywords CREATE or REPLACE PROCEDURE, followed by the SQL stored procedure body, and ends with a semicolon. The definition can also contain parameters, local variables, exception handlers and comments.

The following rules apply:
  1. Logon to Teradata Database is required before executing this command.
  2. The source text for an SQL stored procedure must be written in an input file. Do not issue the CREATE or REPLACE PROCEDURESQL statement at the BTEQ prompt.
  3. One file can contain source text for only one SQL stored procedure. It cannot contain any other BTEQ commands.
  4. The COMPILE command must be the last statement in an open transaction for an SQL stored procedure.

If there are no compilation errors, the entire SQL stored procedure source text is stored in the specified Teradata Database. It can be retrieved later using the SHOW PROCEDURE SQL statement.

For other details of the features and limitations of SPL, plus sample input files, refer to SQL Data Manipulation Language (B035-1146).

For Unicode sessions, a BOM is optional at the beginning of a UTF-8 or UTF-16 file, and the encoding of an SQL stored procedure file must match the encoding of the session character set.

SPL Attributes

Use these attributes to direct the Teradata Database server whether to store or not to store the SQL stored procedure source text.

Select SPL to store the SQL stored procedure source text in Teradata Database. This is the default option.

NOSPL specifies that the SQL stored procedure source text is not to be stored in Teradata Database. For such SQL stored procedures, the SHOW PROCEDURE SQL statement returns an error or failure.

Session Mode

The session mode (Teradata or ANSI) is stored with each SQL stored procedure created in the Teradata Database. This information is used during SQL stored procedure execution.

An SQL stored procedure created in a given mode can be executed only in the same mode. For example, an SQL stored procedure created in ANSI mode can be executed only in ANSI mode.

This rule enables a definition of an SQL stored procedure to be included with the transaction control statements that are specific to a given session mode, and to define defaults associated with a session mode.

Using the = Command to Repeat a COMPILE Request

Use the = command to repeat a COMPILE command request one time. The = command saves time and requires fewer keystrokes to initiate this functionality.

Unqualified Objects in the Source Text

If any database objects referenced in the SQL statements inside the SQL stored procedure source text are not qualified with a database name, it is assumed that the objects are in the current default database of the session which created the SQL stored procedure.

The unqualified objects are qualified before creating the SQL stored procedure database object.

COMPILE Command Errors

The COMPILE command results in a SUCCESS, OK, or FAILURE response from Teradata Database.

If a failure is reported (in either ANSI or Teradata mode) as a response to the COMPILE command, the entire transaction is rolled back.

Compilation Errors

SPL compilation errors and warnings are reported as part of a SUCCESS or OK response. They have no impact on an open transaction.

If there are compilation errors, the SQL stored procedure is not created or replaced.

If only compilation warnings occur, the SQL stored procedure is created or replaced as requested.

See Error Handling for details of the error response size and organization.

Refer to the Messages (B035-1096) manual for a complete list of the SPL compilation error and warning messages.

Aborting a Compilation

When aborting a request using the ABORT feature, the following rules apply:

  • For CREATE PROCEDURE, the SQL stored procedure is not defined in the database.
  • For REPLACE PROCEDURE, the original SQL stored procedure definition is retained.

Compatibility Across Platforms

An SQL stored procedure executable is created on the Teradata Database server. The executable code is compatible only with the operating environment of the server. Hence, an SQL stored procedure created on a Teradata Database platform, such as Solaris, and then restored from Solaris, cannot be executed on another Teradata Database server platform, such as Microsoft Windows, or stored to Microsoft Windows.

Example 1 – COMPILE

This example shows the correct usage of the COMPILE command:

.SET SESSION TRANS BTET
.LOGON server/user, password
BT;
SELECT date;
.COMPILE FILE spSample1
ET;

Example 2 – COMPILE

This example requires a COMMIT/ROLLBACK/ABORT after the COMPILE command to close the transaction.

SET SESSION TRANS ANSI;
LOGON server/user, password;
SELECT date;
.COMPILE FILE spSample1;
COMMIT;

Example 3 – COMPILE

This example is not valid because the COMPILE command is not the last request in an open transaction (it is followed by a SELECT SQL statement):

SET SESSION TRANS BTET;
LOGON server/user, password;
BT;
COMPILE FILE spSample1;
SELECT date;
ET;