Re: COBOL stored procedure for DB2

On Apr 13, 9:12 pm, Simon Tobias <Simon.Tob...@xxxxxxxxxxxxxxxxxxxxx>
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 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.




- 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

.

Relevant Pages

  • Re: Connection to SQL on WMI Executed Process
    ... This seems very related to the SQL Server configuration, ... WMI Test Engineer ... custom Client/Server application that remotely triggers a small client ... been remotely executing the process using sysinternal's psexec. ...
    (microsoft.public.win32.programmer.wmi)
  • Re: Dont understand what version of SQL to install
    ... I'm talking about developing and testing databases for my client and ... To my XP, I can add SQL Developer 2008 to develop (I guess I should say, ... So I can use SQL Developer 2008 to create a database in standard 2005, ... installed on Client OSs such as Windows Vista. ...
    (microsoft.public.sqlserver.setup)
  • Re: Millions of Delete Statements
    ... You mention a cursor. ... Others have mentioned batching the commands, ... so I will throw out one that is less good (executing them ... get the whole SQL statement I don't know of another way to do it). ...
    (comp.databases.ms-sqlserver)
  • Re: DataSet.GetChanges() in RowChanged(DataRowAction.Add)
    ... have you considered SQL Express and use ... > I realize now that I didn't describe well how the client application is ... > Framework installed on the client machine, but not any SQL Server). ... > 20 tables in different relations with eachother in the database, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Oracle 10g2 LIKE operator and case-insensitive issues
    ... SQL> CREATE INDEX IDXTESTCASE ON AUTENTI; ... Predicate Information: ... 338 bytes received via SQL*Net from client ... sorts (memory) ...
    (comp.databases.oracle.server)