ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Auto increment fields

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Re: Auto increment fields

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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment

          Working...
          X