Executing DDL in a Store Procedure

Database
Enthusiast

Executing DDL in a Store Procedure

I am using Data Stage to execute stored procedures. My stored procedures have DROP/CREATE Indexes and COLLECT STATISTICS statements in them. I can execute these with no issues from SQL Assistant.

However, when I go to execute this through DataStage I am getting the following Datbase error

RDBMS code 3932: SP_UI_VOID_CD_DIM:Only an ET or null statement is legal after a DDL Statement.

These ddl statements really need to be able to run. I have tried using Dynamic SQL for this functionality, but got the same error message. I have then tried creating a separate Stored Procedure that gets called that contains nothing but the dynamic SQL to run. Each option runs fine through SQL Assistant, but no matter what I get the same error message back from DataStage. This maybe a question for the DataStage people, but since it is a Database error, I thought if someone could help me here.

The Stored Procedures run in Teradata mode. Any suggestions would be much appreciated.

Thanks,
Beth
1 REPLY
Junior Contributor

Re: Executing DDL in a Store Procedure

If the SP is running in Teradata Mode there's probably a BEGIN TRANSACTION submitted by DataStage, you should check the QueryLog.

You could try an END TRANSACTION before the DDLs and maybe another BT, so DataStage doesn't receive an error message when it submits ET.

Dieter