DB2 Stored Procedure in REXX

[Jeff Wang]

Hi all,

Can someone help me out? I've been struggling with this for almost a
week and still have no clue what's wrong.

Basically I want to write a DB2 stored procedure for OS/390 in REXX.
In this procedure it reads a dataset and return the first line of the
dataset. I met two problems:

Problem 1:
WLM environment does not start automatically when the DB2 stored
procedure is called.

Problem 2:
TSO Commands, such as ALLOCATE and FREE cannot be executed in the REXX
stored procedure, but TSO REXX commands can (like EXECIO, TE, TS).

My story is listed below:

REXX Procedure - JEFF.PDS.REXX(RXREAD1):

ARG fileName
say ADDRESS();
"ALLOC FI(FILEDD) DA('"fileName"') SHR REUSE"
"EXECIO 1 DISKR FILEDD (STEM lines."
fileTxt = lines.1
"EXECIO 0 DISKR FILEDD (FINIS"
"FREE FI(FILEDD)"
EXIT fileTxt
Command used to create the DB2 stored procedure JEFF.RXREAD1:

CREATE PROCEDURE RXREAD1(
IN FILENAME CHAR(44),
OUT FILETXT VARCHAR(256))
LANGUAGE REXX
EXTERNAL NAME RXREAD1
NO COLLID
NO SQL
ASUTIME NO LIMIT
RUN OPTIONS 'TRAP(ON)'
WLM ENVIRONMENT WLMENV1
PARAMETER STYLE GENERAL
SECURITY DB2
DYNAMIC RESULT SET 1
COMMIT ON RETURN NO;

Definition of WLM application environment WLMENV1:

Appl Environment Name . . WLMENV1
Description . . . . . . . Stored Procedures
Subsystem type . . . . . DB2
Procedure name . . . . . DSN1WLM1
Start parameters . . . . DB2SSN=&IWMSSNM,NUMTCB=2,APPLENV='WLMENV
1'
Limit on starting server address spaces for a subsystem instance:
No limit
I got a time-out error when I issued a CALL from the client program
(DB2 Client Command Center):
CALL RXREAD1("JEFF.TEST", ?)

It hangs there until return a time-out error message:

------------------------------ Command Entered
------------------------
CALL RXREAD1("JEFF.TEST", ?)
-----------------------------------------------------------------------
SQL0471N Invocation of routine "JEFF .RXREAD1 " failed
due to reason "00E79002". SQLSTATE=55023

So I read some manuals and wrote a WLM-environment start-up script and
put in the PROCLIB:
ADCD.ZOSV1R2.PROCLIB(DSN1WLM1)
//************************************************** ***********
//* JCL PROCEDURE FOR THE STARTUP OF THE
//* DB2 STORED PROCEDURES ADDRESS SPACE
//* RGN -- THE MVS REGION SIZE FOR THE ADDRESS SPACE.
//* DB2SSN -- THE DB2 SUBSYSTEM NAME.
//* NUMTCB -- THE NUMBER OF TCBS USED TO
//* PROCESS END USER REQUESTS.
//*
//************************************************** ***********
//DSN1WLM1 PROC RGN=24M,DB2SSN=DSN1,NUMTCB=8,APPLENV=
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
// PARM='&DB2SSN,&NUMTCB,&APPLENV.'
//STEPLIB DD DISP=SHR,DSN=DSN710.RUNLIB.LOAD
// DD DISP=SHR,DSN=CEE.SCEERUN
// DD DISP=SHR,DSN=DSN710.SDSNLOAD
//SYSEXEC DD DSN=JEFF.PDS.REXX,DISP=SHR
//SYSTSPRT DD SYSOUT=*
The time out problem still existed.
Then I wrote another JCL to start the WLM address space manually:
JEFF.PDS.REXX(JEFFCHEK)
//JEFFCHEK JOB (999,POK),'SAVE TIME',NOTIFY=&SYSUID.,
// CLASS=A,MSGCLASS=T,REGION=5000K,
//* TYPRUN=SCAN,
// MSGLEVEL=(1,1)
// EXEC DSN1WLM1,DB2SSN=DSN1,NUMTCB=8,APPLENV=WLMENV1
/*
//
I submited job JEFFCHEK and called the procedure again:

CALL RXREAD1("JEFF.TEST", ?)
------------------------------ Command Entered
------------------------
CALL RXREAD1("JEFF.LUCIDRDR", ?)
-----------------------------------------------------------------------
FILETXT: LINES.1
"RXREAD1" RETURN_STATUS: "0"
It returned something, but not the first line of the dataset. I
checked the output of SYSTSPRT of job JEFFCHEK, it has:
TSO
3 *-* "ALLOC FI(FILEDD) DA('"fileName"') SHR REUSE"
+++ RC(-3) +++
IRX0555E The input or output file FILEDD is not allocated. It cannot
be opened for I/O.
IRX0670E EXECIO error while trying to GET or PUT a record.
7 *-* "FREE FI(FILEDD)"
+++ RC(-3) +++

Based on my understanding, it means the host commands: ALLOC and FREE
could not be executed.

Can somebody tell me what's wrong? Any comments are welcome.

Thanks!

Jeff Wang
Afshar Consulting Group

[PM \(pm3iinc-nospam\)]

I think the file must be preallocated in the startup jcl.

PM
"Jeff Wang" <je**********@yahoo.com> a écrit dans le message de
news:99**************************@posting.google.c om...

Hi all,

Can someone help me out? I've been struggling with this for almost a
week and still have no clue what's wrong.

Basically I want to write a DB2 stored procedure for OS/390 in REXX.
In this procedure it reads a dataset and return the first line of the
dataset. I met two problems:

Problem 1:
WLM environment does not start automatically when the DB2 stored
procedure is called.

Problem 2:
TSO Commands, such as ALLOCATE and FREE cannot be executed in the REXX
stored procedure, but TSO REXX commands can (like EXECIO, TE, TS).

My story is listed below:

REXX Procedure - JEFF.PDS.REXX(RXREAD1):

ARG fileName
say ADDRESS();
"ALLOC FI(FILEDD) DA('"fileName"') SHR REUSE"
"EXECIO 1 DISKR FILEDD (STEM lines."
fileTxt = lines.1
"EXECIO 0 DISKR FILEDD (FINIS"
"FREE FI(FILEDD)"
EXIT fileTxt
Command used to create the DB2 stored procedure JEFF.RXREAD1:

CREATE PROCEDURE RXREAD1(
IN FILENAME CHAR(44),
OUT FILETXT VARCHAR(256))
LANGUAGE REXX
EXTERNAL NAME RXREAD1
NO COLLID
NO SQL
ASUTIME NO LIMIT
RUN OPTIONS 'TRAP(ON)'
WLM ENVIRONMENT WLMENV1
PARAMETER STYLE GENERAL
SECURITY DB2
DYNAMIC RESULT SET 1
COMMIT ON RETURN NO;

Definition of WLM application environment WLMENV1:

Appl Environment Name . . WLMENV1
Description . . . . . . . Stored Procedures
Subsystem type . . . . . DB2
Procedure name . . . . . DSN1WLM1
Start parameters . . . . DB2SSN=&IWMSSNM,NUMTCB=2,APPLENV='WLMENV
1'
Limit on starting server address spaces for a subsystem instance:
No limit
I got a time-out error when I issued a CALL from the client program
(DB2 Client Command Center):
CALL RXREAD1("JEFF.TEST", ?)

It hangs there until return a time-out error message:

------------------------------ Command Entered
------------------------
CALL RXREAD1("JEFF.TEST", ?)
-----------------------------------------------------------------------
SQL0471N Invocation of routine "JEFF .RXREAD1 " failed
due to reason "00E79002". SQLSTATE=55023

So I read some manuals and wrote a WLM-environment start-up script and
put in the PROCLIB:
ADCD.ZOSV1R2.PROCLIB(DSN1WLM1)
//************************************************** ***********
//* JCL PROCEDURE FOR THE STARTUP OF THE
//* DB2 STORED PROCEDURES ADDRESS SPACE
//* RGN -- THE MVS REGION SIZE FOR THE ADDRESS SPACE.
//* DB2SSN -- THE DB2 SUBSYSTEM NAME.
//* NUMTCB -- THE NUMBER OF TCBS USED TO
//* PROCESS END USER REQUESTS.
//*
//************************************************** ***********
//DSN1WLM1 PROC RGN=24M,DB2SSN=DSN1,NUMTCB=8,APPLENV=
//IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
// PARM='&DB2SSN,&NUMTCB,&APPLENV.'
//STEPLIB DD DISP=SHR,DSN=DSN710.RUNLIB.LOAD
// DD DISP=SHR,DSN=CEE.SCEERUN
// DD DISP=SHR,DSN=DSN710.SDSNLOAD
//SYSEXEC DD DSN=JEFF.PDS.REXX,DISP=SHR
//SYSTSPRT DD SYSOUT=*
The time out problem still existed.
Then I wrote another JCL to start the WLM address space manually:
JEFF.PDS.REXX(JEFFCHEK)
//JEFFCHEK JOB (999,POK),'SAVE TIME',NOTIFY=&SYSUID.,
// CLASS=A,MSGCLASS=T,REGION=5000K,
//* TYPRUN=SCAN,
// MSGLEVEL=(1,1)
// EXEC DSN1WLM1,DB2SSN=DSN1,NUMTCB=8,APPLENV=WLMENV1
/*
//
I submited job JEFFCHEK and called the procedure again:

CALL RXREAD1("JEFF.TEST", ?)
------------------------------ Command Entered
------------------------
CALL RXREAD1("JEFF.LUCIDRDR", ?)
-----------------------------------------------------------------------
FILETXT: LINES.1
"RXREAD1" RETURN_STATUS: "0"
It returned something, but not the first line of the dataset. I
checked the output of SYSTSPRT of job JEFFCHEK, it has:
TSO
3 *-* "ALLOC FI(FILEDD) DA('"fileName"') SHR REUSE"
+++ RC(-3) +++
IRX0555E The input or output file FILEDD is not allocated. It cannot
be opened for I/O.
IRX0670E EXECIO error while trying to GET or PUT a record.
7 *-* "FREE FI(FILEDD)"
+++ RC(-3) +++

Based on my understanding, it means the host commands: ALLOC and FREE
could not be executed.

Can somebody tell me what's wrong? Any comments are welcome.

Thanks!

Jeff Wang
Afshar Consulting Group

[PM \(pm3iinc-nospam\)]

Maybe you can use something like dynalloc?

b.t.w. my rexx DB2 SP skills is pretty low...

PM

[Heinz Buehler]

i tried to recreate your proc on my test system
(z/OS 1.4, DB2 V7.1), and found:

- WLM address space starts correctly
(note that you must set NUMTCB to 1 for REXX SP's).

- The REXX Stored procedure correctly receives the
file name that is passed to it as an argument.

- my TSO ALLOC command gets RC(-3) just as yours.

Now, in the REXX REFERENCE manual, Chapter 8.6,
'Summary of Writing Execs for Different Address Spaces',
there is a summary table listing the the REXX keyword instructions,
built- in functions, TSO/E external functions, TSO/E REXX commands,
and other services you can use for execs that run in TSO/E and
non-TSO/E address spaces.

ALLOCATE seems to be available *ONLY* in TSO/E address spaces.
EXECIO however is available.

What could be a solution is to link to a program that can do
DYNALLOC, but you would have to write that.

[Jeff Wang]

Thank you PM and heinz. I appreciate your help.

I changed NUMTCB to 1, WLM address space still didn't start
automatically. I am checking it.

Yes, I read the REXX REFERENCE manual, it says that ALLOCATE is only
available in TSO/E address spaces. I printed out the running address
space using "say ADDRESS()" in the REXX script and it returned "TSO".
Did it mean that the REXX procedure was actually running in TSO/E
address space? If yes, why couldn't it execute ALLOCATE statement?

Never used DYNALLOC before. What kind of program can do DYNALLOC?

Thanks again,
Jeff
he***********@gmx.net (Heinz Buehler) wrote in message news:<5f**************************@posting.google. com>...

i tried to recreate your proc on my test system
(z/OS 1.4, DB2 V7.1), and found:

- WLM address space starts correctly
(note that you must set NUMTCB to 1 for REXX SP's).

- The REXX Stored procedure correctly receives the
file name that is passed to it as an argument.

- my TSO ALLOC command gets RC(-3) just as yours.

Now, in the REXX REFERENCE manual, Chapter 8.6,
'Summary of Writing Execs for Different Address Spaces',
there is a summary table listing the the REXX keyword instructions,
built- in functions, TSO/E external functions, TSO/E REXX commands,
and other services you can use for execs that run in TSO/E and
non-TSO/E address spaces.

ALLOCATE seems to be available *ONLY* in TSO/E address spaces.
EXECIO however is available.

What could be a solution is to link to a program that can do
DYNALLOC, but you would have to write that.

[PM \(pm3iinc-nospam\)]

Dynalloc is most likely encoutered as an assembler / cobol module that you
call. (ask your sysprog)
it's a macro for the io svc 99.
It's used to query/modify the 'file' control blocks.
I'm pretty sure you'll find something about it on the net if you search for
: dynalloc rexx svc 99

There are many uses for it.
ex : you have a dd in your jcl, in your pgm, you reassign the dd to a new
dsn, open it, read it....
You can again reassign the dd to a new dsn, open it, read it....
You normally use this if you can't concat the dsn in the jcl (for whatever
reason. ex: no key, no date, ...)
dd dsn
dsn
dsn
or you don't want to use 20 dd in the jcl and programatically find what file
contains what...

Often used in situations like :
tso list* lvl(...) in dsnx
pgm1 has dd dsny
pgm1 opens dsnx that contains file names and boucles with dynalloc to
process them.

I can't help you more on this topic as my rexx/exec2 knowledge is fading...

PM

[Jeff Wang]

PM,

Thanks a lot for your help. I also learned from David on another board
that I can use BPXWDYN in REXX, which probably invokes dynalloc
function. However, it seems that BPXWDYN is only supported in some
latest version of z/OS. If I need to run the REXX procedure on OS/390,
I may still need to write another module to call dynalloc function, as
you said.

Thanks again,
Jeff

"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message news:<Cb*********************@news20.bellglobal.co m>...

Dynalloc is most likely encoutered as an assembler / cobol module that you
call. (ask your sysprog)
it's a macro for the io svc 99.
It's used to query/modify the 'file' control blocks.
I'm pretty sure you'll find something about it on the net if you search for
: dynalloc rexx svc 99

There are many uses for it.
ex : you have a dd in your jcl, in your pgm, you reassign the dd to a new
dsn, open it, read it....
You can again reassign the dd to a new dsn, open it, read it....
You normally use this if you can't concat the dsn in the jcl (for whatever
reason. ex: no key, no date, ...)
dd dsn
dsn
dsn
or you don't want to use 20 dd in the jcl and programatically find what file
contains what...

Often used in situations like :
tso list* lvl(...) in dsnx
pgm1 has dd dsny
pgm1 opens dsnx that contains file names and boucles with dynalloc to
process them.

I can't help you more on this topic as my rexx/exec2 knowledge is fading...

PM

[Heinz Buehler]

Jeff,
this REXX procedure works now,
but we are on z/OS 1.4 ...

/*REXX*/
ARG fileName
say ' '
say 'STORED PROCEDURE: RXREAD1'
say 'Filename :'fileName ;
- - - - - - - - - - - - - - - - 12 Line(s) not
fileName = "'" !! strip(fileName) !! "'"
a_string = 'alloc dd(FILEDD) da('filename') shr reuse'
say a_string

RX_RC = BPXWDYN(a_string)
do i=1 to s99msg.0
say s99msg.i
end
say 'ALLOC RC='Rx_RC
drop lines

"EXECIO 1 DISKR FILEDD (STEM lines."
fileTxt = lines.1
say 'File Text (first Line):'fileTxt

"EXECIO 0 DISKR FILEDD (FINIS"

Rx_RC = bpxwdyn("FREE FI(FILEDD)")
do i=1 to s99msg.0
say s99msg.i
end
say 'FREE RC='Rx_RC

EXIT fileTxt

regards, Heinz