900 Series HP 3000 Computer Systems ALLBASE/SQL Performance and Monitoring Guidelines ABCDE HP Part No. 36216-90102 Printed in U.S.A.
Copyright c 1987, 1988, 1989, 1991, 1992, 1993, 1994 by Hewlett-Packard Company. The information contained in this document is subject to change without notice. Hewlett-Packard makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability or tness for a particular purpose.
Printing History This is the rst edition of the ALLBASE/SQL Performance and Monitoring Guidelines . It is compatible with release G.0 of ALLBASE/SQL. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions. Prior to release G.0, chapters one through ve were published as the ALLBASE/SQL Performance Guidelines .
Preface This book presents a series of guidelines for use in monitoring and tuning the performance of your ALLBASE/SQL G.0 system. These guidelines are applicable for the MPE/iX 5.0 release. The chapters in this book are listed below: Chapter 1, \Basic Concepts in ALLBASE/SQL Performance" describes some very basic concepts to help you understand how to tune performance. Chapter 2, \Guidelines on Logical and Physical Design" describes how your database design and le storage a ect performance.
What's New in This Release The following table highlights the new or changed functionality in this release, and shows you where each feature is documented. New Features in ALLBASE/SQL Release G.0 Feature (Category) Description Documented in . . . Stored procedures (Usability) Provides additional stored procedure functionality for application programs. Allows declaration of a procedure cursor and fetching of multiple rows within a procedure to applications. New statement: ADVANCE.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . New SQLGEN GENERATE parameters (Usability) Generates SQL statements necessary to recreate modi ed access plans for module sections. New syntax for GENERATE: DEFAULTSPACE, MODOPTINFO, PARTITION, PROCOPTINFO, SPACEAUTH. ALLBASE/SQL Database Administration Guide , \SQLGEN Commands" appendix.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) High Availability Description Provides a collection of features to keep systems available nonstop including: Partial STORE and RESTORE, Partial rollforward recovery, DBEFiles in di erent groups (MPE/iX), detaching and attaching database objects, CHECKPOINT host variable, changing log les, console messages logged to a le, generating fewer log records by using TRUNCATE TABLE to delete rows, and new system catalog information.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) New and changed SQLUtil commands for increased availability (High Availability) Description Documented in . . . Adds support for high availability ALLBASE/SQL Database Administration Guide , \SQLUtil" appendix. and System Management Intrinsics. Intended for non-stop, continuously available operations. New SQLUtil commands: ATTACHFILE, CHANGELOG, DETACHFILE, RESTORE PARTIAL, STORE PARTIAL, STOREINFO, STOREONLINE PARTIAL, WRAPDBE.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . Optimizer enhancement (Performance) ALLBASE/SQL Performance and Uses a more e cient algorithm that signi cantly reduces the time Monitoring Guidelines , \Optimization" in \Basic Concepts in ALLBASE/SQL to generate the access plan. Performance." Access plan modi cation (Performance) Allows modi cation of access plans for stored section to optimize performance. View the plan with SYSTEM.SETOPTINFO.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) I/O performance improvement (Performance) Description Optimizes I/O for initial load, index build, serial scans, internal data restructuring, le activity, pseudo mapped les and temporary les. See the following features for new and changed syntax. Documented in . . . ALLBASE/SQL Reference Manual , \SQL Statements." Deletes all rows in a speci ed table ALLBASE/SQL Reference Manual , TRUNCATE TRUNCATE TABLE in \SQL Statements.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . Modi ed SET options (Performance) Provides better performance for LOADs and UNLOADs. Specify bu er size, status reporting for LOAD/UNLOAD or exclusive lock for data table. AUTOSAVE row limit increased to 2147483647. New and changed SET options: LOAD BUFFER, LOAD ECHO, AUTOLOCK, AUTOSAVE.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Increased memory for MPE/iX (HP-UX shared memory allocation is unchanged) (Performance) Increases memory up to 50,000 data bu er pages and 2,000 run time control block pages. Increases the limits signi cantly allowing allocation of enough data bu er pages to keep the entire DBEnvironment in memory if desired for performance.
Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command Command COMMAND It cannot, however, be entered as: comm italics comamnd In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value.
Conventions (continued) [ ... ] In a syntax statement, horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding pair of brackets or braces. In the example below, you can select parameter zero or more times. Each instance of parameter must be preceded by a comma: [,parameter][...
Contents 1. Basic Concepts in ALLBASE/SQL Performance DBEFile Organization . . . . . . . . . . . . Page Organization . . . . . . . . . . . . Page Table Pages . . . . . . . . . . . . . Rows of Data on Pages . . . . . . . . . . Structure of a Page . . . . . . . . . . . . Storage of Table Data on DBEFile Pages . . Slot Table . . . . . . . . . . . . . . . Indirect Rows . . . . . . . . . . . . . . Hash Storage . . . . . . . . . . . . . . . Page Compression . . . . . . . . . . . . .
2. Guidelines on Logical and Physical Design Logical Data Design . . . . . . . . . . . . . . . . Normalization Issues . . . . . . . . . . . . . . Denormalizing Tables that are Consistently Joined Horizontal Partitioning . . . . . . . . . . . . Vertical Partitioning . . . . . . . . . . . . . . Including Calculated Data in Tables . . . . . . . . B-Tree Index Design . . . . . . . . . . . . . . Choosing Keys . . . . . . . . . . . . . . . . Building Indexes on Large Tables . . . . . . . . Maintaining Indexes . .
Using Predicates with LIKE . . . . . . . . . . . . Using Predicates with BETWEEN . . . . . . . . . Using Fetch Unique Scans . . . . . . . . . . . . . Updating Key Columns . . . . . . . . . . . . . . Avoiding User Propagation of Filters . . . . . . . . Using TID Scans . . . . . . . . . . . . . . . . . Using Parallel Serial Scans . . . . . . . . . . . . . Using the BULK Option . . . . . . . . . . . . . . Analyzing Queries with GENPLAN . . . . . . . . . Modifying the Access Optimization Plan with SETOPT . . .
Memory Utilization in Sorting . . . . . . . Performance Hints for Large Sorts . . . . . Join Methods . . . . . . . . . . . . . . . Temporary Space in the SYSTEM DBEFileSet Section Caching and Directory Caching . . . Setting Limits for Section Caching . . . . . . Using Multiconnect Functionality . . . . . . . Using Timeouts to Tune Performance . . . . . Network Guidelines . . . . . . . . . . . . . . MPE/iX System Guidelines . . . . . . . . . . Using Pseudomapped DBEFiles in MPE/iX . .
Step 2 Set Up the Freeze . . . . . . . . . . Step 3 Create a Deadlock . . . . . . . . . Step 4 Examine the Locks with SQLMON . . Step 5 Release the Frozen Session . . . . . . Lock Allocation Failures . . . . . . . . . . . Step 1 Open Three Windows . . . . . . . . Step 2 Set Up the Freeze . . . . . . . . . . Step 3 Generate the Error . . . . . . . . . Step 4 Investigate the Session with SQLMON Step 5 Release the Frozen Session . . . . . . Freezing DBEnvironment Sessions . . . . . . .
Static Size Screen . . . . . . . . . . . . . . . . . . . . . . . . . . . 9. SQLMON Command Reference EXIT . . . . . . . . . . . HELP . . . . . . . . . . . QUIT . . . . . . . . . . . SET . . . . . . . . . . . . SET CYCLE . . . . . . . . SET DBECONNECT . . . . SET DBEFILESET . . . . . SET DBEINITPROG . . . . SET DBENVIRONMENT . . SET DISPLAYSAMPLES . . SET ECHO . . . . . . . . SET LOCKFILTER . . . . . SET LOCKOBJECT . . . . SET LOCKTABFILTER . . . SET MENU . . . . . . . . SET OUTPUT . . . . . . .
Figures 6-1. SQLMON Road Map . . . . . . . . . . . . . . . . . . . . . . . . 7-1. Deadlock Example . . . . . . . . . . . . . . . . . . . . . . . . . 6-8 7-12 Tables 6-1. 6-2. 6-3. 6-4. 6-5. 6-6. 6-7. 6-8. 6-9. 6-10. 6-11. 6-12. 6-13. 6-14. SQLMON Screens . . . . . . . . . . . . . Abbreviated Screen Commands . . . . . . . SQLMON Help Commands . . . . . . . . . Monitoring Disk Usage . . . . . . . . . . Monitoring Memory Usage . . . . . . . . . Monitoring Tables . . . . . . . . . . . .
1 Basic Concepts in ALLBASE/SQL Performance Before presenting speci c tips and tricks for tuning your DBEnvironments, this chapter presents some information about how data is passed about in ALLBASE/SQL among les, bu ers, applications, and other elements in a typical large-scale production environment. The following topics are included: DBEFile organization. Data Bu ering. System Catalog. Log File Organization. Log Bu ering. Locking and Latching. Sorting. Optimization. Sections and Validation.
Page Table Pages The rst page in a DBEFile is known as a page table page, so called because it contains a table of the contents of the following 252 pages in the le. Before each group of 252 data or index pages, a new page table page is included. Page table pages are composed of entries that indicate whether the following pages are allocated, full, free, or in some other state. These pages are accessed during serial scans, and they are modi ed when data is added to or dropped from a table.
Storage of Table Data on DBEFile Pages Tuples from user tables are stored on DBEFile pages as in the following diagram. The tuples in a TABLE DBEFile are rows from user tables. The tuple header contains descriptive information about the columns in a tuple, and the tuple body contains the actual data. The format of a tuple header is as follows: The tuple header contains the following elds: Length of the header (2 bytes) A 2-byte column descriptor for each column in the tuple.
A page with a shared tuple header looks like the following: A ag is set in the slot table to indicate whether or not the shared tuple header is used by a particular row. The slot table is further described below.
Indirect Rows An indirect row is created when a row's length increases during an update, and the page that currently holds the row does not have enough space to store the new information. In cases like this, the updated row is stored on another page, and the original page is updated with the TID of the new row on the new page. An indirect row can only be accessed by rst fetching one page to nd the address of the row and then fetching a second page to obtain the row itself.
Except for the search array and the NXT and PRV pointers, the format of a hash data page is similar to that of an ordinary data DBEFile page, as shown in the following diagram: To get information about the hash structures of a DBEnvironment, run SQLMON and go to the Static Hash screen. Page Compression When a row of data is inserted on a DBEFile page, it enters the region known as the free area on the page. The free area is all the space that is marked as available for data.
A leaf index page for a common type of index looks like the following: The NXT and PRV elds point to the next and previous leaf pages in the index. These pointers make an index scan in key order extremely fast. The shared header describes the tuple's length and characteristics. The tuple body on an index leaf page has two parts: a key and a data TID. The key is the actual index key value as it appears in the table, and the data TID is the address of the row pointed to by this index entry.
The following diagram shows how a B-tree provides access to data. After deciding to use a particular index, ALLBASE/SQL accesses the root page (1). Then it reads non-leaf pages (2) until it obtains a leaf page (3) which contains the TID of a qualifying row. Finally, it accesses the data page containing the row (4).
How PCRs are Stored A PCR (parent-child relationship) is a special kind of B-tree that supports a referential relationship between two tables|the parent table and the child table. This kind of index has entries that point to the rows in the referring table and di erent entries that point to the rows in the table referred to. The table referred to must also have a unique index de ned on it.
When the key value being inserted is greater than the largest value already in the index or smaller than the lowest value, page splitting is one-way. This means that only one new page is allocated, and half the entries from the old page are moved to it, after which the new key value is added to the new page. In the previous example, an attempt to insert a value of 110 on a full leaf page where the highest value is 100 would result in one-way page splitting.
modi ed at all or a page that has been modi ed and written to disk is considered clean, while a page that has been modi ed but not written out to disk is considered dirty. If there is not enough empty space in the bu er, dirty pages are swapped out, that is, written back to disk, and clean pages are overwritten, on the basis of a least recently used (LRU) algorithm. As the following gure shows, pages ow through the operating system's bu ers into the shared ALLBASE/SQL data bu er.
1-12 Basic Concepts in ALLBASE/SQL Performance
From the scan bu er, the application fetches data into host variables, as in the following: Individual rows are fetched (or groups of rows are BULK fetched) into host variables or arrays declared within the application program. It is important to understand that each layer of bu ering requires additional copying of data from one place to another. More signi cantly for performance, the movement of data from DBEFiles into the operating system's bu er pool and back requires I/O.
System Catalog The system catalog in ALLBASE/SQL is a database of runtime code and system information used by SQLCore to carry out internal operations. Like other databases, the system catalog is a set of tables. The base tables underlying SYSTEM views are owned by special user HPRDBSS, and they are located in the SYSTEM DBEFileSet. The runtime code in the system catalog consists of stored sections for application programs, procedures, and views, together with validity information and authorization data.
Note Setting the DDL Enabled ag to NO does not disable section validation, which obtains exclusive locks on the system catalog. Directory Caching When DDL is disabled (DDL Enabled set to NO), certain system catalog information is cached in shared memory where it is available for quick access. Log File Organization The ALLBASE/SQL log is organized as a series of 512-byte pages. Logs are written and read in page-size blocks.
Log Buffering For operations involving a change to data, ALLBASE/SQL writes log records to a log le, so that these operations can be rolled back if necessary or reapplied in the event of a system failure. In logging, the transaction enters log records into the log bu er, which is periodically ushed (written to disk). The log bu er pool consists of a number of 512-byte pages con gured with the START DBE statement or with the SQLUtil ALTDBE command.
Locking and Latching Concurrent access to database objects, bu ers, and other shared elements in a DBEnvironment is regulated by means of three kinds of controls within ALLBASE/SQL: Locks. Latches. Pins. Locks regulate access to tables, pages, and rows of data when di erent users contend for them. Latches regulate system access to in-memory resources such as bu ers, ensuring that one bu er operation is complete before another is allowed to proceed.
starts up. Latches are more e cient than locks, because deadlocks are not automatically detected in latching. Instead, deadlocks are avoided. Unlike locking, latching does not require the overhead necessary for deadlock detection. Pins A pin is similar to a latch, but its purpose is more specialized. A pin freezes a data structure (such as a page) in ALLBASE/SQL shared memory so that it cannot be overwritten while it is being read or written by a transaction.
Optimization Optimization is the process by which ALLBASE/SQL nds the most appropriate access path to data for the execution of a particular SQL statement. An access path is one of the following ways of getting at data: Sequential scan|reading the entire table. Index scan|using a B-tree or PCR to access particular rows or the entire table. Hash scan|accessing a hash page directly, then nding the row on that page or on an over ow page. TID scan|accessing a single row directly using its page address or TID.
As the size of the table increases, the use of an index may become more advantageous. Assume that the table has 10,000 pages, that data is stored in sorted order and that key values are found on 100 data pages. In this case, 10,000 I/Os would be needed to do the query with a serial scan. If a B-tree index on the table has 3 levels and if key values are found on two index leaves and 100 data pages, then only 104 I/Os would be needed for the same query. The index scan clearly makes sense now.
Sections and Validation A section is a stored representation of an access path chosen by the optimizer. Sections are created by SQLCore when you do the following: Preprocess an application using the ALLBASE/SQL preprocessors. Issue a statement in ISQL. Use dynamic operations in an application. Such operations are built using the PREPARE and EXECUTE statements. Issue a CREATE PROCEDURE statement.
2 Guidelines on Logical and Physical Design This chapter shows how to adjust the logical and physical design of your databases for best performance. Additional general information on this subject appears in the \Logical Design" and \Physical Design" chapters of the ALLBASE/SQL Database Administration Guide . Refer also to the \Guidelines on System Administration" chapter below.
Here are some tips: In general, design your tables to be in third normal form so that updates can be carried out against the smallest number of tuples. Avoid overnormalization of tables in logical design. If two reasonably static tables are joined frequently, consider making them into one table.
Duplicate data wastes storage space and can cause update problems. Tables that are combined in denormalization should have a 1:1 relationship with each other. The combining should not result in an excessive row length or an excessive number of indexes. Tables should have the fewest indexes possible. Horizontal Partitioning Large tables that contain historical data may be good candidates for division into two tables with the same column de nition.
Including Calculated Data in Tables You can include calculated data in tables instead of performing calculations when data is retrieved. In this case, the overhead of the calculation occurs at INSERT or UPDATE time rather than at query time. Stored procedures are a useful way of recalculating whenever data values change. Some calculations can be done using ALLBASE/SQL functions such as SUM, MIN, AVG, etc.
INTEGER and CHAR columns are more suitable as index keys than DECIMAL or DATE. INTEGER keys are best for extremely large tables (gigarows). Order of e ciency: INTEGER FLOAT CHAR DECIMAL DATE Index keys greater than 20 bytes are not recommended. Consider creating an index of concatenated keys for WHERE clauses that contain AND's along with an EQUAL predicate (=). Create separate indexes on the columns on each side of an OR in a WHERE clause when the columns are from a single table.
Creating a B-tree index after loading data into the table causes minimal logging. In this case, key data is sorted and then loaded into index pages without page splitting. The result is faster index creation and a more compact index than when creating the index before loading data. Tables and indexes for which the most I/O is performed should be placed on the fastest available disk. If a choice must be made, place the index (not the table) on the fastest disk.
If space does not usually exist on table pages, then a clustering index should not be de ned on the table, even if your applications frequently access the data in index order. You should use a non-clustering index instead, and manually maintain the table data in index order. You do this by monitoring the cluster count of the index, and performing UNLOAD operations followed by sorting and reloading.
SELECT T1.CONSTRAINTNAME, T1.OWNER, T1.TABLENAME, T1.CCOUNT, T2.NROWS, T2.NPAGES FROM SYSTEM.CONSTRAINTINDEX T1, SYSTEM.TABLE T2 WHERE T1.OWNER = T2.OWNER AND T1.TABLENAME = T2.NAME; You should use this command after doing an UPDATE STATISTICS on the tables of interest to make sure the cluster count is up-to-date. For indexes used in sorting or for retrieving large numbers of rows, the cluster count is especially important. A low cluster count may indicate good performance.
An insert to a hash structure must perform a separate calculation to arrive at the correct page for each row of data. An ordinary non-indexed insert must nd an available page in the DBEFileSet, and nding this page may require several I/Os; but once the location for new inserts is determined, ordinary inserts are less expensive than hash inserts. B-tree inserts are more expensive than either hash or non-indexed inserts.
Updating Statistics In order for the optimizer to make the best choice among available indexes, use the UPDATE STATISTICS statement on tables after creating indexes and after doing many inserts or deletes. After updating statistics, use the VALIDATE statement to revalidate any stored sections that were invalidated by the UPDATE STATISTICS statement. While revalidation is taking place, performance may be impaired due to obtaining exclusive locks on the system catalog.
Physical Data Design Physical data design means the arrangement of data inside tables, which reside in DBEFiles, and the arrangement of DBEFiles in DBEFileSets. This section includes the following topics: Creating DBEFileSets. Creating DBEFiles. Creating Tables. Initial Table Loads. Unloading Data. Unloading and Reloading to Avoid Indirect Rows. Unloading and Reloading to Remove Over ow Pages. Tips on Deletions from Tables.
Placing Large Tables in Separate DBEFileSets You should place tables larger than 1000 pages in their own DBEFileSets. When more than one table is stored in the DBEFileSet, the pages of DBEFiles in the DBEFileSet become a mixture of pages from the di erent tables. In doing a sequential scan, if ALLBASE/SQL needs to access a data page that physically resides near the end of the DBEFileSet, it must rst look through all the page table pages that come before it.
Creating DBEFiles DBEFiles are the physical repositories of ALLBASE/SQL data on the operating system. Since you have great exibility in de ning DBEFile types, sizes, and assignment to DBEFileSets, your choices often a ect the overall performance of your system. Here are some suggestions for creating DBEFiles: Create DBEFiles in sizes that are multiples of 253 pages to minimize the space used by page table pages.
Creating Tables Here are some general suggestions on table creation: Create PUBLIC and PUBLICROW tables for maximum concurrency. PUBLICREAD can improve performance by reducing internal page locking. Use PRIVATE for special purposes. Note that PRIVATE is the default; usually, you want to specify something else. Choose data types which suit the programming language so as to avoid data conversions.
Use an appropriate (not an unreasonably long) length for variable length columns. The maximum length can a ect the performance of BULK SELECT and cursor operations. Indexes whose keys will be updated should never include NULL columns as keys, and rarely should they include VARCHAR or VARBINARY columns. Variable length keys can cause long index chains and rearranging when index values are updated.
Additional tips for improving load performance: For subsequent unload and load operations, use the INTERNAL option of the ISQL UNLOAD and LOAD commands. LOAD INTERNAL is 3-5 times faster than LOAD EXTERNAL. Using archive logging during loading requires additional log le space, but it actually requires fewer I/Os than nonarchive logging. However, the saving of I/Os must be weighed against the time required to back up log les so they can be reused. Set the number of log bu ers to at least 120 while loading.
8. Do an UPDATE STATISTICS for the table to update system catalog information about the table and its indexes and constraints. 9. Issue the COMMIT WORK statement. Unloading and Reloading to Remove Overflow Pages Since many key values can hash to the same page address, it is possible for a page in a hash structure to become full. When this happens, a new row must be inserted on an over ow page. Over ow pages increase the amount of I/O that must be performed to obtain table data.
3 Guidelines on Query Design Careful query design is of primary importance for performance. In most queries, the most e cient retrieval of data occurs when an index is used. By default, the ALLBASE/SQL query optimizer decides whether to use an index or not, and if so, it decides which one to use. However, you can override the query optimizer's choice with the SETOPT statement. Also, through careful query design, you can ensure that the optimizer is able to choose an available index.
SELECT W FROM T1 WHERE X = Y + Z - W The following approach avoids the problem by assigning the result of the computation to a host variable which is then used in the predicate: HostVar = Y + X - W SELECT W FROM T1 WHERE X = :HostVar Columns from One Table on Both Sides of the Relational Operator An index scan cannot be used to evaluate a predicate with columns from the same table on either side of a relational operator.
In comparisons that result in conversions from INTEGER or SMALLINT constants or host variables to DECIMAL, an index may be used if the number of places to the left of the decimal point in the DECIMAL type (i.e., p-s) can accomodate the largest value yielded by the INTEGER (or SMALLINT). For example, a SMALLINT value is compatible with a DECIMAL (10,2), but an INTEGER value is not compatible with a DECIMAL (10,2).
Predicates with INTEGER = DECIMAL(n,0) Factors Factors of the form Integer = Decimal (n,0) can be optimized by ALLBASE/SQL provided n is less than or equal to 10. Factors of the form Smallint = Decimal (n,0) can be optimized provided n is less than or equal to 5. In the following, an index may be chosen, since the decimal scale is zero and the precision is within the appropriate bounds. Assume that X is an integer: SELECT W FROM T1 WHERE X = 45.
subqueries get transformed into EXISTS predicates internally. Therefore the nested query using the IN predicate also takes 70 scans. In general, non-correlated subqueries are faster than joins, and correlated subqueries are slower than joins. You should know whether the subquery will return a single row or not so as to take advantage of the speed of non-correlated subqueries.
When Not to Use DISTINCT in Subqueries In general, you should avoid using the DISTINCT keyword in subqueries. DISTINCT does not change the query result and, in fact, hinders performance.
The following example requires conversions: CREATE CREATE CREATE CREATE TABLE TABLE TABLE TABLE SELECT Item, FROM T1 UNION SELECT Item, from T2 UNION SELECT Item, FROM T3 UNION SELECT Item, FROM T4 T1 T2 T3 T4 (Item (Item (Item (Item CHAR(40), CHAR(40), CHAR(40), CHAR(40), Price Price Price Price SMALLINT) INTEGER) DECIMAL(10,2) FLOAT) Price Price Price Price Since the result data type in this UNION is FLOAT, sources 1, 2, and 3 require conversion to FLOAT, which is the largest common denominator t
Using OR Predicates An index scan may be used for a query that has an OR predicate. How OR Predicates are Optimized Most predicates involving OR factors are transformed to conjunctive normal form to make the choice of an index scan during optimization more likely. In addition, the optimization of OR predicates involves internally ANDing additional factors to the predicate you supply in order to eliminate duplicates.
If a query predicate includes only OR factors (all columns in one OR factor from a single table), it is a good idea to de ne a multicolumn index on all columns involved. If a query predicate includes both OR factors and simple EQUAL factors (for example, (c1=10 OR c2=20) AND c3=30), the EQUAL factor (c3=30) may yield a cheaper plan than the OR factor. In such cases, it is a good idea to ensure that there is an index on the columns involved in the EQUAL factor.
Using Predicates with BETWEEN For the BETWEEN clause, the optimizer makes the decision depending on the following (among other things): The range of the BETWEEN. The range of the HIGH and LOW values of the index column(s). For example, if the range in the BETWEEN predicate is very wide relative to the HIGH and LOW values stored in the index, the optimizer expects a large number of rows to be returned and is therefore more likely to choose a serial scan.
Avoiding User Propagation of Filters A lter is an element in a predicate that reduces the size of the query result by eliminating a category of result rows. Consider the following join query fragment: ... WHERE table1.col1 = table2.col2 AND table1.col1 <= 200 Note that, logically, table2.col2 has to be <= 200 as well; that is, it is subject to the same lter as table1.col1. ALLBASE/SQL propagates such lters from one joined table to the other joined tables without your explicitly doing so.
Using Parallel Serial Scans When you need to sequentially read a large table, you can improve performance by using a parallel serial scan. The ALLBASE/SQL optimizer uses a parallel serial scan when it is able to prefetch pages from multiple disk drives in parallel. A parallel serial scan is useful only for large tables that must be read sequentially. It is not bene cial for small tables or for tables that are accessed only by an index.
Analyzing Queries with GENPLAN The GENPLAN statement can be useful in determining the way to write a SELECT, UPDATE, or DELETE statement for maximum performance. GENPLAN lets you see the optimizer's access plan for one ALLBASE/SQL statement at a time. Issue the GENPLAN statement in ISQL, then simply do a SELECT on the temporary table SYSTEM.PLAN within the same transaction.
4 Guidelines on Transaction Design Transaction design is important for performance because it determines the type and duration of locks held by your applications. By selecting appropriate lock types, and by avoiding unnecessary locking, you can improve performance. Topics in this chapter include the following: General Tips on Managing Transactions. Controlling Locking. Using Isolation Levels. Using Row Level Locking. Using KEEP CURSOR. Removing Non-Database Processing. Using Procedures and Rules.
Using Short Transactions and Savepoints At the end of a transaction, the COMMIT WORK statement makes changes permanent to disk, which causes I/O. Short transactions free locks and bu ers more frequently, which improves concurrency, but they also increase log I/O (the increase is slight, since the use of group commits in logging dilutes the e ect of increased log I/O in this case). Longer transactions minimize I/O, but they hold locks longer and thus reduce concurrency.
You can modify the implicit locking structure of the table by changing the table type with the ALTER TABLE statement. You can use the LOCK TABLE statement to override the implicit lock mode for a given transaction. Instead of RR, you can use the CS (Cursor Stability), RC (Read Committed), and RU (Read Uncommitted) isolation levels to reduce the duration of certain locks in a transaction. These strategies can help promote improved concurrency and reduced deadlocks.
Using Row Level Locking Row level locking provides the nest level of lock granularity, where only the row that is read or updated is locked. By locking the row alone, ALLBASE/SQL allows other concurrent transactions to access other rows on the same page. This is in contrast with page level locking, where an entire page containing the row is locked, with the result that concurrent transactions accessing the same page must wait until the lock is released.
As an example, consider a table having 100 pages containing 100 rows each. A scan of the whole table will acquire 100*100 row locks for row locking, in addition to 100 intention locks on the pages and an intention lock on the table, for a total of 10,101 lock objects. With page level locking, the total is only 101 lock objects. A table that is a good candidate for row level locking is one in which the following are true: Rows are small.
Using KEEP CURSOR After you specify KEEP CURSOR in an OPEN statement, a COMMIT WORK does not close the cursor, as it normally does. Instead, COMMIT WORK keeps the cursor open and begins a new transaction while maintaining the cursor position. This makes it possible to update tuples in a large active set, releasing locks as the cursor moves from page to page, instead of requiring you to reopen and manually reposition the cursor before the next FETCH.
Using Procedures and Rules Stored procedures, used independently or invoked by rules, can improve performance in the DBEnvironment. For applications that access a local DBEnvironment, combining multiple SQL statements and logic in a stored procedure can improve performance by reducing the overhead of multiple calls to SQLCore. For applications that operate on a remote DBEnvironment or in a client/server environment, each call to SQLCore must travel across the network.
For example, the following UPDATE statement specifying two dynamic parameters could be put into either a string or a host variable (in this case a string) in your program, then prepared and executed: PREPARE Cmd FROM 'UPDATE PurchDB.Parts SET SalesPrice = ? WHERE PartNumber = ?;' Execute the dynamic command using host variables to provide dynamic parameter values: EXECUTE Cmd USING :SalesPrice, :PartNumber You could now loop back to provide di erent values for SalesPrice and PartNumber.
5 Guidelines on System Administration System administration includes both DBA functions for ALLBASE/SQL and system manager functions for MPE/iX. Careful coordination between these roles is important in performance tuning. This chapter includes information on DBA guidelines Network guidelines MPE/iX system guidelines DBA Guidelines Several aspects of database administration a ect performance. To improve performance in these areas, you need DBA authority.
Developing Application Programs The following tips relate to preprocessing of application programs: Use separate development and production DBEnvironments, because during development, frequent re-preprocessing of applications locks system catalog pages for extended periods. You can later use the ISQL INSTALL command to move nished modules into your production DBEnvironments. The INSTALL command locks the same system catalog pages as the preprocessor, but it only does so a single time.
This includes any two tables that show I/O at the same time, any two B-tree indexes that show I/O at the same time, or a table that shows I/O at the same time as the B-tree index de ned on it. To monitor I/O, run SQLMON and access the SampleIO subsystem. When you access objects simultaneously, the disk heads may move back and forth between the objects. This movement slows access to data. To avoid this problem, use the SQLUtil MOVEFILE command to place the competing objects on di erent drives.
processes on the system and on the type of application programs running on the system. The DBA may be able to make estimates. Excessive shared memory causes page faults. You never get a performance bene t by de ning more page space than stays in real memory. If the data bu ers force paging of virtual space, having too many bu ers degrades performance. When you are not using pseudomapped les, use about 6 to 12 times the number of active transactions.
If a table is too big to t in real memory and the access is random, add a bu er page for aging for each transaction before the small table is accessed. This allows for LRU. Estimate the table access pattern in your transactions. If small tables are accessed in 50% of them or more, you should gain by having a large enough bu er pool to hold the small tables. The right number of bu ers is approximately the number needed to hold the small tables and the aged pages of the large tables.
Based on the numbers of pages given above, and assuming the default number of data bu er pages (100), the application will require an average of 7 I/Os per transaction, as shown in the following table: Source Operation Small table Read data Write data Large table Read second-level index Read leaf index Read data Write data Third table Write data Commit Write log Total Number of I/Os 1 1 1 1 1 1 .01 1 7.
We need enough pages in memory so that pages from the large table are less recently used than pages from the small table. A minimal estimate would be 120 pages; a more comfortable estimate would allow 160 pages.
Second Threshold for Performance Gain The next performance gain comes from tting all the pages from the second level of the large table's index into the data bu er cache.
Cautions The size of the page pool combined with other demands on memory should not exceed available real memory. Checkpoints may take longer if the bu er pool is larger. A checkpoint writes to disk all dirty pages in the bu er pool. If there is a consistent fraction of dirty pages, the checkpoint takes longer with a large bu er pool. However, if the large bu er pool is lled with read-only data, the checkpoint may not take much longer.
To allocate pages for the runtime control block, you can use the START DBE statement or the ALTDBE command. To monitor the usage of the runtime control block, start SQLMON and go to the Overview screen, as described in the chapter \Getting Started with SQLMON." Choosing a Number of Log Buffer Pages The number of log bu ers is independent of the number of data bu ers. The log bu er pages are only 512 bytes (in contrast with the data bu er pages, which are 4096 bytes).
When you use a large log bu er size, you avoid frequent ushing of the bu er to disk. Large nonarchive log les can reduce the number of system checkpoints. To display the number of system checkpoints, run SQLMON and examine the IO screen. If you observe a large number of checkpoints and log bu er writes on the IO screen, you should increase the size of the nonarchive log les. Checkpoints should be postponed as far as possible, as long as rollback recovery time is within limits.
By default, the number of pages used for each temporary space le is 256. The total amount of space used is all that is available in the current group. Tips for Using Temporary Spaces If you do large sorts or create large indexes, use CREATE TEMPSPACE to de ne temporary spaces. Be sure to specify a MaxTempFileSize large enough for your needs. Create one temporary space per volume set to spread scratch les across volume sets and avoid lling up a single volume set.
Performance Hints for Large Sorts Issue the UPDATE STATISTICS statement after loading a large table and before issuing the CREATE INDEX statement. After you issue CREATE INDEX, issue another UPDATE STATISTICS statement to get the correct statistics for the index. Make sure that you have allocated enough temporary space to hold the scratch les. Join Methods ALLBASE/SQL uses two join methods: nested loop join and sort/merge join. Nested loop joins are usually much faster than sort/merge joins.
When there are more than 12 sections in memory, the system deletes only those sections that are not opened. Sections are considered opened if they are cursor sections and have been opened by the OPEN CursorName statement. The system does not delete dynamic sections, even if the cache limit is exceeded. The system can keep more than 12 (or 4) sections in memory, if all of them are opened cursors. The only limit is the amount of user heap space available.
Network Guidelines If you are using ALLBASE/SQL in a network, you should structure your applications with remote access in mind. Remote database access is faster when you send BULK requests to the remote DBEnvironment. For example, if you use the simple FETCH statement on a cursor opened on a remote database, the network must process each successive FETCH as a separate request for data. But if you use BULK FETCH, you make only one request, for a larger number of rows.
Using Memory-Resident Data Buffers On MPE/iX systems, you can freeze data bu er pages in memory so that they are not swapped out by the operating system. When you enable this feature, the operating system does not swap ALLBASE/SQL data bu er pages in and out of memory.
6 Getting Started With SQLMON This chapter describes the basic operations of SQLMON. You will learn how to start the program, exit the program, display screens, modify SQLMON variables, and access online help. The last section in this chapter, \Monitoring Tasks," explains which screens and elds to check as you use SQLMON. Introduction SQLMON is an online diagnostic tool that monitors the activity of a DBEnvironment.
SQLMON has six subsystems, named Overview, IO, Load, Lock, SampleIO, and Static. The SQLMON prompts identify the current subsystem.
Invoking SQLMON Screens To access an SQLMON screen, type the name of the screen at a subsystem prompt. For example, to invoke the Overview screen, issue the following command: NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => OVERVIEW The commands are not case sensitive, and you can abbreviate them.
Table 6-1 lists the SQLMON screens by subsystem. Table 6-1. SQLMON Screens Subsystem IO Load Lock 6-4 Screen Name Description IO Performance information on the data and log bu er pools. IO Data Program Data bu er pool information for each program being run by sessions attached to the DBEnvironment. IO Data Session Data bu er pool information for each session attached to the DBEnvironment.
Table 6-1. SQLMON Screens (continued) Subsystem Overview SampleIO Static Screen Name Description Overview Important aspects of the DBEnvironment's performance, such as the data bu er pool miss rate and the amount of available runtime control block space. Overview Program Session information for each program. Overview Session Information about all sessions connected to the DBEnvironment. SampleIO DBEFile I/O information.
Table 6-2 summarizes the abbreviated commands used to invoke SQLMON screens. You can also issue the commands in the \Di erent Subsystem" column from the same subsystem. Table 6-2.
Leaving an SQLMON Screen To exit an SQLMON screen, press Return. If the subsystem prompt does not appear immediately, the cycle option is probably set to a value other than OFF. Therefore, the screen is displayed for a certain number of refresh cycles, instead of disappearing immediately.
Figure 6-1 shows how the SQLMON screens and subsystems are organized. Figure 6-1.
Setting SQLMON Variables You can modify SQLMON environment variables using the SET commands. For example, the following command sorts rows on a screen according to the value in the screen's second column: NNNNNNNN SQLMONITOR IO => SET SORTIODATA 2 If you omit the last parameter (the number after SORTIODATA), SQLMON issues a prompt: NNNNNNNN SQLMONITOR IO => SET SORTIODATA <0=OFF,1=BUFF ACCESS,2=DATA DISK RD,3=DATA DISK WR,4=MISS RATE>: 2 You can execute a SET command only from a subsystem prompt.
Accessing Online Help SQLMON provides extensive online help, with descriptions of screens, elds, subsystems, and commands, as well as performance tuning hints. The help facility is context sensitive; for example, if you request tuning information from within the Lock subsystem, you see only tuning hints related to locking issues.
SQLMONITOR [ HELP LOCK MEMORY ] => control SQLMONITOR [ HELP LOCK MEMORY ] => tune When you want to leave the help facility, type //, EXIT, or QUIT: SQLMONITOR [ HELP LOCK MEMORY ] => // SQLMONITOR [ LOCK ] => You can also add options to the HELP command when you enter the help facility.
Creating Batch Reports When running SQLMON in batch mode, use the following commands in the job before invoking the screens: SET ECHO ON Echoes SQLMON commands on the $STDLIST. SET DBENVIRONMENT Speci es the DBEnvironment to be monitored. SET CYCLE 1 Stops the display of screens after one refresh interval. SET OUTPUT Speci es the name of the le containing the batch report. The following batch job runs SQLMON and copies the Static screen to an output le named Report1: ! JOB username.
not update statistics in the system catalog or invalidate stored sections). SQLMON does not acquire locks on user tables during this processing. To improve performance, you can issue the command SET DBEFILESET DBEFileSetName which improves performance, because SQLMON obtains information only about the objects in DBEFileSetName , thereby reducing the number of serial scans that must be performed Serial scans are performed only once on DBEFileSetName .
Table 6-4.
Table 6-6.
Table 6-8.
Table 6-9.
Table 6-10.
Table 6-11.
Table 6-14.
7 Troubleshooting with SQLMON This chapter provides examples on how to troubleshoot performance problems with each of SQLMON's subsystems: Overview IO Load Lock SampleIO Static Overview Subsystem You can use the Overview subsystem to determine the overall cause of a performance problem, and then go to another subsystem for detailed information.
Therefore, you may need to raise the transaction limit. Use the Load subsystem for more information. Lock Contention The next screen shows that there are 5 active transactions (ACTIVE XACT) and 4 impeded transactions (IMPEDE XACT). This means that 4 out of 5 transactions are waiting to acquire a lock. d a c b We also see that 25% of all lock requests are not granted immediately because other sessions hold incompatible locks. d a c b This DBEnvironment de nitely has a locking problem.
After you have identi ed the sessions that have the most locks, use the Lock TabSummary screen to identify the programs each session is running and the tables that have the greatest number of locks. You may wish to change some PUBLICROW tables to PUBLIC to reduce the memory overhead associated with them. High Data Buffer Miss Rate If the value of the DATA BUFFER MISS RATE eld on the Overview screen is high, DBEnvironment performance degrades due to increased I/O.
IO Subsystem Slow DBEnvironment performance is often caused by I/O activity. Use the IO subsystem to determine if the DBEnvironment has insu cient data bu er space or insu cient log bu er space. Note You should use the Static subsystem to remove indirect rows, eliminate over ow chains from hashed tables, and recluster appropriate indexes before you use the IO subsystem to tune I/O. Most of the information displayed on the IO screens is for logical I/O, not physical I/O.
The BUFF ACCESS, DATA DISK RD, and DATA DISK WR elds of the IO screen provide more information on data bu er I/O activity. The following screen shows that on average BUFF ACCESS is 3, which means that 3 page requests are made to the data bu er pool every 10.0 seconds. d a c b You can also see that on average, DATA DISK RD is 1, which means that 1 of the 3 pages does not reside in the pool. That page may need to be read from disk, resulting in physical I/O.
To determine which sessions are performing log bu er I/O, check the IO Log Session screen. In the following example, PIN 2 is responsible for all of the log bu er I/O activity.
Load Subsystem The Load subsystem is useful in troubleshooting throughput problems. This section describes how to handle transaction delays, rollbacks, and lock contention. Transaction Delays The ACTIVE XACT, IMPEDE XACT, and THROTTLE WT elds on the Load screen help identify the source of transaction delays. In the following screen, these elds indicate two problems. d a c b The rst problem is that the transaction limit has been reached. The transaction limit (MAX XACT) for the DBEnvironment is 5.0.
d a c b On average, ROLLBK WORK is 0, which means that typically no transactions are rolled back. However, during the last refresh cycle of the LOAD screen, ve transactions were rolled back. To nd out which sessions are rolling back transactions, access the Load Session screen. On the following screen, CID 4 is responsible for most of the rolled back transactions.
Lock Subsystem Use the Lock subsystem to troubleshoot performance bottlenecks caused by lock contention. This section describes how to handle lock waits, deadlocks, and lock allocation failures. Lock Waits If an end user complains of a hung session, perhaps his or her session is actually waiting for a lock. A session will wait for a lock if another session has already acquired an incompatible lock on the same object.
d a c b Now you can invoke the Lock Session screen for PID 6167, which has acquired a lock on the PurchDB.Vendors table. To make the screen easier to read, limit the lock information displayed to the PurchDB.Vendors table. Change the lock lter so that all locks are displayed. NNNNNNNNNNNNNN SQLMONITOR LOCK => SET LOCKTABFILTER PurchDB.
A better solution might be to change the transaction's isolation level. The XID eld displays a system-generated integer that uniquely identi es the transaction, but it does not identify the SQL statements that have been executed. The LABEL eld displays an 8-character string that the application program de nes in the BEGIN WORK or SET TRANSACTION statements. If the application program uses the LABEL eld carefully, it will be easy for you to debug all the transactions in your application programs.
Window 2 Window 1 Window 2 Exit from the ISQL browser but do not commit work. UPDATE PurchDB.SupplyPrice SET UnitPrice = 1.2 * UnitPrice SELECT * FROM PurchDB.
NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => SET LOCKFILTER //WC// Invoke the Lock screen to see which tables have been locked: NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => /lock d a c b In the Lock screen, you can see the PurchDB.Parts and PurchDB.SupplyPrice tables. There are two locks on each table: a share plus intent exclusive (6) lock, and a share (S) lock. The characters displayed in inverse video represent locks held by sessions that are waiting.
d a c b If you specify CID 2, you see the following screen. d a c b Note that CID 6 is waiting for CID 2 to release the lock on the table PurchDB.Supplyprice. These two sessions are indeed deadlocked. Step 5 Release the Frozen Session In window 4, edit a le named SQLUNFRZ and enter the deadlock error number, 1024, on the rst line. The le's group and account must match the frozen session's or DBEnvironment's group and account.
Step 3 Generate the Error In window 1, run the application that encounters the lock allocation failure.
Freezing DBEnvironment Sessions ALLBASE/SQL allows you to freeze a session whenever it encounters a certain DBCORE error. While the session is frozen, you can use SQLMON to examine the session's locks. To freeze a session, set the DBCORERR environment variable to one or more DBCORE error numbers. When the session encounters the errors you specify, it freezes.
For example, to release any session that froze because it encountered either DBCORE error 1024 or 1035, use an editor to create a le named /tmp/SQLunfrz.
SampleIO Subsystem The SampleIO subsystem is useful for balancing the I/O load of a DBEnvironment. The SampleIO screens display the amount of data bu er swapping activity for DBEFiles, tables, indexes, and referential constraints. For more information, refer to the section \Load Balancing" in the chapter \Guidelines on System Administration." You should use the IO subsystem to tune the size of the data bu er pool before you use the SampleIO subsystem to balance load.
4. SQLMON displays a period each time a refresh cycle completes. In the example above, SQLMON displays a period every 10 seconds, because REFRESH is set to 10. It displays 5 periods, because CYCLE is 5. SQLMON takes a total of 125 samples, which is 5 refresh cycles X 25 samples each. After SQLMON has completed the sampling, issue the command SET SAMPLING OFF. If you are using SQLMON interactively, and if CYCLE has a value other than OFF, you should also issue SET CYCLE OFF.
d a c b Using the SET DISPLAYSAMPLES Command If you want to display screens in the SampleIO subsystem during sampling, instead of seeing the refresh scale shown above, you can do so by issuing a SET DISPLAYSAMPLES ON command. When DISPLAYSAMPLES is ON, the screen is refreshed after each set of samples is obtained, that is, after each refresh cycle. When DISPLAYSAMPLES is OFF, you see a refresh scale instead of the screen, and a period is displayed each time a refresh cycle completes.
############################################################################### # Take samples every 10 minutes for 8 hours: # ############################################################################### # Take 1 set of 25 samples every 10 minutes (10 min x 60 sec/min = 600 sec) /set refresh 600 # # Take 48 sets of samples (8 hours = 480 min x (1 cyc/10 min) = 48 cyc) /set cycle 48 # ############################################################################### # Now perform the sampling.
Understanding the Internals of Sampling SampleIO statistics are generated entirely by SQLMON. In other words, the data is not obtained by simply reading from some existing table where these statistics are maintained. The more often you perform sampling, the more complete the I/O statistics become. However, SQLMON uses CPU time whenever it takes samples. The larger the number of samples SQLMON takes, the larger the amount of CPU time it consumes.
Static Subsystem The Static subsystem allows you to troubleshoot full DBEFileSets, poorly clustered indexes, indirect rows, and hash over ow pages. Full DBEFileSets A transaction fails if it attempts to insert a row into a table whose DBEFileSet is full. To prevent this, you should monitor DBEFileSet capacity on a regular basis. The Static DBEFile screen displays the capacity of each DBEFile and DBEFileSet in a DBEnvironment. In the following Static DBEFile screen, the InvoiceFS DBEFileSet is 92% full.
Indirect Rows Avoid indirect rows, because they waste disk space and increase the amount of I/O needed to access data. Use the Static Indirect screen to detect the presence of indirect rows. On the screen that follows, the PurchDB.Invoice table has 12% indirect rows. For instructions on how to remove indirect rows, see \Unloading and Reloading to Remove Indirect Rows" in the chapter \Guidelines on Logical and Physical Design.
8 SQLMON Screen Reference This chapter describes each of the SQLMON screens in alphabetical order. You will nd a table listing each of the SQLMON screens in the chapter \Getting Started with SQLMON." For complete descriptions of the SQLMON commands, refer to the chapter \SQLMON Command Reference.
IO Screen This screen provides I/O information on the data and log bu er pools. d a c b To invoke the IO screen, use the i command from the IO subsystem or the /i command from other subsystems. When you issue the SET DBENVIRONMENT command, the counters on this screen are set to zero. Field Definitions REFRESH SESSIONS per sec BUFF ACCESS DATA DISK RD DATA DISK WR LOG BUFF WR LOG DISK RD 8-2 The screen refresh rate, in seconds. The number of DBEnvironment sessions.
IO Screen The number of log pages written from the log bu er pool to disk. The operating system performs a physical write for each log bu er write. The number of checkpoints taken. Checkpoints are performed automatically, when the nonarchive log becomes full, or when you issue a CHECKPOINT command. The percentage of pages that are not in the data bu er pool at request time.
IO Data Program Screen This screen provides I/O data bu er pool information for each program being run by sessions attached to the DBEnvironment. d a c b To invoke the IO Data Program screen, use the d p command from the IO subsystem or the /i d p command from other subsystems. When you invoke this screen, its counters are set to zero. Field Definitions REFRESH SESSIONS SORTIODATA CID BUFF ACCESS DATA DISK RD DATA DISK WR 8-4 The screen refresh rate, in seconds.
IO Data Program Screen MISS RATE The percentage of pages that are not in the data bu er pool, calculated as follows: MISS RATE = (DATA DISK RD / BUFF ACCESS) * 100 PROGRAM NAME AVERAGE The name of the program being run. All of the sessions running a program are listed beneath the PROGRAM NAME. For a given column, the average amount of work performed by the processes in the list. Each program has a list of processes beneath it.
IO Data Session Screen This screen provides data bu er pool I/O information for each session attached to the DBEnvironment. d a c b To invoke the IO Data Session screen, use the d s command from the IO subsystem, or use the /i d s command from other subsystems. When you invoke this screen, its counters are set to zero. Field Definitions REFRESH SESSIONS SORTIODATA CID BUFF ACCESS DATA DISK RD DATA DISK WR 8-6 The screen refresh rate, in seconds. The number of DBEnvironment sessions.
IO Data Session Screen MISS RATE The percentage of pages that are not in the data bu er pool at request time. The percentage is calculated as follows: MISS RATE = (DATA DISK RD / BUFF ACCESS) * 100 Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET REFRESH Controls the refresh rate of the screen. SET REFRESH 5 SET SORTIODATA Sorts sessions in descending order according to the SET SORTIODATA 2 value of the speci ed column.
IO Log Program Screen This screen provides log bu er pool I/O information for each program being run by sessions attached to the DBEnvironment. d a c b To invoke the IO Log Program Screen, use the l p command from the IO subsystem or the /i l p command from other subsystems. When you invoke this screen, its counters are set to zero. Field Definitions REFRESH SESSIONS SORTIOLOG CID LOG BUFF WR LOG DISK RD LOG DISK WR 8-8 The screen refresh rate, in seconds. The number of DBEnvironment sessions.
IO Log Program Screen PROGRAM NAME AVERAGE The name of the program being run. All of the sessions running a program are listed beneath the PROGRAM NAME. For a given column, the average amount of work performed by the processes in the list. Each program has a list of processes beneath it. Each process has a line of information, which shows percentages in each column. The line shows the exact percentages of work each process performs, in each column.
IO Log Session Screen This screen provides log bu er pool I/O information for each session attached to the DBEnvironment. d a c b To invoke the IO Log Session screen, use the l s command from the IO subsystem or the /i l s command from other subsystems. When you invoke this screen, its counters are set to zero. Field Definitions REFRESH SESSIONS SORTIOLOG CID LOG BUFF WR LOG DISK RD LOG DISK WR 8-10 The screen refresh rate, in seconds. The number of DBEnvironment sessions.
IO Log Session Screen Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET REFRESH Controls the refresh rate of the screen. SET REFRESH 5 SET SORTIOLOG Sorts sessions in descending order according to the SET SORTIOLOG 2 value of the speci ed column. SET TOP Limits the number of sessions displayed.
Load Screen This screen provides information useful in measuring the transaction throughput e ciency of the DBEnvironment. d a c b To invoke the Load screen, use the l command from the Load subsystem or the /loa command from other subsystems. When you issue the SET DBENVIRONMENT command, the counters on this screen are set to zero. See the section \Display Conventions" on the next page for a description of the CUR, AVG, and MAX headings.
Load Screen The scale used to identify the length of the inverse video bars and the data in the CUR, AVG, and MAX columns. In the above example, the MAX frequency of LOCK REQSTS is 40 per 1.0 second. The number of BEGIN WORK commands being processed. This number includes both explicit BEGIN WORK commands that sessions issue and BEGIN WORK commands that ALLBASE/SQL generates. The number of COMMIT WORK commands being processed. The number of ROLLBACK WORK commands being processed.
Load Program Screen This screen provides transaction throughput information for each program being run by sessions attached to the DBEnvironment. d a c b To invoke the Load Program screen, use the p command from the Load subsystem or the /loa p command from other subsystems. When you invoke this screen, its counters are set to zero. Field Definitions REFRESH SESSIONS SORTLOAD CID BEGIN WORK COMMIT WORK ROLLBK WORK DEADLOCKS 8-14 The screen refresh rate, in seconds.
Load Program Screen PROGRAM NAME AVERAGE The name of the program being run. All of the sessions running a program are listed beneath PROGRAM NAME. For a given column, the average amount of work performed by the processes in the list. Each program has a list of processes beneath it. Each process has a line of information to the right of its ID. The line shows the exact percentages of work each process performs, in each column.
Load Session Screen This screen provides transaction throughput information for each session attached to the DBEnvironment. d a c b To invoke the Load Session screen, use the s command from the Load subsystem or the /loa s command from other subsystems. When you invoke this screen, its counters are set to zero. Field Definitions REFRESH SESSIONS SORTLOAD CID BEGIN WORK COMMIT WORK ROLLBK WORK DEADLOCKS 8-16 The screen refresh rate, in seconds. The number of DBEnvironment sessions.
Load Session Screen Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET REFRESH Controls the refresh rate of the screen. SET REFRESH 5 SET SORTLOAD Sorts sessions in descending order according to the SET SORTLOAD 2 value of the speci ed column. SET TOP Limits the number of sessions displayed.
Lock Screen This screen displays lock activity data for the entire DBEnvironment. d a c b To invoke the Lock screen, use the l command from the Lock subsystem or the /loc command from other subsystems. Field Definitions LOCKFILTER The screen refresh rate, in seconds. The current setting of the LOCKFILTER variable, which determines the type of lock information that appears on the screen. G For more information, see the SET LOCKFILTER command.
Lock Screen The character indicates the mode of the lock, as listed below: S Share X Exclusive s Intent share x Intent exclusive 6 Share + intent exclusive R Recovery exclusive r Recovery intent exclusive v Recovery share + intent exclusive When a session is waiting to acquire or convert a lock, the character indicates the lock mode the session is attempting to acquire. Related SET Commands The SET commands in the following table a ect this screen.
Lock Impede Screen This screen identi es the locks granted to a particular session that are causing other sessions to wait. d a c b To invoke the Lock Impede screen, use the i command from the Lock subsystem or the /loc i command from other subsystems. You can also specify the number in response to an SQLMON prompt, for example: NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => /loc i PIN: 18381 When you invoke the screen, you must specify the connection identi cation number of a session.
Lock Impede Screen Field Definitions REFRESH CID PIN STATUS XID ISO USER@ACCT LABEL PRI PROGRAM NAME G OWNER.TABLE [/CONSTRAINT] PAGE/ROW ID GWC The screen refresh rate, in seconds. The DBEnvironment connection identi cation number. The MPE/iX process identi cation number of the DBEnvironment session you speci ed when you invoked this screen. The status of the DBCore call the session has made. This eld is equivalent to the STATUS column of the SYSTEM.CALL pseudotable.
Lock Impede Screen MOD NEW WAITING The mode of lock that has been granted, as follows: S Share X Exclusive s Intent share x Intent exclusive 6 Share + intent exclusive R Recovery exclusive r Recovery intent exclusive v Recovery share + intent exclusive The lock mode the waiting session is attempting to acquire. If the lock has been granted, this eld is blank.
Lock Memory Screen Lock Memory Screen This screen lists the number of locks allocated to each session according to the lock granularity. d a c b To invoke the Lock Memory screen, use the m command from the Lock subsystem or the /loc m command from other subsystems. Field Definitions REFRESH SESSIONS SORTLOCK CID TABLE PAGE ROW The screen refresh rate, in seconds. The number of DBEnvironment sessions. An indicator of how the sessions are sorted.
Lock Memory Screen The total number of locks allocated to the session, calculated as follows: TOTAL TOTAL = TABLE + PAGE + ROW MAXTOTAL The maximum number of TOTAL locks allocated to the session since it was attached to the DBEnvironment. Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET REFRESH Controls the refresh rate of the screen.
Lock Object Screen Lock Object Screen This screen identi es the sessions in the lock queue for a particular table, page, or row. d a c b To invoke the Lock Object screen, use the o command from the Lock subsystem or the /loc o command from other subsystems. By default, the LOCKOBJECT variable is set to ALL. Therefore, by default, the LOCK OBJECT screen displays all the lock objects that qualify under the LOCKFILTER and LOCKTABFILTER variables.
Lock Object Screen Field Definitions REFRESH LOCKFILTER G OWNER.TABLE [/CONSTRAINT] PAGE/ROW ID GWC MOD NEW CID PIN USER@ACCT XID ISO PRI LABEL 8-26 The screen refresh rate, in seconds. If the lock object is ALL, the current setting of the LOCKFILTER variable. The LOCKFILTER variable determines the type of lock information that is provided. For more information, see the description of the SET LOCKFILTER command. The granularity of the lock, either table (T), page (P), or row (R).
Lock Object Screen PROGRAM NAME The name of the program being run. Display Conventions Sessions waiting for locks appear on the screen in inverse video. If the session is converting the lock to a stronger mode, the elds are in inverse video and underlined. The absence of inverse video and underlining indicates that the lock has been granted. Related SET Commands The SET commands in the following table a ect this screen.
Lock Session Screen This screen displays lock activity data for a single session. d a c b To invoke the Lock Session screen, use the s command from the Lock subsystem or the /loc s command from other subsystems. You must specify the session's connection identi cation number when you invoke the screen.
Lock Session Screen STATUS XID ISO USER@ACCT LABEL PRI PROGRAM NAME G OWNER.TABLE [/CONSTRAINT] PAGE/ROW ID GWC MOD The status of the DBCore call the session has made. This eld is equivalent to the STATUS column of the SYSTEM.CALL pseudotable. The possible values are listed below: Running DBCore is processing a call from a session. The session has made a DBCore call, but is waiting until Waiting for it can acquire a resource that is currently unavailable.
Lock Session Screen NEW WAITS FOR The lock mode the waiting session is attempting to acquire, just as listed under MOD. If the lock has been granted, this eld is blank. The connection identi er of a session that has access to the lock that the current session is waiting for. Other sessions might also have access to the lock. For a complete list of the sessions in the lock queue, you can access the Lock Object screen. Display Conventions If the session appears in inverse video, it is waiting for a lock.
Lock TabSummary Screen Lock TabSummary Screen This screen displays summarized information about the locks a session holds. The locks are grouped by granularity, that is, by table locks, page locks, and row locks. You can display lock information for a single session or for all sessions connected to the DBEnvironment. d a c b To invoke the Lock screen, use the t command from the Lock subsystem or the /loc t command from other subsystems.
Lock TabSummary Screen Field Definitions REFRESH CID PIN STATUS If the value of CID is ALL, this eld is not displayed. The transaction identi er, equivalent to the XID column of the SYSTEM.TRANSACTION pseudotable. If the value of CID is ALL, this eld is not displayed. The isolation level, equivalent to the ISOLATION LEVEL column of the SYSTEM.TRANSACTION pseudotable. If the value of CID is ALL, this eld does not appear.
Lock TabSummary Screen OWNER.TABLE [/CONSTRAINT] The name of the table or referential constraint that is locked. Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET REFRESH Controls the refresh rate of the screen. SET REFRESH 5 SET TOP Limits the number of objects displayed SET TOP 10 at each granularity level.
Overview Screen This screen displays important aspects of the DBEnvironment's performance, such as the data bu er pool miss rate and the amount of available runtime control block space. d a c b To invoke the Overview screen, enter the o command from the Overview subsystem or the /o command from other subsystems. Field Definitions REFRESH SESSIONS MAX XACT ACTIVE XACT IMPEDE XACT DATA BUFFER MISS RATE LOCK WAIT % 8-34 The screen refresh rate, in seconds. The number of DBEnvironment sessions.
Overview Screen RUNTIME CB % The percentage of runtime control block space that is occupied, calculated as follows: RUNTIME CB % = (Used Pages / Max Pages) * 100 Used Pages Max Pages LOG FULL % Each runtime control block page holds 4096 bytes. Lock management is the single greatest user of runtime control block space. The number of runtime control block pages in use. The maximum number of runtime control block pages.
Overview Program Screen This screen displays session information for each program. d a c b To invoke the Overview Program screen, use the p command from the Overview subsystem or the /o p command from other subsystems. Field Definitions REFRESH SESSIONS CID PIN USER@ACCT STATUS XID 8-36 The screen refresh rate, in seconds. The number of DBEnvironment sessions. The DBEnvironment connection identi cation number. The MPE/iX process identi cation number of the DBEnvironment session.
Overview Program Screen ISO PRI LABEL PROGRAM NAME The isolation level, equivalent to the ISOLATION LEVEL column of the SYSTEM.TRANSACTION pseudotable. For more information, see the chapter \Concurrency Control through Locks and Isolation Levels" in the ALLBASE/SQL Reference Manual . The transaction priority, equivalent to the PRIORITY column of the SYSTEM.TRANSACTION pseudotable. The lowest transaction priority is 255, and the highest is 0.
Overview Session Screen This screen identi es all sessions connected to the DBEnvironment. d a c b To invoke the Overview Session screen, use the s command from the Overview subsystem or the /o s command from other subsystems. Field Definitions REFRESH SESSIONS CID PIN USER@ACCT STATUS 8-38 The screen refresh rate, in seconds. The number of DBEnvironment sessions. The DBEnvironment connection identi cation number. The MPE/iX process identi cation number of the DBEnvironment session.
Overview Session Screen XID ISO PRI LABEL The transaction identi er, equivalent to the XID column of the SYSTEM.TRANSACTION pseudotable. The isolation level, equivalent to the ISOLATION LEVEL column of the SYSTEM.TRANSACTION pseudotable. See the chapter \Concurrency Control through Locks and Isolation Levels" in the ALLBASE/SQL Reference Manual . The transaction priority, equivalent to the PRIORITY column of the SYSTEM.TRANSACTION pseudotable.
SampleIO Screen This screen displays DBEFile I/O information. d a c b To invoke the SampleIO screen, use the s command from the SampleIO subsystem or the /sa command from other subsystems. For more information about using the SAMPLEIO subsystem, see the chapter \Troubleshooting with SQLMON." Field Definitions REFRESH SORTSAMPLEIO DBEFILESET DBEFILE SWAPIN SWAPOUT TOTALIO 8-40 The screen refresh rate, in seconds. An indicator of how the DBEFiles are sorted.
SampleIO Screen Display Conventions When the DISPLAYSAMPLES variable is set to ON, a row displayed in inverse video indicates that DBEFile I/O has occurred during the most recent refresh interval. Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET DISPLAYSAMPLES Determines whether a SampleIO screen is displayed when samples are collected. SET DISPLAYSAMPLES ON SET REFRESH Controls the refresh rate of the screen.
SampleIO Indexes Screen This screen provides index and referential constraint I/O information. d a c b To invoke the SampleIO Indexes screen, use the i command from the SampleIO subsystem or the /sa i command from other subsystems. For more information about using the SAMPLEIO subsystem, see the chapter \Troubleshooting with SQLMON." Field Definitions REFRESH DBEFILESET SORTSAMPLEIO OWNER.TABLE INDEX, CONSTRAINT SWAPIN SWAPOUT TOTALIO 8-42 The screen refresh rate, in seconds.
SampleIO Indexes Screen Display Conventions When the DISPLAYSAMPLES variable is set to ON, a row displayed in inverse video indicates that index I/O has occurred during the most recent refresh interval. Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET DBEFILESET SET DBEFILESET PurchFS Limits the objects displayed to those in the DBEFileSet speci ed.
SampleIO Objects Screen This screen lists the database objects currently residing in the data bu er pool. d a c b To invoke the SampleIO Objects screen, use the o command from the SampleIO subsystem or the /sa o command from other subsystems. For more information about the SampleIO subsystem, see the chapter \Troubleshooting with SQLMON." Field Definitions REFRESH DBEFILESET OWNER.TABLE [/INDEX, CONSTRAINT] CURRENT PGS TOTALIO 8-44 The screen refresh rate, in seconds. The name of the DBEFileSet.
SampleIO Objects Screen Related SET Commands The SET commands in the following table a ect this screen: Command Description Example SET DBEFILESET Limits the objects displayed to those contained in the DBEFileSet speci ed. SET DBEFILESET PurchFS SET DISPLAYSAMPLES Determines whether a SampleIO screen SET DISPLAYSAMPLES is displayed when samples are collected. ON SET REFRESH Controls the refresh rate of the screen. SET REFRESH 5 SET SAMPLING Enables sampling of the data bu er pool.
SampleIO TabIndex Screen This screen displays I/O information about a speci c table, its indexes, and its referential constraints. d a c b To invoke the SampleIO TabIndex screen, use the tabi command from the SampleIO subsystem or the /sa tabi command from other subsystems. You must specify a table name when invoking the SampleIO TabIndex screen, either on the command line or at an SQLMON prompt: NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => /sa tabi PurchDB.
SampleIO TabIndex Screen An approximation of read I/O for the object since the SET DBENVIRONMENT command was issued. An approximation of write I/O for the object since the SET DBENVIRONMENT command was issued. The sum of the SWAPIN and SWAPOUT values. This value approximates total I/O. SWAPIN SWAPOUT TOTALIO Display Conventions When the DISPLAYSAMPLES variable is set to ON, a value displayed in inverse video indicates that I/O on the database object has occurred during the most recent refresh interval.
SampleIO Tables Screen This screen provides table I/O information. d a c b To invoke the SampleIO Tables screen, use the tabl command from the SampleIO subsystem or the /sa tabl command from other subsystems. For more information about using the SampleIO subsystem, see the chapter \Troubleshooting with SQLMON." Field Definitions REFRESH DBEFILESET SORTSAMPLEIO OWNER.TABLE SWAPIN SWAPOUT 8-48 The screen refresh rate, in seconds. The name of the DBEFileSet.
SampleIO Tables Screen The sum of the SWAPIN and SWAPOUT values. This value approximates the total I/O. TOTALIO Display Conventions When the DISPLAYSAMPLES variable is set to ON, a value displayed in inverse video indicates that table I/O has occurred during the most recent refresh interval. Related SET Commands The SET commands in the following table a ect this screen. Command Description Example SET DBEFILESET Limits the tables displayed to those contained in the DBEFileSet speci ed.
Static Screen This screen lists information about indexes, referential constraints, and hash structures for each table contained in a DBEFileSet. d a c b To invoke the Static screen, use the st command from the Static subsystem or the /st command from other subsystems. Field Definitions DBEFILESET HASH? IMAGE? NUMIDX TYPE OWNER.TABLE 8-50 The name of the DBEFileSet. An asterisk in this eld indicates a hashed table.
Static Cluster Screen Static Cluster Screen This screen provides information about the clustering of indexes and referential constraints in a DBEFileSet. d a c b To invoke the Static Cluster screen, use the c command from the Static subsystem or the /st c command from other subsystems. Field Definitions DBEFILESET OWNER.TABLE The name of the DBEFileSet. The keyword DETACHED means that the DBEFileSet is detached.
Static Cluster Screen A clustering PCR (parent-child relationship), created by de ning a referential constraint with the CLUSTERING ON CONSTRAINT clause in the CREATE TABLE statement. The number of pages containing data for the table. The total number of rows in the table.
Static DBEFile Screen Static DBEFile Screen This screen lists the le capacity of each DBEFile in a DBEFileSet. d a c b To invoke the Static DBEFile screen, use the d command from the Static subsystem or the /st d command from other subsystems. Field Definitions DBEFILESET The name of the DBEFileSet. DBEFILESET FULLNESS % The keyword DETACHED means that the DBEFileSet is detached. The keywords DETACHED DBEFILES mean that the DBEFileSet is attached, but some of the DBEFiles within it are detached.
Static DBEFile Screen TYP BD DBEFILE FULLNESS % USED PAGES MAX PAGES The type of the DBEFile, as listed below: TBL Table data pages, including hash structures and long column data IDX Index or referential constraint pages MIX Mixed, indicating either table data, index, or referential constraint pages Whether a DBEFile is bound, indicated by an asterisk. When a table is de ned as hashed, a group of primary pages from up to 16 DBEFiles is allocated for it.
Static Hash Screen Static Hash Screen This screen provides information about the primary and over ow pages of hashed tables. d a c b To invoke the Static Hash screen, use the h command from the Static subsystem or the /st h command from other subsystems. Field Definitions DBEFILESET OWNER.TABLE PRIMPAGES PRIMDATA PRIMOVERF OVERPAGES The name of the DBEFileSet. The keyword DETACHED means that the DBEFileSet is detached.
Static Hash Screen OVERFLOW CHAIN LNGTH MAXOVERFLOW AVGOVERFLOW The maximum and average lengths of the over ow chains, displayed graphically. The maximum length is represented by full bright, inverse video bars. The average length is indicated by half bright, inverse video bars and is delimited on the right with an asterisk. The length of the longest over ow chain in the table. This value is one less than the MAXLEN column of the SYSTEM.
Static Indirect Screen Static Indirect Screen This screen displays information about the indirect rows in each table of a DBEFileSet. d a c b To invoke the Static Indirect screen, use the i command from the Static subsystem or the /s i command from other subsystems. Field Definitions DBEFILESET OWNER.TABLE TABLE INDIRECT ROW % TOTAL ROWS The name of the DBEFileSet. The keyword DETACHED means that the DBEFileSet is detached.
Static Indirect Screen Related SET Commands Use the SET DBEFILESET command to improve performance and to display only those tables contained in a particular DBEFileSet.
Static Size Screen Static Size Screen This screen provides information about the size of tables, indexes, and referential constraints in a DBEFileSet. d a c b To invoke the Static Size screen, use the s command from the Static subsystem or the /st s command from other subsystems. Field Definitions DBEFILESET The name of the DBEFileSet. The keyword DETACHED means that the DBEFileSet is detached.
Static Size Screen FSUSED PAGES OWNER.TABLE TABLE PAGES INDEX PAGES TOTAL PAGES The number of pages in use in the DBEFileSet. This value includes all pages used for table data, indexes, referential constraints, page table pages, and temporary pages. The names of the tables contained within the DBEFileSet.
9 SQLMON Command Reference This chapter describes the SQLMON commands and gives syntax and examples for each. Within this chapter, the commands are arranged alphabetically. You will nd a table summarizing the commands at the end of the chapter. You can execute these commands from an SQLMON subsystem prompt, but not from within the help facility or from an SQLMON screen. In general, the commands are not case sensitive; however, some of them contain case sensitive parameters.
EXIT Leaves SQLMON Scope SQLMON Only SQLMON Syntax 2 E XIT 3 Description This command is equivalent to the QUIT command.
HELP HELP Invokes the SQLMON online help facility Scope SQLMON Only SQLMON Syntax 2 2 CONTROL 36 INFO 62 3 6 ScreenName 6 2 4 SUBSYSTEM H ELP 6 6 2 6 TUNE Number ? 6 4 MAIN 33 77 77 57 3 7 7 7 5 SetCommand Parameters ScreenName CONTROL INFO SUBSYSTEM TUNE Number MAIN SetCommand Displays help text for ScreenName . Displays information about the SET commands a ecting the current screen. Describes each eld on the current screen. Describes the subsystem.
HELP Examples To invoke the help facility for the last screen displayed, issue the HELP command without options, as in NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => HELP You can use a question mark instead of the HELP command, as in: NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => ? To get general information on SQLMON from within the help facility, use the following command: NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR HELP OVERVIEW => MAIN In the last example, you do not need to enter the keyword H
QUIT QUIT Leaves SQLMON Scope SQLMON Only SQLMON Syntax 2 Q UIT 3 Description This command is equivalent to the EXIT command.
SET Displays the current settings of the SQLMON variables Scope SQLMON Only SQLMON Syntax SET Description Each setting corresponds to a SET command. For example, the CYCLE setting is speci ed by the SET CYCLE command.
SET CYCLE SET CYCLE Speci es the number of refresh cycles for which screens are displayed Scope SQLMON Only SQLMON Syntax 2 SET C YCLE 3 NumCycles OFF Parameters NumCycles Speci es that SQLMON displays screens for NumCycles - 1 refresh cycles before returning you to the prompt. An integer value. Speci es that SQLMON displays screens until you press Return. The logical equivalent of a NumCycles value of in nity. The default value. OFF Description This command is useful in batch jobs.
SET DBECONNECT Modi es the behavior of the SET DBENVIRONMENT command Scope SQLMON Only SQLMON Syntax 2 SET DBEC ONNECT 3 ON OFF Parameters When you issue SET DBENVIRONMENT, SQLMON connects to the DBEnvironment and accesses the system catalog. The default value. When you issue SET DBENVIRONMENT, SQLMON does not connect to the DBEnvironment or access the system catalog. ON OFF Description Use SET DBECONNECT OFF if the prompt does not return after you issue SET DBENVIRONMENT.
SET DBEFILESET SET DBEFILESET Determines which DBEFileSets are included on a screen Scope SQLMON Only SQLMON Syntax 2 SET DBEF ILESET 3 DBEFileSetName OFF Parameters DBEFileSetName OFF Displays information only on DBEFileSetName . Displays information on all DBEFileSets.
SET DBEINITPROG Modi es the behavior of the SET DBENVIRONMENT command Scope SQLMON Only SQLMON Syntax 2 SET DBEI NITPROG 3 ON OFF Parameters SQLMON gathers program information for each session when you issue SET DBENVIRONMENT. The default value. SQLMON defers gathering program information for each session until you invoke a screen that displays the information. ON OFF Description If SET DBENVIRONMENT pauses for too long to gather program information, issue the command SET DBEINITPROG OFF.
SET DBENVIRONMENT SET DBENVIRONMENT Speci es which DBEnvironment SQLMON monitors Scope SQLMON Only SQLMON Syntax 2 SET DBEN VIRONMENT 3 2 DBEnvironmentName MAINT=MaintenanceWord 3 OFF Parameters DBEnvironmentName MaintenanceWord OFF The name of the DBEnvironment to be monitored. The maintenance word of the DBEnvironment, required if you are not the DBEnvironment creator or system manager. The default value, indicating that no DBEnvironment has been speci ed.
SET DISPLAYSAMPLES Determines whether a SampleIO screen or a sampling scale is displayed when samples are collected from the data bu er pool Scope SQLMON Only SQLMON Syntax 2 SET DI SPLAYSAMPLES 3 ON OFF Parameters When SAMPLING is ON and you access a SampleIO screen, SQLMON displays the screen for the number of refresh cycles that you have set with SET CYCLE. When SAMPLING is ON and you access a SampleIO screen, SQLMON displays a scale instead of the screen.
SET DISPLAYSAMPLES NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR SAMPLEIO => /sa SAMPLING = ON REFRESH = 10 CYCLE = OFF (One set of samples will be taken every 10 seconds). No limit has been set for the number of samples to be taken. You must press RETURN when you wish to return to the SQLMONITOR prompt.) --------10--------20--------30--------40--------50 12345678901234567890123456789012345678901234567890 ...........
SET ECHO Echoes commands to standard output Scope SQLMON Only SQLMON Syntax 2 SET E CHO 3 ON OFF Parameters Causes your input to appear on standard output. Does not cause your input to appear on standard output. The default value. ON OFF Description When ECHO is set to ON, SQLMON displays on standard output all of the commands you enter. SET ECHO is primarily used in batch jobs. See \Creating Batch Reports" in the chapter \Getting Started with SQLMON.
SET LOCKFILTER SET LOCKFILTER Filters lock information so that only certain information is displayed Scope SQLMON Only SQLMON Syntax 2 3 SET LOCKF ILTER 32 2 . .. LockMode / 2 32 3 2 . . / Granularity TableType .32 3 ... QueueLength / 3 2 32 ... 3 2 / LockStatus 32 ... 3 Parameters TableType Granularity LockStatus LockMode QueueLength Displays only the locks related to the table types you specify.
SET LOCKFILTER Description SET LOCKFILTER a ects only the Lock, Lock Object, and Lock Session screens. The default value for LOCKFILTER is SU/TPR/GWC/SXRsxr6v/1 This value causes all locks to be displayed. You must use all four delimiting slashes in the parameter list, but you can omit any or all of the parameters. If you omit all of the parameters, you set the lock lter to the default value. If you omit some of the parameters, the settings for the missing parameters are unchanged.
SET LOCKFILTER If you omit the last parameter, SQLMON prompts for it, as follows: NNNNNNNNNNNNNN SQLMONITOR LOCK => SET LOCKFILTER PRESS TO SELECT ALL ITEMS FOR EACH PROMPT TABLES GRANULARITY LOCK STATUS LOCK MODES <(S)ystem <(T)able <(G)ranted <(S)Share (s)IS (U)ser (P)age (W)aiting (X)Exclusive (x)IX (6)SIX QUEUE LENGTH (R)Row > (C)onverting > (R)ecovry Excl (r)RIX (v)RSIX > > : U : T P : W : : 2 The current setting for the LOCKFILTER is: TABLES GRANULARITY
SET LOCKOBJECT Speci es the objects that are displayed on the Lock Object screen Scope SQLMON Only SQLMON Syntax 2 2 SET LOCKO BJECT 3 2 32 Owner.Table /Constraint /PageRowID 4 ALL 33 5 OFF Parameters Owner.Table Constraint PageRowID Displays only table lock information about Owner.Table . Displays only constraint lock information about Constraint . Displays only page lock or row lock information about the page or row you specify.
SET LOCKOBJECT Examples To display only the sessions that are requesting a table level lock on PurchDB.Parts, you would enter NNNNNNNNNNNNNN SQLMONITOR LOCK => SET LOCKOBJECT PurchDB.Parts If you omit the last parameter, SQLMON prompts for it, as follows: NNNNNNNNNNNNNN SQLMONITOR LOCK => SET LOCKOBJECT OWNER.TABLE[/CONSTRAINT]: PurchDB.
SET LOCKTABFILTER Filters lock information according to the object speci ed Scope SQLMON Only SQLMON Syntax 2 SET LOCKT ABFILTER 3 2 Owner.Table /Constraint 3 OFF Parameters Owner.Table Constraint Displays only table lock information about Owner.Table . Displays only constraint lock information about Constraint . Default value. OFF Description This command a ects the following screens: Lock Lock Object Lock Session Example Only the locks associated with the PurchDB.
SET MENU SET MENU Controls the display of SQLMON menus Scope SQLMON Only SQLMON Syntax 2 SET M ENU 3 ON OFF Parameters Displays menus at the SQLMON prompt. The default value. Does not display menus. ON OFF Description Expert users may want to set the menu option to OFF.
SET OUTPUT Saves SQLMON screen images in a le Scope SQLMON Only SQLMON Syntax 2 SET OUTP UT 3 SystemFileName OFF Parameters SystemFileName Echoes the screens that SQLMON displays on standard output to SystemFileName . Does not echo screens to a le. The default value. OFF Description If you issue a SET OUTPUT command with a lename, and if the le does not exist, SQLMON creates it.
SET OUTPUT NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => LOCK NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => LOCK OBJECT NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => SET OUTPUT OFF SQLMON overwrites My le and then appends images of the Lock and Lock Object screens to the le. To prevent accidental data loss, SQLMON prompts you before it overwrites the le, as in NNNNNNNNNNNNNNNNNNNNNNNNNN SQLMONITOR OVERVIEW => SET OUTPUT Myfile SystemFileName already exists.
SET REFRESH Determines the refresh rate of the SQLMON screens Scope SQLMON Only SQLMON Syntax 2 SET R EFRESH 32 Seconds 3 Parameters Seconds The number of seconds SQLMON waits before it resamples data and refreshes a screen. The default value is 10. Description This command a ects all screens, except those in the Static subsystem.
SET SAMPLING SET SAMPLING Enables sampling of the data bu er pool Scope SQLMON Only SQLMON Syntax 2 SET SA MPLING 3 ON OFF Parameters Speci es that SQLMON samples the data bu er pool each time a SampleIO subsystem screen is refreshed. The default value. Speci es that SQLMON does not sample the data bu er pool. Improves the performance of SQLMON.
SET SORTIODATA De nes how items are sorted on the IO Data Program and IO Data Session screens Scope SQLMON Only SQLMON Syntax 2 SET SORTIOD ATA 3 SortColumn OFF Parameters SortColumn The column by which items are sorted. SortColumn is an integer from 0 to 4 that represents a column on the screen, as follows: 0 same as OFF 1 BUFF ACCESS 2 DATA DISK RD 3 DATA DISK WR (default) 4 MISS RATE The items are sorted in descending order according to the values in the column.
SET SORTIOLOG SET SORTIOLOG De nes how items are sorted on the IO Log Program and IO Log Session screens Scope SQLMON Only SQLMON Syntax 2 SET SORTIOL OG 3 SortColumn OFF Parameters SortColumn The column by which items are sorted. SortColumn is an integer from 0 to 3 that represents a column on the screen, as follows: 0 same as OFF 1 LOG BUFF WR 2 LOG DISK RD 3 LOG DISK WR (default) The items are sorted in descending order according to the values in the column.
SET SORTLOAD De nes how items are sorted on the Load Program and Load Session screens Scope SQLMON Only SQLMON Syntax 2 SET SORTL OAD 3 SortColumn OFF Parameters SortColumn The column by which items are sorted. SortColumn is an integer from 0 to 4 that represents a column on the screen, as follows: 0 same as OFF 1 BEGIN WORK 2 COMMIT WORK 3 ROLLBK WORK (default) 4 DEADLOCKS The items are sorted in descending order according to the values in the column.
SET SORTLOCK SET SORTLOCK De nes how sessions are sorted on the Lock Memory screen Scope SQLMON Only SQLMON Syntax 2 SET SORTL OAD 3 SortColumn OFF Parameters SortColumn The column by which items are sorted. SortColumn is an integer from 0 to 5 that represents a column on the screen, as follows: 0 same as OFF 1 TABLE 2 PAGE 3 ROW 4 TOTAL 5 MAXTOTAL (default) The items are sorted in descending order according to the values in the column. Sorts the items by CID.
SET SORTSAMPLEIO De nes how items are sorted on the SampleIO subsystem screens Scope SQLMON Only SQLMON Syntax 2 SET SORTS AMPLIO 3 SortColumn OFF Parameters SortColumn The column by which items are sorted. SortColumn is an integer from 0 to 3 that represents a column on the screen, as follows: 0 same as OFF 1 SWAPIN 2 SWAPOUT 3 TOTALIO (default) The items are sorted in descending order according to the values in the column. Sorts the items by object name.
SET TOP SET TOP Determines the number of items displayed on a screen Scope SQLMON Only SQLMON Syntax 2 SET T OP 3 NumItems OFF Parameters NumItems The number of items to display. An integer value. All items. The default value.
SET USERTIMEOUT Speci es how long SQLMON waits for a database resource that is unavailable Scope SQLMON Only SQLMON Syntax 2 SET U SERTIMEOUT 3 Seconds OFF Parameters Seconds The number of seconds SQLMON waits. Seconds is an integer value greater than or equal to 0. The value 0 is equivalent to OFF. The default value is 5. Speci es that SQLMON does not wait.
: : Escapes temporarily to the operating system and (optionally) executes a single operating system command. Scope SQLMON Only SQLMON Syntax >> : 2 3 CommandName ; Parameters CommandName Name of an MPE operating system command. Description 1. If you include a command name, control returns to SQLMON as soon as the command has been executed. 2. If you omit the command name, use the resume command to return to SQLMON.
A Design for a High-Performance Interactive Table Editor The following design shows how you can create interactive table editing for your applications while maintaining high concurrency using standard ALLBASE/SQL. The editor described supports very fast scrolling and very high concurrency by not holding any locks while terminal I/O takes place. High performance also derives from the use of BULK commands.
Internal Algorithms When the editor starts, it opens a le called the scroll le. It also allocates a 12K data bu er. SELECT When a SELECT is executed, the editor pulls in data from the table with BULK FETCH statements, 12K bytes at a time. If more than one BULK FETCH is needed, it appends the data from the previous FETCH to the scroll le. After the last FETCH in a sequence of FETCH statements, the data is appended to the scroll le, and the rst 12K of data is read back into the data bu er.
Tuple Has Changed The tuple's value in the bu er is updated, and the table is redisplayed starting from the window pointer. In all three cases, the transaction is ended before control returns to the user. Caution A dirty bit is kept for the whole data bu er. The bit is set when the contents of the bu er is modi ed (that is, becomes \dirty"). If set, the current contents have to be written to the scroll le before a new 12K block is read from it.
Index A aborting programs with TERMINATE USER, 5-2 administration DBA guidelines, 5-1 MPE guidelines, 5-15 system, 5-1 aging in data bu er calculations, 5-4 arithmetic expressions causing serial scans, 3-1 authorization e ect on performance, 2-10 B balancing load by separating les, 5-2 batch reports with SQLMON, 6-12 BEGIN WORK monitoring, 8-13 BETWEEN optimization of, 3-10 B-tree and indexes, 1-7 bu er calculating size of log bu er, 5-10 log data, 1-16 monitoring I/O, 8-2 types used by ALLBASE/SQL, 1-10
data storage data page compression, 1-6 DBA guidelines for system administration, 5-1 DBCORERR deadlock, 7-11 lock allocation failure, 7-14 DBEFile de ned, 2-13 monitoring capacity, 8-53 organization, 1-1 DBEFileSet de ned, 2-11 determining contents, 8-50 full, 7-23 DBEnvironment specifying in SQLMON, 6-2 DDL Enabled ag and directory caching, 1-15 improving concurrency, 4-1 setting to NO, 5-2 deadlock and savepoints, 4-2 caused by locking, 4-2 detection, 1-17 example in SQLMON, 7-11 monitoring, 8-13 default
hot spots de ned, 4-4 I IMAGE database storage of table, 8-50 index BETWEEN predicates, 3-10 B-tree compared to hashing, 2-8, 2-9 B-tree splits and logging, 1-16 clustering, 2-6 design of, 2-4 LIKE predicates, 3-9 maintaining, 2-6 MIN/MAX predicates, 3-7 monitoring clustering, 8-51 monitoring I/O, 8-42 monitoring tasks, 6-16 NULL values, 2-15 OR predicates, 3-8 page splitting, 1-9 placement on disks, 5-2 poorly clustered, 7-23 storage on DBEFile pages, 1-6 table size, 1-19 to improve I/O, 2-13 UNION querie
Load Program Screen, 8-14 Load Screen, 8-12 Load Session Screen, 8-16 Load subsystem, 7-7 Lock Impede Screen, 8-20 locking allocation failures, 7-14 and deadlocks, 7-11 avoiding contention, 2-10 cause of waits, 8-20 causing delays, 7-9 causing transaction delays, 7-7 contention, 7-2 ltering information, 9-15 for KEEP CURSOR, 4-6 in uenced by data de nition, 4-1 monitoring contention, 7-8 monitoring tasks, 6-19 on the system catalog, 1-14 overview, 1-17 requests, 8-13 row level, 4-4 selecting types of, 4-1 s
OR optimization of, 3-8 organization DBEFile, 1-1 page, 1-1 output of SQLMON, 9-22 overhead generated by SQLMON, 6-12 overnormalization avoiding, 2-1 Overview Program Screen, 8-36 Overview Screen, 8-34 Overview Session Screen, 8-38 Overview subsystem, 7-1 P page clean, 1-11 compression, 1-6 dirty, 1-11, 4-3 in DBEFiles, 1-1 no-log, 1-16 over ow , 1-5 page splitting of B-tree indexes, 1-9 page table page de ned, 1-2 in DBEFiles, 2-13 parallel serial scans advantages of, 3-12 PCR de ned, 1-9 performance basi
setting with SQLMON, 9-25 savepoint de ned, 4-2 scan bu er di erent from data bu er, 1-10 scan type avoiding serial scans, 3-1 overview of choices for optimization, 1-19 using parallel serial scans, 3-12 screens invoking in SQLMON, 6-3 section caching de ned, 5-13 sections de ned, 1-21 semi-permanent, 4-8 SELECT compared to FETCH with cursor, 4-1 design of, 3-1 session monitoring data I/O, 8-6 monitoring load, 8-16 monitoring locks, 8-28, 8-34 monitoring logging I/O, 8-10 monitoring status, 8-38 monitoring
Lock Impede, 8-20 Lock Memory, 8-23 Lock Object, 8-25 Lock Session, 8-28 Overview, 8-34 Overview Program, 8-36 Overview Session, 8-38 SampleIO, 8-40 SampleIO Indexes, 8-42 SampleIO Objects, 8-44 SampleIO TabIndex, 8-46 SampleIO Tables, 8-48 Static, 8-50 Static Cluster, 8-51 Static DBEFile, 8-53 Static Hash, 8-55 Static Indirect, 8-57 Static Size, 8-59 summary, 6-4 Static Cluster Screen, 8-51 Static DBEFile Screen, 8-53 Static Hash Screen, 8-55 Static Indirect Screen, 8-57 Static Screen, 8-50 Static Size Scr
tuple how stored on DBEFile page, 1-2 tuple body explained, 1-3 tuple bu er di erent from data bu er, 1-10 tuple header explained, 1-3 TurboIMAGE storage of table, 8-50 types of data compatibility of data types, 3-2 U UNION and indexes, 3-6 avoiding conversions with, 3-6 UNLOAD to avoid indirect rows, 2-16 to improve cluster count, 2-8 to remove over ow pages, 2-17 UPDATE STATISTICS Index-8 cluster count, 2-7 indexes, 2-10 invalidating sections, 1-21 large tables, 2-12 VALIDATE, 2-10 V VALIDATE and UPDA
