Re: COBOL stored procedure for DB2

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 ftp://ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter/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 environment -- 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...@xxxxxxxxxxxxxxxxxxxxx>
wrote:

Hi.

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

If you have the Application Development Client installed -- which I'm
guessing
you have ! -- you should find these demos under the samples/cobol_mf
directory.
The makefile will include appropriate build instructions for the
stored procedure,
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.- Hide quoted text -

- 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



.

Relevant Pages

  • Re: COBOL stored procedure for DB2
    ... The makefile will include appropriate build instructions for the stored procedure, ... exec sql begin declare section end-exec. ... Dim cmCallSP As Command ...
    (comp.lang.cobol)
  • Re: COBOL stored procedure for DB2
    ... for building and executing COBOL SP's using Object COBOL 4.1, ... Before building a stored procedure on AIX 4.2.1 using the Micro Focus 4.1 ... confirm the cob command used for creating the SP module. ... exec sql begin declare section end-exec. ...
    (comp.lang.cobol)
  • Re: COBOL stored procedure for DB2
    ... Before building a stored procedure on AIX 4.2.1 using the Micro Focus ... confirm the cob command used for creating the SP module. ... exec sql begin declare section end-exec. ...
    (comp.lang.cobol)
  • Re: COBOL stored procedure for DB2
    ... I would look at using the inpcli 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. ... exec sql begin declare section end-exec. ...
    (comp.lang.cobol)
  • Re: New "base document" available
    ... COBOL, ... That is not normally how command lines are constructed. ... unless Keyword parameters are being employed. ... 15 clp-name usage pointer. ...
    (comp.lang.cobol)