Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    28

    Unanswered: Identity or Sequence in DB2 Stored Procedure

    Hi All,

    One of my table which is already existing in which a column named "Sequence" is there which has not been declared as Identity and logic was getting handled in the application code.

    Now, I need to create a Stored Procedure in DB2 which will do the same operation because we want to reduce the time consumption.

    I tried different ways but not able tp find a way to declare this column as identity in my procedure.



    I am looking for suggestions that if either it is possible?

    If possible kindly help me.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is not clear what you are trying to do in the stored procedure ? Can you post what you have already tried ?

    What version of db2 are you on ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Generally speaking, you would use the ALTER TABLE statement to do what. What alterations you can and cannot do, depend on the DB2 version, which you chose not to disclose.

  4. #4
    Join Date
    Apr 2011
    Posts
    28
    Quote Originally Posted by sathyaram_s View Post
    It is not clear what you are trying to do in the stored procedure ? Can you post what you have already tried ?

    What version of db2 are you on ?
    Hi,

    Sorry for the late reply.

    I am able to achieve, what I was looking for after following these steps on dummy tables where I am declaring my column as Identity.
    But, in my real scenario, clolumn in which I need to put values in a sequence is not declared as Identity column.

    Here I am explaining what I have done for dummy tables:

    CREATE PROCEDURE PRAP (IN YEAR CHAR(2), IN NUMBER CHAR (5))
    LANGUAGE SQL
    BEGIN

    FOR i AS state CURSOR FOR SELECT DISTINCT STATE FROM FILE2
    DO
    INSERT INTO FILE1 VALUES (YEAR,NUMBER,DEFAULT,state);
    END FOR;
    END@

    I create table FILE1 as :
    CREATE TABLE FILE1 (YEAR CHAR(2), NUMBER CHAR(5),SEQ_NBR BIGINT NOT NULL GENERATED BY DEFAULT (START WITH 1 MINVALUE 1 NO CACHE)), STATE CHAR(3);

    This is working, but when I am trying something like this on my existing table, it is not working for me.

    Kindly suggest.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    CREATE TABLE inside a stored procedure has to be done via dynamic SQL, i.e. you have to create the statement as string and then use EXECUTE IMMEDIATE to execute it dynamically.

    Remember that a procedure is usually called multiple times. So creating tables inside procedures usually doesn't make much sense. Only the first call would succeed and all subsequent ones fail because the table already exists.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

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
  •