15.10 - SQL Procedures - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

SQL procedures are database objects executed on the Teradata Database. Typically, SQL procedures consist of:

  • a procedure name
  • input and output parameters
  • procedure body. For more information see SQL Stored Procedures and Embedded SQL.
  • For each SQL procedure, the database includes an SQL procedure table that contains the SQL procedure body you write and the corresponding compiled object code. Data dictionary tables contain procedure parameters and attributes.

    The Transient Journal (TJ) record holds the host request number for a procedure, in addition to the DBS request number, to return a correct response to the query status for a request after DBS restarts.

    Corresponding Tables and Logging Rules

    SQL procedure object information is stored in:

  • DBC.TVM
  • DBC.TVFields
  • DBC.AccessRights
  • DBC.AccLogRuleTbl
  • The SPObjectCodeRows column in the DBC.TVM table references information on the status of the SQL procedure. The value of this column indicates the following SQL procedure creation-time attributes:

  • Session mode
  • Platform type
  • Print option
  • SQL procedure text storage option
  • Version number
  • Warning option
  • Hardware architecture
  • The SPParameterType column in the DBC.TVFields table contains information about the SQL procedure parameters. Parameter types for this column include IN, INOUT, or OUT.

    The following table lists the privileges stored in the AccessRights table and their corresponding logging rules stored in the AccLogRuleTbl table.

     

    Privilege

    Logging Rule

    CREATE OWNER PROCEDURE

    AcrCreOwnerProcedure

    CREATE PROCEDURE

    AcrCreateProcedure

    ALTER PROCEDURE

    AcrAlterProcedure

    EXECUTE PROCEDURE

    AcrExecuteProcedure

    DROP PROCEDURE

    AcrDropProcedure

     

    For more information about ...

    See ...

    tables affected by SQL procedures

    Chapter 3: “Views Reference.”

    usage of SQL procedures

    SQL External Routine Programming.