Using the Relational Data Framework › Cursor Operations › OPS/REXX Program That Demonstrates Cursor Operations

OPS/REXX Program That Demonstrates Cursor Operations

This sample program demonstrates the use of cursor operations:

/*------------------------------------------------------*/
/* Declare a cursor that will select 3 columns from any */
/* "broken" (CURRENT <> DESIRED) DB2 regions.           */
/*------------------------------------------------------*/
ADDRESS SQL 'DECLARE CSR1 CURSOR FOR',
  'SELECT NAME CURRENT_STATE DESIRED_STATE FROM STCTAB',
  "WHERE CURRENT_STATE <> DESIRED_STATE AND TYPE = 'DB2'"
IF sqlcode <> 0 THEN CALL SQLERROR /* Check return code!*/
ADDRESS SQL 'OPEN CSR1'    /* SQL will now get the data */
IF sqlcode = 100 THEN      /* Were any rows selected?   */
  DO                       /* No, issue msg & exit      */
    SAY 'All resources in table are at desired state'
    EXIT 0
  END
IF sqlcode <> 0 THEN CALL SQLERROR /* Check return code!*/
/*------------------------------------------------------*/
/* Fetch each selected row, one row at a time.          */
/*------------------------------------------------------*/
DO WHILE SQLCODE <> 100
  ADDRESS SQL 'FETCH CSR1 INTO :NAME, :CURRENT, :DESIRED'
  IF sqlcode = 100 THEN LEAVE      /* No more rows, done*/
  IF sqlcode <> 0 THEN CALL SQLERROR /* Check ret code! */
  /* Note that name.1, is returned, not just "name".    */
  SAY 'NAME='name.1',',
      'CURRENT='current.1', DESIRED='desired.1
  /*----------------------------------------------------*/
  /* If CURRENT_STATE is neither UP nor DOWN, update    */
  /* this row in the table to set it to UNKNOWN.        */
  /*----------------------------------------------------*/
  IF WORDPOS(current.1,'UP DOWN') = 0 THEN
    DO
      /* Set host variable.  This must be a simple      */
      /* variable (CURRENT), not a stem (like CURRENT.1)*/
      current = 'UNKNOWN'
      ADDRESS SQL 'UPDATE STCTAB',     /* Perform update*/
        'SET CURRENT_STATE = :current',
        'WHERE CURRENT OF CSR1'
      IF sqlcode <> 0 THEN
        CALL SQLERROR                  /* Check ret code*/
    END
END
ADDRESS SQL 'CLOSE CSR1' /* Be sure to close cursor     */
EXIT 0
/* Subroutine to display diagnostic data and exit       */
SQLERROR:
PARSE SOURCE . . pgm .
SAY 'SQL error in program 'pgm' called from line 'sigl
SAY 'RC='rc', SQLCODE='sqlcode
ADDRESS SQL 'CLOSE CSR1' /* Be sure to close cursor!    */
EXIT 12