Maximizing sort resources for System 1032
by Tym Stegner
As System 1032 users upgrade to OpenVMS Version 6 and higher, they encounter sorting problems: keys, loads, and reports suddenly quit working. Errors such as, SORTFAIL, INSFMEM, and VASFULL are reported.
Research reveals that the VMS Sorter itself has been upgraded; it requires additional memory and disk space allocations to perform tasks it used to do with less. (Albeit sorting is now faster than in previous versions of OpenVMS.)
First, a known problem in OpenVMS Version 6.1 affects space allocation by the SORTSHR module. See the Digital bulletin boards for information on this problem. You can either obtain a patch or upgrade to OpenVMS Version 6.2.
To provide more memory to the OpenVMS SORT process, examine the following parameters:
PQL_Mxxxxx parameters specify the minimum value for any process on the system. A process with an account parameter less than the corresponding PQL_Mxxxxx parameter uses the PQL value instead.
Determine the memory amount for a user's process as follows:
Usually a System 1032 SORTFAIL error is preceded by either an insufficient memory (INSUFMEM) or a virtual address space full (VASFULL) error.
The INSFMEM error is returned if:
The VASFULL error is returned if the amount of memory the process requires is larger than the SYSGEN value VIRTUALPAGECNT. This happens when the maximum memory for a process (as shown above) is larger than VIRTUALPAGECNT.
If the totals then exceed the value of VIRTUALPAGECNT, you fill up your virtual address space before you run out of page file quota (hence the error VASFULL, virtual address space full, and not INSFMEM, insufficient virtual memory). This also assumes that your page files are large enough to handle the value of PGFLQUOTA for all active users at the same time.
You can allocate additional disk space for sorting using the OpenVMS SORTWORKn logicals. You can define up to ten logicals: SORTWORK0 through SORTWORK9. For maximum benefit, define each logical to a different disk device; defining multiple logicals to the same device is not useful. (You need not define all the logicals.)
How many sort files can you use?
System 1032 Version 9.40 and earlier can use only two of the SORTWORKn logicals. Version 9.60 and higher can use the $SORTWORK_ COUNT system variable to determine how many of the defined SORTWORKn logicals to use for sort operations.
What manages the sort files?
At the time of a sort-dependent operation such as PRINT BY expression, SORT expression, SEARCH MIN/MAX BY expression, or Joined PRINT, the OpenVMS Sorter is initialized, and the number of sortwork files to be used (as designated by $SORTWORK_COUNT) is passed to the sorter. OpenVMS alone manages the sortwork files.
How is sorted data managed?
A data stream is sent to the Sorter and the sort process starts. A dynamic buffer temporarily stores the results from the Sorter (values and/or record $IDs). If this buffer becomes crowded, a temporary file is created in the user's current default directory area, which is written to SYS$DISK: and cannot be otherwise redirected.
Connect* Technical Tip
by Mark LaRocca
As client/server users know, debugging what appears to be a simple error can become this week's project. Let me review just such a simple error, which turns up often and generates quite a few calls to the support line: unqualified table name errors.
As a test of the Connect* installation, try the following Select statement using DDE and SQLTEST:
SELECT * FROM CATALOG.TABLES
This statement returns a list of all schemas and tables in the catalog. In the above statement, CATALOG is the schema and TABLES is the table.
But, what happens if you enter:
SELECT * FROM TABLES
Because the table name is not qualified by a schema, you get the following error:
SQL error -204 xxxx is an undefined name
The -204 indicates a very simple SQL error and xxxx is the schema name.
Why is xxxx undefined? Because, if the table name is unqualified, the schema name defaults to your logon ID. For example, if your logon ID is MARK, then the previous statement is actually processed as:
SELECT * FROM MARK.TABLES
MARK is an undefined schema; hence, the error message is displayed.
Additionally, the following message appears in the Model 204 audit trail:
M204.2500: SQL error -204 xxxx is an undefined name.
Simple? Yes. Get your best SQL reference manual, check out the -204 SQL error code, and make changes as appropriate. Also, see the Model 204 SQL User's Guide section on "Setting the schema and user context."
On the Model 204 side, set LAUDIT=17 on your IODEV 19 SQL threads while you are testing. This parameter shows you exactly what your SQL requests generate. Take a look at the audit trail after a Select statement and, if the error is not clear, call CCA Customer Support.
IMPACT'97 is approaching
Learn how the Internet, Web, and Intranets can increase your options to meet critical business needs as new technologies, legacy systems, tight budgets, and intensified user demands stretch IT departments beyond available resources.
The Year 2000! Time is running out with regard to the methodologies and processes that companies need to employ to meet the new millennium. Hear what you need to consider regarding these changes and return to your offices ready to apply what you learn to your systems.
For details, access the IMPACT home page via http://www.impactug.org or call the IMPACT office at 617-595-2005.
Contact CCA Webmaster Copyright 2008
