Need help on BTEQ!!! In our project our client asked us to maintain all audit information bteq load.
We need to maintain source and target row processed count - how many rows processed ? count for source rows? count for target rows? This count should get inserted in audit table.[Please suggest query] - I can get row count using activity_count in procedure but i dont want to user proc.
Also need to maintain status of job. If bteq is running then 'Running' status should get inserted into table, once completed/ failed, status should get updated accordingly.[Please suggest query]
Also need to maintain Error handling if bteq fails.[Please suggest query]
We have to write this bteq in UNIX script
Please suggest best approch to work on such requirement.
A great question. I've struggled with this in the past.
In theory, you can do this, by sending the bteq output to a file, then reading it back in, but it's a nightmare and the path to madness.
BTEQ has been around for years, and it does what it does; but it doesn't do what you want.
In my experience, the requirement to log everything often comes from working on Oracle or other non-Teradata database systems, which often take a very different approach to Teradata when building ETL processes. Historically, if you want to know how many rows were processed, you can either look in the table, or look in the bteq logs.
Consider writing a generic stored procedure to run your SQL. You can pass in the SQL as the parameter, run it as dynamic SQL, capturing activity count, error code, error text and logging these as you with. Then just call this procedure from your bteq script.
Alternatively, look at using something other than BTEQ. I have heard great things about python integration, or udaSQL, there are some great examples elsewhere on this site.
Have a play, try a few things out. Beware of building something that is hard to understand or support.
Longer term, try to focus effort around business benefit and ROI, not working really hard to log lots of details that nobody will every look at after the first week.
Hope this helps,
THanks for your reply.
Just headsup... We are writing shell script where we are calling Bteq in shell.
so if that bteq exe successfully we are taking its source and tgt count from log file and checking if its correct ot not.
AT the start of the script we are putting entry as "Start" and after end its "Completed". If it fails in between we have to put entry as "Fail" with proper error message i.e why it failed? whr to contact for resolution? what is issue n all... (Lil bit tough but still working )
Hope we will resolve and complete it soon.
hey tushar, we are looking for the exact solution in our project. Did you get a change to develop the shell script..please share the logic if you did.
I am also looking to capture the audit log from the BTEQ script similar to what you mentioned in your post.
Do share your resolution if you got any.