How to create a cobol subroutine(sub PGM) as DB2 interface

[kick2006]

Hi guys:

I am a mainframe cobol programmer, and now i need some helps.

I want to write a cobol subroutine it can provide functions for accessing DB2.

ex. there is a subroutine called DBI0000.
In cobol program, I can access DB2 use the DBI0000 as below.

CALL DBI0000 USING PARM-OPTION
PARM-KEY
PARM-TABLE
PARM-DATA-AREA.

In my mind,the PARM-OPTION will be SELECT,OPEN,FETCH,CLOSE etc.
the PARM-KEY is the access key
the PARM-TABLE is the db2 table that I read
and the PARM-DATA-AREA is the result.

I can do the SELECT function by myself,
but I have no ideas for writing the CURSOR function.

Could someone can help me or give me some tips(samples).

Thanks for your help in advance.

Regards.

 

[Truusvlugindewind]

Questions:
What is you goal? Dynamic- or static SQL?
What is going to be you ratio service-programs vs db2 tables?
Why?

 

[Glenn9999]

The way I read this, the OP would almost have to do dynamic SQL and some pretty off-the-wall (compared to normal) code, if the intention is truly as generic as the description seems.

My questions would be:
1) How many tables does this address?
2) What is the OP looking to do by such a routine?

Of course, key to me is the question of how sensible such a routine would be. "FETCH" is listed as a parm. It's been a while since I played with any kind of DB/2, but as I recall, opening a cursor generates a result set and fetch returns a row out of the result set. I don't know how browsable the newer DB/2s are, or whether you can keep a result set around after a single execution of a program, but I'm thinking that a sub-program like this would be a big drain on resources, unless perhaps you dropped the result set to a temp file, and then just read the temp file on subsequent "fetch" calls.

Of course, I wonder if the OP is wanting it to be this generic if just calling DSNTEP2 would be better in the long run.

 

[kick2006]

To all,

thanks for your response.
Now I am doing a migration project from XXX to DB2.
the request is all of the COBOL programs will access DB2 via DBI program(subroutine) not use a direct SQL.

I think it needs Dynamic SQL in the DBI.
for easy to do it,
1) The DBI will SELECT all records from the table using SELECT * FROM ..., and the PARM-KEY as the WHERE condition.
2) Each table has its own DBI.
the DBI of TBL001 will be DBI0001, TBL002 will be DBI0002 etc.

The trouble is how to do the FETCH function.
I dont know how to keep the result set.
Whether there are other ways to provide the 'FETCH' function instead of the SQL FETCH statement.

BTW: sorry for my english

 

[Glenn9999]

the request is all of the COBOL programs will access DB2 via DBI program(subroutine) not use a direct SQL.

Fine, this is just saying that they want the DB2 in a sub-program, if possible. I'm not sure they just want ONE program though.

I think it needs Dynamic SQL in the DBI.

Sounds like a nightmare waiting to happen.

2) Each table has its own DBI.

Which would be a maintenance nightmare, given what you're wanting to do.

The trouble is how to do the FETCH function.
I dont know how to keep the result set.

I don't think you can keep the result set in that manner with a sub-program that ends execution after each FETCH.

It'll pay to seriously rethink how you are approaching this. I know I wouldn't want the headache of trying to either implement this plan or maintain this plan. And if I were in a position of code review, I would flunk this idea of a universal sub-routine. Just not feasible.

 

[Truusvlugindewind]

Listen to Glenn with the nine's, he/she speaks wise words.

But, if you ensist on NOT using direct SQL I'd concider a more service-orietnted approach. Implement you database serives as SOA's and use MQ and XML as transport/interface mechanismns. Other application can benefit from that as well.

Do not come back here to complain about performance though.

 

[fredericofonseca]

You are wrong in thinking you need to use dynamic SQL for this type of conversion.


I work on a company that has gone through such a conversion a few years ago. They were using COBOL with standard files/reads/writes, and all such parts were replaced with a common call to a single program "DBCALL" using a set of parameters, of which one was the type of operation "read next/read prior/write/rewrite/delete", and another was was the dataset(db2 table) to be used on the operation.

Without all the details, the linkage of this program is
Operation PIC X(20).
ERROR-CODE PIC X(10)
DATASET PIC X(20)
DATASET-DATA
And a few more fields. The above are the most important ones.

DBCALL will determine which sub-program to call based on the value on DATASET, and will supply the same parameters to that called program.
SET ADDRESS OF is extensively used to allow for variable sized parameters, namely on the DATASET-DATA field, as this will vary according to the table being accessed.


The I/O Modules (your DBI modules) perform all required operations using static SQL. Performance is poor though, and if you use Dynamic SQL it will be even worst.

As a quick example of what you can do.

Working storage

EXEC SQL
DECLARE MYTBL-FORWARD CURSOR FOR
SELECT
FIELD1
,FIELD2
,FIELD3
,FIELD4
,FIELD5
,FIELD6
FROM MY_TABLE
WHERE
FIELD1 = :HV-FIELD1-VALUE
AND FIELD2 = :HV-FIELD2-VALUE
AND FIELD3 > :HV-FIELD3-VALUE
END-EXEC.

EXEC SQL
DECLARE MYTBL-REVERSE CURSOR FOR
SELECT
FIELD1
,FIELD2
,FIELD3
,FIELD4
,FIELD5
,FIELD6
FROM MY_TABLE
WHERE
FIELD1 = :HV-FIELD1-VALUE
AND FIELD2 = :HV-FIELD2-VALUE
AND FIELD3 < :HV-FIELD3-VALUE
END-EXEC.




Procedure

EVALUATE Operation
WHEN “READ-NEXT”
PERFORM READ-NEXT-REC
WHEN “UPDATE”
PERFORM UPDATE-REC
WHEN “READ-PRIOR”
PERFORM READ-PRIOR-REC
WHEN “DELETE”
PERFORM DELETE-REC
WHEN “ADD”
PERFORM ADD-REC
END-EVALUATE


READ-NEXT-REC
IF MYTBL-FORWARD-OPEN = “N”
MOVE LINKAGE-KEY-FIELD1 TO HV-FIELD1-VALUE
MOVE LINKAGE-KEY-FIELD2 TO HV-FIELD2-VALUE
MOVE LINKAGE-KEY-FIELD3 TO HV-FIELD3-VALUE
EXEC SQL
OPEN MYTBL-FORWARD
END-EXEC
IF SQLCODE > 0
PERFORM ERROR-HANDLING
END-IF
MOVE “Y” TO MYTBL-FORWARD-OPEN
ELSE
IF LINKAGE-REC-LAST-READ NOT = REC-LAST-READ (this means calling program changed key to read
MOVE “N” TO MYTBL-FORWARD-OPEN
GO TO READ-NEXT-REC
END-IF
END-IF
EXEC SQL
FETCH MYTBL-FORWARD
INTO
:HV-FIELD1-VALUE
,:HV-FIELD2-VALUE
,:HV-FIELD3-VALUE
,:HV-FIELD4-VALUE
,:HV-FIELD5-VALUE
,:HV-FIELD6-VALUE
END-EXEC.
IF SQLCODE > 0
PERFORM ERROR-HANDLING
END-IF

ADD-REC
EXEC SQL
INSERT INTO MY_TABLE
(
FIELD1
,FIELD2
,FIELD3
,FIELD4
,FIELD5
,FIELD6
)
VALUES
(
:HV-FIELD1-VALUE
,:HV-FIELD2-VALUE
,:HV-FIELD3-VALUE
,:HV-FIELD4-VALUE
,:HV-FIELD5-VALUE
,:HV-FIELD6-VALUE
)
END-EXEC.


Now, and this is really important.

CHANGE all existing extract programs so that they use SQL on them, using proper joins where applicable, and selecting ONLY the fields that are required for the program.

Not doing so will mean that program you have now taking 1 hour to run, will take 4-5 hours instead if you replace all the file reads those programs have now with calls to DB2 I/O modules.

Also, and in all cases where it can be done, define the programs and/or the DB2 I/O modules in a way that if running in batch mode they will load all the DB2 table records into memory, and then use a search all within those programs. This obviously needs to be evaluated on a case per case.

If you really wish to use dynamic SQL, then the following is a sample program with what you need to do. Use of parameter markers should be used when the SQL will be used several times, but where the parameters will vary.
e.g. program should prepare the statement once first time its called, but on following calls, only the open/fetch/close cursor should be done. Or only the fetch.

*************************************************************************
**
** Source File Name = dynamic.sqb
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE: This sample program demonstrates the use of a CURSOR.
** The CURSOR is processed using dynamic SQL. This program
** will list all the entries in the system table
** sysibm.systables that do not have the value "STAFF" in
** the "name" column.
**
** For more information about these samples see the README file.
**
** For more information on programming in COBOL, see the:
** - "Programming in COBOL" section of the Application Development Guide.
**
** For more information on building COBOL applications, see the:
** - "Building COBOL Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*************************************************************************

Identification Division.
Program-ID. "dynamic".

Data Division.
Working-Storage Section.

copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".

EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 table-name pic x(20).
01 st pic x(80). 1
01 parm-var pic x(18).
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.

Procedure Division.
Main Section.
display "Sample COBOL program: DYNAMIC".

display "Enter your user id (default none): "
with no advancing.
accept userid.

if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.

* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".

EXEC SQL CONNECT TO sample USER :userid USING asswd
END-EXEC.
if SQLCODE not equal 0 DISPLAY "SQL ERROR" SQLCODE.

move "SELECT TABNAME FROM SYSCAT.TABLES
- " ORDER BY 1
- " WHERE TABNAME <> ?" to st.
EXEC SQL PREPARE s1 FROM :st END-EXEC. 2
if SQLCODE not equal 0 DISPLAY "SQL ERROR" SQLCODE.

EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. 3

move "STAFF" to parm-var.
EXEC SQL OPEN c1 USING arm-var END-EXEC. 4
if SQLCODE not equal 0 DISPLAY "SQL ERROR" SQLCODE.

perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.

EXEC SQL CLOSE c1 END-EXEC. 6
if SQLCODE not equal 0 DISPLAY "SQL ERROR" SQLCODE.

EXEC SQL COMMIT END-EXEC.
if SQLCODE not equal 0 DISPLAY "SQL ERROR" SQLCODE.

EXEC SQL CONNECT RESET END-EXEC.
if SQLCODE not equal 0 DISPLAY "SQL ERROR" SQLCODE.

End-Main.
go to End-Prog.

Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :table-name END-EXEC. 5
if SQLCODE not equal 0
go to End-Fetch-Loop.
display "TABLE = ", table-name.
End-Fetch-Loop. exit.

End-Prog.
stop run.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

to Glenn9999,

The approach is feasible, just a nightmare in terms of performance.

On the site I am working now, we are changing all batch programs we can to use joins instead of the calls to the DBI modules used, with huge performance benefits as expected.

The original system by the way was a hierarchal database (SUPRA if not mistaken), and as such there are sometimes 3 calls to 3 diff programs to fetch records from diff DB2 tables, so we can get to the real and final table containing the info we really need.
Nightmare, but feasible.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kick2006]

to Frederico

for the first sample,the program will be :

PERFORM UNTIL 'data not found'
CALL DBI USING 'read next' ...
******
PERFORM 'edit-process'
******
END-PERFORM

my question is whether the CURSOR can be kept in per execution(CALL) in the loop.

the flow of the loop will be :
fist time:
open the cursor
fetch
if not end of data
return data
else
return 'data not found'
end-if
second time:
fetch (is this fetch correctly executed?)
if not end of data
return data
else
return data not found
end-if

thanks for advance,
Regards

 

[fredericofonseca]

It can as per the pseudo example I gave you. Correct compile and link parameters required eventually.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com