Dear Board Member

This session takes very long time to execute, even for fewer selected records. I explored the script and could only figure out(or guess) that it is due to do.all.occ, db.retry points. I am posting the script, looking for some urgent help from you all

A promt reply/solution would be a great help

Thanks and regards

please Reply....!!

Compile the script with the profile option; this allows you to obtain a printing of all function calls and the execution time related to them. With this report you can easily verify the number of function calls and the run time, so you can analyze the script by yourself to know where there is the problem.

This session was giving Error 201 previously. If we run it for 4 records, it works fine, but the moment we run it for more than 4 records it gave error 201.

I played with db.retry.point and commit.transaction. the error stoped but it started taking very very long time to complete if we select some 4-6 records.

Any simpler solution would be greatly appreciated

Error 201 means that the record changed when you tried to do an update on it after a delayed lock. In this case the problem seems that you, by example, read a record from table A, do something on it, after that it comes a commit.transaction that closes ALL the transactions you have done in this moment (even the ones you wouldn't expect to close), and after that you do another update in this table, so that the record is different from the starting record.
Debugging the script and tracking the db retries could help you to find the problem.

Try to have the db.retry.point() and the commit.transaction() as pairs on the same level.
In function generate.order.1() you have multiple commits with just 1 retry point.
In function update.order you do not have any retry point.
You can do the update on tskci015 within the same transaction, so remove the commit from update.order().

This would be better:
function generate.order.1()
{
... db.retry.point()
select tskci019.*
from tskci019 for update
...
selectdo
tskci019.ffno = tskci019.ffno + 1
db.update(ttskci019, db.retry)
on.main.table(update.order)
order.count = order.count + 1
commit.transaction() |Amit now
selectempty
...
db.insert(ttskci019, db.retry)
on.main.table(update.order)
order.count = order.count + 1
commit.transaction() | Amit 24-04-09 commented now
endselect
}

Secondly use with retry. From the help:
The WITH RETRY option in queries will prevent re-execution the already-processed records in case of update retries.
Example
The option WITH RETRY must be used in the following situation:
db.retry.point()
select table.*
from table for update
where table.fld1 inrange :fld1.f and :fld1.t
order by table._index1 with retry
selectdo
table.amount = table.amount + amount
db.update(ttable, db.retry)
commit.transaction()
endselect

Explenation

The WITH RETRY option is needed, because of the accumulation of the variable table.amount.
Say, there are two records in the range to be updated. If the commit on the first was done right, and the second fails (retry), the first record is not updated again when the WITH_RETRY option is implemented.
If the WITH_RETRY is not implemented, the first record is updated again and the value of table.amount variable will increase by twice the value of the variable amount.
The WITH RETRY option must be implemented in all situations where the commit.transaction() or abort.transaction() is implemented within the selectdo part.