Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    5

    Unanswered: Need help with this stored procedure

    I am stuck...I cannot get this to work!

    The first bit works, where is checks for the null start date and then inserts a record into the table. But when running it again to update the table, nothing happens.

    I am trying to find some good examples of DB2 procedures that do inserts and updates of tables, but haven't found any.

    I am not a PL/SQL expert or anything and DB2 is new to me. Any help on getting this procedure to work as I want, would really make my day!

    thanks,
    aposullivan

    CREATE OR REPLACE PROCEDURE DS_SP_JOB_LOG_SUMMARY (IN RUN_ID INTEGER,IN R_DATE DATE, IN S_DATE TIMESTAMP, IN E_DATE TIMESTAMP,IN STATUS VARCHAR(50))
    LANGUAGE SQL
    BEGIN

    IF (S_DATE IS NOT NULL) THEN
    INSERT INTO DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY VALUES (RUN_ID,R_DATE,S_DATE,E_DATE,STATUS);

    END IF;
    --Update Staging Job
    IF (S_DATE IS NULL AND STATUS = 'SUCCESS_EXTRACT') THEN

    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET END_DATE = E_DATE
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_EXTRACT'
    ;

    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET JOB_STATUS = STATUS
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_EXTRACT'
    ;
    END IF;
    -- Update Transform Job
    IF (S_DATE IS NULL AND STATUS = 'SUCCESS_TRANSFORM') THEN

    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET END_DATE = E_DATE
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_TRANSFORM'
    ;

    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET JOB_STATUS = STATUS
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_TRANSFORM'
    ;
    END IF;
    END

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Always post your DB2-server version and fixpack, and operating-system name and version, when you ask for help.

    You can find good examples of SQL PL procedures on your DB2 server, if you chose default options at installation.

    If you are on Windows, the samples are in \program files\ibm\sqllib\samples\sqlpl.

    If you are on a Unix type platform the samples are in
    $INSTHOME\sqllib\samples\sqlpl

    Your problem is probably caused because you have no error checking in your procedure. Please study the IBM samples to read about adding EXCEPTION handlers for errors.

    You can also benefit by learning how to debug your stored procedures with a GUI. The Data Studio 3.1.1 has a stored procedure debugger, that is a free download.

    If you don't like GUIs, then You can also add conditional-compilation and trace/debug statements to the procedure (for example to write a message when something happens) using the DBMS_OUTPUT.PUT_LINE, depending on your DB2 version.

  3. #3
    Join Date
    Aug 2012
    Posts
    5

    Talking

    Hi ,

    I have had a look at the DB2 examples, I've used Data Studio client tool and they haven't helped me resolve the issue with my stored procedure. There aren't any examples of how you would perform an insert and then update to a table when passing in values.

    The procedure below works fine in a SQL Server database, but when attempting to do the same in DB2 (DB2 9.7 FP5), it isn't behaving as I thought it would.

    The first if statement, will perform an insert if the condition is true - this works.

    if it isn't true, then it should move onto the next if statement and perform an update. But nothing is happening.

    I'm pretty sure when I call my procedure I'm passing in the correct values for the different if conditions.

    Can't say I am very impressed with developing in DB2 compared with SQL Server and Oracle either. More hard work than it needs to be.

    Anyway, if anyone can see what's wrong with my code, it would make my day.
    In the meantime, I'll put some error logging into the procedure and see what happens.

    I must add that I have tried just running the procedure like this:
    CREATE OR REPLACE PROCEDURE DS_SP_JOB_LOG_SUMMARY (IN RUN_ID INTEGER,IN R_DATE DATE, IN S_DATE TIMESTAMP, IN E_DATE TIMESTAMP,IN STATUS VARCHAR(50))
    LANGUAGE SQL
    BEGIN
    IF (S_DATE IS NULL AND STATUS = 'SUCCESS_EXTRACT') THEN

    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET END_DATE = E_DATE
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_EXTRACT'
    ;

    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET JOB_STATUS = STATUS
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_EXTRACT'
    ;
    END IF;
    END

    Then calling it with the following:
    CALL DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY_TEST(58,'11/09/2012',null,'11/09/2012','SUCCESS_EXTRACT');

    Nothing happens. The table does not get updated with data.

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    DB2 is new to me
    Can't say I am very impressed with developing in DB2 compared with SQL Server and Oracle either. More hard work than it needs to be.
    get rid of the attitude
    and things may come a little easier
    if they do not have to first overcome your bias.
    Dick Brenholtz, Ami in Deutschland

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by aposullivan View Post
    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET END_DATE = E_DATE
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_EXTRACT'
    The first part of this WHERE condition will always be true (unless RUN_ID is NULL)...
    Also, verify that your table has a column called STATUS, since you won't get a syntax error if it isn't. (Or move the condition out of the UPDATE statement if STATUS is a parameter!)
    I'd suggest to prefix all your parameter names with "P_", to avoid confusion between variables (or parameters) and column names.
    (B.t.w., DB2 will not be confused since column names take precedence over variable names; but you or any other reader of the statement will be!)
    Quote Originally Posted by aposullivan View Post
    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY
    SET JOB_STATUS = STATUS
    WHERE RUN_ID = RUN_ID AND STATUS = 'START_EXTRACT'
    Better combine both UPDATE statements into a single one; better for readability, for maintainability, and for performance:
    Code:
    UPDATE DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY 
    SET END_DATE = P_E_DATE, JOB_STATUS = STATUS
    WHERE RUN_ID = P_RUN_ID AND STATUS = 'START_EXTRACT'
    B.t.w., are you sure you want to set the JOB_STATUS column to the STATUS column? Or should it be the P_STATUS parameter?

    Quote Originally Posted by aposullivan View Post
    CALL DS_JOB_REGISTER.DS_JOB_LOG_SUMMARY_TEST(58,'11/09/2012',null,'11/09/2012','SUCCESS_EXTRACT');
    Are you sure you want to pass it November 9, and not September 11 (which should be written as either '2012-09-11' or '11.09.2012')?
    Last edited by Peter.Vanroose; 09-11-12 at 05:54.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Don't assume that if the code runs on SQL-Server then it must run on DB2 also without change. That would be a silly assumption, possibly born of inexperience.


    You have likely incorrect assumptions in your code, your challenge is to solve it.

    For example, assumptions about a date value format, assumptions about how DB2 might distinguish between a column name and a parameter name.


    Unless you debug it (and verify your assuptions), and add exception handlers, you are unlikely to progress.

Tags for this Thread

Posting Permissions

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