RE: Sequence Objects -- MIDRANGE-L

This is quite interesting and thanks for taking time.
Sudha

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, January 18, 2005 10:52 AM
To: Midrange Systems Technical Discussion
Subject: RE: Sequence Objects

Found some samples at 
http://64.233.167.104/search?q=cache:PlCkw2XYYIYJ:www.common.be/pdffiles/17112004DB2WhatsNew53.pdf+create+sequence+v5r3&hl=en

CREATE SEQUENCE order_seq
  START WITH 1 
 INCREMENT BY 1
 NO MAX VALUE

INSERT INTO orders(ordnum,custnum) VALUES (NEXT VALUE FOR order_seq, 123)

VALUES NEXT VALUE FOR order_seq INTO :hostvar

UPDATE orders SET ordnum = :hostvar
  WHERE custnum = 123

Now, I think I've read that a Identity column might be a better choice for 
the ordnum column.  A unique sequence would be more for a unique return 
authorization number or some such thing.  Make sense?

I have a question.  Does the NEXT VALUE always increment it?  Then does 
PREVIOUS VALUE not increment it?  Let me test.

STRSQL

CREATE SEQUENCE ROB/MY_SEQ 
START WITH 1 
INCREMENT BY 1 
NO MAXVALUE 
Sequence MY_SEQ was created in ROB.

CREATE TABLE ROB/TESTSEQ 
  (MYKEY INT AS IDENTITY NOT NULL,
   MYCHAR CHAR (15) NOT NULL, 
   RGA INT ) 

INSERT INTO ROB/TESTSEQ (MYCHAR) VALUES('TEST 1')
1 rows inserted in TESTSEQ in ROB. 
INSERT INTO ROB/TESTSEQ (MYCHAR) VALUES('TEST 2')
1 rows inserted in TESTSEQ in ROB. 

SELECT * FROM ROB/TESTSEQ
.1....+....2....+....3....+....4....+..
MYKEY   MYCHAR                     RGA 
    1   TEST 1                        -
    2   TEST 2                        -
  End of data  ******** 

SELECT MYKEY, MYCHAR, NEXT VALUE for ROB/MY_SEQ
FROM ROB/TESTSEQ 
ROB in *LIBL type *DTAARA not found. 
Ok, small bug in supporting library qualifiers perhaps??????

SELECT MYKEY, MYCHAR, NEXT VALUE for MY_SEQ
FROM ROB/TESTSEQ 
.1....+....2....+....3....+....4....+..
MYKEY   MYCHAR              NEXT VALUE 
    1   TEST 1                       1 
    2   TEST 2                       2 
  End of data  ******** 

SELECT MYKEY, MYCHAR, PREVIOUS VALUE for MY_SEQ
FROM ROB/TESTSEQ 
.1....+....2....+....3....+....4....+...
MYKEY   MYCHAR           PREVIOUS VALUE 
    1   TEST 1                        2 
    2   TEST 2                        2 
  End of data  ******** 



Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





<SRamanujan@xxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/18/2005 11:06 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: Sequence Objects






I have the same questions.  How to use it? There was something about 'Next 
value' expression.

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax


-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Tuesday, January 18, 2005 9:57 AM
To: Midrange Systems Technical Discussion
Subject: Re: Sequence Objects

I read that too.  How does one READ the sequence object from within SQL?

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Richard Theis <theis_richard@xxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/18/2005 10:48 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
midrange-l@xxxxxxxxxxxx
cc

Subject
Re: Sequence Objects










> Hi all,
> Is there any sequence objects in iSeries that can auto generate key by
> incrementing when the record is read?
> Sudha

Look at SQL statement CREATE SEQUENCE (Creates a DTAARA under the covers)
V5R3 only

Bonne réception/Best regards
Richard THEIS
  iSeries Education, France



-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.