COBOL stored procedure for DB2

I am trying to learn how to use stored procedures written in COBOL so
I wrote 2 small programs to test it out: the stored procedure and the
the calling program. I have no problems compiling them but when the
calling program enters the SP, it either hangs or gives me sqlcode
-1131.

We are on AIX 5.2 (I think) running DB2 UDB ver 7.2 and MF COBOL 4.1.


Below are the programs I wrote:


Calling program (spcaller.sqb)
identification division.
program-id.    spcaller.
environment division.
input-output section.
file-control.


working-storage section.
exec sql  begin declare section  end-exec.


01  work-area.
05  lnk-stored-proc         pic x(12).
05  lnk-policy              pic x(10).
05  lnk-plan                pic x(3).


exec sql  end declare section  end-exec.


exec sql  include sqlca  end-exec.
exec sql  include sqlda  end-exec.


procedure division.
00000-main-rtn.


exec sql  connect to noadmidb user dbmssi using dbmssi
end-exec
exec sql  set schema dbipas  end-exec
initialize work-area
accept lnk-policy from command-line
move "spprog" to lnk-stored-proc
display "calling spprog"
exec sql
call :lnk-stored-proc
(:lnk-policy, :lnk-plan)
end-exec
if sqlcode not= 0
display 'error in sp = ' sqlcode
else
display "policy number :  " lnk-policy
display "plancode      :  " lnk-plan
end-if
exec sql  connect reset  end-exec
stop run.


The database name si "noadmidb" and the schema is "dbipas" but the
account running the program is "dbmssi". Our DBA says this account
has
all privileges of the instance except for commands that alter the
database/tables such as DROP.


SP program: (spprog.cbl)
identification division.
program-id.    spprog.
environment division.
input-output section.
file-control.


data division.
working-storage section.


linkage section.
01  lnk-policy                   pic x(10).
01  lnk-plan                     pic x(3).


procedure division using lnk-policy lnk-plan.
00000-main-rtn.


move "XXX" to lnk-plan
goback.


This used to be a little longer and had embedded SQL commands in it
but I removed them to try and figure out where the problem was.


I compiled the sp program using the commands:
cob -c -x spprog.cbl
cob -x -o spprog spprog.o -Q -bnoentry -Q -bE:noadmi.exp -Q -bI:
$DB2PATH/lib/db2g.imp -L$DB2PATH/lib -ldb2 -ldb2gmf


...and copied the executable file spprog to the $INSTHOME/sqllib/
function directory. I then prepped/bound/compiled the calling program
using the commands:
db2 prep spcaller.sqb bindfile target ibmcob
db2 bind spcaller.bnd
cob -c -x spcaller.cbl
cob -x -o spcaller spcaller.o -ldb2 -ldb2gmf -L$DB2PATH/lib


I also tried using "target mfcob" for DB2 prep but with the same
results.


I then created the procedure with the CREATE PROCEDURE command as
follows:
db2 "create procedure dbipas.spprog (in policy char(10), out plan
char(3)) dynamic result sets 0 no sql language cobol external name
'spprog' parameter style general program type sub"


My co-worker also wrote a VB program to run the SP but it also hangs
when it tries to call it:
Option Explicit


Private Sub cmdOK_Click()
txtPlan.Text = ExecSP(txtPolNo.Text)
End Sub


Private Function ExecSP(ByVal sPolno As String) As String
Dim cmCallSP As Command
Dim parmSP As Parameter
Dim sSQLState As String
Dim g_sConnection As String


g_sConnection = "DSN=NONADMI;UID=dbmssi;PWD=dbmssi"
Screen.MousePointer = vbHourglass
Set cmCallSP = New Command
cmCallSP.CommandText = "spprog"
cmCallSP.CommandType = adCmdStoredProc
cmCallSP.ActiveConnection = g_sConnection


Set parmSP = cmCallSP.CreateParameter("policy", adChar,
adParamInput, 10, sPolno)
Call cmCallSP.Parameters.Append(parmSP)
Set parmSP = cmCallSP.CreateParameter("plan", adChar,
adParamOutput, 3)
Call cmCallSP.Parameters.Append(parmSP)


cmCallSP.Execute


ExecSP = cmCallSP.Parameters("plan").Value
End Function


Can anyone tell me what I'm doing wrong? We have tons of COBOL
programs that I would like to use as stored procs so they can be
called by VB6 programs running on Windows XP or 2000.


Thanks in advance for your help.

Hi.

IBM do provide COBOL samples, which include demos of how to build/deploy/exe
cute
stored procedures.

If you have the Application Development Client installed -- which I'm guessi
ng
you have ! -- you should find these demos under the samples/cobol_mf directo
ry.
The makefile will include appropriate build instructions for the stored proc
edure,
as well as the client app. I would look at using the inpcli (client) and
inpsrv (Stored Procedure) demo to make sure things are working ok for you,
especially with how the SP itself is linked.

Unfortunately I don't have a UDB 7.2 installation on AIX to hand to confirm
the cob command used for creating the SP module.

SimonT.

> I am trying to learn how to use stored procedures written in COBOL so
> I wrote 2 small programs to test it out: the stored procedure and the
> the calling program. I have no problems compiling them but when the
> calling program enters the SP, it either hangs or gives me sqlcode
> -1131.
>
> We are on AIX 5.2 (I think) running DB2 UDB ver 7.2 and MF COBOL 4.1.
>
> Below are the programs I wrote:
>
> Calling program (spcaller.sqb)
> identification division.
> program-id.    spcaller.
> environment division.
> input-output section.
> file-control.
> working-storage section.
> exec sql  begin declare section  end-exec.
> 01  work-area.
> 05  lnk-stored-proc         pic x(12).
> 05  lnk-policy              pic x(10).
> 05  lnk-plan                pic x(3).
> exec sql  end declare section  end-exec.
>
> exec sql  include sqlca  end-exec.
> exec sql  include sqlda  end-exec.
> procedure division.
> 00000-main-rtn.
> exec sql  connect to noadmidb user dbmssi using dbmssi
> end-exec
> exec sql  set schema dbipas  end-exec
> initialize work-area
> accept lnk-policy from command-line
> move "spprog" to lnk-stored-proc
> display "calling spprog"
> exec sql
> call :lnk-stored-proc
> (:lnk-policy, :lnk-plan)
> end-exec
> if sqlcode not= 0
> display 'error in sp = ' sqlcode
> else
> display "policy number :  " lnk-policy
> display "plancode      :  " lnk-plan
> end-if
> exec sql  connect reset  end-exec
> stop run.
> The database name si "noadmidb" and the schema is "dbipas" but the
> account running the program is "dbmssi". Our DBA says this account
> has
> all privileges of the instance except for commands that alter the
> database/tables such as DROP.
> SP program: (spprog.cbl)
> identification division.
> program-id.    spprog.
> environment division.
> input-output section.
> file-control.
> data division.
> working-storage section.
> linkage section.
> 01  lnk-policy                   pic x(10).
> 01  lnk-plan                     pic x(3).
> procedure division using lnk-policy lnk-plan.
> 00000-main-rtn.
> move "XXX" to lnk-plan
> goback.
> This used to be a little longer and had embedded SQL commands in it
> but I removed them to try and figure out where the problem was.
>
> I compiled the sp program using the commands:
> cob -c -x spprog.cbl
> cob -x -o spprog spprog.o -Q -bnoentry -Q -bE:noadmi.exp -Q -bI:
> $DB2PATH/lib/db2g.imp -L$DB2PATH/lib -ldb2 -ldb2gmf
> ...and copied the executable file spprog to the $INSTHOME/sqllib/
> function directory. I then prepped/bound/compiled the calling program
> using the commands:
> DB2 prep spcaller.sqb bindfile target ibmcob
> DB2 bind spcaller.bnd
> cob -c -x spcaller.cbl
> cob -x -o spcaller spcaller.o -ldb2 -ldb2gmf -L$DB2PATH/lib
> I also tried using "target mfcob" for DB2 prep but with the same
> results.
>
> I then created the procedure with the CREATE PROCEDURE command as
> follows:
> DB2 "create procedure dbipas.spprog (in policy char(10), out plan
> char(3)) dynamic result sets 0 no sql language cobol external name
> 'spprog' parameter style general program type sub"
> My co-worker also wrote a VB program to run the SP but it also hangs
> when it tries to call it:
> Option Explicit
> Private Sub cmdOK_Click()
> txtPlan.Text = ExecSP(txtPolNo.Text)
> End Sub
> Private Function ExecSP(ByVal sPolno As String) As String
> Dim cmCallSP As Command
> Dim parmSP As Parameter
> Dim sSQLState As String
> Dim g_sConnection As String
> g_sConnection = "DSN=NONADMI;UID=dbmssi;PWD=dbmssi"
> Screen.MousePointer = vbHourglass
> Set cmCallSP = New Command
> cmCallSP.CommandText = "spprog"
> cmCallSP.CommandType = adCmdStoredProc
> cmCallSP.ActiveConnection = g_sConnection
> Set parmSP = cmCallSP.CreateParameter("policy", adChar,
> adParamInput, 10, sPolno)
> Call cmCallSP.Parameters.Append(parmSP)
> Set parmSP = cmCallSP.CreateParameter("plan", adChar,
> adParamOutput, 3)
> Call cmCallSP.Parameters.Append(parmSP)
> cmCallSP.Execute
>
> ExecSP = cmCallSP.Parameters("plan").Value
> End Function
> Can anyone tell me what I'm doing wrong? We have tons of COBOL
> programs that I would like to use as stored procs so they can be
> called by VB6 programs running on Windows XP or 2000.
>
> Thanks in advance for your help.
>

On Apr 11, 11:22 pm, Simon Tobias <Simon.Tob...@nospam.microfocus.com>
wrote:
> Hi.
>
> IBM do provide COBOL samples, which include demos of how to build/deploy/e
xecute
> stored procedures.
>
> If you have the Application Development Client installed -- which I'm gues
sing
> you have ! -- you should find these demos under the samples/cobol_mf direc
tory.
> The makefile will include appropriate build instructions for the stored pr
ocedure,
> as well as the client app. I would look at using the inpcli (client) and
> inpsrv (Stored Procedure) demo to make sure things are working ok for you,
> especially with how the SP itself is linked.
>
> Unfortunately I don't have a UDB 7.2 installation on AIX to hand to confir
m
> the cob command used for creating the SP module.
>
> SimonT.
>
>
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> - Show quoted text -

Hi, Simon. Thanks for taking the time to reply to my problem.

I actually got the DB2 prep/bind and cob commands and compiler options
from the bldapp and bldsrv files in the samples directory but I didn't
know about the make file so I tried following the steps there as you
suggested. It's pretty much the same as what I did before except for
the wrapsrv command. I did the wrapsrv command and changed all
references to my sp from "spprog" to "spprog!spprog_wrap" including
the EXTERNAL NAME in my CREATE PROCEDURE command but still got the
SQLCODE -1131. Am I supposed to invoke the CREATE WRAPPER command for
this? Do I need to include the checkerr program when I link it?

Thanks again!

Pompeyo C

Hello pompeyoc,

I've gone back to the IBM documentation, to remind myself of the requirement
s
for building and executing COBOL SP's using Object COBOL 4.1, rather than
Server Express (which I'm more familiar with).

If you refer to chapter 6 of ftp://ftp.software.ibm.com/ps/produ...er/db2axe71.pdf
, it covers a few steps required to set up the DB2 environment. Specifically
:

"1. Before building a stored procedure on AIX 4.2.1 using the Micro Focus 4.
1
compiler, execute the following commands:
db2stop
db2set DB2LIBPATH=$LIBPATH
db2set DB2ENVLIST="COBDIR LIBPATH"
db2set
db2start
Ensure that db2stop stops the database and LIBPATH is set properly in your
shell environment. The last db2set command is issued to display your
settings: make sure DB2LIBPATH and DB2ENVLIST are set correctly."

I suspect that this the cause of your SQL1131N error. The stored procedure
is loaded under the control of DB2 -- as opposed to the client-side environm
ent
-- hence DB2 will need to inherit the COBOL environment for the purposes
of executing the stored procedure.

Regards,
SimonT.

> On Apr 11, 11:22 pm, Simon Tobias <Simon.Tob...@nospam.microfocus.com>
> wrote:
> 
> Hi, Simon. Thanks for taking the time to reply to my problem.
>
> I actually got the DB2 prep/bind and cob commands and compiler options
> from the bldapp and bldsrv files in the samples directory but I didn't
> know about the make file so I tried following the steps there as you
> suggested. It's pretty much the same as what I did before except for
> the wrapsrv command. I did the wrapsrv command and changed all
> references to my sp from "spprog" to "spprog!spprog_wrap" including
> the EXTERNAL NAME in my CREATE PROCEDURE command but still got the
> SQLCODE -1131. Am I supposed to invoke the CREATE WRAPPER command for
> this? Do I need to include the checkerr program when I link it?
>
> Thanks again!
>
> Pompeyo C
>

On Apr 12, 9:05 pm, Simon Tobias <Simon.Tob...@nospam.microfocus.com>
wrote:
> Hello pompeyoc,
>
> I've gone back to the IBM documentation, to remind myself of the requireme
nts
> for building and executing COBOL SP's using Object COBOL 4.1, rather than
> Server Express (which I'm more familiar with).
>
> If you refer to chapter 6 offtp://ftp.software.ibm.com/ps/products/db2/inf
o/vr7/pdf/letter/db2axe...
> , it covers a few steps required to set up the DB2 environment. Specifical
ly
> :
>
> "1. Before building a stored procedure on AIX 4.2.1 using the Micro Focus 
4.1
> compiler, execute the following commands:
> db2stop
> db2set DB2LIBPATH=$LIBPATH
> db2set DB2ENVLIST="COBDIR LIBPATH"
> db2set
> db2start
> Ensure that db2stop stops the database and LIBPATH is set properly in your
> shell environment. The last db2set command is issued to display your
> settings: make sure DB2LIBPATH and DB2ENVLIST are set correctly."
>
> I suspect that this the cause of your SQL1131N error. The stored procedure
> is loaded under the control of DB2 -- as opposed to the client-side enviro
nment
> -- hence DB2 will need to inherit the COBOL environment for the purposes
> of executing the stored procedure.
>
> Regards,
> SimonT.
>
>
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> - Show quoted text -

You are a genius! I followed your instructions and my stored procedure
is now running. I added sql commands to insert rows into a dummy table
and it worked fine. My only problem is the SP is not getting the IN
variable passed by the calling program and the calling program is not
getting the value of the OUT variable set by the SP. Can you help me
again?

Thanks!

Pompeyo C

Hi Simon,

Thanks very much for these posts. I learned a few things...:-)

Great job!

Looks from your address like you are still with MF. They are lucky to have
you !

Pete.

TOP POST - nothing new below....


"Simon Tobias" <Simon.Tobias@nospam.microfocus.com> wrote in message
news:dab96b19accf8c94b22b3ac64ba@news.altohiway.com...
> Hello pompeyoc,
>
> I've gone back to the IBM documentation, to remind myself of the
> requirements for building and executing COBOL SP's using Object COBOL 4.1,
> rather than Server Express (which I'm more familiar with).
>
> If you refer to chapter 6 of
> [url]ftp://ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter/db2axe71.pdf[/url
] ,
> it covers a few steps required to set up the DB2 environment. Specifically
> :
>
> "1. Before building a stored procedure on AIX 4.2.1 using the Micro Focus
> 4.1
> compiler, execute the following commands:
> db2stop
> db2set DB2LIBPATH=$LIBPATH
> db2set DB2ENVLIST="COBDIR LIBPATH"
> db2set
> db2start
> Ensure that db2stop stops the database and LIBPATH is set properly in your
> shell environment. The last db2set command is issued to display your
> settings: make sure DB2LIBPATH and DB2ENVLIST are set correctly."
>
> I suspect that this the cause of your SQL1131N error. The stored procedure
> is loaded under the control of DB2 -- as opposed to the client-side
> environment -- hence DB2 will need to inherit the COBOL environment for
> the purposes of executing the stored procedure.
>
> Regards,
> SimonT.
> 
>
>

Hello pompeyoc,

Great, I'm glad the SP is at least being executed now!

Regarding how you precompile the app, you must specify target mfcob to db2
prep, rather than target ibmcob.

Have you also confirmed that, prior to executing the CALL statement, the
host variables specified within the client app have the appropriate values?

Assuming that the app is not picking up the parameters correctly, could you
please repost the snippets showing :

1. Client code -- the CALL statement being used.
2. SP code -- the LINKAGE SECTION content and PROCEDURE DIVISION USING ...
statement.
3. The DB2 CREATE PROCEDURE statement.

Regarding (3), for the sample shipping with DB2 itself, they use :

DYNAMIC RESULT SETS 0
LANGUAGE COBOL
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
NOT THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB

I noticed that you had 'no sql' in your earlier email:

> DB2 "create procedure dbipas.spprog (in policy char(10), out plan
> char(3)) dynamic result sets 0 no sql language cobol external name
> 'spprog' parameter style general program type sub"

You should be specifying either 'READS SQL DATA' or 'MODIFIES SQL DATA' depe
nding
on the SQL statements actually being executed within your SP.

SimonT.

> On Apr 12, 9:05 pm, Simon Tobias <Simon.Tob...@nospam.microfocus.com>
> wrote:
> 
> You are a genius! I followed your instructions and my stored procedure
> is now running. I added sql commands to insert rows into a dummy table
> and it worked fine. My only problem is the SP is not getting the IN
> variable passed by the calling program and the calling program is not
> getting the value of the OUT variable set by the SP. Can you help me
> again?
>
> Thanks!
>
> Pompeyo C
>

On Apr 13, 9:12 pm, Simon Tobias <Simon.Tob...@nospam.microfocus.com>
wrote:
> Hello pompeyoc,
>
> Great, I'm glad the SP is at least being executed now!
>
> Regarding how you precompile the app, you must specify target mfcob to db2
> prep, rather than target ibmcob.
>
> Have you also confirmed that, prior to executing the CALL statement, the
> host variables specified within the client app have the appropriate values
?
>
> Assuming that the app is not picking up the parameters correctly, could yo
u
> please repost the snippets showing :
>
> 1. Client code -- the CALL statement being used.
> 2. SP code -- the LINKAGE SECTION content and PROCEDURE DIVISION USING ...
> statement.
> 3. The DB2 CREATE PROCEDURE statement.
>
> Regarding (3), for the sample shipping with DB2 itself, they use :
>
>   DYNAMIC RESULT SETS 0
>   LANGUAGE COBOL
>   PARAMETER STYLE GENERAL
>   NO DBINFO
>   FENCED
>   NOT THREADSAFE
>   MODIFIES SQL DATA
>   PROGRAM TYPE SUB
>
> I noticed that you had 'no sql' in your earlier email:
> 
>
> You should be specifying either 'READS SQL DATA' or 'MODIFIES SQL DATA' de
pending
> on the SQL statements actually being executed within your SP.
>
> SimonT.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 

Simon,

The CREATE PROCEDURE statement in my first post had NO SQL because I
originally tried a simple SP with NO SQL just to test if it can
actually be called (this is the first time I tried using external
SPs). When it finally did (after following your advice), I added a
simple SQL INSERT statement and modified the CREATE PROCEDURE to
MODIFIES SQL DATA. I am also using "target mfcob" now instead of
"target ibmcob" on the DB2 prep.

I am at home right now so I don't have access to the programs. I will
post the snippets on Monday.

Thanks!

Pompeyo C

On Apr 13, 9:12 pm, Simon Tobias <Simon.Tob...@nospam.microfocus.com>
wrote:
> Hello pompeyoc,
>
> Great, I'm glad the SP is at least being executed now!
>
> Regarding how you precompile the app, you must specify target mfcob to db2
> prep, rather than target ibmcob.
>
> Have you also confirmed that, prior to executing the CALL statement, the
> host variables specified within the client app have the appropriate values
?
>
> Assuming that the app is not picking up the parameters correctly, could yo
u
> please repost the snippets showing :
>
> 1. Client code -- the CALL statement being used.
> 2. SP code -- the LINKAGE SECTION content and PROCEDURE DIVISION USING ...
> statement.
> 3. The DB2 CREATE PROCEDURE statement.
>
> Regarding (3), for the sample shipping with DB2 itself, they use :
>
>   DYNAMIC RESULT SETS 0
>   LANGUAGE COBOL
>   PARAMETER STYLE GENERAL
>   NO DBINFO
>   FENCED
>   NOT THREADSAFE
>   MODIFIES SQL DATA
>   PROGRAM TYPE SUB
>
> I noticed that you had 'no sql' in your earlier email:
> 
>
> You should be specifying either 'READS SQL DATA' or 'MODIFIES SQL DATA' de
pending
> on the SQL statements actually being executed within your SP.
>
> SimonT.
>
>
>
>
> - Show quoted text -

Hi Simon. I posted the snippets yesterday but it seems it didn't take.
Anyway, here they are again.

1. Client Code CALL statement
accept lnk-policy from command-line
move "spprog!spprog_wrap" to lnk-stored-proc
exec sql
call :lnk-stored-proc
(:lnk-policy, :lnk-plan)
end-exec

lnk-policy is the parameter provided when the program is executed, so
when I run the program via:
./spcaller 0000012345 (spcaller is the name of the client program)
I expect lnk-policy to contain "0000012345". I ran it using animator
and it did.

I also tried using indicators (I got the idea from the sample program
inpcli.sqb):
exec sql
call :lnk-stored-proc
(:lnk-policy:ind-policy,
:lnk-plan:ind-plan)
end-exec

... but it didn't help

2. SP Code
linkage section.
01  lnk-policy                   pic x(10).
01  lnk-plan                     pic x(3).

procedure division using lnk-policy lnk-plan.
00000-main-rtn.

move function current-date to sql-timestamp
string lnk-policy "entered spprog: " sql-timestamp
delimited by size into sql-message
exec sql  insert into dbipas.spdebug values (:sql-message)
end-exec
(this inserts the concatenated message into the spdebug table and
tells me that the call to the sp was successful. it also tells me that
the value of lnk-policy is not properly received because when I
exported the contents of the table and ran it thru a hexdump program,
the first 10 characters were (in HEX) "H'00C22453514C2443414C'" or (in
ASCII) "C'..$SQL$CAL'". This value was actually defined within the
copybook sqlenv.cbl. I added it because it was in the sampel client
program)
if lnk-policy = spaces
move "NON" to lnk-plan
else
move "XXX" to lnk-plan
end-if
(this sets the value of lnk-plan)
move sqlz-hold-proc to return-code
move function current-date to sql-timestamp
string "exited spprog: " sql-timestamp " " lnk-plan
delimited by size into sql-message
exec sql  insert into dbipas.spdebug values (:sql-message)
end-exec
(this inserts another row into the spdebug table and tells me that lnk-
plan has the correct value before returning to the client program)
goback.

The inpcli.sqb sample program provides another method of passing
parameters via SQLDA but I really didn't understand how to use it so I
tried to stick with using host variables.

3. The CREATE PROCEDURE statement
create procedure dbipas.spprog (in policy char(10), out plan char(3))
dynamic result sets 0
modifies sql data
language cobol
external name 'spprog!spprog_wrap'
parameter style general
program type sub

I didn't include NO DBINFO, FENCED and NOT THREADSAFE because I
believe these are the defaults anyway. I also tried changing OUT PLAN
to INOUT PLAN but it didn't help either.

Thanks again!

Pompeyo C