× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi, Buck

Thanks for the test. I hope I didn't come across too testy (so to speak)
when I threw down that gauntlet. Must be this cold.

At 07:15 PM 12/1/1997 -0500, you wrote:
>>>Okay, look, here's my question. If I issue and SQL request against a
logical
>>>and the logical is built with select/omit criteria, will the records
>>>returned be filtered by the s/o criteria? 
>>>
>>>I have always counted on this for query, but I don't know if SQL behaves
the
>>>same way. I would expect it to, since I might give access to a logical for
>>>purposes of security.
>>
>>AFAIK, the records are filtered by the S/O. Now, are we all (including me)
>>just talking through our hats, or has anyone actually tested this? I'm home
>>with a cold-can't do anthing right now-but I'll be glad to report on a
>>_real_ test when I got back to work.
>
>OK.  Here's the set-up:
>Physical file MASTER keyed by PEXC, PNUM, PSUF.  Record count 13,390.
>Logical file BILLPRT keyed by ZIP, BTEXC, BTNUM, BTSUF, PEXC, PNUM, PSUF
>                   SELECT TYPE NE 'C'
>
>If I run this statement: select count(*) from master
>I get this answer: 13,390  
>
>If I run this statement: select count(*) from billprt
>I get this answer: 13,322
>
>If I run this statement: select count(*) from billprt where type = 'C'
>I get this answer: 0
>
>In all these interactive statements, I get the following message in debug:
>Arrival sequence access was used for file xxxxxx
>
>I ran this one: select * from billprt order by pexc,pnum,psuf
>Got this count: 13,322 (I scrolled to the end of the list)
>Got this message:
>                         Additional Message Information
     
>
     
> Message ID . . . . . . :   CPI4321       Severity . . . . . . . :   00
     
> Message type . . . . . :   Information
     
> Date sent  . . . . . . :   12/01/97      Time sent  . . . . . . :
18:59:42   
>
     
> Message . . . . :   Access path built for file BILLPRT.
     
> Cause . . . . . :   A temporary access path was built to access records
from   
>   member NOV of file BILLPRT in library TELBASE for reason code 1. This
     
>   process took 0 minutes and 1.7 seconds. The access path built contains
13322 
>   entries. The access path was built using 0 parallel tasks. A zero for
the    
>   number of parallel tasks indicates that parallelism was not used. The
reason 
>   codes and their meanings follow:
     
>     1 - Perform specified ordering/grouping criteria.
     
>     2 - Perform specified join criteria.
     
>     3 - Perform specified record selection to minimize I/O wait time.
     
>     The access path was built using the following key fields. The key
fields   
>   and their corresponding sequence (ASCEND or DESCEND) will be shown:
     
>     PEXC       ASCEND,  PNUM       ASCEND,  PSUF       ASCEND.
    
>     A key field of *MAP indicates the key field is an expression (derived
    
>   field).
    
>     The access path was built using sequence table *N in library *N.
    
>     A sequence table of *N indicates the access path was built without a
    
>   sequence table. A sequence table of *I indicates the table was an
internally
>   derived table that is not available to the user.
    
>     If file BILLPRT in library TELBASE is a logical file then the access
path 
>   is built over member NOV of physical file MASTER in library TELBASE.
    
>     A file name of *N indicates the access path was built over a
temporary    
>   file.
    
> Recovery  . . . :   If this query is run frequently, you may want to
create an
>   access path (index) similar to this definition for performance reasons.
    
>   Create the access path using sequence table *N in library *N, unless
the    
>   sequence table is *N. If an access path is created, it is possible the
query
>   optimizer may still choose to create a temporary access path to process
the 
>   query.
    
>     If *MAP is returned for one of the key fields or *I is returned for
the   
>   sequence table, then a permanent access path cannot be created. A
permanent 
>   access path cannot be built with these specifications.
    
>     For more information, refer to the DB2 for OS/400 SQL Programming
book,   
>   SC41-5611, or the DB2 for OS/400 SQL Reference book, SC41-5612.
    
> Technical description . . . . . . . . :   Parallelism can only be
implemented 
>   when the system feature DB2 Symmetric Multiprocessing for OS/400 is
    
>   installed on the system.
        
>
>On my machine at V4R1, SQL used the logical I specified, and it obeyed
>the select criteria in use.  If there's another set of circumstances you'd 
>like me to test with, I'd be very happy to do it: I would have been
>shocked to the core if I did a SELECT from the logical and got all the
>records, under any circumstances!

Looks like it had to create a new access path, since the ORDER BY you used
is deep into the logical's key.

What results do you get if you specify the WHERE clause with the physical,
setting the same criteria as the S/O above?

select * from master where type <> 'C' order by pexc,pnum,psuf

Cheers

Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN  55401-2499
(612) 371-1111 x480


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.