Archives of the TeradataForum

Message Posted: Fri, 19 Mar 2010 @ 17:21:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I'd like to know how to reference the returned value(s) from a Stored Procedure from within BTEQ.

So, we have something like this :

     /* -----------------------------*/
     /* EXECUTE THE STORED PROCEDURE */
     /* -----------------------------*/

     CALL ${ENV}${EXECU}.AL_CSSR_CONSIGNMENT_P ( :ResultCode );

     .If ErrorCode <> 0 Then .Exit 12;


     .Label END_BTEQ
     .Exit 0;

     EOI

At the moment, the ErrorCode is based upon whether the Stored Procedure fails, but that's not quite what I want - at least, I don't think it's what I want !

What I'd like to do is let the BTEQ fail only where the ResultCode is non-zero, something we can then control within the Stored Procedure. Thing is, I don't know how to interrogate that returned value from within BTEQ ... I've no doubt it's either easy of not possible !

For some background - just in case I've got the wrong high level design - we've got a Fact Table and a Dimension Table.

I've created two Single Table Aggregate Join Indexes on the Fact Table, to provide a distinct list of Country Codes. The performance of these are astonishingly good, so I'm happy with them.

Unfortunately, as we load the data (every 4 hours) these Country codes can (but normally don't) get changed but, given that we've got an Update statement, the fields are always updated by the SQL, even if it is to the same value.

This, as we've seen to our cost, brings the load process to its knees when the Indexes are kept in place !

So, my thinking was, via a simple call to a Stored Procedures, Drop the Join Indexes, perform the Load process and then with another simple call to a Stored Procedure, re-Create the Index.

It does mean of course that in that time frame of Drop-Load-ReCreate, the performance of that part of the overall system becomes, shall we say, less than ideal !

Anyway, any words of wisdom will be met with gratitude and humbleness, etc, etc.

Regards

David Clough
Database Developer
Database Design Group

	<Prev	Next>		<<First	<Prev	Next>	Last>>