QAQQINI File

470 pts.
Tags:
Database
iSeries
QAQQINI
SQL
V5R4
What is the QAQQINI file? Does it have to be in every library? I saw someone mention ignore_derived_index and setting it to yes. My files don't even have that value. I have the file in QSYS, QUSRSYS, and one of our test libraries. Are there any other options I should worry about? Currently, any option in that file is set to *DEFAULT. I'm running v5r4.

Answer Wiki

Thanks. We'll let you know when a new response is added.

qaqqini is a system file, the copy in QSYS should not be deleted or modified

for more information see the following link

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq/createqaqqini.htm

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Dcantwell
    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?
    470 pointsBadges:
    report
  • mcl
    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
    2,740 pointsBadges:
    report
  • graybeard52
    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.
    3,115 pointsBadges:
    report
  • Dcantwell
    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?
    470 pointsBadges:
    report
  • graybeard52
    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.
    3,115 pointsBadges:
    report
  • Alexlex
    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
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • Alexlex
    Thank You Tom !
    50 pointsBadges:
    report
  • Alexlex
    Going to implement this QAQQINI recommended attribute value into a test library today.. Good luck to me.. :D 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
    50 pointsBadges:
    report
  • graybeard52
    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.
    3,115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following