All Forums Tools
Ansh 49 posts Joined 10/08
03 Jul 2009
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.

Adeel Chaudhry 773 posts Joined 04/08
03 Jul 2009

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.bteqThis way you can make very generic and dynamic BTEQ scripts as well! :)HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

CarlosAL 512 posts Joined 04/08
03 Jul 2009

Or (if in UNIX/Linux) you can use a 'here document' inside a shell script:#!/bin/bashTHE_DATE=2009-07-03 #This could be your $1 input argumentecho $THE_DATE #Just to show the datebteq << EOF.LOGON DB/user,password;EXEC Macro1('$THE_DATE');EXEC Macro2('$THE_DATE');.LOGOFFEOFHTHCheers.Carlos.

rluebke 65 posts Joined 11/05
15 Jul 2009

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

Raja_KT 1246 posts Joined 07/09
16 Jul 2009

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sunilsvh 2 posts Joined 07/09
23 Jul 2009

'&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.

Jimm 298 posts Joined 09/07
23 Jul 2009

1. Set up a file with the date parameter in (as a character string).So TestFile.txt contains a single line with:2009-01-022. Import his file every time you want to use it. So:.Import Report file = TestFile.txtUsing InDate (Char(10))Exec Macro1 (:InDate) ;.Import Report file = TestFile.txtUsing 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

srinivasarao 1 post Joined 02/13
13 Feb 2013

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.
 

Srini

KS42982 137 posts Joined 12/12
13 Feb 2013

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.

You must sign in to leave a comment.