AUTO INCREMENT

15 pts.
Tags:
Auto increment
DDS
MySQL
PHP
We created a File with an Auto Increment Field using MYSQL statements through PHP. I have no Idea how to do this using DDS statements or settings.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

There is no equivalent to Auto Increment Field in DDS – you may however be able to implement something which will give the same result if you create a trigger program for your file which updates a newly added record with the next available number.

Regards,

Martin Gilbert.

Hi from PGMBOB,
In an RPG program we setgt with the key including the data you want to increment,
readp the file (you might not want to lock that record for update).
Increment your result of the READP.
Fill in all your data fields. MAke sure you do this completely, the data from the READP is in the buffer.
Any other data you thought you put in the record is gone.
For safety you might rename the fields in the record you accessed to get the increment.

Write your new record.

It is possible for a trigger program to update the same record that caused the trigger.
Trigger programs seem better at maintaining other files as a result the record that caused the trigger.
Good Luck,
PGMBOB

Discuss This Question: 16  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sloopy
    Historically, this has been done on the AS/400 by creating a numeric data area with the same name as the physical file. Whe a program is responsible for adding a record to the file (or is a trigger program which receives control during the write process and may modify the data before the write), it reads the data area locked, increments the number, updates and unlocks the data area and uses the incremented data area value in the file's 'auto increment' field. The code looks like this:
         D daMMID          S                   DtaAra(MFMAST) Like(MMMMID)
    
         C                   DoU       not *In91                              
         C     *Lock         In        daMMID                               91
         C                   EndDo                                            
                                                                              
         C                   Eval      daMMID = daMMID + 1                    
         C                   Out       daMMID                   
                                                               
         C                   Eval      MMMMID      = daMMID    
         C                   Eval      MMSTAT      = StatusLive
         C                   Eval      MMMAFN      = D2MAFN    
         C                   Eval      MMMAFT      = D2MAFT    
         C                   Eval      MMMAFL      = D2MAFL    
         C                   Eval      MMMAFH      = D2THGT                  
         C                   Move      CurrISOdate   MMCDAT
         C                   Move      CurrISOtime   MMCTIM
         C                   Move      PGUSER        MMCUSR
         C                   Write  (E)MM                  
    
    The loop around the data area allows the code to wait until the data area is free - another program or instance may be doing the same thing! Regards, Sloopy
    2,195 pointsBadges:
    report
  • Cwc
    For functions like this that are supported by the database (DB2/400 or DB2 UDB, or whatever its official name is now), but not by DDS, you should be using SQL to define your data (and maybe consider using it for every new file instead of DDS). Through SQL, you can create a file (table in SQL terminology) with an identity column that will auto increment every time a record is added to the file. Depending on your specific needs, the syntax for a file with two fields, the first of which is the identity column, would be something like this: CREATE TABLE &LIB/SOMETABLE ( SEQKEY INTEGER GENERATED ALWAYS AS IDENTITY (CYCLE) , NEXTFIELD CHAR(10) DEFAULT ' ' ) ; CWC
    4,290 pointsBadges:
    report
  • Sloopy
    PGMBOB.... What you suggest has been done before, and does not work effectively. A software house I used to work for incremented its ID fields by SETGT / READP. Unfortunately, by the time a program had constructed the record and added it to the file, another program had come along and generated the same 'new' record ID, resulting in duplicate record IDs. So, they tried locking the record read for the last ID number, but that causes horrendous problems in the database. Then, they immediately wrote a 'blank' record with the new ID - but then they got blank records in the database if an operation did not complete correctly; and they still got duplicate IDs... I had to force them to look at the data area approach. I had to prove it worked, and it does. CWC -- this was before DDL and the ability to use SQL tables through RPGIV. I do like your solution, as long as a company is going towards using DDL. If they are staying with DDS, making use of DDL for one or two files may be counter-productive. It's probably not a decision a programmer can make - it would have to be referred upwards, and a case made. Regards, Sloopy
    2,195 pointsBadges:
    report
  • BigKat
    Also, the generated identity value is not user friendly if you are looking at generating. for example, an item number that someone would have to enter. It is fine for programatically used primary-foreign key relationships. Kevin - Affiliated
    8,210 pointsBadges:
    report
  • Cwc
    An SQL created table appears as a file to any HLL program, whether RPG III or IV, or COBOL, and can be intermixed with traditional DDS files. We have done so in our application. I'm not sure why the generated value wouldn't be user friendly, as it would just be a sequential number; the same as a what a program would do through a data area. I'd like to voice what may be a controversial opinion: DDL is becoming the preferred way of describing data, is more universal, and is what IBM has been recommending for the past few years. I believe that it is time for more System i developers to get with the 1990s and 2000s and use modern approaches to application development, instead of the old methods which tend to require more coding and more time. This is what has been creating the "legacy" perception of the i among younger programmers, and why fewer of them even want to learn it. Since Steyn said he's not familiar with DDS at all, but is with SQL, it seems to make sense to use the SQL approach, which requires no program logic.
    4,290 pointsBadges:
    report
  • Alopez1571
    I really like the DDL approach as I'm convinced that System i developers have to move on to current software development features. Also, you don't have to deal with how the solution works, the DBMS does it for you. Here there is a link for document about identity colunm in DB2: http://www.db2expert.com/dpj/Vol_1-4_Luksetich.pdf Regards, Arturo Lopez
    150 pointsBadges:
    report
  • GAC
    Maybe I do not understand Sloopy so well, but I think that he is not "voting" for a solution over another. What he is (IMHO) trying to communicate is that if you are going DDS then go DDS and if you go DDL then keep going this same way, and that mixing both flavors (what I would do) is not a decision a single programmer may take, but an administrative person. Why? I think it is a matter of documentation and procedure. Please correct me if I am wrong, but this appreciation is not about a technical method but about procedures. Maybe CWC is not as free to make such a decision and this advice is to be considered. Have a happy day you all, Gerardo Ag├╝ero
    300 pointsBadges:
    report
  • Cwc
    My main point was that DDS and DDL can be co-exist just fine, just as RPG III and RPG IV can, and I wanted readers to understand that. Thus, managers can make an informed decision when they lay out and approve new strategies. If people are under the impression that an all or none approach must be taken to implementing new features such as this, then their shops will likely never undertake them, and their applications will fall even farther behind - which will hold back the platform from wider acceptance, and let the overused "legacy" term sink in even deeper than it already has. Such companies are then likely to choose a different architecture that is perceived as more current, and overlook one of the greatest systems on the market. I guess Angus, The IT Chap (Trevor Perry) has made a bigger impression on me than I realized. ;)
    4,290 pointsBadges:
    report
  • Sloopy
    I think Gerardo has got what I was saying. I have no problem with DDL. And I do agree with the point you make, CWC - DDL and DDS can be mixed, and where we are able to do that, we should. However, I am not free to start creating DDL-based files in this company, unless we are building a brand-new database and application set. And even then, I would have to make the case to my managers. As an aside.... At that time, I will probably create a new source type so that we can create DDL as source members which can be run through our compilers and change-control system in exactly the same way as PF and LF DDS. That way, we have a record of the DDL that programmers can edit, compile, manage and promote. That's something which is lacking at present in DDL, but which is required where I am working. Regards, Sloopy
    2,195 pointsBadges:
    report
  • Cwc
    I agree, we also have editable source members for our DDL that are set up in our change management system so that they are documented and can be managed accordingly. IBM also has an API that will generate DDL source from any physical or logical *FILE based object. I don't remember its name, but have it referenced in a previous Q&A topic if anyone wants more info about it. So, if there were no DDL source available, it could always be created from that API and then set up for change control as desired.
    4,290 pointsBadges:
    report
  • Koohiisan
    In the past, I've created a numeric 'index' field in my DDS. When I write to the file, I use the SQL function max() over that field: insert into myfile (index, part, qty) values((select max(index) from myfile), 'part1', 200) Something along those lines has proven to work well for me. I typically create a logical over that field and have it sorted in descending order. Not sure if that helps or not, but it's there and I reference it in my real SQL. I've never had multiple programs walk over each other using this method, although to be honest I always have staged those particular programs in their own job queue. HTH.
    5,020 pointsBadges:
    report
  • Kccrosser
    Koohisan - while that technique may work 99.999% of the time, in a heavily loaded system there is no guarantee that two process threads won't generate the same value, as the "select" part of the process could be run for both processes concurrently - giving both processes back the same value. However, a simple solution (if coded correctly), is to have that column defined with a "unique" constraint. Then, when two (or more) processes do generate the same key value, the second thru nth inserts will fail. Just put the inserts in a loop so that they keep retrying the select...insert until they do not get a uniqueness violation.
    3,830 pointsBadges:
    report
  • DanTheDane
    You may add an auto-incremental field to to an existing DDS-defined file by using this SQL-statement:
    ALTER TABLE mylib/myddsfile 
    ADD COLUMN myincrementfield 
    INTEGER GENERATED ALWAYS AS IDENTITY 
    
    DanF
    2,555 pointsBadges:
    report
  • Stiloabarthian
    I think you have to be on V6R1 to use indentity columns.
    175 pointsBadges:
    report
  • Cwc
    ^Identity columns have been supported since V5R3.
    4,290 pointsBadges:
    report
  • Cwc
    Correction, since V5R2.
    4,290 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following