BTEQ (PGM=BTQMAIN) on z/OS Mainframe

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Enthusiast

BTEQ (PGM=BTQMAIN) on z/OS Mainframe

Hi,

 

Is there a way to pass variables (SYMBOLICS) to BTEQ (PGM=BTQMAIN) on z/OS in JCL ?
I tried this and it works fine for instream but i need to put in a PDS because instream SYSIN is not allowed.
//*------------------------------------------------------------------*//
//JOBLIB  DD   DSN=SYS3A.TERADATA.APPLOAD,DISP=SHR
//        DD   DSN=SYS3A.TERADATA.TRLOAD,DISP=SHR
//        DD   DSN=SYS3A.TERADATA.TPTLOAD,DISP=SHR
//*------------------------------------------------------------------*//
//E1  EXPORT SYMLIST=(DAY)
//S1  SET    DAY='15'
//*------------------------------------------------------------------*//
//JS0010  EXEC PGM=BTQMAIN
//SYSPRINT DD  SYSOUT=*
//SYSTERM  DD  SYSOUT=*
//SYSOUT   DD  SYSOUT=*
//LOGON    DD  DISP=SHR,DSN=USERID.LOGON(MYLOGON)
//SYSIN    DD  *,SYMBOLS=JCLONLY
.SET ERRORLEVEL (3807,2580) SEVERITY 0;
.RUN FILE LOGON;
 SELECT DATE-&DAY;
 .IF ERRORCODE  = 0 THEN .QUIT 0;
 .LOGOFF;
.QUIT;
// 

 *** Teradata Database Release is 15.10.05.04                         
 *** Teradata Database Version is 15.10.05.04                         
 *** Transaction semantics are BTET.                                  
 *** Session Character Set Name is 'EBCDIC'.                          
                                                                      
 *** Total elapsed time was 0.09 seconds.                             
                                                                      
+---------+---------+---------+---------+---------+---------+---------+
 *** Encountered EOF on RUN file. Returning to stdin.                 
+---------+---------+---------+---------+---------+---------+---------+
SELECT DATE-15;                                                       
 *** Query completed. One row found. One column returned.             
 *** Total elapsed time was 0.04 seconds.                             
17/09/22                                                  SELECT DATE-15
                                                             (Date-15)
                                                             ---------
                                                              17/09/07

I tried SYMNAMES and that would not work

 

//JOBLIB  DD   DSN=SYS3A.TERADATA.APPLOAD,DISP=SHR
//        DD   DSN=SYS3A.TERADATA.TRLOAD,DISP=SHR
//        DD   DSN=SYS3A.TERADATA.TPTLOAD,DISP=SHR
//*------------------------------------------------------------------*////JS0010  EXEC PGM=BTQMAIN                             
//SYSPRINT DD  SYSOUT=*                                
//SYSTERM  DD  SYSOUT=*                                
//SYSOUT   DD  SYSOUT=*                                
//SYMNOUT  DD  SYSOUT=*                                
//SYMNAMES DD  DISP=SHR,DSN=USERID.SYMLIB(TEST)          
//LOGON    DD  DISP=SHR,DSN=USERID.LOGON(MYLOGON)  
//SYSIN    DD  DISP=SHR,DSN=USERID.PARMLIB(TESX)          
//                                                     

 

 

 

 

3 REPLIES
Apprentice

Re: BTEQ (PGM=BTQMAIN) on z/OS Mainframe

Hi,

If you can then this will be an OS feature, substitutable variables are not a feature offered by BTEQ.

Normally to do something like this (on any OS) you have to 'pre process' the BTEQ script using an OS platform scripting capabilities (REXX on the m/f?), making substitutions and then pass the resulting script to the BTEQ program.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
New Member

Re: BTEQ (PGM=BTQMAIN) on z/OS Mainframe

If you wrote a REXX exec like:

/*********************************** REXX ****************************/
/* This exec writes parm to OUTPUT                                   */
/*********************************************************************/
PARSE ARG '/' parm '/' .                                              
IF parm = '' THEN EXIT 0                                              
outline = parm                                                        
PUSH outline                                                          
"EXECIO 1 DISKW output (FINIS)"                                       
EXIT 0                                                                

 

And coded the JCL as:

//*------------------------------------------------------------------*//
//JOBLIB  DD   DSN=SYS3A.TERADATA.APPLOAD,DISP=SHR                     
//        DD   DSN=SYS3A.TERADATA.TRLOAD,DISP=SHR                      
//        DD   DSN=SYS3A.TERADATA.TPTLOAD,DISP=SHR                     
//*------------------------------------------------------------------*//
//E1  EXPORT SYMLIST=(DAY)                                             
//S1  SET    DAY='15'                                                  
//JS0005   EXEC PGM=IRXJCL,                                            
// PARM='PRM2CRD /  SELECT DATE-&DAY;/'                                
//SYSEXEC  DD   DSN=EXEC.LIB,DISP=SHR                                  
//SYSTSPRT DD   SYSOUT=*                                               
//SYSTSIN  DD   DUMMY                                                  
//OUTPUT   DD   DSN=&&OUTPUT,UNIT=SYSDA,                               
//         SPACE=(TRK,1),LRECL=80,RECFM=FB,                            
//         DISP=(NEW,PASS)                                             
//*------------------------------------------------------------------*//
//JS0010  EXEC PGM=BTQMAIN                                             
//SYSPRINT DD  SYSOUT=*                                                
//SYSTERM  DD  SYSOUT=*                                                
//SYSOUT   DD  SYSOUT=*                                                
//LOGON    DD  DISP=SHR,DSN=USERID.LOGON(MYLOGON)                      
//SYSIN    DD   DSN=CARD.LIB(CARDS1),DISP=SHR                          
//         DD   DSN=&&OUTPUT,DISP=(OLD,DELETE)                         
//         DD   DSN=CARD.LIB(CARDS2),DISP=SHR                          
//                                                                     

Where EXEC.LIB is where you stored the REXX exec and

CARD.LIB(CARDS1) contained
.SET ERRORLEVEL (3807,2580) SEVERITY 0;
.RUN FILE LOGON;                       

and CARD.LIB(CARDS2) contained
 .IF ERRORCODE  = 0 THEN .QUIT 0;
 .LOGOFF;                       
.QUIT;                          

It might meet your needs.

 

Enthusiast

Re: BTEQ (PGM=BTQMAIN) on z/OS Mainframe

Rexx is a great language and have coded it for over 27 years on (AS/400, iSeries), VM, (OS/390,z/OS), and PC but in this case

 

ICETOOL and SORT with SYMNAMES works better

 

SORT FIELDS=COPY                  
OUTREC FINDREP=(INOUT=(Find,Replace,
                                                   

Sample job

 

//MYUSERIC JOB (00105),'PROGRAMMER',                                   
//             MSGCLASS=U,CLASS=0,NOTIFY=&SYSUID
//*------------------------------------------------------------------*//
//PS0010  EXEC PGM=IEFBR14
//DD1      DD  DSN=MYUSERID.TEST1,
//             DISP=(MOD,DELETE,DELETE),
//             SPACE=(TRK,(1,1),RLSE)
//*------------------------------------------------------------------*//
//PS0020  EXEC PGM=ICETOOL
//TOOLMSG  DD  SYSOUT=*
//DFSMSG   DD  SYSOUT=*
//SYMNOUT  DD  SYSOUT=*
//SYMNAMES DD  DISP=SHR,DSN=MYDATASET(SYMNAME)<----Symbols, All system symbols are available like S'&LYYMMDD' and S'&LWDAY' define Find/Replace SYSMBOL with values
//*-In---------------------------------------------------------------*//
//IN1      DD  DISP=SHR,DSN=MYDATASET(BTEQSCRT)<- Bteq Script with variables you want to change
//*-Out--------------------------------------------------------------*//
//OUT1     DD  DSN=MYUSERID.TEST1, <- your bteq SYSIN
//             DISP=(NEW,CATLG,DELETE),
//             SPACE=(CYL,(1,1),RLSE),
//             DCB=(LRECL=132,RECFM=FB)
//*-Controls---------------------------------------------------------*//
//TOOLIN   DD  DISP=SHR,DSN=MYDATASET(TOOLIN) <- Sort copy statement 
//CTL1CNTL DD  DISP=SHR,DSN=MYDATASET(CTRL) <- Sort OUTREC FINDREP=(INOUT=(Find,Replace))