Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Fri, 20 Jul 2007 @ 11:46:44 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Enhancements or Improvements to Stored Procedures
 
From:   Stegelmann, Rolf

  Does anyone know of any useful enhancements or application improvements to Stored Procedures, in recent releases of Teradata ?  


Depends on what release you are currently running on.


  One thing I do find difficult is stepping through a SP, particularly when it's not doing what you expect it to do. I end up writing data to Testing Table at strategic points in the SP but it's all rather time-consuming.  


Yes, I agree with you. An easier way if you can use a User Defined Function (UDF) is to write a UDF that will write to a trace table. Define the information you want to trace as the input to the UDF. The UDF in turn can call FNC_Trace_Write_DL function that will put the information in a temporary trace table. The nice deal with that is if the trace is not turned on it will do nothing. It will cause some overhead though because it will still call the UDF. Make sure once the trace UDF is debugged to run it in non protected mode, that way it executes in-line with the SP. A premature end of the SP or rollback of the transaction has no impact on the temporary trace table. Its contents are not deleted until the session logs off. It does make your SP run slower because each trace call forces the data to be put into the trace table. Nothing is buffered, the purpose being to not lose anything while testing. At the end of this email I have include a complete example on how to do it. It should be self explanatory when used in conjunction with the SQL documentation.


  Is there anyway of defining functions, for example, which returns a value, or is this effectively just another SP which is invoked from within the main SP ?  


UDFs are functions that return a value. You have to write them in C or C++. Came out in release 5.1. See documentation. In 6.1 you can also define your own data types and write the UDM (like a UDF -- User Defined Method) to do the proper operations with the new data type.


  In simple terms what does ITERATE do with LOOP. It does explain it in the manual, but still the penny is dropping in my head.  


Hmmm, it simply causes a jump to the beginning of the loop to the specified loop label. It's like the continue statement in C, if you know C.

     L1: LOOP
          BEGIN
             SQL stmt1;
             SQL stmt2;
             IF condition THEN
                  ITERATE L1;
             END IF;
             SQL stmt3;
             SQL stmt4;
           END;
         END LOOP L1;

If condition is true it will not execute stmt3 or stmt4 but go directly to beginning of LOOP and start executing stmt1.


  Also, I take it that there's no easy way of returning a > result set of values, like an array or, even better, an object?  


Returning result sets from an SP is in 12.0 (in beta now). They can be returned to the calling procedure or client or always to client no matter the call nesting level of the SP.

---------------------------------------

The following SP trace example is I hope not to lengthy, but it is complete and works. Comments are in [[ .. ]]

     [[ The temporary trace table -- defined by user except for first two
     columns, which are always fixed. ]]

     BTEQ -- Enter your DBC/SQL request or BTEQ command:
     show table tracetst;

      *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.

     ------------------------------------------------------------------------
     ---
     CREATE MULTISET GLOBAL TEMPORARY TRACE TABLE RGS.tracetst ,NO FALLBACK ,
          CHECKSUM = DEFAULT,
          NO LOG
          (
           proc_id BYTE(2),
           Sequence INTEGER,
           trace_str VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)

     ON COMMIT PRESERVE ROWS;

     [[ Next statement turns on the trace, the 'T' is a user defined string
     which can be 255 characters long and is interpreted by your trace UDF ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     SET SESSION FUNCTION TRACE USING 'T' FOR TABLE tracetst;

      *** Set SESSION accepted.
      *** Total elapsed time was 1 second.

     [[ nothing in trace table ]]
      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select * from tracetst order by 1, 2;

      *** Query completed. No rows found.
      *** Total elapsed time was 1 second.

     [[ run the trace demo SP -- the show procedure at end of email ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     call sptracedemo(3);

      *** Procedure has been executed.
      *** Total elapsed time was 1 second.

     [[ trace table contents after the SP executed -- see SP to see what it
     traces ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select * from tracetst order by 1, 2;

      *** Query completed. 6 rows found. 3 columns returned.
      *** Total elapsed time was 1 second.

     proc_id    Sequence trace_str
     ------- -----------
     -------------------------------------------------------
     FF3F              1 Start of sptrace demo
     FF3F              2 Table sp_demo1 created
     FF3F              3 did: insert (          1, 2.53000000000000E 001);
     FF3F              4 did: insert (          2, 5.02991053598372E 000);
     FF3F              5 did: insert (          3, 2.24274620409526E 000);
     FF3F              6 Got to end of sptrace demo

     [[ Called SP demo again takes different path because table already
     exists this time. ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     call sptracedemo(3);

      *** Procedure has been executed.
      *** Total elapsed time was 1 second.

     [[ next trace output is added to trace table ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select * from tracetst order by 1, 2;

      *** Query completed. 12 rows found. 3 columns returned.
      *** Total elapsed time was 1 second.

     proc_id    Sequence trace_str
     ------- -----------
     -------------------------------------------------------
     FF3F              1 Start of sptrace demo
     FF3F              2 Table sp_demo1 created
     FF3F              3 did: insert (          1, 2.53000000000000E 001);
     FF3F              4 did: insert (          2, 5.02991053598372E 000);
     FF3F              5 did: insert (          3, 2.24274620409526E 000);
     FF3F              6 Got to end of sptrace demo
     FF3F              7 Start of sptrace demo
     FF3F              8 deleted contents of sp_demo1 in handler
     FF3F              9 did: insert (          1, 2.53000000000000E 001);
     FF3F             10 did: insert (          2, 5.02991053598372E 000);
     FF3F             11 did: insert (          3, 2.24274620409526E 000);
     FF3F             12 Got to end of sptrace demo

     [[ next set demonstrates when tracing is turned off that nothing
     additional is written to trace table ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     SET SESSION FUNCTION TRACE OFF;

      *** Set SESSION accepted.
      *** Total elapsed time was 1 second.

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     call sptracedemo(3);

     *** Procedure has been executed.
      *** Total elapsed time was 1 second.


      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     select * from tracetst order by 1, 2;

      *** Query completed. 12 rows found. 3 columns returned.
      *** Total elapsed time was 1 second.

     proc_id    Sequence trace_str
     ------- -----------
     -------------------------------------------------------
     FF3F              1 Start of sptrace demo
     FF3F              2 Table sp_demo1 created
     FF3F              3 did: insert (          1, 2.53000000000000E 001);
     FF3F              4 did: insert (          2, 5.02991053598372E 000);
     FF3F              5 did: insert (          3, 2.24274620409526E 000);
     FF3F              6 Got to end of sptrace demo
     FF3F              7 Start of sptrace demo
     FF3F              8 deleted contents of sp_demo1 in handler
     FF3F              9 did: insert (          1, 2.53000000000000E 001);
     FF3F             10 did: insert (          2, 5.02991053598372E 000);
     FF3F             11 did: insert (          3, 2.24274620409526E 000);
     FF3F             12 Got to end of sptrace demo

     [[ The trace UDF. This one is real simple. You can make it as complex or
     simple as you have the desire or need to do. ]]

      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     show function sptrace;

      *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.

     ------------------------------------------------------------------------
     ---
     REPLACE FUNCTION RGS.SPTRACE (P1 VARCHAR(100) CHARACTER SET LATIN)
      RETURNS INTEGER
      SPECIFIC sptrace
      LANGUAGE C
      NO SQL
      PARAMETER STYLE TD_GENERAL
      NOT DETERMINISTIC
      RETURNS NULL ON NULL INPUT
      EXTERNAL NAME sptrace


      *** Text of DDL statement returned.
     ------------------------------------------------------------------------
     ---
     #define SQL_TEXT Latin_Text
     #include 


     /*
         Install in SYSLIB for general use and change to not protected mode

         ALTER FUNCTION sptrace EXECUTE NOT PROTECTED;

     */

     /* Assumes that the following trace table has been */
     /* created                                                   */
     /*
        CREATE MULTISET GLOBAL TEMPORARY TRACE TABLE tracetst,
           NO FALLBACK, NO LOG (proc_id BYTE(2),
                                Sequence INTEGER,
                                trace_str VARCHAR(100))
                     ON COMMIT PRESERVE ROWS;

       Turn on tracing with following SQL (decide what your options mean ):

       SET SESSION FUNCTION TRACE USING 'T' FOR TABLE tracetst;

     */

     void sptrace(VARCHAR_LATIN     *trace_text,
                  INTEGER           *result,
                  char               sqlstate[6])

     {

       SQL_TEXT    trace_string[257];
       void        *argv[20];  /* only need 1 arg -- its an array */
       int         length[20]; /* one length for each argument */
       int         tracelen;

         /* read trace string */

         FNC_Trace_String(trace_string);

         /* get length of string */
         tracelen = strlen(trace_string);
         /* Make sure tracing is turned on */
         if (tracelen == 0)
           return;

         if (trace_string[0] == 'T')
           {

             argv[0] = trace_text;
             length[0] = strlen(trace_text) +1;
             /* have something to trace */
             FNC_Trace_Write_DL(1, argv, length);
           }

     }


     [[ the SP that calls the trace function sptrace ]]
      BTEQ -- Enter your DBC/SQL request or BTEQ command:
     show procedure sptracedemo;

      *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.

     ------------------------------------------------------------------------
     ---
     REPLACE PROCEDURE sptracedemo  ( num_rows INTEGER  )
     BEGIN
        -- Teradata mode SP

        DECLARE dummy INTEGER;
        DECLARE start_val INTEGER;
        DECLARE fnum  FLOAT;

        SET dummy = sptrace('Start of sptrace demo');

        BEGIN
          DECLARE EXIT HANDLER FOR SQLSTATE '52010'
            BEGIN
               -- table already exists, delete the contents
               DELETE  sp_demo1 ALL;
               SET dummy = sptrace('deleted contents of sp_demo1 in handler');
               -- get error here and sp will exit
            END;
          CREATE TABLE sp_demo1 (a INTEGER, b FLOAT);
          SET dummy = sptrace('Table sp_demo1 created');
        END;


        SET start_val = 1;
        SET fnum = 25.3;

        WHILE start_val <= num_rows DO
           INSERT INTO sp_demo1 (start_val, fnum);
           SET dummy = sptrace('did: insert (' || start_val ||','|| fnum ||');');

           SET start_val = start_val +1;
           SET fnum = sqrt(fnum);
          END WHILE;

         SET dummy = sptrace('Got to end of sptrace demo');
     end;

     [[ that's all ]]


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020