Another way to generate unique values is to use the scalar function
GENERATE_UNIQUE. Each successive value is greater than the previous one. The
current timestamp (UTC) is included in the result of the GENERATE_UNIQUE
function. But contrary to the special register CURRENT_TIMESTAMP,
GENERATE_UNIQUE generates unique values, even if there are several entries
in the same micro second.
GENERATE_UNIQUE function returns a bit data character string 13 bytes long
--> (CHAR(13) FOR BIT DATA)
Example:
CREATE TABLE MySchema/MyTable
(MyUnqCol Char(13) for Bit Data,
... other Columns);
Insert into MySchema/MyTable
Values (Generate_Unique(), ....);
Because the current_timestamp is included in the result of the
GENERATE_UNIQUE, it can be resolved by using the SQL Scalar Function
TIMESTAMP()
Select Timestamp(MyUnqCol), ...
From MySchema/MyTable
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Mark Murphy/STAR BASE Consulting Inc.
Gesendet: Monday, 12. September 2011 23:29
An: rpg400-l@xxxxxxxxxxxx
Betreff: RE: SQL Insert Row as last RRN
If you use a SEQUENCE rather than an identity column you can cache a number
of values to improve performance, and you can also easily reset the numbers
on your own schedule without having to jump through the hoops required of an
identity column.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----rpg400-l-bounces@xxxxxxxxxxxx wrote: -----
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
From: "Morgan, Paul"
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
Date: 09/12/2011 03:09PM
Subject: RE: SQL Insert Row as last RRN
Michael,
IDENTITY column requires maintaining a sequence in the database with some
minor performance issues especially with multiple processes using the same
table. The column sequence could require a reset. It will eventually wrap
around to a lower sequence and throw off your ordering when it does wrap.
It's a similar problem you are having with the RRN.
Is this your interface to the CS 1500? Likely you don't require 24/7 so
having to shut down this single process to occasionally reset is probably
not a problem.
Hope this was helpful,
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 2:23 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
Kevin, I'd not played with the IDENTITY Column before. I just ran some
tests and found it to be a pretty good solution. It didn't seem to re-use
values from rows I'd just deleted either. Unless someone knows of any
shortcomings of that, I think I'll add that to my CREATE TABLE, then do the
CHGPF trick to so new rows get added to the bottom, and ensure that all my
selects are ORDERED BY the identity column. Perhaps a tiny bit more effort
than I initially planned, but not too much, and for as critical as sequence
is in this case, it gives me more confidence than RRN.
Thanks for that.
And thanks to everyone else who helped me be not as dumb as I was earlier
today :)
-- Michael K
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kevin Bucknum
Sent: Monday, September 12, 2011 1:47 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
Instead of a timestamp could you use a IDENTITY column?
Kevin B
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Koester, Michael
Sent: Monday, September 12, 2011 12:05 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Insert Row as last RRN
In testing the timestamp idea from interactive sql, I'm showing no
duplicate timestamps (yet), but most are within 1 or 2 microseconds of
the previous row. Who said sql has performance issues?
It looks like I may be best off with an integer that I have to
hand-manage. The template file does have a sequence field, which may be
useful, but did I mention that the whole set of commands passed to the
switch includes commands from a dozen or so templates? Nothing is ever
easy, and the RRN sure looked good, if we could only get SQL to respect
it.
Back to the question I posed earlier: If I did not go the added column
route, would I be safe in changing the file to REUSEDLT(*NO), and adding
ORDER BY RRN(myfile) to the sql retrieving the rows to process?
-- Michael K
Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550
midrange.com
