batch scripts to run batch scripts in bteq or queryman

[muleskinner]

Hi

In order to do QA on data, I would like to prepare a set of SQL scripts and then run these as a batch script. For example:

q1.txt
q2.txt

are two scripts that each contain SQLs. I would like to create a q3.txt that contains:

run q1.txt
run q2.txt

and then run it using "run q3.txt". I tried this and it failed. I can run q1 and q2 individually, but that gets old in a hurry. Any ideas?

(The equivalent in Oracle would be a q3.sql that contains
@q1.sql
@q2.sql
which I could then run using @q3.sql).

 

[craig322]

What I have done to run multiple scripts is embed them
in a shell script invoking bteq to run the scripts. I am
running them from a unix platform. I use this process
to run all of our data loads each month, this way there
is no operator intervention. Once they are in a script
you can use cron to run the script.

The basic syntax inside a ksh is as follows. We pass in
our username and password for security, but you can just
embed them in the script.

bteq << EOF
.logon username,password;

SELECT *
FROM table1;

SELECT *
FROM table2;

.EXIT
EOF

Craig

 

[muleskinner]

Hi Craig,

thanks for the tip. Sadly, I am running on an NT machine and do not have the comforts of unix available to me. What you described can be done in queryman, i.e., executing a script with multiple sqls, each separated by a semicolon. It's a bit messier than individual scripts, but for want of something better, I'll probably have to go that route.

BTW, your example answered another question I had but could not test for lack of unix access, namely if BTEQ can be called up in a here document. I used this mechanism quite extensively in Oracle for sqlplus access within ksh scripts and found it very easy to work with. I had been wandering if the same could be done with BTEQ (which your example illustrated very nicely).

Hermann

 

[seidub]

As you mentioned, you can create q3.txt to run both q1.txt and q2.txt by putting the lines below in q3.txt.
.run file=q1.txt
.run file=q2.txt

So your q3.txt will look something like

/*******************/
.logon usr/passwd;
.run file=q1.txt
.run file=q2.txt
.exit
/*******************/

 

[tdatgod]

Hi,
The BTEQ command for running a file is

.run file = blort

where blort is the name of your file.

bteq
.logon tdpid/user,password
.run file = blort1
.run file = blort2
quit;

actually if you don't like having the Password embeded in the script you could actually make a third script...

bteq
.run file = blort3
.run file = blort1
.run file = blort2
quit;

where in blort3 you put just

.logon tdpid/user,password


however I don't know the full path extended naming for blort on Windows. On UNIX I don't hvae a problem.

On WINDOWS does anyone know the full path extended naming for the blort portion of the .run file = syntax?

is it...

c:\temp\myscript.sql

or

&quot;c:\temp\myscript.sql&quot;

or

&quot;c:\\temp\\myscript.sql&quot;

or

some combination of the above?


Anyway, the easiest way to run your script is to use WINDOWS input redirection. it is identical to the Korn shell on UNIX.

bteq < inputfile > outputfile 2>&1

( the 2>&1 tell STDERR to be redirected to STDOUT which is redirected by the > to outputfile. )


now input file should contain your full logon, desired SQL, and a QUIT; to tell BTEQ you are done.



I see for your post you don't like embeding the password in the script. However even you wrote a BAT file or a PERL script to concat $1 (username) and $2 (password) into a temp file and then ran that temp file against BTEQ and then deleted, if you used AT to run the script you would have to provide all the command line arguements to the AT program and therefore anyone looking at the AT scheduler would still be able to see the username and password.


Perl program.

Now on the other hand you could write a BAT file or a PERL program to generate the bteq script into a file and then run it against BTEQ. Here is a sample perl program since it will work anywhere PERL is supported where as BAT is kind of WINDOWS centric.


perl myscript &quot;tdpid/username,password&quot;

here is a simple version of myscript....

$mylogon = $ARGV[0];

open OF, &quot;> bteq.in&quot;;
print OF &quot;.logon $mylogon\n&quot;;
print OF &quot;sel * from dbc.dbcinfo;\n&quot;;
print OF &quot;quit;\n&quot;;
close OF;

system (&quot;bteq < bteq.in > bteq.out&quot;
unlink &quot;bteq.in&quot;;





Now as for scheduling it, I don't know but I thought in the Windows NT resource KIT there was an 'AT' command used for scheduling items to run at certain times.

Windows 2000 on the other hand has this built into the main product and you don't need the resource kit any more ( although the windows 2000 resource kit still contains a few net utilities ).

I heard the windows 2000 version of scheduling in far superior to the Windows NT 4.0 stuff.

Personally I have never used either so I couldn't tell you.


hope this helps.....

 

[tdatgod]

Hi,
I should point out that if your Teradata Server is the Windows 2000 version As opposed to the MPRAS version, the latest version of the software V2r4.1 supports what microsoft calls Single Domain Sign-on.

Sign on once to your Windows workstation Domain and every resource on the Domain is available to you without requiring a separate password. This now includes your Windows 2000 Teradata database server.

This isn't available on MPRAS because we use an Open Standard Security library which no one has ported to MPRAS yet. Also the authentication server is a feature of Windows Domain controller.

You logon on to your Windows NT or 2000 box
you start up BTEQ
you type .logon tdpid/ ( no username,password )

CLI obtains your NT Domain Logon token ( Name and authentication information) and passes that to Teradata as you logon.

Teradata validates it and if acceptable logs you on.

Therefore your scripts no longer have to have your user,password embeded in them.

The only draw back in the first implmenation is the Teradata Userid must be in the form.....

&quot;user@domain&quot;

where user is your Windows Userid and
domain is your windows domain name.

This is the only way we could validate bill@domain1 and bill@domain2 are 2 distinct people. We couldn't allow them both to logon to account BILL since maybe that is the financial departments database.

therefore you must

create user &quot;user@domain&quot; as perm = x, password = blort;

Password is kind of MOOT, Although you can still logon on with username,password if you aren't on your workstation for example so I guess maybe the password isn't MOOT.

The &quot;quotes&quot; are required because the parser will choke on the @ sign if you don't quote it.


Just thought I would point this out. If you would like more information on this feature let me know.

 

[muleskinner]

Tnx to all for the advice. My idea of a q3.txt containing the scripts q1.txt and q2.txt will apparently work in BTEQ, but not in Queryman. In Queryman it runs q1.txt and then
returns, cheerfully waiting for the next input. The q2.txt in the script is completely ignored.

BTEQ will apparently do what I want - after a fashion. Perhaps a bit clumsier than I would have liked, but fot the moment, the results are what count.

 

[TheTeradataKid]

The only thing that I could suggest is to paste your code for both scripts into one and run it.