GDG Generic Database Gateway

GDG Generic Database Gateway

Introduction

1. General Concept
2. GDG Preprocessor
   
3. GDG batch utility
   
4. GDG C langage API
   

Annexes

1. GDG Area COBOL structure
2. GDG Area header for C program
3. Sample use of GDG COBOL preprocesseur
4. Sample use of GDGUTIL utility on MVS

1 - General Concept

Server side:

Fig.1 Client/Server organisation

Client side:

Available Platforms:

GDG Client

Item	z/OS	USS OMVS
GDG API
COBOL Precompiler
GDGUTIL batch tool

GDG Server

SGBD	Linux on Z	Linux-x86	AIX	Solaris
PostgreSQL
MySQL
DB2 UDB
Oracle

2 - GDG preprocessor

The GDG preprocessor is intended for COBOL developers. It converts EXEC GDG clauses into the corresponding GDG API calls and inserts the required work areas, including GDG-AREA and its GDGRC and SQLRC return codes.

On MVS, it reads the source program from DDNAME FGDG and writes the result to DDNAME FCOB. Under USS, both files are command-line arguments and default to standard input and output:

gdg2cob gdg-source cobol-source
gdg2cob < gdg-source > cobol-source

The preprocessor supports the following operations:

Fig. 2 - General structure of a COBOL program using the GDG API.

An EXEC GDG clause follows standard COBOL formatting rules: it may span several lines and must end with a full stop. Parameters may be alphanumeric constants or COBOL variables defined as USAGE IS DISPLAY and prefixed with a colon.

INIT

Initializes the GDG-AREA communication area generated by the GDG2COB preprocessor. No return code is produced.

CONNECT

Connects to the GDG server and authenticates the program with the selected database. dbalias identifies the database, instance or alias. connectstring uses the form userid[:password]@hostname[:port]; optional values use installation defaults.

Return codes

GDGRC: 0 when successful; non-zero when the TCP/IP connection or database access fails.

SQLRC: not applicable.

CALLPROC

Calls a stored procedure identified by the first parameter. Optional arguments may be alphanumeric constants or variables.

Return codes

GDGRC: 0 when successful; 1000 + n when parameter n is invalid; other values indicate a broken TCP/IP connection.

SQLRC: not applicable.

SENDSQL

Sends one SQL request supplied as an alphanumeric constant or variable.

Return codes

GDGRC: 0 when successful; non-zero when the TCP/IP connection or database access is interrupted.

SQLRC: not applicable.

SETREPLY

Controls how the API handles the rows returned by a SELECT. GDG-PARSE splits results field by field into the COBOL variables of the next GETREPLY; GDG-NO-PARSE returns each complete row or discards it when no variable is supplied.

Return codes

GDGRC and SQLRC: not applicable.

GETREPLY

Stores results from the preceding request in an optional list of variables. It is required after every SENDSQL or CALLPROC.

Return codes

GDGRC: 0 when successful; 1000 + n when parameter n is invalid; other values indicate an interrupted TCP/IP connection.

SQLRC: 0 when successful; 100 when no more rows are available; other values are returned by the SQL engine.

EXEC GDG examples in a COBOL program

gdg2cob gdg-source cobol-source

3 - GDGUTIL batch utility

GDGUTIL sends requests to a GDG server. It reads GDG or SQL subcommands from DDNAME SYSIN on MVS or standard input under USS, and writes results to DDNAME SYSPRINT or standard output.

GDGUTIL explicitly handles two subcommands:

echo text
connect dbalias [userid[:password]@hostname[:port]]

All other clauses are treated as SQL commands and sent to the GDG server for execution. See the MVS example in Appendix D.

4 - GDG C API

The API comprises a C header (gdgbase.h) and a library delivered as a Windows DLL, a UNIX/Linux shared object, or a traditional static object library for MVS, UNIX/Linux or Windows.

The library implements five entry points:

Session management

GDGOPEN

GDGCLOSE

Stored procedures and SQL commands

GDGSPROC

GDGSRQST

Reading results

GDGGRPLY

All routines receive GDG_AREA as their first parameter and return GDGRC for connection state and parameter validity. SQLRC is supplied by the SQL engine when results are read.

GDGOPEN

GDGCLOSE

GDGSPROC

GDGSRQST

GDGGRPLY

GDGRC is 0 while the connection remains established and parameters are valid, 1 when the connection is unavailable or has been closed, and 1000 + n when parameter pair n (area and length) is invalid.

SQLRC is set only by GDGGRPLY. For SELECT commands and similar procedures it is returned unchanged by the SQL engine. For SQL commands that return no row, 0 is changed to 100 to provide a consistent end-of-results condition; other values are passed through unchanged.

Appendix A: GDG AREA COBOL structure

   * Zone automatiquement generee par le preprocesseur GDG :
    01  GDG-AREA.
        05 SQLRC   PIC S9(9) COMP.
        05 GDGRC   PIC S9(9) COMP.
        05 CNXFD   PIC S9(9) COMP.
        05 REQNO   PIC S9(9) COMP.
        05 GDGDBG   PIC X.
        05 GDGRQTYP  PIC X.
        05 GDGPRG   PIC X(8).
        05 FILLER   PIC X.

Appendix B: GDG Area header for C programs (gdgbase.h)

typedef struct t_gdg {
      int  sqlrc;         /* retcode sql */
      int  gdgrc;         /* retcode GDG */
      int  cnxfd;         /* handle  (0, 1, ... */
      int  reqno;         /* n. requete, future extension */
      char gdgdbg;        /* 'D' = debug GDG */
      char reqtyp;        /* 'S' = complete result, other = field by field */
      char prgname[8];    /* pour les traces                          */
      char filler;        /* internal                                 */

} T_GDG, * PGDG;

#define LGDG sizeof(T_GDG)

void GDGOPEN(PGDG pgdg, char * dbn, char * cnxstr);
void GDGCLOSE(PGDG pgdg);
void GDGSPROC(PGDG pgdg, char * procname, ... );
void GDGSRQST(PGDG pgdg, char * rqst, int * plg);
void GDGGRPLY(PGDG pgdg, ... );

#define GDG_ERR_OK         0
#define GDG_ERR_NOPIPE     1
#define GDG_ERR_BROKEN     2
#define GDG_ERR_BADARG  1000

Appendix C: Sample use of the GDG COBOL preprocessor

//PRECOMP  JOB XYZ,'ACCOUNT',MSGLEVEL=(1,1),MSGCLASS=X,CLASS=A
//PREGDG   EXEC PGM=GDG2COB
//STEPLIB  DD  DISP=SHR,DSN=XYZ.GDG.LOADLIB
//FCOB     DD  DISP=SHR,DSN=XYZ.DEVEL.COB(ESGDGCB)
//FGDG     DD  *
       IDENTIFICATION DIVISION.
       PROGRAM-ID.    ESGDGCB.
      *----------------------------------------------------------------
      *
      * Ce programme demontre l'utilisation du preprocesseur GDG COBOL :
      *
      * 1) connect to the database
      * 2) send a stored procedure with its arguments,
      *    read results row by row into selected fields.
      * 3) envoyer une commande select en format libre,
      *    read results row by row as complete records.
      * 4) envoyer une commande update en format libre,
      *    read the result for the SQL return code only
      * 5) close the connection.
      *
      *----------------------------------------------------------------
       ENVIRONMENT DIVISION.
      *
       CONFIGURATION SECTION.
       SPECIAL-NAMES.
           DECIMAL-POINT IS COMMA.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
      *
       DATA DIVISION.
       FILE SECTION.
      *
      *
       WORKING-STORAGE SECTION.
      *
      *------  LES ZONES DE MANOEUVRE ------------------
      *
      * Connection areas
       77  ESGDGV  PIC X(8)  VALUE 'V 3.12A'.
       77  CNXSTR  PIC X(50).
       77  DBNAME  PIC X(10) VALUE 'archives'.
      * Fields for SELECT results
       77  JD           PIC X(15).
       77  PATH         PIC X(40).
       77  NUM          PIC 9999.
       77  SIZ          PIC 9999.
       77  DAT          PIC X(12).
      * Divers
       77  CATEG        PIC X(12).
       77  STMT         PIC X(1000).
       77  result     PIC X(1000).
      *
       PROCEDURE DIVISION.
       MAIN SECTION.
      *
      *--------------------------------------------------------------*
      *
           DISPLAY "Debut programme " ESGDGV
           ACCEPT CNXSTR
           DISPLAY "Database archives on " CNXSTR.
      *--------------------------------------------------------------*
      *       PRIMITIVE INIT                                         *
           EXEC GDG 'INIT'.
      *--------------------------------------------------------------*
      *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *       PRIMITIVE CONNECT                                      *
      *       BUT : ETABLIR LA CONNEXION                             *
      *       ARGS: database name, string d'identification          *
      *       RETCODE  : GDGRC = 0 : Ok,  <> 0 : KO                  *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *
           DISPLAY "GDG CONNECT " DBNAME " + " CNXSTR " ..."
           EXEC GDG 'CONNECT'  USING :DBNAME :CNXSTR.
           DISPLAY "gdgopen  -> GDGRC=" GDGRC.
           IF GDGRC NOT = 0  GO TO FIN.
      *
      *
      *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *       PRIMITIVE CALLPROC :                                   *
      *       BUT :   DEMANDE D'EXEC D'UNE PROCEDURE                 *
      *       ARGS: NOM de la proc, params_proc ...                  *
      *       NOM DE LA PROC : procsel1                              *
      *       Param de la PROC: CATEG, 12 octets                     *
      *       RETCODE  : GDGRC = 0 : Ok,  <> 0 : KO                  *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *
           DISPLAY "GDG CallProc for procsel1 ..."
           MOVE 'DBMS' TO CATEG
           EXEC GDG 'CALLPROC' USING 'procsel1' :CATEG.
           DISPLAY "gdg Callproc : retcode GDGRC=" GDGRC.
           IF GDGRC NOT = 0  GO TO CLOSE-IT.
      *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *       PRIMITIVE GETREPLY :                                   *
      *       PURPOSE: READ ONE RESULT ROW                      *
      *       ARGS: Liste de champs                                  *
      *             ex       : JD, NUM, PATH, SIZ                    *
      *       NOTE: set GDGRQTYP to blank for field-by-field reading*
      *       RETCODES : GDGRC = 0 : Ok,  <> 0 : KO                  *
      *                  SQLRC = 0 : Ok,  <> 0 (100) : plus rien     *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *
           DISPLAY "gdg getreply for procsel1 ...".
      * Select field-by-field results:
           EXEC GDG 'SETREPLY' USING 'GDG-PARSE'.
       LEC.
      * Read one result row into the selected fields:
           EXEC GDG 'GETREPLY' USING :JD :NUM :PATH :SIZ :DAT.
           IF GDGRC NOT = 0 GO TO CLOSE-IT.
           IF SQLRC = 0
                 DISPLAY JD "+" NUM "+" PATH "+" SIZ "+" DAT
                 GO TO LEC.
      * On s'arrete quand SQLRC est different de 0
      *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *       PRIMITIVE SENDSQL  :                                   *
      *       BUT : ENVOYER UN PHRASE SQL ARBITRAIRE                 *
      *       ARGS: The SQL command                                       *
      *       RETCODES : GDGRC = 0 : Ok,  <> 0 : KO                  *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *
      * A free-format SELECT :
           DISPLAY "gdg sendsql select EIFFEL 1 ..."
           EXEC GDG 'SENDSQL'  USING
            "select id, num, dat, siz from arch where ID='EIFFEL'".
           IF GDGRC NOT = 0  GO TO CLOSE-IT.
           DISPLAY "Gdg Select : retcode SQL=" SQLRC.
      *--------------------------------------------------------------
      *  GETREPLY : read one complete result row
      *--------------------------------------------------------------
           DISPLAY "gdg getreply for select ...".
      * Select unparsed results:
           EXEC GDG 'SETREPLY' USING 'GDG-NO-PARSE'.
       LEC2.
      * Read one result row unchanged:
           EXEC GDG 'GETREPLY' USING :result.
           IF GDGRC NOT = 0 GO TO CLOSE-IT.
           IF SQLRC = 0
                 DISPLAY result
                 GO TO LEC2.
      * On s'arrete quand SQLRC est different de 0
      *
      *--------------------------------------------------------------
      *------- idem pour une commande update :
      *--------------------------------------------------------------
           DISPLAY "gdg sendsql update EIFFEL ..."
      * Mettre en place la commande SQL :
           MOVE
            "update arch set dat='200-05-14' where ID='EIFFEL'"
           TO STMT
      * Envoyer la commande par la routine GDGSRQST :
           EXEC GDG 'SENDSQL'  USING :STMT.
           IF GDGRC NOT = 0  GO TO CLOSE-IT.
      * Read the result; only return codes are relevant:
           EXEC GDG 'SETREPLY' USING 'GDG-NO-PARSE'.
      * Appel de la routine GDGGRPLY, sans arguments,
      * juste pour les return codes :
           DISPLAY "gdg getreply for update EIFFEL ..."
           EXEC GDG 'GETREPLY'.
           IF GDGRC NOT = 0 GO TO CLOSE-IT.
           DISPLAY "Gdg Update : retcode SQL=" SQLRC.
      *
           DISPLAY "gdg sendsql select EIFFEL 2 ..."
      *--------------------------------------------------------------
      * Another free-format SELECT :
      *--------------------------------------------------------------
           EXEC GDG 'SENDSQL'  USING
            "select dat from arch where ID='EIFFEL'".
           IF GDGRC NOT = 0  GO TO CLOSE-IT.
           DISPLAY "Gdg Select : retcode SQL=" SQLRC.
      *--------------------------------------------------------------
      *  GETREPLY : read one complete result row
      *--------------------------------------------------------------
           DISPLAY "gdg getreply for select EIFFEL ...".
      * Select field-by-field results:
           EXEC GDG 'SETREPLY' USING 'GDG-PARSE'.
       LEC3.
      * Read one result row unchanged:
           EXEC GDG 'GETREPLY' USING :DAT.
           IF GDGRC NOT = 0 GO TO CLOSE-IT.
           IF SQLRC = 0
                 DISPLAY "DAT For EIFEEL : " dat
                 GO TO LEC3.
      * On s'arrete quand SQLRC est different de 0
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *       PRIMITIVE CLOSE    :                                   *
      *       BUT : COUPER LA CONNEXION                              *
      *       ARGS: NEANT.                                           *
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *--------------------------------------------------------------*
      *
       CLOSE-IT.
           EXEC GDG 'CLOSE'.
       FIN.
           STOP RUN.
/*

Appendix D: Sample use of the GDGUTIL utility on MVS

//JHHEUTL  JOB 1,'PP5645-001',MSGLEVEL=(1,1),MSGCLASS=X,CLASS=A
//*------ EXEC GDGUTIL
//GDGUTIL  EXEC PGM=GDGUTIL
//STEPLIB  DD DISP=SHR,DSN=XYZ.GDG.LOADLIB
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD  *
echo ------------ connection ... ------------------
connect archives myuserid:mypasswd!mygdgsrv:5432
echo ------------ procedure procsel1 ... ---------
select procsel1('MMEDIA')
echo ------------ select DBMS ... ----------------
select id, num, path, dat from arch where cat='DBMS'
echo ------------ update EIFFEL ... --------------
update arch set dat='2003-08-10'  where id='EIFFEL'
echo ------------ display EIFFEL ... -------------
select id, num, path, dat from arch where id='EIFFEL'
echo ============ End of Job =====================
/*

For more information, contact us.