Re: ECH / ECL Indexes

In a message dated 7/20/99 6:01:37 AM Central Daylight Time, 
mark.curtis@hafele.co.uk writes:

> 6.0.04 Sept 98, HP-UX / Informix
>  
>  I would like to add an index to ECH and ECL on hid and lid followed by
>  various other fields.
>  
>  I have added a number of indexes to BPCS tables before and achieved in some
>  areas significant performance improvements.
>  
>  I am however nervous about adding these as they seem obvious ones to exist
>  as standard (to aid views ecll01 & echl01) but are not there (when you use
>  ecll01 or echl01 the engine performs a sequential read of the tables to
>  filter for hid and lid)
>  
>  Does anybody know why such indexes do not exist ?
>  Has anyone added these indexes (successfully or otherwise) ?

I am not certain how this works on Unix databases, because I am an AS/400 
type - however, I do know the answer to this same question in AS/400-speak. 

On AS/400 ECHL01 not only orders by HID but also Selects out only records 
where HID = a particular value for signifying an active record. In fact, one 
of the most commonly added logicals/indexes to base BPCS 6.x on AS/400 is an 
ECH logical keyed by HID, with no 'Select/Omit' criteria (as they call it on 
AS/400) which selects only records with HID= 'valuexyz' (can't remember 
actual value - sorry). This Select/Omit is a construct of what DB2 allows in 
logical views. It is accomodated in the ADK data model set up, so ADK 
generates the source for the AS/400 file with these attributes. 

So, if your statement in question in SQL says "SELECT .... from ECH WHERE 
HORD=variable and has an ORDER BY HID, blah blah - but never says in the 
WHERE clause, WHERE HID='valuexyz' for active records, then ECHL01 will 
naturally not be used by the optimizer. This is because ECHL01 is too 
selective to fit the SQL statement's potential selection - the statement is 
written to consider all records regardless of HID value, but ECHL01 does 
indeed care about what is HID's value.

I don't know what happens to this file when transferred to a Unix database. I 
have tried asking some Unix-types I know, but they are confused by the AS/400 
words I use to describe this. If anyone knows the answer, I would love to 
hear it, out of curiosity. Are Select/Omits retained in Unix logical views?

Now, it may in fact be the case that a better statement for executing the 
program's business logic would be to say "WHERE HID='valuexyz' for active 
records, because logically the program only needs to view active records to 
accomplish the selection task at hand. But the AS/400 is not a mind reader, 
and has no way to know that the only possible place where HORD would match 
the program variable is also where HID=xyz. In such a case, it is likely that 
the programmer also did not think of this fact as they wrote the program. As 
humans, programmers are known to make these sorts of errors. If the same 
human happens to program an entire set of similar program code, it is also 
likely that this human perpetuates that logical error in many places in said 
code. It doesn't prevent the program from completing a proper record 
selection, but it sure makes it take a heck of a lot longer to do so. 

Solution? 
1. Build yourself a new ECH index keyed only by HID (for this example) so the 
optimizer doesn't have to do this itself 
OR 
2. Find all program code where it should select only Active records (by HID) 
in the ECH file and change each SQL statement in the code to state this 
programatically in the WHERE clause yourself, so the optimizer will choose 
the existing ECHL01 instead. Or phone in BMRs to Helpline for each program 
statement in error. In each case, you will have to ensure that selecting only 
Active records in the SQL of ECH is proper business logic. 

Obviously option 1 is faster for you to do and will solve 99% of your 
problems for selection of this databasetable/field combination very very 
quickly. Option 2 should be considered if you have time to deal with reading 
all the SQL code that does reads on ECH . . . 

In version 6.1.00, SSA simply delivered this new logical file, if that gives 
you an idea of how many places you might have to look to resolve it in the 
code (and who knows what else you might accidentally muck up in the process), 
and of the relative safety and ease of doing this yourself via just adding a 
new index.

I think there is a BMR on this somewhere on OGS if you search on ECH and HID 
or something like that in 'D' BMRs.

+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---