TERADATA BTEQ LOGS GENERATION

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

TERADATA BTEQ LOGS GENERATION

Hello Experts,

 

I have a situation where I have to develop BTEQ scripts for migration and put them into WINSCP server and call them on an adhoc basis using control M scheduler. While most of the part is quite clear, kindly advice how can I generate log files for the BTEQ script and receive it in my email address? Kindly provide a process for me to achieve this. Thanks in advance.


Accepted Solutions
Senior Apprentice

Re: TERADATA BTEQ LOGS GENERATION

Hi,

 

BTEQ will automatically generate a 'log', a plain text file  which is written to 'standard out' on Linux/Windows systems.

 

A common command line to run Bteq is;

Bteq <input_script_file.txt > log_file.txt

 

There are additional options which allow you to include error messages in your main log file. See (https://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/Query_Management_Tools/B035-2414-086...) for more info.

 

There is no built-in capability to send emails. You will have to set up something outside of Bteq to do this. Linux does (or used to) have a ssmtp package which might help you.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
9 REPLIES
Senior Apprentice

Re: TERADATA BTEQ LOGS GENERATION

Hi,

 

BTEQ will automatically generate a 'log', a plain text file  which is written to 'standard out' on Linux/Windows systems.

 

A common command line to run Bteq is;

Bteq <input_script_file.txt > log_file.txt

 

There are additional options which allow you to include error messages in your main log file. See (https://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/Query_Management_Tools/B035-2414-086...) for more info.

 

There is no built-in capability to send emails. You will have to set up something outside of Bteq to do this. Linux does (or used to) have a ssmtp package which might help you.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: TERADATA BTEQ LOGS GENERATION

Hey Dave, Your answer was partly helpful, but assume I have my BTEQ script in desktop and I execute it using the run command. After the execution, I want the logs to goto a default folder in my desktop(say /desktop/logs) from where I can configure my mailbox process. Kindly guide me to achieve this.

Senior Apprentice

Re: TERADATA BTEQ LOGS GENERATION

No problem, just change the output folder in the command line.

 

Something like: bteq <input_file >desktop/logs/output_file

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: TERADATA BTEQ LOGS GENERATION

Hey what I am trying to achieve is:

I have a BTEQ script which does data migration (simple insert). I invoke that BTEQ script using .RUN FILE scriptpath and Name in the terminal. The script is running fine and I am getting the log or run information in the terminal.

What I wanted to achieve is to get the log as a file and subsequently emailing to myself using the ssmtp thing in unix. I am not able to achieve this.

I have tried using the BTEQ <script name> logfile command. The command is not able to run the file as the logon details are there in the BTEQ script and it is asking me to logon first explicitly (think it is not able to read the script without the run command). Kindly give me a step by step procedure to achieve this as I am relatively new to teradata.

Senior Apprentice

Re: TERADATA BTEQ LOGS GENERATION

Hi,

 

There should not be any need to use the 'run file' command, you should be able to run the bteq script using the '<' and '>' redirection symbols.

 

Can you share your current scripts? (replace the actual username & password details with x's).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: TERADATA BTEQ LOGS GENERATION

One thing just came to mind. Are you trying to 'nest' ".run file" commands? That is not supported within bteq. You can go 'down' one level but then your scripts need to come 'up' again to the top level script before using another '.run file' command.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: TERADATA BTEQ LOGS GENERATION

In general you have to code your scripts as:

A unix shell script which is run by your scheduler (I think you said control m):

#!/bin/bash
cd folder-containing-bteq script
/usr/bin/bteq <top-level-bteq.txt >logs/process.log 2>&1

The file "top-level-bteq.txt" is your main bteq script and (typically) it is this one which logs on to TD.

.run file = logon.txt

execute an sql statement here;

execute another sql statement here;

.run file = yet-another-sql.txt;
.quit 0;

File "logon.txt" contains your logon statement:

.logon tdsystem/username,password;

File "yet-another-sql.txt" contains more sql commands:

execute an sql request;

do some more sql stuff;

Does that make sense?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: TERADATA BTEQ LOGS GENERATION

Got it.. Yup that was really helpful.. Cheers..

Enthusiast

Re: TERADATA BTEQ LOGS GENERATION

Hey Dave..works perfectly.. Thank you so much..  The shell scripting part was new to me.. Followed your example. Now I will look to setup the email client..