BTEQ - Executing commands after .run file statement

Tools & Utilities
Enthusiast

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

4 REPLIES
Enthusiast

Re: BTEQ - Executing commands after .run file statement

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.

Enthusiast

Re: BTEQ - Executing commands after .run file statement

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

Enthusiast

Re: BTEQ - Executing commands after .run file statement

How about .EXPORT RESET?

Is there anything extra in logon.txt????????

Enthusiast

Re: BTEQ - Executing commands after .run file statement

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