All Forums Tools
goldminer 118 posts Joined 05/09
12 Jun 2014
BTEQ - Executing commands after .run file statement

Hi all,
 
I have created a bteq script that basically gathers a bunch of show table, show view, etc. statements and runs the file to create the ddl file.  I use this to perform migrations.
 
I would like to be able to export to a new file immediately after the .run file statement in the script.  My goal is to append the contents of this new file to the end of the file created by the .run file command.  The script goes something like this:
 
export format.txt
sel 'show table' dynamic sql
sel 'show view' dynmaic sql
etc...
.run file format.txt creating ddl.txt
export another.txt
add data to another.txt
append data in another.tx to the end of ddl.txt
quit
 
No matter what I do, I cannot get the script to execute any statements past the .run file format statement.  Is there no way around this?  I pulled this from the bteq documentation:
 
If you specify a RUN command within a run file, BTEQ switches and begins reading from the
new file; BTEQ does not return to the previous file.
 
Can there be nothing executed after the .run file command in my example above? 
 
I simply drop the script into a bteqwin window to be executed.
 
Thanks,
 
Joe
 
 

Raja_KT 1246 posts Joined 07/09
12 Jun 2014

I think this is what you are looking for.

In bteq you can execute os commands, using .os 

 

example:

.if errorcode = 0 then .os echo  '.goto abcdefg' > bteq_restart_step.txt;

(what it means is if errorcode=0 then redirect '.goto abcdefg' to a file bteq_restart_step.txt)

 > is for writing in unix/linux. >> is for appending. So somewhere in your script you can implement this logic.

 

Another os command example to remove a file:

 .os rm -f bteq_restart_step.txt;( Please check the directory)

In windows platform, I have not tried > and >> :), but it should be same.

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.

goldminer 118 posts Joined 05/09
12 Jun 2014

This is similar to what I am trying to accomplish.  I cannot get any command to execute after the last .run file statement similar to what is in this script.  Is this a feature?
 
.SET ECHOREQ ON
.SET RETRY ON
.SET TIMEMSG DEFAULT
.SET ERRORLEVEL 3807 SEVERITY 0;

.run file logon.txt
.set width 200
.set titledashes off
.set format off
.set rtitle ''

.export report file=del_app_work.run

SEL 'DEL FROM ' ||Trim(Databasename)||'.'||Trim(Tablename)||';' (Title '')
FROM DBC.TABLES
WHERE CREATORNAME='xyz'
AND TABLEKIND='T'

;

.Export report file=del_app_work.sql
.run file = del_app_work.run

.logoff

.quit
 

Raja_KT 1246 posts Joined 07/09
12 Jun 2014

How about .EXPORT RESET?
Is there anything extra in logon.txt????????

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.

goldminer 118 posts Joined 05/09
13 Jun 2014

Thanks Raja!
 
If I drop my script into a bteqwin window, it stops after the .run file command.  If I run the same script using a *.bat file (bteq < script.txt > script.out) all the statements after the .run file command execute beautifully.  Guess I have a workaround but not sure why it does not work in bteqwin.
 
Joe

You must sign in to leave a comment.