Working with Stored Procedures in Unity Director

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Teradata Employee

Working with Stored Procedures in Unity Director

Unity Director currently has a number of limitations regarding the use of stored procedures. These limitations are often misunderstood, and mistakenly lead to the assumption that stored procedures are not currently supported by Unity Director. This is not the case – while there are a number of considerations to be aware of, most stored procedures can be used with no issues.

Understanding these limitations is key to overcoming or avoiding them altogether, especially since some of them conflict with common best practices when developing stored procedures. In this three part article, we’ll examine the three most common issues encountered in using stored procedures in a Unity Director environment.

Creating and changing stored procedures

In order to recognize and manage database objects, Unity Director and Loader rely on a Data Dictionary that is stored in the Unity repository. For most DDL changes, Unity Director will automatically update this Data Dictionary as DDL are issued (as long as they are issued via Unity Director or Loader).

Stored procedures and triggers are a special case however.  In version 14.11, the Unity Director software does not have the capability to dynamically update its Data Dictionary for any changes to stored procedures. This does not necessarily mean stored procedures cannot be created or changed on multiple Teradata systems via Unity Director. It does however mean they cannot be used via Unity Director until a Data Dictionary scan has been done to create (or re-create) a dictionary entry for that stored procedure.

It’s extremely important to keep this data dictionary entry in sync with the actual stored procedure or trigger contents. Failing to do so can result in hard to diagnose locking issues, or possible data inconsistencies across systems.

Error Handlers

One of the most common, but least understood current Unity Director limitations involving stored procedures is the use of error handlers. To illustrate the issue, consider the following stored procedure, which follows the normal best practice to implement an error handler:

REPLACE PROCEDURE LoadId (id_in integer)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION H1:
BEGIN
DECLARE errmsg VARCHAR(257);
GET DIAGNOSTICS EXCEPTION 1 errmsg = MESSAGE_TEXT;
insert into errorLog (id, sqlerror, error_message) values (id_in, SQLCODE, errmsg);
insert into rejectedIds (id) values (id_in);
END H1;
INSERT INTO LoadedIds (id) VALUES (id_in);
END;

If an SQL exception is raised, the error handler traps (and consumes) the error within the handler. It records the error in a central application errorLog table, and then inserts the id for which the error arose into a second table rejectedIds, for later processing.

In a single system environment, this works perfectly. However, when called in a multi-system environment managed by Unity Director and Loader, it can have undesirable effects. Consider this scenario – assume that the id one has already been loaded (via Unity Director to both Teradata systems - in this case, the two Teradata systems are db1 and db2, while the unity server is unity1) using this stored procedure:

client:# bteq
Teradata BTEQ 14.10.00.03 for LINUX. PID: 27549
Copyright 1984-2013, Teradata Corporation. ALL RIGHTS RESERVED.
Enter your logon or BTEQ command:
.logon unity1/etltest
BTEQ -- Enter your SQL request or BTEQ command:
call loadId(1);
*** Procedure has been executed.
*** Total elapsed time was 1 second.

Later on, an administrator wants to clean up the LoadedIds table, but mistakenly logs into one of the Teradata systems directly:

client:# bteq
.logon db1/etltest
Password:
*** Logon successfully completed.

*** Teradata Database Release is 14.10.00.04
*** Teradata Database Version is 14.10.00.04
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:

delete loadedIds;
*** Delete completed. One row removed.
*** Total elapsed time was 1 second.

This administrator error has created a data inconsistency that will eventually cause an error; the ID 1 has been loaded on system 2, but not system 1. Later on, someone calls the procedure to load id 1 again:

BTEQ -- Enter your SQL request or BTEQ command:
call loadId(1);
*** Procedure has been executed.
*** Total elapsed time was 1 second.

Note that since the procedure worked on one system, the client receives a success. Indeed, on system 1, the stored procedure call did work as intended, and the errorLog and rejectedIds table are empty:

select count(*) from etltest.errorlog;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Count(*)
-----------
0

select count(*) from etltest.rejectedIds;
*** Query completed. One row found. One column returned.
;*** Total elapsed time was 1 second.

Count(*)
-----------
0

However on system 2, they are not, since the stored procedure actually failed because of a unique constraint violation:

BTEQ -- Enter your SQL request or BTEQ command:
select count(*) from etltest.errorlog;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Count(*)
-----------
1

BTEQ -- Enter your SQL request or BTEQ command:
select count(*) from etltest.rejectedIds;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Count(*)
-----------
1

Assuming there will be a follow-on process to process the IDs in the rejectedID table, this data inconsistency will in-turn lead to more data inconsistencies, and likely result in other tables becoming unrecoverable. However since the original source of the inconsistency is not apparent, it will be very difficult to connect the consequence to the original effect; the duplicate rows as a result of the administrators error.

The root cause of this issue is that by consuming the error, the stored procedure, in effect, hides it from Unity Director. Unity Director currently has no visibility into the actual internal execution of the stored procedure, so it must rely on the request return code provided by the database, which is always a success when an error handler is in use.

Besides run-time issues, this can cause problems and data inconsistencies when a system or table is being recovered. Since Unity Director can’t see that the transaction within the stored procedure actually failed, it will not attempt recovery of the request.

Fortunately, it does not require much work to modify this error handler to make to safe to use with Unity. All that is needed is to re-throw the error at the end of the error handler, so it becomes visible to Unity. For this purpose, a RESIGNAL statement is added to the procedure.

RESIGNAL;

Adding this call to the original stored procedure's error handler now makes it safe to use in a Unity environment:

REPLACE PROCEDURE new_LoadId (id_in integer)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION H1:
BEGIN
DECLARE errmsg VARCHAR(257);
GET DIAGNOSTICS EXCEPTION 1 errmsg = MESSAGE_TEXT;
insert into errorLog (id, sqlerror, error_message) values (id_in, SQLCODE, errmsg);
insert into rejectedIds (id) values (id_in);

RESIGNAL;

END H1;
INSERT INTO LoadedIds (id) VALUES (id_in);
END;

As before, the stored procedure completes successfully on one system, so the client sees the success response:

BTEQ -- Enter your SQL request or BTEQ command:
CALL new_LoadId(1);

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

Now, however, an interrupted alert appears in Unity, informing the DBA of the problem:

System 2 (db2) sent an inconsistent message, table etltest.loadedids is interrupted:
Transaction: 168
SQL: 'CALL new_LoadId(1);'
Response 1 from system 1 (db1): row count 0
Code: 0
Message: Success.
Response 2 from system 2 (db2): row count 0>
Code: 2802
Message: Duplicate row error in etltest.loadedIds.

Additionally, when this stored procedure fails on both system, this error is now visible to client applications:

BTEQ -- Enter your SQL request or BTEQ command:
call new_loadId(6);

*** Failure 2802: Duplicate row error in etltest.loadedIds.
*** Total elapsed time was 1 second.

As well, since the tables are interrupted, Unity Director can prevent any further data inconsistencies from being created and also take care of resynchronizing system 2, once the DBA resolves the initial error. It is important to note that any action taken by the error handler on system 2 might still produce data that is different than system 1. There will still be an entry in the rejectedIds table on system 2 for example. But the only manual intervention necessary for the immediate processing is to correct the duplicate row on system 2.

.logon db2/etltest
Password:

*** Logon successfully completed.
*** Teradata Database Release is 14.10.00.04
*** Teradata Database Version is 14.10.00.04

*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.
*** Total elapsed time was 1 second.

BTEQ -- Enter your SQL request or BTEQ command:
delete LoadedIds where id=1;

*** Delete completed. One row removed.
*** Total elapsed time was 1 second.

Then Unity Director’s automatic recovery will replay the stored procedure request and any requests that followed it.

Avoiding serialization issues

In the previous part of this article, we examined a stored procedure that followed a normal stored procedure practice of using an error handler to record errors in a central application error log table. We examined the need to modify the stored procedure to re-throw any errors raised so they were visible to Unity Director in order to prevent data inconsistencies and allow automatic recovery.

Next, let’s consider the consequences of using this error handler in all stored procedures in the environment. This is also a common practice, since it ensures all errors encountered by stored procedures report them to the same table.

Recall that Unity Director takes preemptive locks on all tables that potentially might be read or written to when it executes a stored procedure call. It does this to ensure a guaranteed consistent and recoverable order for the writes to the involved tables, regardless of other concurrent work on the same tables that might be occurring. This locking is preemptive because Unity Director cannot “see” inside the stored procedure execution. Since it can’t know whether any stored procedure using this error handler is actually going to writes to the errorLog table, it needs to execute all those stored procedures with a table-level write lock on that table. This will cause all stored procedure requests using the error handler to serialize. In an environment that has many concurrent users calling stored procedures, this could be a serious problem.

Fortunately, this is not a difficult issue to resolve, by modifying the Unity Data Dictionary entry for the affected stored procedures. To do this, go to the Data Dictionary scanner in the Unity Director Configuration portlet, and locate the stored procedures that use the error handler:

Clicking on the details icon will open a window displaying the locks acquired for the stored procedure call. In this case, we can see that the example LoadId procedure writes to the tables loadedIds, rejectedIds and errorLog.

Unselect the write checkbox for the errorLog table, and the LoadId call will no longer take a write lock on that table.

The change will take effect once the Data Dictionary has been re-deployed:

 

The next time the stored procedure is called, it will not acquire a write lock on the errorLog table. The locks acquired by the call are visible in the details view of the sessions page in the Unity Director portlet (while the request is executing).

It’s important to understand why it’s safe to do so for this particular situation. The error handler inserts single rows to the errorLog table – it will never modify or access rows potentially being read from other sessions. Only because there should never be any conflict between concurrent requests to this table, the table-level lock can be safely removed. Attempting to do the same in other more complex situations where there are potentially conflicting requests will result in data inconsistencies or dead-locks.

Working around dynamic SQL

Another common issue is the use of dynamic SQL within stored procedures. Dynamic SQL can take two forms. The most common is the dynamic SQL executed using the DBC.SYSEXECSQL function. Statements can also be dynamically prepared and executed using the PREPARE statement.

Dynamic SQL inside stored procedures poses a problem for Unity Director because Director needs to be able to provide locks to guarantee write consistency across Teradata systems it manages. Since the SQL string that is executed is not constructed until the stored procedure begins to execute, there is no way for it to predict which tables will be touched.

The Data Dictionary scanner will detect stored procedures that use dynamic SQL and flag them with a warning:

Clicking on the warning will provide more detailed information:

There are three general situations that stored procedures using dynamic SQL will fall in to:

    • Dynamic SQL that does not touch tables; in other words DDL or DCL requests.
    • Dynamic SQL that touches a known and unchanging set of tables
    • Dynamic SQL that may touch any table.

Before delving into how to work around any of these situations, closely examining why dynamic SQL is being used is highly recommended. In many situations, dynamic SQL it is not the ideal way to implement an application process. It is more complex and harder to debug than client-side code. It also removes any benefit of using stored procedures (dynamic sql is not pre-compiled before execution). DBA’s sometimes tend to choose to capture programs in stored procedures because they are more comfortable with working inside the database rather than outside, even though developing a client side application might be more optimal. This personal comfort can come with a high overall cost.

Dynamic SQL that does not touch tables (DDL or DCL requests)

This class of stored procedure is typically used by DBA’s to retain typical maintenance requests. These may at first appear to be a non-issue, since Unity Director may not even need to acquire any table locks for many requests in this use case. However, Unity Director has a further limitation – stored procedures called via Unity Director cannot contain multiple DDL statements.

Also, it’s important to note that in addition to dynamic SQL, Unity Director cannot be used to call stored procedures that contain multiple DDL statements without some modifications. This is because Unity Director wraps the stored procedure call within a transaction in order to track its successful completion. Since DDL statements are themselves complete transactions this will cause an error, since you cannot execute transactions within a transaction. A DDL statement must be the last request issued within its’ transaction. Consider this simple example:

 REPLACE PROCEDURE createUserTables (username varchar(256))
BEGIN
DECLARE sqlstring VARCHAR(1024);
DECLARE EXIT HANDLER FOR SQLEXCEPTION H1:
BEGIN
DECLARE errmsg VARCHAR(257);
GET DIAGNOSTICS EXCEPTION 1 errmsg = MESSAGE_TEXT;
insert into errorLog (id, sqlerror, error_message) values (-1, SQLCODE, errmsg);
RESIGNAL;
END H1;

SET sqlstring='CREATE TABLE '||username||'.usertableA (id integer)';
CALL DBC.sysexecsql(:sqlstring);

SET sqlstring='CREATE TABLE '||username||'.usertableB (id integer)';
CALL DBC.sysexecsql(:sqlstring);
END;

This stored procedure cannot be called via Unity Director because it does two DDL requests. It will cause an error:

call etltest.createUserTables('etltest');
*** Failure 3932 CREATEUSERTABLES:Only an ET or null statement is legal after a DDL Statement.

The problem from the first part of this article (masking error handlers) now becomes the solution to this difficulty. It is possible to allow the stored procedure to call multiple DDL by adding an ET; to the beginning of the stored procedure. But an CONTINUE HANDLER is also required to capture and comsume error 3510 'Too many End transaction statements' to allow the stored procedure to still be called directly on Teradata.

REPLACE PROCEDURE createUserTables (username varchar(256))
BEGIN
DECLARE sqlstring VARCHAR(1024);
DECLARE EXIT HANDLER FOR SQLEXCEPTION H1:
BEGIN
DECLARE errmsg VARCHAR(257);
GET DIAGNOSTICS EXCEPTION 1 errmsg = MESSAGE_TEXT;
insert into errorLog (id, sqlerror, error_message) values (-1, SQLCODE, errmsg);
CALL throwError(SQLCODE,errmsg);
END H1;

-- CLOSE THE UNITY DIRECTOR TRANSACTION WRAPPER - This SP is non-recoverable
DECLARE CONTINUE HANDLER FOR SQLSTATE 'T3510' BEGIN END;
ET;

SET sqlstring='CREATE TABLE '||username||'.usertableA (id integer)';
CALL DBC.sysexecsql(:sqlstring);

SET sqlstring='CREATE TABLE '||username||'.usertableB (id integer)';
CALL DBC.sysexecsql(:sqlstring);
END;

Since the additional ET; now ends the transaction Unity Director started to track the completion of the stored procedure, it can now be called successfully via Unity Director, without errors.

call createUserTables('etltest');

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

BTEQ -- Enter your SQL request or BTEQ command:

However, it is important to note that this stored procedure cannot be automatically recovered during any type of outage. Unity Director will have no way to track the fact that the stored procedure did not complete on all systems. For this reason, the stored procedure should only be called when all systems and involved tables are ACTIVE.

DCL requests (GRANT and REVOKE), on the other hand, will generally work. However, it’s important to note that the sequencing of GRANT and REVOKE requests are also not guaranteed to be replayed during recovery, or replayed in a particular order. If a stored procedure contains only GRANT and REVOKE requests, it may be played at any time during a recovery process. The solution to this problem is to introduce a write to a table (for example, the errorLog table from the previous example) that forces requests to serialize. Since GRANT and REVOKE are normally executed as part of a DBA’s maintenance activities, concurrency is usually not an issue.

Dynamic SQL that touches a known and unchanging set of tables

Sometimes dynamic SQL in stored procedures is used to perform a different type of action based on certain conditions that are evaluated by the stored procedure. Typically, the names of any tables accessed in this situation are visible within a string variable in the text of the stored procedure. In this case, it is possible manually configure the Unity Data Dictionary entry for the stored procedure to allow Unity Director to provide the correct locks for that table. Obviously, this requires manually inspecting the stored procedure to determine the list of affected tables. Consider this example:

REPLACE PROCEDURE processId (id_in integer,processmode integer)
BEGIN
DECLARE sqlstring VARCHAR(1024);
DECLARE EXIT HANDLER FOR SQLEXCEPTION H1:
BEGIN
DECLARE errmsg VARCHAR(257);
GET DIAGNOSTICS EXCEPTION 1 errmsg = MESSAGE_TEXT;
insert into errorLog (id, sqlerror, error_message) values (-1, SQLCODE, errmsg);
RESIGNAL;
END H1;
SET sqlstring='update etltest.table2 set';
if processmode=1 then
SET sqlstring=sqlstring||' col1=col1*2 ';
elseif processmode=2 then

SET sqlstring=sqlstring||' col1=-1 '; end if; SET sqlstring=sqlstring||' WHERE id='||id_in; CALL DBC.sysexecsql(:sqlstring); END;

In this stored procedure, the table ‘table2’ is visible in the executed SQL string. This makes it possible to manually configure the Data Dictionary entry for this stored procedure, by adding a write lock to this table. In the Data Dictionary scanner, locate the stored procedure and open the lock details view. Unselect the ‘Only selected’ button at the top. This will show all objects available to reference in this Data Dictionary entry.

Now, type the name of the table that must be locked, and check the approperiate lock type. In this case, that is etltest.table2, with a write lock.

Once the dictionary is deployed, this stored procedure will now properly lock table2 when it executes, ensuring consistent execution on all Teradata systems.

Dynamic SQL that may touch any table

The last class of stored procedures that use dynamic SQL could read or write to any dynamic set of tables. Often, this is used when there is a common process that is shared between multiple users or databases to perform transformations against a common schema. For example:

REPLACE PROCEDURE transferData (username varchar(256))
BEGIN
DECLARE sqlstring VARCHAR(1024);
DECLARE EXIT HANDLER FOR SQLEXCEPTION H1:
BEGIN
DECLARE errmsg VARCHAR(257);
GET DIAGNOSTICS EXCEPTION 1 errmsg = MESSAGE_TEXT;
insert into errorLog (id, sqlerror, error_message) values (-1, SQLCODE, errmsg);
RESIGNAL;
END H1;
SET sqlstring='insert into '||username||'.usertableA select * from '||username||'.usertableB';
CALL DBC.sysexecsql(:sqlstring);
END;

It’s not possible to simply manually configure the stored procedure to acquire locks against a particular table or set of tables, because it’s the database name that is changing. Picking a single arbitrary database would result in serialization issues if there are concurrent copies of the stored procedure working on different databases.

There is no way to use this stored procedure safely without minor modifications to the client-side application code. In order to inform Unity which tables need to be locked, the client must first issue some type of locking statement that to acquire locks on the tables it intends to modify and then call the stored procedure within a single transaction. For example, the client application could call:

BT;
LOCK TABLE etltest.userTableA for WRITE SELECT 1 WHERE 1=0;
LOCK TABLE etltest.userTableB for WRITE SELECT 1 WHERE 1=0;
call transferData('etltest');
ET;

This will allow the stored procedure to be safely used via Unity Director.

11 REPLIES
Teradata Employee

Re: Working with Stored Procedures in Unity Director

Thanks Paul for this article. This will rest a lot of perception about Unity handling of Stored Procedures along with encouraging the DBAs & Developers to use Exception Handling and the butterfly effect associated with bypassing commands directly to the managed Teradata Systems.

It will be great if we can have a similar article about the Locking Mechanism of Unity. As Unity involves a secondary locking mechanism in addition to the traditional Teradata Database locking & the processing of Lock Types is slightly different for both Unity Director & Teradata Database (Courtesy of Restrictions with Unity Director), any practical article like the above would be great addition.

Again, Thanks for this Helpful Article,

Smarak

Teradata Employee

Re: Working with Stored Procedures in Unity Director

Thanks Smarak!

>It will be great if we can have a similar article about the Locking Mechanism of Unity

Ah, that's a good idea, thanks! I will see what I can do about realizing it in the next few months.

Enthusiast

Re: Working with Stored Procedures in Unity Director

This is an interesting article that has covered a number of points that are of concern.  I have 2 follow up points.

The first is with regard to the use of RESIGNAL.  I have been experimenting with its use as I was not familiar with it.  I am concerned that it appears to change the behaviour of my existing error handling.

Example of original proc:

DECLARE WS_TabCount SMALLINT;

DECLARE EXIT HANDLER

FOR SQLEXCEPTION, SQLWARNING

BEGIN

set ReturnCode = sqlcode;

set ReturnMessage = 'Unsuccessful, check sqlcode.';

END;

SELECT count (*)

into :WS_TabCount

FROM DBC.TABLES; 

END MAIN_ROUTINE;

When this is called from SQLA,

call DBADMIN.RESIGNLno(rc,rm);

the result is 2 out parameters

ReturnCode ReturnMessage

  2616          Unsuccessful, check sqlcode.

and the message

Call completed. 1 row returned. Elapsed time = 00:00:01

If the code is changed to

DECLARE WS_TabCount SMALLINT;

DECLARE EXIT HANDLER

FOR SQLEXCEPTION, SQLWARNING

BEGIN

RESIGNAL;

set ReturnCode = sqlcode;

set ReturnMessage = 'Unsuccessful, check sqlcode.';

END;

SELECT count (*)

into :WS_TabCount

FROM DBC.TABLES;

END MAIN_ROUTINE;

and called from SQLA

call DBADMIN.RESIGNLA(rc,rm);

This time there are NO output parameters, but there is a better message.

Call failed. 2164: RESIGNLA: Numeric overflow occured during computation.

I should be interested in your comments on the loss of the out parameter values and the change in the value of the errorcode.

The second point is in regard to pre-emptive acquisition of table locks.  Teradata is a poor lock manager and it is poor at serialising locks.  My comparison is with DB2.  Has any analysis been done on the extent to which this lock behaiour increases transaction failures due to deadlocks?  I am particularly concerned about the increased duration of locks and the increased size of locks that may previously have been hash locks.

Teradata Employee

Re: Working with Stored Procedures in Unity Director

Yes, you're correct, adding resignal will change the behaviour of that stored procedure. In the orignal version, you are capturing the error, and returning a success return code, with the error code in the OUT parameters.

Resignal will cause the return code to be set to the error code itself, and the OUT parameters will be unset.

> Teradata is a poor lock manager and it is poor at serialising locks.  My comparison is with DB2.

This is a little beyond the scope of this article, since we're talking about Unity Director, not Teradata itself. But Teradata and DB2 are designed for different purposes (my background from the days of xkoto was orginally in DB2 as well). DB2 is an OLTP database engine designed for smaller transactional data stores. Teradata has always been designed for high scalablity for in the data warehouse. The two purposes have different requirements when it comes to locking, which is why they are optimized differently.

As far as Unity Director goes, however, Unity Director will potentially increase the scope and duration of locks take by stored procedures. Unity Director can't use rowhash locks inside stored procedures, for example. So it is something to be cautious of if you rely heavily on stored procedures in a high concurrency environment.

Enthusiast

Re: Working with Stored Procedures in Unity Director

Hi Paul

the effect of RESIGNAL has me confused and I am wondering if this is going to be an issue. 

From your example, my understanding of the benefit of adding RESIGNAL to SP error handling is to avoid the error being consumed so that Unity director can compare completion codes and report if there is a difference after an instance of a call has executed in multiple locations.

The testing that I am doing is in a single environment.  The reason for this testing is to identify any changes to our existing code should we decide to use Unity.  Some of the challanges that have been identified will probably mean that Unity loader will not be usable for all of our ETLs.  Therefore one question is whether we can continue to use the same common SPs for standard tasks for non-unity and unity managed ETLs.  That SP code behaves differently, depending on whether it is run in a unity environment, is potentially a very significant issue.  It is a small cost to make small changes to a single piece of code, whereas it is a very high cost to change hundreds of SP calls.

As an aside, I think your article under-articulates the reasons why SPs are used in this kind of scenario.  Their value is that it enables standards to be enforced in a piece of encapsulated code that is available to all clients.

Returning to my problem and drawing together other threads of investigation, the first issue is the 2164 code.  I cannot find this in the 14.00 or 15.00 manuals, but I am told that this is an event message because there is no handler defined to catch the exception.  How is it possible for your code to displays a resignalled code of 2802 without getting a 2164?  Does the unity environment have a built-in handler to catch resignalled states?

If this is the case then it may well prevent the same code being used in a mixed environment.  If it is not the case then I remain in search of a code solution that can be used in a mixed environment.

Teradata Employee

Re: Working with Stored Procedures in Unity Director

Hi butchec,

Sorry for the slow response. We've figured out that the RESIGNAL is not returning a 2164 instead of a 2616 because of a database bug. I'm told it is fixed in TDBMS_14.10.05h.49.

Teradata Employee

Re: Working with Stored Procedures in Unity Director

Hi Paul,

Thank, this great artical. one thing to clarify that in the last section for the "Dynamic SQL that may touch any table", you suggest to add BT/ET around the lock statement and SP call on the client side script, but my my understanding is that in the ANSI mode, this will results error in Unity, and in the TERADATA mode, this BT/ET will be ignored by Unity, isn't it? if so this end up with seperated transactions, and Unity still not able to put lock on, multi-statement may help.

There is no way to use this stored procedure safely without minor modifications to the client-side application code. In order to inform Unity which tables need to be locked, the client must first issue some type of locking statement that to acquire locks on the tables it intends to modify and then call the stored procedure within a single transaction. For example, the client application could call:

?

1

2

3

4

5

BT;

LOCK TABLE etltest.userTableA for WRITE SELECT 1 WHERE 1=0;

LOCK TABLE etltest.userTableB for WRITE SELECT 1 WHERE 1=0;

call transferData('etltest');

ET;



This will allow the stored procedure to be safely used via Unity Director.

Teradata Employee

Re: Working with Stored Procedures in Unity Director

another issue: I have seen SP have nested SPs calls, this is not working with Unity.

Teradata Employee

Re: Working with Stored Procedures in Unity Director

Thanks!

In ANSI mode, you wouldn't use a BT/ET aronud the SP, but you would still use a locking statement, followed by the PS call.

No, the BT/ET outside of the SP isn't ignored - it is used to prememtively lock the tables the stored procedure is going to call.

Obviously, it would be much simplier just to skip the SP call althogether and send the dynamic SQL requests from the client. That's usually the best solution in this case.

Early versions of Unity did have issues with nested stored procedures in the dictionary scanner, but those are all resolved now.  Just be cautious of the serialization issues that nested stored procedures can lead to that I mentioned in the article.