IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Embeding DB2 sql statements in scirpt and calling script bpx


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Tue May 11, 2021 12:48 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10879
Location: italy

PostPosted: Tue May 11, 2021 4:04 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Tue May 11, 2021 4:08 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2120
Location: USA

PostPosted: Tue May 11, 2021 5:59 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Tue May 11, 2021 6:24 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2120
Location: USA

PostPosted: Tue May 11, 2021 7:58 pm
Reply with quote

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
View user's profile Send private message
FNYD

New User


Joined: 11 May 2021
Posts: 14
Location: INDIA

PostPosted: Wed May 12, 2021 12:59 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2120
Location: USA

PostPosted: Wed May 12, 2021 5:40 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Dynamically build sort control statem... SYNCSORT 18
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Calling Java method from batch COBOL ... COBOL Programming 5
No new posts Calling an Open C library function in... CICS 1
No new posts calling a JCl inside a JCL JCL & VSAM 3
Search our Forums:

Back to Top