|
midrange.com
At V5R2 (beginning at 5r1?) Query uses a data area for look up for limiters
and other default values. The system comes without this data area, so the
effective defaults are *nomax and *same.
Please read the email below before attemping the following example I did.
After creating the dtaara in QSYS (I wanted all users set up same) the
following commands were run to limit queries to 600000 records and increase
by 10,000 records up to 5 times if exceeded.
Notice that the record increment is limited to 5 characters, so a max of
99999.
CHGDTAARA DTAARA(QQUPRFOPTS (66 10)) VALUE('100005 ')
CHGDTAARA DTAARA(QQUPRFOPTS (56 10)) VALUE('600000 ')
Although I suspect I would be better off using a smaller initial file size
and make better use of the number of increments a filesize can be increased.
In other words, use an initial filesize of 10000, incremental increase(s) of
5000, up to 2000 times. Might be more efficient of system resources. But I
was in a hurry.
Comments?
Here is a copy of the email I received from Rochester last week:
=============================================
SA54605
Problem Summary:
When a query is submitted, the job queue associated with the job
description from the USRPRF is the job queue where the query will be
submitted. The user needs another way to direct a query to a specific job
queue.
Problem Conclusion:
Support for a new data area has been added to Query/400. The new data area
(QQUPRFOPTS) provides the user with Query user profile options. The
following user profile options are now available (the SBMJOB parameters
refer to the 8=Batch option from the Work with Queries display):
|------------------------+------------------------+------------------------|
|Purpose of User Profile |Position in Data Area |Options |
|Option | | |
|------------------------+------------------------+------------------------|
|*Reserved for later |1 |N/A |
|release | | |
|------------------------+------------------------+------------------------|
|RTGDTA parameter for |2 |'B' = QCMDB (QBATCH) |
|SBMJOB | |' ' = *JOBD |
|------------------------+------------------------+------------------------|
|OUTQ parameter for |3 |'J' = *JOBD (default) |
|SBMJOB | |'C' = *CURRENT |
| | |'U' = *USRPRF |
| | |'D' = *DEV |
|------------------------+------------------------+------------------------|
|PRTDEV parameter for |4 |'J' = *JOBD (default) |
|SBMJOB | |'C' = *CURRENT |
| | |'U' = *USRPRF |
| | |'S' = *SYSVAL |
|------------------------+------------------------+------------------------|
|INQMSGRTY parameter for |5 |'J' = *JOBD (default) |
|SBMJOB | |'R' = *RQD |
| | |'D' = *DFT |
| | |'S' = *SYSRPYL |
|------------------------+------------------------+------------------------|
|JOB parameter for SBMJOB|6-15 |10-Character name |
| | |associated with the |
| | |batch job. Special |
| | |allowed values are: |
| | | |
| | |*JOBD - The job |
| | |description name is |
| | |used. |
| | | |
| | |*QRY - The query |
| | |definition named is |
| | |used. |
|------------------------+------------------------+------------------------|
|JOBD parameter for |16-35 |10-Character job |
|SBMJOB | |description + |
| | |10-character library. |
| | |Special allowed values |
| | |are: |
| | | |
| | |*USRPRF - The job |
| | |description from the |
| | |user profile is used. |
|------------------------+------------------------+------------------------|
|JOBQ parameter for |36-55 |10-Character job queue +|
|SBMJOB | |10-character library. |
| | |Special allowed values |
| | |are: |
| | | |
| | |*JOBD - Use the job |
| | |queue found in the |
| | |current job description.|
|------------------------+------------------------+------------------------|
|Initial number of |56-65 |Any value from 1 to |
|records in the output | |2147483646, can be |
|file | |right- or |
| | |left-justified. |
|------------------------+------------------------+------------------------|
|Number of records per |66-70 |Any value from 1 to |
|increment for the output| |32767, can be right- or |
|file | |left-justified. |
|------------------------+------------------------+------------------------|
|Number of increments for|71-75 |Any value from 0 to |
|the output file | |32767, can be right- or |
| | |left-justified. |
|------------------------+------------------------+------------------------|
|USER parameter for |76 |'J' = *JOBD |
|SBMJOB (Added at Release| |' ' = *CURRENT |
|V4R5M0) | |Note: This is for |
| | |SBMJOB or Option 8 in |
| | |WRKQRY. |
| | | |
| | |Note: If you specify J |
| | |in position 76 and |
| | |*USRPRF starting in |
| | |position 16, these |
| | |options cancel each |
| | |other out and will have |
| | |no effect. |
|------------------------+------------------------+------------------------|
To enable the new user profile data area, the data area must reside in a
library contained in your current library list. If the data area
QQUPRFOPTS cannot be found for any reason, the default values are used. To
enable the user profile options, on the OS/400 command line type the
following:
QSYS/CRTDTAARA DTAARA(libname/QQUPRFOPTS) +
TYPE(*CHAR) LEN(80) VALUE(user profile option settings) +
AUT(*USE) TEXT('Query/400 User Profile Options')
-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx]
Sent: Monday, April 14, 2003 9:59 AM
To: Midrange Systems Technical Discussion
Subject: Re: Query Question
We have limited by group profile for just this reason. Yes, it will still
blow everyone out of accounting and payroll, but all of our manufacturing
entities will continue to function. And if they don't function, how do
you make the payroll? And it is a simple fix to temporarily fix the group
profile until you resolve the problem. And, a query which prints off any
user profile that has a limit, and what percent of that limit they are at,
is emailed to critical people on a weekly basis. Thus if payroll grows
during the year, we can adjust that maximum. We try to keep it around 90%
of that user profile (NOT TOTAL DISK). Therefore we can blow the runaway
query in a timely fashion. The last person to do so was the payroll
analyst, so don't give me that song and dance about keeping Query away
from the users.
Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin
"Bruce Barrett" <bruce.barrett@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
04/14/2003 10:42 AM
Please respond to Midrange Systems Technical Discussion
To: "Midrange Systems Technical Discussion"
<midrange-l@xxxxxxxxxxxx>
cc:
Fax to:
Subject: Query Question
We had a query run by our QA department that caused our development
system to crash on Friday night. The query created a file of over 100
million records and was 20% of the 1.3 terabyte of storage.
Is there a way to limit the size of a file a query can produce? We do
not know at this time, if the query was run in batch or interactively.
We can not limit the size by profile, due to group profiles.
Any help is appreciated.
Bruce Barrett
PMI Mortgage Insurance Co.
3003 Oak Road
Walnut Creek, CA 94597
925-658-6152
Fax 925-658-6412
Cell 510-520-4263
bruce.barrett@xxxxxxxxxxxx
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
