Using Parameter in BTEQ

Tools
N/A

Using Parameter in BTEQ

Hi All,

I have a requirement in which I need to pass the same DATE parameter to a set of macros.I want to incorporate all the macros in one bteq script so that the user can just specify the parameter value and all the macros are executed at one go.

Can anybody please tell me how can I accomplish this using Bteq?It would be better if you could provide me with a sample script for it.

I could have created one macro and have included all the SQL statements in it in sequence order and have simply executed the macro specifying the date parameter.But I have few Collect Stats statements in between.I have learnt that we cannot specify more than 1 collect stats in a single macro thats the reason I am switching towards Bteq.

Thanks for your quick response.
8 REPLIES
Teradata Employee

Re: Using Parameter in BTEQ

Hello,

Following can solve your problem:

Suppose we have BTEQ script as Script.bteq, which contains following calls (SP or Macro whatever it may be):

.logon .... ;
EXEC Macro1(DATE_PARAMETER_VALUE);
EXEC Macro2(DATE_PARAMETER_VALUE);
.logoff;

What you can do is to write a shell-script (if using Unix) or dos-script (if using MS Windows), which does the following:

- Take the input value of date from user
- Create a copy of Script.bteq to ScriptReady.bteq
- Replace the string "DATE_PARAMETER_VALUE" to value of date in ScriptReady.bteq
- Execute ScriptReady.bteq
- On execution completion (or error handling), delete ScriptReady.bteq

This way you can make very generic and dynamic BTEQ scripts as well! :)

HTH!

Regards,

Adeel

Re: Using Parameter in BTEQ

Or (if in UNIX/Linux) you can use a 'here document' inside a shell script:

#!/bin/bash

THE_DATE=2009-07-03 #This could be your $1 input argument

echo $THE_DATE #Just to show the date

bteq << EOF
.LOGON DB/user,password;

EXEC Macro1('$THE_DATE');
EXEC Macro2('$THE_DATE');

.LOGOFF

EOF

HTH

Cheers.

Carlos.

Re: Using Parameter in BTEQ

The two answers above are OS workarounds. (You will see a lot of them on this forum for the TD command line utilities. They are very primitive and haven't really been updated with new features in a very long time.) You would do well to investigate Teradata Parallel Transporter (TPT). While still lacking in some areas, it does have variables you can set via a file or command line. TPT has a pretty steep learning curve, but once you get the idea of what they are trying to accomplish and how it works, it is a real swiss army knife. We are replacing many of our BTEQ, fastload and fastexport scripts with it. I am sorry to say other than the Teradata documentation, there really isn't much help on how to learn how to use it.

R

Re: Using Parameter in BTEQ

Hi,

I m not sure in BTEQ. iT WORKS FINE IN MLOAD,FASTLOAD,TPUMP. It works this way:(ASSIGN THE DATE TO A VARIABLE and use that variable).Let me know if it works. I m not able to test it.

.SET DD TO '&SYSDATE'

then use DD anywhere you like.

Let me know if it works.

Raja

Re: Using Parameter in BTEQ

'&SYSDATE' will not satisfy the above requirement I guess as it will take the server date,but the User will be providing the date as per the above requirement.

Correct me if I am wrong.
N/A

Re: Using Parameter in BTEQ

1. Set up a file with the date parameter in (as a character string).
So TestFile.txt contains a single line with:

2009-01-02

2. Import his file every time you want to use it. So:

.Import Report file = TestFile.txt
Using InDate (Char(10))
Exec Macro1 (:InDate) ;

.Import Report file = TestFile.txt
Using InDate (Char(10))
Exec Macro2 (:InDate) ;

(That assumes your macro expects a char date; convert it in the macro.)

You need to qualify the filename with the location, depending on your OS. Use a DDNAME if it is mainframe.

HTH

Re: Using Parameter in BTEQ

Hi Carl,

Please help me in resolve the issue facing while executing a script in Unix environment.

I'm trying to pass database name by an unix variable like below

dbaname='my_database'

echo $dbaname

bteq</home_dir/myname/myfile.sql

But it is unable to executing the script and throughing below error.

 *** Warning: You must log on before sending DBC/SQL requests.

 *** BTEQ exiting due to EOF on stdin.

 *** Exiting BTEQ...

 *** RC (return code) = 2

Even I did keep my logon script with in myfile.sql

Please help me to resolve the same.

Re: Using Parameter in BTEQ

Do you have the logon script in the beginning (the very first line) of the BTEQ ? If not, then it will give you this error. If it is, then just make sure that it is the correct script/statement. If it still creates an issue, please share the bteq and can look into it.