Complete sample program

 /* rexx ***************************************************************/
 /*                                                                    */
 /*  SYNOPSIS                                                          */
 /*      TSSPACE - Space utilization for DB2 tablespaces               */
 /*                                                                    */
 /*  DESCRIPTION                                                       */
 /*      A report by tablespace of space allocated and number of       */
 /*      extents.                                                      */
 /*                                                                    */
 /*  USAGE NOTES                                                       */
 /*      Parms                                                         */
 /*        ssid plan tbname-prefix dbname                              */
 /*                                                                    */
 /**********************************************************************/

/* The following statement should specify the table name of the       */
 /* DASD MANAGER PLUS tablepart table                                  */

 dmtablepart = asualias('BMCASU_STABLEPART');

 title = 'DASD SPACE FOR TABLESPACE';

 /* tablepart format definition                                        */

 tpfd. = ;
 tpfd.1 =     ' @<<<<<<<<<<<<<<<<< @>>> @>>>>>>>>  @>>>';
 tpfd.2 =     ' tsobj tp.partition tp.space tp.extents';
 tpfd.3 =     ' . ';               /* end of format definition        */

 /* tablepart top of page format definition                            */

 tpfd.top.1 = ' ';
 tpfd.top.2 = ' @||||||||||||||||||||||||||||||||||| PAGE @<<<<';
 tpfd.top.3 = ' title                                rpt.page# ';
 tpfd.top.4 = ' ';
 tpfd.top.5 = ' TABLESPACE         PART     SPACE   EXT';
 tpfd.top.6 = ' ----------------- ----- ---------  ----';
 tpfd.top.7 = ' . ';               /* end of format definition        */

parse upper arg ssid plan dbnamex .; /* get input data               */

  rc = asurxsql('CONNECT' ssid plan); /* connect and open plan         */

 if (rc ¬= 0) then                   /* if connect or open plan error */
 signal caferror;                  /* go output caf error message   */

 rc = asurxrpt('INIT 60 suppress tpfd');   /* initialize report       */

 if (rc ¬= 0) then                   /* if an error                   */
 signal rpterror;                  /* go output report error msg    */

 dbnamex = translate(dbnamex, '%', '*');  /* change * to % in dbname  */
 eqorlike = '=';                      /* default to equal             */

 if (0 < pos('%', dbnamex) | ,        /* if wildcard in dbname        */
 0 < pos('_', dbnamex)) then
 eqorlike = 'LIKE';                 /* use like                     */

 /* obtain tablepart data                                              */

 rc = asurxsql('OPEN ' ,              /* open cursor                  */
 'TP CURSOR FOR SELECT    ',
 'A.DBNAME ,'              ,
 'A.TSNAME ,'              ,
 'A.PARTITION ,'           ,
 'A.SPACE , '              ,
 'A.EXTENTS   '            ,


 'WHERE A.DBNAME' eqorlike '''dbnamex'' ' ,
 '       AND TIMESTMP = (SELECT MAX(TIMESTMP) ' ,
 '          FROM ' dmtablepart 'B ',
 '          WHERE B.DBNAME = A.DBNAME ' ,
 '                AND B.TSNAME = A.TSNAME ' ,
 '                AND B.PARTITION = A.PARTITION) ' ,
 'ORDER BY DBNAME, ' ,
          '         TSNAME, ' ,
 '         PARTITION ' ,
 'FOR FETCH ONLY ');

if (rc ¬= 0) then                 /* if open failed                  */
 signal error;                   /* go output sql error message     */

 do forever                        /* loop for all tablepart rows     */
 rc = asurxsql('FETCH TP ');     /* fetch next row                  */

 if (rc ¬= 0) then               /* if fetch failed                 */
 signal sqlerror;              /* go output sql error message     */
 if (sqlcode = 100) then         /* if end of data                  */
 leave;                        /* leave forever loop              */
dbn = strip(tp.dbname, 'T');    /* remove trailing blanks          */
 tsn = strip(tp.tsname, 'T');
 tsobj = dbn'.'tsn;              /* dbname.tsname                   */
 rc = asurxrpt('WRITE tpfd');    /* output tablepart data           */
 if (rc ¬= 0) then               /* if an error                     */
 signal rpterror;              /* go output report error message  */
 end                             /* end do forever                  */

 rcx = asurxsql('CLOSE TP');       /* close cursor                    */

 signal cleanup;                   /* cleanup                         */


 /*--------------------------------------------------------------------

 rpterror - output error code and message from asurxrpt

 --------------------------------------------------------------------*/

 rpterror:

 say 'ASURXRPT return code = 'rptcode;       /* error code            */
 say rpterrm;                      /* output report error message     */
  signal cleanup;                   /* cleanup                         */

/*--------------------------------------------------------------------*/

 caferror - output caf error msg

 /*--------------------------------------------------------------------*/

 caferror:
 say 'Connect failed, rc = 'rc', cafreason = 'cafreason;
 exit 8;

/*--------------------------------------------------------------------*/

 sqlerror - output sql error codes and messages

/*--------------------------------------------------------------------*/

 sqlerror:
 say 'ASURXSQL return code = 'rc;

 if sqlcode ¬= 0 then do i = 1 to 1000 by 80
 m = substr(sqlerrm,i+1,79)
 if m = ' ' then leave
 say m
 end

 cleanup:
 rcx = asurxrpt('TERM');           /* terminate report                */
 rcx = asurxsql('DISCONNECT');     /* disconnect from db2             */
 exit rc;