Re: DDS CHECKING...IS this possible?

[rob@xxxxxxxxx]

Hopefully you are running V5R2!  They have JUST the field you are looking 
for!
http://makeashorterlink.com/?Q17625073
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymst79.htm

Creating and altering an identity column
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. 



Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




"Hatzenbeler, Tim" <thatzenbeler@clinitech.net>
Sent by: midrange-l-bounces@midrange.com
02/11/2003 07:35 PM
Please respond to Midrange Systems Technical Discussion
 
        To:     "'Midrange Systems Technical Discussion'" 
<midrange-l@midrange.com>
        cc: 
        Fax to: 
        Subject:        DDS CHECKING...IS this possible?


I know I can place restraints on fields in my database... 

But can I create a field called status and in that field place a value of
'1' in it...  '1' signifing your next to be processed...  but, I don't 
want
more than one row to have the value of '1' at one time...  all the other
rows must be blank or have a '2'  in it...  but only one row may have a 
'1'
in it at any given time...

I was thinking about a trigger, looking at a logical selecting '1' and
seeing if there was more than one active record, and failing the write...
but I don't even know if that would work, or if it's the best way...

thanks, tim
This e-mail message, including any attachments, is for the sole use of the
intended recipient(s) and may contain confidential or privileged
information.  Any unauthorized review, use, disclosure or distribution is
prohibited.  If you are not the intended recipient, please contact the
sender by reply e-mail and destroy the message.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.