CODEWITHSUNDEEP
ibm-midrangerpg
techie
techie

Reputation: 467

Autogenerate numbers AS/400

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.

Upvotes: 0

Views: 3776

Answers (3)

WarrenT
WarrenT

Reputation: 4532

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.

Upvotes: 0

paolo
paolo

Reputation: 31

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

Upvotes: 3

Buck Calabro
Buck Calabro

Reputation: 7648

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

Upvotes: 1

Related Questions