Re[2]: SQL select via logicals

[Buck Calabro <mcalabro@xxxxxxxxxxxx>]

>>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!

Buck Calabro
Commsoft

+---
| 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
+---