Execute SQL Server Stored Procedure to Drop Index

mikesherm · Post by **mikesherm** » Wed Jan 13, 2010 3:43 pm

You folks have always been great in responding with quick and accurate info. Here's what I think is a fairly simple problem but I'm have trouble figuring out the answer:

I want to execute SQL Server stored procedures within a Job that will drop an index before a target table is loaded and then recreate the index after the target table is loaded. The stored procedures have been sucessfully tested in SQL Server and imported into the Datastage project.

I am trying to use the Stored Procedure stage but can't seem to configure it properly. Is this the proper stage to use and how do I configure
Any help would be appreciated.

Mike

ray.wurlod · Post by **ray.wurlod** » Wed Jan 13, 2010 3:46 pm

Probably better to use the Open and Close commands in the ODBC Enterprise stage (or SQL Server Enterprise stage, if that's what you're using).

mikesherm · Post by **mikesherm** » Wed Jan 13, 2010 4:26 pm

Thanks, Ray. Just one more slightly stupid

question.

Which command (Open or Close) do I set in the Source Enterprise Stage and which command do I set in the Target Enterprise Stage? Or can I set them in the same stage?

Mike

ray.wurlod · Post by **ray.wurlod** » Wed Jan 13, 2010 4:32 pm

Open command to drop indexes, Close command to re-create indexes. Open command in either stage, Close command in target stage: I'd put them both in the target stage.

chulett · Post by **chulett** » Wed Jan 13, 2010 4:33 pm

I would put both in the target stage, all though it may not ultimately matter, it just makes more sense for them to be in the stage working the same table.

mikesherm · Post by **mikesherm** » Wed Jan 13, 2010 4:37 pm

Excellent Ray!

The only reason I was confused is that the Information description for the Close Command in the stage is as follows:

SQL statement to be executed before insert array is processed. This statement is executed only once on the conductor node.

This certainly seems counter-intuitive to me.

Anyway, I'm good to go. Thanks a lot!

Mike

chulett · Post by **chulett** » Wed Jan 13, 2010 4:41 pm

That would be a documentation bug, it would seem. Basically, the 'open' command is processed when the stage is 'opened' before any rows are processed, while the 'close' command is executed after all rows have been processed and just before the stage itself is closed.

Similar to the 'Before SQL' and 'After SQL' options other stages have.