Saturday, September 25, 2010

How to pass parameter to BTEQ script (Windows) ?

How to pass parameter to BTEQ script (Windows) ?

I was wondering whether we could pass parameters to Bteq script. After doing some research , found out a closest way to passing parameter to Bteq script.  Actually this involves creating of BATCH file".bat"  and passing parameter to it.


Let us try this for following select query
select * from user.tab_test1  where test1='2010';

STEP1:
The batch file is created as follows (script2.bat)
del script1.txt
echo .logon localhost/test,test >>script1.txt
echo select * from user.tab_%1%  where %1% ='2010'; >>script1.txt
echo .logoff >> script1.txt
echo .quit >> script1.txt
bteq < script1.txt


STEP2:
Run the batch files as follows
C:\>script2.bat test1





STEP3:
we can see following execution steps
C:\>del script.txt
C:\>echo .logon localhost/test,test 1>>script1.txt
C:\>echo select * from user.tab_test1 where test1='2010' ; 1>>script1.txt
C:\>echo .logoff 1>>script1.txt
C:\>echo .quit 1>>script1.txt
C:\>bteq 0

BTEQ 08.02.00.00 Thu Jul 18 09:33:12 2010

+---------+---------+---------+---------+---------+---------+---------+----
.logon localhost/test,

*** Logon successfully completed.
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 5 seconds.

+---------+---------+---------+---------+---------+---------+---------+----
select * from user.tab_test1  where test1='2010';

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

test1
-----------
2010

+---------+---------+---------+---------+---------+---------+---------+----
.logoff
*** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+---------+---------+----
.quit
*** Exiting BTEQ...
*** RC (return code) = 0

2 comments: