Bulk Merge for Jobs with different Storage Dataset names

Document ID : KB000044197
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

The Repository for z/OS front end tool provides an online Merge utility that combines two repository instances including their associations and relations.    This tool; however, is only available online and can only be executed for one instance at a time.  We have 1000's of Jobs which need to merge.  Manually it takes a lot of time.

With regards to merging Jobs all of ours are in the same status.  The storage dataset name is different.  Earlier we were using Jobtrac and then one entry was made for the jobs and only contacts were associated to it. Later on Jobtrac was decommissioned and control M is being used for job scheduling. At this point another entry was made with a different version and relationships and associations were established.

We need to combine these two entries into one and retain all the relationships and associations and keep the qualified dataset name as control M library.

 

Answer: 

The following SQL has been developed to accomplish the merging of Jobs with jobtrac in the storage dataset name with jobs with ControlM in the storage dataset name.   The SQL takes the jobtrac jobs and merges it with the Control-M names.    The end result is that the Control-M jobs will keep their original XREF connections plus the connections that existed for the Jobtrac jobs.   Once merged, the JobTrac names are deleted from the Repository. 

Note.   This SQL is intended to run all at once in order for the bulk merge process to complete successfully

 

--    CHANGE REPOWNER TO YOUR REPOSITORY CREATOR

--    CHANGE STATUS 'DBXT' TO YOUR STATUS

 

--    CHECK IF WE HAVE DUPLICATE NAMES IN SAME CASE

   SELECT *

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

           A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET = A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE

   ;

 

--  REPORT OF DUPLICATE JOBS WITH DIFFERENT DATASET NAMES

 

    SELECT A1.JOB_NAME AS JOBTRACK_NAME,

             A1.ENT_ID AS JOBTRACK_ENT_ID,

             A1.VERSION AS JOBTRACK_VERSION,

             A2.JOB_NAME AS CTLM_NAME,

             A2.ENT_ID AS CTLM_ENT_ID,

             A2.VERSION AS CTLM_VERSION

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

           A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE

   ;

 

  DELETE  FROM REPOWNER.DBX_XREF X

    WHERE  X.ENT_ID IN

  (SELECT X3.ENT_ID

--SELECT *

    FROM REPOWNER.DBX_SYS_JOB A1,

         REPOWNER.DBX_SYS_JOB A2,

         REPOWNER.DBX_XREF X1,

         REPOWNER.DBX_XREF X2,

         REPOWNER.DBX_XREF X3,

         REPOWNER.DBX_XREF X4

    WHERE

      A1.JOB_NAME = A2.JOB_NAME

      AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

      AND A1.ENT_ID != A2.ENT_ID

      AND A1.VERSION < A2.VERSION

      AND A1.ENT_ID = X1.ENT_ID

      AND A2.ENT_ID = X2.ENT_ID

      AND A1.STATUS = 'DBXT'

      AND A1.STATUS = A2.STATUS

      AND X1.ENT_TYPE = 256

      AND X1.ENT_TYPE = X2.ENT_TYPE

      AND (X3.SOURCE_ID = X1.ENT_ID OR

           X3.TARGET_ID = X1.ENT_ID)

      AND (X4.SOURCE_ID = X2.ENT_ID OR

           X4.TARGET_ID = X2.ENT_ID)

      AND X3.ENT_TYPE = X4.ENT_TYPE)

      ;

 

-- UPDATE ALL RELATIONS/ASOCIATIONS OF CTLM JOBS ENTITIES SOURCE_ID

 

   UPDATE  REPOWNER.DBX_XREF X

      SET   X.SOURCE_ID =

   (SELECT X2.ENT_ID

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

        A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE)

   WHERE

     X.SOURCE_ID IN

   (SELECT X1.ENT_ID

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

        A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE)

   ;

 

-- UPDATE ALL RELATIONS/ASOCIATIONS OF CTLM JOBS ENTITIES TARGET_ID

 

   UPDATE  REPOWNER.DBX_XREF X

      SET   X.TARGET_ID =

   (SELECT X2.ENT_ID

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

        A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE)

   WHERE

     X.TARGET_ID IN

   (SELECT X1.ENT_ID

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

        A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE)

   ;

 

-- DELETE ENTITIES WITH JOBTRACK FROM XREF TABLE

 

   DELETE FROM REPOWNER.DBX_XREF X

   WHERE

     X.ENT_ID IN

   (SELECT X1.ENT_ID

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X1,

           REPOWNER.DBX_XREF X2

      WHERE

        A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A1.ENT_ID = X1.ENT_ID

        AND A2.ENT_ID = X2.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X1.ENT_TYPE = 256

        AND X1.ENT_TYPE = X2.ENT_TYPE)

   ;

 

-- DELETE ENTITIES WITH JOBTRACK FROM UNDERLINE TABLE

 

   DELETE FROM REPOWNER.DBX_SYS_JOB A

   WHERE

     A.ENT_ID IN

   (SELECT A1.ENT_ID

      FROM REPOWNER.DBX_SYS_JOB A1,

           REPOWNER.DBX_SYS_JOB A2,

           REPOWNER.DBX_XREF X

      WHERE

        A1.JOB_NAME = A2.JOB_NAME

        AND A1.STORAGE_DATASET != A2.STORAGE_DATASET

        AND A1.ENT_ID != A2.ENT_ID

        AND A1.VERSION < A2.VERSION

        AND A2.ENT_ID = X.ENT_ID

        AND A1.STATUS = 'DBXT'

        AND A1.STATUS = A2.STATUS

        AND X.ENT_TYPE = 256)

   ;