Chapter 41. Look-ups

Logic Table

Although we’ll execute all three views, we’ll only show the logic table for our new view. Thus the rows of the logic table continue from the prior example.

Figure 102. Lookup Logic Table

Figure 103. Simple Look-up Functions

The Reference File Phase process prepares the in-memory reference file table, and is discussed in the next chapter.

Extract Phase

The following is the trace output for only those rows applicable to our new view.

Figure 104. Lookup Logic Table Trace

Note that we can see that the key values supplied by the event file for the first LKE is “522349999”. The LUSM was performed, and because the next executed logic table row is 26, the results of the LUSM were found. In other words, the reference file had a record with the key of 522349999. The CFLC function compares the constant of “K & N jones” to the value on the found looked-up record, which is also “K & N jones”.

On this first event record, two other lookups and compares are also performed, until we get to logic table row 34. At this point, the trace for view 3263 against record 1 stops, and we go on to event file record two (we have omitted the trace rows for view 3261 and 3262 in this printout which would have been rows 1-21). This tells us that the view did not meet the general selection criteria.

If ({EVENT_JOURNAL_TO_LE_DESC.LEGAL_ENTITY_TITLE}= "K & N jones family" 
  And {EVENT_JOURNAL_TO_CC_DESC.COST_CENTRE_TITLE} = "DAD"
  And {EVENT_JOURNAL_TO_ACC_TITLES.ACCOUNT_TITLES} = "checking account")Then
    SKIP
EndIf

The “Skip” statement is what causes the record to be skipped. The lookups resulted in values of “K & N jones family”, “DAD” and “checking account”. The skip statement causes the GOTO True and GOTO False rows to be swapped on the CFLC function for the Account Title. Typically the true row is the next logic table row, and the false row is the a few rows later. Because of the skip statement, they are reversed.

The following are the output records for this view. The values in these output records can be seen in the logic table trace for DTL and DTE functions.

Figure 105. Lookup Extract Only View Output

Multi-step Look-up

The test I was performing that day which Doug found fairly complex was a multiple step lookup. That means that the values for a lookup can come from a prior lookup, rather than all having to come from the event file.

Figure 106. Complex Look-up Functions

LKE function becomes a LKL function: Build Lookup Key from Looked-up value. CFEL functions can similarly become CFLL to compare two looked up values.

Date-effective Joins

You’ll remember we considered the needs for date-effective joins when using the SAFR method¹. Let’s examine how date effective joins are implemented in the SAFR process.

The date field on the target reference table is technically not part of the key because the values passed in for the search do not have to be matched byte for byte. So the effective date field on the target logical record could be thought as being between the key and the data areas of the record.

Although technically not part of the key, the logic table contains LK functions, specifically Lookup Key Date or LKD functions, to place the desired effective date in the lookup key buffer. And, predictably, these are the last LK functions for a particular join.²

The dates can come from constants, LKDC. The date can come from the event file, LKDE, or from a looked-up value on another reference table, LKDL.

The constant value on the LKDC can come from multiple places. For example, in a view definition someone may place a hard-coded date, like December 31 of some particular year. Alternatively, the developer might also use a keyword in the workbench, such as run-date, when specifying what date to use. This keyword is translated into a specific date constant during the logic table phase of processing. By the time the logic table arrives at the extract process, it is indistinguishable from a hard coded date. The translated constant date value is shown in both the logic table print and the logic table trace. Use of the key words allows for dates to be passed in as part of the batch process; thus the View logic remains the same day after day, but the date used in the view can vary.

When the extract engine performs a search on a date effective table, it will “fall back” to the record with a matching key, whose effective date is less than or equal to the value provided by the LKD function. If there is no record with an effective date less than or equal to the effective date, the extract engine will execute the “not found” condition.

Join Optimization

I think the idea was probably Jay’s, but I don’t think I know for sure where it started. It was that we could add another step into the SAFR Scan engine process to analyze the logic table and remove certain joins which had been performed by a view earlier in the process. For example if view 3261 did a join to the account master table, and 3262 required the same join, there would be no reason to go build the look-up key again; the results of the join were already known and the results of the join could already be known.

It was such a simple idea, but the word “simplistic” might be a better description. It started a process of work that continued part time for probably a year. We kept running into technical problems every step of the way. When we finally got it to work, the results ended up being remarkable. As we put the new process into production after making the code permanent and more testing, we found that we could reduce the CPU time on some already very efficient, join intensive processes by 60 and 70%. It was nearly unbelievable.

The result is a process whereby the more views that are resolved in one SAFR execution, with greater numbers of lookups to shared table, the greater the efficiency is for each join.

Next, let’s consider how the data is prepared for joins by understanding the Reference File Phase.