Running Unsupported Queries from a Stored Procedure

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

Running Unsupported Queries from a Stored Procedure

Stored Procedures

SQL Stored Procedures were added to Teradata around 2003 with the release of Teradata V2R5.1. Since then the capabilities of SQL Stored Procedure s has been expanded. However, there are still some queries that cannot be run directly from within a Stored Procedure.

Unsupported Queries

The Teradata 14 Stored Procedures manual has a list of DDL and DML statements that cannot be executed from within a Stored Procedure. Examples include:

    • Show (all forms)
    • Set Query_Band = … for session
    • Dynamic SQL that exceeds 32,000 characters in size (the maximum that DBC.sysexecsql can accept).

and many more. If you try to run these from within a stored procedure an error condition will be raised.

Recently, colleagues of mine found themselves in scenarios where they needed to execute the above queries from within a Stored Procedure. The reasons that they needed to do this are complicated and I won’t go into that here. All attempts to run the above queries from within a Stored Procedure (including DBC.sysexecsql) proved fruitless.

Their predicament got me wondering, can I run these “unsupported queries” (a.k.a. the naughty queries) within an External Stored Procedure (XSP) called from a Stored Procedure. My thinking is that External Stored Procedures do not appear to have a naughty query list. As it turns out, the answer is: “Yes; you can run the naughty queries from within an XSP!”.

My colleagues suggested that others might benefit from this capability and encouraged me to publish. So here it is.

External Stored Procedures

In many ways, External Stored Procedures are similar to SQL Stored Procedures except that they are written in either C or Java. As such, External Stored Procedures can draw upon most of the facilities available in those languages.

Just like regular SQL Stored Procedures, External Stored Procedure have access to a database session to run its own queries. It is this facility that enables us to run the “naughty queries” that you cannot directly run from within a regular Stored Procedure.

The example I’ve included here implements the “show (all forms)” naughty query. I’ve used Java because, IMHO, it is simpler for articles such as this. The principles can be applied equally to C/C++ External Stored Procedures. Similarly, the principles can be applied to the other examples I’ve listed above and if you are feeling a bit “Gangsta” you may have success with running some of the other “naughty queries” that I have not listed.

Disclaimer

There may be good reasons why the queries listed as unsupported within Stored Procedures are unsupported. This article provides a mechanism to run these unsupported queries from within a Stored Procedure. As such I would caution you to check carefully for any undesirable side effects. Otherwise, feel free to “go for it” at your own risk.

Having said that the above 3 examples have been constructed based upon the example shown below. All 3 scenarios work successfully with no apparent adverse effects.

The Code

The following code listing is the entire Show DDL Java External Stored Procedure and a shell script containing environmental variables used by the deploy and undeploy scripts. The attachment consists of:

    • the Show DDL Java External Stored Procedure as a NetBeans 7.1 project and
    • the various deploy, undeploy and test scripts.

If you choose to use Eclipse, I am told that the Eclipse plugin has a wizard that may be used to deploy the Procedure.

The Show DDL XSP

Following is the Show DDL External Stored Procedure Java code:

package com.teradata.show;

import java.sql.*;

public class DbObjects {
    public static void getDDL (int [] status, String objectType, String objName, String [] ddl)
            throws SQLException {
        ddl[0] = "Unknown failure in XSP.";
        status[0] = 1;

        try {
            String sql = "show " + objectType + " " + objName + ";";

            Connection conn = DriverManager.getConnection("jdbc:default:connection");
            PreparedStatement stmt = conn.prepareStatement(sql);

            StringBuilder result = new StringBuilder();
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                result.append(rs.getString(1));
                result.append("\n");
            }
            if (result.length() > 0) {
                ddl[0] = result.toString();
            }
            rs.close();
            stmt.close();
            conn.close();
            status[0] = 0;
        } catch (SQLException e) {
            status[0] = e.getErrorCode();
            ddl[0] = e.getMessage();
        }
    }

};

The External Stored Procedure is pretty straightforward. It consists of four parameters as outlined below:

    • A status code - an OUT parameter.
    • An object type - an IN parameter.
    • An object name - an IN Parameter.
    • The resulting DDL or an error message - an OUT Parameter.

The procedure operates as follows:

    1. It constructs a show statement by concatenating the keyword “show” with the object type (objectType) and object name (objName).

      A prudent precaution might be to validate both the object name and object type values supplied to the procedure. This is left an exercise for the reader. If it were me, I’d simple ensure that the objectType value consists of characters and that the object name did not contain a semi-colon. Any and all other validation could be performed by Teradata.
    1. A database session is obtained. This database session is essentially the same session that called the External Stored Procedure.
    1. While not strictly necessary for this example, the query is prepared and then executed.
    1. The results are then stepped through and accumulated into a String. Newlines are inserted at the end of each row returned from Teradata.

      Note that in the case of show queries, Teradata returns less rows than there are lines of text within the returned data. Within each row retrieved from the rs.getString() method, there will be multiple “lines of text”. The lines of text consist of Carriage Return (a.k.a. CR, \r or ASCII 0x0d) characters to separate the lines. Most systems do not correctly display these as individual lines. Thus an additional edit may prove useful here and that is to replace \r characters with \n characters. Again, this is left as an exercise for the reader. I believe this issue only applies to SHOW queries. EXPLAIN queries seem to return one row per line of text to display.
    1. The entire content of the string (result) is returned to via the ddl parameter and the status code is set to 0 (indicating success).
    1. If an error occurs the SQLException is triggered. In this case, the ddl out parameter is set to the error text and the status code is set to the Teradata error code (which can be searched for in the Teradata Messages Manual).

Refer to the Teradata SQL External Routine Programming manual for details relating to how to compile the procedure. The only real tricks are:

    • Obtain the relevant runtime library (javFNC.jar) and include it in your classpath for compiling.
    • Ensure that you use the correct Java version or when compiling the code (usually 1.6). The correct version is the version of the JRE that is installed on the Teradata nodes. This can be determined via the "cufconfig” utility (refer to the SQL External Routing Programming and the Utilities – Volume 1, A-K for details).

       

Create External Procedure DDL

To install the External Stored Procedure into Teradata, you will need at least the following:

    • grant Execute procedure on SQLJ to $U with grant option;                // Refer to envirables.sh below for $U
    • grant function on $DP to $U with grant option;
    • grant external procedure on $DP to $U with grant option;

The code to deploy the procedure is broken into a couple of files to make it a bit easier to administer. This consists of a deployment and undeployment script that leverages the file envirables.sh. This script (shown below) contains the various names and paths used to locate and create the UDF in Teradata. The values shown here relate to my environment, you will almost certainly have different values and will need to update this script as per the comments shown in the script:

envirables.sh

# Teradata User
U=gm310509
# Teradata Pass
P=secret

# Database containing the procedure.
DP=gm310509
# Database containing the JAR.
DJ=gm310509

# Path to the compiled Java library (Jar) file
JARFILE=/mnt/hgfs/Shared/tools/Judf/ShowDDL/dist/ShowDDL.jar
# Name used internally to identify the library within Teradata.
JARNAME=ShowDDLJAR
# Entry point to the Java library for this procedure
METHODNAME=com.teradata.show.DbObjects.getDDL
# Name of Teradata (External) Stored Procedure
PROCNAME=ShowDDL

The variables DP and DJ identify where the Procedure will be created and where the compiled JAR will be installed. Take a look at the Replace Procedure query in deploy.sh to make sense of this. They do not need to be the same database name. JARFILE is the path to the compiled JAR which will depend upon where you create your Java project and the IDE that you are using. My project was created in Linux at /mnt/hgfs/Shared/tools/JUdf.

Once you have setup your envirables.sh, simply run the deploy.sh script. If you wish to deploy from windows, I'm afraid, you will have to manually run the call to sqlj.install_JAR and replace procedure queries yourself. 

test.sql

replace procedure x (OUT ddl varchar(20000))
begin
    Declare outp varchar(20000);
    Call showDDL(:stat, 'table', 'lvc', outp);
    set ddl = outp;
end;