Re: COBOL stored procedure for DB2

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' depending on the SQL statements actually being executed within your SP.

SimonT.

On Apr 12, 9:05 pm, Simon Tobias <Simon.Tob...@xxxxxxxxxxxxxxxxxxxxx>
wrote:

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
offtp://ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter/db2a
xe...
, 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- Hide quoted text -

- 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



.

Relevant Pages

  • Re: COBOL stored procedure for DB2
    ... Have you also confirmed that, prior to executing the CALL statement, the ... The DB2 CREATE PROCEDURE statement. ... MODIFIES SQL DATA ... confirm the cob command used for creating the SP module. ...
    (comp.lang.cobol)
  • Re: How to Log errors while executing ingres sql command
    ... The sql UNIX command sends E_ errors to channel 1. ... While executing ingres 'sql' command, all the errors that raises gets ... I am executing this comand through shell script. ...
    (comp.databases.ingres)
  • BizTalk 2006 Host Adapters for IBM DB2
    ... and get data into Biztalk using the following SQL command on the DB2 ... My problem is when I attempt to use the "Update Command" portion of the DB2 ... On the SQL connection string I use my AS400 login which has full security ...
    (microsoft.public.biztalk.general)
  • Re: Writing update back to MSAccess table?
    ... (with the exception of maybe a using statement on the command, ... value of 'remainreward' successfully, executing this SQL ... The SQL being executed to update this row when the application closes ...
    (microsoft.public.dotnet.languages.csharp)
  • EXEC(sqlstatement) Resultset usage
    ... How do I, from within the same stored procedure, use the result of a sql ... statement after executing it using the EXECcommand? ...
    (microsoft.public.sqlserver.programming)