Results 1 to 5 of 5

Thread: Create Sequence

  1. #1
    Join Date
    Sep 2007
    Posts
    56

    Unanswered: Create Sequence

    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.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    version n sql error code
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Oct 2003
    Location
    Curitiba - PR - Brazil
    Posts
    18
    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
    Norberto Gasparotto Filho

    IBM Cert. DB2 v9 DBA
    IBM Cert. DB2 v9 App. Developer
    SCJP 5

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    But of course

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

  5. #5
    Join Date
    Sep 2007
    Posts
    56
    Thanks norbertogf that did the trick

Posting Permissions

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