Real basic question - Table Open, Table Close

What, if any, harm is done by doing fetch singles, or select/fetch next loops without using the Table Open and Table Close commands? What do these commands do? When would you use them?
Thanks,
Tom

Maybe not such a basic question. I have looked through JDE documentation, and I can't find anything that tells me what Table Open and Table Close do. My thinking is that if I do a Table Open, then I have put a lock on the table. Therefore I need to do a Table Close to release that lock. I have nothing to back this up though. Does anyone else?

Tom,

There's really no point to using these statments that I am aware of unless you are using "Table Handles". This is because the underlying runtime automatically opens/closes tables (cursors) for you.

The exception, Table Handles, allow you to have multiple cursors open against the same table and/or override the environment to use to determine what schema/database to find the table in.

Table handles are discussed somewhere in the developer dcumentation.
Good how-to documentation also exists in the book "J.D. Edwards OneWorld - A Developer's Guide" by Steven Hester and Chris Enyeart.

Basically what you need to do is:
1. Create a table handle definition in the Data Dictionary if one does not already exist for your table (see F41021Handle for an example).
2. Create a Form or Report Level ER Variable based on the Data Dictionary handle entry.
3. If an application, use the "Table Open" and " statement in the Dialog Initialized event. Use the Close statement in the End Dialog event.
4. All I-O statements (Fetch, Select, etc) against that particularly table should use the same table handle variable.

Hope this helps.

Thanks, Larry, for the reply. That is very helpful. I usually don't get into table handles, so it's good to know I don't really need these commands.....for now.

We have used the table open and close statements in transaction processing. when we want to include a table in the transaction boundary, we have to open the table and check the include in transaction option. then at the end..roll back or committ... use table close prior to the commits or roll backs.

thanks,
Hari

Thanks, Hari. This seems to support my theory that table open puts a lock on a table, and table close releases that lock.

Hari,

Would like to know more about the transaction processing.
I saw the Advanced option of the Open statement about the Transaction processing. But how did you use the Rollback and Commit. Those two System Functions use the Transaction ID. Please explain or provide an example code.

Thanks in advance,
Sriram Visvanathan.
Independant JDE Consultant.

I will try to expalin you with an example....

I am working on one Inbound Interface, where i get records from a text file. i have to create records in Accont Ledger (F0911) table. For this i have to insert records into F0911Z1.
For each line i get from text file i have to insert two records into F0911Z1. One for Debit entry one for credit entry. (bcoz the amount should be balanced to ZERO)
Here i am concerned about record level processing. so i will open a transaction in the beginning of a do section.
you can use a constant numeric value for the transaction id.
Transaction ID = Any literal (or next number )
Begin transaction (Transaction ID)
F0911Z1.Open (Check Include in transaction in advance properties)

F0911Z1.Insert (For debit entry)
if success
F0911Z1.Insert (For credit entry)
F0911Z1.Close
If success
Commit Transaction (Transaction ID)
Else
Rollback Transaction (Transaction ID)
End If
End If
Here the Commit statement inserts records to F0911Z1, and Rollback statement stops the inserts to F0911Z1.

this is a case where we have to have the transacation processing at record level (thats my client requirement).

in some cases where we need to have this transaction processing for the entire file.

For example, if i want to send my Item/Branch information to a legacy system. And i want all the records from F4102 to be sent to the legacy system. so in this case if the insert of one record is failed then we have to do a rollback to stop the commits. If all the records are succesfully written then we can do a commit.

Hope this helps....

Thanks and Regards,
Hari

Hari,

Thank you very much for a detailed explanation of the transaction processing with an example. Appreciate it.

Thanks
Sriram Visvanathan.

Hi Sriram,

Just some addition to Har's post.
I frequently use transaction processing with Open/Close.

#1. I always use MATH10 data item for Transaction ID.
#2. You can not define "Sub-Transactions".
#3. I exerieneced, if in an UBE any insert inside the transaction boundary violates the primary key constraint, then the transaction will be automatically Rolled Back, irrescpctivly of you issued the Commit Transaction system function call. (Maybe other unsuccesful Table I/O on Delete, Update can cause the same, but I faced only with this behaviour on Insert up to know.
#4. You can play with Transaction Processing and Open/Close in interactive application too. For more detail, please check the "Transaction Processing" chapter of Devekopment Tools guide.

Regards,

Zoltán

Zoltan,

Thanks for the information, i am not aware of this.

I have one more question regarding the transaction processing. In one of my interface i have to use
transaction processing and at the same time i need the object reservation. Can we use
theset two at the same time. i can explain you my requirement.

i have to send the sales order details to a legacy system and if all the orderlines
in the shipment are successfully processed then i have to move the sales order status.
So i have to reserve the object to stop others from modifying the order which my
interface has selected and at the same time i have to use transaction processing to
send all the order lines (or no lines if any failure).

The thing i am worrying about is will there be any conflicts between these two features.

Please let me know if you have ever faced this situation. Any help will be really helpful for me.


Thanks,
Hari

Hi Hari,

I have never faced the described situtation, so the only suggestions of mine are the following:

#1.: try to play Include Transaction option, when you call Object Reservation Business function and check the results after Commit and Rollback too
#2.: Try to play with Open for the Object Reservation table and/or combine it with previous.

Tell the truth, I am afraid, if you include the object reservation table into the transaction, the the other process won't be see the changes, until you commit the transaction, at it can cause problem. Maybe you should no include this table into the transaction.

Sorry, that's all.

Regards,

Zoltán

Zoltan,

i am not getting time to do this R&D, so just trying to know if some one have ever faced this scenario.

my interface has so many other complexities, i am updating multiple tables. (F4215, F4942, 4 of my custom tables used for this processing.) i have to include all this in transaction processing. i donno how many of them needs ot go into transaction processing......

its really a hard one for me... my interfaces is working fine without transaction and object reservation. donno what will happen if i start including them in my code.

anyway i have to include these two in my code, as they are really important and required.

i will let you know once i have completed this. I am confident that i can complete this successfully (i may face some difficulties..but in jde these thiings always happens)..

thanks for the info
Hari

TomAbbott said:

What, if any, harm is done by doing fetch singles, or select/fetch next loops without using the Table Open and Table Close commands? What do these commands do? When would you use them?
Thanks,
Tom

Click to expand...

Even these table Open and Close will be useful to go for advanced operations like Transaction Processing and Buffer Inserts. To facilitate Buffer Inserts functionality we have to open the table and in advanced option select "Buffer Inserts". It will be followed by Insertion and in the end closing the table will insert the data to destination table at a time and will flush the data stored in temp cache.

This is an old thread and has been answered before, but I'll reply my 2c again

I've done a lot of research into this and now I ALWAYS use Open and Close around my selects and Fetch Next

The reason? Memory

If you add a simple Fetch Single in your code and then look at you log it does open, select, fetch, close for you automatically

When you do a Select, the data has to be stored somewhere right? Well it's stored in the memory allocated to the job running. Be that BSFN, UBE, APPL.

You perform a select and the records come back into memory, you then do a fetch and the job starts to leaf through the records selected and saved into memory. (A little more complicated than that, but that's the gist of it)

So, if you don't close that selected data down, all those records stay in memory.

Now this maybe ok for UBEs etc that will usually end anyway (then all memory is freed) but if it's a subsystem or an application screen someone uses for hours on end, then it doesn't get cleared down.

It takes a few mouse clicks to do the Open+Close

Questions you should be asking is, why have JDE given us the option of using them if they are not needed

Yep, Select/Fetch Next has no implicit end point, so it won't give up the memory allocated until explicitly told, or end of job/section, which you can often get away with in a UBE.
Though in a UBE you shouldn't need to Select/Fetch Next very often, better to make a conditional section to handle it.

Handles can also save you some memory/performance/log entries if you are doing lots of fetches over the same table, to avoid the default coding repeatedly opening and closing the table between each.

Basically, Handles handle it better, but require a bit more set up, so the default stuff is ok for the most of the time laziness.

Does anyone have any documentation from oracle on this topic? I would like to know when it makes sense to use open and close and if they are not used in a select, fetch next scenario when does the memory get released? End of event? End of section? Close of form? Thanks for the help.

Not specifically, but check this

Tables/Views* (does not apply to jdenet_n processes) This report the number of JDB Table handles or JDB View handles opened in the EnterpriseOne server job. This resource is opened by calling JDB_OpenTable and JDB_OpenView API. The application developer should call JDB_CloseTable API to fix the table and view handle leaks. An increase in this value over time could indicate a leak that needs to be analyzed.

Copied from:
http://docs.oracle.com/cd/E17984_01/doc.898/e14718/troubleshoot_e1_server.htm

Thanks for the information. Does the open statement actually put a lock on the table as discussed earlier in the thread or is it just reserving memory? Thanks again.

I don't think it puts a lock on it no