I understand that it’s a system file and shouldn’t be touched.

The link answered some questions, but not every one.

Should I have this file in EVERY library that an SQL query may be accessing data?

Are there some other options that might be useful to change?

No, you do not have to have the file in EVERY library.

If you do not have a copy of the file in the library or schema, the defaults will be used.

If you do put a copy in your library or schema, you can modify the defaults that are used for that library or schema.

Offhand, I can’t tell you what you might want to change. Follow the info on the IBM page – there is a page that tells you what all the query options are.

Regards
Mike

To modify this value system-wide, CRTDUPOBJ and copy this file (make sure TRIGGERS(*YES) on the CRTDUPOBJ) and place the new copy in QUSRSYS. Leave the QSYS copy unchanged. If the value is missing, you can add it to this file. You can put an additional copy in your libraries, but that is not needed. It finds it using the library list, just like any other object. The only reason to do so would be if you had cases where you wanted different options.

Ok… Now I have another question regarding the copy of QAQQINI from QSYS to QUSRSYS….

The SYS library list is set up as follows:

QSYS
QSYS2
QHLPSYS
QUSRSYS

I would think that copying the file from QSYS to QUSRYSYS and changing the values there would accomplish nothing because it sees QSYS first and the first instance of QAQQINI is in QSYS.

Does IBM do a special “override” saying take from QUSRSYS first? Or, does our system library list structure need to change?

To be truthful, I have never tried the LIBL feature. I have done the CRTDUPOBJ, modified the QUSRSYS version, and saw a 400% improvement on large queries. I may be wrong about LIBL. You can override which qaqqini file is used by including CHGQRYA. I have never found any case where I needed to override the attributes, but some people certainly will.

Hi Graybeard52,

How can you check/know the performance improvement 400%? If i want to get the before and after for my production environment which having of 3000 over jobs.

Any recommend value to set for normal environment? I did referring to a site which having some recommendation value.

alexlex.lex@gmail.com

Does IBM do a special “override” saying take from QUSRSYS first?

Not exactly. QUSRSYS is the default location for a QAQQINI file. Prompt the CHGQRYA command to verify the default location on your system. I’m not aware of the QSYS version ever being used, unless perhaps you explicitly specify QRYOPTLIB(QSYS) for a job.

How can you check/know the performance improvement 400%?

Well, if a query originally took 5 minutes, a 100% improvement would seem to indicate that it now runs in zero minutes. A 400% improvement would put it at -15 minutes I suppose. I suspect that the improvement was probably 75%. (I’m kidding!)

In any case, review iSeries Navigator functions. Drill into {Connection}-> Databases-> {databasename}-> SQL Performance Monitor. Create one or more monitors, and track performance before making changes to QAQQINI. Compare against results after any changes.

Tom

Thank You Tom !

Going to implement this QAQQINI recommended attribute value into a test library today.. Good luck to me..

Attribute Name Recommended attribute Value
APPLY_REMOTE *YES
FORCE_JOIN_ORDER *YES
IGNORE_DERIVED_INDEX *YES
MATERIALIZED_QUERY_TABLE_REFRESH_AGE *ANY
MATERIALIZED_QUERY_TABLE_USAGE *ALL
OPEN_CURSOR_CLOSE_COUNT 4
OPEN_CURSOR_THRESHOLD 30
OPTIMIZATION_GOAL *FIRSTIO
PARALLEL_DEGREE *OPTIMIZE
REOPTIMIZE_ACCESS_PLAN *YES
STAR_JOIN *COST

OK the 400% was probably misstated. What I meant to say was that the queries ran in 1/4 of the time it used to. The 1 hour query dropped to 15 minutes, etc.

As far as your options go, I would question the *FIRSTIO option. That means that the system should get the first records as fast as possible, even if the query overall runs slower. If you are looking at only the first record(s), this may be what you want (for example interactive query). But if you want to return all the records in the minimum time, this is not the correct option here.