Results 1 to 5 of 5

Thread: Auto increment fields

  1. #1
    Driver of cars, eater of food jamief's Avatar
    Join Date
    Jan 2004
    Location
    Belvidere, IL - United States of America
    Age
    49
    Posts
    9,416
    Rep Power
    12599

    Auto increment fields

    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
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Age
    21
    Posts
    1,000,000
     

  3. #2
    Analyst dhuesman's Avatar
    Join Date
    Aug 2005
    Location
    KY
    Posts
    279
    Rep Power
    1081

    Re: Auto increment fields

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

  4. #3
    Driver of cars, eater of food jamief's Avatar
    Join Date
    Jan 2004
    Location
    Belvidere, IL - United States of America
    Age
    49
    Posts
    9,416
    Rep Power
    12599

    Re: Auto increment fields

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

    PHP Code:
    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.

    PHP Code:
    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:

    PHP Code:
    ALTER TABLE ORDER         
      ALTER COLUMN ORDERNO         
      RESTART WITH 1 
    You can also drop the identity attribute from a column:

    PHP Code:
    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:

    PHP Code:
    CREATE TABLE ORDERS
       
    (ORDERNO ROWID
       GENERATED ALWAYS
    ,
       
    SHIPPED_TO VARCHAR (36) ,
      
    ORDER_DATE DATE 
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  5. #4
    Ex - Solutions Architect kpmac's Avatar
    Join Date
    Aug 2005
    Location
    NJ
    Age
    36
    Posts
    454
    Rep Power
    2424

    Talking Re: Auto increment fields

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

    PHP Code:
    CREATE TABLE
       MYLIB
    .TEST 
      
    (ENTDATE TIMESTAMP ,
      
    TEST CHAR 30NOT  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  ******** 
    Predictions are usually difficult, especially about the future. ~Yogi Berra

    Vertical Software Systems
    VSS.biz

  6. #5
    Analyst
    Join Date
    Sep 2005
    Location
    Germany
    Posts
    1,575
    Rep Power
    9360

    Re: Auto increment fields

    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

  7. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. query builds index?
    By GLS400 in forum AS400 REPORTS
    Replies: 8
    Last Post: January 26th, 2007, 02:23 PM
  2. RLU ---entering fields
    By praxceda in forum AS400 REPORTS
    Replies: 11
    Last Post: September 26th, 2006, 01:30 PM
  3. Replies: 4
    Last Post: May 17th, 2006, 09:11 AM
  4. Multiple fields at a time in DSPF
    By ranjini in forum RPG/RPGLE
    Replies: 3
    Last Post: December 2nd, 2005, 07:50 AM
  5. Null Values and SQL Fetch
    By pjk in forum SQL
    Replies: 1
    Last Post: February 22nd, 2005, 11:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •