Data Area (Decimal) - Read/Write/Increment for Unique Running Number

[Sowhat]

Hi guys,

I have this assignment that required me to do the followings,

• To retrieve a Next Transaction ID from Data Area (Decimal)
• To use that ID in a SQL Insert Statement on a specific table on the same system
• For a successful insert (committed), the value of Transaction ID should be updated

I would like to know if there exist any mechanism or method to prevent dirty read on the Data Area before another process committed the update? Or if there exist any method to solve the synchronization issue?

My current approach is to use JTOpen and have 2 connections: (1) AS400 (2) AS400JDBC
(1) By using AS400 Connection, i will read the decimal value
(2) By using AS400 JDBC Connection, i will use that value and run an insert statement
(3) If insert is committed successfully, by using AS400 Connection, i will then update the value by +1
But this din solve the synchronization issue, do it?

Do correct me if i am wrong.

Thanks for your helps.

[B.Hauser]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

I think an SQL sequence object is what you are looking for:
A sequence object is created with the SQL command CREATE SEQUENCE:

CREATE SEQUENCE MySchema/MySequence
   START WITH 1
   INCREMENT BY 1
   NO MAXVALUE
   NO CYCLE
   CACHE 20;

To get the next value from the sequence object the following SQL code can be used:
NEXT VALUE FROM MySequence

Example:

Insert into MyTable
Values (Next Value From MySequence, ...)

Birgitta

[Sowhat]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

Hi Birgitta,

Thanks for your help.

The current AS400 system is using the decimal data area for transaction ID.
I am required to use that to retrieve next transaction ID.
I afraid I cant create a new sequence to use. =(

Any luck?

Sowhat

[MichaelCatalani]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

You can wrap the data area retrieval into a UDF, and use that on your SQL statement. That will at least allow you to use the existing data area.

But I dont think there is any way you are going to be able to control the entire process under commitment control. (I dont think you can control data areas under commitment control anyway, unless its something new I havent heard about.)

[Sowhat]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

Cool.

Thanks a lot on the information.
I will look into that area. =)

[BenThurley]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

You might be able to try an exclusive lock on the data area while performing the updates. Something like this:

1. Request lock on data area
2. If locked then proceed else wait and try again
3. Increment data area
4. Write to table
5. If successful then release lock on data area
6. If failure then decrement data area and then release lock

The command to get the lock is ALCOBJ and DLCOBJ to release it. I would try either *EXCLRD or *EXCL for the lock state. You should be able to find a way to run these commands using the jt400 or jtopen jars.

The existing greenscreen programs will sit waiting for the lock until you release it. This means it is vital that you release the lock. You don't want an exception in your code to cause the lock to be left lying around. I would run a few tests here depending on how you are creating the AS400 object. If you create one for each request then you might get lucky and the lock be released at the end of the request. If you implement some kind of connection pooling then you'll need to be extra careful to make sure the lock is released.

[Sowhat]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

Thanks. This is exactly what i wanted to implement.

From your experience, do you think we can let the Application Server handle the AS400 connections (not the AS400 JDBC Connection)? I tried to google it but cannot find any materials on that. Seems that it is not possible.

I currently implemented the AS400 Connection Pool in a java singleton and make sure connection is return to the pool after execution. In this case, I should make sure the lock is released before returning the connection too. I am not too sure whether my methodology and implementation are correct, and would appreciate if you can point me to a sample or best practice site for a good implementation of AS400 Connection Pooling?

Thanks for you helps.

[BenThurley]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

First up I should add a disclaimer that I'm not really an expert at this. I've had a play about with the jt400 jar in the past but I haven't used it in a production environment so to speak.

I found this page in the documentation on creating AS400 connections. It talks a bit about connection pooling. May be useful.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzahh/mngcon.htm

I had a look for a way to run ALCOBJ but I couldn't find anything specific. I think you would have to run it as a CommandCall. I'm not entirely sure how well this would work. The object would be locked to the job processing your request/AS400Connection. Presumably if using connection pooling the system would keep the job going.

If anything went wrong and the DLCOBJ was missed I don't know what would happen. In an ideal world the call to returnConnectionToPool would release any open locks. I think you would need to test that this happens. The documentation tells you how to use it but it doesn't explain much about how it works and what it actually does.

There may be a better lock type to use too. I always have to read up what they are before using this command.

[Sowhat]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

Thanks Ben.

I will work on this using all the suggested approaches and determine which one is more feasible.

[Sowhat]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

Java Transaction control in data area is a mess. Any one work on the data area transaction before? rollback etc...
I cant find any materials on net that helps me.
As far as i know, there is only journaling which enables commit and rollback for database in iSeries.

Will the problem be solved by writing the whole functionality (read/write data area, insert new record) in RPG programming?

Thanks for your helps.

[B.Hauser]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

As far as i know, there is only journaling which enables commit and rollback for database in iSeries.

Data queues and data areas can also be journaled on system i. To register a data queue or a data area in a journal you need to execute the CL Command STRJRNOBJ.

As soon as the data area is registered, it can be used under commitment control, i.e. it can be locked and rolled back by the COMMIT and ROLLBACK commands.

Will the problem be solved by writing the whole functionality (read/write data area, insert new record) in RPG programming?

Without having registered a data area in a journal, commit and rollback will not work with RPG either.
In either way keep in mind, the data area keeps locked until the next commit or rollback is executed.

Birgitta

[Sowhat]

Re: Data Area (Decimal) - Read/Write/Increment for Unique Running Number

Data queues and data areas can also be journaled on system i. To register a data queue or a data area in a journal you need to execute the CL Command STRJRNOBJ.

As soon as the data area is registered, it can be used under commitment control, i.e. it can be locked and rolled back by the COMMIT and ROLLBACK commands.

I suppose the COMMIT and ROLLBACK commands is not from Java Toolkit? Coz i am looking at the solution from Java programming using the jt400.

Without having registered a data area in a journal, commit and rollback will not work with RPG either.
In either way keep in mind, the data area keeps locked until the next commit or rollback is executed.

RPG maybe a better solution to handle all the stuff, and I will just call store procedure thru Java Toolkit.