DB2/COBOL Store Procedure Parmeter List (Reply)

Dj

If you passed all of the fields as one group level when you rejester the SP
you would only be able to use one CHAR field. Now here is the rub. If you
are passing mix data, why not send it in the smallest network transaction
possible. Also why not have DB2 validate each parameter. This should be a
must so the SP knows that data type has already passed DB2 validation
requirements.

Last item. A group level can not be use in the linkage or on the procedure
division using statement. For more information, please send a request to
ne******@db2helpdesk.com

We have examples that we could send you, and we can also show you how to
stream line the SP for best performance across the network.

Mar 2 '06 #1

JLE

Dj wrote:

If you passed all of the fields as one group level when you rejester the SP
you would only be able to use one CHAR field. Now here is the rub. If you
are passing mix data, why not send it in the smallest network transaction
possible. Also why not have DB2 validate each parameter. This should be a
must so the SP knows that data type has already passed DB2 validation
requirements.

Thanks for your response, Dj.

The stored procedure is set up and working in a production environment.
The SP is set to receive five 01 level parameters. The caller passes
these five parameters using one group level item. I am concerned because
the system where these types of calls are located is having data
inconsistency problems. In addition, I have not seen any other examples
of this type of parameter passing. These calls, as far as I know, are
not generating SQL errors. However, I am concerned that passing
parameters this way may hide problems. For instance, would the 05 level
parameters from the caller match up internally with the 01 levels of the SP?

Thanks again, Dj.

John

Mar 2 '06 #2

Phil Sherman

The key to matching the 05s in the caller to the stored procedure's 01
levels lies in the invocation of the stored procedure:

Exec sql call called-procedure :ws-all5-parms

This is not a standard program to program call but is an SQL statement
in the calling program. The SQL preporcessor recognizes the parameter
construct (01 with 05s) and converts it to the multiple parameters being
passed to the called procedure. The LINKAGE SECTION and PROCEDURE
DIVISION USING code in the stored procedure handle mapping the
parameters to appropriate data areas for the stored procedure to use.

Phil Sherman
JLE wrote:

Dj wrote:

If you passed all of the fields as one group level when you rejester
the SP
you would only be able to use one CHAR field. Now here is the rub.
If you
are passing mix data, why not send it in the smallest network transaction
possible. Also why not have DB2 validate each parameter. This
should be a
must so the SP knows that data type has already passed DB2 validation
requirements.

Thanks for your response, Dj.

The stored procedure is set up and working in a production environment.
The SP is set to receive five 01 level parameters. The caller passes
these five parameters using one group level item. I am concerned because
the system where these types of calls are located is having data
inconsistency problems. In addition, I have not seen any other examples
of this type of parameter passing. These calls, as far as I know, are
not generating SQL errors. However, I am concerned that passing
parameters this way may hide problems. For instance, would the 05 level
parameters from the caller match up internally with the 01 levels of the
SP?

Thanks again, Dj.

John

Mar 3 '06 #3

JLE

Thanks, Phil.

It has been about seven years since I've coded DB2, and SPs are
completely new to me. I really appreciate the help.

Thanks everyone.

Phil Sherman wrote:

The key to matching the 05s in the caller to the stored procedure's 01
levels lies in the invocation of the stored procedure:

Exec sql call called-procedure :ws-all5-parms

This is not a standard program to program call but is an SQL statement
in the calling program. The SQL preporcessor recognizes the parameter
construct (01 with 05s) and converts it to the multiple parameters being
passed to the called procedure. The LINKAGE SECTION and PROCEDURE
DIVISION USING code in the stored procedure handle mapping the
parameters to appropriate data areas for the stored procedure to use.

Phil Sherman
JLE wrote:

Dj wrote:

If you passed all of the fields as one group level when you rejester
the SP
you would only be able to use one CHAR field. Now here is the rub.
If you
are passing mix data, why not send it in the smallest network
transaction
possible. Also why not have DB2 validate each parameter. This
should be a
must so the SP knows that data type has already passed DB2 validation
requirements.

Thanks for your response, Dj.

The stored procedure is set up and working in a production
environment. The SP is set to receive five 01 level parameters. The
caller passes these five parameters using one group level item. I am
concerned because the system where these types of calls are located is
having data inconsistency problems. In addition, I have not seen any
other examples of this type of parameter passing. These calls, as far
as I know, are not generating SQL errors. However, I am concerned that
passing parameters this way may hide problems. For instance, would the
05 level parameters from the caller match up internally with the 01
levels of the SP?

Thanks again, Dj.

John

Mar 3 '06 #4