Auto increment fields

[jamief]

Can someone show me an example of an auto increment field in sql when creating a table? Also if possible an auto timestamp?

Thanks
Jamie

[dhuesman]

For Timestamp could you use something like CurTime()? for AutoIncrement could you use Integer(somefld + 1)?

[jamief]

Here is example of auto increment field Im looking for auto time stamp

create table jamielib/customer
 (cust# smallint not null     
   generated always as identity
   (start with 1              
    increment by 1            
    cycle),                   
  shipped_to varchar (5),     
  name  varchar (50),         
address varchar (40),         
city varchar(18),             
state char (2),               
 constraint customer_cust#    
  Primary key (Cust#)) 

a bit of IBM talk

Every time that a new row is added to a table with an identity
column, the identity column value in the new row is incremented
(or decremented) by the system. Only columns of type SMALLINT,
INTEGER, BIGINT, DECIMAL, or NUMERIC can be created as identity
columns. You are allowed only one identity column per table.
When you are changing a table definition, only a column that
you are adding can be specified as an identity column;
existing columns cannot.

When you create a table, you can define a column in the table
to be an identity column. For example, create a table ORDERS
with 3 columns called ORDERNO, SHIPPED_TO, and ORDER_DATE.
Define ORDERNO as an identity column.

CREATE TABLE ORDERS
   (ORDERNO SMALLINT NOT NULL
   GENERATED ALWAYS AS IDENTITY
   (START WITH 500 
   INCREMENT BY 1 
   CYCLE),
   SHIPPED_TO VARCHAR (36) ,
   ORDER_DATE DATE) 

This column is defined with starting value of 500,
incremented by 1 for every new row inserted, and will
recycle when the maximum value is reached. In this example,
the maximum value for the identity column is the maximum value
for the data type. Because the data type is defined as SMALLINT,
the range of values that can be assigned to
ORDERNO is from 500 to 32767. When this column value reaches 32767,
it will restart at 500 again. If 500 is still assigned to a
column, and a unique key is specified on the identity column,
then a duplicate key error is returned. The next insert will
attempt to use 501. If you do not have a unique key specified
for the identity column, 500 is used again, regardless of how
many times it appears in the table.

For a larger range of values, you could specify the column to be
an INTEGER or even a BIGINT. If you wanted the value of the
identity column to decrease, you would specify a negative
value for the INCREMENT option. It is also possible to
specify the exact range of numbers by using MINVALUE and MAXVALUE.

You can modify the attributes of an existing identity column
using the ALTER TABLE statement. For example, if you wanted to
restart the identity column with a new value:

ALTER TABLE ORDER         
  ALTER COLUMN ORDERNO         
  RESTART WITH 1 

You can also drop the identity attribute from a column:

ALTER TABLE ORDER         
  ALTER COLUMN ORDERNO        
  DROP IDENTITY 

The column ORDERNO remains as a SMALLINT column, but the identity
attribute is dropped. The system will no longer generate
values for this column.

--also-- Date is filled with current date!

ROWID
Using ROWID is another way to have the system assign a unique value
to a column in a table. ROWID is similar to indentity columns, but
rather than being an attribute of a numeric column, it is a separate
data type. To create a table similar to the identity column example:

CREATE TABLE ORDERS
   (ORDERNO ROWID
   GENERATED ALWAYS,
   SHIPPED_TO VARCHAR (36) ,
  ORDER_DATE DATE ) 

[kpmac]

You can try using a trigger on the table that updates a timestamp field with the current_timestamp whenever an insert occurrs.

CREATE TABLE
   MYLIB.TEST 
  (ENTDATE TIMESTAMP ,
  TEST CHAR ( 30) NOT  NULL WITH DEFAULT)                                                  


CREATE TRIGGER MYLIB.INSERT_DATE                                  
  BEFORE INSERT ON MYLIB.TEST                                     
  REFERENCING NEW  as NEWROW                                        
    FOR EACH ROW                                                    
BEGIN                                                               
   SET NEWROW.ENTDATE = CURRENT_TIMESTAMP;                          
END                                     

INSERT INTO MYLIB.TEST (TEST)  VALUES('AAAAAA')
INSERT INTO MYLIB.TEST (TEST)  VALUES('BBBBBB')
INSERT INTO MYLIB.TEST (TEST)  VALUES('CCCCC') 



SELECT * FROM MYLIB.TEST

ENTDATE                     TEST   
2006-04-24-22.11.06.404966  AAAAA  
2006-04-24-22.11.12.169658  BBBBB  
2006-04-24-22.11.17.439606  CCCCC  
********  End of data  ******** 

[B.Hauser]

Hi,

You can try using a trigger on the table that updates a timestamp field with the current_timestamp whenever an insert occurrs.

Two Remarks:
1. There is no guarantee that an timestamp value will be really unique! The faster the machines are, the greater is the chance, that several rows get written in the same microsecond. New technologies, like Concurrent Write Support, where first the adresses for the new requested rows are returned before writing physically will force paralell writes.

2. The implementation of a trigger program is always a dynamic call. That means a lot of overhead. Using an identity column or a ROWID column instead will not cause this overhead, and it gets performed on the database level (on almost the lowest level).

For performance issues identity columns or rowid should be prefered.

Birgitta