Hi All,

I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the
IBM Stored Procedure guide (SG24-7083-00). The database calls to read and
update the database work fine...however, I can't seem to figure out how to
pass parms to the C Program. The compile, bind, and run using DB2BATCH all
work fine, however, when I attempt to access any values passed into the
program, they're not present.

Here's part of the JCL I'm using to run the proc:

//EMSDB2 EXEC PGM=DB2BATCH,DYNAMNBR=20
//STEPLIB DD DSN=DBMT.TEST.DSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
//SYSTSIN DD *
DSN SYSTEM(DBMS)
RUN PROGRAM(PTMFMBID) PLAN(PTMFMBID) PARMS('TEST') -
LIB('NGST.MCMILP2.DB2.LOADLIB' )
END
//*

In my C program (PTMFMBID), I'm attempting to access argv[1] (which I think
should be "TEST", based on the call above). This parm is simply not being
passed in.

My create proc statements look like this:

CREATE PROCEDURE TMFDBC.PTMFMBID
(
MAILBOXID VARCHAR(8) IN
)
EXTERNAL NAME PTMFMBID LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS
....

Have I missed something?

Paul M wrote:

> Hi All,
>
> I'm currently writing a z/OS DB2 Stored Proc in C, using an example from
> the
> IBM Stored Procedure guide (SG24-7083-00). The database calls to read and
> update the database work fine...however, I can't seem to figure out how to
> pass parms to the C Program. The compile, bind, and run using DB2BATCH
> all work fine, however, when I attempt to access any values passed into
> the program, they're not present.
>
> Here's part of the JCL I'm using to run the proc:
>
> //EMSDB2 EXEC PGM=DB2BATCH,DYNAMNBR=20
> //STEPLIB DD DSN=DBMT.TEST.DSNLOAD,DISP=SHR
> //SYSTSPRT DD SYSOUT=*
> //SYSPRINT DD SYSOUT=*
> //SYSUDUMP DD SYSOUT=*
> //SYSIN DD *
> //SYSTSIN DD *
> DSN SYSTEM(DBMS)
> RUN PROGRAM(PTMFMBID) PLAN(PTMFMBID) PARMS('TEST') -
> LIB('NGST.MCMILP2.DB2.LOADLIB' )
> END
> //*
>
> In my C program (PTMFMBID), I'm attempting to access argv[1] (which I
> think
> should be "TEST", based on the call above). This parm is simply not being
> passed in.
>
> My create proc statements look like this:
>
> CREATE PROCEDURE TMFDBC.PTMFMBID
> (
> MAILBOXID VARCHAR(8) IN
> )
> EXTERNAL NAME PTMFMBID LANGUAGE C
> PARAMETER STYLE GENERAL WITH NULLS
> ...

I don't know exactly how DB2 on z/OS handles this, but on DB2 for LUW you
have to
(1) create a function with some name of your choosing
(2) adhere to the protocol used by DB2 to pass the parameters to this
function

Step (2) is a bit more involved because it depends on the PARAMETER STYLE.
You are using GENERAL WITH NULLS and that means (on LUW) that each
parameter is passed separately via pointer and all the NULL indicators are
passed in a single array. You should really expect all those parameters,
otherwise you're stack is not properly aligned and you won't find the
correct variables and parameters. This could effectively lead to a stack
corruption.

Have a look at the PARAMETER STYLE and its influence on your platform.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Hi Knut,

Thanks for your assistance and reply!

I recreated the Stored Proc definition without the "WITH NULLS" keywords
(ie. "PARAMETER STYLE GENERAL") hoping so simplify debugging this problem.
Unfortunately, I still got the same results - the C code does not receive
the parm I'm trying to pass in.

How does the variable used on the stor proc define...

CREATE PROCEDURE TMFDBC.PTMFMBID
(
INOUT MAILBOXID VARCHAR(8) CCSID EBCDIC
)
EXTERNAL NAME PTMFMBID LANGUAGE C
PARAMETER STYLE GENERAL

....MAILBOXID, in this case, relate to the argv[] array in C?

I'm assuming that since I'm only specifying 1 parameter, I *should* be able
to reference it using argv[1]? (argv[0] being the program name by default).

This is making me crazy! I'm following the example in the Redbook, but I
can't get it to work!

Thanks again for your help with this.






"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:e281p9$j22$1@lc03.rz.uni-jena.de...
> Paul M wrote:
>
>> Hi All,
>>
>> I'm currently writing a z/OS DB2 Stored Proc in C, using an example from
>> the
>> IBM Stored Procedure guide (SG24-7083-00). The database calls to read
>> and
>> update the database work fine...however, I can't seem to figure out how
>> to
>> pass parms to the C Program. The compile, bind, and run using DB2BATCH
>> all work fine, however, when I attempt to access any values passed into
>> the program, they're not present.
>>
>> Here's part of the JCL I'm using to run the proc:
>>
>> //EMSDB2 EXEC PGM=DB2BATCH,DYNAMNBR=20
>> //STEPLIB DD DSN=DBMT.TEST.DSNLOAD,DISP=SHR
>> //SYSTSPRT DD SYSOUT=*
>> //SYSPRINT DD SYSOUT=*
>> //SYSUDUMP DD SYSOUT=*
>> //SYSIN DD *
>> //SYSTSIN DD *
>> DSN SYSTEM(DBMS)
>> RUN PROGRAM(PTMFMBID) PLAN(PTMFMBID) PARMS('TEST') -
>> LIB('NGST.MCMILP2.DB2.LOADLIB' )
>> END
>> //*
>>
>> In my C program (PTMFMBID), I'm attempting to access argv[1] (which I
>> think
>> should be "TEST", based on the call above). This parm is simply not
>> being
>> passed in.
>>
>> My create proc statements look like this:
>>
>> CREATE PROCEDURE TMFDBC.PTMFMBID
>> (
>> MAILBOXID VARCHAR(8) IN
>> )
>> EXTERNAL NAME PTMFMBID LANGUAGE C
>> PARAMETER STYLE GENERAL WITH NULLS
>> ...
>
> I don't know exactly how DB2 on z/OS handles this, but on DB2 for LUW you
> have to
> (1) create a function with some name of your choosing
> (2) adhere to the protocol used by DB2 to pass the parameters to this
> function
>
> Step (2) is a bit more involved because it depends on the PARAMETER STYLE.
> You are using GENERAL WITH NULLS and that means (on LUW) that each
> parameter is passed separately via pointer and all the NULL indicators are
> passed in a single array. You should really expect all those parameters,
> otherwise you're stack is not properly aligned and you won't find the
> correct variables and parameters. This could effectively lead to a stack
> corruption.
>
> Have a look at the PARAMETER STYLE and its influence on your platform.
>
> --
> Knut Stolze
> DB2 Information Integration Development
> IBM Germany

Paul M wrote:

> Hi Knut,
>
> Thanks for your assistance and reply!
>
> I recreated the Stored Proc definition without the "WITH NULLS" keywords
> (ie. "PARAMETER STYLE GENERAL") hoping so simplify debugging this problem.
> Unfortunately, I still got the same results - the C code does not receive
> the parm I'm trying to pass in.
>
> How does the variable used on the stor proc define...
>
> CREATE PROCEDURE TMFDBC.PTMFMBID
> (
> INOUT MAILBOXID VARCHAR(8) CCSID EBCDIC
> )
> EXTERNAL NAME PTMFMBID LANGUAGE C
> PARAMETER STYLE GENERAL
>
> ...MAILBOXID, in this case, relate to the argv[] array in C?
>
> I'm assuming that since I'm only specifying 1 parameter, I *should* be
> able
> to reference it using argv[1]? (argv[0] being the program name by
> default).
>
> This is making me crazy! I'm following the example in the Redbook, but I
> can't get it to work!

First, check how DB2 for z/OS passes the parameters to a procedure. You
have probably some samples shipped with DB2. I'll I'm saying now refers to
DB2 for LUW.

A procedure declared as above would require the following C code:

int function(char *mailBoxId)
{
// ...
}

Note that the argc/argv stuff is only applicable to a "main" function in
C/C++. In particular, DB2 sends the parameters not in an array but rather
as separate parameters (or rather separate pointers to the parameters).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

In article <w7L1g.6480$wK1.355422@news20.bellglobal.com>,
nospam@nospam.com says...
> Hi All,
>
> I'm currently writing a z/OS DB2 Stored Proc in C, using an example from the
> IBM Stored Procedure guide (SG24-7083-00). The database calls to read and
> update the database work fine...however, I can't seem to figure out how to
> pass parms to the C Program. The compile, bind, and run using DB2BATCH all
> work fine, however, when I attempt to access any values passed into the
> program, they're not present.
>
> Here's part of the JCL I'm using to run the proc:
>
> //EMSDB2 EXEC PGM=DB2BATCH,DYNAMNBR=20
> //STEPLIB DD DSN=DBMT.TEST.DSNLOAD,DISP=SHR
> //SYSTSPRT DD SYSOUT=*
> //SYSPRINT DD SYSOUT=*
> //SYSUDUMP DD SYSOUT=*
> //SYSIN DD *
> //SYSTSIN DD *
> DSN SYSTEM(DBMS)
> RUN PROGRAM(PTMFMBID) PLAN(PTMFMBID) PARMS('TEST') -
> LIB('NGST.MCMILP2.DB2.LOADLIB' )
> END
> //*
>
> In my C program (PTMFMBID), I'm attempting to access argv[1] (which I think
> should be "TEST", based on the call above). This parm is simply not being
> passed in.
>
> My create proc statements look like this:
>
> CREATE PROCEDURE TMFDBC.PTMFMBID
> (
> MAILBOXID VARCHAR(8) IN
> )
> EXTERNAL NAME PTMFMBID LANGUAGE C
> PARAMETER STYLE GENERAL WITH NULLS
> ...
>
> Have I missed something?
>

I downloaded these samples so I could understand what you're trying to
do. It looks like you missed one step. The samples don't show how to run
the stored procedure directly because the stored procedures are called
by the main C program (CAL*.SRC). You can run these programs with the
CAL*.JCL members. The EMP*.JCL contains the JCL to compile the stored
procedures (EMP*.SRC).

Hope this helps.

Kind regards, Gert

Thanks Knut!

I was able to get the Stored Proc working finally!

Thank you again for your assistance!



"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:e28cnp$nid$1@lc03.rz.uni-jena.de...
> Paul M wrote:
>
> > Hi Knut,
> >
> > Thanks for your assistance and reply!
> >
> > I recreated the Stored Proc definition without the "WITH NULLS" keywords
> > (ie. "PARAMETER STYLE GENERAL") hoping so simplify debugging this
problem.
> > Unfortunately, I still got the same results - the C code does not
receive
> > the parm I'm trying to pass in.
> >
> > How does the variable used on the stor proc define...
> >
> > CREATE PROCEDURE TMFDBC.PTMFMBID
> > (
> > INOUT MAILBOXID VARCHAR(8) CCSID EBCDIC
> > )
> > EXTERNAL NAME PTMFMBID LANGUAGE C
> > PARAMETER STYLE GENERAL
> >
> > ...MAILBOXID, in this case, relate to the argv[] array in C?
> >
> > I'm assuming that since I'm only specifying 1 parameter, I *should* be
> > able
> > to reference it using argv[1]? (argv[0] being the program name by
> > default).
> >
> > This is making me crazy! I'm following the example in the Redbook, but
I
> > can't get it to work!
>
> First, check how DB2 for z/OS passes the parameters to a procedure. You
> have probably some samples shipped with DB2. I'll I'm saying now refers
to
> DB2 for LUW.
>
> A procedure declared as above would require the following C code:
>
> int function(char *mailBoxId)
> {
> // ...
> }
>
> Note that the argc/argv stuff is only applicable to a "main" function in
> C/C++. In particular, DB2 sends the parameters not in an array but rather
> as separate parameters (or rather separate pointers to the parameters).
>
> --
> Knut Stolze
> DB2 Information Integration Development
> IBM Germany

Hi Gert,

Thank you for taking the time to help me with this. I took a closer look at
the examples and discovered where I had gone wrong.

The Stored Proc is now working and I'm back to being happy again!

Thanks again for your help!





"Gert van der Kooij" <nomail@nl.invalid> wrote in message
news:MPG.1eb1de42704ea7fc989724@news.xs4all.nl...
> In article <w7L1g.6480$wK1.355422@news20.bellglobal.com>,
> nospam@nospam.com says...
> > Hi All,
> >
> > I'm currently writing a z/OS DB2 Stored Proc in C, using an example from
the
> > IBM Stored Procedure guide (SG24-7083-00). The database calls to read
and
> > update the database work fine...however, I can't seem to figure out how
to
> > pass parms to the C Program. The compile, bind, and run using DB2BATCH
all
> > work fine, however, when I attempt to access any values passed into the
> > program, they're not present.
> >
> > Here's part of the JCL I'm using to run the proc:
> >
> > //EMSDB2 EXEC PGM=DB2BATCH,DYNAMNBR=20
> > //STEPLIB DD DSN=DBMT.TEST.DSNLOAD,DISP=SHR
> > //SYSTSPRT DD SYSOUT=*
> > //SYSPRINT DD SYSOUT=*
> > //SYSUDUMP DD SYSOUT=*
> > //SYSIN DD *
> > //SYSTSIN DD *
> > DSN SYSTEM(DBMS)
> > RUN PROGRAM(PTMFMBID) PLAN(PTMFMBID) PARMS('TEST') -
> > LIB('NGST.MCMILP2.DB2.LOADLIB' )
> > END
> > //*
> >
> > In my C program (PTMFMBID), I'm attempting to access argv[1] (which I
think
> > should be "TEST", based on the call above). This parm is simply not
being
> > passed in.
> >
> > My create proc statements look like this:
> >
> > CREATE PROCEDURE TMFDBC.PTMFMBID
> > (
> > MAILBOXID VARCHAR(8) IN
> > )
> > EXTERNAL NAME PTMFMBID LANGUAGE C
> > PARAMETER STYLE GENERAL WITH NULLS
> > ...
> >
> > Have I missed something?
> >
>
> I downloaded these samples so I could understand what you're trying to
> do. It looks like you missed one step. The samples don't show how to run
> the stored procedure directly because the stored procedures are called
> by the main C program (CAL*.SRC). You can run these programs with the
> CAL*.JCL members. The EMP*.JCL contains the JCL to compile the stored
> procedures (EMP*.SRC).
>
> Hope this helps.
>
> Kind regards, Gert