Database execution cost and system performance estimator

Abstract

The software tool of this invention estimates the costs of an application program accessing a database. These costs may be execution costs of the application or of a transaction, SQL statement, and/or a utility. Execution costs include CPU time, I/O time and minimum elapsed time. For estimating the execution costs, the tool receives simplified and partial definitions of tables, utilities, SQL statements, transactions, and applications. The estimator tool requires only a minimal amount of information to calculate the various execution costs.

Claims

We claim:

1. A tool, for use in a computer system, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database; said tool comprising:

a user interface for receiving input from a user corresponding to a partial definition of at least one table in said database;

means for building, from user input, a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table; and

means, based on said simplified query statement, for estimating a cost of executing said at least one query statement against said database.

2. The tool of claim 1 wherein the simplified query statement comprises only those portions of the query statement that are essential in determining the execution costs of the application accessing the database with said query statement and wherein the tool provides for a possibility of user input for an estimate of a number of rows resulting in an execution of the query statement, an estimate of a number of pages touched and an estimate of a number of pages read.

3. The tool of claim 1 wherein the cost of executing includes CPU time, I/O time, and minimum elapsed time.

4. The computer program on the computer usable medium of claim 1 wherein the simplified query statement comprises only those portions of the query statement that are essential in determining the execution costs of the application accessing the database with said query statement and wherein the computer program provides for a possibility of user input for an estimate of a number of rows resulting in an execution of the query statement, an estimate of a number of pages touched and an estimate of a number of pages read.

5. The computer program on the computer readable medium of claim 1 wherein the cost of executing includes CPU time, I/O time, and minimum elapsed time.

6. A tool, for use in a computer system, for estimating execution costs of a transaction having at least one query statement for accessing a database; said tool comprising:

a user interface for generating, from user input, a simplified transaction definition including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

means for including in the transaction definition information necessary to define transaction overhead costs; and

means for determining an estimation of a cost of said at least one transaction based on said costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

7. A tool, for use in a computer system, for estimating system performance of at least one application having at least one transaction for accessing a database; said tool comprising:

a user interface for generating, from user input, a simplified application definition including at least one predefined simplified transaction definition having at least one simplified query statement for each of said at least one transaction and a frequency of execution of said simplified transaction in said computer system;

means for including in the application definition information necessary to define a system load from external sources; and

means for computing the system performance of said at least one application based on said costs of said at least one simplified transaction definition having at least one simplified query statement definition, said frequency of execution of said transaction, and said system load from external sources.

8. The tool of claim 7, wherein the system performance includes CPU utilization, I/O device utilization, and elapsed time including service and queuing time for CPU and I/O devices.

9. A tool, for use in a computer system, for estimating execution costs of an application having at least one query statement for accessing a database; said tool comprising:

a user interface for receiving input from a user corresponding to a simplified definition of at least one table in said database and a simplified definition of at least one utility command;

means for building, from user input, at least one simplified query statement, said simplified query statement having a place-holder instead of expression details within said at least one query statement, using at least one of said at least one tables;

means, through said user interface, for generating a simplified transaction definition including at least one predefined simplified query statement definition and a frequency of execution of said simplified query statement in a transaction;

means for including in the transaction definition information necessary to define transaction overhead costs;

means for determining an estimation of a cost of said at least one utility based on said simplified definitions of said at least one table and said utility command;

means, based on said at least one simplified query statement, for estimating a cost of executing said at least one query statement against said database;

means for determining an estimation of a cost of said at least one transaction based on costs of said simplified query statements within said transaction, said frequency of execution, and said overhead costs; and

means for determining an estimation of the execution costs of an application based upon said means for determining the estimation of the cost of the at least one utility and said means for estimating the cost of executing said at least one query statement and means for determining the estimation of the cost of said at least one transaction.

10. A computer system for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database; said system comprising:

a user front end for receiving input from a user corresponding to a partial definition of at least one table in said database;

means for building, from user input, a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table; and

means, based on said simplified query statement, for estimating a cost of executing said at least one query statement against said database.

11. A computer system for estimating execution costs of a transaction having at least one query statement for accessing a database; said system comprising:

a user interface for generating, from user input, a simplified transaction definition including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

means for including in the transaction definition information necessary to define transaction overhead costs; and

means for determining an estimation of a cost of said at least one transaction based on costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

12. A computer system, for estimating system performance of at least one application having at least one transaction for accessing a database; said system comprising:

a user interface for generating, from user input, a simplified application definition including at least one predefined simplified transaction definition having at least one simplified query statement for each of said at least one transaction and a frequency of execution of said transaction in said computer system;

means for including in the application definition information necessary to define a system load from external sources; and

means for determining an estimation of system performance of said at least one application based on costs of said at least one simplified transaction definition having at least one simplified query statement, said frequency of execution of said transaction, and said system load from external sources.

13. A method, for use in a computer system, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database; said method comprising:

receiving input from a user corresponding to a partial definition of at least one table in said database;

building, from user input, a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table;

estimating, based on said simplified query statement, a cost of executing said at least one query statement against said database.

14. A method, for use in a computer system, for estimating execution costs of a transaction having at least one query statement for accessing a database; said method comprising:

generating, from user input, a simplified transaction definition, through a user interface, including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

including in the transaction definition, information necessary to define transaction overhead costs; and

determining an estimation of a cost of said at least one transaction based on execution costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

15. A method, for use in a computer system, for estimating system performance of at least one application having at least one query statement for accessing a database; said method comprising:

generating a simplified application definition, from user input through a user interface, including at least one predefined simplified transaction definition having at least one simplified query statement and a frequency of execution of said simplified transaction in said computer system;

including, in the application definition, information necessary to define a system load from external sources; and

determining the system performance of said at least one application based on execution costs of said at least one simplified transaction definition having at least one simplified query statement, said frequency of execution of said transaction, and said system load from external sources.

16. A method, for use in a computer system, for estimating execution costs of an application having at least one query statement for accessing a database; said method comprising:

receiving, through a user interface, input from a user corresponding to a simplified definition of at least one table in said database and a simplified definition of at least one utility command;

building, from user input, at least one simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table;

generating, through said user interface, a simplified transaction definition including at least one predefined simplified query statement definition and a frequency of execution of said simplified query statement in a transaction;

including, in the transaction definition, information necessary to define transaction overhead costs;

determining an estimation of a cost of said at least one utility based on said simplified definitions of said at least one table and said utility command;

estimating, based on said at least one simplified query statement, a cost of executing said at least one query statement against said database;

determining an estimation of a cost of said at least one transaction based on said costs of said simplified query statements within said transaction, said frequency of execution, and said overhead costs; and

determining an estimation of the execution costs of an application based upon said step for determining the estimation of the cost of the at least one utility and said step for estimating the cost of executing said at least one query statement and said step for determining the estimation of the cost of said at least one transaction.

17. A computer program, on a computer usable medium, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database, comprising:

first program code for providing a user interface for receiving input from a user corresponding to a partial definition of at least one table in said database;

second program code for causing a building, from user input, of a simplified query statement, said simplified query statement having a place-holder for each expression detail within said at least one query statement, using at least one of said at least one table; and

means, based on said simplified query statement, third program code for causing an estimation of a cost of executing said at least one query statement against said database.

18. A computer program, on a computer usable medium, for estimating execution costs of a transaction having at least one query statement for accessing a database, comprising:

first program code for providing a user interface for generating a simplified transaction definition including at least one predefined simplified query statement definition for said at least one query statement, said simplified query statement definition having a place-holder for each expression detail within said at least one query statement, and a frequency of execution of said query statement in said transaction;

second program code for including in the transaction definition information necessary to define transaction overhead costs; and

third program code for determining an estimation of a cost of said at least one transaction based on said costs of said at least one simplified query statement within said transaction, said frequency of execution, and said overhead costs.

19. A computer program on a computer usable medium, for estimating system performance of at least one application having at least one transaction for accessing a database, comprising:

first program code for providing a user interface for generating, from user input, a simplified application definition including at least one predefined simplified transaction definition having at least one simplified query statement for each of said at least one transaction and a frequency of execution of said simplified transaction in said computer system;

second program code for including in the application definition information necessary to define a system load from external sources; and

third program code for computing the system performance of said at least one application based on said costs of said at least one simplified transaction definition having at least one simplified query statement definition, said frequency of execution of said transaction, and said system load from external sources.

20. The computer program on a computer usable medium of claim 19, wherein the system performance includes CPU utilization, I/O device utilization, and elapsed time including service and queuing time for CPU and I/O devices.

21. A computer program on a computer usable medium, for estimating execution costs of an application having at least one query statement for accessing a database, comprising:

first program code for providing a user interface for receiving input from a user corresponding to a simplified definition of at least one table in said database and a simplified definition of at least one utility command;

second program code for building, from user input, at least one simplified query statement, said simplified query statement having a place-holder instead of expression details within said at least one query statement, using at least one of said at least one table;

third program code, through said user interface, for generating a simplified transaction definition including at least one predefined simplified query statement definition and a frequency of execution of said simplified query statement in a transaction;

fourth program code for including in the transaction definition information necessary to define transaction overhead costs;

fifth program code for determining an estimation of a cost of said at least one utility based on said simplified definitions of said at least one table and said utility command;

sixth program code, based on said at least one simplified query statement, for estimating a cost of executing said at least one query statement against said database;

seventh program code for determining an estimation of a cost of said at least one transaction based on costs of said simplified query statements within said transaction, said frequency of execution, and said overhead costs; and

eighth program code for determining an estimation of the execution costs of an application based upon said means for determining the estimation of the cost of the at least one utility and said means for estimating the cost of executing said at least one query statement and means for determining the estimation of the cost of said at least one transaction.

22. A computer program, on a computer usable medium, for estimating execution costs of at least a portion of an application having at least one query statement for accessing a database, comprising:

means for providing a user interface for receiving input from a user for defining columns of a table by data type and quantity and for defining indexes by specifying a defined column as an index;

means for generating a partial table definition from the defined columns and defined indexes and from a user specifiable DASD type;

means for providing a user interface for receiving input from a user specifying a cpu and DASD type;

means for providing a user interface for receiving input from a user specifying a partial definition of a SQL statement, the specification including an identifier for the partial definition of the SQL statement, and a type of the SQL statement;

means for generating a simplified SQL statement from user input received through an interface providing selectable options, based upon the type of the SQL statement specified, the interface including i) a list of selectable statement clause types, selectable defined columns, selectable operators, and selectable values where a selectable value is selected as being a host variable, a constant, or an expression, if the specified type is SELECT, ii) a list of types of insert including columns or sub-select clauses, if the specified type is INSERT, iii) a list of selectable defined columns and selectable values where a selectable value is selected as being null, a host variable, a constant, or an expression, if the specified type is UPDATE or DELETE; and

means, based on said generated simplified query statement, for causing an estimation of a cost of executing said at least one query statement against said database.

23. The program of claim 22 further comprising means for adding, deleting and modifying entries in the partial table definition.

24. The program of claim 22 wherein the generated table definition comprises scrollable column names with a corresponding data type for each column name and a list of index names and a list of index columns.

25. The program of claim 22 further comprising means for displaying table and index space requirements.

26. The program of claim 22 wherein a cpu and DASD type is specified from a list of default cpu and DASD types.

27. The program of claim 22 wherein a cpu or DASD type is specified by configuring a new cpu or DASD type.

28. The program of claim 22 wherein the simplified query statement comprises only those portions of the query statement that are essential in determining the execution costs of the application accessing the database with said query statement.

Description

A portion of the Disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

BACKGROUND OF THE INVENTION

1. Field of the Invention

This invention relates to software application development tools and system management tools, and more particularly relates to a system, method and tool for estimating the execution costs of an application designed to access a database, and for estimating the system performance when executing the application.

2. Description of the Related Art

In a relational database, information is kept in logical tables comprised of rows and columns. Underlying this logical structure is the physical structure used to hold the data and the indexes used to access the data.

A utility is used to maintain the data in the database. The REORG utility is used for reorganizing the physical data on the disk. It reorganizes a table space to improve access performance and reorganizes indexes so that they are more efficiently clustered. The BACKUP utility is used for making a copy of the data so that the data can be recovered if it is lost such as after a disaster. The COPY utility creates an image copy of a table space or a data set within the table space. There are two types of image copies. A FULL IMAGE COPY is a copy of all pages in a table space or data set. An INCREMENTAL IMAGE COPY is a copy only of pages that have been modified since the last use of the COPY utility. The LOAD utility loads data into one or more tables in a table space or partition. It may also be used to replace the contents of a single partition or an entire table space. The RUNSTATS utility scans a table space or indexes to gather information about utilization of space and efficiency of indexes.

A Structured Query Language (SQL) statement allows a user (an application or an end user) to access the logical data without regard to physical structure. In data access languages that predated SQL, the data access language included directions to the database manager on how to obtain the data. With SQL statements, the data access language indicates only what data is required, and leaves it to the optimizer function of the database manager to figure out how to find the data.

A transaction is made up of one or more SQL statements and any given SQL statement, within the transaction, may be executed any number of times. In an actual transaction, the SQL statements may be interleaved with application logic, and there may be a specific sequence of execution of the SQL statements. Overhead costs, such as thread create, terminate, authorization checking, and commits, are associated with transactions.

An application is made up of transactions interleaved with application logic. All of the transactions contend for the CPU and the I/O resources.

In specifying a full definition of a table in a relational database, such as in the IBM DB2 relational database, the user and/or application must specify the database name; the table space or storage group defined; the table description in terms of column names, their data types, and other associated information such as data length; as specified in IBM Database 2 Version 2 SQL Reference Release 2, 1989, Chapter 6. "Statements," pages 95-246, as incorporated herein by reference.

In specifying a full definition of a SQL statement, a user and/or application must specify the columns selected; the tables accessed; the join columns and predicates; all other predicates with specific values and how they are combined, such as through boolean operators; the columns in GROUP BY and ORDER BY clauses; as well as any columns updated, as shown in Chapter 5, "Queries" of the above referenced document, as incorporated herein by reference.

In specifying a full definition of a transaction, the fully defined SQL statements are fully defined in a programming language such as Cobol, C++, etc., as shown in Appendix C of IBM Database2 Version 2 Release 1, Application Programming Guide, 1988. The full definition of the transaction is embodied in program source code that includes the fully defined SQL statements as well as the other language statements that make up the program logic and structure.

When a database manager processes an SQL statement on behalf of an application, the database manager will need to use CPU resources and possibly perform I/O operations to return the results of the SQL query back to the requestor. For any given SQL statement, this may involve scanning all the rows of the database tables or using an index to directly access the data. The amount of CPU and I/O necessary will vary depending on various factors. Some of these factors are under the control of the requestor (an application or end user) while others are internal to the database manager and out of the control of the requester.

For applications that are designed to access a database, design decisions affecting the logical structure of the table impact how the SQL statement is coded as well as the performance of the SQL statement. Decisions affecting the physical structure of the table do not affect the SQL statement, but still may affect the performance of the application accessing the data.

Developing an application for accessing a database can be a very expensive process. Before incurring the development cost, it is important to know in the early design phase whether the performance of the finally designed application will meet performance requirements. During the development process, it is important to be able to estimate the performance of alternative designs to determine which design is optimal. Also, after an application is in use, it is important to estimate how modifications to the system may affect its performance.

Methods are known (IBM Database2 Version 2 Administration Guide Volume 1, Release 2, September 1989, pages 2-24 to 2-28, ) for estimating the amount of disk space required to hold a table and indexes based upon the number of rows in the table and the type of data.

Methods are known for estimating the CPU and I/O time for SQL statements. ("DB2 Cost Formula," Shibamiya, A., Yeung, M. Y., IBM Technical Disclosure Bulletin, Volume 34, Number 12, May 1992, pages 389-394).

Tools are known to estimate the SQL statement cost. However, these estimates are based on user inputs regarding database internals that the user is unlikely to have any real knowledge of, such as the number of columns used in an internal sort.

Tools are known to estimate the database cost of an existing application which was designed using a specific design methodology. Unless an application has been designed using this methodology, no estimation is possible.

Tools are known to estimate the cost based upon the costs of other, possibly similar, applications that have been measured. This type of tool may not take into account that the database manager may execute two similar SQL statements in different ways. Tools are known that have a graphical user interface (GUI) that allows a user to build complete executable SQL statements and to execute those SQL statements against the database. However, these tools require more input than is necessary for estimating the cost of executing the SQL statement, and are, therefore, more complex.

SUMMARY OF THE INVENTION

It is an object of this invention to provide a tool that can be used to estimate performance of an application, which accesses a relational database, during all phases of the application's life--early design, development, and use and maintenance.

It is a further object of this invention to provide an estimation tool that is independent of the methodology used to design, develop, and maintain an application program.

It is a further object of this invention to query the application developer only for that information that is currently known about the application.

It is a further object of this invention to query the application developer only for that information that is necessary in estimating the execution costs and system performance.

The software tool of this invention estimates the costs of an application program accessing a relational database. These costs may be execution costs of the application or of a transaction, SQL statement, and/or a utility. Execution costs include CPU time, I/O time, and minimum elapsed time. Minimum elapsed time is an estimation of the amount of time that the application, transaction, SQL statement or utility will take to execute. It is referred to as a minimum elapsed time because the tool does not account for contentions for resources such as buffers and locks. Also, when costs of SQL statements or transactions are estimated in isolation, contention for CPU and I/O resources are not considered.

The above execution costs may also reflect system performance affected by transactions accessing the database, including batch processing. System performance includes an application's CPU utilization, I/O device utilization, and elapsed time including service and queuing time for CPU and I/O devices.

The software tool has a graphical user interface which allows easy input by the user, easy formulation of query (SQL) statements, easy formulation of database definitions and utility commands, and easy formulation of transaction definitions and application definitions.

For estimating execution costs, the user specifies only a partial definition of those tables, in a database, which the application program will be accessing. From these partial definitions, the user can make selections in the graphical user interface which automatically builds a simplified query statement. The simplified query statement does not contain the expression details of an executable query statement. It contains only that information necessary to calculate the execution cost of the query statement. The estimated cost of executing the query statement against the database is then calculated.

Additionally, the software tool calculates the execution costs of a utility command based upon a simplified definition of the database (which comprises one or more simplified table definitions) and simplified definitions of utility commands. Again, the simplified definitions of the database and the utility command comprise only that information which is necessary for computing the cost of the utility.

For estimating the execution costs of a transaction accessing a database, the tool generates a simplified transaction definition including a predefined simplified query statement definition and a frequency of execution of the query statement in the transaction. The transaction definition also includes that information which is necessary to define CPU and I/O costs for non-transaction related program logic, and transaction overhead costs. The execution costs of a transaction is computed based on the costs of the query statement within the transaction, the frequency of execution and the overhead costs.

For estimating the system performance of an application accessing a database, the tool generates a simplified application definition which includes a predefined transaction definition and a frequency of execution of the transaction in the computer system. The application definition includes a predefined simplified transaction definition. The application definition also includes that information necessary to define the system load from external sources, i.e, other than from the predefined transaction. An estimation of the system performance is then calculated based upon the application definition.

DESCRIPTION OF THE DRAWINGS

FIG. 1 shows the components of the estimator tool of this invention.

FIG. 2A shows a graphical user interface of the tool of this invention for receiving a simplified and partial definition of a table by specifying information pertaining to the columns in the table.

FIG. 2B shows a graphical user interface of the tool of this invention for receiving a simplified and partial definition of a table by specifying information pertaining to indexes.

FIG. 2C shows a simplified and partial definition of a table generated by the tool based on the input received through FIG. 2A and FIG. 2B.

FIG. 2D shows the table and overall index space requirements as generated by the tool.

FIG. 2E shows the index space requirements, for a specific index, generated by the tool.

FIG. 3A shows a graphical user interface of the tool of this invention for receiving as input information pertaining to the CPU and DASD.

FIG. 3B shows a graphical user interface of the tool of this invention for adding a CPU not on the CPU list of FIG. 3A.

FIG. 3C shows a graphical user interface of the tool of this invention for adding a DASD not on the DASD list of FIG. 3A.

FIG. 4 shows a graphical user interface of the tool of this invention for receiving a simplified definition of a utility.

FIG. 5A shows a graphical user interface for receiving a partial and simplified definition of any SQL statement type.

FIG. 5B shows a graphical user interface for receiving a partial and simplified definition of an SQL statement when the statement type is SELECT.

FIG. 5C shows a graphical user interface for receiving a partial and simplified definition of an SQL statement when the statement type is INSERT.

FIG. 5D shows a graphical user interface for receiving a partial and simplified definition of an SQL statement when the statement type is UPDATE.

FIG. 5E shows a graphical user interface for receiving a partial and simplified definition of an SQL statement when the statement type is DELETE.

FIG. 5F shows a SQL SELECT statement, generated by the tool, that involves a relational database JOIN operation of two tables.

FIG. 5G illustrates two tables to be JOINed.

FIG. 6A shows a graphical user interface for specifying the access method for a given table referenced within a SQL statement.

FIG. 6B shows a graphical user interface for specifying information related to the access method chosen for a given table within the SQL statement.

FIG. 6C shows the graphical user interface for specifying the order in which the tables are accessed in an SQL statement.

FIG. 7 shows the tool output for the execution costs for an SQL statement.

FIG. 8 shows a graphical user interface for receiving a partial and simplified definition of a transaction and overhead costs.

FIG. 9 shows a graphical user interface for specifying input for evaluating the performance of the system when at least one application is running; including a simplified application definition including a predefined transaction definition, a frequency of execution of the transaction, and the system load from external sources.

FIG. 10 shows the system performance including CPU utilization with options to display device utilizations and elapsed time.

FIG. 11 shows the method of estimating elapsed time for SQL statements and utilities.

DESCRIPTION OF THE PREFERRED EMBODIMENT

The tool of this invention estimates the performance of database applications such as applications accessing IBM's DB2 database product. This tool estimates the performance of SQL statements, transactions, database utilities and applications. This estimator tool can be used stand-alone in a Windows or OS/2 environment, or integrated with the IBM DataHub/2 product as part of a database system management solution.

In a preferred embodiment, the tool of this invention runs on a IBM personal computer with a 80386SX or upward compatible processor supported by OS/2 Version 2 Release 1, with four megabytes of memory, a hard-disk with 5 MB of unused space, a VGA or VGA compatible display, and an IBM PS/2 or Microsoft compatible mouse. Optional software includes Lotus 1-2-3 for charting the results of the tool's analysis, and IBM DataHub/2 Platform feature for optional invocation of the tool from an OS/2 workstation control point.

The estimator tool provides users with the ability to evaluate the impact of database and application design tradeoffs early in the development cycle. It can easily model database applications and evaluate performance tradeoffs of different design alternatives. The tool provides the user with the ability to specify data at varying levels of detail in order to allow refinement of the data model as the application design progresses; the ability to create objects by merely pointing and clicking; and the ability to estimate the costs of the database portion of the system for capacity planning purposes; the ability to define new types or to modify predefined DASD and CPU types thereby enabling the user to evaluate the impact of hardware changes to the database application.

The estimator tool can assist an application developer in designing and developing efficient SQL statements and tables. The user can identify the SQL statements and transactions and model the tables for a new or changed application, allowing for "what-if" analysis and early performance projections. The estimator tool makes intelligent assumptions about tables, access paths, etc., which the user may accept or override based upon application-specific knowledge. The estimator tool can assist a database administrator in providing a well-designed database environment. The tool's "what-if" analysis capability combined with its performance estimates enables the administrator to make decisions that result in better system utilization. The estimator tool can assist the capacity planner in estimating the resource requirements associated with new applications, changing workloads, and changing resources, such as hardware, table size, and design of indexes. The estimator tool can assist a business analyst in defining the functional specifications of a new or changed database application design. By identifying performance and capacity costs of proposed changes early in the design of key applications, use of the estimator tool can result in reduced application development time and increased developer productivity.

In a multi-user environment, several transactions and SQL statements execute concurrently. These transactions and SQL statements compete with each other for hardware resources such as CPU time, buffer pools, and I/O devices. The estimator tool analyzes the data model defined by the user and combines that with its knowledge of the internal workings of the database to derive its performance estimation. By enabling the user to more accurately estimate the performance of an application, the user can make choices that allow for better system resource utilization.

The estimator tool enables better utilization of systems resources by providing estimates of database applications' CPU and I/O capacity requirements as well as those for individual SQL statements, transactions, and utilities; average elapsed time of SQL, transactions, applications, and utilities; and DASD space requirements at the table level.

The results of the estimator tool's analysis can be viewed online as graphs, tables, text reports, or as files which may be exported to other word processing or spreadsheet applications.

FIG. 1 shows the overall process and components of determining the execution costs of utilities, SQL statements, transactions, and applications. The tool receives input, through a graphical user interface, corresponding to a simplified and partial definition of a table, step 1. In step 2, the tool estimates the amount of storage required to hold the table and the indexes. In step 3, the tool receives input corresponding to a utility type, and it receives input corresponding to a table the utility is to act upon. In step 4, the tool determines the utility execution cost based upon the information obtained from step 1 and step 3. The tool receives input corresponding to a simplified partial definition of a SQL statement, step 5. The tool receives as input, for a given SQL statement, the method by which a relational database manager (e.g., IBM DB2) will access the tables, the sequence in which the tables are accessed when multiple tables are processed, and the number of rows and pages that are processed, step 6. The tool determines the SQL execution cost, step 7, based on the partial definitions of the table(s) and the partial definition of the SQL statement, and knowledge of the internal workings of the database management system. The tool receives a simplified definition of a transaction, step 8, in determining an estimated transaction cost, step 9. The tool has means for receiving as input a simplified application definition, step 10, and estimating the system performance such as CPU utilization, I/O device utilization and elapsed times, step 11. In performing the above estimates, the tool takes the user inputs, and then calls the formulas needed to perform the estimate.

At any step in this process, a user may choose (based upon the estimated results or changes in the design) to return to an earlier step in the process. At the earlier step, the user may modify earlier inputs (such as changing the number of rows in the table), add more detailed inputs (such as specifying specific column names of the table), or modify the design (such as adding an index). The results will reflect, immediately, the changes made in the earlier steps, but the user should continue from the step where the modifications have been made through subsequent steps to see if additional changes may be necessary. For each object the user creates, the user specifies a name and description for that object to be used by the tool in order to reference the object throughout the process.

As shown in FIGS. 2A, 2B and 2C, in creating a partial definition of a table, the graphical user interface of the tool allows the user to input some values (such as column name, data type, length and number of such columns, FIG. 2A), and a more complete table definition is created by the tool, FIG. 2C, by filling in some reasonable defaults which can be changed by the user, if necessary. For example, In FIG. 2A, the tool defaults a column name 205 if not otherwise specified. In FIG. 2B, the tool provides a default for free page 212 as zero, percent free 211 as ten, and subpages 213 as four, and average duplicate keys 216 as one. The tool does not require those details not necessary for estimating the DASD space or execution cost of the SQL statement, transactions, or applications.

In FIG. 2A, for a given table, the user specifies, for each column, an optional column name 205, the data type 202 (integer, small integer, decimal, character, variable character, float, date, time, timestamp), whether or not nulls 206 are allowed, and the number of similar columns 201 in the table (with a default to 1). For the data types decimal, character, variable character and float, the user should specify the length 203 of each of these columns. If all columns of the same data type have the same length, this length is entered. If they have different lengths, the columns can be individually entered or an average length 204 of the columns can be entered. For the variable character field, the maximum length 203 should be specified and the average length 204 of the column when the table is filled with data. To define more columns, the user selects the "another" 207 button.

In FIG. 2B, for a given table, the tool receives as input the index definitions of the table. More specifically, the user selects the columns that the index is based upon 215, specifies the percent free space to be left on each page 211, specifies the number of pages before a completely empty page is left 212, specifies the number of subpages within a 4K page 213, and specifies the cardinality of the index 214.

In FIG. 3A, the user can select a CPU from a list 301 of CPU's to be used as the default CPU for all estimations, or add CPU's to the list by selecting the new CPU button 314 in FIG. 3A, and entering a name for the new CPU 315, FIG. 3B, selecting a reference CPU name from the list 301, and by putting a multiplier 302 representative of the speed of the added CPU relative to the speed of the CPU being referenced. A DASD can also be selected from a list of DASD 303, FIG. 3A, to be used as the default DASD type 227, FIG. 2C, in the table definition, or a new dasd can be added by giving a name 304, FIG. 3C, and average seek time 305, average latency 306, a transfer rate in megabytes per second 307, a description of the unit such as tracks or sectors 308, the number of pages per unit 309, and the number of tracks per cylinder 310 if tracks were selected 308.

From this partial definition specified by the user through the graphical user interface as show in FIGS. 2A and 2B, the tool generates an expanded partial definition of a table, as shown in FIG. 2C. As shown in FIG. 2C, the tool creates the individual columns with the user input using either default column names 225 or column names 226 that have been specified by the user. In addition, in the table definition in FIG. 2C, the number of rows in the table are specified 220, the number of pages that are filled before an empty page is left is specified 221, and the percent of free space left on a page is specified 222.

As shown above, only a partial definition of a table has been entered by the user, and the inputs have been simple. In fact, not requiring the user to input all aspects of a table definition is a major simplification from prior art methods. In the above process steps, a user may enter inputs beyond those required (such as specifying column names and the order the columns will be in the table), but this is not required.

It should be noted that this estimation tool can be used in conjunction with tables that have been fully defined to a database manager. These predefined table definitions can be downloaded for use in conjunction with the tool.

After the above inputs have been made, step 1, FIG. 1, the tool can estimate the amount of disk space that will be required to store the table, step 2, FIG. 1, by using the formulas specified in IBM Database2 Version 2 Administration Guide Volume 1, Release 2, September 1989, pages 2-24 to 2-28, which is hereby incorporated by reference, and enclosed as described below as DASD Storage for User Data.

DASD Storage for User Data

The amount of DASD space you need for your database is not merely the number of bytes of data; the true number is some multiple of that. That is, space required =M*(bytes of data).

The multiplier M depends on your particular circumstances. It includes factors that are common to all data sets on DASD, as well as others that are peculiar to DB2. It can vary significantly, from a low of about 1.25 to perhaps 4.0 or more. For a first approximation, perhaps set M=2 and skip to "Calculating the Space Required for a Table" below. For more accuracy, calculate M as the product of the following factors:

    ______________________________________
    Factor      Allows for
    ______________________________________
    Record overhead
                8 bytes of record header and control
                data, plus space wasted for records that
                do not fit exactly into a DB2 page.
                The factor can range from about 1.01
                (for a careful space-saving design) to
                as great as 4.0. A typical value is
                about 1.10.
    Free space  Space intentionally left empty to allow
                for inserts and updates. You can
                specify this factor on the CREATE
                TABLESPACE statement. The factor can
                range from 1.0 (for no free space) to
                200 (99% of each page used left free,
                and a free page following each used
                page). With default values, the factor
                is about 1.05.
    Unusable space
                Track lengths in excess of the nearest
                multiple of page lengths. DB2 uses 4K
                pages, which are blocked to fit as many
                as possible on a track. The table below
                shows the track size, number of pages
                per track, and the value of the
                unusable-space factor for several
                different device types.
    ______________________________________
    Device Type
              3380     3375    3350   3340  3330
    ______________________________________
    Track Size
              47476    35616   19069  8368  13030
    Pages per track
                10       8       4      2     3
    Factor value
              1.16     1.09    1.16   1.02  1.06
    ______________________________________
    Data set excess
                Unused space within allocated data sets,
                occurring as unused tracks or part of a
                track at the end of any data set. The
                amount of unused space depends upon the
                volatility of the data, the amount of
                space management done, and the size of
                the data set. Generally, large data
                sets can be managed more closely, and
                those that do not change in size are
                easier to manage. The factor can range
                without limit above 1.02. A typical
                value is 1.20.
    Indexes     Storage for indexes to data. For data
                with no indexes, the factor is 1.0; for
                a single index on a short column, the
                factor is 1.01; if every column is
                indexed, the factor can be greater than
                2.0. A typical value is 1.20. For
                further discussion of the factor, see
                "Calculating the Space Required for an
                Index" below.
    ______________________________________

    ______________________________________
                 "Tight"     "Medium" "Loose"
    Factor       Design      Design   Design
    ______________________________________
    Record overhead *
                 1.02        1.10     1.30
    Free space * 1.00        1.05     1.30
    Unusable space *
                 1.16        1.16     1.16
    Data set excess *
                 1.02        1.25     1.50
    Indexes =    1.02        1.20     1.40
    Multiplier M 1.23        2.01     4.12
    ______________________________________

    ______________________________________
    Table for The Total Space Requirement for an Index
    Quantity       Calculation      Result
    ______________________________________
    Length of key  (k)              10
    Proportion of available space
                   (s):(100-10)/100 0.9
    Number of subpages per page
                   m                1
    Average number of
                   n                1
    duplicate keys
    Number of entries per page
                   FLOOR (4050 * 0.9/(10+4))
                                    260
    Number of leaf pages
                   CEILING (100000/260)
                                    385
    Number of nonleaf pages on
                   CEILING (385/260)
                                    2
    second level
    Number of nonleaf pages on
                   CEILING (2/260)  1
    third level
    Total index pages on
                   385 + 2 + 1      388
    all levels
    TOTAL SPACE REQUIRED
                   4 * (388 + 2)    1560
    in kilobytes
    ______________________________________

    ______________________________________
    Notations and assumptions
    ______________________________________
    BSAM  =     basic sequential access method for input/output
    K     =     kilobyte (1024)
    I/O   =     input and output to external computer media
    IOT   =     average I/O time; 35 ms for 3330, 25 ms for 3350, 20 ms
                for 3380, 16 ms for 3390
    NC    =     number of columns in row in Load
          =     number of physical leaf pages of clustered index in Reorg
    NIX   =     number of indexes for a given table if Load; tablespace
                otherwise
    NL    =     average number of physical leaf pages
    NP    =     number of data pages for a given table if Load; tablespace
                otherwise
    NPC   =     number of pages between commits
    NPU   =     number of pages containing updated rows (PPU .times. NP)
    NPW   =     number of pages written in 1 I/O (32 pages)
    NR    =     number of rows in a given table if Load; tablespace
                otherwise
    NS    =     number of sorted rows = Nr .times. NIX
    NT    =     number of pages per track; 3 for 3330, 4 for 3350, 10 for
                3380, 12 for 3390
    MB    =     megabyte (1,048,576)
    PPU   =     % pages containing updated rows
    SPFU  =     number of pages prefetched (64 pages)
    R     =     device rotation time = .0167 seconds (3380) os .0141
                (3390)
    SIO   =     number of pages read or written in sort intermediate
                workfile I/O
          =     2SP .times. SM
    SM    =     number of merge passes
          =     1 if SP > 307 (= (1.8 .times. 2MB/3)/4K page)
                assuming 4MB storage to work with
          =     0 otherwise
    SP    =     number of pages of sorted rows = NIX .times. NL .times. 1.3
                Multiplier 1.3 is used, since index key is padded to the
                length of largest key among all indexes.
    ______________________________________
     --Model intended for utilities which take minutes more.
     --I/O tuning done to avoid frequentlyaccessed datasets on the same volume
     --Singletable tablespace
     --VS Sort used in Load/Reorg
     --Load input data size approximately equal to output data size
     --No input field conversion in Load
     --32K blocksize for BSAM input in Load

    ______________________________________
           NP    SPFUXR
    IO time =
    -----) Buffer Manager read in
           SPFU NT         full copy
           NPUx(R = R/NT)  Buffer Manager read in
           NP   R   16R    incr. copy
    -)- =  BSAM write 4 16K pages in
           16   2   NT     1 I/O
           NPU  R   2xNPWxR
    +
    ------)
           BufMgr write to reset change bit
           NPW  2   NT
    +      (R/2 + R/NT)xNP/NPC
                           BufMgr write for spacemap
                           page per commit
    +      2IOTxNP/NPC     2 log I/O's per commit
    ______________________________________

    ______________________________________
    Pathlength = (NP/SPFU)x(7000+450xSPFU) + 1380xNP
                              Buffer
                              Manager
                              read
    + 5000xNP/8 + 1000xNP     BSAM write
    + (NPU/NPW)x(7000+450xNPW) + 1380xNPU
                              BufMgr write
                              to reset
                              change bit
    + 20000xNP/NPC            Commit every
                              NPC pages
    ______________________________________

    ______________________________________
            NP    SPFUxR
    IO time =
    -----)  Buffer Manager read of data
            SPFU    NT
            NIXxNL   SPFUXR
    -----)  Buffer Manager read of index
            SPFU    NT
    ______________________________________

    ______________________________________
    Pathlength =
            (NP/SPFU)x(7000+450xSPFU)+1000xNP
                                  BufMgr read
                                  of data
    +       NIXxNLx(5000 + 1000)  Buffer
                                  Manager read
                                  of index
    +       430xNR                Get each row
                                  of the
                                  tablespace
    +       620xNRxNIX            Get each row
                                  of each index
    ______________________________________

    ______________________________________
    IO time =
           NP    SPFUxR
    -----) Buffer Manager read of data
           SPFU    NT
           NP   8R
    +
    -)(R + BSAM write
           8   NT
    +      2IOTxNP/NPC    2 log I/O's per commit
           NP   R   8R
    +
    --)-- +
           Sort formula - BSAM Read
           8   2   NT
    +      2NPxSM  R
    x (-- + R)
           Sort - intermediate workfile I/O
             NT   2
           NP   R   8R
    +
    -)- +  write to output file
           8   2   NT
    ______________________________________

    ______________________________________
    Pathlength=
    +       ((NP/SPFU)x(7000+450xSPFU) +
                               BM read of data
            700xNP)
    +       950xNR             Pathlengths per row
    +       6000xNP            Write the unloaded
                               data BSAM blocks
    +       100000xNP/NPC      Commit every NPC of
                               data unloaded
    +       250xNR             sort input and output
                               with E15 and E35 exit
    +       100xNRxSM          sort merge
            2XNPxSM
    ------) 5000x(2xNP +
            BSAM I/O
            NT
    +       1000x(2xNP + 2XNPxSM)
                               per page overhead
    ______________________________________

    ______________________________________
           NP  R  8R
    IO time =
    -)- +  BSAM read of 32K block input
           8   2  NT
           NP 2R
    -)R +  BSAM read, if Load from unloaded
           2 NT         dataset
           NIXxNL   8R
    -)R +  BSAM write of Key for each index
            8 NT
    +      1.1x2xRxNP/NT
                        Preformat write
           NP  R  NPWxR
    +
    -------)
           Buffer Manager write
           NPW  2   NT
    +      2IOTxNP/NPC  2 log I/O's per commit; takes place
                        even if LOG NO because of SYSUTIL
                        update.
    ______________________________________

    ______________________________________
    Pathlength=
    +       12000xNP/8         Read from BSAM
                               input - LOAD
            6000xNP/2          Read from BSAM
                               input- REORG
    +       985xNR             Overhead of LOAD of
                               each row
            700XNR             Overhead for REORG
                               of each row
    +       Nrx192xNC          Load each col of each
                               row if LOAD
    +       Nrx800xNC          for each variable-
                               length column LOAD
    +       402xNRxNIX         Extract key/rid pairs
                               for each index
    +       4000xNP            Validity check on the
                               page
    +       NIXxNLx(2000 + 6000/2)
                               BSAM write the index
                               data with
                               1 I/O every other page
    +       6500xNP/NT         Preformat tablespace
    +       (2xNP/NPW)x(10000+500xNPW)
                               Buffer Manager write
                               data pages
    +       100000xNP/NPC      Commit data being
                               loaded
    ______________________________________

    ______________________________________
    SP =       NIXxNLx1.3      if NIX > 1
               NL              if NIX = 1
    ______________________________________

    ______________________________________
             NIXxNL  R   8R
    IO time =
    -)- +    BSAM read
              8   2   NT
             S10  R
    x(- + R) intermediate workfile I/O
             NT   2
             NIXxNL  R   8R
    +
    -)- +    write to output file
             8   2   NT
    ______________________________________

    ______________________________________
    Pathlength =
            250xNS          sort input and output with E15
                            and E35 exits
    +       100xNSxSM       sort merge
            SIO
    --)     5000x(2xNIXxNL +
            BSAM I/O
            NT
    +       1000x(2xNIXxNL + SIO)
                            per page overhead
    ______________________________________

    ______________________________________
    SP = NIXxNL
             SP  R  8R
    IO time +
    -)- +    Read from sort output file
             8  2  NT
    +        NIXx100xIOT    Non-DB2 I/O's
             SP  R  NPWxR
    ----)+   Buffer Manager write
             NPW  2   NT
    +        2xIOTxSP/NPC   2 log I/O per commit
    +        1.1x2xRxNP/NT  Preformat write
    ______________________________________

    ______________________________________
    Pathlength =
            (1200+1500)xSP Read the index data
    +       60xNIXxNR      Read index
    +       1000xSP        Process new page
    +       370xNRxNIX     Load each index record
    +       NIXx2000000    Open/close of index pagesets
    +       10000xSPx2/NPW Buffer Manager write
    +       100000xSP/NPC  Commit
    +       6500xNP/NT     Preformat write
    ______________________________________

    ______________________________________
    SELECT EMPLOYEE.NAME, DEPARTMENT.MANAGER
    FROM EMPLOYEE, DEPARTMENT
    WHERE EMPLOYEE.NAME=CONSTANT
    AND EMPLOYEE.DEPT#=DEPARTMENT.DEPT#
    ______________________________________

    ______________________________________
    Notations
    NDMS  =     number of rows scanned by Data Manager, the lower-level
                (first) component to process records
    NDMC  =     number of rows participating in column function evaluation
    NRDS  =     number of rows scanned by RDS, the higher-level
                component to process records from Data Manager
    NQ    =     number of rows qualifying
    NSAN  =     number of times table column appears in predicates
    NC    =     number of columns retrieved, updated, or inserted
    IO    =     number of I/O's, adjusted for I/O chaining if applicable
    NP    =     number of leaf and data pages scanned
    NG    =     number of rows returned to the user
    NIX   =     number of indexes to be upgraded in Insert, Delete, or
                Update
    NS    =     number of rows sorted
    NCS   =     nunber of columns and keys in sorted row
    RL    =     sort row length
    KL    =     sort key length
    NRG   =     average number of rows per group in Groupby sort
    NHV   =     number of host variables in search argument, update set
                list, or field-list insert
    T     =     total number of pages in table
    R     =     total number of rows in table
    CR    =     cluster ratio
    F     =     filter factor (% of rows qualifying for given search
                arguments)
    L     =     number of physical leaf pages
    NLEVELS =     number of index levels
    DML cost formulas
    1. Pathlength formula
    a. Internal OPEN/SCAN/CLOSE
    1) Base = DB2 sort + 2000*NP + 50*NHV + 500O*IO
    2) Tablespace scan =
    1500 + NDMS*(75 + 60*(1 + (NSAN - 1)/2)) + NDMC*160 +
    NRDS*(450 + 35*NC)
    3) Data scan via index =
    2000 + NDMS*(110 + 60*(1 + (NSAN - 1)/2)) +
    NDMC*130 + NRDS*(900 + 35*NC)
    4) Data scan via RID-list
    Notations
    NDMS, NRDS, NQ to represent processes after index
    AND/ORing
    Note that NDMS =< FRIDS
    NDP = number of data pages accessed
    NIXA = number of indexes used in access
    NRIDS = number of RIDs extracted and sorted
    TRIDS = total number of RIDs ANDed or ORed
    FRIDS = final number of RIDs resulting from index
    AND/ORing
    Ksp' = sequential prefetch factor for list
    prefetch
       = 0.2 + 0.3*(1 - NDP/T)**2
    Index-only access to retrieve RIDs via set
    interface
    NIXA * (5000 + 2000*NPindex + 5000*IO + 90*NRIDS)
    Sort/merge of RIDs
    NRIDS * (120 + 70 if NRIDS > 1000)
    AND/ORing of RIDs
    TRIDS*63
    Data page access via RIDlist
    2500*NDP + 5000*IO + 150*NDMS + (450 + 35*NC)*NRDS
    b. Select = Internal Open/Scan/Close + 5000 + 44*NC
    c. Fetch = Internal Open/Scan/Close + 5000 + NG*(2100 +
     44*NC)
    d. Update with cursor = 8000 + 130*NC + 50*NHV + 12000*NIX +
     5000*IO
    e. Update without cursor = Internal Open/Scan/Close + 5000 +
     50*NHV + NQ*(3600 + 130*NC + 12000*NIX)
    f. Insert of single row via clustering index = 7500 + 83*NC +
     50*NHV + 6500*NIX + 5000*IO
    g. Insert of multiple rows = Internal Open/Scan/Close
     Select + 4000 + NQ*(2900 + 83*NC + 6500*NIX)
    h. Delete with cursor = 5000 + 6500*NIX + 5000*IO
    i. Delete without cursor = Internal Open/Scan/Close (with
     NC = 0) + 5000 + NQ(2600 + 6500*NIX)
    j. Multi-table query
    1) Noncorrelated IN/ALL/ANY subquery
    = (subquery scan) + (outer query scan) + sort
    + NRDSouter*(1800 + 135*NQ/3) fetch from temporary
    workfile
    2) Noncorrelated EXISTS subquery
    = (subquery scan) + (outer query scan)
    + 12000 temporary workfile create/delete
    + NQinner*600 insert into temporary workfile
    + NRDSouter*3000 fetch from temporary workfile
    3) Other noncorrelated subquery, including single value
    ANY/ALL with range operation
    = outer table scan + inner table scan
    4) Nest loop join or correlated subquery
    = (outer table scan) + (NQouter * inner table scan)
    5) Merge join
    = outer table scan + sort if needed + inner table scan
    + sort + NQouter*(MJR + 1)*(700 + 35*NC)
    where MJR = average number of rows in inner table
    workfile scanned for each qualifying row
    in outer table
    2. I/O cost formula
    One or more of the following I/O cost formula is used as
    appropriate for each SQL call above:
    Tablespace scan: Ksp*T
    Unique index with matching equal predicate:
    (NLEVELS-2) for index + 1 for data
    Index scan:
    (NLEVELS-3) + Ksp*F*L
                        index I/O
    + CR*Ksp*F*T + (1 - CR)*F*R
                        data I/O
    Reading data pages via list prefetch:
    Ksp*T*F*CR + Ksp' *NDP,
    where NDP = T(1 - (1 - 1/T)**(R*F*(1 - CR)))
    DB2 sort
    1. Calculated parameters for sort
    NS = number of rows sorted
    SW = number of rows in sort workfile page
     .sup. = MIN(FLOOR(4000/(2 + rowsize + keysize)), 127)
    Rowsize and keysize calculated by padding variable-length
    columns to maximize lengths. Add 1 to nullable column
    length.
    SP = number of pages required to hold all sorted rows
    .sup.  = CEIL(NS/SW)
    ST = number or rows in replacement selection tree
    .sup.  = MIN(16000,SW*SPRMSPOL/4000)
    where SPRMSPOL (sort pool size) = 10% of total database
    bufferpool size with MIN of 240KB and MAX of 10MB
    If NS > (2*ST),
    SR     =     number of initial runs
           =     1 + CEIL(NS/(2*ST))
    SM     =     number of merge passes
           =     average number of times each row is processed in merge
           =     1.0 if SR < 200
                 1.5 if SR < 500
                 2.0 if SR < 900
                 2.5 if SR < 1900
                 3.0 if SR < 3800
                 3.5 if SR < 7600
                 4.0 if SR < 15200
                 4.5 else
    2. Pathlength =
    65000 + 1780*NS + 60*NCS*NS
                       Base
    + NG*(900 + 35*NCS)
                       API Fetch from sort output
                       file for orderby, groupby,
    union, distinct
    + 30000 + SM*(7000 + 1600*NS)  if NS > 2ST
    + 6000 + 790*NS   if Distinct, UNION, or noncorrelated
    IN/ALL/ANY subquery
    + 5000 * number of sort workfile I/O's  CPU cost for
    sort workfile I/O
    3. Sort workfile I/Os (assuming no buffer hit, which is a good
    approximation for large sort) =
    0.6*SP     for first insert in API Open and last fetch in
               API
    + .6*SP*SM if NS > (2*ST); get old page and insert
               assume no requested buffers found in pool
    + .3*SP    if Distinct, UNION, or noncorrelated IN/ALL/ANY
               subquery
    DB2 Create/terminate thread and commit
    1. Create/terminate thread
    Pathlength = 37000 base
    + 500 * number of indexes to be accessed
    (from static SQL call)
    + 1000 * number of tablespaces to be accessed
    (from static SQL call)
    assuming no EDM pool I/Os
    2. Commit
    Pathlength = 8000
                    if read only
     17000 + physical log I/O
                    if TSO update
     20000 + physical log I/O
                    if IMS or CICS update
    IO = 2 (TSO) or 4 (IMS or CICS) for log if update.
    DB2 Dynamic Bind
    Pathlength =
            70000 + 5000*IO
            + 12000* (number of terms connected by AND or OR +
             number of items in IN-LIST + number of UNIONS)
            + (number of tables * (10000 +
            + (5000 + (17000 if 1st column of index key))*
             average number of columns in table))
            + (number of views * (50000 +
            + (5000 + (17000 if 1st column of index key))*
             average number of columns in view))
    IO = 3 * number of tables and views
    ______________________________________

• Inventors
  Eberhard, Rainer; Hall, Harold; Lakshmi, Seetha;
• Assignee
  International Buisness Machines Corporation (Armonk, NY)
• Published
  Dec-14-1999
• Current US Classes:
  707/2
  707/200
• Application #
  916560
• International Classes
  G06F 017/30
• Field of Search
  364/578 707/2 707/200
• Examiner
  Black; Thomas G.
• Agent
  Gates & Cooper
• US Patent References:
  4829427
  4956774
  5043872
  5091852
  5301317
  5412806
  5724569