Transaction Processing in Batch Application

[Zoltan_Gyimesi]

Hi List/Forum,

I have never used transaction processing up to now in batch applications. The documentations that I was able to find are very poor in this topic. Really hope, somebody already used this feature and can help me how to use it with success. Unfortunately I haven't time to experiences all of my open questions.

First, here is my scenario:
=====================
I want to create an interface UBE which gets input from a text file produced by a legacy system. The driver section of this UBE won't have Business View but will update records in two tables using Table I/Os in the event rules. My goal is that when I detect any problem (e.g. type conversion, invalid value, failing record update, etc.) during the running I want to Roll Back all update on the records that were done previously in this running.

Some of my several questions are:
=================================
0.) Does it work at all?
1.) Could somebody povide me with a good scenario?
2.) Could somebody povide me with or point to some good documentation or white-paper?
3.) Do I have to enable the Transaction Processing on the Report Properties or can I use simply the Begin, Commit and Rollback system functions without this setting?
4.) Does previous enabling start a transaction automatically for the report or do I have to do explicitily with Begin Transaction system functions?
5.) What kind of type parameter requiered for the Transaction ID in the transaction system functions (Numeric, Identifier, Integer, etc.)?
6.) What happens with the transaction when the UBE ends without issuing a Commit or Rollback call?
7.) Do I have to Open the tables (and later Close) with marked Include In Transaction check-box? If yes, then do I have to position the Open after Begin Transaction or is it all the same?
8.) What happens with the transaction when won't issue a Close for the table?
9.) Where to position the Close when it is required?
10.) Could be placed the Begin, Open, Update, Close, Commit and Rollback into different events of different sections? There is any required sequence for this calls?
11.) Can I handle more than one independent but simultaneous transactions?
12.) Can I handle more than one independent but NOT simultaneous transactions?
13.) Can I handle separate sub-transactions wich could be Rolled Back independently from the parent transaction?
14.) ... and several more ...

This topic is interested me generally but currently I want to deal with it on B7332 SP 11, NT server and workstations and SQL engine, so please, if you know any specility for this platform, let me know too.

Any help will highly appreciated!

Thanks in advance,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[Guest]

Hi,
I have used once Transaction Processing in batch application.
It works If you use business function instead of table I/O provide that you
have checked the 'included in transaction'.
You make a TCID type working variable. Firstly you have to use system
function Begin Transaction, If the process ends successfully you have to
call system function Commit Transaction else Rollback transaction. The
parameter of all the system function is the working variable type TCID.
I hope this will help you.

Maria Akrivou
Technical Consultant
SOFTECON - J.D. Edwards
Athens
Greece

Tel: +301-8253803
Fax:+301-8218249
Email: [email protected]

 

[Zoltan_Gyimesi]

Re: RE: Transaction Processing in Batch Application

Hi Maria,

Thanks for your reply.
Please, let me some further questions:

1.) Do you mean that transaction processing does not work for Table I/O with "include in transaction"? Do I have to place my Table I/O-s into a business function making possible to manage the transaction?

2.) What about the Open/Close?

3.) Am I right that the TCID parameter is an output for Begin and an input for Commit and Rollback?

FYI: I haven't found TCID data item on our B7332 just TNID "Transaction ID" but it was a bit suspicious because TNID is a 3 character long string type. Now I checked our XE too and I have found TCID there and seen that this is a 15 long numeric without decimals.

Rread you later,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[Guest]

RE: RE: Transaction Processing in Batch Application

Hello Zoltan,

Where can you put 'include in transaction' in the Table I/O?
I don't see any suggestion like this in Table I/O...
That's why I'm suggesting you to use business function.
I have tested using Table I/O at first and it didn't work.
On the other hand when I used business function it works.

What did you mean with open - close. You mean the table?
As long as I use Table I/O in business function I have concluded that
Open or Close is not necessary when you use Fetch Single, Insert, Update or
Select and Fetch Next.

Yes you are right the working variable is used as output variable in begin
transaction and
input variable in Commit Transaction or Rollback Transaction.

I don't remember if TCID data item exists in B7332. Sorry

Maria Akrivou

 

[Zoltan_Gyimesi]

Re: RE: RE: Transaction Processing in Batch Application

Hello Maria,

Thanks again for your reply.

Before I posted my original post I re-read yesterday again the Transaction Processing chapter in the Development Tools manual. There was mentioned the Open and Close. When you are in the ER Designer and select the Table I/O operations, further select the Open function then you will have a check box "Include in Transaction" under the File/Business View selection list on the "Data Source" window.

Maybe this File Open technique were missing when you tried first Transaction Processing with Table I/O.

You are right, Open and Close are not necessary in most of cases but sometimes in special situations it is required. Unfortunately I haven't found any good explanation about this issue up to now. Maybe it could be necessary when you want to manipulate a table with more then one pointer independently or something like. (Maybe somebody will explain for us the Open/Close issue.)

At last, never mind that TCID does not exist under B7332. I can select any other 15 long numeric data item under B7332 (e.g.: MATH10).

Thanks again,

Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[Guest]

RE: RE: RE: Transaction Processing in Batch Application

Thank you for the information.
I will try it

Maria Akrivou

 

[Zoltan_Gyimesi]

Hi List/Forum,

It is just FYI: IT IS WORKING!
... at least in my scenario (described below) and on our system (see details in my signature).

My scenario:
============
* I get the input from a text file calling our Flat File BSFNs, so the UBE hasn't any Business View.
* I have three un-conditional sections (amoung more other), in order let's call them S1, S2 and S3.
* I Update two tables in the UBE, F4211 and a custom one.

* The "Enable Transaction Processing" is On on the Report Properties.

* In S1 section: I issue "BeginTransaction" System Function call, I Open (not Open Handle) the two tables, setting On the "Include in Transaction" check-box for both table.

* In S2 section: I go through the lines of the input text file and update the related records in the two tables. I set a flag for RollBack when I detect any problem (e.g. non-existing record, un-convertable data in the input text, etc.)

* In S3 section: I close the two tables and Commit or Rollback the transaction based on my flag using these System Function calls.

* Yeah, the Commit and Rollback works fine. When I Rollback the transaction then no update appears in the two tables. Great!

Since I hadn't enough time, I didn't made more experiments with other scenarios (e.g. what happens when Close call is mising, change the sequence of System Function Calls, Turn-off transactin enabling in Report Properties, etc.)

Regards,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[Christian Audet]

Zoltan,

I'm realy happy to hear that it's working, since I didn't get any chance to try it, I was always wondering if this was working at all.

Again, Thank for the feedback !

Christian Audet

*** SHARED KNOWLEDGE = BETTER KNOWLEDGE ***

Implementing B7333 (Xe) SP16.1, SQL
(Support B732, B7331 and B7332)

 

[Zoltan_Gyimesi]

Hi Christian,

I was also happy that it is working because it is a very usefull and many times necessary feature to make reliable Database Modifications.
Once upon a time I had worked with Progress V6.x Database and 4GL and I loved its reliable and easy to deal with transaction processing features.

I wondered a bit that I haven't got replies (except from Maria!) about this interesting and important feature.

I hope, more of us will experiment with this feature and share the results here on the Forum. For example, what happens when neither Commit nor Rollback is issued.

*** SHARED KNOWLEDGE = BETTER KNOWLEDGE ***

Zoltán

P.S.: Hey Moderator, I like your signature

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[vbojan]

Hi List/Forum,

My experience is that you need to Open table with “Include In Transaction” only once and all Table I/O between Open and Close for that table will be included in the same Transaction. Problem is when I try to call UBE from another UBE and include child UBE in transaction. On my UBE Interconnections screen (Xe, SP16, AS400) that check box is grayed out. Both UBEs are Transaction Processing Enabled. Any help will be appreciated.

Regards,

Bojan.

 

[Zoltan_Gyimesi]

Hi Bojan,

I checked this on B7332 SP 11 and the situation is the same.

It seems so that this is enabled only when you make the Report Interconnect from an Interactive application (APPL). Why?

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[vbojan]

Servus Zoltan,

It shouldn’t be any .INI settings because I can do Include In Transaction from Application too. Maybe it assumes that child UBE is included in existing transaction? In that case what will happen if error occurs in child UBE, will that rollback parent UBE as well? Looks to me like I have to spend some time testing all of this.

Regards,

Bojan.

(Xe, AS400, SP16)