Hi,
RRN is available, but there are several problems:
1. How to determine the last inserted relative record no within the current
level (that's what IDENTITY_VAL_LOCAL is for).
It is possible to retrieve the highest record no, but what if another row
or even several rows are inserted by another job
between your insert and the execution of the statement that retrieves the
highest record no?
Or if the file is created with the option REUSEDLT *YES, where deleted
records get replaced before adding new records at the end of the file?
(and the WEB people will not use native I/O!!!)
2. When using the relative record no to access rows with SQL a table scan or
at least in a table probe will be performed.
Both methods will result in a bad performance for large files or tables.
Even in release 6.1 it is not possible to create an index over the
relative record no.
Identity columns can only be implemented in SQL tables.
If it is not possible to convert your DDS described tables into a SQL Table,
you may consider the following solution:
- Add an additional (numeric!?) column to your DDS described table.
- Add a before insert trigger. Determine the next (running) number for this
column.
If you are on release V5R3 or higher, you can create an SQL Sequence
object and retrieve the next running number with the SQL command NEXT VALUE
FOR yourSequence.
If you are not yet on release V5R3 you may simulate a Sequence Object,
i.e. create a data area that always holds the highest value.
In your trigger program you read the data area add 1 to the highest number
save the data area and write the newly determined highest number into your
record/row.
- create a keyed logical file with unique key over this new column.
In this way you do not have to change your existing programs (except you
have to recompile them, because you added a column/field).
A single column unique key is added and SQL can use Index Access to retrieve
the record/row, which is very fast.
We had a similar problems with our web application. A record can only be
identified by a single column/field. For compound keys we either need to
concatenate them (and resplit them later) or enhance our files with a column
that holds an "artificial" unique key.
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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Brian Johnson
Gesendet: Tuesday, 02. February 2010 00:12
An: 'Midrange Systems Technical Discussion'
Betreff: RE: IDENTITY_VAL_LOCAL
No sure why someone thinks they need RRN to insert. However RRN is
available for SQL queries:
select rrn(mytable), mytable.* from mytable ...
No need to add additional fields, and works with physical files created from
DDS. The RRN result column could be used in subsequent update statements,
eliminating the need to know the primary key (perhaps one does not even
exist) for the table.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
fbocch2595@xxxxxxx
Sent: Monday, February 01, 2010 3:45 PM
To: midrange-l@xxxxxxxxxxxx
Subject: IDENTITY_VAL_LOCAL
Hi Folks, I'm out of my league on this one but I'd like to
know if it's possible to define a field in DDS that will hold
the RRN without having any programming to populate the field?
We are trying to update fields in an AS400 file from the web
server using SQL. Our web people are saying they need the
RRN in the file in order to insert the record using SQL.
They asked if we could create a field and use the function
Identity_Val_Local but we've got no experience with it.
Does anyone out there in AS400/iSeries land know how it's used?
As always, thanks ahead of time.
Frank
--
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.
_______________
Confidentiality Notice: This e-mail, including attachments, may include
confidential and/or proprietary information, and may be used only by the
person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
________________
midrange.com
