Compiling a Procedure Teradata

The first requirement for using SPL is to create a procedure. The procedure probably contains SQL, but more importantly it must contain procedural commands that determine when or if the SQL is used. If the only requirement were SQL, the capability of a macro is easier to use.

All SPL must be compiled with the SQL in order to create a procedure. The ODBC is written to do this automatically. Therefore, a CREATE PROCEDURE statement can be entered and executed directly within Queryman. However, when using BTEQ, DMTEQ or TeraTalk, a compile must be requested.

The basic syntax to do a compile follows:

If you are familiar with BTEQ, the format of this command should look familiar. It uses dot (.) commands. When the dot is used, the semi-colon is optional. The COMPILE indicates that a stored procedure is to be created using a compile operation.

To use BTEQ, a host file must be identified that contains the SPL to compile,. This is accomplished using any of these: FILE, DD or DDNAME. The DD and DDNAME are for IBM mainframe usage and indicate which DD statement in the JCL (Job Control Language) statement contains the name and disposition of the CREATE PROCEDURE file. For all other computer systems, FILE is used to provide the name of the file. In other words, the CREATE PROCEDURE cannot be typed directly into BTEQ. It must be read from a disk on the computer running BTEQ.

After the WITH, notice that SPL and NOPRINT are the default values fo the compile process. This means that the SPL source text will be stored with the compiled code for the HELP command and no print statements are included with the compiled code to assist in debugging a procedure.

Either of these settings may be over-ridden using the WITH option. The NOSPL option indicates to not store the source text statements. The PRINT option requests that any PRINT statements coded be used to help with debugging new procedures.

When using the ODBC and Queryman to create and compile the SPL, these options may still be used. However, they are set differently.

It will use these options in the setup file:

  • ProcedureWithPrintStmt
  • ProcedureWithSPLSource

These are the setup files for both UNIX and Windows:

  • UNIX: defined in the ODBC.INI file
  • Windows: defined in ODBC Setup Options

Temporary Directory Usage

The storage of all stored procedure specific, intermediate files are created in the designated directory for the server operating system environment on which the stored procedure is created. The files are called:

  • /tmp/TDSPTemp in UNIX MP-RAS
  • <TDBMS Installation Directory Name>\ DAT\TDConfig\TDSPTemp in NT and Windows 2000

The directory is created during the Teradata start-up, if it does not exist. If it is already there, any leftover temporary files are cleaned up at that time.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics