INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How do Logical Files Work

How do Logical Files Work

(OP)
I have a question about Logical Files and indexes.

My Phyisical file with 200,000 records and has 70+ fields one of the fields is numeric.

I create a SQL statement to view all the fields of the records where the numeric field is equal to 0.

The SQL takes a while.

I need to know how to speed this up.

I think I am supposed to create a Logical File with the numeric field as key. My question is, does the Logical File need to include all of the fields or only the numeric field to cause the SQL to optimize.

RE: How do Logical Files Work

You can create an index file which index is the numerical field you need.
Or you can create logical file which contains only some fields (from the 70+) you need and which has an key - your numerical field.

You have not specified on which platform you are. What I said works on iSeries (aka AS/400).
The logical file is similar to SQL view, so it doesn't need to include all fields from the physical file.



  

RE: How do Logical Files Work

(OP)
I am working on iSeries(As400).

Again though so I understand. If I have a LF with one field in it and set it as key. Remember it is based on a PF with 70+ fields.

If I run a SQL statement on the PF(all 70 fields) and the where portion of this SQL is the field used in the LF(the only field it contains) will my SQL run optimized?

RE: How do Logical Files Work

Logical file with one field? As I said logical file is like an SQL view.
So, the purpose of logical file is to get some fields from physical file and get these field in sorted order according to the keys you defined.
Logical files on iSeries are coded in legacy DDS (Data Description Specification), not in SQL.

Example: Given is a physical file with N-fields

CODE

CREATE TABLE mylib/mypfile (                               
FIELD01    DEC ( 7, 0) NOT NULL WITH DEFAULT,              
FIELD02    DEC ( 2, 0) NOT NULL WITH DEFAULT,              
FIELD03    DEC ( 3, 0) NOT NULL WITH DEFAULT,              
....
...
FIELD0N    CHAR (8)    NOT NULL WITH DEFAULT FOR SBCS DATA)

To get only view with FIELD01 and FIELD0N sorted according to the FIELD03 you can create logical file (coded in DDS):

CODE

                R mylfile                   PFILE(mypfile)
                  FIELD01
                  FIELD03
                  FIELD0N
                K FIELD03

If you want only speed up your SQL select sorted according to FIELD03, then define simply an index file with your key field such as

CODE

CREATE UNIQUE INDEX                    
mylib/myidx ON                     
mylib/mypfile                        
(FIELD03)

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close