Performance improvement

[satya]

Hello Guys,

I have been given the task of improving one of the program which is running very long.

The program has a clp calling rpg and the ncalling another rpg to produce a report

The cl takes lot of time it includes OVRDBF,OPNQRY .

The qryslt parameter of opnqryf contains a variable of 2000 length ,which changes according to the input parameter

I was thinking of replacing the opnqryf with a view but view won't accept parameters. so i thought of going to a udtf to write the logic which includes couple of joins some grouping etc.

Is my approach correct ?Is there any different way to deal with these dynamic chaning qryslt parameter.?

[TedHolt]

The purpose of a view is to provide an alternate way of looking at data, Satya. I don't see how using a view would help performance.

I would look into creating appropriate indexes to support the QRYSLT expressions you use.

[satya]

Well index are one options we are not allowed to do.I ran a dbmon on the whole job and the particular opnqryf in conjunction with cpyfrmqryf is the culprit.then again I don't have any recommendations from the dbmon on what should be the order of index or keys.and doing functions takes a long time I was thinking of rewriting the whole cl in fuction or sqlrpg.

[B.Hauser]

Replacing OPNQRYF with a view won't is not a good decision.
BTW Views are unkeyed and if you want to read a view with native I/O you never know in which sequence your data is return, because it depends on which indexes are used.
You need change you RPG program useing embedded SQL
With embedded SQL Views can be used and it is also possible to add additional WHERE conditions for filtering the data.
Once you have changed your program to embedded SQL, you need to check wether ther right indexes are available or not.
Indeed you can run your SELECT statement separately and analyse it with Visual Explain wich also includes an Index Advisor

Birgitta

[B.Hauser]

Well index are one options we are not allowed to do.

Why? Because you cannot specify it in an SQL statement?
If you are not allowed to create indexes, you may stuck with your slow runnung OPNQRYF or SQL Statements.
BTW SQL indexes can be specified within the F-Specs of an RPG Program and handled like any keyed DDS described logical file.

Birgitta

[tomliotta]

Without appropriate indexing, it seems unlikely that major performance improvements will be found. It seems odd that the one thing that's most likely to help is not allowed. I don't recall ever hearing of a site that wouldn't allow indexes, though I've run across one or two that might have had too many.

However, for performance improvements that might be possible, it could help if we could see at least the CL that sets up and runs the OPNQRYF command. There might be changes that can be made to any OVRDBF commands for example. It might also help to include at least something about any files, such as number of records, record lengths, number of fields in a record, number of fields used in the programs and maybe more. It's hard to make useful suggestions without seeing anything.

[Rocky]

Well index are one options we are not allowed to do.I ran a dbmon on the whole job and the particular opnqryf in conjunction with cpyfrmqryf is the culprit.then again I don't have any recommendations from the dbmon on what should be the order of index or keys.and doing functions takes a long time I was thinking of rewriting the whole cl in fuction or sqlrpg.

A very odd restriction... I'd go into iSeries Navigator and look at the index advisor and see what indexes it recommends for that file... then if the index isn't allowed, create a LF that matches the keys... it's not as good as using an index but it should give an access path for the file.

[tomliotta]

Since a SQL INDEX is a LF, it might not quite be accepted.

But I like the concept!

[Rocky]

I understand from a different thread that the reason is not wanting to use any more disk space... an index is pretty small compared to the data. Perhaps take a look at other indexes and see if you could simply modify one of them for optimization....