PL/SQL for DB2 to re-initiate the sequences

I want to drop the sequence and create a sequence in DB2, Following is the PL/SQL code I tried but showing error at DECLARE looks like some syntax error...

DECLARE
    TYPE seq_in_cur                     IS REF CURSOR;
    cur_set_seq                         seq_in_cur;
    sql_dyn                             VARCHAR2(2048);    
    v_count                             INTEGER := NULL;    
BEGIN

    sql_dyn := 'SELECT max(ID) from TL_GROUP';
    OPEN cur_set_seq FOR sql_dyn;
    FETCH cur_set_seq INTO v_count;
    v_count := v_count + 1;
    sql_dyn := 'drop sequence GROUP_SEQ';
    EXECUTE IMMEDIATE sql_dyn;
    sql_dyn := 'create sequence GROUP_SEQ start with ' || v_count || 'increment by 1 NOCACHE NOMAXVALUE NOMINVALUE';
    EXECUTE IMMEDIATE sql_dyn;
    CLOSE cur_set_seq;
END;

I had some data migration and want to re-initialize the sequences.
SaggiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex [***Alex140181***]Software DeveloperCommented:
C&P'ed in Oracle DB and it just looked fine, don't know about DB2?!?

What's the error?!
SaggiAuthor Commented:
It works in oracle but in Db2 error:

Parser Messages:
 line 1, col 1: Incorrect syntax near ''
Alex [***Alex140181***]Software DeveloperCommented:
Could it be a problem with invisible characters or blank lines within the code?!
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

SaggiAuthor Commented:
Nope
SaggiAuthor Commented:
There was other error:
Lookup Error - DB2 Database Error: ERROR [42601] [IBM][DB2/LINUXX8664] SQL0104N An unexpected token "<space>" was found following "TYPE". Expected tokens may include: "seq_in_cur".
Alex [***Alex140181***]Software DeveloperCommented:
weird...
Kent OlsenDBACommented:
Hi Saggi,

DB2 SQL is different from Oracle SQL (which is different from Microsoft SQL which is different from MySQL SQL, etc...)

In addition, IBM supports three completely different versions of DB2, each with their own code base.  One version for their mainframe, one version for the AS/400, and another version for everyone else.  The required code will change depending on which version of DB2 you're using, and the release level.

That said, the easiest way to do what you're trying to do is with the ALTER SEQUENCE statement.

  ALTER SEQUENCE {sequence_name} RESTART WITH {new_value};

If you need to rename the sequence, you may have to as you suggest and drop and recreate it.  DB2/LUW has no provision for renaming a sequence.  Note that all objects that use the original sequence become invalid.  You'll have to recreate the triggers and other objects that used the sequence.

  DROP SEQUENCE {sequence_name};
  CREATE SEQUENCE {sequence_name} START WITH {first_value};

Also, the statement:

  SELECT max(ID) from TL_GROUP

should throw an error in most releases of DB2 when executed within a procedure.  The correct syntax would be:

  SELECT max(ID) INTO v_count ...


If you're using the sequence to assign unique (incrementing) integer values to each new column in a table, I suggest that you drop the Oracle sequence/trigger style and use the IDENTITY syntax.  It's common to DB2, SQL Server, MySQL and most all engines except Oracle.


Good Luck,
Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.