Macros and logging output

Database

Macros and logging output

Is there a way to output the result of a MACRO to a log?

i.e. MACRO1 has a couple of selects, several updates, and additional selects -- I'd like to output the record counts (processed) from each statement,

i.e. "UPDATE completed. 100 rows processed." "SELECT completed. 5 rows returned;" etc.

... rather than just the total result of the MACRO, which is what I am current seeing, i.e. "Statement 1: EXECUTE completed. 211014 rows processed."

The reason I am asking, is I have just jumped on board with MACROs (previously just using SQL scripts). I converted all of my SQL scripts into MACROS (about 40 MACROS). Then created a single master MACRO that EXEC's each of the 40 MACROs in order. However, I can no longer track the individual results that are occurring in each MACRO.

Any suggestions? Am I going about this correctly?

thanks! Mark
Tags (1)
2 REPLIES
Junior Contributor

Re: Macros and logging output

Hi Mark,
did you know, that all SQL contained in a macro is a Multi-Statement-Request and MSRs are treated as a single transaction?
Do you actually want to run all those 40 macros as a single huge transaction?

Back to your problem:
If you run the "exec macro" in a BTEQ script, you should see your expected output.
But if you execute it with SQL Assistant you get that combined number of rows.

If you actually need all those details, the best way is to use Stored Procedures where you can group those INS/UPD/DEL into transactions and insert all neccessary information about rows processed into a log table.

Dieter

Re: Macros and logging output

Hi,

I am trying to execute the below macro in the SQL Assistant:

Execute ResCPUbyAmpOneNode('2014-04-22','2014-04-23',080000,080000,'001-01');

the error message received was:

EXECUTE Failed.  [100016] Invalid Teradata response message sequence.

Before executing this macros, I have also enabled the logging for resusage tables and I can see the output for all the resusage tables. Still I am unable to execute the macro. Any help in this issue would be appreciated.

Thanks,

Stanley.