Table caching and Table Triggers

Hi All,

I have observed that if a Table Trigger (post Insert) runs, as triggered by inserting a record in an interactive session on a web server, the trigger code is seeing a cached copy of a table (F0006). The trigger is not seeing changes made to F0006 made by other users.

Clearing JDBi Service Cache from Server Manager does not resolve this problem. Stopping the Web Instance and restarting it does resolve the problem.

Is there a better way around this issue? I want to resolve this in PD without booting off the users on what is a 24/7 operation.
System is:
8.12 8.98.4.5 Oracle WEBLogic4 iSeries V6R1 DB2 for i

Thanks
JohnO

JohnO,

Have a look at E1: ENV: Frequently Asked Questions on Caching in EnterpriseOne [ID 944015.1] on the Oracle Support website. It's a very interesting and useful read.

It is indeed - thanks Peter!

But I couldn't see anything specific about how to refresh the cache my table trigger is seeing?

Cheers
JohnO

John,

The answer is ... don't cache table F0006.

From Oracle Doc 914015.1:
-----------------------------
...
Q25. Unable to see changes to tables F0006 and F0901 listed in P98613 after they are updated

Custom applications that write to F0006 and F0901 which are listed in P98613, do not show newly updated data after an insert/update. If these tables are removed from P98613, the new/updated data shows. Is there any impact to these tables (Ex: data integrity) if they are removed from P98613?

If these tables are being updated frequently (inserts/updates, etc.), then caching these tables is not recommended; which would mean deleting these tables from P98613. If these tables are not updated frequently, resetting the database cache after any changes to these tables would be required.
------------------------------------

I doubt that you'll see any user measurable performance difference by removing it from cache - based on our experience removing F0010 from cache.

John, Larry,

You can reset the database cache on the enterprise/logic server where it is cached (not in the JAS cache on the web) - see below.

I would guess there would be a significant difference in the sizes of F0010 and F0006 - there is in our database and I'm guessing that there would be a higher benefit from caching the F0006 than the F0010. Whether that difference in benefit would be significant - that would depend on the circumstance of the installation.

It is also worth noting that the F0010 is cached in both the database cache on the enterprise/logic server(s) and the JAS service cache on the web server(s).

From E1: ENV: Frequently Asked Questions on Caching in EnterpriseOne [ID 944015.1]

<font color="blue">
Q9. Is there a full list of tables for Database Cache and Service Cache?
Database Cache in Logic server and FAT client
.
.
.
F0006 Cost Center Master
.
.
F0010 Company Constants Database Cache & Service Cache
* In Xe and ERP8, this table is not a member of Database Cache
.
.
.


Q10. How to refresh/ reset/ flush Database Cache and Service Cache?

Refreshing/resetting/flushing cache can be vary, for example:

Database Cache
Log into a FAT/HTML or WebDev client
Fast Path to WSJ (Work with Submitted Jobs)
(Optional) if you have the enhancement bug 11734236 (Form Exit) Advanced (P986116)

Highlight your logic Server and from ROW menu click on Reset Cache
Select Database, then click OK. This will flush database caching

Update: EnterpriseOne Tools Release 9.1.2 and above - Two additional options are available to reset database table cache:

Option 2 - to reset Database Cache in Enterprise Server (through Server Manager)

Managed Homes and Managed Instances
(Combo Box) Select View > Clear Table Cache

vOption 3 - to reset Database Cache in Enterprise Server (through HTML client)

Reset Database Table Cache through P986116D
GH902 (EnterpriseOne Menus > EnterpriseOne Life Cycle Tools > Application Development > Reset Table Cache (P986116D))
</font>

Hi Larry,

I've long suspected this to be true, but never found anyone to agree with me!

Cheers
JohnO

Thanks Peter,

I've been warned off the Reset Cache option in WSJ by various CNC people, possibly out of religious fear of things that delete stuff.

But I'll give it a try on a test system and see if this affects the triggers.

Hey John,

The WSJ reset cache option should actually be called "reset server". It has the possible side effect on some tools releases of clearing the active job count for job queues. This can temporarily turn a single-threaded job queue into a 2-threaded job queue (1 active job now forgotten, plus a new job which is then allowed to enter the queue). I have never seen the behaviour documented but I have had it bite me severely once. I ended up with two sales update jobs running concurrently. Fortunately it was overnight with no users and I was able to do a point in time recovery on the database and rerun the nightly batch. This was on tools 8.96 so perhaps it is no longer an issue. It is probably less a religious CNC fear for me than once bitten twice shy.

Here is a thought - could you use the virtual table approach and direct your fetch for the F0006 information to another table, say F550006, that is really a DB view against the F0006. This should allow you to see the uncached F0006 as the F550006 will not be configured to be cached in the JDEBase middleware.

Yeah John,
the thing is ... now-a-day's caching happens on multiple levels
- application
- database
- OS
- controllers (hardware)
- physical drives
- quantum level - who knows?

Thats one reason why I believe if a small table is frequently accessed let the database handle the caching - its much smarter at it than any application layer caching anyways - as you've experienced.