SQL Procedures Processor (?) in WLM-managed SP JCL

Lockwood Lyon

SQL Procedures Processor (?) in WLM-managed SP JCL
List,

We're about to implement WLM-managed Stored Procedures (We're DB2 V6 on OS/390 2.8). In the JCL "generated" as part of DB2 subsystem install/migration we've got some "sample" JCL for the WLM address space(s). Regrettably, there's lots more in the JCL than appears in the samples in the "Getting Started with DB2 Stored Procedures" Redbook; specifically, there's a mention of ... well, see for yourself below.

Question: What's the "SQL Procedures Processor" ?

Question: Anyone know why I'd care about the "Location of DSNTPSMP" ?

Question: Is there a simple, short(er) way to do this?

Thanks!

- - LL

//DST1WLM1 JOB ADMINI,MSGLEVEL=(1,1),MSGCLASS=Y
//*
//DST1WLM PROC DB2SSN=DST1,NUMTCB=8,APPLENV=SPTEENV1
//*
//DSNTPSMP EXEC PGM=DSNX9WLM,TIME=1440,
// PARM='&DB2SSN,&NUMTCB,&APPLENV',
// REGION=0M
//*
//STEPLIB DD DISP=SHR,DSN=SYS1.DST1.DSNLOAD
// DD DISP=SHR,DSN=SYS1.CBC.SCBCCMP
// DD DISP=SHR,DSN=SYS1.CEE.SCEERUN
//*
//SYSEXEC DD DISP=SHR, <== Location of DSNTPSMP
// DSN=DSN.V61.SDSNCLST
//*
//SYSTSPRT DD SYSOUT=*
//CEEDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABEND DD DUMMY
//DSNTRACE DD SYSOUT=*
//*
//**** Data sets required by the SQL Procedures Processor
//*
//SQLDBRM DD DISP=SHR, <== DBRM Library
// DSN=DSN.V61.DBRMLIB.DATA
//SQLCSRC DD DISP=SHR, <== Generated C Source
// DSN=DSN.V61.SRCLIB.DATA
//SQLLMOD DD DISP=SHR, <== Application Loadlib
// DSN=DSN.V61.RUNLIB.LOAD
//SQLLIBC DD DISP=SHR, <== C header files
// DSN=SYS1.CEE.SCEEH.H
// DD DISP=SHR,
// DSN=SYS1.CEE.SCEEH.SYS.H
//SQLLIBL DD DISP=SHR, <== Linkedit includes
// DSN=SYS1.CEE.SCEELKED
// DD DISP=SHR,
// DSN=DSN.V61.SDSNLOAD
//SYSMSGS DD DISP=SHR, <== Prelinker msg file
// DSN=SYS1.CEE.SCEEMSGP(EDCPMSGE)
//*
//**** Workfiles required by the SQL Procedures Processor
//*
//SQLSRC DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=FB,LRECL=80)
//SQLPRINT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=VB,LRECL=137)
//SQLTERM DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=VB,LRECL=137)
//SQLOUT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=VB,LRECL=137)
//SQLCPRT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=VB,LRECL=137)
//SQLUT1 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=FB,LRECL=80)
//SQLUT2 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
// DCB=(RECFM=FB,LRECL=80)
//SQLCIN DD UNIT=SYSDA,SPACE=(32000,(20,20))
//SQLLIN DD UNIT=SYSDA,SPACE=(8000,(30,30)),
// DCB=(RECFM=FB,LRECL=80)
//SQLDUMMY DD DUMMY
//SYSMOD DD UNIT=SYSDA,SPACE=(16000,(20,20)), <= PRELINKER
// DCB=(RECFM=FB,LRECL=80)
//*
// PEND
//*
//******************************************************************
//*
// EXEC DST1WLM


Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are mine and not necessarily those of my employer, Meijer, Inc.



Myron Miller

Re: SQL Procedures Processor (?) in WLM-managed SP JCL
(in response to Lockwood Lyon)
Lockwood,
1) The SQL Procedures Processor is an address space
that executes the DSNTPSMP stored procedure. This is
the stored procedure that actually processes the
output from the Stored Procedure Builder.
2) You need to know where DSNTPSMP is located if
you're planning on using SPB on your pc's. If not,
then you don't have to worry about it.

Unfortunately there's no shortcut. It does take a
little bit to get set up. Especially if you're using
the SPB. BUT once you've got it setup, you can
exceptionally quickly generate complex stored
procedures. Its a great tool. And WLM gives you
great flexibility in scheduling and managing the
Stored Procedure environment.

Myron

--- Lockwood Lyon <[login to unmask email]> wrote:
> List,
>
> We're about to implement WLM-managed Stored
> Procedures (We're DB2 V6 on OS/390 2.8). In the
> JCL "generated" as part of DB2 subsystem
> install/migration we've got some "sample" JCL for
> the WLM address space(s). Regrettably, there's lots
> more in the JCL than appears in the samples in the
> "Getting Started with DB2 Stored Procedures"
> Redbook; specifically, there's a mention of ...
> well, see for yourself below.
>
> Question: What's the "SQL Procedures Processor" ?
>
> Question: Anyone know why I'd care about the
> "Location of DSNTPSMP" ?
>
> Question: Is there a simple, short(er) way to do
> this?
>
> Thanks!
>
> - - LL
>
> //DST1WLM1 JOB ADMINI,MSGLEVEL=(1,1),MSGCLASS=Y
> //*
> //DST1WLM PROC DB2SSN=DST1,NUMTCB=8,APPLENV=SPTEENV1
> //*
> //DSNTPSMP EXEC PGM=DSNX9WLM,TIME=1440,
> // PARM='&DB2SSN,&NUMTCB,&APPLENV',
> // REGION=0M
> //*
> //STEPLIB DD DISP=SHR,DSN=SYS1.DST1.DSNLOAD
> // DD DISP=SHR,DSN=SYS1.CBC.SCBCCMP
> // DD DISP=SHR,DSN=SYS1.CEE.SCEERUN
> //*
> //SYSEXEC DD DISP=SHR, <==
> Location of DSNTPSMP
> // DSN=DSN.V61.SDSNCLST
> //*
> //SYSTSPRT DD SYSOUT=*
> //CEEDUMP DD SYSOUT=*
> //SYSPRINT DD SYSOUT=*
> //SYSABEND DD DUMMY
> //DSNTRACE DD SYSOUT=*
> //*
> //**** Data sets required by the SQL Procedures
> Processor
> //*
> //SQLDBRM DD DISP=SHR, <==
> DBRM Library
> // DSN=DSN.V61.DBRMLIB.DATA
> //SQLCSRC DD DISP=SHR, <==
> Generated C Source
> // DSN=DSN.V61.SRCLIB.DATA
> //SQLLMOD DD DISP=SHR, <==
> Application Loadlib
> // DSN=DSN.V61.RUNLIB.LOAD
> //SQLLIBC DD DISP=SHR, <== C
> header files
> // DSN=SYS1.CEE.SCEEH.H
> // DD DISP=SHR,
> // DSN=SYS1.CEE.SCEEH.SYS.H
> //SQLLIBL DD DISP=SHR, <==
> Linkedit includes
> // DSN=SYS1.CEE.SCEELKED
> // DD DISP=SHR,
> // DSN=DSN.V61.SDSNLOAD
> //SYSMSGS DD DISP=SHR, <==
> Prelinker msg file
> // DSN=SYS1.CEE.SCEEMSGP(EDCPMSGE)
> //*
> //**** Workfiles required by the SQL Procedures
> Processor
> //*
> //SQLSRC DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLPRINT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLTERM DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLOUT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLCPRT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLUT1 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLUT2 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLCIN DD UNIT=SYSDA,SPACE=(32000,(20,20))
> //SQLLIN DD UNIT=SYSDA,SPACE=(8000,(30,30)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLDUMMY DD DUMMY
> //SYSMOD DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> <= PRELINKER
> // DCB=(RECFM=FB,LRECL=80)
> //*
> // PEND
> //*
>
//******************************************************************
> //*
> // EXEC DST1WLM
>
>
> Lockwood Lyon -- Meijer Technical Support
> (616) 735-7553 (office)
> (616) 791-5131 (fax)
>
> Copyright (c) 2000 by Lockwood Lyon. All rights
> reserved. These opinions are mine and not
> necessarily those of my employer, Meijer, Inc.
>
> =======================To
> change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/



Lockwood Lyon

Re: SQL Procedures Processor (?) in WLM-managed SP JCL
(in response to Myron Miller)
Thanks, Myron. We don't plan on using the SP Builder immediately, so I'll worry about that part later.

Meanwhile, another newbie question: We've implemented DB2 Data Sharing. We have a four-way DS group with 4 (surprise) DB2s: DSP1, DSP2, DSP3, and DSP4. We want to implement WLM-managed SPs. How many WLM JCL procs do I need?

First, let's assume we're only implementing a single Application Environment (we aren't, but ignore that for the moment.) Do I use one WLM proc for the DS group (with symbolics somwhere), or four procs, one for each DB2 subsystem?

So, for example, we create Application Environment DSP1ENV1. (*Was this a good idea, embedding the subsystem name in the AE name? Shouldn't we use the DS group name instead? I think we only need one AE for our group, not four.) In the AE definition we're supposed to place the "Procedure Name", i.e. (I think) a member of a JCL library. If that member is (say) DSP1WLM1, how do we handle different invocations of stored procedure SP1 from different subsystems?

Ack! My brain hurts! Any simple explanations, or suggestions on where this might be documented?

- - LL

Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are mine and not necessarily those of my employer, Meijer, Inc.

>>> [login to unmask email] 01/03/01 02:57PM >>>
Lockwood,
1) The SQL Procedures Processor is an address space
that executes the DSNTPSMP stored procedure. This is
the stored procedure that actually processes the
output from the Stored Procedure Builder.
2) You need to know where DSNTPSMP is located if
you're planning on using SPB on your pc's. If not,
then you don't have to worry about it.

Unfortunately there's no shortcut. It does take a
little bit to get set up. Especially if you're using
the SPB. BUT once you've got it setup, you can
exceptionally quickly generate complex stored
procedures. Its a great tool. And WLM gives you
great flexibility in scheduling and managing the
Stored Procedure environment.

Myron

--- Lockwood Lyon <[login to unmask email]> wrote:
> List,
>
> We're about to implement WLM-managed Stored
> Procedures (We're DB2 V6 on OS/390 2.8). In the
> JCL "generated" as part of DB2 subsystem
> install/migration we've got some "sample" JCL for
> the WLM address space(s). Regrettably, there's lots
> more in the JCL than appears in the samples in the
> "Getting Started with DB2 Stored Procedures"
> Redbook; specifically, there's a mention of ...
> well, see for yourself below.
>
> Question: What's the "SQL Procedures Processor" ?
>
> Question: Anyone know why I'd care about the
> "Location of DSNTPSMP" ?
>
> Question: Is there a simple, short(er) way to do
> this?
>
> Thanks!
>
> - - LL
>
> //DST1WLM1 JOB ADMINI,MSGLEVEL=(1,1),MSGCLASS=Y
> //*
> //DST1WLM PROC DB2SSN=DST1,NUMTCB=8,APPLENV=SPTEENV1
> //*
> //DSNTPSMP EXEC PGM=DSNX9WLM,TIME=1440,
> // PARM='&DB2SSN,&NUMTCB,&APPLENV',
> // REGION=0M
> //*
> //STEPLIB DD DISP=SHR,DSN=SYS1.DST1.DSNLOAD
> // DD DISP=SHR,DSN=SYS1.CBC.SCBCCMP
> // DD DISP=SHR,DSN=SYS1.CEE.SCEERUN
> //*
> //SYSEXEC DD DISP=SHR, <==
> Location of DSNTPSMP
> // DSN=DSN.V61.SDSNCLST
> //*
> //SYSTSPRT DD SYSOUT=*
> //CEEDUMP DD SYSOUT=*
> //SYSPRINT DD SYSOUT=*
> //SYSABEND DD DUMMY
> //DSNTRACE DD SYSOUT=*
> //*
> //**** Data sets required by the SQL Procedures
> Processor
> //*
> //SQLDBRM DD DISP=SHR, <==
> DBRM Library
> // DSN=DSN.V61.DBRMLIB.DATA
> //SQLCSRC DD DISP=SHR, <==
> Generated C Source
> // DSN=DSN.V61.SRCLIB.DATA
> //SQLLMOD DD DISP=SHR, <==
> Application Loadlib
> // DSN=DSN.V61.RUNLIB.LOAD
> //SQLLIBC DD DISP=SHR, <== C
> header files
> // DSN=SYS1.CEE.SCEEH.H
> // DD DISP=SHR,
> // DSN=SYS1.CEE.SCEEH.SYS.H
> //SQLLIBL DD DISP=SHR, <==
> Linkedit includes
> // DSN=SYS1.CEE.SCEELKED
> // DD DISP=SHR,
> // DSN=DSN.V61.SDSNLOAD
> //SYSMSGS DD DISP=SHR, <==
> Prelinker msg file
> // DSN=SYS1.CEE.SCEEMSGP(EDCPMSGE)
> //*
> //**** Workfiles required by the SQL Procedures
> Processor
> //*
> //SQLSRC DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLPRINT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLTERM DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLOUT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLCPRT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=VB,LRECL=137)
> //SQLUT1 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLUT2 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLCIN DD UNIT=SYSDA,SPACE=(32000,(20,20))
> //SQLLIN DD UNIT=SYSDA,SPACE=(8000,(30,30)),
> // DCB=(RECFM=FB,LRECL=80)
> //SQLDUMMY DD DUMMY
> //SYSMOD DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> <= PRELINKER
> // DCB=(RECFM=FB,LRECL=80)
> //*
> // PEND
> //*
>
//******************************************************************
> //*
> // EXEC DST1WLM
>
>
> Lockwood Lyon -- Meijer Technical Support
> (616) 735-7553 (office)
> (616) 791-5131 (fax)
>
> Copyright (c) 2000 by Lockwood Lyon. All rights
> reserved. These opinions are mine and not
> necessarily those of my employer, Meijer, Inc.
>
> =======================To
> change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/






Myron Miller

Re: SQL Procedures Processor (?) in WLM-managed SP JCL
(in response to Lockwood Lyon)
Lyon,
I haven't answered this before because DB2 Data
Sharing is not my speciality. But here's my gut
feeling.

I would think that you'd need for one WLM JCL Proc.
naming it for the DS group to match the WLM
application environment. Since the WLM AE is shared
over the Data sharing group over each processor, WLM
can decide depending upon the workload on each
processor where SP1 will actually run (ie which
processor to start the WLM address space).
--- Lockwood Lyon <[login to unmask email]> wrote:
> Thanks, Myron. We don't plan on using the SP
> Builder immediately, so I'll worry about that part
> later.
>
> Meanwhile, another newbie question: We've
> implemented DB2 Data Sharing. We have a four-way DS
> group with 4 (surprise) DB2s: DSP1, DSP2, DSP3, and
> DSP4. We want to implement WLM-managed SPs. How
> many WLM JCL procs do I need?
>
> First, let's assume we're only implementing a single
> Application Environment (we aren't, but ignore that
> for the moment.) Do I use one WLM proc for the DS
> group (with symbolics somwhere), or four procs, one
> for each DB2 subsystem?
>
> So, for example, we create Application Environment
> DSP1ENV1. (*Was this a good idea, embedding the
> subsystem name in the AE name? Shouldn't we use the
> DS group name instead? I think we only need one AE
> for our group, not four.) In the AE definition
> we're supposed to place the "Procedure Name", i.e.
> (I think) a member of a JCL library. If that member
> is (say) DSP1WLM1, how do we handle different
> invocations of stored procedure SP1 from different
> subsystems?
>
> Ack! My brain hurts! Any simple explanations, or
> suggestions on where this might be documented?
>
> - - LL
>
> Lockwood Lyon -- Meijer Technical Support
> (616) 735-7553 (office)
> (616) 791-5131 (fax)
>
> Copyright (c) 2000 by Lockwood Lyon. All rights
> reserved. These opinions are mine and not
> necessarily those of my employer, Meijer, Inc.
>
> >>> [login to unmask email] 01/03/01 02:57PM >>>
> Lockwood,
> 1) The SQL Procedures Processor is an address
> space
> that executes the DSNTPSMP stored procedure. This
> is
> the stored procedure that actually processes the
> output from the Stored Procedure Builder.
> 2) You need to know where DSNTPSMP is located if
> you're planning on using SPB on your pc's. If not,
> then you don't have to worry about it.
>
> Unfortunately there's no shortcut. It does take a
> little bit to get set up. Especially if you're
> using
> the SPB. BUT once you've got it setup, you can
> exceptionally quickly generate complex stored
> procedures. Its a great tool. And WLM gives you
> great flexibility in scheduling and managing the
> Stored Procedure environment.
>
> Myron
>
> --- Lockwood Lyon <[login to unmask email]> wrote:
> > List,
> >
> > We're about to implement WLM-managed Stored
> > Procedures (We're DB2 V6 on OS/390 2.8). In the
> > JCL "generated" as part of DB2 subsystem
> > install/migration we've got some "sample" JCL for
> > the WLM address space(s). Regrettably, there's
> lots
> > more in the JCL than appears in the samples in the
> > "Getting Started with DB2 Stored Procedures"
> > Redbook; specifically, there's a mention of ...
> > well, see for yourself below.
> >
> > Question: What's the "SQL Procedures Processor" ?
> >
> > Question: Anyone know why I'd care about the
> > "Location of DSNTPSMP" ?
> >
> > Question: Is there a simple, short(er) way to do
> > this?
> >
> > Thanks!
> >
> > - - LL
> >
> > //DST1WLM1 JOB ADMINI,MSGLEVEL=(1,1),MSGCLASS=Y
> > //*
> > //DST1WLM PROC
> DB2SSN=DST1,NUMTCB=8,APPLENV=SPTEENV1
> > //*
> > //DSNTPSMP EXEC PGM=DSNX9WLM,TIME=1440,
> > // PARM='&DB2SSN,&NUMTCB,&APPLENV',
> > // REGION=0M
> > //*
> > //STEPLIB DD DISP=SHR,DSN=SYS1.DST1.DSNLOAD
> > // DD DISP=SHR,DSN=SYS1.CBC.SCBCCMP
> > // DD DISP=SHR,DSN=SYS1.CEE.SCEERUN
> > //*
> > //SYSEXEC DD DISP=SHR, <==
> > Location of DSNTPSMP
> > // DSN=DSN.V61.SDSNCLST
> > //*
> > //SYSTSPRT DD SYSOUT=*
> > //CEEDUMP DD SYSOUT=*
> > //SYSPRINT DD SYSOUT=*
> > //SYSABEND DD DUMMY
> > //DSNTRACE DD SYSOUT=*
> > //*
> > //**** Data sets required by the SQL Procedures
> > Processor
> > //*
> > //SQLDBRM DD DISP=SHR, <==
> > DBRM Library
> > // DSN=DSN.V61.DBRMLIB.DATA
> > //SQLCSRC DD DISP=SHR, <==
> > Generated C Source
> > // DSN=DSN.V61.SRCLIB.DATA
> > //SQLLMOD DD DISP=SHR, <==
> > Application Loadlib
> > // DSN=DSN.V61.RUNLIB.LOAD
> > //SQLLIBC DD DISP=SHR, <== C
> > header files
> > // DSN=SYS1.CEE.SCEEH.H
> > // DD DISP=SHR,
> > // DSN=SYS1.CEE.SCEEH.SYS.H
> > //SQLLIBL DD DISP=SHR, <==
> > Linkedit includes
> > // DSN=SYS1.CEE.SCEELKED
> > // DD DISP=SHR,
> > // DSN=DSN.V61.SDSNLOAD
> > //SYSMSGS DD DISP=SHR, <==
> > Prelinker msg file
> > // DSN=SYS1.CEE.SCEEMSGP(EDCPMSGE)
> > //*
> > //**** Workfiles required by the SQL Procedures
> > Processor
> > //*
> > //SQLSRC DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=FB,LRECL=80)
> > //SQLPRINT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=VB,LRECL=137)
> > //SQLTERM DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=VB,LRECL=137)
> > //SQLOUT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=VB,LRECL=137)
> > //SQLCPRT DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=VB,LRECL=137)
> > //SQLUT1 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=FB,LRECL=80)
> > //SQLUT2 DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > // DCB=(RECFM=FB,LRECL=80)
> > //SQLCIN DD UNIT=SYSDA,SPACE=(32000,(20,20))
> > //SQLLIN DD UNIT=SYSDA,SPACE=(8000,(30,30)),
> > // DCB=(RECFM=FB,LRECL=80)
> > //SQLDUMMY DD DUMMY
> > //SYSMOD DD UNIT=SYSDA,SPACE=(16000,(20,20)),
> > <= PRELINKER
> > // DCB=(RECFM=FB,LRECL=80)
> > //*
> > // PEND
> > //*
> >
>
//******************************************************************
> > //*
> > // EXEC DST1WLM
> >
> >
> > Lockwood Lyon -- Meijer Technical Support
> > (616) 735-7553 (office)
> > (616) 791-5131 (fax)
> >
> > Copyright (c) 2000 by Lockwood Lyon. All rights
> > reserved. These opinions are mine and not
> > necessarily those of my employer, Meijer, Inc.
> >
> > =======================To
> > change your subscription options or to cancel your
> > subscription visit the DB2-L webpage at
> > http://www.ryci.com/db2-l. The owners of the list
> > can
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Photos - Share your holiday photos online!
> http://photos.yahoo.com/
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can
>
> =======================To
> change your subscription options or to cancel your
> subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/



Lockwood Lyon

Re: SQL Procedures Processor (?) in WLM-managed SP JCL
(in response to Myron Miller)
Dear List,

I don't think WLM works correctly this way ... not sure. help.

When defining the Application Environment (plex-wide) you get to specify a single ProcName and pass it parms.

So, for example, if you have DB2A and DB2B in a data sharing group (maybe on the same LPAR). A stored procedure SP1 gets invoked from an appl on DB2A (or from its DDF). DB2A checks in SYSIBM.SYSROUTINES for the WLM_ENVIRONMENT value, and invokes WLM. WLM notes the subsystem ID, finds the Proc (say, DB2AWLM1) in the ProcLib, (optionally) passes parameters, and starts the address space.

Note, however, that if SP1 is invoked from DB2B that the same thing happens. SYSROUTINES, WLM_ENV, and WLM kicks off the same Proc DB2AWLM1 from Proclib! Or, worse, it says "Hey, we already got this address space running, run SP1 there!" Unfortunately, the DB2AWLM1 that's running is connected to DB2A, not DB2B.

Note this comment from redbook Getting Started with DB2 Stored Procedures: "... because you cannot have a DB2 member receiving an SQL CALL statement, passing this SQL CALL statement to be executed on another member in the Sysplex." (p 32.)

So this is very complex and confusing for me. Here's what I think: For multiple DB2s in the same LPAR (yes, it might happen) that are in a data sharing group, they: (a) share a DB2 Catalog; hence (b) have the same entry for a stored procedure; which c) runs in a single Application Environment; which (d) defines a single Proc in Proclib; which (e) has only one Job Name.

So how do you get stored procedures invoked from different DB2s to execute in Different WLM-started tasks that connect to Different DB2s?

My brain hurts again!

- - LL


Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are mine and not necessarily those of my employer, Meijer, Inc.
>


Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are mine and not necessarily those of my employer, Meijer, Inc.

Lee Hayden

Re: SQL Procedures Processor (?) in WLM-managed SP JCL
(in response to Lockwood Lyon)
I admit that I have no experience here but it seems to me that to get this
to work
you would have "shared" and "not shared" Stored procedure procs.
i.e. DB2GWLM1 would attach to Group DB2G which contains DB2A and DB2B.
The "shared" procs should be connecting to the GROUP name, if it is
possible for that stored procedure to run on any DB2 in that data sharing
group.
As I understand it CAF and RRS attach support Group attach.

This does seem to imply that you cannot mix "shared" and "not shared"
stored procedures in the same application. I believe there are also some
Data Sharing implications in DDF.

-----Original Message-----
From: Lockwood Lyon [mailto:[login to unmask email]
Sent: Tuesday, January 09, 2001 4:17 PM
Subject: Re: SQL Procedures Processor (?) in WLM-managed SP JCL




Dear List,

I don't think WLM works correctly this way ... not sure. help.

When defining the Application Environment (plex-wide) you get to specify a
single ProcName and pass it parms.

So, for example, if you have DB2A and DB2B in a data sharing group (maybe on
the same LPAR). A stored procedure SP1 gets invoked from an appl on DB2A
(or from its DDF). DB2A checks in SYSIBM.SYSROUTINES for the
WLM_ENVIRONMENT value, and invokes WLM. WLM notes the subsystem ID, finds
the Proc (say, DB2AWLM1) in the ProcLib, (optionally) passes parameters, and
starts the address space.

Note, however, that if SP1 is invoked from DB2B that the same thing happens.
SYSROUTINES, WLM_ENV, and WLM kicks off the same Proc DB2AWLM1 from Proclib!
Or, worse, it says "Hey, we already got this address space running, run SP1
there!" Unfortunately, the DB2AWLM1 that's running is connected to DB2A,
not DB2B.

Note this comment from redbook Getting Started with DB2 Stored Procedures:
"... because you cannot have a DB2 member receiving an SQL CALL statement,
passing this SQL CALL statement to be executed on another member in the
Sysplex." (p 32.)

So this is very complex and confusing for me. Here's what I think: For
multiple DB2s in the same LPAR (yes, it might happen) that are in a data
sharing group, they: (a) share a DB2 Catalog; hence (b) have the same entry
for a stored procedure; which c) runs in a single Application Environment;
which (d) defines a single Proc in Proclib; which (e) has only one Job Name.

So how do you get stored procedures invoked from different DB2s to execute
in Different WLM-started tasks that connect to Different DB2s?

My brain hurts again!

- - LL


Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are
mine and not necessarily those of my employer, Meijer, Inc.
>


Lockwood Lyon -- Meijer Technical Support
(616) 735-7553 (office)
(616) 791-5131 (fax)

Copyright (c) 2000 by Lockwood Lyon. All rights reserved. These opinions are
mine and not necessarily those of my employer, Meijer, Inc.