help... on passing parms into script

Tools & Utilities
Enthusiast

help... on passing parms into script

need to change the file date in BTEQ script every month (example 200706). what is the method that I can pass the Year and Month either in a file or any other methods. this date will be passed to other programs so changing every month is a pain in the butt. if I could find a method of changing only once and then cascade down..

Drop table vince_gabrielli.ss_cktlist_200706;
COMMIT;

-- Create table on EDW...
-- data will be loaded into this table

CREATE MULTISET TABLE vince_gabrielli.ss_cktlist_200706,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
( service varchar(10) CHARACTER SET LATIN CASESPECIFIC
,short_mkt varchar(6) CHARACTER SET LATIN CASESPECIFIC
,ckt_id varchar(24) CHARACTER SET LATIN CASESPECIFIC
)
PRIMARY INDEX( ckt_id );

COMMIT;
2 REPLIES
Enthusiast

Re: help... on passing parms into script

As per your explanation, I understand that your table definition does not change each month. If that is the case try the following script....

C:\>bteq < scr00.txt
.logon localhost/test,

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

*** Total elapsed time was 3 seconds.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
database vince_gabrielli;

*** New default database accepted.
*** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.set width 500;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.set titledashes off
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.os del scr01.txt;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.export report file = scr01.txt;
*** To reset export, type .EXPORT RESET
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
select 'CREATE MULTISET TABLE vince_gabrielli.ss_cktlist_'||
(date (format 'yyyymm'))||' as vince_gabrielli.ss_cktlist_'||
(date (format 'yyyy'))||(add_months(date,-1)(format 'mm'))||
' with no data;' As "--Create Table Statement";

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

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

select 'show table vince_gabrielli.ss_cktlist_'||
(date (format 'yyyymm'))||';' As "--New Table Definition";

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

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
select 'drop table vince_gabrielli.ss_cktlist_'||
(date (format 'yyyy'))||
(add_months(date,-1)(format 'mm'))||';' as "--Delete table Statement ";

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

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.export reset;
*** Output returned to console.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.run file=scr01.txt;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
--Create Table Statement
CREATE MULTISET TABLE vince_gabrielli.ss_cktlist_200707 as vince_gabrielli.ss_cktlist_200706 with no data;

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
--New Table Definition
show table vince_gabrielli.ss_cktlist_200707;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

CREATE MULTISET TABLE vince_gabrielli.ss_cktlist_200707 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
service VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC,
short_mkt VARCHAR(6) CHARACTER SET LATIN CASESPECIFIC,
ckt_id VARCHAR(24) CHARACTER SET LATIN CASESPECIFIC)
PRIMARY INDEX ( ckt_id );

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
--Delete table Statement
drop table vince_gabrielli.ss_cktlist_200706;

*** Table has been dropped.
*** Total elapsed time was 1 second.

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

Enthusiast

Re: help... on passing parms into script

It is also recommended to use a view so that you don't have to change the table name in all the scripts. And just have to bother about pointing the view to new table in the script where the table is created.