RE: SQL Entity Key Question

What the heck is an entity key?

The iSeries supports two auto-number columns, know as IDENTITY and
SEQUENCE.

The IDENTITY column is unique to the table, a SEQUENCE can be shared
among multiple tables.

IDENTITY is implemented internally in the *FILE object.  

SEQUENCE is implemented externally to the *FILE as a *DTAARA.

Both IDENTITY and SEQUENCE have an attribute named "cache".  The default
value of cache is 20.  When a job requests a number, the DBMS will
pre-allocate and keep in memory the number of values specified by cache.
This improves the performance of the job when requesting multiple
values.  However, if that job doesn't use all the pre-allocated values
the values are lost.

In addition, both IDENTITY and SEQUENCE have an attribute named
"increment by" which specifies the interval between consecutives values
in the IDENTITY or SEQUENCE.  The default for increment by is 1.


All of this info is in the SQL Reference manual.  Take a look at the
CREATE TABLE and CREATE SEQUENCE along with the ALTER TABLE and ALTER
SEQUENCE statements.

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of directsystemsinc02
> Sent: Thursday, January 19, 2006 8:54 AM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: SQL Entity Key Question
> 
> When you specify that entity keys are to be used, the system will
> automatically establish them. When you have more than one table in the
> schema it generates numbers that start at various points in 
> the 9.0 range
> and then increment for each row added to the table. The 
> question I asked was
> what controls the spread of these starting points.
> 
> Jack Derham
> Direct Systems, Inc.
> 
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> On Behalf Of Wilt, Charles
> Sent: Thursday, January 19, 2006 8:38 AM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: SQL Entity Key Question
> 
> Jack,
> 
> Don't understand the questions.
> 
> What "set of numbers" are you talking about?
> 
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>   
> 
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx 
> > [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of 
> directsystemsinc02
> > Sent: Thursday, January 19, 2006 5:57 AM
> > To: RPG programming on the AS400 / iSeries
> > Subject: SQL Entity Key Question
> > 
> > To Our SQL Gurus,
> > 
> >  
> > 
> > Am building an SQL Database with multiple tables that are 
> > interconnected
> > with entity keys. Have some question relative to these rascals:
> > 
> >  
> > 
> > 1.                   How does the system decide which set of 
> > numbers will be
> > associated with each table of the database.
> > 
> > 2.                   Where is this information stored in the system
> > 
> > 3.                   If we have to recover the database what 
> > will happen to
> > these values and will the inter-table relationships be recovered.
> > 
> >  
> > 
> > That should be enough to start a conversation going but as always
> > information sources would be appreciated.
> > 
> >  
> > 
> > Jack Derham
> > 
> > Direct Systems, Inc.
> > 
> > -- 
> > This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> > mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.
> > 
> > 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>