Teradata

The External Stored Procedure enhancement to utilize SQL access is a powerful extension to Teradata Database 12.0. The addition of the Java language and enabling Result Sets for Stored Procedures also significantly enhances the Teradata Database capabilities.

In an increasingly global economy, companies must consolidate their operational and decision logic within their data warehouse along with their data to provide not only a consistent view of their data, but a consistent view of their business logic. Current trends and technology are widening the number of touch points that customers and suppliers use to access a company, such as call centers, mobile devices, and web access. To keep pace with these trends, consistent decision policies must be implemented within the data warehouse. To address these issues and more, Teradata Database 12.0 has been enhanced to allow SQL access within External Stored Procedures to use Java as an External Stored Procedure language and to return Result Sets from Stored Procedures.

By providing SQL access, a complete customization of logic interspersed with data can be utilized as customers add their own extensions to Teradata Database. Entire applications can become External Stored Procedures that underlie the various customer access mechanisms. Existing C/C++ or Java libraries may be used in conjunction with sophisticated queries and updates. The results of this customized logic and SQL can be then returned to the client application as multiple rows of data via Result Sets.

Application developers can now write extensions to the Teradata Database in the form of External Stored Procedures to not only provide specialized logic, but to access both data (DML) and database structures (DDL). Additionally, the potential to access external data via the web or the file system extends External Stored Procedures' usefulness significantly. These benefits are categorized as follows:

A business can now customize the Teradata Database to fit its exact business logic needs by combining the complete power of C/C++ or Java External Stored Procedures with full SQL statements. Industry-specific business logic can be encapsulated into these Stored Procedures providing: a single point of access for each and every client, a consistent application of business logic, and a single point of coding maintenance.

If SQL Stored Procedure logic cannot supply a specific function or is cumbersome to implement, users can now choose between the rich language of Java or the widely accepted C/C++. Users can also access external data or even external databases to make the decision logic complete. Both the logic and data access can now be written in a single language making development simpler.

Applications on the desktop or web can now access the exact same logic by having the logic stored within the database. This provides a single point of management for business logic. Highly complex logic can be kept in one place.

Proprietary business logic can be kept within the database. Also External Stored Procedures' SQL access can now execute within the user session the same as SQL Stored Procedures, making the access possible without additional user id and password requirements.

Stored procedures can provide specific and granular access to the system. For example, ten stored procedures may all update table X in some way. If a user needs to be able to update a particular column in a table, but never update any other columns, then that user is granted permission to execute the one procedure out of the ten that performs the required update.

Since External Stored Procedures execute within the Teradata Database environment, they can automatically become globally accessible to all users of the Teradata Database. This can mitigate the cost of distributing specialized software throughout the enterprise.

External Stored Procedures execute within the Teradata Database and access to the database using an optimal mechanism unavailable to client applications.

Result Sets permit Stored Procedures to return multiple result sets containing multiple rows of data to the client application calling the Stored Procedure. This elevates Stored Procedures to be more powerful than any select statement.

A Teradata Database External Stored Procedure is a custom procedure written in the C/C++ or Java Language that may operate on data stored in relational tables, database structures, or even on external data. This paper uses the following abbreviations: XSP refers to both C/C++ and Java External Stored Procedures, while CXSP refers specifically to C/C++ External Stored Procedures, and JXSP refers to Java External Stored Procedures.

Input to the XSP is accomplished via its argument list. The arguments can consist of literals and values supplied by a client. XSPs can access SQL data, perform inserts and updates, and submit DDL statements. They can return results either from OUT parameters in the argument list or as result sets.

As each type of stored procedure has been extended, the capabilities have blended. In general, the following guidelines can be used in deciding how to apply the three forms of external stored procedures:

Stored Procedure Language (SQL) provides a simple mechanism that permits SQL statements and flow control. They support basic error handling facilities, simple logic, and no complex data manipulation. Generally the SQL is static. SPL has been extended to return result sets.

CXSPs provide the highest performance and the highest complexity. The C/C++ Stored Procedures allow the most complete native data manipulation. CLI access is also complex. This form would be used for something like an every row trigger. The SQL can be dynamic, and result sets can be returned.

JXSPs provide a rich language that is easy to prototype. Performance is modestly less than CXSPs, but when compared to actual SQL statements, this performance difference has little overall impact. The SQL can be dynamic. Currently, Result Sets are not supported.

External Stored Procedures can execute SQL requests using the library or driver appropriate for the language. The C/C++ language uses a CLI library. Java uses the Teradata JDBC driver. These components are designed to have the advantage of using the same credentials as the user executing the XSP.

The SQL requests that are issued by the driver are the same messages that are sent by a client. These messages are routed through an internal DBS gateway to the DBS. They use the same session information so additional logon processing does not occur. The expanded data access clause on the CREATE PROCEDURE statement signals the SQL access mode and sets up the internal gateway.

The data access clause permits or denies the use of SQL via the server JDBC or CLI via a default session connection mechanism. There are four choices: NO SQL, MODIFIES SQL DATA, READS SQL DATA, and CONTAINS SQL. A NO SQL clause does not set up the session default connection, and the XSP is limited to data passed via parameters. Any attempt to access the driver default connection results in an exception. The XSP with MODIFIES SQL DATA can insert or select data from tables using the same session under which the JXSP call is executing and requires no logon and password. The READS SQL DATA clause signifies that SELECT statements may be done, but no data may be inserted or updated. A stored procedure may call another stored procedure, and the data access clause restrictions of the parent stored procedure apply to the child stored procedure. Finally, the CONTAINS SQL clause indicates that the external stored procedure can execute SQL control statements.

Proper source code management using a tool, such as Clearcase, SCCS, CSSC should be used when developing source code and should be considered as part planning the deployment of stored procedures. The version string provided by these tools to identify the build version can be stored in the Java JAR manifest file or be used to support a "Version" stored procedure to identify the currently installed version.

C/C++ External Stored Procedures are generally implemented by submitting the source language files as part of the CREATER PROCEDURE statement. The compile and distribution of the CXSP is handled by the database. In a typical scenario, a CXSP developer writes a C procedure, then installs and executes it via standard SQL. Coding a CXSP is identical to coding any other C/C++ application program that uses SQL. The only deviation is that while an application must provide logon credentials, a CXSP can assume the current session credentials.

The CREATE PROCEDURE statement requires the database to have CREATE EXTERNAL PROCEDURE permission. The statement specifies the language, data access clause, and external name clause.

The FIRSTCALL procedure in this example uses a language clause of CPP and data access clause of READS SQL DATA indicating to the system that data will only be read, but not modified. The external name clause includes the SP!CLI indicator, which signals the DBS to link this stored procedure with the CLI library. The CLI library package must be installed on all nodes of the database.

Linking against the CLI library provides access to the DBCHCL routine that provides logon/logoff, submits SQL, and fetches SQL data. To use the same logon credentials the CLI, DBCAREA data structure must be initialized to use the default connection mechanism. This is done by setting the create_default_connection field to 'Y'.

Java External Stored Procedures use source that is compiled externally to the DBS and kept in Java Archive (JAR) files. These JAR files are installed into the database using stored procedures in the SQLJ database. The installation distributes the JAR files to all nodes of the database. Once a JAR file is installed, a JXSP can be defined to use the Java class and methods within the JAR file. JXSPs are executed via a protected mode server separate from the database process. Parameters passed from the DBS are converted to their Java form, and return parameters are converted back to DBS form. JXSPs can also access SQL with the same session credentials using the JDBC driver.

There are five JAR handling statements. These XSPs reside in the SQLJ database and are used to manage Java JAR files following SQL standards. Execution rights to the SQLJ database and for Create External Procedure access in the current database are required to manage Java JAR files.

The INSTALL_JAR XSP has three parameters. The first parameter describes the location of the JAR file to be installed. This file can be located in one of two places: on the client's file system or on the server's file system. The first token of the parameter is either SJ (Server) or CJ (Client) denoting that file location. The next token provides the JAR name and includes a path. The JAR path can be absolute or relative. The second parameter is the name of the JAR object within the database and must match the Create Procedure statement for the JXSP. The third and final parameter will be used in the future to indicate that a deployment command file should be executed after the JAR is installed. For example, this could be used to create the associated procedures within the JAR.

The REPLACE_JAR is used to install a replacement JAR. This process checks that all existing JXSP methods exist in the replacement JAR. The behavior of the method may change, and new methods can be added, but existing methods and their exact parameters can not be changed unless the changed JXSP is first dropped. Unlike a REPLACE PROCEDURE statement, the JAR must exist before it can be replaced as dictated by the Java standard.

The REMOVE_JAR XSP must have all JXSPs defined against the JAR dropped before the JAR can be removed. The first parameter identifies the JAR to be dropped. The second parameter will be used in the future to indicate that a deployment command file should be executed to assist with removing the JAR.

The ALTER_PATH XSP is used to permit an association to be established between two or more JARs. This association lets a JXSP use methods defined in another JAR, such as a commercial library package. ALTER_PATH updates the DBC.JAR_JAR_USAGE table to show which JARs use other JARs. This information is used by the DBS Java Loader to lookup classes being loaded. It does not influence the System.getProperty ("java.class.path"). In the case where one JAR references another, and the other JAR also references the first JAR, a circular path exists. To drop a JAR in this situation requires that the classpath first be altered to remove the circular references. The command to delete all references is: call sqlj.alter_java_path('spsql','');

The purpose of the REDISTRIBUTE_JAR XSP is to redistribute an installed JAR file on all nodes of a database system. This procedure is used during copy, migrate, or system restore operations. The procedure signature is: SQLJ.REDISTRIBUTE_JAR ( JAR IN VARCHAR(64) )

The user must have the EXECUTE privilege on the procedure before it can be executed, as well as the CREATE EXTERNAL PROCEDURE privilege on the current database. The current user must be the owner of the JAR file. The JAR file to be redistributed is specified by the JAR parameter. The format of this parameter is identical to the SQLJ.INSTALL_JAR external stored procedure parameter. The JAR to be redistributed is retrieved from its internal table location, written out to the primary database node with an updated version number, and redistributed to all nodes of the system.

A JXSP is created by compiling a Java class and method and placing the resulting class in a JAR file. The JAR is registered and installed into the user's database using the SQLJ.INSTALL_JAR XSP. The JXSP is then defined using a CREATE PROCEDURE statement.

The JXSP Create Procedure statement specifies the language and parameter style to be JAVA and the data access clause. The external name clause for a Java stored procedure defines the JAR, class, and method name that the JXSP will use when called. Method names must be unique within the database where they are defined. During the Create Procedure statement, the external name clause is validated, and the JAR, class, method, and parameters are validated.