Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Problem with DB2 Sequences object

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
a027412

New User


Joined: 05 Jul 2007
Posts: 40
Location: Minneapolis, MN

PostPosted: Wed Jul 11, 2007 4:04 pm    Post subject: Problem with DB2 Sequences object
Reply with quote

I face a problem with DB2 Sequences object.
I have created SEQUENCE as follows
Code:
(SEQ_I                             DECIMAL(15 , 0)
 GENERATED ALWAYS AS IDENTITY                     
   (START WITH                                 1 ,
    RESTART WITH                               1 ,
    INCREMENT BY                               1 ,
    CACHE                                     20 ,
    NO CYCLE                                     ,
    MINVALUE                                   1 ,
    MAXVALUE                     999999999999999 ,
    ORDER                                        )

For the first time i loaded the table with 1000 rows. Then i emptied the table and again loaded 1000 rows. Now the SEQ_I which has to start from 1, it started from 1001.
My requirement is:
1: When i continue loading record without emptying the table, the SEQ_I must be a continous one.
2: When i empty the table, the SEQ_I i should start with 1.

Please help me on this.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 13, 2007 5:32 am    Post subject:
Reply with quote

Hello,

How did you empty the table?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jul 13, 2007 7:40 am    Post subject:
Reply with quote

Have you read the manual? Emptying the table does not reset an auto generated sequence number. You need to either drop and recreate the table or reset the sequence number (I don't know if this can be done by a non-dba)
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jul 13, 2007 7:42 am    Post subject:
Reply with quote

Also, if you are using an auto generated value as your primary key in the table, that is generally seen as bad practice.

We had to re-design a 400 million row table because some one had decided auto number primary keys were the way to go and even with indexing on the "natural" primary key, the table was completely unusable even for simple queries.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts define object with CCSID = 500 Pedro DB2 0 Thu Feb 01, 2018 4:51 am
No new posts Having a problem FTP'ng to the MF pahiker All Other Mainframe Topics 2 Fri Nov 10, 2017 8:20 pm
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Problem commiting to DB2 from IMS MPP Ole Soerensen IMS DB/DC 2 Wed Jul 26, 2017 5:19 pm
No new posts Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us