BI Publisher - Migrating Scheduler Jobs and Job History ... on MSSQL

MFreitag

MFreitag

Reputable Poster
So today i stumbled over this:
You cannot migrate scheduler data from DB2 and SQL Server databases. You can migrate scheduler data only from Oracle databases.

Seriously?! Nope, not having it.
So i had a look at bip_12c_scheduler_migration.sql and it didn't seem too complicated, so i "translated" it into MSSQL/T-SQL. So if you stumble over the same issue, and happen to have your BI repository on a SQL Server, here you go.

I suggest to create a linked server to your old instance, named OLDBIP here. My databases are called BIP_REP_PD, schemas are BIPPD_BIPLATFORM - both old and new.
If you don't start fresh and had some test runs e.g. you will see primary key errors on the job history tables. You should probably truncate those then.

SQL:
-- Backups, you never know...
USE BIP_REP_PD;
SELECT * INTO BIPPD_BIPLATFORM.XMLP_SCHED_JOB_202101051_SAV FROM BIPPD_BIPLATFORM.XMLP_SCHED_JOB;
SELECT * INTO BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT_202101051_SAV FROM BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT;
SELECT * INTO BIPPD_BIPLATFORM.XMLP_SCHED_DATA_202101051_SAV FROM BIPPD_BIPLATFORM.XMLP_SCHED_DATA;
SELECT * INTO BIPPD_BIPLATFORM.XMLP_SCHED_DELIVERY_202101051_SAV FROM BIPPD_BIPLATFORM.XMLP_SCHED_DELIVERY;
SELECT * INTO BIPPD_BIPLATFORM.QRTZ_JOB_DETAILS_202101051_SAV FROM BIPPD_BIPLATFORM.QRTZ_JOB_DETAILS;
SELECT * INTO BIPPD_BIPLATFORM.QRTZ_TRIGGERS_202101051_SAV FROM BIPPD_BIPLATFORM.QRTZ_TRIGGERS;
SELECT * INTO BIPPD_BIPLATFORM.QRTZ_CRON_TRIGGERS_202101051_SAV FROM BIPPD_BIPLATFORM.QRTZ_CRON_TRIGGERS;
SELECT * INTO BIPPD_BIPLATFORM.SEQUENCE_202101051_SAV FROM BIPPD_BIPLATFORM.SEQUENCE;

--Import JOB HISTORY DATA --
INSERT INTO BIPPD_BIPLATFORM.XMLP_SCHED_JOB
    SELECT * FROM
        OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_JOB
        WHERE JOB_ID NOT IN (SELECT JOB_ID FROM BIPPD_BIPLATFORM.XMLP_SCHED_JOB)
        GO

INSERT INTO BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT
    SELECT * FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT
    WHERE JOB_ID NOT IN (SELECT JOB_ID FROM BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT)
    GO

INSERT INTO BIPPD_BIPLATFORM.XMLP_SCHED_DATA
    (DATA_ID,NEXT_DATA_ID,LAST_UPDATED,CREATED,DATA_SIZE,CHUNK_SIZE,DATA)
    SELECT DATA_ID,NEXT_DATA_ID,LAST_UPDATED,CREATED,DATA_SIZE,CHUNK_SIZE,DATA FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_DATA
    WHERE DATA_ID IN (SELECT DOCUMENT_LOCATOR FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT)
    GO

INSERT INTO BIPPD_BIPLATFORM.XMLP_SCHED_DELIVERY
    (DELIVERY_ID,OUTPUT_ID,PARENT_DELIVERY_ID,LAST_UPDATED,CREATED,STATUS,STATUS_DETAIL,DELIVERY_PARAMETERS)
    SELECT DELIVERY_ID,OUTPUT_ID,PARENT_DELIVERY_ID,LAST_UPDATED,CREATED,STATUS,STATUS_DETAIL,DELIVERY_PARAMETERS FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_DELIVERY
    WHERE OUTPUT_ID IN (SELECT OUTPUT_ID FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_OUTPUT)
    GO

--Import Schedule Jobs ---
INSERT INTO BIPPD_BIPLATFORM.QRTZ_JOB_DETAILS 
    SELECT * FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.QRTZ_JOB_DETAILS
    WHERE JOB_NAME IN (SELECT JOB_ID FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_JOB WHERE RUN_TYPE='R' AND  END_DATE >= GETDATE())
    GO

INSERT INTO BIPPD_BIPLATFORM.QRTZ_TRIGGERS
    SELECT * FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.QRTZ_TRIGGERS
        WHERE JOB_NAME IN (SELECT JOB_ID FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_JOB WHERE RUN_TYPE='R' AND  END_DATE >= GETDATE())
        GO

INSERT INTO BIPPD_BIPLATFORM.QRTZ_CRON_TRIGGERS
    SELECT * FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.QRTZ_CRON_TRIGGERS
        WHERE TRIGGER_NAME IN (SELECT JOB_ID FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.XMLP_SCHED_JOB WHERE RUN_TYPE='R' AND  END_DATE >= GETDATE())
        GO

--Update Sequence value ----
UPDATE BIPPD_BIPLATFORM.SEQUENCE
SET SEQ_COUNT = (SELECT DISTINCT(SEQ_COUNT) FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.SEQUENCE WHERE SEQ_NAME='OUTPUT_ID_SEQ')
WHERE SEQ_NAME='OUTPUT_ID_SEQ'
GO

UPDATE BIPPD_BIPLATFORM.SEQUENCE
SET SEQ_COUNT = (SELECT DISTINCT(SEQ_COUNT) FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.SEQUENCE WHERE SEQ_NAME='JOB_ID_SEQ')
WHERE SEQ_NAME='JOB_ID_SEQ'
GO

UPDATE BIPPD_BIPLATFORM.SEQUENCE
SET SEQ_COUNT = (SELECT DISTINCT(SEQ_COUNT) FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.SEQUENCE WHERE SEQ_NAME='DELIVERY_ID_SEQ')
WHERE SEQ_NAME='DELIVERY_ID_SEQ'
GO

UPDATE BIPPD_BIPLATFORM.SEQUENCE
SET SEQ_COUNT = (SELECT DISTINCT(SEQ_COUNT) FROM OLDBIP.BIP_REP_PD.BIPPD_BIPLATFORM.SEQUENCE WHERE SEQ_NAME='DATA_ID_SEQ')
WHERE SEQ_NAME='DATA_ID_SEQ'
GO

Maybe i need to clarify this? This is not supported by ORACLE in any way and neither will i take any responsibility for what you do with this ;)
 
Back
Top