Example: Using Data Virtualization Manager in a COBOL program

This example shows how a non-relational data source can be accessed via SQL from a COBOL program using the DS Client high-level API.

About this example

Assume that you have a VSAM data set that has been virtualized as a virtual table STAFFVS, and you need to access it from a program that is written in a high-level language such as COBOL.

Data Virtualization Manager offers a DS Client high-level language API through the program AVZCLIEN that is utilized with this sequence of requests:
  1. Open a connection to the Data Virtualization Manager server
  2. Send the SQL command to the Data Virtualization Manager server
  3. Receive the data back from the Data Virtualization Manager server until all data of this SQL command is received
  4. Close the connection to the Data Virtualization Manager server
When multiple SQL commands are needed, each of them must be completely received before the next one can be sent. For more information, see Multiple SQL commands.

The program’s data structures

The COBOL program needs to have a DS Client Control Block (DVCB) defined in its working storage and a copy book can be used for that. See Example: DS Client control block (DVCB).

Various other variables are also needed and they are discussed when they are referenced. See Other useful variables.

Program preparation

The program should be compiled without any specific options.

The program should be linked as follows:
//SYSLIB DD DSN=AVZ.SAVZLOAD,DISP=SHR
//SYSLMOD DD DSN=APPL.LOADLIB,DISP=SHR
//SYSIN DD *
INCLUDE SYSLIB(AVZCLIEN)
NAME module(R)
AVZCLIEN is to be found in the Data Virtualization Manager SAVZLOAD library.

Batch program execution

The method to invoke an existing module that uses Data Virtualization Manager as well does not change, so no special instructions are needed.

More specifically, the invocation of a module will also stay the same for modules running in BMP regions or modules attaching to Db2 for z/OS.

In this example, the module is called using EXEC PGM=, which does not require a BMP region or a connection to Db2 for z/OS.
//STEP1 EXEC PGM=module,REGION=4096K
//STEPLIB DD DSN=APPL.LOADLIB,DISP=SHR
// DD DSN=AVZ.SAVZLOAD,DISP=SHR

OPEN request

The following example shows an OPEN request:
MOVE 'AVZC' TO DVCB-SSID.
MOVE MESSAGE-LENGTH TO DVCB-MESSAGE-LENGTH.
MOVE FCT-OPEN TO DVCB-REQUEST-CODE.
MOVE 'Y' TO DVCB-OPT-SQLDA.
MOVE SQLDA-LENGTH TO DVCB-SQLDA-LENGTH.
CALL 'AVZCLIEN' USING DVCB SEND-AREA RECEIVE-AREA
ERROR-AREA SQLDA-AREA.
PERFORM 5000-CHECK-RESPONSE.
Notes:
  • The first MOVE identifies the name of the Data Virtualization Manager server that you will work with.
  • The second MOVE initializes the message length that will be passed to the server
  • The third MOVE will put OPEN in the request.
  • The fourth MOVE tells the server that, upon RECV time, metadata information (mostly for debugging) must be returned in the area that is identified by the fifth parameter of the CALL. Metadata is returned for each column in the result set. See Example: DS Client control block (DVCB) for the structure of this area.
  • The fifth MOVE sets the length of this area of metadata information.

The OPEN request will initiate the communication with the Data Virtualization Manager server through the API called AVZCLIEN.

After the CALL statement is executed, the various one-byte fields in the DVCB-RETURN-FLAGS group of the DVCB can be tested for a Y value. See the contents of a sample paragraph 5000-CHECK-RESPONSE in Typical error checking.

Note that a Y for such a flag is not necessarily an error (they are informational), except for DVCB-ERROR-RETURNED which might be a reason to stop the execution of the program.

The following points are about the metadata information in SQLDA-AREA (the fifth parameter of the CALL):
  • The header is 16 bytes long, where SQLDAID is an eye-catcher with the value SQLDA and SQLDABC contains the total length of SQLDA as returned by the server.
  • Each repeating SQLVAR group (44 bytes long) has the name of the column in SQLNAME and the length of the name in SQLNAME-LENGTH.

SEND request

The following example shows a SEND request:
MOVE FCT-SEND TO DVCB-REQUEST-CODE.
MOVE SEND-LENGTH TO DVCB-SEND-LENGTH.
CALL 'AVZCLIEN' USING DVCB SEND-AREA RECEIVE-AREA
ERROR-AREA SQLDA-AREA.
PERFORM 5000-CHECK-RESPONSE.
Notes:
  • The first MOVE will put SEND in the request.
  • The second MOVE initializes the length of SEND-AREA into the DVCB, in practice the length of the SQL command. SEND-AREA is a placeholder for the SQL command that you want to pass to the Data Virtualization Manager server.

Again, check for eventual return information with 5000-CHECK-RESPONSE in Typical error checking.

You must build the SQL command yourself in your COBOL program, maybe using character functions in the COBOL language. An example is shown as follows:
STRING
"SELECT STAFFVS_KEY_ID,"
DELIMITED BY SIZE
" STAFFVS_DATA_NAME,"
DELIMITED BY SIZE
" STAFFVS_DATA_DEPT,"
DELIMITED BY SIZE
" STAFFVS_DATA_JOB,"
DELIMITED BY SIZE
" STAFFVS_DATA_YRS"
DELIMITED BY SIZE
" FROM STAFFVS"
DELIMITED BY SIZE
INTO SQL-TEXT.
This example results in the following SQL command being sent:
SELECT STAFFVS_KEY_ID, STAFFVS_DATA_NAME, STAFFVS_DATA_DEPT, STAFFVS_DATA_JOB, 
       STAFFVS_DATA_YRS FROM STAFFVS

where STAFFVS is the virtualized table.

For this example, the length of SQLDA-AREA as returned by the server in SQLDABC is 236 (hex EC): a fixed header of 16 bytes and five groups of 44 bytes. The following example shows a hexadecimal representation of SQLDA-AREA (first repeating group only):
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6
SQLDA \ 4 STAFFVS_KEY_ID
EDDCC444000E00000F000000000000EECCCEE6DCE6CC4444444444444444
28341000000C05051402000000000E2316652D258D940000000000000000

RECEIVE request

Data Virtualization Manager stores the results of the SQL command in the storage of the Data Virtualization Manager started task. Those results must be transferred into the program’s working storage. As the program’s working storage normally is much smaller than the server storage, you must request reception of the server’s results in a repetitive process until Data Virtualization Manager tells you that all data has been received. This concept can be compared to using multi-row fetch from an application with Db2 for z/OS.

The following example shows a way to receive the results repetitively:
PERFORM 4000-PROCESS-RESULTS
UNTIL DVCB-END-OF-DATA = 'Y'
OR DVCB-ERROR-RETURNED = 'Y'.
UNTIL forces at least one execution of 4000-PROCESS-RESULTS.
The following example shows a code snippet to process the result set:
******************************************************************
4000-PROCESS-RESULTS.
******************************************************************
* UNCOMMENT THE COMMENTS TO SHOW ONLY FIRST RESULT SET *
******************************************************************
MOVE 0 TO DVCB-ROWS-RETURNED.
PERFORM 3000-GET-RESULTS.
DISPLAY 'RESULT-SET #' RESULT-SET-NUMBER
PERFORM TEST AFTER VARYING W1 FROM 1 BY 1
UNTIL W1 = DVCB-ROWS-RETURNED
DISPLAY DVC-ID (W1) ' '
DVC-NAME (W1) ' '
DVC-DEPT (W1) ' '
DVC-JOB (W1) ' '
DVC-YEARS (W1) ' '
END-PERFORM
ADD 1 TO RESULT-SET-NUMBER.

Paragraph 4000-PROCESS-RESULTS is executed until Data Virtualization Manager communicates the end of the Data Virtualization Manager result set or the presence of an error.

Inside 4000-PROCESS-RESULTS, some of the coding is for data processing after data has been received. The data is received with the execution of paragraph 3000-GET-RESULTS.
******************************************************************
3000-GET-RESULTS.
******************************************************************
MOVE FCT-RECEIVE TO DVCB-REQUEST-CODE.
MOVE RECEIVE-LENGTH TO DVCB-DATA-BUFFER-LENGTH.
CALL 'AVZCLIEN' USING DVCB SEND-AREA RECEIVE-AREA
ERROR-AREA SQLDA-AREA.
PERFORM 5000-CHECK-RESPONSE.
DISPLAY 'NUMBER OF ROWS IN BUFFER:' DVCB-ROWS-RETURNED.
DISPLAY 'ROW LENGTH:' DVCB-ROW-LENGTH.
ADD DVCB-ROWS-RETURNED TO TOTAL-ROWS.

The first MOVE will put RECV in the request and the second MOVE initializes the length of RECEIVE-AREA into the DVCB. This length should be greater than the largest row returned.

RECEIVE-AREA will receive the data back. Note that RECEIVE-AREA can receive many rows of the Data Virtualization Manager result set.

In this example, the RECEIVE-AREA has 200 positions that can hold 10 rows of the Data Virtualization Manager result set (one row is 20 positions). The first execution of 3000-GET-RESULTS will return the rows 1 to 10, and the fourth execution will return rows 31 to 35 (as there are 35 rows in the result set).

The number of rows returned is in DVCB-ROWS-RETURNED.

CLOSE request

The CLOSE request is not required when the field DVCB-OPT-CLOSE-AFTER has been set to Y.

This can be achieved with the following code example:
MOVE OPT-AUTOCLOSE TO DVCB-OPT-CLOSE-AFTER.

This is an indication that the connection to the Data Virtualization Manager server is automatically closed when the RECV request has delivered all rows of the server’s result set.

The following example shows the CLOSE request if AUTOCLOSE is not used:
******************************************************************
* CLOSE CONNECTION TO DV SERVER *
******************************************************************
9000-CLOSE.
MOVE MESSAGE-LENGTH TO DVCB-MESSAGE-LENGTH.
MOVE FCT-CLOSE TO DVCB-REQUEST-CODE.
CALL 'AVZCLIEN' USING DVCB SEND-AREA RECEIVE-AREA
ERROR-AREA SQLDA-AREA.
PERFORM 5000-CHECK-RESPONSE.

Multiple SQL commands

Assume that an application needs to select some clients and read the orders for these clients.

Technically, the program must RECEIVE all these clients. For each client in the RECEIVE-AREA1 it needs to issue another SQL command statement in the order database and to process these orders as they come into the RECEIVE-AREA2.

Note that the API of this Data Virtualization Manager client uses a stateful protocol, and that you cannot send another SQL statement on the same open connection until you have completed receiving the result set from the current SQL statement.

However, one can have as many open connections as needed. The application must manage two connections, each with its own DVCB: one for clients, another for orders.

Processing logic is then as follows:
  1. Obtain two DVCBs: CLIENTS-DVCB and ORDERS-DVCB
  2. Call OPEN twice (once for CLIENTS-DVCB and once for ORDERS-DVCB)
  3. Call SEND for the CLIENTS fetch, using CLIENTS-DVCB
  4. Call RECV, pointing to CLIENTS-DVCB
  5. Scan the clients in the CLIENTS buffer and for each client:
    1. Call SEND for the ORDERS fetch, using ORDERS-DVCB
    2. Call RECV, using ORDERS-DVCB
    3. Process the orders returned in the ORDERS receive buffer
    4. Loop to 5.b until EndOfData is returned in the ORDERS-DVCB
    5. Locate the next client in the CLIENTS buffer
    6. Return to step 5a.
  6. When the CLIENTS buffer is exhausted, return to step 4
  7. When EndOfData is returned in the CLIENTS-DVCB, call CLOS twice (once for CLIENTS-DVCB and once for ORDERS-DVCB)

Example: DS Client control block (DVCB)

000100******************************************************************00010000
000200* *00020000
000300* COPYRIGHT ROCKET SOFTWARE, INC. 1991, 2016. *00030001
000400* *00040000
000500******************************************************************00050000
000600 01 DVCB. 00060000
000700 03 DVCB-TAG PIC X(4) VALUE 'DVCB'. 00070000
000800 03 DVCB-VERSION PIC X(2) VALUE X'0001'. 00080000
000900 03 DVCB-RESERVED1 PIC X(2). 00090000
001000 03 DVCB-SSID PIC X(4). 00100000
001100 03 DVCB-REQUEST-CODE PIC X(4). 00110000
001200 03 DVCB-CNID. 00120000
001300 05 DVCB-CONNECTION PIC X(12). 00130000
001400 05 DVCB-CONNECTED-SSID PIC X(4). 00140000
001500 03 DVCB-SERVER-GROUP PIC X(8). 00150000
001600 03 DVCB-USER-PARM PIC X(8). 00160000
001700 03 DVCB-SQL-CODE PIC S9(5) COMP. 00170000
001800 03 DVCB-DATA-BUFFER-LENGTH PIC S9(5) COMP. 00180000
001900 03 DVCB-DATA-RETURNED-LENGTH PIC S9(5) COMP. 00190000
002000 03 DVCB-RESERVED2 PIC S9(5) COMP. 00200000
002100 03 DVCB-ROWS-RETURNED PIC S9(5) COMP. 00210000
002200 03 DVCB-OPTIONS. 00220000
002300 05 DVCB-OPT-RECV-MODE PIC X(1). 00230000
002400 05 DVCB-OPT-AUTO-COMMIT PIC X(1). 00240000
002500 05 DVCB-OPT-CLOSE-AFTER PIC X(1). 00250000
002600 05 DVCB-OPT-REFORMAT PIC X(1). 00260000
002700 05 DVCB-OPT-SQLDA PIC X(1). 00270000
002800 05 DVCB-OPT-RESERVED PIC X(3). 00280000
002900 03 DVCB-BLOCKING-TIMEOUT PIC S9(5) COMP. 00290000
003000 03 DVCB-SEND-LENGTH PIC S9(5) COMP. 00300000
003100 03 DVCB-RETURN-CODE PIC S9(5) COMP. 00310000
003200 03 DVCB-DB2-SUBSYSTEM PIC X(4). 00320000
003300 03 DVCB-ROW-LENGTH PIC S9(5) COMP. 00330000
003400 03 DVCB-SQLDA-LENGTH PIC S9(5) COMP. 00340000
003500 03 DVCB-MESSAGE-LENGTH PIC S9(5) COMP. 00350000
003600 03 DVCB-MAP-NAME PIC X(50). 00360000
003700 03 DVCB-RETURN-FLAGS. 00370000
003800 05 DVCB-ROW-RETURNED PIC X(1). 00380000
003900 05 DVCB-SQLCODE-RETURNED PIC X(1). 00390000
004000 05 DVCB-MESSAGE-RETURNED PIC X(1). 00400000
004100 05 DVCB-SQLDA-RETURNED PIC X(1). 00410000
004200 05 DVCB-END-OF-DATA PIC X(1). 00420000
004300 05 DVCB-ERROR-RETURNED PIC X(1). 00430000
004400 05 DVCB-PARMS-RETURNED PIC X(1). 00440000
004500 05 DVCB-END-OF-RSET PIC X(1). 00450000
004600 03 DVCB-RESERVED3 PIC X(2). 00460000
004700 03 DVCB-ROW-LIMIT PIC S9(5) COMP. 00470000
004800 03 DVCB-USERID PIC X(8). 00480000
004900 03 DVCB-PASSWORD PIC X(8). 00490000
005000 03 DVCB-MAPREDUCE-ID PIC X(2). 00500000
005100 03 DVCB-MAPREDUCE-NO PIC X(2). 00510000
005200 03 DVCB-RESERVED4 PIC X(64). 00520000
005300 03 DVCB-TAG2 PIC X(4) VALUE 'DVCB'. 00530000
005400 01 FCT-SEND PIC X(4) VALUE 'SEND'. 00540000
005500 01 FCT-RECEIVE PIC X(4) VALUE 'RECV'. 00550000
005600 01 FCT-OPEN PIC X(4) VALUE 'OPEN'. 00560000
005700 01 FCT-CLOSE PIC X(4) VALUE 'CLOS'. 00570000
005800 01 OPT-AUTOCLOSE PIC X VALUE 'Y'. 00580000
005900 01 OPT-LOCAL-MODE PIC X VALUE 'L'. 00590000
006000 01 OPT-MOVE-MODE PIC X VALUE 'M'. 00600000

Other useful variables

003300 01 SEND-LENGTH PIC S9(5) COMP VALUE 400. 00330000
003400 01 RECEIVE-LENGTH PIC S9(5) COMP VALUE 200. 00340013
003500 01 MESSAGE-LENGTH PIC S9(5) COMP VALUE 40. 00350000
003600 01 SQLDA-LENGTH PIC S9(5) COMP VALUE 1000. 00360000
003700 01 ERROR-AREA. 00370000
003800 02 ERROR-AREA1 PIC X(40) VALUE IS SPACES. 00380000
003900 01 SEND-AREA. 00390000
004000 02 SQL-TEXT PIC X(400) VALUE IS SPACES. 00400000
004100 01 SQLDA-AREA. 00410000
004200 02 SQLDAID PIC X(8). 00420000
004300 02 SQLDABC PIC S9(5) COMP. 00430000
004400 02 SQLN PIC S9(4) COMP. 00440000
004500 02 SQLD PIC S9(4) COMP. 00450000
004600 02 SQLVAR OCCURS 100. 00460000
004700 05 SQLTYPE PIC S9(4) COMP. 00470000
004800 05 SQLLEN PIC S9(4) COMP. 00480000
004900 05 SQLDATA PIC S9(5) COMP. 00490000
005000 05 SQLIND PIC S9(5) COMP. 00500000
005100 05 SQLNAME-LENGTH PIC S9(4) COMP. 00510000
005200 05 SQLNAME PIC X(30). 00520000
005300 01 RECEIVE-AREA. 00530000
005400 02 RECORD-SET PIC X(200). 00540013
005500 02 RECORD-OUT REDEFINES RECORD-SET OCCURS 10 TIMES. 00550013
005600 05 DVC-ID PIC S9(4) COMP. 00560000
005700 05 DVC-NAME PIC X(9). 00570000
005800 05 DVC-DEPT PIC S9(4) COMP. 00580000
005900 05 DVC-JOB PIC X(5). 00590000
006000 05 DVC-YEARS PIC S9(4) COMP. 00600000

Typical error checking

014400******************************************************************01540000
014500 5000-CHECK-RESPONSE. 01550000
014600******************************************************************01560000
014700 EVALUATE TRUE 01570000
014800 WHEN DVCB-ROW-RETURNED = 'Y' 01580000
014900 DISPLAY 'ROW RETURNED: TRUE' 01590000
015000 WHEN DVCB-SQLCODE-RETURNED = 'Y' 01600000
015100 DISPLAY 'SQLCODE RETURNED: TRUE' 01610000
015200 DISPLAY 'SQLCODE:' DVCB-SQL-CODE 01620000
015300 WHEN DVCB-MESSAGE-RETURNED = 'Y' 01630000
015400 DISPLAY 'MESSAGE RETURNED: TRUE' 01640000
015500 DISPLAY 'MESSAGE:' ERROR-AREA 01650000
015600 WHEN DVCB-SQLDA-RETURNED = 'Y' 01660000
015700 DISPLAY 'SQLDA RETURNED: TRUE' 01670000
015800 WHEN DVCB-END-OF-DATA = 'Y' 01680000
015900 DISPLAY 'END-OF-DATA RETURNED: TRUE' 01690000
016000 WHEN DVCB-ERROR-RETURNED = 'Y' 01700000
016100 DISPLAY 'ERROR RETURNED: TRUE' 01710000
016200 PERFORM 6000-WRITE-ERROR 01720000
016300 WHEN DVCB-PARMS-RETURNED = 'Y' 01730000
016400 DISPLAY 'IN/OUT PARMS RETURNED: TRUE' 01740000
016500 WHEN DVCB-END-OF-RSET = 'Y' 01750000
016600 DISPLAY 'END OF RESULT-SET RETURNED: TRUE' 01760000
016700 WHEN OTHER 01770000
016800 DISPLAY 'NO RESPONSE FLAGS SET' 01780011
016900 END-EVALUATE. 01790000
017000******************************************************************01800000
017100 6000-WRITE-ERROR. 01810000
017200******************************************************************01820000
017300 EVALUATE DVCB-RETURN-CODE 01830000
017400 WHEN 20 01840000
017500 DISPLAY 'DV SERVER NOT AVAILABLE' 01850000
017600 WHEN 32 01860000
017700 DISPLAY 'CONNECTION HANDLE NOT FOUND ON THIS SERVER' 01870000
017800 WHEN 36 01880000
017900 DISPLAY 'REQUEST MADE WHILE CONNECTION IN WRONG STATE' 01890000
018000 WHEN 40 01900000
018100 DISPLAY 'ROW DOES NOT FIT INTO RECEIVE BUFFER' 01910000
018200 WHEN 44 01920000
018300 DISPLAY 'RECV REQUEST TIMEOUT WAITING FOR DATA' 01930000
018400 WHEN 60 01940000
018500 DISPLAY 'SQL STATEMENT SEND LENGTH NOT SET' 01950000