Rational Developer for System z

Connecting to a DB2 database using COBOL or PL/I

Before attempting to connect to a DB2® database, you must first configure a database connection (refer to Configuring a database connection) and then configure your workbench to use that database connection with the Database Connection wizard (refer to Setting up the connection to a DB2 UDB zSeries database for an example of how to connect to an OS/390® DB2 database).
  1. Edit the property group associated with the COBOL or PL/I program.
  2. Click the COBOL or PL/I tab to open the COBOL Settings or PL/I Settings page.
  3. On the Local Compiler Options page, provide the following information:
    Compile Options
    TEST,MACRO,OPTIONS
    Note: Do not specify DB2-related options in the Compile Options field. The necessary compiler options and suboptions are automatically generated for you from your selected DB2 connection and the options you enter in the SQL Options field.
    SYSLIB
    For COBOL, specify the following:
    installation_directory\INCLUDE\COBOL_A
    For PL/I, specify the following:
    installation_directory\EXTRAS\INCLUDE\PLI
    Note: Replace installation_directory with your DB2 installation directory.
  4. Select Source contains EXEC SQL statements.
  5. Click Browse and select your DB2 connection. If you have not yet set up a connection to your database, click New Connection.
  6. Optional: Enter additional options to pass onto the SQL preprocessor in the SQL Options field. Separate multiple SQL options with commas.
  7. Click the Link tab to open the Link Options page in the property group editor.
  8. Expand the ELAXFLNK procedure and click the LINK step.
  9. In the Link Options field, type /de db2api.lib.
  10. Save the property group.
  11. For COBOL Programs: Insert the following statement before using any SQL statements in the WORKING-STORAGE, LOCAL-STORAGE, or LINKAGE sections of your code:
    EXEC SQL INCLUDE SQLCA END-EXEC;
    Delimit SQL statements with EXEC SQL and END-EXEC statements. Here is an example using the DB2 Sample database:
    EXEC SQL UPDATE employee
           SET firstnme = 'Myname'
           WHERE empno = '000010' END-EXEC.
  12. For PL/I Programs: Insert the following statement before using any SQL statements in your code:
    EXEC SQL INCLUDE SQLCA;
    EXEC SQL INCLUDE SQLDA;
    Also ensure that all of your SQL statements are formatted as follows in your code:
    EXEC SQL statement;
    where statement is your SQL statement.
Sample COBOL code to access a DB2 database
      * ---------------------------------------------------
      *   Module Name: COBOLDB2.cbl
      *
      *   Description: Sample COBOL with DB2 program.
      *
      *   Purpose: Performs a Select on the employee table
      *   in the Sample database shipped with DB2.
      *
      *   COMPLILER OPTIONS (be sure to change the USERNAME and PASSWORD):
      *   DATA,EXIT(ADEXIT(FTTDBKW)),sql('database sample user USERNAME using PASSWORD')
      *
      *   SYSLIB:
      *   C:\Program Files\IBM\SQLLIB\INCLUDE\COBOL_A
      *
      *   ILINK OPTIONS:
      *   /de db2api.lib
      *
      * ---------------------------------------------------
       Identification Division.
       Program-ID.  COBOLDB2.

       Data Division.

      *Make sure you have SQLCA included in Working-Storage
       Working-Storage Section.
       EXEC SQL INCLUDE SQLCA END-EXEC.

      *Data structure to store the Firstname of the employee
       01 Program-pass-fields.
          05 Firstnme         Pic x(30).

       Procedure Division.
      *A Connection to the database must be made!
            EXEC SQL CONNECT TO sample END-EXEC.

      *Performs a SQL SELECT to get the firstname of the employee
      *with the employee number of 10.
           EXEC SQL SELECT firstnme INTO :Firstnme
           FROM employee
           WHERE empno = '000010' END-EXEC.

      *Displays the firstname we pulled from the Sample database.
           Display "Firstname"
           Display "========="
           Display Firstnme

           Display " "
      *Displays the status of the SQL statements
           Display SQLCA

           Goback.
Sample PL/I code to access a DB2 database
*PROCESS MARGINS(1,100) LANGLVL(SAA2) MACRO;
*PROCESS SOURCE XREF;
*PROCESS NOT('ª^') DFT(BYVALUE)  ;
*PROCESS INCLUDE (EXT('CPY','INC','PLP','MRP'));

/*********************************************************************/
/*  Author                : Your name                                */
/*  Date-written          : 7/07/04                                  */
/*  Primary Function(s)   : Perfom a select on the Sample db2 DB     */
/*                                                                   */
/*  What you need to change:                                         */
/*  Make sure you change the USERNAME and PASSWORD for your Sample   */
/*  DB2 database BOTH in the *PROCESS PP(SQL... statment and in the  */
/*  Connect query towards the middle of this example code.           */
/*                                                                   */
/*********************************************************************/
 DB2Samp: PROCEDURE OPTIONS(MAIN);

    DISPLAY('EXAMPLE STARTED.');

 /*******************************************************************/
 /* declare SQL structures                                          */
 /*******************************************************************/
 EXEC SQL INCLUDE SQLCA;
 EXEC SQL INCLUDE SQLDA;
 EXEC SQL WHENEVER SQLERROR CONTINUE;

 DCL VALCODE                 BINARY(31) FIXED INIT(0);
 DCL GOODCODE                BINARY(31) FIXED INIT(0);
 DCL FIRSTNME                 CHAR(30) var init('NOVAL');

 /*******************************************************************/
 /* CONNECT TO DATABASE IS MANDATORY OR TABLE WON'T BE FOUND        */
 /*******************************************************************/
 EXEC SQL CONNECT TO sample USER 'USERNAME' using 'PASSWORD';
 CALL PRSQLC ('CONNECT TO SAMPLE');


 /********************************************************************/
 /*Performs a SELECT to get the first name of the employee           */
 /*with and employee number of '000010' and store it in the variable */
 /*FIRSTNME and then displays it.                                    */
 /********************************************************************/
  EXEC SQL SELECT firstnme
           INTO :FIRSTNME
           FROM employee
           WHERE empno = '000010';

           DISPLAY('FIRSTNME');
           DISPLAY('========');
           DISPLAY(FIRSTNME);
           DISPLAY('');

           CALL PRSQLC ('SELECT');


 /****************************************************/
 /****************************************************/
 /* The PRSQLC procedure verifys that the SQL worked.*/
 /* If it does not work, it will display the SQLCode */
 /* as well as the SQL error message.                */
 /*                                                  */
 /* Written by: person                               */
 /****************************************************/
 /****************************************************/
    PRSQLC:   PROCEDURE(FUNCTION);
    DECLARE FUNCTION CHARACTER (80) VARYING;

    IF (SQLCODE = GOODCODE) THEN
       PUT SKIP(2) EDIT (FUNCTION, ' WAS SUCCESSFUL')
                     (A(LENGTH(FUNCTION)), A(15));
    ELSE DO;
       IF (SQLCODE ^= 1) THEN DO;
          PUT SKIP(2) EDIT (FUNCTION, ' GOT SQLCODE ', sqlcode)
                     (A(LENGTH(FUNCTION)), A(13), F(8));
          PUT SKIP(2) EDIT (FUNCTION, ' GOT SQLERRM ', SQLERRM)
                     (A(LENGTH(FUNCTION)), A(13), A(70));
          VALCODE = VALCODE + 1;
          PUT SKIP(2) EDIT ('| VALCODE = ', VALCODE) (A(12), F(8));
       END;
       ELSE PUT SKIP(2) EDIT (FUNCTION, ' REACHED BOTTOM OF TABLE')
                       (A(LENGTH(FUNCTION)), A(24));
    END;
 END PRSQLC;
 /**************************************************/
 /**************************************************/

 EXEC SQL COMMIT;


 /*******************************************************************/
 END DB2Samp;

Terms of use | Feedback

This information center is powered by Eclipse technology. (http://www.eclipse.org)