Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2013
    Posts
    4

    Unanswered: Need help converting Oracle triggers to DB2

    Hello everyone. New to the forum and hoping someone can help. I am in the process of moving our database from Oracle 11g to DB2 and am having some trouble converting a few triggers. Any help provided would sure help me along my way to learn DB2.

    Thanks in advance......

    Mike

    Trigger #1 (Oracle syntax)
    ______________________

    create or replace
    TRIGGER "MAXIMO"."LR_COMMSTAT_T"
    BEFORE INSERT OR UPDATE ON MAXIMO.WORKORDER
    FOR EACH ROW
    WHEN (NEW.STATUS = 'APPR' AND NEW.CPSTATUS IS NULL AND NEW.SYSTEM IS NOT NULL) DECLARE
    BEGIN
    :NEW.CPSTATUS := 'PENDING';
    END;

    Trigger #2 (Oracle syntax)
    ______________________

    create or replace
    TRIGGER "MAXIMO"."LR_COMMSETREVDATES_T"
    BEFORE INSERT OR UPDATE ON MAXIMO.WORKORDER
    FOR EACH ROW
    DECLARE
    BEGIN

    IF (:OLD.REV1RETURNED = 0 AND :NEW.REV1RETURNED = 1 AND :NEW.REV1 = 1 AND :NEW.REV1ENDDATE IS NOT NULL AND :NEW.REV1START IS NOT NULL) THEN
    :NEW.REV1ENDDATE := SYSDATE;
    ELSE IF (:NEW.REV1 = 1 AND :NEW.REV1ENDDATE IS NULL) THEN
    :NEW.REV1START := SYSDATE;
    :NEW.REV1ENDDATE := SYSDATE + 10;

    END IF;
    END IF;

    IF (:OLD.REV2RETURNED = 0 AND :NEW.REV2RETURNED = 1 AND :NEW.REV2 = 1 AND :NEW.REV2ENDDATE IS NOT NULL AND :NEW.REV2STARTDATE IS NOT NULL) THEN
    :NEW.REV2ENDDATE := SYSDATE;
    ELSE IF (:NEW.REV2 = 1 AND :NEW.REV2ENDDATE IS NULL) THEN
    :NEW.REV2STARTDATE := SYSDATE;
    :NEW.REV2ENDDATE := SYSDATE + 10;

    END IF;
    END IF;

    IF (:OLD.REV3RETURNED = 0 AND :NEW.REV3RETURNED = 1 AND :NEW.REV3 = 1 AND :NEW.REV3ENDDATE IS NOT NULL AND :NEW.REV3STARTDATE IS NOT NULL) THEN
    :NEW.REV3ENDDATE := SYSDATE;
    ELSE IF (:NEW.REV3 = 1 AND :NEW.REV3ENDDATE IS NULL) THEN
    :NEW.REV3STARTDATE := SYSDATE;
    :NEW.REV3ENDDATE := SYSDATE + 10;

    END IF;
    END IF;

    IF (:OLD.REV4RETURNED = 0 AND :NEW.REV4RETURNED = 1 AND :NEW.REV4 = 1 AND :NEW.REV4ENDDATE IS NOT NULL AND :NEW.REV4STARTDATE IS NOT NULL) THEN
    :NEW.REV4ENDDATE := SYSDATE;
    ELSE IF (:NEW.REV4 = 1 AND :NEW.REV4ENDDATE IS NULL) THEN
    :NEW.REV4STARTDATE := SYSDATE;
    :NEW.REV4ENDDATE := SYSDATE + 10;

    END IF;
    END IF;

    IF (:OLD.REV5RETURNED = 0 AND :NEW.REV5RETURNED = 1 AND :NEW.REV5 = 1 AND :NEW.REV5ENDDATE IS NOT NULL AND :NEW.REV5STARTDATE IS NOT NULL) THEN
    :NEW.REV5ENDDATE := SYSDATE;
    ELSE IF (:NEW.REV5 = 1 AND :NEW.REV5ENDDATE IS NULL) THEN
    :NEW.REV5STARTDATE := SYSDATE;
    :NEW.REV5ENDDATE := SYSDATE + 10;

    END IF;
    END IF;
    END;

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all,
    did you specified(or used?) Oracle compatibility feature of DB2?
    if not, it would be worth to study basics of differences DB2 from Oracle.

    There are some articles about Oracle compatibility of DB2, like...
    DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows
    DB2 fundamentals for Oracle professionals: Introduction to DB2
    DB2 Viper 2 compatibility features

    and more in Technical library in developerWorks
    IBM developerWorks : Information Management : Technical library
    ...

  3. #3
    Join Date
    Dec 2013
    Posts
    4
    Thank you for your response. I have briefly read through those articles and do not see anything specific about how to convert my two triggers. Any help you can provide with this conversion will allow me to move forward as I continue to read and learn about DB2.

    For now if i could get some help on these two things I would be able to test and roll out this portion of our Maximo applications.

    Many thanks,

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    With Oracle compatibility enabled, your PL/SQL triggers should work with minimal or no changes.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Aug 2012
    Posts
    23
    Hi,

    We are using DB2 9.7 on Linux 5.5 version.Actually we have some oracle scripts around 40 which all starts with @ the same we have to execute into the DB2 environment all at a time.could you please suggest me what is the command we have to use for the same.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    @raveendra: I think you should start a different thread, as your problem is different.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •