Dynamic Database Caching in EnterpriseOne

Purpose of Document

This document is to explain enhancements made for Resetting Database Table Cache Using a Pre-Configured Application which is available as of EnterpriseOne Application Release 9.1 Update 2 and above. If your application meets this update, you can reset database cache per table depending on your business requirement.

• For general information on JDB Caching, refer to <Document 944015.1> E1: ENV: Frequently Asked Questions on Caching in EnterpriseOne.
• Information can be found in various sections of Oracle Documentation as follows:
  
  • JD Edwards EnterpriseOne Tools System Administration Guide Release 9.1.x
    • 27 Understanding Dynamic JDB Table Cache Refresh (Release 9.1 Update 2)
      • Section 27.1, "Understanding Dynamic JDB Table Cache Refresh"
      • Section 27.2, "Resetting Database Table Cache"
      • Section 27.3, "Resetting Database Table Cache Using a Pre-Configured Application"
      • Section 27.4, "Enable/Disable Clear Cache Applications Using UDC"
  • JD Edwards EnterpriseOne Tools Runtime Administration Guide 9.2.x
    
    • 23 Understanding Dynamic JDB Table Cache Refresh
      • Section 23.1 "Understanding Dynamic JDB Table Cache Refresh"
      • Section 23.2 "Resetting Database Table Cache"
      • Section 23.3 "Resetting Database Table Cache using a Pre-Configured Application"
      • Section 23.4 "Enable/Disable Clear Cache Applications Using UDC"

This document covers:

• Manual resetting of database cache per table level through P986116D (Reset Database Table Cache)
• Automatic resetting of database cache per Pre-Configured applications

Dynamic Cache feature is to work only when,

• Application release 9.1 and above
• Tools Release 9.1.4.0 and above (FLUSH_CACHE_ENABLED)
• Individual ESUs are in place (or Update 2 are installed)
• TBLREFR is enabled through P99410 (Work with EnterpriseOne System Control)
  
• Special handling code of 2nd digit is 1 for UDC 00/RF for a specific application and table combination (Turn it on dynamically after determining your business requirement)

Purpose:

To minimize possible dirty reads (a fetch before modified data when update is performed) or phantom reads (fetch deleted data when delete is performed) by flushing/deleting/resetting database cache per table against data source mapped.

List of Pre-Configured applications:

UDC 00/RF - Table Cache Auto Refresh

The following applications do not belong to UDC 00/RF but they have implementation for Clearing Table Cache:

Example of implementation (Case of P400951 (Default Location & Printers))

The Applications listed above have more of less same routine as below,

Business Function - Example of B4098610

Business Function B4098610 is made up of the functions below:

• IsClearTableCacheEnabled_40 (Is Clear Table Cache Feature Enabled - 40)
• ClearTableCache_40

IsClearTableCacheEnabled_40 (Is Clear Table Cache Feature Enabled - 40)

1. Check the Tools Release to determine FLUSH_CACHE_ENABLED (which is defined in JDEKDFN.H on your system directory). Tools release must be 9.1 and above.
2. If 1 is TRUE then call business function GetEnvironmentValue with input parameter TBLREFR (=F99410.DTAI) to determine whether this module is enabled (F99410.MEOW = 1) or not (F99410.MEOW = 0)
3. To read special handling code of UDC 00/RF based on Application Name then call business function GetUDC and return cClearTableEnabled:

Data Structure for D4098610B (Clear Table Cache Enabled - 40)

ClearTableCache_40

1. Repeat the validation above again
2. Call JDB API JDB_ClearTableCache(lpDS->szTableName) based on input table name

Data Structure for D4098610A (Clear Table Cache -40)

Currently JDE.LOG can contain the 3 messages below (which replaces cClearCacheSuccessful):

• Clear Table Cache(TBLREFR) is enabled in System Control File - F99410: This message is always present so long as you activate TBLREFR
• Called JDB_ClearTableCache for F40095 table. Success: This message appears only when JDB_ClearTableCache is SUCCESS
• Called JDB_ClearTableCache for F40095 table. Failed: This message appears only when JDB_ClearTableCache is FAIL

List of Database Cache for comparison

The table below shows the relationship between P98613 (Database Caching) and Database Table Cache Using a Pre-Configured Application:

How to read:

Full List:

Question and Answers:

Question 1: In clearing table cache through P986116D, how does it determine the data source?
Answer 1: Environment name has to be stored during start up and/or initializing any kernels on the Enterprise Server. In order to run any application per kernel 'User (hUser as handle/pointer)', Env (hEnv) and 'Role' must be initialized. Subsequent processes can make use of it.

In running application (or session) it calls BSFN jdeInitEnvBSFN which the calls api GetEnvironmentName() and returns (HENV hEnv) so for this example the environment name selected during login to E1 has to be the environment for P986116D. We define HENV the environment handle contains information related to the current database connection and valid connection handles. Every application connecting to the database must have an environment handle. This handle is required to connect to a data source. Assuming that database cache is "holding" a data source the session knows which memory to flush.

Possibly through the Server Map definition in the JDE.INI and bootstrap (since both F98611 (Data Source Master) and F986101 (Object Configuration Master)) are server map and bootstrap tables, JDE knows the environment and data source relationship.

In general, we can flush cache through JDB API JDB_ClearTableCache(). Since any access to DB follows (JDB APIs) JDB_InitEnv() -> JDB_InitUser() -> JDB_InitBhvr()... hEnv retrieved in application P986116D has to be used.

This is why we need to specify environment when we try to reset database cache through Server Manager because Server Manager does not have instances per environment.

Question 2: Can we add additional application through UDC 00/RF?
Answer 2: No. As explained above, clear cache is implemented by listed application. Note that some of UDC code may not be available. In case the special instruction for your ESU does not contain instruction on adding UDC, add it manually. Note that though you add some UDC code, so long as a certain table is not a member of F98613, it shall not function because there is no database cache to flush. In case you have modified P98613, you need to restart JDE service to make change effective.

Question 3: What is values to populate for DD Alias TBLREFR (TableCacheAutoRefresh)?
Answer 3: In case you do not follow special instruction this action can be done after package deployment.

Go to P92001 (Work with Data Item) and populate controls below,

• Data Item: TableCacheAutoRefresh
• Glossary Group: D (Primary Data Elements)
• Description: Use Table Cache Auto Refresh
• Data Type: 1
• Size: 1
• Control Type: 4 (Generic Edit)
• Product Group: 98
• Product Code Reporting: 98
• Row Description: Use Table Cache Auto Refresh
• Upper Case Only
• Item Glossary: Answering YES to the question indicates that you are using this feature