Is there any way to use BTEQ to export data in NoSpool mode?

Tools & Utilities
Enthusiast

Is there any way to use BTEQ to export data in NoSpool mode?

This is a BTEQ related question. TPT can export data in NoSpool mode which can eliminate spool usage during export, this can be quite useful if BTEQ can also directly export from table/partition without dumpping records into spool.

 

Thanks.

3 REPLIES
Teradata Employee

Re: Is there any way to use BTEQ to export data in NoSpool mode?

NOSPOOL is not available to BTEQ. It can only be used in the context of TPT Export.

 

Of course scripts can be created to do exports just like scripts can be generated for BTEQ.

Enthusiast

Re: Is there any way to use BTEQ to export data in NoSpool mode?

Thanks @ToddAWalter for quick reply.

 

The reason behind the question is:

TPT FastExport always requires utility slot which is an expensive resource. For medium size table, eg. less than 10GB on disk, it makes more sense to use 2~4 BTEQ sessions to extract the data, and save the utility slots for big table export, and FastLoad.

 

We have used AMPList() function to reduce the overhead of scanning table multiple times, but sometimes we can still run out of spool for the pure export use cases when multiple tables are being exported at the same time.

Teradata Employee

Re: Is there any way to use BTEQ to export data in NoSpool mode?

If a recent release is in place and the TASM management of utilities is being used, then Export's use of a slot is significantly less impactful than in prior releases. Slots can be identified for export over and above the slots for the load utilities making it so they do not compete. This allows for more use of Export without interfering with the slots needed for loads. Also, more slots can be defined for Export if higher concurrency is desired.

 

Running multiple jobs in BTEQ to export the same table is an expensive use of resources. Even if the qualification is done to focus on a subset of AMPs, the work for the query will be badly skewed affeccting other work.

 

I understand the desire to have a single pattern for all jobs but it really is indiceted to use a couple patterns for eexport. Small stuff like the 10G tables can reasonably be exported in a single session BTEQ while large things really indicate use of Export.