Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us