Passing parms to a C Stored Proc

[Paul M]

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?

[Knut Stolze]

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]

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" <st****@de.ibm.com> wrote in message
news:e2**********@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

[Knut Stolze]

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

[Gert van der Kooij]

In article <w7*******************@news20.bellglobal.com>,
no****@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

[Paul M]

Thanks Knut!

I was able to get the Stored Proc working finally!

Thank you again for your assistance!

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:e2**********@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

[Paul M]

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" <no****@nl.invalid> wrote in message
news:MP************************@news.xs4all.nl...

In article <w7*******************@news20.bellglobal.com>,
no****@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