Bteq Script: Iterate through scripts in text file and remove the scripts which ran successfully.

Teradata Applications
Fan

Bteq Script: Iterate through scripts in text file and remove the scripts which ran successfully.

Hello Everyone,

 

I have a very specific question. I have a text file which has multiple sql scripts. I am able to run this using .run file. Now, some of these scripts run successfully and some of them fail with specific error code. I have if else and goto statements to handle these cases. I want to delete the scripts which executed successfully from the text file or write the queries which failed in some other file, so that I can only execute the failed queries next time. I am not sure if we can specify such things in .label clause based on error code.

 

Any help will be appreciated. Or if you have any other approach to accomplish this will be helpful.

 

Thank you. 

  • Teradata BTEQ
  • Teradata Script
4 REPLIES
Supporter

Re: Bteq Script: Iterate through scripts in text file and remove the scripts which ran successfully.

why not use the good old technic of log tables.

You can check before each sql if it was executed successfully - if not execute it, if yes skip the step.

 

If possible check the python module for teradata. It is much nicer to do this kind of logic with python...

 

 

Fan

Re: Bteq Script: Iterate through scripts in text file and remove the scripts which ran successfully.

Thank you! Yes, python module for Teradata seems to be a good way. Can you elaborate the log tables technique. Do I need to manually check because I cannot think of any method to check if query ran successfully through script (except checking error code.). But in this case how will I delete the script, if executed successfully.

Fan

Re: Bteq Script: Iterate through scripts in text file and remove the scripts which ran successfully.

Hi @ulrich,

 

Thank you so much. I am able to segregate the successful and unsuccessful scripts using python module. 

I have one concern, unlike bteq which returns the error code and then we can apply if else loops based on error codes python module doesn't return error codes. Although we can see the error code in error trace. Is there a way to retrieve error code (apart from long journey to convert the error tuple to dictionary) and keep the count of specific error codes.

Supporter

Re: Bteq Script: Iterate through scripts in text file and remove the scripts which ran successfully.

Check the below example...

To be able to react on an SQL error within the program you need to set

continueOnError=True

 

enjoy Ulrich

 

import teradata

udaExec = teradata.UdaExec(appName="HelloWorld", version="1.0",
                           logConsole=False)

session = udaExec.connect(method="odbc", system="zzz",
                          username="zzz", password="zzz");

cursor = session.execute("drop table test",continueOnError=True)
print(cursor.error)
cursor = session.execute("create table test (a nteger) no primary index",continueOnError=True)
print(cursor.error)
cursor = session.execute("select * from dbc.whatever",continueOnError=True)
print(cursor.error.code)