Rational Developer for System z, Version 7.6

Connecting to a DB2 database using 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. Create a local PL/I project for your source code.
  2. Link the DB2 library to your project by opening the Local Link Optionsproperty page.
  3. Type /de db2api.lib in the Link Options field.
  4. Click Local PL/I Build Options in the list on the left and provide the following information:
    Compile Options
    TEST,MACRO,OPTIONS
    SYSLIB
    installation_directory\EXTRAS\INCLUDE\PLI
    Note: Replace installation_directory with your DB2 installation directory.
  5. Select Source contains embedded SQL.
  6. Click Browse and select your DB2 connection. If you have not yet set up a connection to your database, click New Connection.
  7. Optional: Enter additional options to pass onto the SQL preprocessor in the Other SQL options field. Separate multiple SQL options with commas.
  8. Click OK.
    Note: Do not specify DB2-related options in the Compile Options field. The necessary compiler options and sub-options will automatically be generated for you from your selected DB2 connection and the options you enter in the Other SQL options field.
  9. 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 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)