View previous topic :: View next topic
|
Author |
Message |
FNYD
New User
Joined: 11 May 2021 Posts: 14 Location: INDIA
|
|
|
|
Hi Team,
Can i embed db2 select query in a script and call that script via jcl using BPXBATCH utility. i have a requirement where i need to frequently perform search(select *) from multiple db2 tables of same schema and extract data to flat files and send across to different server.
How do i achieve this , and other way or links or sample code. Please let me know
Thanks! |
|
Back to top |
|
 |
enrico-sorichetti
Superior Member

Joined: 14 Mar 2007 Posts: 10896 Location: italy
|
|
|
|
Quote: |
Can i embed db2 select query in a script and call that script via jcl using BPXBATCH utility. |
all depends on what language you plan to write the script |
|
Back to top |
|
 |
FNYD
New User
Joined: 11 May 2021 Posts: 14 Location: INDIA
|
|
|
|
enrico-sorichetti wrote: |
Quote: |
Can i embed db2 select query in a script and call that script via jcl using BPXBATCH utility. |
all depends on what language you plan to write the script |
using BPXBATCH utility and callign shell script , i belive shell will be executing in omvs of zos. correct meif i am wrong |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2228 Location: USA
|
|
|
|
FNYD wrote: |
Hi Team,
Can i embed db2 select query in a script and call that script via jcl using BPXBATCH utility. i have a requirement where i need to frequently perform search(select *) from multiple db2 tables of same schema and extract data to flat files and send across to different server.
How do i achieve this , and other way or links or sample code. Please let me know
Thanks! |
What prevents you from using the simplest JCL?
1) unload required tables into flat files via standard utility, and
2) FTP all files to the servers you need
Maybe, 50% of existing requirements are exactly what you need, but none of them does require any complex mechanism to achieve this.
What is so specific in your requirements that you need to make simple things so complicated for no reason? |
|
Back to top |
|
 |
FNYD
New User
Joined: 11 May 2021 Posts: 14 Location: INDIA
|
|
|
|
sergeyken wrote: |
FNYD wrote: |
Hi Team,
Can i embed db2 select query in a script and call that script via jcl using BPXBATCH utility. i have a requirement where i need to frequently perform search(select *) from multiple db2 tables of same schema and extract data to flat files and send across to different server.
How do i achieve this , and other way or links or sample code. Please let me know
Thanks! |
What prevents you from using the simplest JCL?
1) unload required tables into flat files via standard utility, and
2) FTP all files to the servers you need
Maybe, 50% of existing requirements are exactly what you need, but none of them does require any complex mechanism to achieve this.
What is so specific in your requirements that you need to make simple things so complicated for no reason? |
-- can you pvovide me a link , i need to start with this. some sort of sample jcl would help. |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2228 Location: USA
|
|
|
|
FNYD wrote: |
-- can you pvovide me a link , i need to start with this. some sort of sample jcl would help. |
The code may be site-specific, customized by your support. Generally it may look like this one, with possible tons of variations
Code: |
//********************************************************************
//* UNLOAD THE TABLE DATA INTO SEQUENTIAL DATASET(S)
//********************************************************************
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=syshlq.SDSNLOAD.db2id,DISP=SHR
// DD DSN=syshlq.RUNLIB.db2id,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(db2id)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DISP=(NEW,CATLG),DSN=xxx.yyy.TABLE1,
// UNIT=SYSDA,SPACE=(CYL,(100,200))
//SYSREC01 DD DISP=(NEW,CATLG),DSN=xxx.yyy.TABLE2,
// UNIT=SYSDA,SPACE=(CYL,(100,200))
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT * FROM sqlid.TABLE1 WITH UR;
SELECT * FROM sqlid.TABLE2 WITH UR;
//*
//********************************************************************
//* TRANSMIT FLAT FILES TO WHEREEVER NEEDED
//********************************************************************
//FTP EXEC FTPBATCH <- one of site-specific-procedures
//NETRC DD DSN=site-specific-setting,DISP=SHR
//SYSREC00 DD DISP=SHR,DSN=*.UNLOAD.SYSREC00
//SYSREC01 DD DISP=SHR,DSN=*.UNLOAD.SYSREC01
//INPUT DD *
...site-optional-parameters.....
CD /directory-to-transmit-to
PUT //DD:SYSREC00 desired-filename1-on-server
PUT //DD:SYSREC01 desired-filename2-on-server
CLOSE
QUIT
//* |
You cannot start doing something with zero preliminary knowledge (as per your questions, and used terminology). You desperately need:
1) to read, and to learn aggressively a lot of things about programming in general, and mainframe specifics in particular.
2) to consult with your support team: what are the tools available at your environment, with their comments, and examples. |
|
Back to top |
|
 |
FNYD
New User
Joined: 11 May 2021 Posts: 14 Location: INDIA
|
|
|
|
sergeyken wrote: |
FNYD wrote: |
-- can you pvovide me a link , i need to start with this. some sort of sample jcl would help. |
The code may be site-specific, customized by your support. Generally it may look like this one, with possible tons of variations
Code: |
//********************************************************************
//* UNLOAD THE TABLE DATA INTO SEQUENTIAL DATASET(S)
//********************************************************************
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=syshlq.SDSNLOAD.db2id,DISP=SHR
// DD DSN=syshlq.RUNLIB.db2id,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(db2id)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DISP=(NEW,CATLG),DSN=xxx.yyy.TABLE1,
// UNIT=SYSDA,SPACE=(CYL,(100,200))
//SYSREC01 DD DISP=(NEW,CATLG),DSN=xxx.yyy.TABLE2,
// UNIT=SYSDA,SPACE=(CYL,(100,200))
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT * FROM sqlid.TABLE1 WITH UR;
SELECT * FROM sqlid.TABLE2 WITH UR;
//*
//********************************************************************
//* TRANSMIT FLAT FILES TO WHEREEVER NEEDED
//********************************************************************
//FTP EXEC FTPBATCH <- one of site-specific-procedures
//NETRC DD DSN=site-specific-setting,DISP=SHR
//SYSREC00 DD DISP=SHR,DSN=*.UNLOAD.SYSREC00
//SYSREC01 DD DISP=SHR,DSN=*.UNLOAD.SYSREC01
//INPUT DD *
...site-optional-parameters.....
CD /directory-to-transmit-to
PUT //DD:SYSREC00 desired-filename1-on-server
PUT //DD:SYSREC01 desired-filename2-on-server
CLOSE
QUIT
//* |
You cannot start doing something with zero preliminary knowledge (as per your questions, and used terminology). You desperately need:
1) to read, and to learn aggressively a lot of things about programming in general, and mainframe specifics in particular.
2) to consult with your support team: what are the tools available at your environment, with their comments, and examples. |
---f---
thanks for the support on this!
yes amature in asking questions but got a good start with my work!
i am trying to use SFTP my file to host server but getting error to connecting to host anythign i am missing in below code:
error : could not resolve host name
host is windows network
//*
pwdsn="TEST.XXX.xx(xx)"
user=xxxxxx
host=\\xxxxxxxx.xxxx.com
lzopt="mode=text"
timestamp=`date +%Y%m%d%H%M%S`
rdir=\\server location directory
. $script_dir/sftp_connect.sh <<EOB
lzopts $lzopt
cd $rdir
PUT //Mf.file.tobe.ftpd test_$timestamp.txt
chmod 775 test*
EOB |
|
Back to top |
|
 |
sergeyken
Senior Member

Joined: 29 Apr 2008 Posts: 2228 Location: USA
|
|
|
|
No response should be expected unless your code samples are properly <coded>, and properly aligned.
P.S.
The appeal “I’m getting errors” cannot be resolved anyway, without clarification of the error(s).
It is equivalent to “My car did not start, what is wrong?” |
|
Back to top |
|
 |
|
|