Can we execute SQLs using JCL.
Can we execute SQLs using JCL.
Hi,
Can we SQLs using JCL instead of QMF or SPUFI? Could you please share a JCL to do this, if it is possible.
Thanks for any help.
Can we SQLs using JCL instead of QMF or SPUFI? Could you please share a JCL to do this, if it is possible.
Thanks for any help.
-
- Global Moderator
- Posts: 588
- Joined: Wed Nov 20, 2013 11:53 am
- Location: Mars
Re: Can we SQLs using JCL.
There are quiet a few programs and utility that could be executed by JCL
For ex:
LOAD,UNLOAD,DSNTIAUL,DSNTIAD,DSNTEP2
Google for more information on each one of them
For ex:
LOAD,UNLOAD,DSNTIAUL,DSNTIAD,DSNTEP2
Google for more information on each one of them
zprogrammer
- Anuj Dhawan
- Founder
- Posts: 2806
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Can we execute SQLs using JCL.
Below listed JCLs are some of the example of executing SQL queries in JCL:
Suppose you want to unload the rows for department D01 from the project table. You can fit the table specification on one line, and you do not want to execute any non-SELECT statements, so you do not need the SQL parameter. Your invocation looks like this:
Now suppose that you also want to use DSNTIAUL to do these things:
These examples are taken from DSNTIAUL documentation.
Suppose you want to unload the rows for department D01 from the project table. You can fit the table specification on one line, and you do not want to execute any non-SELECT statements, so you do not need the SQL parameter. Your invocation looks like this:
Code: Select all
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8710.PROJ WHERE DEPTNO='D01'
Now suppose that you also want to use DSNTIAUL to do these things:
- Unload all rows from the project table
Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
Lock both tables in share mode before you unload them
For these activities, you must specify the SQL parameter when you run DSNTIAUL. Your DSNTIAUL invocation looks like this:
Code: Select all
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) PARMS('SQL') -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSREC01 DD DSN=DSN8UNLD.SYSREC01,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
LOCK TABLE DSN8710.EMP IN SHARE MODE;
LOCK TABLE DSN8710.PROJ IN SHARE MODE;
SELECT * FROM DSN8710.PROJ;
SELECT * FROM DSN8710.EMP
WHERE WORKDEPT LIKE 'D%'
ORDER BY EMPNO;
Thanks,
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Re: Can we execute SQLs using JCL.
Thanks for the detailed examples. I have identified a job in use at our company and they also use DSNTIAUL. Very much similar to what you have showed.Anuj Dhawan wrote:Below listed JCLs are some of the example of executing SQL queries in JCL:
Suppose you want to unload the rows for department D01 from the project table. You can fit the table specification on one line, and you do not want to execute any non-SELECT statements, so you do not need the SQL parameter. Your invocation looks like this:
Code: Select all
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DSN) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) - LIB('DSN710.RUNLIB.LOAD') //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSREC00 DD DSN=DSN8UNLD.SYSREC00, // UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG), // VOL=SER=SCR03 //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH, // UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG), // VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200 //SYSIN DD * DSN8710.PROJ WHERE DEPTNO='D01'
Now suppose that you also want to use DSNTIAUL to do these things:
- Unload all rows from the project table
Unload only rows from the employee table for employees in departments with department numbers that begin with D, and order the unloaded rows by employee number
Lock both tables in share mode before you unload them
For these activities, you must specify the SQL parameter when you run DSNTIAUL. Your DSNTIAUL invocation looks like this:These examples are taken from DSNTIAUL documentation.Code: Select all
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * DSN SYSTEM(DSN) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) PARMS('SQL') - LIB('DSN710.RUNLIB.LOAD') //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSREC00 DD DSN=DSN8UNLD.SYSREC00, // UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG), // VOL=SER=SCR03 //SYSREC01 DD DSN=DSN8UNLD.SYSREC01, // UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG), // VOL=SER=SCR03 //SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH, // UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG), // VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200 //SYSIN DD * LOCK TABLE DSN8710.EMP IN SHARE MODE; LOCK TABLE DSN8710.PROJ IN SHARE MODE; SELECT * FROM DSN8710.PROJ; SELECT * FROM DSN8710.EMP WHERE WORKDEPT LIKE 'D%' ORDER BY EMPNO;
- Anuj Dhawan
- Founder
- Posts: 2806
- Joined: Sun Apr 21, 2013 7:40 pm
- Location: Mumbai, India
- Contact:
Re: Can we execute SQLs using JCL.
Nice to know tht we had been helpful. Appreciate the feedback.
Thanks,
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
Anuj
Disclaimer: My comments on this website are my own and do not represent the opinions or suggestions of any other person or business entity, in any way.
-
- Website Team
- Posts: 70
- Joined: Wed Jul 31, 2013 10:19 pm
Re: Can we execute SQLs using JCL.
One more example with DSNTEP2 if you want the query output in sysout
Thanks,
Chandan
Thanks,
Chandan
Code: Select all
//SQL EXEC PGM=IKJEFT1A,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(<DB2SUBSYSTEM>)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DSN8710.PROJ WHERE DEPTNO='D01';
Last edited by Anuj Dhawan on Wed Mar 09, 2016 11:49 am, edited 1 time in total.
Reason: Edited to align the code a bit.
Reason: Edited to align the code a bit.
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute