Understanding Commitment Control and Best Practices

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Understanding Commitment Control and Best Practices

Post by jyoung » Tue May 24, 2016 4:02 am

Hi All,

I need some help understanding commitment control and the best practices with using it in LANSA. Having said that I am well versed in the use of Transactions and TransactionScope in the .NET world where you would have a begin / end "boundary".

The documentation at http://docs.lansa.com/140/EN/lansa065/i ... E_0060.htm raises a few questions.
Avoid actually using "programmer controlled" COMMIT and ROLLBACK techniques whenever possible. This simplifies your applications and makes you less likely to be affected by different DBMS commit/rollback methods in the future.
If I have to insert data into two different tables, how do I do this without COMMIT and / or manually rolling back the previous insert if the second fails?
Use simple and well defined transaction boundaries.
When does the boundary begin? With SQL you would have a BEGIN / COMMIT or ROLLBACK that clearly outlines the begin and end boundaries. Here all we have is the end with COMMIT / ROLLBACK.
If your application uses mixed mode commitment control (ie. some tables under RDML level commitment control, some not) please refer to 13.12.1 Using Mixed Mode Commitment Control for instructions on how to make this work on platforms other than the IBM i.
This makes me think that Commitment Control is an all or nothing approach at the SYSTEM or PARTITION level. Yet each File has Commitment Control and Auto Commit File Attributes. Do I need to enable these attributes?

Finally there are "Platform Considerations" that state how Commitment Control is different on the IBM i. I confess I am bit new to the IBM i and to LANSA, so what kind of things should I be concerned about?

MarkD
Posts: 627
Joined: Wed Dec 02, 2015 9:56 am

Re: Understanding Commitment Control and Best Practices

Post by MarkD » Tue May 24, 2016 8:46 am

In most DBMSs commitment control is generally an all or nothing thing - but somewhat weirdly and historically on the IBM i that is not so. You can have some tables (aka files) under commitment control and some not.

The only good reason I have ever heard for using this ‘mixed mode’ was if you had a table that you reported errors into, which you would not want a ROLLBACK to remove data from. There’s also potentially some mixed mode requirement if you are using tables from OEM software packages because you often can’t control the table’s commitment control definition. Generally sticking to all or nothing would be the way to go these days I think.

Regarding boundaries, the start boundary is when you issue the first uncommitted DBMS operation and the end boundary is when you very distinctly either commit or rollback. I think that basically the documentation is just trying to say that the boundaries should be well defined/understood and should never span across any type of user interaction.

Tim McEntee
Posts: 10
Joined: Thu May 26, 2016 8:46 am

Re: Understanding Commitment Control and Best Practices

Post by Tim McEntee » Thu May 26, 2016 9:20 am

My experience with commitment control is from the early 90's. Each file to be under commitment control must be journalled. You must start commitment control before starting LANSA. This means that everything that you update that is journalled will be under commitment control and you must do a commit or rollback after your Update/Insert/Delete commands. This is a big overhead.

It makes more sense if you have a batch job that does some complex updates. Here you would submit a job that first starts commitment control, then runs the batch job, which has the explicit COMMIT and ROLLBACK commands after your block of updates. There is a FUNCTION option called *PGMCOMMIT.

An alternative to commitment control that many LANSA programmers use is to validate the data to be updated before writing it. This can take the form of program written validations, but more importantly you would use the CHECK_ONLY(*YES) option on each of the Update/Insert/Delete commands. This allows you to test the file level validations without writing data to the database.

This is a good use of the power of the LANSA repository. If setup well you can trust the data going into the files if they pass the validations.

eg.
* validate
BEGINCHECK

INSERT FIELDS(#EMP_GRP) ON_FILE(EMPLOYEE) WITH_KEY(#EMPNO) CHECK_ONLY(*YES)

SELECTLIST NAMED(#MYLIST)
INSERT FIELDS(#PAY_GRP) ON_FILE(PAYROLL) WITH_KEY(#EMPNO #WKNO) CHECK_ONLY(*YES)
ENDSELECT

ENDCHECK IF_ERROR(*RETURN)

* passed validation - now do the update
INSERT FIELDS(#EMP_GRP) ON_FILE(EMPLOYEE) WITH_KEY(#EMPNO)

SELECTLIST NAMED(#MYLIST)
INSERT FIELDS(#PAY_GRP) ON_FILE(PAYROLL) WITH_KEY(#EMPNO #WKNO)
ENDSELECT

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: Understanding Commitment Control and Best Practices

Post by jyoung » Thu May 26, 2016 11:33 pm

Interesting. I did not know about the CHECK_ONLY(*YES) option.

Thanks.

MikeS
Posts: 7
Joined: Wed Jun 01, 2016 12:22 am

Re: Understanding Commitment Control and Best Practices

Post by MikeS » Wed Jun 01, 2016 12:38 am

I've been using commitment control in Lansa for several years. Whilst you can do some checking before updating database records it is not completely fool proof. It could be that something happens in another user session (like adding a duplicate record) that couldn't be handled by your program, so the ability to roll everything back if you encounter any sort of issue is extremely valuable.

Whilst you do need to journal every file that you want to participate in commitment control this does have it's own advantages. It's very useful to be able to view the journal when debugging programs, and you can even use the journal to rollback changes manually in the event of a system issue or when testing.

It is important to structure your code correctly. Make sure you only use a commit statement once at the end of the update routine, make sure you always issue a rollback in an error handling routine, and never prompt for a user action (like answering a message) between issuing the first update statement and issuing a rollback or commit command. Failure to do this will result in record locking issues.

My experience is that people are very easily frightened off of commitment control because it seems a bit complicated at first, but I would strongly recommend it.

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: Understanding Commitment Control and Best Practices

Post by jyoung » Wed Jun 01, 2016 11:22 pm

Hi Mike,

Can you elaborate on how you handle errors?

For example, in most other languages that support a try/catch/finally block I could do the something like the following pseudo code.

Code: Select all

transaction = new transaction
try
	insert
	insert 
	insert
	transaction.commit
catch
	transaction.rollback
finally
	transaction.dispose
How does this translate to LANSA? Would you check the status of each statement and rollback if any failed and then if we made it to the end commit?

For example would you do this?

Code: Select all

insert fields(....) to_file(....)
if_status is_not(*okay)
	rollback
	return
end_if

insert fields(....) to_file(....)
if_status is_not(*okay)
	rollback
	return
end_if

commit
Thanks for help.

MikeS
Posts: 7
Joined: Wed Jun 01, 2016 12:22 am

Re: Understanding Commitment Control and Best Practices

Post by MikeS » Thu Jun 02, 2016 3:23 am

Hi

Yes , I would do it pretty much as you describe. I would rollback every time I encounter an error condition, just as you do in your example, and issue a commit only when I successfully get to the end of the update routine.

There is no equivalent begin commitment cycle - the cycle starts as soon as you update the first record in your 'logical' commitment cycle, and ends when you issue a rollback or commit statement.

As I said previously you should avoid waiting for any user interaction (such as responding to a message) once you have started updating records, as the system will lock any updated records until a rollback or commit is issued.

One final thing -you need to make sure that commitment control is started for your job. We use the VLF to deliver windows applications, and there is a setting within the framework to start commitment control when the framework is started. You can do it when running a form, but I can't remember exactly what it is off the top of my head. Let me know if you need this info. Similarly, if you are submitting a batch job you need to make sure commitment control is started for the batch job. In RDML functions you can specify *PGMCOMMIT as part of the function definition. I've found this doesn't work for RDMLX function and so I issue the statement

Use Builtin(SYSTEM_COMMAND) With_Args(X 'STRCMTCTL LCKLVL(*CHG)') To_Get(#RETCDE)

at the beginning of the function to start commitment control and

Use Builtin(SYSTEM_COMMAND) With_Args(X 'ENDCMTCTL') To_Get(#RETCDE)

to end it.

One final, final thing. If you change a file you need to re-compile any functions that use that file under commitment control. Because they use native access when under commitment control they will fail with a level check if not re-compiled.

Regards

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: Understanding Commitment Control and Best Practices

Post by jyoung » Thu Jun 02, 2016 4:47 am

Thanks for the help. That clarifies things quite a bit.

We are using VLF-ONE so anything that would be coordinating the transactions would be done at the server module. I don't know if we need to use the same BIFs that you do in your functions and forms but it gives me a clue as to where to look next.

One final question, on all your Files (Tables) do you enable commitment control in the File Attributes?

Again, thanks for the info.
Joe

MikeS
Posts: 7
Joined: Wed Jun 01, 2016 12:22 am

Re: Understanding Commitment Control and Best Practices

Post by MikeS » Thu Jun 02, 2016 10:40 pm

Hi Joe

Yes, you need to enable commitment control via the file attributes within Lansa. Outside the Lansa environment you do need to make sure that the file is journalled using the STRJRNPF command. Let me know if you need any info on this.

Regards

Mike

Post Reply