The Pooled Tables option permits you to produce many reports or extract files in a single pass of your data source, dramatically reducing database I/O, CPU, and elapsed time. Requests against any data source, file, or JOIN structure that FOCUS reads can be pooled without incurring a penalty, even if the application does not exploit the feature.
Pooling is added with several SET commands, and its analytical functions can automatically identify reports that can share database I/O and run them concurrently.
Pooling is applicable whenever consecutive report requests run against the same database, which is ideal for large batch operations, as well as canned FOCUS reporting and data-extract applications. It also applies in most reporting situations where record-selection costs exceed the costs for report formatting.
How to: |
To implement Pooled Tables in an application, you simply add several SET commands; no other changes are required. As FOCUS runs a group of report requests, it starts pooling as soon as it encounters a SET POOL=ON command, and pooling continues until it reads a SET POOL=OFF. During processing, FOCUS searches for consecutive TABLE requests that access the same data source with the same access method, and it stores those in sub-pools. A read-ahead feature even crosses FOCEXEC boundaries, dividing commands into retrieval and non-retrieval sub-pools. These sub-pools are collections of TABLE requests and related commands against common data sources—only report requests within sub-pools can be combined. Sub-pool boundaries are established whenever FOCUS encounters commands that either alter the data or change the processing environment (see Sub-Pool Boundaries and Pooling Restrictions).
Sub-pools are further subdivided into clusters, which are sets of consecutive TABLE requests against the same logical database that employ the same access method. Requests that cannot be pooled due to syntactical or environmental conditions are executed as single-TABLE clusters, which execute concurrently and share their data retrieval and screening processes (as well as related overhead), but do not share sorts or output formatting functions.
You can make processing more efficient if you can estimate the expected number of records to be read and lines of output. You can also perform a degree of memory management by limiting the amount of memory made available for pooling. For more information, see Estimating Memory Requirements
SET POOL = {OFF|ON} where:
Ends Pooled Tables and executes any queued requests. OFF is the default value.
Activates Pooled Tables.
The following example illustrates the ease of implementing Pooled Tables. Here a small amount of memory is provided for Pooled Tables (4,000K); then pooling is turned on and report size estimates are provided for each report. The report requests are queued until pooling is turned off. At that time, data is retrieved only once for all report requests in the pool. They are executed concurrently, and the reports printed one after the other.
SET POOLMEMORY = 4000 SET POOL=ON TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME HIRE_DATE BY DEPARTMENT IF HIRE_DATE GE 820101 ON TABLE SET ESTLINES 10000 AND ESTRECORDS 10000 END
The output is:
DEPARTMENT LAST_NAME FIRST_NAME HIRE_DATE
---------- --------- ---------- ---------
MIS JONES DIANE 82/05/01
BLACKWOOD ROSEMARIE 82/04/01
GREENSPAN MARY 82/04/01
PRODUCTION SMITH RICHARD 82/01/04
BANNING JOHN 82/08/01
IRVING JOAN 82/01/04
ROMANS ANTHONY 82/07/01
MCKNIGHT ROGER 82/02/02TABLE FILE EMPLOYEE PRINT CURR_SAL BY CURR_JOBCODE IF CURR_JOBCODE EQ 'A$*' ON TABLE SET ESTLINES 5 AND ESTRECORDS 4000 END
The output is:
CURR_JOBCODE CURR_SAL
------------ --------
A01 $9,500.00
A07 $11,000.00
$9,000.00
A15 $26,862.00
A17 $29,700.00
$27,062.00TABLE FILE EMPLOYEE PRINT LAST_NAME FIRST_NAME BY DEPARTMENT IF PAY_DATE FROM 820701 TO 820831 ON TABLE SET ESTRECORDS 120000 END
SET POOL=OFF
The output is:
DEPARTMENT LAST_NAME FIRST_NAME
---------- --------- ----------
MIS SMITH MARY
JONES DIANE
MCCOY JOHN
BLACKWOOD ROSEMARIE
GREENSPAN MARY
CROSS BARBARA
PRODUCTION STEVENS ALFRED
SMITH RICHARD
BANNING JOHN
IRVING JOAN
ROMANS ANTHONY
MCKNIGHT ROGER
Reference: |
Sub-pools are collections of TABLE or GRAPH requests and related commands. Only reports within a sub-pool can be pooled together to share I/O. Sub-pool boundaries are imposed by non-retrieval commands that can change the data or retrieval method for the data source. Therefore, you cannot reliably pool together reports on either side of a sub-pool boundary. When sub-pool boundary commands are encountered, pooling temporarily halts and all queued requests are executed.
Sub-pool boundaries are created when:
The following table lists retrieval commands included in sub-pools.
Note: This list may be subject to change in future releases.
? |
?F |
?FF |
CHECK |
DEFINE |
GRAPH |
HELP |
HOLD |
OFFLINE |
ONLINE |
PCHOLD |
REPLOT |
RETYPE |
SAVB |
SAVE |
TABLE |
TABLEF |
The following table lists commands that cause sub-pool boundaries.
ANALYSE |
CALC |
CMS |
COMBINE | |
COMPILE |
CREATE |
DECRYPT |
DYNAM |
ENCRYPT |
EX |
EXEC |
FILETALK |
FILTER |
FIN |
FINISH |
FIXPACK |
FS |
FSCAN |
GRAPHTALK |
JOIN |
LET |
LOAD |
MAINTAIN |
MATCH |
MODIFY |
MODIFYTALK |
MPAINT |
MVS |
PASS |
PLOTTALK |
REBUILD |
RECALC |
REMOTE |
RESTRICT |
RUN |
SCAN |
SET |
SQL |
TABLETALK |
TED |
TSO |
UNLOAD |
USE |
WINDOW |
The following lists SET commands that cause sub-pool boundaries. SET commands included in ? SET ALL that are not on this list do not cause sub-pool boundaries.
ADABAS |
AGGRRATIO |
ALL. |
AUTOINDEX |
AUTOPATH |
AUTOSTRATEGY |
AUTOTABLEF |
BINS |
BLKCALC |
BYPANEL 2 |
BYSCROLL |
CACHE |
CALC |
CALCMEMORY |
CALCROWS |
CALCWAIT |
CARTESIAN |
CDN |
COLUMNSCROLL2 |
COMMIT |
COMPUTE |
CURRENCY |
DATETIME | |
DEFCENT |
ESTLINES 1 |
ESTRECORDS 1 |
EXTSORT |
FIELDNAME |
FILENAME |
FIXRETRIEVE |
FOCSTACK |
FOC144 1 |
HIPERFOCUS |
HTMLMODE |
ICUFORM |
IMPLIEDLOAD |
IMS |
LABELPROMPT |
LANGUAGE |
LE370 |
LOADLIMIT |
LOOKGRAPH |
MAXLRECL |
MAXPOOLMEM |
MINIO |
MODE XXXXXX |
MPRINT |
PASS |
POOL |
POOLBATCH |
POOLFEATURE |
POOLMEMORY |
POOLRESERVE |
PREFIX |
PREVIEW |
PRINTPLUS 2 |
QUALCHAR |
RECORDLIMIT 1 |
SAVEMATRIX |
SHIFT |
SM |
SQLENGINE |
SQLTCARTES |
SQLTOPTTF |
STYLEMODE |
SUSI |
SUTABSIZE |
TCPIPINT |
TEMP DISK |
TERMINAL |
TEXTFIELD |
TRACKIO |
TRMSD |
TRMSW |
TRMTYP |
USER |
WINPFKEY |
XRETRIEVAL |
YRTHRESH |
3DGRAPH |
Note:
In certain instances, reports cannot be pooled due to syntactical or environmental conditions. In this case, they are executed as single TABLE clusters. Reports in this category include:
The list below provides subroutines and functions that can be pooled.
ARGLEN |
ATODBL |
AYM |
AYMD |
BAR |
BITSON |
BITVAL |
BYTVAL |
CHGDAT |
CHKFMT |
CHKPCK |
CTRAN |
CTRFLD |
DADMY |
DADYM |
DAMDY |
DAMYD |
DAYDM |
DAYMD |
DMOD |
DOWK |
DOWKL |
DTDMY |
DTDYM |
DTMDY |
DTMYD |
DTYDM |
DTYMD |
EXP |
FEXERR |
FINDMEM |
FMOD |
FTOA |
GETPDS |
GETTOK |
GETUSER |
GREGDT |
HEXBYT |
HHMMSS |
IMOD |
ITONUM |
ITOPACK |
ITOZ |
JULDAT |
LCWORD |
LJUST |
LOCASE |
OVRLAY |
PARAG |
PCKOUT |
POSIT |
RJUST |
SOUNDEX |
SUBSTR |
TODAY |
UFMT |
UPCASE |
YM |
The number of executable reports per cluster depends on how much memory is allocated to Pooled Tables (POOLMEMORY). To optimize pooling capacity, give POOLMEMORY an adequate size: z/OS region size, or z/VM virtual memory. Reduce POOLRESERVE after loading interface and other modules.
To improve the pooling potential of requests, remove unnecessary sub-pool boundary commands such as extraneous -RUN statements, and consolidate necessary sub-pool boundary-forcing commands such as DYNAM and SET. You can further improve opportunities for pooling within clusters by grouping requests with the same source, entry point and retrieval method.
For optimal processing, supply accurate estimates for ESTRECORDS, ESTLINES, and POOLMEMORY for each request. Remember that these estimates apply to each report, not to the aggregate size of the set of reports in the cluster. To calculate these sizes, issue ? STAT and review the statistics.
The statistical report produced by ? STAT is useful in tuning applications that employ Pooled Tables.
Note: This annotated sample shows only information concerning Pooled Tables.
STATISTICS OF LAST COMMAND | |||||
RECORDS = |
50000 |
. | |||
LINES - |
50000 |
. | |||
. |
. | ||||
. |
. | ||||
. |
. | ||||
1. |
READS = |
250000 |
. | ||
. |
. | ||||
. |
|
. |
| ||
|
|
. |
|
. |
| |
|
|
. |
|
. |
| |
2. |
SUBPOOL |
= 1 |
. |
| |
3. |
CLUSTER |
= 2 |
8. |
ITERATION |
= 1 |
4. |
# CLUSTER ITEMS |
= 25 |
9. |
# ITER ITEMS |
= 16 |
5. |
SEQ# IN CLUSTER |
= 5 |
10. |
SEQ# IN ITER |
= 5 |
6. |
ESTIMATED RECS |
= 50000 |
11. |
ESTIMATED LINES |
= 50000 |
7. |
REPORT WIDTH |
= 148 | |||
For this report:
| 1. | READS |
Total number of read I/O’s. |
| 2. | SUB POOL |
This is the first subpool. |
| 3. | CLUSTER |
This is the second cluster in the subpool. |
| 4. | # CLUSTER ITEMS |
Total number of reports in the cluster. There are 25 reports in the cluster. |
| 5. | SEQ # IN CLUSTER |
Sequence number of the current report in the cluster. This is the fifth report in the cluster. |
| 6. | ESTIMATED RECS |
ESTRECORDS was set to 50,000. Compare this with RECORDS at the top of the output. If a discrepancy, exists correct ESTRECORDS. |
| 7. | REPORT WIDTH |
The report width is 148 bytes. |
| 8. | ITERATION |
Sequence number of the iteration (multiple iterations are used if pool memory is insufficient to run all reports at once). This report was produced in the first iteration. |
| 9. | # ITER ITEMS |
Number of reports within the iteration. Sixteen reports are included in the first iteration. |
| 10. | SEQ # IN ITER |
Sequence number of this report within the iteration. This is the fifth report in iteration number one. |
| 11. | ESTIMATED LINES |
ESTLINES was set to 50,000. Compare this with LINES at the top of the output. If a discrepancy exists, correct your ESTLINES value for this report. |
How to: |
Pooled Tables memory requirements per report vary depending on numbers of records selected, output lines produced, and report widths, all of which Pooled Tables calculates based on the values of ESTLINES and ESTRECORDS. Gather ESTLINES and ESTRECORDS input from:
The memory requirement for a small summary report roughly equals:
NUMBER OF LINES OF OUTPUT * REPORT WIDTH.
For large summary reports and detail reports, use:
NUMBER OF RECORDS SELECTED * REPORT WIDTH
In the absence of estimates, Pooled Tables uses the following defaults:
ON TABLE SET ESTRECORDS {m|0}where:
Is the estimate of the number of records being retrieved for a report. The default value is 0.
Assign a global value for each report in a pool with the following command:
SET ESTRECORDS={m|0}
ON TABLE SET ESTLINES nSET ESTLINES=n
where:
Is a user estimate of the number of output lines for a report. The default value is 0. If no value is given, Pooled Tables assumes there is no aggregation, and that the number of lines is the same as the number of records.
You can assign a global value for each report in a pool with the following command:
SET ESTLINES=n
When ESTLINES is 0, Pooled Tables uses the current value of ESTRECORDS for ESTLINES. While adequate for large extract reports, this provide minimal benefit if inaccurate.
SET POOLMEMORY = nwhere:
Is the upper limit in kilobytes of memory that FOCUS may use during any cluster for a user. In z/OS, this is memory above the 16-megabyte line. In z/VM, it represents total virtual memory.
The default value is 16,384 K (16 M). The minimum value is 1,024 K.
The minimum value for POOLMEMORY is 1,024 K. You can set a maximum threshold for POOLMEMORY when you install Pooled Tables.
You can also set POOLMEMORY from the command line, during FOCUS initialization (in the PROFILE FOCEXEC), or within an application.
When POOLMEMORY is insufficient to execute every request in a cluster simultaneously, Pooled Tables executes them in iterations, producing as many reports as it can in memory in the first iteration and staging data for the remaining reports in a FOCPOOLT work file it creates for this use. The remaining reports are produced from FOCPOOLT in subsequent iterations, so the original data source is still only accessed once at the outset. When a cluster can be produced directly from memory, no FOCPOOLT file is created.
SET POOLRESERVE =nwhere:
Is an amount of memory (kilobytes) reserved for other modules that Pooled Tables cannot use.
In z/VM, the default is 1,024K. In z/OS, it is 100K.
POOLRESERVE reserves memory for use by other modules during the Pooled Tables request-parsing and decision-making processes. For example, initial access to SQL/DS requires loading of Information Builders interface code and IBM modules (this memory will not be made available to Pooled Tables).
You can change either at installation time, by setting POOLRESERVE from the command line during FOCUS initialization (in the PROFILE FOCEXEC), or within your application.
Suggested values for POOLRESERVE are:
Running an interface (not in saved segment): 1024 K Running an interface (in saved segment): 256 K Using SyncSort as the external sort: 512 K Using any other sort: 128 K
Memory for such activities is not used in the Pooled Tables case until the common read is executed. After loading these modules, you can reduce POOLRESERVE, perhaps to zero. If the Information Builders interface and IBM load modules are stored in a saved segment, you can even reduce POOLRESERVE before executing Pooled Tables.
The Pooled Tables Trace facility (see Observing the Results of Pooling (TRACEON)) displays actual memory allocations for each report and the statistics used to calculate it.
If a cluster contained 30 report requests, each requiring 1megabyte of memory, and 10 megabytes was all the memory allocated for POOLMEMORY, Pooled Tables would retrieve data for all 30 reports but produce only the first 10 reports directly from memory (the first iteration), writing the records for the remaining 20 reports to the FOCPOOLT work file. In the next iteration, Pooled Tables would read data for the next 10 reports from FOCPOOLT and process those. In a third iteration it would process the data for the final 10 reports.
FOCPOOLT retrievals are more efficient than going back to the data source, because the data is pre-screened and formatted, and because Pooled Tables collected accurate record counts (ESTRECORDS) when it wrote records for the second and subsequent iterations to FOCPOOLT. With accurate memory requirements calculated, Pooled Tables performance is optimized.
Selection statements that appear in every report request in a cluster are automatically applied just once during Pooled Tables retrievals. To qualify, such tests must refer to the same field and apply an equality test (EQ or IS); however the actual values selected need not be the same. For example, if the first report tests WHERE FISCAL_YEAR EQ 1997 and the second tests for WHERE FISCAL_YEAR EQ 1998, Pooled Tables applies the test WHERE FISCAL_YEAR EQ 1997 OR 1998 during data retrieval. Common selection tests greatly reduce the size of answer sets returned.
Pooled Tables can also evaluate common selection criteria not based on equality tests through the FOCUS Filters feature. Filters permit specification of simple or complex selection tests against a common file for all reports. If, for example, all reports in a cluster use WHERE DELETE_FLAG NE 'Y', you can create a filter with that test. Alternately, you could change the test to read WHERE DELETE_FLAG EQ 'N' so that the common selection command is used in the Pooled Tables common read.
Reports against non-relational databases, such as VSAM, IMS, IDMS, FOCUS, and sequential files, must meet several simple criteria to be pooled into one cluster. To qualify, all reports must access the same data source, use the same Master File and share the same access method. All reports in a cluster must also share the same entry point (the reporting view must be from the same segment and, in the case of indexed access, from the same field). Reports against sequential files always meet these criteria and always pool. Reports against joined structures are pooled if they share the same access method to the host file.
Reports against relational databases, such as UDB (DB2) and SQL/DS, can be pooled into the same cluster when they share several common attributes. Like non-relational files, all reports must access the same Master File from the same entry point. Reports requiring SQL aggregation (the generated SQL statements contain the GROUP BY phrase) are not pooled, which assures that the set presented to each report in the pool is accurate. Further, requests against a multi-table relational view must all reference the same tables to be pooled into the same cluster.
If a view contains table A and table B, all reports that reference only fields in table A can be pooled, all reports that reference only fields in table B can be pooled, and all reports referencing fields in both table A and B can be pooled. However, none of the reports in those sets could be pooled with reports from the other sets. This limitation insures that the RDBMS retrieval engine uses the same optimization logic for each report in the set.
Less stringent pooling requirements apply with optimization off (SQL SET OPTIMIZATION OFF). Since FOCUS manages the retrieval and aggregation operations in this case, pooling conditions are the same with optimization off as with non-relational databases. Restrictions regarding common accessed tables and SQL aggregation do not apply.
Pooling benefits obtained with optimization off, versus those gained by allowing the RDBMS to optimize retrievals, vary from case to case. For example, a request requiring an area sweep that returns a large answer set (even with optimization), would be a good candidate to pool with other requests if optimization were turned off.
When the interface trace facility is used for a relational database, the SQL generated for each request is echoed. The SQL is generated during the Pooled Tables parsing phase but is not submitted to the RDBMS. Instead, Pooled Tables constructs an internal request to retrieve all data for the cluster. The SQL SELECT statements generated for the cluster are echoed in the trace, and these are the statements passed to the RDBMS.
SQL SELECT statements generated by Pooled Tables are optimized by the RDBMS. Therefore, the best optimization occurs when all requests in a cluster contain the same equality screening conditions or Filters. In such cases, the screening tests are included in the SQL and passed to the RDBMS for optimization. Without application of common selections or Filters, it is possible that efficiencies gained through RDBMS optimization could be lost in pooling individual requests. Consider these two requests: the first returns a small answer set based on a selection against a key field named KEY1. The second returns a small answer set based on a selection against a key field named KEY2. The independent screening conditions are not included in the SQL generated by Pooled Tables, resulting in an area sweep and a large answer set for the cluster. If the two tests were included as an OR condition in a Filter, the screening operation would be passed to the RDBMS and a much smaller answer set returned to Pooled Tables.
How to: |
Pooled Tables automatically pools batch requests wherever possible if the POOLBATCH SET command is issued in a user's PROFILE or in FOCPARM. A batch is any non-interactive session. In z/OS, this is whenever ddname SYSIN is allocated to a data set. In z/VM, non-interactive jobs occur when ddname SYSIN is defined (FILEDEF) to a file, FOCUS is invoked with the syntax FOCUS IN fileid, or the z/VM session is running disconnected.
SET POOLBATCH = {OFF|ON}where:
Disables automatic use of Pooled Tables for batch processing. This is the default.
POOLBATCH can be included in the FOCPARM ERRORS, FOCUS PROFILE, a FOCEXEC, or issued in the SYSIN input stream.
SET POOLBATCH=ON has the effect of automatically setting POOL=ON for batch execution. SET POOLBATCH=OFF does not reverse this setting. To disable pooling when POOLBATCH=ON, issue the command SET POOL=OFF.
Enables automatic use of Pooled Tables for batch processing.
How to: |
Pooled Tables chooses an in-memory FOCUS sort or an external sort based on report-size estimates. Normally, the FOCUS sort is used for reports under a megabyte, and external sorts in other cases. The limiting factor on concurrently executing sorts is the amount of memory available to Pooled Tables. While Pooled Tables can execute up to 26 external sorts, this is controlled by the MAXEXTSRTS setting and by how much memory is provided below the 16-megabyte line in z/OS. In z/VM, only one external sort can be executed with SyncSort. When it is practical, the FOCUS sort is substituted for the external sort when external sorts are limited but memory is available.
SET SORTLIB = sorttype where:
Can be one of the following:
SYNCSORT identifies the external sort utility as SYNCSORT.
DFSORT identifies the external sort utility as DFSORT.
VMSORT identifies the external sort utility as VMSORT.
MVSMSGSS identifies the external sort utility as SYNCSORT and its messages are displayed (z/OS only).
MVSMSGDF identifies the external sort utility as DFSORT and its messages are displayed (z/OS only).
SET MAXEXTSRTS=n where:
Is the maximum number (from 1 to 26) of concurrent external sorts permitted. The default is 26.
In z/VM, only one version of SyncSort can run concurrently. If you use SyncSort in z/VM, the value of MAXEXTSRTS is assumed to be 1.
How to: Reference: |
The Pooled Tables trace facility breaks down pools into sub-pools and clusters, warns when memory allocation is insufficient, and displays report statistics. The trace facility shows how pools were executed to help developers tune their applications.
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=POOLTABL //{CLIENT|FSTRACE}where:
Directs trace output to the terminal.
Is a ddname where trace output can be directed. You must allocate a FILEDEF ddname FSTRACE to a sequential data source. Recommended DCB attributes are RECFM=F and LRECL=160.
Note: SET TRACEUSER=ON is required to enable the trace facility. SET TRACEOFF=ALL ensures that no traces are activated. When you then activate the Pooled Tables trace, it will be the only trace activated.
SET TRACEOFF=POOLTABL
where:
Ends the Pooled Tables Trace facility.
These messages indicate sub-pool boundary encounters:
Sub pool boundary--prior output required as input Sub pool boundary--FOCUS/SET command Sub pool boundary--DEFINE ADD Sub pool boundary--new MASTER name Sub pool boundary--new DEFINE clears pre-pool DEFINE This command will run now, outside of pooling: A DEFINE ADD will run now, outside of pooling.
These messages indicate cluster boundary encounters:
Cluster boundary--new master name Cluster boundary--single-table cluster Cluster boundary--new alternate view Cluster boundary--new pool flag Cluster boundary--new pool condition Cluster boundary--mid-stream DEFINE Cluster boundary--new entry segment Cluster boundary--too many verb objects
These messages indicate reports that cannot be pooled (single-table clusters):
Single-table cluster--REDEFINEd real field Single-table cluster--User subroutine not known safe Single-table cluster--self-referential DBA/filter Single-table cluster--INCLUDES/EXCLUDES selection Single-table cluster--too many test literals Single-table cluster--complex test on index Single-table cluster--$ORTPARM allocated Single-table cluster--REDEFINEd constant real field Single-table cluster--RANKED BY Single-table cluster--COUNT DISTINCT Single-table cluster--RECAP Single-table cluster--COUNT is a verb object Single-table cluster--indexed view via AUTOINDEX Single-table cluster--EMR Single-table cluster--ON TABLE SET Single-table cluster--TEXT field Single-table cluster--PREVIEW mode Single-table cluster--ALL = ON/PASS Single-table cluster--per message above Single-table cluster--indexed view for FOCUS database Single-table cluster--non-poolable interface request Single-table cluster--too many verb objects
These trace messages appear during the creation and execution of clusters and iterations:
Building cluster x... Cluster contains n table(s) Cluster n dedicated to command x Clusters built; sub pool contains x cluster(s). ****** Stack before 1st cluster: ****** ****** Stack before nth cluster: ****** ****** Begin union table ****** **** Stack before nth iteration: ****
During the parsing phase of Pooled Tables, the following statistics are displayed for each report. These indicate whether a report request can be pooled and under what conditions. All reports with the same pooling criteria can be pooled together.
Entry Segment : x Relational Flag : y Pool Flag : z Condition Length: n Condition : c
After execution of a pooled report, the output from ? STAT is included in the trace. The entries for TRACKIO and MINIO are included in the output, but their values are not populated. In addition, the following statistics are included:
TRAVERSAL MTHD = x ENTRY SEGMENT = I FOCUS SORT MEM = y1 EXTSORT MEMORY = y2 ALGORITHM USED = z
The following trace messages indicate limitations imposed on Pooled Tables by users in executing reports under less than the most favorable conditions, based on parameters provided for POOLMEMORY, POOLRESERVE, ESTRECORDS, and ESTLINES or available memory. These messages do not inhibit the execution of Pooled Tables, but make it less efficient. To correct these situations, replace the values for ESTRECORDS and ESTLINES with accurate values or increase the memory allocated for Pooled Tables.
# concurrent external sorts reduced from x to y by below-16M shortage Minimum sort memory forces iterations Warning--POOLMEMORY desired = x but only y is available Warning: actual line count (x) exceeds lines estimate (y) in heavy aggregation case Warning: records estimate (x) off by more than 10%-actual record count=y Warning: lines estimate (x) off by more than 10%-actual line count = y
How to: |
This section provides installation instructions for all systems: IMS, z/OS, and z/VM.
Enable Pooled Tables for your release of FOCUS by including the following command in FOCPARM:
SET POOLFEATURE = ON
To disable Pooled Tables, include the following command in the FOCPARM file:
SET POOLFEATURE = OFF
If FOCPARM does not contain a SET POOLFEATURE command, FOCUS assumes Pooled Tables is disabled.
The maximum memory above 16 megabytes that can be requested with the SET POOLMEMORY command can be restricted by including the SET MAXPOOLMEM = n command in FOCPARM.
To make POOL = ON the default for all batch jobs, include the command SET POOLBATCH = ON. This must follow the SET POOLFEATURE = ON command in FOCPARM.
Each of the commands is also included in member FOCPARM of ERRORS.DATA (z/OS) or in the file FOCPARM ERRORS (CMS).
Include the POOLFEATURE, POOLBATCH, and MAXPOOLMEM commands in member FOCPARM in ERRORS.DATA as outlined above. Refer to your FOCUS documentation to change the default allocation for the file FOCPOOLT.
If you use DFSort and try to run more than 10 sorts concurrently, DFSort displays this message:
ICE149A DFSORT IS NOT LICENSED FOR USE ON THIS SYSTEM. RETURN CODE 12, REASON CODE 4.
This causes FOCUS to ABEND. Issue the command SET MAXEXTSRTS=10 to avoid this symptom temporarily. IBM has fixed this problem with APAR OW29152. Order IBM PTF UW41671 if you run SMS Release 1.3. Order IBM PTF UW41672 if you run SMS 1.4.
Include the POOLFEATURE, POOLBATCH, and MAXPOOLMEM commands in the file FOCPARM ERRORS as outlined above. Change the value of POOLRESERVE in FOCPARM ERRORS if appropriate for your installation.
To configure Pooled Tables, include the following commands in the FOCPARM file
SET POOLFEATURE = {OFF|ON}where:
Disables Pooled Tables for this FOCUS site. OFF is the default value.
Enables Pooled Tables for this FOCUS site.
SET POOLBATCH = {OFF|ON}where:
Does not enable automatic use of Pooled Tables for batch processing. This is the default.
POOLBATCH can be included in the FOCPARM ERRORS, FOCUS PROFILE, a FOCEXEC, or issued in the SYSIN input stream.
SET POOLBATCH=ON has the effect of automatically setting POOL=ON for batch execution. SET POOLBATCH=OFF will not reverse this setting. To disable pooling when POOLBATCH=ON, issue the command SET POOL=OFF.
Enables automatic use of Pooled Tables for batch processing.
SET MAXPOOLMEM = n where:
Sets an upper limit in kilobytes for memory above 16 megabytes that users can allocate in the SET POOLMEMORY command. The default is 32,768 K (32 M) and the minimum is 1,024K.
| Information Builders |