newbie question: index on DB2 file

[matt.stucky]

sweet merciful crap!

that did it.

however, it is still like a view in that i can call it with a select statement: "select * from didata/empLf". which i then could use in an SQL statement.... QUESTION: since this was not created with the CREATE INDEX statement, does DB2 still know to use it for a join on the K column (birthdate) or do i need to call the LF explicitly?

and 14=compile is the same as the CRTLF command. ok that helps a lot.

thank you for the help!!

matt

[jamief]

If using RPG you have to use logical

SQL may or may not pickup on the logical if the file was already opened..... Best bet would be referencing the logical directly...


But if you are using SQL Y dont you just select the sort by birthday???



jamie

[arrow483]

LF

Yes, it will use it as in index.

The S for select is also available if you want to select/omit records. You need to add criteria under "Functions" to make that feature work.

S SALARY COMP(GT 500)



Then the index includes only salary > $500. This is more like the where clause in SQL (and not SELECT as one might think).

Of course, if the SQL where doesn't match this selection, now the LF may become unusable by SQL, because it no longer includes all records.

[matt.stucky]

i see, said the blind man

yes making much more sense now.

to jamie's question, 2 posts previous: I don't necessarily want it SORTED by birthdate, i want other functions that depend on the bdate to operate faster. such as a SQL join on a corporate calendar, or perhaps a "select * from whatever where birthdate>YYYYMMDD" or such. or, if I have, say 10Million records, and I say "select * from whatever order by birthdate" then the query should be much faster using the logical because it has already indexed the rows by birthdate. the database can go directly to the logical and start returning records rather than scan the entire 10 mil rows.... then start returning records.

nonetheless thank you all very much!!

matt