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.

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 ?

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.

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.

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.