Autogenerate numbers AS/400

Question

I have a field in my display file "EMPID".

This field must be autogenerated and protected.

If i am to use data area how exactly do i include this in my rpg(not rpgle).What are the other possible ways of auto generating numbers in a rpg.Please guide.

Reedits: Data area was used and it worked like a charm:

Please note the below code:

           C*** ARATWO IS NAME OF DATA AREA OBJECT           
C           *NAMVAR   DEFN           ARATWO  40   
C           *LOCK     IN   ARATWO                 
C                     Z-ADDARATWO    EMP     40   
C                     ADD  1         EMP          
C                     Z-ADDEMP       ARATWO       
C                     OUT  ARATWO

Thanks for all the aid.

Do you need the field autogenerated on your screen first, or just in your database file? I assume you are using RPG/400? — WarrenT, Sep 13 '12 at 15:55
@WarrenT I want it to reflect on my screen the values that r being incremented in d PF ofcourse.Would appreciate if any hint on those lines can be provided — techie, Sep 14 '12 at 04:41
I have some serious suggestions. Post your code. If you are completely stumped with code, try pseudo-code. If you can't come up with that, post your flowchart / block diagram. This is what I do when I am tackling a project. It helps me to organise my thoughts and focus on the things I need to learn. If you intend to work on the IBM midrange platform, you really must get accustomed to looking things up in the Infocenter http://publib.boulder.ibm.com/eserver/ibmi.html If you don't understand something there, cite the specific item and we'll try to clarify it. — Buck Calabro, Sep 14 '12 at 16:14
@BuckCalabro The problem has been solved using data area.Pl go thru the reedits. — techie, Sep 18 '12 at 06:49

score 3 · Answer 1 · edited Sep 14 '12 at 15:35

You can use SQL in your RPG. Two possibility: The first SEQUENCE SQL CREATE SEQUENCE my_lib/my_sequence …..

look at here http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_createsequence.htm

The second: auto-increment field in a PF (table) In DB2 SQL you can create a table (Phisical File) with a field defined like generated always as identity Example:

create table MY_LIB/MY_FILE (                                        
    Id_Auto     int             not null generated always as identity,
    Filed2     int             not null with default,                
    Field3        char(10)        not null with default,                
    PRIMARY KEY (Id_Auto)  
);

In this way, every INSERT operation on this file auto-increment the value of the field Id_Auto.

insert into MY_LIB/MY_FILE (Field2, Field3) values(10, 'Paolo');
set My_Var = IDENTITY_VAL_LOCAL()

After those two operations the Id_Auto field is incremented automatically and you get his values in My_Var with the IDENTITY_VAL_LOCAL() function

ciao

Appreciate the help.Please see d reedits for usage with data area. — techie, Sep 18 '12 at 06:51

score 1 · Answer 2 · answered Sep 13 '12 at 13:27

1

Chapter 11 of the RPG User's Guide has examples of using data areas in RPG.

answered Sep 13 '12 at 13:27

Buck Calabro

7,558
22
25

WarrenT · Answer 3 · 2012-09-15T16:25:06.893

If you are willing to use SQL in your RPG program, then I would recommend using a DB2 Sequence object (which is actually a data area under the covers) to assign your EMPID with.

To set this up you could use something like:

CREATE SEQUENCE GenEmpID as numeric (6,0) start with 10000;  -- pick your own start point

Then in your RPG you could use a statement like:

VALUES next value for GenEmpID 
  INTO :newkey

The value in newkey can go onto your screen, and can be used as the EMPID value on your record when you do an INSERT.

This method avoids doing an empty INSERT just to let DB2 generate the next key values, then performing a separate UPDATE to actually populate the empty record. Why do two database operations when you can do only one? Instead we only read & modify a data area, and the way we do this is with one call to SQL rather than using one RPG statement to go out to the OS to read the data area, increment the value, followed by another RPG statement to go out to the OS again and update the value. In general, all other things being equal, the fewer times you have to call out to the operating system the better. This allows the DB2 and the OS to optimize the operation for you, sometimes below the MI level.

Appreciate the help.Please see d reedits for usage with data area. — techie, Sep 18 '12 at 06:51

Autogenerate numbers AS/400

3 Answers3