invoking BTEQ from Mainframe?

Tools & Utilities
Enthusiast

invoking BTEQ from Mainframe?

Currently I am using following to update date column in Teradata on Mainframe platform.

- There is one Job which copies the date stored in a DATASET in YYYYMMDD format to SNAP_DT.TXT file on DATASTAGE EE server.
- Another Job then takes SNAP_DT.TXT file as a source, and Multi-Load this field to Teradata table: CURRENT_SNP_DATE.

Everything works, but DBA suggested its inappropriate use of Multi-Load, as there is only One solum in CURRENT_SNP_DATE table and its getting updated only once in a month with one record (YYYYMMDD field), and suggested to use BTEQ as a SYSIN (Insert/update) instead.

Here is a new requirement:

-In one JCL Job, I want to accept input from Dataset in YYYYMMDD format, and take that input into BTEQ (as a SYSIN using INSERT) to update CURRENT_SNP_DATE table.

Please provide sample of JCL, and BTEQ (SQL). how would I incorporate these changes as BTEQ, instead of Multi-Load. I am a beginner, so please assist as much as you can.

I appreciate your time, and assistance in advance.
2 REPLIES
Enthusiast

Re: invoking BTEQ from Mainframe?

Here is an example using a macro to carry out the functionality requested...

The first 3 steps can be done on SQL assistant...

/* Step 1 - Create the table (may already exist in your case) */
CREATE TABLE
mytest
,NO FALLBACK
,NO JOURNAL
(mydate DATE
)
;

/* Step 2 - Create the macro */
REPLACE MACRO mymacro(
new_DATE DATE
)
AS
( UPDATE mytest
SET mydate = :date_from_dataset (DATE,FORMAT 'YYYYMMDD')
;
);

/* Step 3 - Insert a Date into the table (may already exist for you) */
INSERT INTO
mytest
SELECT
DATE

;

Below is the JCL to run the program (obviously this will change according to your environment). The dataset 'MYDATASET.MYDATE' is just an 8 byte dataset with the date stored as 20071202 etc...

//ACC#TEST JOB (TERADATA),
// 'TERA',MSGCLASS=X,TIME=(2,05),NOTIFY=&USERID
//*
//*
//DATEUPD EXEC PGM=BTQMAIN,
// PARM='=ERRORLEVEL=OFF',REGION=0M
//*
//MYDATE DD DSN=MYDATASET.MYDATE,DISP=SHR
//STEPLIB DD DSN=SYS3.DBC.&VDBC..APPLOAD,DISP=SHR
// DD DSN=SYS2.DBC.&VDBC..TRLOAD,DISP=SHR
//*
//SYSPRINT DD SYSOUT=D
//*
//SYSIN DD *
.LOGON ;

DATABASE MYDATABASE ;

.IMPORT DATA DDNAME=MYDATE ;

USING (
MYDATEFROMFILE CHAR(08)
)

EXEC MYMACRO (CAST(:MYDATEFROMFILE AS DATE FORMAT 'YYYYMMDD'));

.LOGOFF

//SYSOUT DD SYSOUT=D
//

Hope this helped...

Enthusiast

Re: invoking BTEQ from Mainframe?

Lukekay thankyou very much for taking so much trouble for me! appreciate your help again! :-)