Debugging Using Trace Tables | Java External Stored Procedures | Vantage - Debugging Using Trace Tables - Advanced SQL Engine - Teradata Database

After an external stored procedure is installed in the database, debugging is limited.

The best practice is to develop and test the external stored procedure outside the database before you install it. You can use your own debugging tools to verify functionality.

If debugging outside the database is not sufficient, you can use trace tables to get trace diagnostic output.

Overall Procedure

Here is a synopsis of the steps you take to debug a Java external stored procedure using trace tables.

1. Create a trace table using the CREATE GLOBAL TEMPORARY TRACE TABLE statement.

   The first two columns of the trace table are used by the Teradata function trace subsystem. Any columns that are defined beyond the first two are available for an external stored procedure to use to write trace output during execution.

2. Enable the trace table for trace output using the SET SESSION FUNCTION TRACE statement.

   You can specify an optional trace string that the external stored procedure can obtain during execution.

3. Invoke the external stored procedure.
4. Call DbsInfo.getTraceString() in the external stored procedure to get the trace string that was specified in the SET SESSION FUNCTION TRACE statement.

   You can use the value of the trace string to determine what to output to the trace table.

5. Call DbsInfo.traceWrite() to write trace output to the columns of a trace table.
6. Use a SELECT statement to query the trace table and retrieve the trace output from the external stored procedure.

Example: Debugging an External Stored Procedure Using a Trace Table

Consider the following statement that creates a trace table that defines one column, Trace_Output, for an external stored procedure to write trace output to:

CREATE GLOBAL TEMPORARY TRACE TABLE XSP_Trace
  (vproc_ID     BYTE(2)
  ,Sequence     INTEGER
  ,Trace_Output VARCHAR(256))
ON COMMIT PRESERVE ROWS;

The following code uses the value of the trace string to determine whether to write the value of the input argument to the trace table:

public static void debugJXSP(String[] Str) throws SQLException {
  try {
    /* If the trace string is set to 2, write the value */
    /* of the Str input argument to the trace table.    */
    if (Str[0]!=null && DbsInfo.getTraceString().compareTo("2")==0) {
      DbsInfo.traceWrite("Debug Info: " + Str[0]);
    }
    String[] x=null;
    /* The following is an exception. */
    if (x[0].compareTo(Str[0])==0) return;	
  }
  catch (Throwable t) {
    StackTraceElement[] errs = t.getStackTrace();
    DbsInfo.traceWrite(t.toString() + " " + t.getMessage());
    for (int i=0;i<errs.length;i++)
      DbsInfo.traceWrite("In " + errs[i].getFileName() +
                                    " at "+ errs[i].getLineNumber());
    throw new SQLException(t.getMessage(), "38U01");
  }
}

The following statement enables trace output for table XSP_Trace and sets the trace string to 2 so that the external stored procedure outputs the value of the input argument to the trace table:

SET SESSION FUNCTION TRACE USING '2' FOR TABLE XSP_Trace;

The SET SESSION FUNCTION TRACE statement disables any previously enabled trace tables for the session.

The following statement queries the trace table to retrieve the trace output from the external stored procedure:

SELECT Trace_Output
FROM XSP_Trace
ORDER BY Sequence;

Related Topics