/* REXX                                                          */
 /*                                                               */
 /* THIS REXX COPIES RUNSTATS STATISTICS FROM ONE SUBSYSTEM TO    */
 /*  ANOTHER SUBSYSTEM BY INVOKING DB2CPSTS                       */
 /*                                                               */
 /* THE REXX WILL READ INPUT FROM THE FOLLOWING FILES:            */
 /*  SCMAP    - THIS FILE WILL CONTAIN MAPPING RULES FOR THE      */
 /*      CREATOR OF OBJECTS.                                      */
 /*  OBMAP    - THIS FILE WILL CONTAIN MAPPING RULES FOR THE      */
 /*      OBJECT NAMES                                             */
 /*  DBMAP    - THIS FILE WILL CONTAIN MAPPING RULES FOR THE      */
 /*      DATABASE NAMES                                           */
 /*  SPMAP    - THIS FILE WILL CONTAIN MAPPING RULES FOR          */
 /*      TABLESPACE AND INDEXSPACE NAMES                          */
 /*                                                               */
 /*      IN THESE FILES, EACH RECORD SHOULD BE OF THE FORMAT      */
 /*      SOURCE  TARGET                                           */
 /*      BOTH CAN USE THE LIKE FORMAT                             */
 /*      THE RULES WILL BE CHECKED IN THE ORDER THEY ARE CODED    */
 /*                                                               */
 /* THE SCRIPT GETS THE FOLLOWING PARAMETERS                      */
 /*                                                               */
 /* DB2SYS - DB2 SYSTEMS WHERE THIS REXX WILL RUN                 */
 /* DB2SRC - DB2 LOCATION NAME OF THE SOURCE DB2                  */
 /* COLLID - NAME OF COLLECTION OF PACKAGE                        */
 /* PACK   - NAME OF PACKAGE                                      */
 /*                                                               */
 /*    LICENSED MATERIALS - PROPERTY OF B-ADVISED LTD             */
 /*    (C) COPYRIGHT 2009 B-ADVISED LTD.  ALL RIGHTS RESERVED.    */
 /*****************************************************************/

 PARSE ARG DB2SYS DB2SRC COLLID PACK

 TRUE  = 1
 FALSE = 0

 SAY '---------------------------------------------------------'
 SAY '-- RUNNING WITH THESE PARAMETERS: '
 SAY '-- RUNNING IN: 'DB2SYS
 SAY '-- SOURCE DB2: 'DB2SRC
 SAY '-- COLLID    : 'COLLID
 SAY '-- PACKAGE   : 'PACK
 SAY '---------------------------------------------------------'

 COLLID   = STRIP(COLLID)
 PACK     = STRIP(PACK)

 'SUBCOM DSNREXX' /* HOST CMD ENV AVAILABLE? */

 IF RC THEN /* IF NOT, MAKE IT AVAILABLE */
  S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* ADD HOST CMD ENV */

 /* CONNECT TO DB2 */
  ADDRESS DSNREXX
  'CONNECT '||DB2SYS


 SQL_STMT = "SELECT DISTINCT BQUALIFIER, BNAME         " ||,
     " FROM   SYSIBM.SYSPACKDEP                        " ||,
     " WHERE  DLOCATION=''                             " ||,
     " AND    DCOLLID  = '"COLLID"'                    " ||,
     " AND    DNAME    = '"PACK"'                      " ||,
     " AND    BTYPE    = 'R' WITH UR"

 "EXECSQL DECLARE C1 CURSOR FOR S1"
 IF SQLCODE <> 0 THEN
    DO
       SAY "DECLARE CURSOR FAILED "
       CALL SQLCA
    END

 "EXECSQL PREPARE S1 FROM :SQL_STMT"
 IF SQLCODE <> 0 THEN
    DO
       SAY "PREPARE CURSOR FAILED FOR SQL: "SQL_STMT
       CALL SQLCA
    END

 "EXECSQL OPEN C1"
 IF SQLCODE <> 0 THEN
    DO
       SAY "OPEN CURSOR FAILED FOR SYSIBM.SYSTABLES"
       CALL SQLCA
    END

 "EXECSQL FETCH C1 INTO :DBNAME, :TSNAME"

 IF SQLCODE <> 0 & SQLCODE <> 100 THEN
    DO
       SAY "FETCH CURSOR FAILED FOR SYSIBM.SYSTABLES"
       CALL SQLCA
    END

 OBJCOUNT = 0

 DO WHILE (SQLCODE = 0)

    OBJCOUNT         = OBJCOUNT + 1
    DBNAMES.OBJCOUNT = OVERLAY(STRIP(DBNAME),'        ')
    TSNAMES.OBJCOUNT = OVERLAY(STRIP(TSNAME),'        ')

    "EXECSQL FETCH C1 INTO :DBNAME, :TSNAME"

    IF SQLCODE <> 0 & SQLCODE <> 100 THEN
       DO
          SAY "FETCH CURSOR FAILED FOR SYSIBM.SYSPACKDEP"
          CALL SQLCA
       END
 END

 "EXECSQL CLOSE C1"
 "EXECSQL DISCONNECT"

 /* CALL THE OTHER REXX HERE */
 DO I = 1 TO OBJCOUNT

    /* CALL REXX THAT DEVELOP COPY STATISTICS STATEMENTS */
    SAY "*** CALLING DB2CPSTS FOR "DBNAMES.I"."TSNAMES.I
    CALL DB2CPSTS(DB2SYS" "DB2SRC" "DBNAMES.I" "TSNAMES.I)
    SAY "***         DB2CPSTS FOR "DBNAMES.I"."TSNAMES.I" DONE"

 END

 SAY '---------------------------------------------------------'
 SAY '-- DONE '
 SAY '---------------------------------------------------------'

 EXIT

 /***************************************************************/
 /* DISPLAY SQLCA */
 /***************************************************************/
 SQLCA:
  SAY "SQLSTATE= "SQLSTATE
  SAY "SQLWARN = "SQLWARN.0",",
              || SQLWARN.1",",
              || SQLWARN.2",",
              || SQLWARN.3",",
              || SQLWARN.4",",
              || SQLWARN.5",",
              || SQLWARN.6",",
              || SQLWARN.7",",
              || SQLWARN.8",",
              || SQLWARN.9",",
              || SQLWARN.10""

  SAY "SQLERRD = "SQLERRD.1",",
              || SQLERRD.2",",
              || SQLERRD.3",",
              || SQLERRD.4",",
              || SQLERRD.5",",
              || SQLERRD.6""

  SAY "SQLERRP  = "SQLERRP
  SAY "SQLERRMC = "SQLERRMC
  SAY "SQLCODE  = "SQLCODE

  EXIT(8)