Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2013
    Posts
    12

    Post Unanswered: Procedure sintex

    Hello I am new to db2. Could some one help me. I need to write a procedure where it will check a columnt in a table which has unique numbers and if a number is missing then it will insert a record with the missing number.

    Thank you.

    CREATE PROCEDURE SUBJECT_CREATION
    LANGUAGE SQL
    BEGIN
    DECLARE V_COUNTER SMALLINT;
    DECLARE V_FID SMALLINT;
    DECLARE c1 CURSOR FOR SELECT FID FROM INDIC.SUBJECT WHERE FID < 301;
    OPEN c1;
    LOO: LOOP
    FETCH c1 INTO v_fid ;
    IF V_COUNTER != v_fid THEN
    INSERT INTO INDIC.SUBJECT (FID, DISPLAY_SUBJ, LAST_UPDATED_DT, FILE_SINCE_DT)
    VALUES (V_COUNTER, 'Y', SYSDATE, SYSDATE);
    ITERATE c1;
    END IF;
    END FOR;
    CLOSE c1;
    END

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in the manual for the MERGE statement.

    Andy

  3. #3
    Join Date
    Mar 2013
    Posts
    12
    could not find any thing in merge. I am not merging. I found some procedure examples and tried my best to write a procedure but still getting an error.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    Andy

  5. #5
    Join Date
    Mar 2013
    Posts
    12
    DB2 = 9.7 fix pack 4,
    OS = GNU/Linux

    I am usingAqua Data Studio 4.7

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

    You can use the MERGE command to do everything for you. It will check if the row is there and insert it if is not.

    Andy

  7. #7
    Join Date
    Mar 2013
    Posts
    12
    Thank you Andy for your help.

    Here is what I got:

    MERGE INTO indic.subject
    USING (select fid from indic.subject where fid < 301)
    ON (????)
    WHEN NOT MATCHED THEN
    INSERT
    (FID, DISPLAY_SUBJ, LAST_UPDATED_DT, FILE_SINCE_DT)
    VALUES (V_COUNTER, 'Y', SYSDATE, SYSDATE);

    I do not know what I should state in the ON condition. I want to have Fid column records from 1 - 300 some of them exist and some are missing so those that are missing I want insert. Could you help.
    (I just finished my oracle sql and pl/sql course but at work we use db2 which is different. So a lot of strugling for me) Thanks again.
    Ivan.

  8. #8
    Join Date
    Mar 2013
    Posts
    12
    indic is my schema and subject is the table name

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Those examples may help you to understand DB2 ways.

    Example 1: Create a sample table and populate it.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE indic.subject
    ( fid             SMALLINT  NOT NULL UNIQUE
                                   /* or PRIMARY KEY */
    , display_subj    CHAR(1)   NOT NULL
    , last_updated_dt TIMESTAMP NOT NULL
    , file_since_dt   TIMESTAMP NOT NULL
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO indic.subject
    VALUES
      ( 1 , 'N' , current timestamp , current timestamp )
    , ( 4 , 'N' , current timestamp , current timestamp )
    , ( 5 , 'N' , current timestamp , current timestamp )
    , ( 8 , 'N' , current timestamp , current timestamp )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Example 2: insert missing fid from 1 to /*300*/ 10.
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO
     indic.subject
    ( fid , display_subj , last_updated_dt , file_since_dt )
    WITH
     gen_fid ( v_counter ) AS (
    VALUES 1
    UNION ALL
    SELECT v_counter + 1
     FROM  gen_fid
     WHERE v_counter < /*300*/ 10
    )
    SELECT v_counter
         , 'Y' , current timestamp , current timestamp
     FROM  gen_fid
     WHERE NOT EXISTS
           (SELECT 0
             FROM  indic.subject
             WHERE fid = v_counter
           )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Example 3: Display the result.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT *
     FROM  indic.subject
     ORDER BY
           fid
    ;
    ------------------------------------------------------------------------------
    
    FID    DISPLAY_SUBJ LAST_UPDATED_DT            FILE_SINCE_DT             
    ------ ------------ -------------------------- --------------------------
         1 N            2013-03-08-04.09.31.467000 2013-03-08-04.09.31.467000
         2 Y            2013-03-08-04.09.48.962000 2013-03-08-04.09.48.962000
         3 Y            2013-03-08-04.09.48.962000 2013-03-08-04.09.48.962000
         4 N            2013-03-08-04.09.31.467000 2013-03-08-04.09.31.467000
         5 N            2013-03-08-04.09.31.467000 2013-03-08-04.09.31.467000
         6 Y            2013-03-08-04.09.48.962000 2013-03-08-04.09.48.962000
         7 Y            2013-03-08-04.09.48.962000 2013-03-08-04.09.48.962000
         8 N            2013-03-08-04.09.31.467000 2013-03-08-04.09.31.467000
         9 Y            2013-03-08-04.09.48.962000 2013-03-08-04.09.48.962000
        10 Y            2013-03-08-04.09.48.962000 2013-03-08-04.09.48.962000
    
      10 record(s) selected.

  10. #10
    Join Date
    Mar 2013
    Posts
    12
    Thank you kindly. That is a big deal for me. Thanks again.

  11. #11
    Join Date
    Mar 2013
    Posts
    12
    Could you explain what doe outer select do?

    SELECT v_counter, 'Y' , current timestamp , current timestamp
    FROM gen_fid
    WHERE NOT EXISTS (SELECT 0 FROM indic.subject WHERE fid = v_counter);

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
  •