Writing SQL queries in REXX
GC
I have two questions:
1. I want to run SQL queries on some DB2 tables using REXX in the MVS/TSO=
environment. Mainly I am interested in reading some rows and writing to a=
file. Is it possible to embed SQL statements in a REXX program? Can DSNTE=
P2
be invoked from the REXX program?
2. In IEF/Composer 3, dynamic link-edit of acton block through the tool,
causes all the action blocks called by a Procedure to get link-edited. Is=
there a way to suppress unnecessary link-edits so that only the specified=
action blocks gets dynamically link-edited? =
G.Chakravarty
Isaac
Hi,
There is a tool from IBM (as-is) called REXX/SQL. I used it in prev life
and it is good, easy to handle and does all you need, the price was low
($3000 one time charge if I"m not mistaken, but that was 2 years ago).
Isaac Yassin
GC wrote:
>
> I have two questions:
>
> 1. I want to run SQL queries on some DB2 tables using REXX in the MVS/TSO
> environment. Mainly I am interested in reading some rows and writing to a
> file. Is it possible to embed SQL statements in a REXX program? Can DSNTEP2
> be invoked from the REXX program?
>
> 2. In IEF/Composer 3, dynamic link-edit of acton block through the tool,
> causes all the action blocks called by a Procedure to get link-edited. Is
> there a way to suppress unnecessary link-edits so that only the specified
> action blocks gets dynamically link-edited?
>
> G.Chakravarty
V. C. Ilagan
At 21:49 26/05/97 -0700, you wrote:
>Hi,
>
>There is a tool from IBM (as-is) called REXX/SQL. I used it in prev life
>and it is good, easy to handle and does all you need, the price was low
>($3000 one time charge if I"m not mistaken, but that was 2 years ago).
>
Why pay when DSNTEP2 called from within a REXX program does nicely as well...
specially if you make the output as VIO....
Regards,
Ven
Sean Jeffries
Date: Tuesday, 27 May 1997 8:27am ET
To: Softswitch.OAS
From: Sean.Jeffries@TAOFUN1
Subject: Writing SQL queries in REXX
In answer to your first question, yes, DSNTEP2 can be invoked from REXX.
I've written a simple REXX exec called "TEP2" that allows you to specify
your SQL statement, then executes the statement via DSNTEP2, then displays
the result on your terminal. The code for this is included below. It
requires a temporary PDS to exist to store the SQL in. This should be a
standard RECFM=FB LRECL=80 PDS. You could modify this code (which I've
done in a more complicated program) to display the output of the DSNTEP2
in an ISPF table so that you can page backward and forward through it.
Here's the REXX code:
/* REXX */
arg sid
"ispexec libdef ispplib dataset id('<YOUR.PANEL.LIBRARY>')"
START:
"ispexec control display line"
SHOW_WINDOW:
address ispexec
"addpop row(4) column(1)"
"display panel(tep2)"
If rc = 8 then exit(0)
"rempop"
address TSO
"alloc fi(output) da('<YOUR.TEMP.PDS("USERID()"TP)') shr reu"
queue spufline
"execio "queued()" diskw output (finis"
"free dd(output)"
"alloc fi(SYSIN) da('<YOUR.TEMP.PDS("USERID()"TP)') shr reu"
queue 'DSN SYS('sid')'
queue "run program(DSNTEP2)"
queue 'END'
pull DSNCMD
DSNCMD
signal START
/*======================== end of REXX ==========================*/
Here's the panel used by the REXX:
)attr
+ type(text) skip(on) intens(low)
% type(text) skip(on) intens(low)
_ type(input) intens(high)
)body window(77,4)
%
% SQL :_spufline +
% SSID:_sid +
%
)INIT
)PROC
)END
--------------------------( Forwarded letter 1 follows )---------------------
Date: Sunday, 25 May 1997 9:50pm
To: DB2-L
From: INTERNET...@SSWITCH.SNADS
Subject: Writing SQL queries in REXX
GLASER, TOM
Hello G.Chakravarty:
=20
Since you cannot run SQL in REXX on MVS (unless you have 3rd party
software), you can run DSNTEP2 in foreground using REXX. Below is the
REXX code that can be used to execute DSNTEP2. =20
While in edit on a PDS member or sequential file, type in your SQL
statement or statements. REXX will read your input, execute the queries
and display the output. Works just like DSNTEP2 in batch.=20
This allows me to execute a query quickly without having to get into a
tool such as SPUFI. Since our environment may have multiple DB2
subsystems per MVS, the REXX code asks for the approriate DB2 subsystem
ID. You might want to modify this. I cannot take credit for the
original code. I received this code from several other users and
modified it to our needs. Hope it helps.
=20
Tom Glaser
Southwestern Bell - DB2 Support
/* REXX */ =20
/*-------------------------------------------------------------------*/
/* */
/* Description: Edit macro to submit current edit session to */
/* DSNTEP2 in foreground and browse the output. */
/* */
/* Any line that is not "excluded" or commented */
/* out (two "--" prior to data) will be executed. */
/* */
/* */
/*-------------------------------------------------------------------*/
=20
address ISPEXEC "ISREDIT MACRO (ssid)" =20
=20
/* Prompt user for DB2 Subsystem ID */
do while (ssid =3D '') =
say ' ' =20
say 'Enter four position DB2 Subsystem ID or END:' =20
pull ssid =20
end =20
=20
ssid =3D strip(ssid) =
=20
if ssid =3D end then exit =
=20
"STEPLIB '"||ssid||".REF.DSNLOAD'" =20
orc =3D rc =
do while (orc /=3D 0) =
/* do until (orc =3D 0) */ =
say ' ' =20
say 'Error allocating DB2 Load library. Please verify' =20
say 'correct DB2 Subsystem ID.' =20
say ' ' =20
say 'Enter four position DB2 Subsystem ID or END:' =20
pull ssid =20
if ssid =3D end then exit =
"STEPLIB '"||ssid||".REF.DSNLOAD'" =20
orc =3D rc =
end =20
=20
j =3D 0 =
=20
/* Save the current non-excluded lines from the current edit session */
address ISPEXEC "ISREDIT (lines) =3D LINENUM .ZLAST" =
do i =3D 1 to lines =
address ISPEXEC "ISREDIT (status) =3D XSTATUS" i =
if status =3D 'NX' then =
do =20
address ISPEXEC "ISREDIT (line) =3D LINE" i =
parse value line with line '--' =20
if line =AC=3D '' then =
=20
do =20
j =3D j + 1 =
sysin.j =3D line =
end =20
end =20
end =20
/* Display a message if all the lines are excluded and exit */ =20
if j =3D 0 then =
do =20
zedsmsg =3D 'All lines are excluded' =
address ISPEXEC "SETMSG MSG(ISRZ001)" =20
exit =20
end =20
=20
/* Allocate SYSIN and SYSPRINT ddnames to temporary files and write */=20
/* the saved edit session lines to SYSIN */ =20
address TSO "ALLOC F(SYSIN) NEW UNIT(SYSDA) SPACE(3) TRACKS REUSE" =20
"EXECIO * DISKW SYSIN (STEM sysin. FINIS" =20
address TSO "ALLOC F(SYSPRINT) NEW UNIT(SYSDA) SPACE(8,2) TRACKS REUSE"
=20
"NEWSTACK" =20
=20
/* Place the RUN and END commons on the data stack and execute DSN */ =20
queue "RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('"||ssid||".REF.PGMLIB')"
queue "END" =20
=20
address TSO "DSN SYSTEM("ssid")" =20
=20
"DELSTACK" =20
=20
/* Browse the SYSPRINT file */ =20
address ISPEXEC "LMINIT DATAID(dsid) DDNAME(SYSPRINT)" =20
address ISPEXEC "BROWSE DATAID("dsid")" =20
address ISPEXEC "LMFREE DATAID("dsid")" =20
=20
/* Free the SYSIN and SYSPRINT ddnames */ =20
address TSO "FREE F(SYSIN SYSPRINT)" =20
=20
zedsmsg =3D 'Macro completed' =20
address ISPEXEC "SETMSG MSG(ISRZ001)" =20
=20
"STEPLIB FREE" =20
=20
exit =20
=20
=20
>----------
>From: GC[SMTP:gc...@COMPUSERVE.COM]
>Sent: Sunday, May 25, 1997 8:06 PM
>To: DB...@AMERICAN.EDU
>Subject: Writing SQL queries in REXX
>
>I have two questions:
>
>1. I want to run SQL queries on some DB2 tables using REXX in the =
MVS/TSO
>environment. Mainly I am interested in reading some rows and writing to =
a
>file. Is it possible to embed SQL statements in a REXX program? Can =
DSNTEP2
>be invoked from the REXX program?
>
>2. In IEF/Composer 3, dynamic link-edit of acton block through the =
tool,
>causes all the action blocks called by a Procedure to get link-edited. =
Is
>there a way to suppress unnecessary link-edits so that only the =
specified
>action blocks gets dynamically link-edited?=20
>
>G.Chakravarty
>
Isaac
V. C. Ilagan wrote:
>
> At 21:49 26/05/97 -0700, you wrote:
> >Hi,
> >
> >There is a tool from IBM (as-is) called REXX/SQL. I used it in prev life
> >and it is good, easy to handle and does all you need, the price was low
> >($3000 one time charge if I"m not mistaken, but that was 2 years ago).
> >
>
> Why pay when DSNTEP2 called from within a REXX program does nicely as well...
> specially if you make the output as VIO....
>
> Regards,
Using REXX/SQL allows you to manipulate the data within REXX context "in
the fly" - read into REXX vectors, issue updates and so on like you do
in regular programming language while having the benefit of running
interpreter (REXX) no compilation needed. For Q&D work it is very nice
and easy and if you know REXX you can do much more. Using either DSNTEP2
or DSNTIAUL and then parsing the output file is more tedious and error
prone (I do it now as I don't have it and I miss it). BTW you
wouldn't do the same in C,COBOL,PL1...(means use DSNTEP2 and then
analyze the output ?) I don't know about pricing tactics of IBM at your
location - but give it a try - you'll love it.
Isaac Yassin
> >Isaac Yassin
> >GC wrote:
> >>
> >> I have two questions:
> >>
> >> 1. I want to run SQL queries on some DB2 tables using REXX in the MVS/TSO
> >> environment. Mainly I am interested in reading some rows and writing to a
> >> file. Is it possible to embed SQL statements in a REXX program? Can DSNTEP2
> >> be invoked from the REXX program?
> >>
> >> 2. In IEF/Composer 3, dynamic link-edit of acton block through the tool,
> >> causes all the action blocks called by a Procedure to get link-edited. Is
> >> there a way to suppress unnecessary link-edits so that only the specified
> >> action blocks gets dynamically link-edited?
> >>
> >> G.Chakravarty
> >
> >
Al Stone
ibm has a product call rxsql which sounds pretty cool in that
it supports cursors & loads data into rexx variables. it will
cost about $3500 asis. You will need to talk to your ibm rep
to get hold of it.
i have been looking at a product called RLX for DB2 by Relational
Architects which does the same as rxsql and also supports direct
read into ispf tables & supports a number of sql to ispf functions.
it also has an ifi interface that lets you exec db2 commands and load
the results into rexx variables.
the doc looks promising - havent installed it yet though.
for our cpu it costs about twice as much as the ibm offering and then
there is maint cost as well.
good luck,
steve
Stan Hoey
In article <338A67...@netvision.net.il>,
yas...@NETVISION.NET.IL (Isaac) wrote:
> Hi,
>
> There is a tool from IBM (as-is) called REXX/SQL. I used
it in prev life
> and it is good, easy to handle and does all you need, the
price was low
> ($3000 one time charge if I"m not mistaken, but that was 2
years ago).
You may have some difficulty getting this tool. It was used
internally in IBM until around 2 years ago, when it became
an ASIS product. Shortly after the announcement, the
Austrian IBMer who wrote it left IBM, who dropped it as a
product PDQ.
If you can get it, it is very easy to use and is quite a
clever piece of code.
Steve Evans
you can get it from your ibm rep. it will cost you $US3,500 asis
for nearly double that you can get much more from folks like
open software tech (rexxtools) or relational architects (rlx for db2).
good luck,
steve
Peter Schaeffer
----------------------------------------------------------------------
THIS MAIL MESSAGE IS FROM THE INTERNET AND MAY HAVE BEEN READ, COPIED,
OR MODIFIED BY USERS OTHER THAN INTENDED RECIPIENTS.
----------------------------------------------------------------------
My company (NEON Systems) has used a product call REXXTOOLS for
executing SQL from REXX programs. This product has worked quite well
and is very efficient. REXXTOOLS is markted by Open Software
Technologies of Longwood, FL. Their URL is www.open-softtech.com
and their phone number is 407-788-7173.
Peter Schaeffer
Stan Hoey (by way of Ergun Ozel <ozele@pamukkale.escortnet.com>)
You can find an assembler pgm at www.xephon.com/archives/db2a.htm
in article A REXX-SQL interface( November 1991 ). It's free...