Query Enhancements Implemented Using Special Data Areas

Resolving The Problem

Several new functions have been added to IBM OS/400 or IBM i5/OS query functions using data areas. This is a summary of the functions provided. The appropriate APAR and PTF cover letters follow.

APAR	Release	Data Area	Function Deals With:
SA19117	210	QQULCKLVL	*SHRNUP lock during query definition
SA21643	210/211	QQUCURRENT	Submitting queries with *CURRENT information
SA22053	211/220	QQURUNOPTS	Pre-211 style format level IDs
SA27876	220	QQURUNOPTS	Direct mapping of numeric field data, decimal data errors
SA21451	210/211	QQUCHKIRUN	Limiting interactive queries
SA32441	220	QQUSAVOPTS	Allow user to manage query optimization for save query definition operations.
SA47749	310	QQQOPTIONS	Allow user to control key range estimates performed during save query definition operations.
SA54605	310	QQUPRFOPTS	Process queries using user profile assignments, such as job queue and output queues and limit output file size.
SE49086 SE49224	610 710	QQURUNOPTS	Force Query to use the QDT saved with the QRYDFN when possible.

SA19117

Problem Summary:

When, in the definition mode of QUERY, the referenced file(s) have a *SHRNUP lock on them, applications are prevented from adding members to the file and otherwise updating the file object. There is no effect on updating the data in the file. The purpose of this APAR is to provide a new feature to change the type of lock put on the file.

Problem Conclusion:

QRY is changed to allow the default type of lock for input database files to be set to *SHRRD.

If this support is not enabled, QRY continues to apply the same lock level to the input database files as it did prior to applying this PTF.

To enable support to have QRY default to use *SHRRD locks on the input database files, on the OS/400 or i5/OS command line type the following:

QSYS/CRTDTAARA DTAARA(QUSRSYS/QQULCKLVL) TYPE(*CHAR) +
LEN(4) VALUE(LSRD) AUT(*USE)

Press the Enter key. QRY examines the contents of the data area named QQULCKLVL in library QUSRSYS to determine the lock level to apply to the input files for the QUERY definition during definition time activity.

LSRD - Causes a *SHRRD lock to be put on the files.
LSRO - Causes a *SHRNUP lock to be put on the files.

Any value other than one of the above values results in the same *SHRNUP lock as in the past. Similarly, any problems encountered by QRY trying to access the data area causes the *SHRNUP lock to be applied (for example, if the data area does not exist).

SA21643:

Problem Summary:

If a user profile has the Print Device parameter (PRTDEV) set to *WRKSTN or the output sent to an OUTQ, then any Query/400 query sent to batch will send the output to the system printer. This is working as designed per APAR SA20496. However, the customer would like to be able to take advantage of the *WRKSTN support provided in R210 through the submit query run function in QRY.

Problem Conclusion:

An enhancement will be made to QRY to enable the *WRKSTN support for submitted query run requests (8=Submit run from the Work with Queries display). The support is limited in the System/36 environment in the same way a SBMJOB CMD(STRS36PRC) function is because this is the QRY operation when Work with Queries is reached from the QRY procedure in the System/36 environment.

With this PTF applied, the enhancement to the QRY licensed program product can be enabled by creating a data area. The data area is checked at initiation of Work with Queries for both the command WRKQRY and the procedure QRY. If the data area cannot be retrieved at that time (for any reason), the operation of the submit run function from QRY will operate the same as prior to this enhancement. If the value of the data area indicates to do so, the SBMJOB command parameters that allow a value of *CURRENT are defaulted to *CURRENT for submitted query runs. To enable the support, on the OS/400 or i5/OS command line type the following:

QSYS/CRTDTAARA DTAARA(QUSRSYS/QQUCURRENT) TYPE(*LGL) LEN(1) +
VALUE('1') AUT(*USE) TEXT('Query/400 SBMJOB parm(*CURRENT) support')

Changing the value of the data area to 0 or making the data area irretrievable disables the support.

SA22053

Problem Summary:

After upgrading to R211 from R210, several queries fail with MSGQRY1058 Level for file &3 in library &4 does not match query. Taking the option to ignore (I) allows the query to complete successfully. In addition, if the query is changed and saved, it runs without errors. The queries and the files have not been changed. In this case, the file listed in the error message was created as output from another query run in the same job. Rather, message CPF4131 may be the symptom for programs being run against files created by queries.

Problem Conclusion:

This change allows QRY to create the fields in the same fashion it did in previous releases; however, the fields are not created correctly. The interactive SQL output file function is not changed to work as it did in previous releases.

With this and the prerequisite PTF applied, the change to make QRY create fields as done in pre-R211 releases can be enabled by creating a data area. The data area is checked at initiation of interactive query, RUNQRY, and QRYRUN. If the data area cannot be retrieved at that time (for any reason), the operation of the output file create function operates the same as it did prior to this change. If the value of the data area indicates to do so, the QRY code creates file formats in the same manner as in previous releases.

To enable the change in function, on the OS/400 or i5/OS command line type the following:

QSYS/CRTDTAARA DTAARA(QUSRSYS/QQURUNOPTS) +
TYPE(*CHAR) LEN(80) VALUE('1') +
AUT(*USE) TEXT('Query/400 run options')

Press the Enter key. To disable the support, change the value of the first byte of the data area to be a blank (' ') or make the data area irretrievable.

Important Note: If you are currently operating successfully with the base function and you are satisfied that all the necessary modifications to the applications (programs and queries) on your machine have been completed, you may prefer to have QRY continue to create the output files correctly. If this is the case, you do not need to do anything. If you are almost complete in recompiling and so on, because of the change in this release, you may want to fix any remaining errors when they occur.

Additional Information:

The code is working correctly on R211. On previous releases, QRY and Interactive SQL output files were often created incorrectly. Blind copies of field definitions from the open query definition template were used to create the format for the output file. The code was corrected to continue copying that information but to make some additional necessary changes. The level-check error occurs because some of the changed (corrected) information in the field definitions was being used for the format level identifier. Now that the fields are not the same, a different format level identifier is generated. This change is being made to allow users to upgrade to the new release without having problems with level-check.

SA27876

Problem Summary:

Running an existing QRYDFN in R220 is giving results different from older releases. The QRYDFN is using a logical file with a character field. The related field of the physical file is defined to contain zoned decimal data. If this field contains non-numeric data (for example, blanks or "-"), the QRYDFN results in "+" characters or zeros at this field, indicating that this field contains invalid decimal data. When using other functions to display the file contents (for example, the RCVF command), the data is returned as it was written into the file. In releases before R220, QRYRUN returned the same values as RCVF.

Problem Conclusion:

This change allows QRY400 to directly map data for a numeric field. The change can be enabled by creating a data area called QQURUNOPTS. The data area will be checked at initiation of interactive query, RUNQRY, and QRYRUN.

If the data area cannot be retrieved at that time (for any reason), the operation will operate the same as it did prior to this change. If the value of the data area indicates to do so (the second byte is set to '1'), the Query/400 code allows direct mapping of data for numeric fields.

Note: In some cases, non-Query/400 code may continue to prevent the direct mapping of data for numeric fields.

To enable the change in function (if the data area does not exist on the system), on the OS/400 or i5/OS command line type the following:

QSYS/CRTDTAARA DTAARA(QUSRSYS/QQURUNOPTS) +
TYPE(*CHAR) LEN(80) VALUE(' 1') +
AUT(*USE) TEXT('Query/400 run options')

If the data area already exists on the system, on the OS/400 or i5/OS command line type the following:

QSYS/CHGDTAARA DTAARA(QUSRSYS/QQURUNOPTS (2 1)) VALUE('1')

Press the Enter key. To disable the change in function, on the OS/400 or i5/OS command line type the following:

QSYS/CHGDTAARA DTAARA(QUSRSYS/QQURUNOPTS (2 1)) VALUE(' ')

Press the Enter key.

Caution: Data that is not valid continues to be remapped to valid data if the process option Ignore decimal data errors is set to Y.

SA21451

Problem Summary:

The customer has disabled the interactive run function of QRY queries by following the steps described in the Query/400 User's Guide, Appendix D-1 (Removing the *INTERACT from the list of where allowed to run values using CHGCMD on the command *LIBL/RUNQRY). However, if a user gets to Work with Queries (WRKQRY) from the OFC editor, using F17=Functions or F5=Goto, the user can select Opt6=Direct Merge which is running a query interactively. It appears that this interactive running of queries should be prevented similarly to all other interactive runs described in the Query/400 User's Guide.

Problem Conclusion:

The ability to control the interactive use of direct merge requests is added with this PTF. This function is enabled through the presence of a data area object named QUSRSYS/QQUCHKIRUN. The data area must be created as directed by the special instructions with this PTF. In addition, the prerequisite PTF special instructions should be reviewed to gain a more thorough understanding of the data area being created.

If this PTF is enabled and the interactive run function is controlled through the authority or ALLOW(*INTERACT) of the command *LIBL/RUNQRY (as described in the Query/400 User's Guide), direct merge requests fail with a MSGQRY2294. This makes the direct merge consistent with the other run-time requests of QRY.

Note: The following text is provided only as additional reference information.

To continue to have other operations of QRY run query requests controlled as before and to apply this PTF, do the following:

o	The data area must be named QUSRSYS/QQUCHKIRUN.
o	The data area must be available to all users that need to make use of this function (authority, locks, existence, and so on).
o	Byte positions 01 through 10 must equal 'RUNQRY '. Note: If the prerequisite PTF is applied and enabled, this value must not equal 'RUNQRY '.
o	Byte positions 11 through 20 must equal 'QRYRUN '.
o	Byte positions 21 through 30 must equal 'WRKQRY '.
o	Byte positions 31 through 40 must equal 'QRY '.
o	Byte positions 41 through 50 must equal 'DIRECTMRG '.
o	Byte positions 51 through 80 are reserved for future use and should be left blank.

If, for any reason, the data area is not available to a user when QRY run-time tries to access it, the operation of that function will be as it was prior to the application of this PTF. To determine why or if the data area is not available, a TRCJOB must be done. The messages issued by program QCLRTVDA after program QQUDA appears in the trace indicates that a data area could not be retrieved. For example, message CPF1015 indicates a data area was not found; therefore, the existence of a data area QUSRSYS/QQUCHKIRUN should be verified.

SA32441

Problem Summary:

Query/400 saves (QRYSAV) of queries at R220 are taking longer than at R211. R220 incorporated changes to the query optimizer that require additional processing to perform cost estimates. With queries involving large data files and/or many access paths over the data files, the time required to save the query can be greatly increased over the time required previously at R211. In addition, other users of the database files being analyzed by the query may also experience performance delays due to the temporary locks/seizes being placed by the query to perform the estimates. The functions being performed during the save of the query are not modified by this APAR; these are necessary optimization functions that must be performed as documented in APARs SA17433 and SA21490. This APAR, SA32441, provides the capability for users to manage the query saves.

Problem Conclusion:

When saving a query definition in release R220 and beyond, a complete optimization occurs when building the access plan. In release R211, the optimizer could time out during optimization and, therefore, end up choosing the best plan evaluated up to that point. With full optimization, the optimizer evaluates all of the possible access plans. This process can be lengthy for the following reasons:

o

If the user has a lot of logical files,

o

If the user has indexes built over large physical files. Part of the optimizer process includes performing key range estimates on some indexes to gather optimization statistics (see the AS/400 Database Guide, Appendix D). These key range estimates lock the index and its data space (the physical and related logicals) while the estimate is in process. Usually, an estimate is gathered very quickly. However, in the case of large files, this estimate could take minutes. This results in seize/lock contentions in addition to extending the time to finish the access plan optimization and complete the save.

This enhancement PTF is targeted for the following users:

o	Customers who experience long delays during the save of a Query/400 definition. It does not improve run-time performance of a Query/400 definition.
o	Customers who experience lock/seize conditions during the save of a Query/400 definition. It does not prevent lock/seizes during the run of a Query/400 definition.

This enhancement provides the following options to control how the access plan of a query is built:

Option 1) Timeout (T)

Use of this option allows the optimizer to perform an access plan build; however, it could time out during the optimization. If the optimizer time-out occurs, the optimal access plan may not be created. This option provides time-out support similar to the support provided at R211. In some cases, the time-out support may not improve save performance.

Option 2) No Access Plan (N)

Use of this option states that the optimizer is not invoked to build an access plan. Only the query definition is saved/updated. This allows for instant saves; however, the optimization cost will be paid at run time (every time). This should also resolve lock/seize problems that occur during the save of the query definition.

After application of the PTF, the selection of Option 1 or Option 2 can be made by creating a data area called QQUSAVOPTS. The data area can be created in a library of your choice. However, the library must reside in your library list (*LIBL). The data area will be found by searching the library list. If the data area cannot be retrieved for any reason, the default of full optimization still occurs.

The value of the data area QQUSAVOPTS first byte indicates which option was selected. If the first byte is T, TIMEOUT is the choice. If the first byte is N, NO ACCESS PLAN is the choice. If the first byte contains any other value, the default of full optimization is performed.

If the data area does not exist on the system and your choice is to have the optimizer TIMEOUT, on the OS/400 or i5/OS command line type the following:

QSYS/CRTDTAARA DTAARA(libname/QQUSAVOPTS) +
TYPE(*CHAR) LEN(80) VALUE(T) AUT(*USE) +
TEXT('QUERY/400 SAVE OPTIONS')

Press the Enter key. Then, type the following:

ADDLIBLE libname

Press the Enter key. On the OS/400 or i5/OS command line, type the following:

QSYS/CRTDTAARA DTAARA(libname/QQUSAVOPTS) +
TYPE(*CHAR) LEN(80) VALUE(N) +
AUT(*USE) TEXT('QUERY/400 SAVE OPTIONS')

Press the Enter key. Then, type the following:

ADDLIBLE libname

Press the Enter key.

Changing the value of the first byte of the data area to a blank (' ') or making the data area irretrievable disables the support. Full optimization is then performed.

SA47749

Problem Summary:

Certain types of queries over large database files can cause the files to be locked. This prevents the files from being updated until the query is complete. In addition, the query performance may be degraded.

Problem Conclusion:

The problem with lock/seize conditions on the data space and some extended query optimization times is due to the current estimate range key process design. When large files are involved (usually a million records or more), an estimate key range can take seconds or even minutes to complete. During the estimate process, the LIC code requires a seize of the data space. This prevents any updaters from accessing the file.

Originally, APAR MA09930 was created to address the issue of the seize/lock condition. Unfortunately, this APAR has the effect of increasing the performance degradation for the estimate process. In addition, the fix is not easy to enable or disable (disabled by default).

With this APAR, the estimate key range process in the query optimizer process is enhanced to allow the user to specify what type of estimate processing to perform.

Note: By default, this PTF is not enabled. Review the special instructions for details on the types of estimate key range processing and how to enable them.

Caution: Enabling this PTF could cause the query implementation to change. This may result in a performance degradation (in some cases, severe degradation). For this reason, the PTF should be enabled only when necessary.

By default, the code for this estimate key range enhancement is not activated. To enable the special estimate key range processing, do the following:

1.	A data area called QQQOPTIONS must exist somewhere on the library list for the job. The data area should contain the correct value(s) for the type of estimate key range processing desired.
2.	A job loads this date area only when it runs the first query for that job. Therefore, changing the data area value has no affect on pre-existing jobs. However, if a CHGQRYA CL command is issued for the job, the data area is loaded when the next query is run within that job. For example (before any query is run for that job), on the OS/400 or i5/OS command line type the following: CRTDTAARA DTAARA(QGPL/QQQOPTIONS) + TYPE(*CHAR) LEN(16) VALUE('2') Press the Enter key. To restrict the optimizer from performing any estimates on files that have more than 500,000 records, on the OS/400 or i5/OS command line type the following: CRTDTAARA DTAARA(QGPL/QQQOPTIONS) + TYPE(*CHAR) LEN(16) VALUE('C0015') Press the Enter key. This instructs the optimizer to stop performing any estimates that return a result of more than 150,000 records (15 * 10,000 = 150,000 records). This option is used if estimates are desired to allow the optimizer to determine the optimal implementation; however, some of the estimates over indexes that are not ideal are taking a long time. This value is generally defaulted by the optimizer to 35 percent of the records for the file being estimated. This option allows the user to lower this ceiling to whatever is best suited to the environment.
3.	Data area values: Byte 1: 'N' - No key range estimates are performed. '1' - Key range estimates occur on any file that has less than 100,000 records. '2' - Key range estimates occur on any file that has less than 500,000 records. '3' - Key range estimates occur on any file that has less than 1,000,000 records. 'C' - Use the specified early exit cutoff value. 'V' - Do piecemeal estimates. Estimates on large indexes are done in small pieces and release the seize between each piece. All other values are ignored, and key range estimate processing works normally. Bytes 2-5: A right-justified numeric value is used to determine the early exit cutoff value for any estimate. This value is used as a multiplier against a constant of 10,000 to allow a variety of values to be specified by the user. Valid only if C is specified in the first byte of the data area. Bytes 6-16: Reserved for future use. Caution: Using any of these data area values while reducing or eliminating the time spent performing an estimate over the data space can result in changes in query implementations. This could lead to significant degradation in performance.

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 Option	Position in Data Area	Options
*Reserved for later release	1	N/A
RTGDTA parameter for SBMJOB	2	'B' = QCMDB (QBATCH) ' ' = *JOBD
OUTQ parameter for SBMJOB	3	'J' = *JOBD (default) 'C' = *CURRENT 'U' = *USRPRF 'D' = *DEV
PRTDEV parameter for SBMJOB	4	'J' = *JOBD (default) 'C' = *CURRENT 'U' = *USRPRF 'S' = *SYSVAL
INQMSGRTY parameter for SBMJOB	5	'J' = *JOBD (default) '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 SBMJOB	16-35	10-Character job description + 10-character library. Special allowed values are: *USRPRF - The job description from the user profile is used.
JOBQ parameter for SBMJOB	36-55	10-Character job queue + 10-character library. Special allowed values are: *JOBD - Use the job queue found in the current job description.
Initial number of records in the output file	56-65	Any value from 1 to 2147483646, can be right- or left-justified.
Number of records per increment for the output file	66-70	Any value from 1 to 32767, can be right- or left-justified.
Number of increments for the output file	71-75	Any value from 0 to 32767, can be right- or left-justified.
USER parameter for SBMJOB (Added at Release V4R5M0)	76	'J' = *JOBD ' ' = *CURRENT 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.
Prevent *OUTFILE Output (Added at Release V5R1M0, see SA91425)	77	'Y' = Yes, prevent file output. ' ' = Allow file output.

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 or i5/OS 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')

Press the Enter key. Then, type the following:

QSYS/ADDLIBLE libname

Press the Enter key.

To change an existing QQUPRFOPTS data area to limit the initial number of records in an output file to 10000, the number of records per increment for the output file to 1000, and the number of increments for the output file to 3, type the following on an OS/400 or i5/OS command line:

CHGDTAARA DTAARA(library/QQUPRFOPTS (56 20)) VALUE(' 10000 1000 3')

Press the Enter key. The values are right-justified.

SE49086/SE49224

Problem Summary:

Running an existing QRYDFN created in a previous release may result in output of the MSGQRY1155 Query definition template rebuilt - reason 1. When this message is output, a query definition template (QDT) will be rebuilt and used to run the query and the QDT stored in the QRYDFN will not be used. Because a new QDT is used for formatting the report, the output may be different than it was in the previous release when the query was created. A case in point is the column heading of a report column. If the query was created in a previous release and then the RUNQRY CL command is used in a later release, use of the QRYFILE keyword to override one or more of the files used in the query will result in the report having a column heading from the file specified on the QRYFILE keyword, rather than the column heading from the file specified in the QRYDFN.


Problem Conclusion:

This change allows IBM Query for i to attempt to use the query definition template stored in the QRYDFN rather than rebuilding a new QDT when the QRYDFN was created in a previous release. The change can be enabled by creating a data area called QQURUNOPTS. The data area will be checked at initiation of interactive query, RUNQRY , and QRYRUN .

If the data area cannot be retrieved at that time (for any reason), the operation will operate the same as it did prior to this change. If the value of the data area indicates to do so (the third byte is set to '1'), the IBM Query for i code will attempt to use the QDT saved with the QRYDFN. As a result, the column headings output for the query result will be those associated with the files that were specified when the query was defined. Additionally, the column widths and data types for query result will be those associated with the files that were specified when the query was defined.

Use of the data area does not effect any changes to the QDT that is saved with the QRYDFN. On subsequent runs of the QRYDFN, when the data area is not in use, the MSGQRY1155 Query definition template rebuilt - reason 1 will still be generated, unless the QRYDFN is changed through the Work with Queries function.

Note: In some cases, with this change, the inquiry message MSGQRY1058 - Level for file &1 in &2 does not match query (I C) will also be output.

To enable the change in function (if the data area does not exist on the system), on the IBM i command line type the following:

QSYS/CRTDTAARA DTAARA(QUSRSYS/QQURUNOPTS) +
TYPE(*CHAR) LEN(80) VALUE(' 1') +
AUT(*USE) TEXT('IBM Query for i run options')

If the data area already exists on the system, on the IBM i command line type the following:

QSYS/CHGDTAARA DTAARA(QUSRSYS/QQURUNOPTS (3 1)) VALUE('1')

Press the Enter key. To disable the change in function, on the IBM i command line type the following:

QSYS/CHGDTAARA DTAARA(QUSRSYS/QQURUNOPTS (3 1)) VALUE(' ')

Press the Enter key.