Using a vector index in a SQLRPGLE program

[brami06]

Working as a consultant and the client has files with millions of records. In one case, 130 million. Vector indexing seems that it would speed things up if I can get it to work. So I created a index over one of the files (only 13 million in this one).

Does anyone have an example of how this is needs to be coded in the SQLRPGLE program (if it is necessary to change the program at all)?

I did re-compile the program and saw no improvement. I then tried changing the program D spec and subsequent SQL statements to match the name of the index but that did not work (compile failed).

Any ideas?

Thanks for your assistance!

[kpmac]

Re: Using a vector index in a SQLRPGLE program

Unlike a logical you cannot force or specify which index your sql statement should use. When you issue your sql the optimizer evaulates your indices and logicals to determine the access path will provide the fastest results. It then uses said index to fetch the results.

It is my understanding that EVI's should be created on tables that are not frequently updated i.e INSERT, DELETE, UPDATE. And should be used on key columns that have few distinct values.

However, you should be able to see if your query is using the EVI's by running an explain plan via Visual Explain.

[DeadManWalks]

Re: Using a vector index in a SQLRPGLE program

brami06;
1) start the debugger
2) call your program
3) look at your job log for what the optimizer is saying about the sql statements.

You can make different logicals and run the program again with out recompiling to see if the suggestions make a difference.

DM

[arrow483]

Re: Using a vector index in a SQLRPGLE program

I have had pretty good success with some SQL performance issues. What version of OS are you on?

1. Try NOT to use any logicals or views in the query
2. Run the query thru Visual Explain (if V5R4) and build recommended indexes
3. Modify QUSRSYS/QAQQINI file to set IGNORE_DERIVED_RESULTS to *YES

The system will automatically try to use any logicals or indexes it finds. You need only specify the PF name in the query. If running under V5R4, it may improve substantially after the first run completes.