as400 tables and views

So I'm using the .Net datas provider that came with client access to write windows programs against out 400.  If I query a physical file I will also get a shared lock on a logical that has 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.  It's almost like it looks like it's passing my to the logical, but my query times don't reflect that.  Any ideas what I'm talking about?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Molly FaganApplications Team SupervisorCommented:
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?
Dave FordSoftware Developer / Database AdministratorCommented:
In the SQL world, you always query the table (a.k.a. physical file), and the SQL optimizer decides what access paths (indexes) to use. Therefore, if it determines that using an existing index (a.k.a. logical file) will give better performance, it will use it to satisfy the query.

In the SQL world, you never query an index (logical-file) directly. You only query the table (physical-file) and let the SQL optimizer decide whether or not to use an index.

In your query, since you're asking for a MAX over a column in table1, it's pretty easy to imagine that the index (LF) over that column will get the best performance.

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

bergertimeAuthor Commented:
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.
Gary PattersonVP Technology / Senior Consultant Commented:
This sounds like perfectly normal behavior.  

The system has to get a shared lock on both the table (physical file) and the index (all indexes and views that are set up for immediate maintenance, really) in order to add the new row to the table, and a new entry to each index or view.

When you execute a query with a WHERE or JOIN clause on DB2/400, the DBMS examines available indexes (logical files) and determines which (if any) is the best to use to access the file.  If no acceptable index is located, the system may create a dynamic index, perform a table scan, or use other methods to access the table.

When you issue write operations (INSERT) or DELETE operations against a table (physical file), the system must also temporarily lock each index in order to add or remove index entries.  UPDATEs may also require index locks if an index update is required as a result of the operation.

The nature and duration of the locks depend on what commitment control level is used, what operations are performed, and the specific query being executed.

You can use Navigator's Visual Explain tool to get an explanation of the access plan that was selected.  Also, you can get a lot of information by starting debug in a green-screen session (STRDBG <enter>) and executing the query under STRSQL.  Then review the job log and look at the detailed messages regarding optimization. (DSPJOBLOG).  Put the cursor on each message and press F1 for more details.

- Gary Patterson

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
...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.

Gary PattersonVP Technology / Senior Consultant Commented:
@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

Molly FaganApplications Team SupervisorCommented:
@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.
bergertimeAuthor Commented:
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 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.  :)
bergertimeAuthor Commented:
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.
Molly FaganApplications Team SupervisorCommented:
I just went and asked the AS400 programmer here.  He explained that the logical files are simply index files and that the 400 will determine, based on the query, which of the logical files to use if any of the indexes are part of the query, thus locking them both in your case.
Gary PattersonVP Technology / Senior Consultant Commented:
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

Dave FordSoftware Developer / Database AdministratorCommented:

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.

bergertimeAuthor Commented:
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.

bergertimeAuthor Commented:
Thanks Gary for sticking with me on this.
Gary PattersonVP Technology / Senior Consultant Commented:
Happy to help.
Gary PattersonVP Technology / Senior Consultant Commented:

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.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.