 |
 |
Archives of the TeradataForum
Message Posted: Fri, 20 Jul 2007 @ 11:46:44 GMT
| 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 ]]
| |
