as400 tables and views

I know what you're talking about--I develop .NET front-ends for the AS400 but I don't do anything on the AS400 itself.  The AS400 programmer gives me the file names to use--sometimes they're the physical file, sometimes they're the logical file.  Is there a reason why you would just query the logical file instead?

did you already try:
"select max(columnA) from table1 with nolock."
Of course, all depends on how the provider will interpret it. In any case, I do belive it is a matter related on how the  "Max" is done: seems as if it counts the rows one by one reading them, thus locking the entire table till it ends...Might it be?
 

mjfagan,
Not really, just curious.  Not sure why I get a file lock on both the physical and logical.  If I query the physical, I still get a lock on the logical if it has the same key.  Now if I query the logical, the physical gets a lock.  Seems odd.  I would prefer to only lock the logical or physical that I'm hitting.

...a shared lock on a logical that has the same key.

If the physical file actually has a key (are you sure that it does have a key?), do you know why the logical file was even created? Unless it provides a different view, it wouldn't seem to have any point in existing.

Regardless if they do have the same key, then it's almost guaranteed that the access path is shared between the physical and the logical. There will physically be only a single access path that is used by both the physical and the logical unless it was explicitly requested not to share.

Tom

@Tom:  I think "same key" is in reference to the column in the SELECT being the same as the key column in the LF, not the LF and PF having the same key.

For example my SQL is "select max(columnA) from table1."  If columnA is the key in a logical, I get a file lock on table1 and physical1

- Gary Patterson

@Tom, from my understanding, logical files get created because they're indexed differently.  The system that I frequently access, has lots of incidences where there's a physical file named "table" and then logical files named "table1", "table2" and so on and so forth.

See mjfagan, that's where my question is coming from.  say I have physical1 and logical1.  and my sql statement is "select columnB from physical1"   Now if columnB is the index on logical1, then they both get locked.  I assume it's because the 400 is passing the query to logical1.  So see if this is right.  Anytime I query a table on the 400, it's going to look to see if there is a logical with the index in my statement (which column I don't know).   So if I query the logical directly my return times are quicker, that I can prove.  So here is what I don't understand.......how does the 400 know if it takes longer to look for a logical that matches my query as opposed to just giving me my results from the physical?  See what Dave says is my question.  I would think that I could always just query the table and let the 400 send me where ever.  But my results are faster if I hit the logicals. I realize none of this may really matter, I just don't like it pawning me off to some other file.  If I wanted results from logical1, I would have asked it and not Table1.  :)

The whole way this got brought up was some program I had written was putting a lock on a logical I had never heard of.  I was telling our 400 guy I had never even heard of that logical, I even had to show him my sql statement.  It just kind of left me scratching my head.

DB2/400 has a component called the "query optimizer".  

The optimizer's job is to look at your query, and figure out the best, fastest way to solve it.  One of the things that the optimizer does is inspect all (or most) or the indexes over the table to see if any of them can be helpful.  If so, it will use it.

The DBMS doesn't "pass the query" to the logical.  The logical file is just an index over the physical file.  Logical files do not contain data, they just contain a list of keys, and a list of physical file record numbers.  A logical file (SQL INDEX or VIEW) is used in conjunction with the physical file (SQL TABLE) to quickly locate individual records (or ranges of records) by key.  Any time you use a logical file (other than just a simple "does it exist" check like an RPG SETLL), the system searches the LF index, and gets a record number.  It then retrieves the actual data for that record from the physical.

In general, you aren't supposed to specify a logical in a query: specify the physical and let the optimizer do it's job.  Specifying a logical cripples DB2/400 and forces it to use the old CQE that isn't nearly as fast for most queries.  The newer, faster SQE can only be used when the query contains only physical files.  

Occasionally you may find a specific cases where specifying a LF in the query does turn out to be faster, but even that may change over time as the file grows or index statistics change.  It may also be the result of running old or unpatched server side code.

- Gary Patterson

Wow, this is a lot of back-and-forth discussion for something that's really pretty simple.

First, never select from a logical. As Gary points out, query the physical and let the query optimizer determine what access path to use. It usually comes up with a better plan than we humans do.

Just remember that even if you query only the physical, it MAY read / lock an assoicated logical file if the query optimizer decides that using the logical life will make the query faster.

Usually, that results in faster / smarter performance.

HTH,
DaveSlash

Gee Dave, I guess I'm a little slow. :)  That makes sense.  I do have a couple of more questions, but I'll post these as new questions.

Thanks

Thanks Gary for sticking with me on this.

Happy to help.

@daveslash

First, never select from a logical.

My advice is similar, but not the same.  It is "Generally, don't select from a logical."  Only because, from time to time, you can get significantly better performance out of a query that specifies a LF instead of the physical, and sometimes the benefit might be worth it.  Sounds like bergertime may have run into such a case.

Let me give you a V6R1 example:  Let's say you have a large table that has only one logical over it, and it happens to be a Select/Omit logical.

PF1: Columns A, B, C
LF1: Key column A, Select only records where C = "Active"

This query:

Select Max(A) from PF1 where C = "Active"

Under V6R1, SQE will process this query and ignore the derived index (select/omit) by default.  As a result, it will probably perform a table scan, and read through every record in the file: thousands or even millions of I/Os for a large file.

Select Max(A) from LF1 where C = "Active"

If the LF was specified, however, this will force the query to use CQE, which can use the select/omit logical, and can find the record with a single index (LF) lookup followed by a single table (PF) row lookup.  Much faster and easier.

Of course, there are ways to get around specifying the LF and still improving performance: create a new index, but the example is just for illustration purposes.

http://www.itjungle.com/fhg/fhg041608-story01.html

Now, don't get me wrong: the standard should be to use the PF in AS/400 SQL, but I do thing that, especially in legacy systems containing a mixture of DDS and SQL, there are times that it may make sense to make an exception to the rule.

- Gary Patterson