Executing a Rexx routine from a DB2 stored procedure

[Gilbert C Cardenas]

I have a REXX routine that I have coded that basically returns the size of a member in a PDS or Panvalet library. The script works fine on the mainframe, however, we want to set up a stored procedure in DB2 to execute the REXX routine.

This is a first for us and the DBA is telling me that he is getting the following error...

74 *-* "LMINIT DATAID(MYLIB) DATASET('"LIBNAME"')"
+++ RC(-3) +++

Are there some functions not supported through DB2 or do we have to set up some kind of ISPF environment library for this to work.

I am basically using the ALLOC, LMINIT, LMOPEN, LMMFIND, LMCLOSE, LMFREE and LINKMVS for the PAN#2 module.

Any pointers appreciated,
Gil Cardenas




________________________________
This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.


----------------------------------------------------------------------
For TSO-REXX subscribe / signoff / archive access instructions,
send email to LIST...@VM.MARIST.EDU with the message: INFO TSO-REXX

[Dave Salt]

All of the LM services (e.g. LMINIT etc) require ISPF to be available.

Dave Salt

SimpList(tm) - try it; you'll get it!

http://www.mackinney.com/products/program-development/simplist.html




> Date: Fri, 16 Nov 2012 14:48:43 -0600
> From: Gilbert...@GROCERYBIZ.COM
> Subject: Executing a Rexx routine from a DB2 stored procedure
> To: TSO-...@VM.MARIST.EDU

[Paul Gilmartin]

On Nov 16, 2012, at 13:55, Dave Salt wrote:

> All of the LM services (e.g. LMINIT etc) require ISPF to be available.
>

> http://www.mackinney.com/products/program-development/simplist.html
>
Yes, however ...

>> Date: Fri, 16 Nov 2012 14:48:43 -0600
>> From: Gilbert...@GROCERYBIZ.COM
>>

>> I have a REXX routine that I have coded that basically returns the size of a member in a PDS or Panvalet library. The script works fine on the mainframe, however, we want to set up a stored procedure in DB2 to execute the REXX routine.

>> ...

>> I am basically using the ALLOC, LMINIT, LMOPEN, LMMFIND, LMCLOSE, LMFREE and LINKMVS for the PAN#2 module.
>>

It's straightforward however tedious to open a PDS (or PDSE -- I
haven't tried) directory as a PS data set, RECFM=F, LRECL=256, and
read the directory blocks with "address MVS EXECIO" and parse them.
The format of the ISPF user info is documented somewhere,

Even this requires that ISPF member stats have been saved previously.

Does DB2 allow MVS EXECIO?

It boggles the mind to ponder an OS so primitive that it doesn't
keep the sizes and timestamps of files in a uniform, conveniently
accessible format. Use HFS/zFS files; they don't suffer such
deficiencies.

-- gil

[Steve Coalbran]

Hej Gilbert,

If there some reason one cannot start the DSN command within ISPF?
I don't use DB2 much except in batch so have never had to try this.
ISPSTART CMD(RUNDSN ssid,parms)
- where RUNDSN is an EXEC that accepts the ssid and starts DB2 with a DSN(whatever) and processes the parms appropriately?

-OR-

as has been suggested, in outline, to get the ISPF stats in an old-fashioned method reading the DIR directly.
If you want to avoid assembler, try...

/*REXX(DIREAD)*/ TRACE "N"
ARG thispds
dirattr = "RECFM(F B) DSORG(PS) LRECL(256) BLKSIZE(256)"
"ALLOC DD(SYSUT1) DS("thispds") SHR REUSE" dirattr
"ALLOC DD(SYSUT2) UNIT(VIO) NEW REUSE" dirattr
"ALLOC DD(SYSIN) DUMMY REUSE"
"ALLOC DD(SYSPRINT) DS(*) REUSE"
"CALL *(ICEGENER)"
"EXECIO * DISKR SYSUT2 (STEM DIR. FINIS"
TRACE "N"
DO i = 1 TO dir.0
CALL DIRSCAN dir.i
END
EXIT
DIRSCAN: PROCEDURE
PARSE ARG ent
/* a miracle happens? */
RETURN

Sorry, this means abandonning all the LM power in favour of more coding in the DIRSCAN subrtn but it should work?

/Steve

Steve Coalbran
Accredited Senior IT Specialist, Global Business Services, Application Services

IBM Svenska AB, SE 164 92 Stockholm, Sweden

> Date: Fri, 16 Nov 2012 14:48:43 -0600
> From: Gilbert...@GROCERYBIZ.COM

> Subject: [TSO-REXX] Executing a Rexx routine from a DB2 stored procedure
> To: TSO-...@VM.MARIST.EDU
>

[Robert Prins]

On 2012-11-16 20:49, Gilbert C Cardenas wrote:> I have a REXX routine

that I have coded that basically returns the size of a member in a PDS
or Panvalet library. The script works fine on the mainframe, however,
we want to set up a stored procedure in DB2 to execute the REXX
routine.
>
> This is a first for us and the DBA is telling me that he is getting the following error...
>
> 74 *-* "LMINIT DATAID(MYLIB) DATASET('"LIBNAME"')"
> +++ RC(-3) +++
>
> Are there some functions not supported through DB2 or do we have to set up some kind of ISPF environment library for this to work.
>
> I am basically using the ALLOC, LMINIT, LMOPEN, LMMFIND, LMCLOSE, LMFREE and LINKMVS for the PAN#2 module.
>
> Any pointers appreciated,

Doug Nadel: http://sillysot.com/ftp/, and look for "mlrexx.txt" - It
does not support the new extended stats

Robert
--
Robert AH Prins
robert(a)prino(d)org

[Steve Coalbran]

Hej Gilbert - Odd, I tried appending this before but it disappeared!

Can one ISPSTART an exec that does the DSN starting DB2, that would give you the option of using those wonderful ISPF LM utilities?!
ISPSTART CMD(RUNDSN ssid,otherparms)
Where RUNDSN is the exec to issue the DSN etc. to start DB2?

-OR-

Do it the old-fashioned way and grab the DIR...

dirattr = "RECFM(F B) DSORG(PS) LRECL(256) BLKSIZE(256)"
"ALLOC DD(SYSUT1) DS("thispds") SHR REUSE" dirattr
"ALLOC DD(SYSUT2) UNIT(VIO) NEW REUSE" dirattr
"ALLOC DD(SYSIN) DUMMY REUSE"
"ALLOC DD(SYSPRINT) DS(*) REUSE"
"CALL *(ICEGENER)"
"EXECIO * DISKR SYSUT2 (STEM DIR. FINIS"

DO i = 1 TO dir.0
CALL DIRSCAN dir.i
END
EXIT
DIRSCAN: PROCEDURE
PARSE ARG ent

/* a miracle happens! ;-) or...*/ SAY ent
RETURN

You have to work out the DIRSCAN subrtn from the manuals - sorry no examples left of this (I use LM utes too!)

Steve Coalbran
Accredited Senior IT Specialist, Global Business Services, Application Services

IBM Svenska AB, SE 164 92 Stockholm, Sweden

> Date: Fri, 16 Nov 2012 21:47:52 -0700
> From: PaulGB...@AIM.COM
> Subject: Re: [TSO-REXX] Executing a Rexx routine from a DB2 stored procedure
> To: TSO-...@VM.MARIST.EDU
>

[Adrian Stern]

Did you address isredit first? -3 is command not found so maybe you didn't

[Don Imbriale]

ISREDIT would be for the ISPF editor. ISPEXEC is needed here.

- Don Imbriale

[Adrian Stern]

As you can read a pds member with execio and without tso or ispf that may be
a solution too
Execio * diskr <file>
And the number of lines on the stack is the no of records * linelength gives
you size in bytes

-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of

[Adrian Stern]

Yes you're so right Don - sorry all!

[Paul Gilmartin]

On Nov 17, 2012, at 02:10, Steve Coalbran wrote:
>
> as has been suggested, in outline, to get the ISPF stats in an old-fashioned method reading the DIR directly.
> If you want to avoid assembler, try...
>
> /*REXX(DIREAD)*/ TRACE "N"
> ARG thispds
> dirattr = "RECFM(F B) DSORG(PS) LRECL(256) BLKSIZE(256)"
> "ALLOC DD(SYSUT1) DS("thispds") SHR REUSE" dirattr
> "ALLOC DD(SYSUT2) UNIT(VIO) NEW REUSE" dirattr
> "ALLOC DD(SYSIN) DUMMY REUSE"
> "ALLOC DD(SYSPRINT) DS(*) REUSE"
> "CALL *(ICEGENER)"
> "EXECIO * DISKR SYSUT2 (STEM DIR. FINIS"
>

Why not "EXECIO * DISKR SYSUT1 (STEM DIR. FINIS" and omit the ICEGENER?

On Nov 17, 2012, at 07:14, Adrian Stern wrote:

> As you can read a pds member with execio and without tso or ispf that may be
> a solution too
> Execio * diskr <file>
> And the number of lines on the stack is the no of records * linelength gives
> you size in bytes
>

True. I suspect the OP was seeking a member less intensive
of I/O bandwidth. The greater cost is that Rexx provides
no alternative to ALLOCATE and FREE for each member. And
it requires a means of enumerating the members; perhaps
OUTTRAP LISTDS. (Does that work in DB2?_

-- gil

[Paul Gilmartin]

On Nov 17, 2012, at 07:14, Adrian Stern wrote:

[Adrian Stern]

Well I don't know that allocate, free is necessarily a problem for a batch
job - but could be perhaps
And they can always get the member list from my mbrlist exec that'll run
without ispf or tso and returns a member list for a pds. Available from that
tape site - or I can post it here - it's not long

[Farley, Peter x23353]

PMFJI here, but from what little I know about DB2 the basic question for the OP is:

Does the DB2 stored procedure environment tolerate anything but SQL calls? I.E., are ALLOCATE/FREE/etc. even allowed, never mind the whole TSO/E environment required for ISPF functions like LMINIT and friends?

DB2 stored procedures are a very special beast which execute in a very special DB2-controlled environment. The OP has to Read The Fine Manual to determine first what z/OS facilities (if any) DB2 stored procedures are allowed to use.

Peter
--

This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.

[Adrian Stern]

As far as I remember all that happens is that a program is executed and can
do just about anything it could do if submitted - but I think it will always
run under DB2 - so it may be that in order to use tso and/or ispf resources
the DB2 started task would need to have these environments allocated in it's
jcl but honestly I don't know only having written SQL started tasks myself.

-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of

[Paul Gilmartin]

On Nov 17, 2012, at 08:50, Adrian Stern wrote:

> Well I don't know that allocate, free is necessarily a problem for a batch
> job - but could be perhaps
>

And here I have to differ. Here's an EXEC that creates 20
UNIX members, then 20 PDS members by allocating and
freeing each. (Is there any way to do the latter in Rexx
outside ISPF?)

/* Rexx */ signal on novalue; /*
Doc: compare PDS with UNIX file performance.
Doc: Likely limited by ALLOCATE/OPEN/CLOSE/FREE overhead.
*/
trace N
RC = SYSCALLS( 'ON' )
address 'SYSCALL'
DIR = 'TEMP.PERFORM'
'umask 022'

call time( 'R' )
'mkdir' DIR 777
say 'RETVAL from mkdir is' RETVAL
do J = 1 to 20
MEM = DIR'/MBR'right( J, 3, 0 )
'open (MEM)' O_WRONLY+O_CREAT+O_TRUNC 666
Desc = RETVAL
L.1 = 'This is file ' MEM'15'x
'write' Desc 'L.1'
'close' Desc
end J
say 'Elapsed time for HFS is' time( 'R' )

trace N
DSN = userid()'.TEMP.PERFORM'
address 'MVS'

call time( 'R' )
RC = BPXWDYN( 'alloc rtddn(DD) space(1000) block(1000) dir(50)' ,
'new catalog dsn('DSN') msg(WTP)' )
RC = BPXWDYN( 'free dd('DD') msg(WTP)' )
do I = 1 to 20
MEM = 'MBR'right( I, 3, 0 )
RC = BPXWDYN( 'alloc rtddn(DD) shr dsn('DSN'('MEM') msg(WTP)' )
L.1 = 'This is member' MEM
'EXECIO 1 DISKW' DD '(finis stem L.'
RC = BPXWDYN( 'free dd('DD') msg(WTP)' )
end I
say 'Elapsed time for PDS is' time( 'R' )


The results (most favorable to PDS of a handful of tries):

Elapsed time for HFS is 0.075489
Elapsed time for PDS is 12.067157

An overhead factor of 150 or so is a powerful argument against
allocating individual PDS members. (Or an argument for using
z/OS UNIX files rather than legacy data sets.)

(Is there any way to get CPU time in Rexx?)

-- gil

[richard rozentals]

Hi,

Yes this is a short coming of z/os REXX. Yes I had the same problem in the past.  What I did was I wrote function. They way it works is that the PDS file is allocated once with a standard ALLOC. Then the function is called passing the member name. The function will do simple open of the member. I wrote my function in assembler. Sorry I don't have the code for this any more.

Several vendors have this type function.One that I use is CA's MAXREXX.  



Richard Rozentals




>________________________________
> From: Paul Gilmartin <PaulGB...@AIM.COM>
>To: TSO-...@VM.MARIST.EDU
>Sent: Saturday, November 17, 2012 3:00:52 PM
>Subject: [TSO-REXX] ALLOCATE/FREE performance (was: Executing a Rexx routine ...)

[Adrian Stern]

I'm not sure, but io from uss to mvs via BPXWDYN could be a high overhead on
its own.

-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of

Paul Gilmartin
Sent: den 17 november 2012 21:01
To: TSO-...@VM.MARIST.EDU
Subject: [TSO-REXX] ALLOCATE/FREE performance (was: Executing a Rexx routine
...)

[Gilbert C Cardenas]

I tried addressing the ISPEXEC in the LM commands but still received the same error.

74 *-* ADDRESS ISPEXEC "LMINIT DATAID(MYLIB) DATASET('"LIBNAME"')"
+++ RC(-3) +++

I know the rexx runs fine from TSO and batch (if I use ISPSTART and allocate all the appropriate libraries) however, I do not know anything about DB2 and the DBA has set up cobol stored procedures but not rexx.

I did run into Doug Nadel's "mlrexx.txt" and I started down that road but I believe I had a problem with loadlibs so I switched over to using LMMFIND.

I was hoping that someone might have gone down this road already and could offer some insight cause it is the blind leading the blind here. I don't know squat about DB2 and the DBA don't know squat about rexx.

Perhaps we need to cross-post on a DB2 forum?

Gilbert Cardenas
Manager of Computer Production
713-746-5741

This e-mail (and any attachments) may contain information that is confidential and/or protected by law. Any review, use, distribution or disclosure to anyone other than the intended recipient(s) is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and delete all copies of this message.

[Don Imbriale]

You are on the right track with your statement "if I ... allocate all the
appropriate libraries"

Wherever the DB2 stored procedure runs needs the ISPF libraries allocated
in order for you to use ISPF services.

- Don Imbriale

On Mon, Nov 19, 2012 at 10:15 AM, Gilbert C Cardenas <
Gilbert...@grocerybiz.com> wrote:

> I tried addressing the ISPEXEC in the LM commands but still received the
> same error.
>
> 74 *-* ADDRESS ISPEXEC "LMINIT DATAID(MYLIB) DATASET('"LIBNAME"')"
> +++ RC(-3) +++
>
> I know the rexx runs fine from TSO and batch (if I use ISPSTART and
> allocate all the appropriate libraries) however, I do not know anything
> about DB2 and the DBA has set up cobol stored procedures but not rexx.
>
> I did run into Doug Nadel's "mlrexx.txt" and I started down that road but
> I believe I had a problem with loadlibs so I switched over to using LMMFIND.
>
> I was hoping that someone might have gone down this road already and could
> offer some insight cause it is the blind leading the blind here. I don't
> know squat about DB2 and the DBA don't know squat about rexx.
>
> Perhaps we need to cross-post on a DB2 forum?
>
> Gilbert Cardenas
> Manager of Computer Production
> 713-746-5741
>
>

[Adrian Stern]

The ispf libraries need to be allocated in the dn2 started task!

-----Original Message-----
From: TSO REXX Discussion List [mailto:TSO-...@VM.MARIST.EDU] On Behalf Of