Is there a BTEQ-equivalent PX stage?

joeyfan · Post by **joeyfan** » Tue Mar 14, 2006 10:56 am

I am a rather inexperienced user of DS 7.5.1A PX, and also an experienced Teradata DBA. I am wondering if there exists a stage for Teradata that allows SQL to be run via ODBC that requires no inputs or outputs.

We are trying to integrate DS into our production environment and our standards require some BTEQ activites before and after a data load. For example, we check for the presence of log or work tables before starting a new Multiload. If either are found, we do not permit the new Multiload on the same table to start. Another standard is collecting statistics after a Fastload.

Is there a stage that can execute user-defined SQL that I can call, or am I going to have to shell out to something external to DS before and after each load?

Thanks for the help,
Adam

ray.wurlod · Post by **ray.wurlod** » Tue Mar 14, 2006 11:32 am

You might try the Stored Procedure stage. Or the Open Command or Close Command options in the Enterprise ODBC stage.

joeyfan · Post by **joeyfan** » Tue Mar 14, 2006 12:47 pm

Thanks Ray! I tried the Stored Procedure stage on your suggestion but Teradata is not a supported platform. If it worked with Teradata it would be just what I need.

I am playing around with the Before and After commands but mostly to satisfy my curiosity. For my scenario I am pretty sure I can't rely on the Before statement because the condition that is an error in daily processing is the expected situation in a load restart. I need to be able to bypass the safety check and execute the load in the event it is a restart of a failed load. Having the safety check code embedded in the load prevents me from restarting, unless I manually intervene and remove the error check code. I would like a more automated solution. Thus, a standalone SQL submitter stage is desired so I can choose to run or not run the safety checks.

Any other ideas?

Thanks,
Adam

ray.wurlod · Post by **ray.wurlod** » Tue Mar 14, 2006 2:29 pm

Umm.. ask "them" to support Teradata in the Stored Procedure stage?

Sorry about the paucity of other ideas; I tried to be comprehensive the first time, and didn't check whether Teradata was supported for the SP stage.

kcbland · Post by **kcbland** » Tue Mar 14, 2006 2:36 pm

Whip out a standard shell script to run DB commands. Wrapper that script in a stage and use when needed.

joeyfan · Post by **joeyfan** » Thu Mar 16, 2006 2:28 pm

Update: I found I can do what I want to do in the Before stage because I have the ability to stop the load job with a non-zero return code if a specified condition is met. That takes care of most of my problem.

I still have to shell out to our Teradata load server to invoke BTEQ when I want to just run SQL in the job stream, but I can live with that because our master scheduler allows it.

Thanks again for the help,
Adam

Ultramundane · Post by **Ultramundane** » Mon May 31, 2010 1:36 pm

Do you know if it is possible to use the TeraData Connector for this type of activity?

ray.wurlod · Post by **ray.wurlod** » Mon May 31, 2010 3:58 pm

There is a company that exhibited at IOD EMEA 2010 that has a utility for converting BTEQ scripts (and lots of other things, like Informatica, Ab Initio, etc.) into DataStage jobs and metadata that can be consumed by Metadata Workbench. The name of the company is COMPACT (there are more details, but I don't have them with me at the moment).

Ultramundane · Post by **Ultramundane** » Mon May 31, 2010 5:21 pm

Many thanks.

I'll look around for those add-ons and thank you for sharing information from the conference. It looks impressive and nice job.

Take care

ray.wurlod · Post by **ray.wurlod** » Mon May 31, 2010 5:53 pm

Try here first.