Re: DB2 / DFSort question
On Thu, 16 Apr 2020 11:24:43 -0500, Tim Hare wrote: >Before I start chiseling a piece of stone into a wheel :-) - I've been >thinking about ICETOOL and DB2. Is there already in existence something that >would provide a SELECT (similar to what is used in DSNTIAUL or DSNTIAD) to >select DB2 data as an E15 exit for DFSORT? > >My ideal would be (Maybe I should write an RFE) >-- >In ICETOOL the ability to code SQLSEL FROM(SQL) USING() > >CNTL DD * > >SELECT For anybody that might be interested, SYNCSORT can run SQL SELECT statements. I'm not sure of the exact details on how to install/enable that feature, but here is some sample SYNCSORT JCL to run a SELECT: //SORTDB2 EXEC PGM=SORT,PARM='DB2=ssid' //STEPLIB DD DISP=SHR,DSN=hlq.xxx.SDSNEXIT // DD DISP=SHR,DSN=hlq.xxx.SDSNLOAD //SYSOUT DD SYSOUT=* //SORTOUT DD SYSOUT=* //SORTDBIN DD * SELECT * FROM Q.STAFF; /* //SYSINDD * SORT FIELDS=COPY INREC FINDREP=(INOUT=(X'00',X'40'),STARTPOS=5,ENDPOS=13) OUTFIL HEADER2=(1:' ID# Name Dept Title Years', 42:'SalaryCommission'), BUILD=(1:1,2,BI,ZDF,C' ', 5,9,C' ', 15,2,BI,ZDF,C' ', 18,5,C' ', 24,2,BI,EDIT=(T),C' ', 27,4,PD,EDIT=($I,III,IIT.TT),C' ', 32,4,PD,EDIT=($I,III,IIT.TT)) END /* //* Q.STAFF is a sample table that ships with QMF. You have to tell SYNCSORT the DB2 SubSystem ID (DB2=ssid) to use and you have to STEPLIB to the DB2 SDSNLOAD Library, (if you use SDSNEXIT, then it should come before SDSNLOAD). You can run much more complicated SELECTs then I have in this example! You can use SYNCSORT control statements to reformat the output into a readable report. -- Dale R. Smith -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2 / DFSort question
I was not aware that the UNLOAD utility invoked DFSORT, I figured DSNTIAUL did. I was wondering it that was as efficient as invoking an SQL query in E15, but with the other responses I think it's probably not worth coding another one to find out. As for creating the symbols - I can probably find ways to do that from the DDL created by DSNTIAUL. Thanks for all the responses! -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2 / DFSort question
IBM supplies (or did at one time) a sample COBOL program that supports a varying list SELECT statement via dynamic SQL. I adapted this program so that it could be invoked as an E15 exit. It accepts an SQL statement via a dd statement called SQLIN, then executes the query, fetching the results and passing the rows back to SORT. Having said that, I was unable to come up with a plausible use case for it, so it sits on the shelf along with other interesting but impractical experiments. On Thu, Apr 16, 2020 at 15:12 Sri h Kolusu wrote: > > Why offline? What's so secret about it? > > There is nothing secret about it. Just did not want to clutter this forum > and I wasn't even sure if OP was willing to share his DB2 DDL's. > > DFSORT Smart tricks page already has "Create DFSORT Symbols from COBOL > Copybook" trick which can be used to create DFSORT symbols from the Output > of DCLGEN. > > https://www.ibm.com/support/pages/smart-dfsort-tricks > > The other approach to do it in the SQL itself for which I wanted to test on > OP's DDL first before giving it to him and hence the offline request. > > Thanks, > Kolusu > > IBM Mainframe Discussion List wrote on > 04/16/2020 02:26:57 PM: > > > From: Robert Prins > > To: IBM-MAIN@LISTSERV.UA.EDU > > Date: 04/16/2020 11:27 AM > > Subject: [EXTERNAL] Re: DB2 / DFSort question > > Sent by: IBM Mainframe Discussion List > > > > On 2020-04-16 17:56, Sri h Kolusu wrote: > > >> It would be nice if this could execute before SYMNAMES is read, so > > >> the SELECT handler could create symbols for all of the columns in > > >> the result set. > > > > > > If your intention is to create DFSORT symbols from DB2 table definition > > > then there are a couple of ways to get it done. Let me know if you are > > > interested in that and we can discuss that offline. > > > > Why offline? What's so secret about it? > > > > 1) Use the standard Db2 tools to generate a PL/I %INCLUDE member foryour > table > > 2) Download RAP00110 and ESYMSORT from the URL in the signature below > > 3) Put them into an EXEC library concatenated to SYSEXEC (or use ALTLIB) > > 4) VIEW the generated PL/I %INCLUDE member, give the command "ESYMSORT ?" > and > > follow the instructions displayed. > > > > 5) Optional, but very much appreciated: Give me some feedback. > > > > Robert > > -- > > Robert AH Prins > > robert.ah.prins(a)gmail.com > > The hitchhiking grandfather - https://urldefense.proofpoint.com/v2/ > > url? > > u=https-3A__prino.neocities.org_indez.html&d=DwICaQ&c=jf_iaSHvJObTbx- > > siA1ZOg&r=IAduPlMgORoDwSg- > > > > QJfFI61PqQWHjCo5brtkXopHqZk&m=5E9jJTfDQ9c477aKnqviCWWkeYN8g3e1smY-4c9lqnI&s=A0k- > > > GHqK9B1F7Xd9UrSObixq-WOI26EwnuPTBZE8bfk&e= > > Some REXX code for use on z/OS - https://urldefense.proofpoint.com/ > > v2/url? > > > > u=https-3A__prino.neocities.org_zOS_zOS-2DTools.html&d=DwICaQ&c=jf_iaSHvJObTbx- > > > siA1ZOg&r=IAduPlMgORoDwSg- > > > > QJfFI61PqQWHjCo5brtkXopHqZk&m=5E9jJTfDQ9c477aKnqviCWWkeYN8g3e1smY-4c9lqnI&s=lrqqJu2TCk4icUOAWwu9RBa6yHRVCO81xXQP0IfUp_g&e= > > > > > -- > > For IBM-MAIN subscribe / signoff / archive access instructions, > > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2 / DFSort question
> Why offline? What's so secret about it? There is nothing secret about it. Just did not want to clutter this forum and I wasn't even sure if OP was willing to share his DB2 DDL's. DFSORT Smart tricks page already has "Create DFSORT Symbols from COBOL Copybook" trick which can be used to create DFSORT symbols from the Output of DCLGEN. https://www.ibm.com/support/pages/smart-dfsort-tricks The other approach to do it in the SQL itself for which I wanted to test on OP's DDL first before giving it to him and hence the offline request. Thanks, Kolusu IBM Mainframe Discussion List wrote on 04/16/2020 02:26:57 PM: > From: Robert Prins > To: IBM-MAIN@LISTSERV.UA.EDU > Date: 04/16/2020 11:27 AM > Subject: [EXTERNAL] Re: DB2 / DFSort question > Sent by: IBM Mainframe Discussion List > > On 2020-04-16 17:56, Sri h Kolusu wrote: > >> It would be nice if this could execute before SYMNAMES is read, so > >> the SELECT handler could create symbols for all of the columns in > >> the result set. > > > > If your intention is to create DFSORT symbols from DB2 table definition > > then there are a couple of ways to get it done. Let me know if you are > > interested in that and we can discuss that offline. > > Why offline? What's so secret about it? > > 1) Use the standard Db2 tools to generate a PL/I %INCLUDE member foryour table > 2) Download RAP00110 and ESYMSORT from the URL in the signature below > 3) Put them into an EXEC library concatenated to SYSEXEC (or use ALTLIB) > 4) VIEW the generated PL/I %INCLUDE member, give the command "ESYMSORT ?" and > follow the instructions displayed. > > 5) Optional, but very much appreciated: Give me some feedback. > > Robert > -- > Robert AH Prins > robert.ah.prins(a)gmail.com > The hitchhiking grandfather - https://urldefense.proofpoint.com/v2/ > url? > u=https-3A__prino.neocities.org_indez.html&d=DwICaQ&c=jf_iaSHvJObTbx- > siA1ZOg&r=IAduPlMgORoDwSg- > QJfFI61PqQWHjCo5brtkXopHqZk&m=5E9jJTfDQ9c477aKnqviCWWkeYN8g3e1smY-4c9lqnI&s=A0k- > GHqK9B1F7Xd9UrSObixq-WOI26EwnuPTBZE8bfk&e= > Some REXX code for use on z/OS - https://urldefense.proofpoint.com/ > v2/url? > u=https-3A__prino.neocities.org_zOS_zOS-2DTools.html&d=DwICaQ&c=jf_iaSHvJObTbx- > siA1ZOg&r=IAduPlMgORoDwSg- > QJfFI61PqQWHjCo5brtkXopHqZk&m=5E9jJTfDQ9c477aKnqviCWWkeYN8g3e1smY-4c9lqnI&s=lrqqJu2TCk4icUOAWwu9RBa6yHRVCO81xXQP0IfUp_g&e= > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2 / DFSort question
On 2020-04-16 17:56, Sri h Kolusu wrote: It would be nice if this could execute before SYMNAMES is read, so the SELECT handler could create symbols for all of the columns in the result set. If your intention is to create DFSORT symbols from DB2 table definition then there are a couple of ways to get it done. Let me know if you are interested in that and we can discuss that offline. Why offline? What's so secret about it? 1) Use the standard Db2 tools to generate a PL/I %INCLUDE member for your table 2) Download RAP00110 and ESYMSORT from the URL in the signature below 3) Put them into an EXEC library concatenated to SYSEXEC (or use ALTLIB) 4) VIEW the generated PL/I %INCLUDE member, give the command "ESYMSORT ?" and follow the instructions displayed. 5) Optional, but very much appreciated: Give me some feedback. Robert -- Robert AH Prins robert.ah.prins(a)gmail.com The hitchhiking grandfather - https://prino.neocities.org/indez.html Some REXX code for use on z/OS - https://prino.neocities.org/zOS/zOS-Tools.html -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: DB2 / DFSort question
Tim, DFSORT or ICETOOL does NOT have the capability of running DB2 queries. However DB2 utilities(LOAD, REORG, UNLOAD..) invoke DFSORT in the background. IBM already have 2 free unload utilities that does the job for you. DSNTIAUL and DSNTEP2 are free unload utilities. DSNTIAUL unload utility invokes DFSORT in the background if there is a SORT involved(Order By). Also there is a DB2 High Performance Unload (INZUTILB) which is an add-on product which gives you much more flexibility when you are unloading. >>> It would be nice if this could execute before SYMNAMES is read, so > the SELECT handler could create symbols for all of the columns in > the result set. If your intention is to create DFSORT symbols from DB2 table definition then there are a couple of ways to get it done. Let me know if you are interested in that and we can discuss that offline. Thanks, Sri Hari Kolusu DFSORT Development IBM Corporation IBM Mainframe Discussion List wrote on 04/16/2020 09:24:43 AM: > From: Tim Hare > To: IBM-MAIN@LISTSERV.UA.EDU > Date: 04/16/2020 09:25 AM > Subject: [EXTERNAL] DB2 / DFSort question > Sent by: IBM Mainframe Discussion List > > Before I start chiseling a piece of stone into a wheel :-) - I've > been thinking about ICETOOL and DB2. Is there already in existence > something that would provide a SELECT (similar to what is used in > DSNTIAUL or DSNTIAD) to select DB2 data as an E15 exit for DFSORT? > > My ideal would be (Maybe I should write an RFE) > -- > In ICETOOL the ability to code SQLSEL FROM(SQL) USING() > > CNTL DD * > > SELECT > > -- > > It would be nice if this could execute before SYMNAMES is read, so > the SELECT handler could create symbols for all of the columns in > the result set. > > Maybe it is just as efficient to use DSNTIAUL with ORDER BY ? > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
DB2 / DFSort question
Before I start chiseling a piece of stone into a wheel :-) - I've been thinking about ICETOOL and DB2. Is there already in existence something that would provide a SELECT (similar to what is used in DSNTIAUL or DSNTIAD) to select DB2 data as an E15 exit for DFSORT? My ideal would be (Maybe I should write an RFE) -- In ICETOOL the ability to code SQLSEL FROM(SQL) USING() CNTL DD * SELECT -- It would be nice if this could execute before SYMNAMES is read, so the SELECT handler could create symbols for all of the columns in the result set. Maybe it is just as efficient to use DSNTIAUL with ORDER BY ? -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
