A sequence object manager provides a sequence object with a dynamic cache block size that indicates a block size of values to be added to the sequence object when the cache values are exhausted. The dynamic block size allows the sequence object manager to optimize performance and storage space depending on applications using the sequence object. The dynamically block size is set and maintained by the sequence object manager based on observed performance and historical trends of the applications. A seed value may be provided by the user to initially set the dynamic block size.
|
7. A computer-implemented method executed by at least one processor for providing a database sequence object with a dynamic cache block size comprising: creating a sequence object with a cache that has a dynamic cache block size;
setting a threshold; and
while executing a job with a query that uses the sequence object, determining, when the threshold is reached and before the cache is exhausted, a new dynamic cache block size and allocating, before the cache is exhausted, values to the cache of the sequence object according to the new dynamic cache block size, wherein the new dynamic cache block size for the sequence object is based on a seed value parameter indicated in a sequence object create statement and based further on a job type of the job, wherein the job type is an interactive job type or a batch job type.
1. An apparatus comprising:
at least one processor;
a memory coupled to the at least one processor; and
a database system residing in the memory and executed by the at least one processor that includes a sequence object manager that:
creates a sequence object with a cache that has a dynamic cache block size; sets a threshold;
determines, when the threshold is reached and before the cache is exhausted, a new dynamic cache block size; and
allocates, before the cache is exhausted, a block of cache values with the new dynamic block size to the cache of the sequence object, wherein the sequence object manager dynamically allocates the new dynamic cache block size for the sequence object based on a seed value parameter indicated in a sequence object create statement and based further on a job type, wherein the job type is an interactive job type or a batch job type.
14. A computer-implemented method executed by at least one
processor for providing a database sequence object with a dynamic cache size comprising:
creating a sequence object with a cache that has a dynamic cache block size based on a seed value parameter indicated in a sequence object create statement;
setting a threshold;
while executing a job with a query that uses the sequence object, determining, when the threshold is reached and before the cache is exhausted, a new dynamic cache block size based on sequence object historical information including a job type of the job, wherein the new dynamic cache block size for the sequence object is different than the seed value parameter indicated in the sequence object create statement and wherein the job type is an interactive job type or a batch job type;
allocating, before the cache is exhausted, values to the cache of the sequence object according to the new dynamic cache block size; and
updating the sequence object historical information after executing the query that uses the sequence object.
2. The apparatus of
3. The apparatus of
4. The apparatus of
5. The apparatus of
6. The apparatus of
8. The method of
9. The method of
10. The method of
11. The method of
a numerical seed value that indicates an initial cache block size for the sequence object.
13. The method of
object create statement using a structured query language in an application that accesses database
data through the database system.
15. The method of
17. The method of
|
This disclosure generally relates to computer database systems, and more specifically relates to a system and method for a sequence object manager that provides a database sequence object with a dynamic cache block size.
Many database applications use a structured query language (SQL). The SQL has SQL statements that are used to communicate with a database. SQL statements perform tasks such as update data on a database, or retrieve data from a database. SQL statements used by an application may include sequence objects. Sequence objects return a newly generated unique value with a NEXTVAL statement or a previously generated unique value with a CURRVAL statement in the structured query language. When a sequence object is created a cache number of values are created (default is 20 in some cases). The number of cache values to be carved out can be specified when the sequence object is created in the cache block size. The cache block size is a fixed number of values added to the cache each time the sequence objects are exhausted.
A sequence object manager provides a sequence object with a dynamic cache block size that indicates a block size of values to be added to the sequence object when the cache values are exhausted. The dynamic block size allows the sequence object manager to optimize performance and storage space depending on applications using the sequence object. The dynamic block size is set and maintained by the sequence object manager based on observed performance and historical trends of the applications. A seed value may be provided by the user to initially set the dynamic block size.
The foregoing and other features and advantages will be apparent from the following more particular description, as illustrated in the accompanying drawings.
The disclosure will be described in conjunction with the appended drawings, where like designations denote like elements, and:
The disclosure and claims herein relate to a system and method for a sequence object manager providing a sequence object with a dynamic cache block size that indicates a block size of values to be added to the sequence object when the cache values are exhausted. The dynamic block size allows the sequence object manager to optimize performance and storage space depending on applications using the sequence object. The dynamically block size is set and maintained by the sequence object manager based on observed performance and historical trends of the applications. A seed value may be provided by the user to initially set the dynamic block size.
Referring to
Main memory 120 preferably contains data 121 and an operating system 122. Operating system 122 is a multitasking operating system known in the industry as IBM i; however, those skilled in the art will appreciate that the spirit and scope of this disclosure is not limited to any one operating system. The memory 120 further includes a database system 123 with a sequence object manager 124. The memory 120 also includes database data 125 with a sequence object 126, an application 127 with a job 128 and historical information for sequence objects 129.
Computer system 100 utilizes well known virtual addressing mechanisms that allow the programs of computer system 100 to behave as if they only have access to a large, single storage entity instead of access to multiple, smaller storage entities such as main memory 120 and mass storage 155. Therefore, while data 121, operating system 122, database system 123, software object manager 124, database data 125, sequence object 126, application 127, job 128 and SO historical information 129 are shown to reside in main memory 120, those skilled in the art will recognize that these items are not necessarily all completely contained in main memory 120 at the same time. It should also be noted that the term “memory” is used herein generically to refer to the entire virtual memory of computer system 100, and may include the virtual memory of other computer systems coupled to computer system 100.
Processor 110 may be constructed from one or more microprocessors and/or integrated circuits. Processor 110 executes program instructions stored in main memory 120. Main memory 120 stores programs and data that processor 110 may access. When computer system 100 starts up, processor 110 initially executes the program instructions that make up operating system 122 and the database system 123 and later executes the program instructions that make up the application 127 and the job 128 as directed by a user.
Although computer system 100 is shown to contain only a single processor and a single system bus, those skilled in the art will appreciate that the system may be practiced using a computer system that has multiple processors and/or multiple buses. In addition, the interfaces that are used preferably each include separate, fully programmed microprocessors that are used to off-load compute-intensive processing from processor 110. However, those skilled in the art will appreciate that these functions may be performed using I/O adapters as well.
Display interface 140 is used to directly connect one or more displays 165 to computer system 100. These displays 165, which may be non-intelligent (i.e., dumb) terminals or fully programmable workstations, are used to provide system administrators and users the ability to communicate with computer system 100. Note, however, that while display interface 140 is provided to support communication with one or more displays 165, computer system 100 does not necessarily require a display 165, because all needed interaction with users and other processes may occur via network interface 150, e.g. web client based users.
Network interface 150 is used to connect computer system 100 to other computer systems or workstations 175 via network 170. Network interface 150 broadly represents any suitable way to interconnect electronic devices, regardless of whether the network 170 comprises present-day analog and/or digital techniques or via some networking mechanism of the future. In addition, many different network protocols can be used to implement a network. These protocols are specialized computer programs that allow computers to communicate across a network. TCP/IP (Transmission Control Protocol/Internet Protocol) is an example of a suitable network protocol.
The present invention may be a system, a method, and/or a computer program product at any possible technical detail level of integration. The computer program product may include a computer readable storage medium (or media) having computer readable program instructions thereon for causing a processor to carry out aspects of the present invention.
The computer readable storage medium can be a tangible device that can retain and store instructions for use by an instruction execution device. The computer readable storage medium may be, for example, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing. A non-exhaustive list of more specific examples of the computer readable storage medium includes the following: a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanically encoded device such as punch-cards or raised structures in a groove having instructions recorded thereon, and any suitable combination of the foregoing. A computer readable storage medium, as used herein, is not to be construed as being transitory signals per se, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through a waveguide or other transmission media (e.g., light pulses passing through a fiber-optic cable), or electrical signals transmitted through a wire.
Computer readable program instructions described herein can be downloaded to respective computing/processing devices from a computer readable storage medium or to an external computer or external storage device via a network, for example, the Internet, a local area network, a wide area network and/or a wireless network. The network may comprise copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers and/or edge servers. A network adapter card or network interface in each computing/processing device receives computer readable program instructions from the network and forwards the computer readable program instructions for storage in a computer readable storage medium within the respective computing/processing device.
Computer readable program instructions for carrying out operations of the present invention may be assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine dependent instructions, microcode, firmware instructions, state-setting data, configuration data for integrated circuitry, or either source code or object code written in any combination of one or more programming languages, including an object oriented programming language such as Smalltalk, C++, or the like, and procedural programming languages, such as the “C” programming language or similar programming languages. The computer readable program instructions may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider). In some embodiments, electronic circuitry including, for example, programmable logic circuitry, field-programmable gate arrays (FPGA), or programmable logic arrays (PLA) may execute the computer readable program instructions by utilizing state information of the computer readable program instructions to personalize the electronic circuitry, in order to perform aspects of the present invention.
Aspects of the present invention are described herein with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and/or block diagrams, and combinations of blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer readable program instructions.
These computer readable program instructions may be provided to a processor of a general purpose computer, special purpose computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks. These computer readable program instructions may also be stored in a computer readable storage medium that can direct a computer, a programmable data processing apparatus, and/or other devices to function in a particular manner, such that the computer readable storage medium having instructions stored therein comprises an article of manufacture including instructions which implement aspects of the function/act specified in the flowchart and/or block diagram block or blocks.
The computer readable program instructions may also be loaded onto a computer, other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable apparatus or other device to produce a computer implemented process, such that the instructions which execute on the computer, other programmable apparatus, or other device implement the functions/acts specified in the flowchart and/or block diagram block or blocks.
The flowchart and block diagrams in the Figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of instructions, which comprises one or more executable instructions for implementing the specified logical function(s). In some alternative implementations, the functions noted in the blocks may occur out of the order noted in the Figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems that perform the specified functions or acts or carry out combinations of special purpose hardware and computer instructions.
Again referring to
Again referring to
As introduced above, when a query uses a sequence number and the cache of sequence objects is exhausted, the sequence object manager 124 must allocate additional cache sequence numbers for the sequence object. However, instead of allocating a fixed number of new cache sequence numbers, the sequence object manager calculates a new dynamic block of sequence numbers and dynamically allocates the block of sequence numbers when the CACHE parameter 314 is set to “DYNAMIC” as described above. If this is the first time for allocating the sequence numbers or there is no historical information, the sequence object manager may use the seed value specified in the SEED VALUE parameter of the sequence object create statement described above. If there is historical information available, the sequence object manager 124 gets the appropriate historical information and analyzes it to determine whether to change the number of sequence object cache values to allocate.
The sequence object historical information 129 stored in
The query optimizer may use information available to it to determine the block size of sequence object cache values to allocate. For example, the query optimizer may set the block size for a sequence object based on the number of data records in the database that is accessed by a query to the database. For example, if the database has sales records with 1000 records having a Minnesota address and 10 records with a Wisconsin address, then the query optimizer may reserve 1000 for the sequence object size for a query limited to the state of Minnesota and reserve 10 for the sequence object size for a query limited to the state of Wisconsin.
Referring to
While method 600 shown in
The disclosure and claims herein relate to a system and method for a sequence object manager that provides a sequence object with a dynamic cache block size that indicates a dynamic block size for the sequence object. The dynamic block size allows the sequence object manager to optimize performance and storage space depending on applications using the sequence object. The dynamically block size is set and maintained by the sequence object manager based on observed performance and historical trends of the applications. A seed value may be provided by the user to initially set the dynamic block size.
The claims and disclosure herein support a computer apparatus comprising: at least one processor; a memory coupled to the at least one processor; and a database system residing in the memory and executed by the at least one processor that includes a sequence object manager that creates a sequence object with a dynamic cache block size, determines a new dynamic cache block size and allocates a block of cache values with the new dynamic block size to a cache of the sequence object
The claims and disclosure herein further support a computer-implemented method executed by at least one processor for providing a database sequence object with a dynamic cache block size comprising: creating a sequence object with a dynamic cache block size; while executing a job with a query that uses the sequence object, determining a new dynamic cache block size and allocating values to a cache of the sequence object according to the new dynamic cache block size.
The claims and disclosure herein further support a computer-implemented method executed by at least one processor for providing a database sequence object with a dynamic cache size comprising: creating a sequence object with a dynamic cache block size based on a seed value parameter indicated in a sequence object create statement; while executing a job with a query that uses the sequence object, determining a new dynamic cache block size based on sequence object historical information wherein the new dynamic cache block size for the sequence object is different than the seed value parameter indicated in the sequence object create statement; allocating values to the cache of the sequence object according to the new dynamic cache block size; and updating the sequence object historical information after executing the query that uses the sequence object.
One skilled in the art will appreciate that many variations are possible within the scope of the claims. Thus, while the disclosure is particularly shown and described above, it will be understood by those skilled in the art that these and other changes in form and details may be made therein without departing from the spirit and scope of the claims.
Konik, Rafal P., Mittelstadt, Roger A., Bestgen, Robert J., Baranczyk, Shawn J.
Patent | Priority | Assignee | Title |
Patent | Priority | Assignee | Title |
7089356, | Nov 21 2002 | Oracle International Corporation | Dynamic and scalable parallel processing of sequence operations |
9141609, | Dec 01 2011 | Oracle International Corporation | Reducing sequence cache latch contention in a database system |
9355128, | Apr 02 2014 | International Business Machines Corporation | Using a sequence object of a database |
20170185645, |
Executed on | Assignor | Assignee | Conveyance | Frame | Reel | Doc |
Sep 04 2018 | International Business Machines Corporation | (assignment on the face of the patent) | / | |||
Sep 04 2018 | KONIK, RAFAL P | International Business Machines Corporation | ASSIGNMENT OF ASSIGNORS INTEREST SEE DOCUMENT FOR DETAILS | 046779 | /0033 | |
Sep 04 2018 | BESTGEN, ROBERT J | International Business Machines Corporation | ASSIGNMENT OF ASSIGNORS INTEREST SEE DOCUMENT FOR DETAILS | 046779 | /0033 | |
Sep 04 2018 | BARANCZYK, SHAWN J | International Business Machines Corporation | ASSIGNMENT OF ASSIGNORS INTEREST SEE DOCUMENT FOR DETAILS | 046779 | /0033 | |
Sep 04 2018 | MITTELSTADT, ROGER A | International Business Machines Corporation | ASSIGNMENT OF ASSIGNORS INTEREST SEE DOCUMENT FOR DETAILS | 046779 | /0033 |
Date | Maintenance Fee Events |
Sep 04 2018 | BIG: Entity status set to Undiscounted (note the period is included in the code). |
Date | Maintenance Schedule |
Nov 02 2024 | 4 years fee payment window open |
May 02 2025 | 6 months grace period start (w surcharge) |
Nov 02 2025 | patent expiry (for year 4) |
Nov 02 2027 | 2 years to revive unintentionally abandoned end. (for year 4) |
Nov 02 2028 | 8 years fee payment window open |
May 02 2029 | 6 months grace period start (w surcharge) |
Nov 02 2029 | patent expiry (for year 8) |
Nov 02 2031 | 2 years to revive unintentionally abandoned end. (for year 8) |
Nov 02 2032 | 12 years fee payment window open |
May 02 2033 | 6 months grace period start (w surcharge) |
Nov 02 2033 | patent expiry (for year 12) |
Nov 02 2035 | 2 years to revive unintentionally abandoned end. (for year 12) |