Stored Procedure Execution via Unity

UDA

Stored Procedure Execution via Unity

Hi,

I am not sure if this is the right category of the current issues we are facing right now. Unity Director had been implemented in our client together with the Teradata database implementation. I am not sure if this is a verified bug but below are the current scenario we are facing.

We created stored procedure to standarize the security process in our client. These stored procedures created in each database system and working fine as intended.

Then we we tried to execute it via Unity, first error we faced was  “CALL Failed. 3807: Object does not exist”. This error appear to all stored procedure we created. We analyzed the problem and find out the most probably cause of the problem. At first we suspect that its because all stored procedure are using the DBC.SysExecSQL to execute the dynamic sql inside the stored procedure.

After digging some help in other forum when I asked what can be a replacement for DBC.SysExecSQL, someone suggest to use EXECUTE IMMEDIATE which we did. Another part of the solution is not only replacing the DBC.SysExecSQL in all our stored procedure with EXECUTE IMMEDIATE, we also use Unity Configuration portlet to declared all objects used in the stored procedure as "managed" in Data Dictionary  tab. Now all the stored procedures are successfully created via Unity.

Now that we are trying to execute the stored procedure, we always encounter "ERROR: T3932". We tried to execute again these stored procedure created via Unity in each individual system and the stored procedure is working fine.

Is there anyone out there faced similar problems with Unity? Did we missed something in Unity or we missed to understand how these stored procedure will work fine when executing via Unity? If someone is familiar with this situation any help really appreciated. Thanks in advance.

3 REPLIES
Teradata Employee

Re: Stored Procedure Execution via Unity

Unity Director does not currently support stored procedures. It is in the works however.

Enthusiast

Re: Stored Procedure Execution via Unity

Hi,

I am curious here since I work in both Teradata and Oracle. I have never seen a code in Teradata proc having execute immediate. Is it there even if you could execute them independent of Unity?

Thanks and regards,

Raja

Re: Stored Procedure Execution via Unity

We use the EXECUTE IMMEDIATE as replacement to DBC.SysExecSQL to execute dynamic sql inside stored procedure since DBC.SysExecSQL is not recognized by Unity, not sure if there is a way to make this DBC.SysExecSQL deploy as managed object in Unity Configuration Data Dictionary.