I have set up a BTEQ of an SQL code through PUTTY.
The code runs weekly. However, I run a drop statement and then a create table statement, thus losing the table of the previous week. Is there anyway for me to create a table of the following format so that I retain all the tables that I have created ?
CREATE TABLE TABLE_NAME_<CURRENT_DATE> AS
(SEL * FROM XYZ)
Please let me know at the earliest! Thanks in advance.
You can put your bteq script in a unix script and pass unix date to your create table or you can pass it as parameter from your unix script.
Another choice, to avoid this , you may have SVN, VSS, PVCS or SCM tools where you can checkin/out.
bteq << td_bteq_content
.SET TITLEDASHES OFF
select 1 from dbc.TablesV where databasename = database and TabkeName = 'table_$CURR_DT';
.IF ACTIVITYCOUNT = 0 THEN GOTO OK
DROP TABLE TABLE_$CURR_DT;
CREATE TABLE TABLE_$CURR_DT AS (SEL * FROM TABLEXXX) WITH DATA;
you can write a unix shell script to pass the parameter to the table name. Below code can help you.
I have a question with yout script. If the CREATE TABLE is in a file (e.g. CreateDynamicTable.sql) how can I to call the file and it take the variable $CURR_DT? That because I tried execute a file with .RUN FILE and it does'nt take the variable and it interprates as a String
$CURR_DT would have to be exposed to the shell to get evaluated. In other words, somehow CreateDynamicTable.sql needs to be transformed into a shell script that runs bteq and includes the contents of the SQL. You could manually edit the .sql file and create a CreateDynamicTable.sh file, or you could write another general-purpose shell script that would dynamically create such a file and execute it. The .sh file would look something like:
bteq <<EOF >something.out
<------ place contents of CreateDynamicTable.sql here --------->