Create Sequence

donraja_ht · #1 (**permalink**) 05-13-09, 04:45

Hello,
I want to create a sequence but before creating it i need to check if that sequence exist in the db, i have got the logic as to how to check this.
hence created a procedure in db2 which will check for the sequence and Only if it does not exists then it creates it, here is the checking sql
SELECT COUNT(*) into isSEQPresent FROM SYSCAT.SEQUENCES
where SYSCAT.SEQUENCES.SEQSCHEMA = 'user' AND
SYSCAT.SEQUENCES.SEQNAME = 'sequence_name';

IF isSEQPresent=1 THEN
BEGIN
CREATE SEQUENCE DIF_HOURS_SEQ START WITH 1 INCREMENT BY 1;
END;
END IF;

I have wrote this code in db2 but it is not compiling my procedure, can anybody tell me if this is wrong and the main part where it is giving error is after the IF condition. looks like the if condition is fine caues when i comment the create sequence statement then the procedure compiles.

rahul_s80 · #2 (**permalink**) 05-13-09, 05:09

version n sql error code

norbertogf · #3 (**permalink**) 05-13-09, 20:31

You can't execute certain DDL commands directly in routine bodies.

Instead of doing that, try using EXECUTE IMMEDIATE host-variable;

I tried here with an amended version of your code, and it worked ok:

Code:

create procedure dummyproc1()
begin atomic
	declare sqlstm varchar(500);
	declare isSEQPresent int;

	SELECT COUNT(*) into isSEQPresent 
	FROM SYSCAT.SEQUENCES
	WHERE SYSCAT.SEQUENCES.SEQSCHEMA = 'user' AND
		SYSCAT.SEQUENCES.SEQNAME = 'sequence_name';

	IF isSEQPresent>=1 THEN
		set sqlstm = 'CREATE SEQUENCE DIF_HOURS_SEQ START WITH 1 INCREMENT BY 1';
		execute immediate sqlstm;
	END IF;
end

umayer · #4 (**permalink**) 05-14-09, 01:37

But of course

IF isSEQPresent>=1 THEN the sequence already exists and the CREATE will fail.

donraja_ht · #5 (**permalink**) 05-14-09, 03:03

Thanks norbertogf that did the trick

Internet Services

Web Development

Digital Marketing

Consumer Tech