Solved

PL/SQL for DB2 to re-initiate the sequences

Posted on 2013-12-20
9
813 Views
Last Modified: 2014-02-15
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.
0
Comment
Question by:Saggi
  • 3
  • 3
9 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39731485
C&P'ed in Oracle DB and it just looked fine, don't know about DB2?!?

What's the error?!
0
 

Author Comment

by:Saggi
ID: 39731494
It works in oracle but in Db2 error:

Parser Messages:
 line 1, col 1: Incorrect syntax near ''
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39731501
Could it be a problem with invisible characters or blank lines within the code?!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Saggi
ID: 39731502
Nope
0
 

Author Comment

by:Saggi
ID: 39731503
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".
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39731527
weird...
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 39731706
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now