Systems and servers including database schema for accessing and managing utilization and operational data

Abstract

In some embodiments, the system is directed to an architecture that prevents transaction deadlocks by decoupling tables so that no transactions attempt to access the same table simultaneously. In some embodiments, the system is able to identify instances of jobs that ran on an entity from a single record in a job history table, without combining / truncating one or more records from the legacy schema. In some embodiments, the system improves performance by calculating the duration of a single job that ran on an entity from job history data, as opposed to storing multiple data points during a time interval.

Description

[0001] Cross-references

[0002] This application claims priority to U.S. Provisional Patent Application No. 62 / 875,214, filed on July 17, 2019, which is incorporated herein by reference in its entirety. Background Art

[0003] Managing data blocks used to process algorithms designed to monitor and run many analyses simultaneously, such as those used in industrial process systems such as manufacturing execution systems, is a significant challenge for many conventional computer systems and processes. To provide the most efficient performance, scalable and efficient data structures that conform to the database schema for industrial process management logic applications are needed, particularly those that access and manipulate utilization and job history data.

[0004] During the execution of an archive command, a transaction requires exclusive control of a computing resource. A deadlock occurs if a first transaction is waiting for a second transaction to release exclusive control of a second resource (e.g., a record in a database), but the second transaction cannot release control because it is waiting for the first transaction to release exclusive control of the first resource in order to complete its task. Conventionally, the only way to resolve the deadlock is to cancel one of the first and second transactions to release its resources. Summary of the Invention

[0005] In some embodiments, the system is configured to reduce and / or eliminate transaction deadlocks that may occur during industrial process monitoring and control by reducing and / or preventing coupling of databases (e.g., databases containing tables of data) to prevent multiple transaction events from adversely affecting the same table. In some embodiments, the system is configured to reduce coupling between utilization and job history data. In some embodiments, the system is configured to eliminate overhead associated with recalculating utilization event durations that change over time. In some embodiments, the system is configured to use query-driven calculations (e.g., for utilization event durations) instead of actual values stored in calculated columns to improve read / write performance and / or eliminate database contention (e.g., deadlocks and race conditions).

[0006] In some embodiments, the system includes a utilization history table and a job history table, wherein each table is decoupled from the other. In some embodiments, the utilization history table is configured to store utilization data. In some embodiments, the job history table is configured to store job history data. In some embodiments, the system includes a decoupled dynamic job history table configured to store dynamically updated values, the dynamically updated values including periodic data (e.g., hourly data).

[0007] In some embodiments, the system is configured to capture each instance of a job run in a job history table as job history data, along with one or more of a job context, a job start time, and / or a job end time. In some embodiments, the system is configured to calculate the duration of a single job run on an entity based on the job history data. In some embodiments, the system is configured to independently change shift information and utilization events by decoupling the entity's shift information and utilization events, wherein the decoupling removes transaction bottlenecks. In some embodiments, the system is configured to eliminate persistence of utilization event durations by decoupling shift information and utilization events using separate job history tables and utilization tables, respectively.

[0008] In some embodiments, the system is configured to dynamically link utilization events to the shift reported immediately before the event began. In some embodiments, the system is configured to use query-triggered computations to provide periodic job history data. In some embodiments, the system is configured to perform one or more delayed updates to the periodic job history table to allow related production and utilization events to stabilize over time. In some embodiments, the system is configured to reduce the number of updates to the periodic job history table by performing one or more delayed updates.

[0009] In some embodiments, the system is configured to reduce transaction deadlocks during manufacturing by decoupling two or more critical tables. In some embodiments, the system is configured to prevent multiple simultaneous events from adversely affecting the same table by providing decoupled critical tables and / or by decoupling two or more critical tables. In some embodiments, the system is configured to modify time data (i.e., shift times) without affecting utilization events and / or forcing the update of utilization events.

[0010] In some embodiments, the system is configured to reduce maintenance overhead by not artificially splitting utilization events at the start of a new time period (e.g., a new shift). In some embodiments, the system is configured to eliminate the need to persist data (e.g., utilization data, job history data, and / or periodic data) by calculating utilization event duration in response to queries. In some embodiments, the system is configured to calculate utilization event duration on the fly.

[0011] In some embodiments, the system is configured to reduce runtime processing overhead when performing one or more operations including splitting, merging, updating, and / or deleting utilization events (e.g., scheduled maintenance tasks, shift changes, etc.) by providing decoupled tables and / or by not coupling two or more tables. In some embodiments, the system is configured to prevent negative durations by not updating durations in one or more databases to eliminate race conditions. In some embodiments, the decoupling of job and utilization event tables and / or histories eliminates the possibility of creating duplicate job history records that identify the same corresponding utilization event. In some embodiments, the system is configured to identify an instance of a job run on an entity from a single record in a job history table without combining / truncating one or more records from an old schema.

[0012] In some embodiments, the system is configured to delay periodic (e.g., hourly) time periods ("buckets") in a dynamic job history table that includes production and utilization information for a predetermined time period. In some embodiments, the time period is 1-5 hours. In some embodiments, the time period is 3 hours. In some embodiments, the time period is a volatile time period. In some embodiments, the volatile time period includes the utilization status of a machine and / or entity and / or the production of jobs running on the machine and / or entity. In some embodiments, delaying the periodic time period eliminates one or more race conditions (i.e., multiple race condition categories) that are caused by unnecessarily performing updates at very small intervals (e.g., <= 1 second), which can create process bottlenecks. In some embodiments, the system includes a view (e.g., vw_tpm_status_data) that provides production and utilization information over a given time period (e.g., up to the second). In some embodiments, at least a portion of the production and utilization information is calculated. In some embodiments, the view includes an auxiliary benefit that provides legacy support. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] Figure 1 A portion of a system architecture according to some embodiments is shown.

[0014] Figure 2 Illustrated is another portion of a system architecture according to some embodiments. DETAILED DESCRIPTION

[0015] Figure 11 shows an entity 100 and a portion of a system architecture 10 according to some embodiments. In some embodiments, the system architecture 10 can be implemented by at least partially decoupling utilization data and job history data. In some embodiments, the process for decoupling utilization and job history data can include defining or preparing separate history tables for utilization and job history, and adding a distinction to at least one separate hourly or other periodic job history table that can be used for dynamic updating.

[0016] Some other embodiments of the present invention include eliminating overhead associated with recalculating utilization event durations (e.g., such as processor time, and / or interrupt time, and / or data cycle usage, and / or data storage device usage), which may change continuously or periodically over time.

[0017] Some embodiments include improving the handling of utilization data across shift boundaries. Some embodiments include replacing calculated columns with query-driven calculations (e.g., such as utilization event durations), which results in improved read / write performance, eliminating database contention such as deadlocks and / or race conditions.

[0018] Some embodiments of the present invention include program logic that, when executed by at least one processor of a system, captures each instance of a job run in a job history table with a job context and / or job start data and / or job end time data, wherein a duration of a single job run on an entity (e.g., entity 100) is calculated from the job history table.

[0019] Some embodiments include program logic that, when executed by at least one processor of the system, can decouple utilization events and shift information for entity 100 so that they can vary independently, thereby removing transaction bottlenecks.

[0020] Some embodiments of the present invention include program logic that, when executed by at least one processor of a system, eliminates at least one persistence utilizing an event duration.

[0021] Some embodiments of the present invention include dynamically linking a utilization event to a shift that begins immediately before or shortly before the event begins.

[0022] Some embodiments of the present invention include replacing system-computed columns with query-triggered system calculations on hourly or other periodic job history data.

[0023] Some embodiments of the present invention include program logic that, when executed by at least one processor of a system, provides delayed updates to a job time history table. In some embodiments, the system is configured to capture hourly data to allow for correlation of production and utilization events, and to allow the hourly data to stabilize over a period of time, thereby reducing the number of updates required by not updating the job time history table for a period of time.

[0024] In some embodiments, the program logic, when executed by at least one processor of the system, provides a system in which shift times are configured to be modified without affecting utilization events (ie, without forcing an update of the events).

[0025] In some embodiments, program logic is included that, when executed by at least one processor of the system, provides a system in which utilization events are not artificially split at the start of a new shift, thereby reducing maintenance overhead.

[0026] In some embodiments, program logic calculates utilization event durations on the fly when executed by at least one processor of the system, which eliminates the need for persisting data while also reducing runtime processing overhead when splitting and / or merging and / or updating and / or deleting utilization events (e.g., such as scheduled maintenance tasks, shift changes, etc.) In some embodiments, negative durations are no longer a problem because problematic race conditions no longer exist due to the lack of updated durations in the database.

[0027] In some embodiments, implementations of one or more of the systems and methods described for decoupling job and utilization event history eliminate the need to identify duplicate instances of job history records for identical corresponding utilization events. In some embodiments, instances of jobs run on an entity are readily identified from a single record in a job history table, as opposed to combining or truncating multiple or many records.

[0028] In some embodiments, implementations of one or more of the described systems and methods include delayed updates to hourly buckets containing production and utilization information represented in a job time history table. In some embodiments, the delay is approximately three hours because the last three hours from the current time are volatile for both the utilization state of the entity 100 and the production of the jobs running on the entity 100. In some embodiments, this can eliminate an entire class of race conditions that arise from unnecessarily performing updates at very small intervals (e.g., such as those performed at intervals of approximately one second or less in duration) and that create processing bottlenecks. Additionally, in some embodiments, a view ("vw_tpm_status_data") can provide essentially near real-time (up to approximately one second) production and utilization information, some of which is calculated by the system and has the ancillary benefit of providing legacy support.

[0029] In some embodiments, the system considers and / or processes program logic when using Coordinated Universal Time (UTC). In some embodiments, this eliminates issues where entities reside in multiple time zones, and / or transactions occur just before / after a daylight saving time change, and / or locations have half-hour and 15-minute offsets from UTC. In some embodiments, an example scenario may include using a web interface to log / modify events where the user's time zone is different from the web server's time zone, which may be different from the entity / device's time zone.

[0030] In some embodiments, an example scenario includes an event that is detected and sent to the database for processing just before a DST change, but the message is processed just after the DST change, which creates ambiguity in the event time. In some embodiments, difficulties are encountered when editing events that cross DST boundaries, where it is necessary to know which local time to use (before or after the time change), especially during the US fall time change when the same local time occurs twice.

[0031] In some embodiments, the entity 100 of the system architecture 10 may be operatively coupled to Figure 2 The computer system 210 shown in FIG and / or the computer system 210 includes the system architecture 10. In some embodiments, the computer system 210 includes and / or operates and / or processes computer executable code of one or more of the program logic, software modules and / or systems mentioned above. In addition, in some embodiments, the computer system 210 is coupled to Figure 1 A- Figure 1 B. The computer system 210 may operate and / or display information within one or more graphical user interfaces of the system architecture 10. In some embodiments, the computer system 210 may include a cloud server and / or may be coupled to one or more cloud-based server systems.

[0032] In some embodiments, the system 210 includes at least one computer including at least one processor 232. In some embodiments, the at least one processor 232 comprises a processor resident in or coupled to one or more server platforms. In some embodiments, the system 210 includes a network interface 235a and an application interface 235b coupled to the at least one processor 232 capable of processing at least one operating system 234. Additionally, in some embodiments, the interfaces 235a, 235b coupled to the at least one processor 232 are configured to process one or more of the software modules 238 (e.g., such as enterprise applications). In some embodiments, the software modules 238 comprise server-based software and are configured to operate to host at least one user account and / or at least one client account, and / or to transfer data between one or more of these accounts using the at least one processor 232.

[0033] In view of the above embodiments, it should be understood that the present invention can employ various computer-implemented operations involving data stored in computer systems. Furthermore, the above-described databases and models described throughout can store analytical models and other data on a computer-readable storage medium within the system 210 and on a computer-readable storage medium coupled to the system 210. Furthermore, the above-described applications of the system can be stored on a computer-readable storage medium within the system 210 and on a computer-readable storage medium coupled to the system 210. These operations are those requiring physical manipulation of physical quantities.

[0034] Typically, though not necessarily, these quantities take the form of electrical, electromagnetic, or magnetic signals, or optical or magneto-optical forms capable of being stored, transmitted, combined, compared, and otherwise manipulated. In some embodiments, system 210 includes at least one computer-readable medium 236 coupled to at least one data source 237a and / or at least one data storage device 237b and / or at least one input / output device 237c. In some embodiments, the system is implemented as computer-readable code on computer-readable medium 236. In some embodiments, computer-readable medium 236 is any data storage device that can store data that can subsequently be read by a computer system (such as system 210). In some embodiments, computer-readable medium 236 is any physical or material medium that can be used to tangibly store desired information, data, or instructions and that can be accessed by computer or processor 232. In some embodiments, computer-readable medium 236 includes a hard drive, network attached storage (NAS), read-only memory, random access memory, FLASH-based memory, CD-ROM, CD-R, CD-RW, DVD, magnetic tape, or other optical and non-optical data storage devices. In some embodiments, various other forms of computer-readable media 236 transmit or carry instructions to computer 240 and / or at least one user 231, including routers, private or public networks, or other transmission devices or channels, both wired and wireless. In some embodiments, software modules 238 are configured to send and receive data from a database (e.g., from computer-readable media 236, which may include a database, including data source 237a and data storage device 237b), and data may be received by software modules 238 from at least one other source. In some embodiments, at least one of software modules 238 is configured within the system to output data to at least one user 231 via at least one graphical user interface rendered on at least one digital display.

[0035] In some embodiments, the computer-readable medium 236 is distributed over a conventional computer network via a network interface 235a, where a system implemented by computer-readable code can be stored and executed in a distributed manner. For example, in some embodiments, one or more components of the system 210 are coupled to send and / or receive data via a local area network ("LAN") 239a and / or an internet-connected network 239b (e.g., such as wireless internet). In some other embodiments, the networks 239a, 239b include a wide area network ("WAN"), a direct connection (e.g., via a universal serial bus port), and / or other forms of computer-readable medium 236, or any combination thereof.

[0036] In some embodiments, the components of networks 239a, 239b include any number of user devices, such as personal computers, including, for example, desktop computers and / or laptop computers, or any fixed, generally non-mobile, internet-connected device coupled via LAN 239a. For example, some embodiments include a personal computer 240a coupled via LAN 239a that is configured for any type of user, including an administrator. Other embodiments include a personal computer coupled via network 239b. In some embodiments, one or more components of system 210 are coupled via an internet network (e.g., such as network 239b) to send or receive data. For example, some embodiments include at least one user 231 that is wirelessly coupled and accesses one or more software modules of the system, including at least one enterprise application 238, via input and output ("I / O") devices 237c. In some embodiments, system 210 enables at least one user 231 coupled via LAN 239a to access enterprise application 238 via I / O devices 237c. In some embodiments, user 231 includes user 231a coupled to system 210 using a desktop computer and / or laptop computer or any fixed, generally non-mobile, internet device coupled via internet 239b. In some embodiments, user 231 includes mobile user 231b coupled to system 210. In some embodiments, user 231b wirelessly couples to system 210 using any mobile computing device 231c, including but not limited to a personal digital assistant and / or a cellular telephone, mobile phone or smartphone and / or a pager, and / or a digital tablet and / or a fixed or mobile internet device.

[0037] It should be understood that the present invention is not limited in its application to the details of the construction and the arrangement of components set forth in the specification or illustrated in the accompanying drawings. The present invention can have other embodiments and can be practiced or implemented in various ways. Moreover, it should be understood that the words and terms used herein are for descriptive purposes and should not be considered restrictive. The use of "including", "comprising" or "having" and their variations herein is intended to cover the items listed thereafter and their equivalents as well as additional items. Unless otherwise specified or limited, the terms "mounted", "connected", "supported" and "coupled" and their variations are used broadly and include direct and indirect mounting, connection, support and coupling. In addition, "connected" and "coupled" are not limited to physical or mechanical connections or couplings.

[0038] As applicant's own lexicographer, applicant defines the use of "and / or" with respect to "A and / or B" to mean that one option can be "A and B" and the other option can be "A or B." This interpretation is consistent with a unilateral decision of the USPTO Patent Trial and Appeal Board, which determined that "and / or" refers to element A alone, element B alone, or elements A and B in combination.

[0039] The discussion is presented to enable those skilled in the art to make and use the embodiments of the present invention. Various modifications to the illustrated embodiments will be apparent to those skilled in the art, and the general principles herein can be applied to other embodiments and applications without departing from the embodiments of the present invention. Therefore, the embodiments of the present invention are not intended to be limited to the illustrated embodiments, but are to be accorded the widest scope consistent with the principles and features disclosed herein.

[0040] The detailed description will be read with reference to the accompanying drawings, in which similar elements in different figures have similar reference numerals. The accompanying drawings, which are not necessarily drawn to scale, depict selected embodiments and are not intended to limit the scope of embodiments of the invention. Those skilled in the art will recognize that the examples provided herein have many useful alternatives and fall within the scope of embodiments of the invention.

[0041] Some embodiments of the present invention include various methods, apparatuses (including computer systems) for performing such methods, and computer-readable media containing instructions that, when executed by a computing system, cause the computing system to perform such methods. For example, some non-limiting embodiments include certain software instructions and / or program logic stored on one or more non-transitory computer-readable storage devices that tangibly store program logic for execution by one or more processors of the system and / or coupled to one or more processors of the system.

[0042] Some embodiments relate to improved data processing in electronic devices, including, for example, entities or machines, such as manufacturing execution systems that provide technical solutions in which users can more efficiently process and view and / or retrieve useful data based on improvements in capturing and manipulating utilization, job history, and job time history data. For example, some embodiments generally describe unconventional methods (not well-known and further not taught or suggested by any known conventional methods or systems) for systems and methods for capturing and manipulating utilization, job history, and job time history data. Moreover, in some embodiments, specific functional features are significant technical improvements over conventional methods and systems, including at least the operation and functionality of computing systems that are technical improvements. These technical improvements include one or more aspects of the systems and methods described herein that describe the details of how the machine operates, which the Federal Circuit expressly stated is the essence of the statutory subject matter.

[0043] Some embodiments described herein include functional constraints that cooperate in an ordered combination to transform the operation of a data repository in a manner that improves pre-existing database update and data storage problems. In particular, some embodiments described herein include systems and methods for managing single or multiple content data items across different sources or applications, which present problems for users of such systems and services, where maintaining reliable control over distributed information is difficult or impossible.

[0044] The description herein further describes some embodiments that provide novel features that improve communications and the performance of software, systems, and servers by providing users with automated functionality to effectively and more efficiently manage resource and asset data in a manner that cannot be effectively accomplished manually. Therefore, one of ordinary skill in the art can readily recognize that these features provide the automated functionality described herein in a manner that is not well known and certainly not conventional. Thus, the embodiments of the invention described herein are not directed to abstract concepts and further provide significantly more tangible innovations. Moreover, the functionality described herein was unthinkable in previously existing computing systems and did not exist until some embodiments of the invention solved the technical problems described previously.

[0045] Some embodiments include systems and methods for arranging, structuring, and transmitting data or data sets in a computer system using one or more data streams created by separating the data into multiple parts. In some embodiments, the multiple parts can be stored on various components of the system and transmitted as partial or complete data or data sets over one or more data channels, each data or data set representing at least a portion of the entire data set or multiple data sets.

[0046] Those skilled in the art will recognize that although the invention has been described above with reference to specific embodiments and examples, the invention is not necessarily limited thereto, and many other embodiments, examples, uses, modifications and deviations from the embodiments, examples and uses are intended to be covered by the description and drawings herein.

Claims

1. A system for eliminating transaction deadlock, comprising: A computer, comprising one or more processors and one or more non-transitory computer-readable media including instructions stored thereon that, when executed by the one or more processors, implement: a utilization history table configured to store one or more utilization events, the one or more utilization events comprising one or more scheduled tasks associated with an entity and / or a machine; as well as a job history table configured to capture each instance of a job run and store job history data including a job start time and a job end time for jobs run on the entity and / or machine; wherein the instructions configure the computer to: The one or more processors decouple the job history data from the utilization history data by: defining or preparing separate tables, the separate tables including a utilization history table, a job history table, and a dynamic job history table, the dynamic job history table being configured to store dynamically updated values of production information and utilization information of the entity over a predetermined time period; as well as storing the job history data and the utilization history data in corresponding separate tables, wherein the separate tables eliminate transaction deadlocks and prevent multiple transaction events from adversely affecting the same table; decoupling shift information and utilization events using separate job history tables and utilization history tables by the one or more processors, wherein the shift information and utilization events vary independently and the shift information is modified without affecting the utilization events, the shift information including a time period; calculating, by the one or more processors in response to the query, a job run duration using job history data stored in a job history table; The utilization event duration is calculated by the one or more processors in response to the query using the job history data stored in the job history table.

2. The system according to claim 1, The system is configured to replace actual values stored in calculated columns with query-driven calculations that utilize event durations to improve read / write performance and / or eliminate database deadlocks and / or race conditions.

3. The system according to claim 1, The system includes a decoupled dynamic job history table configured to store dynamically updated values including periodic data.

4. The system according to claim 1, The system is configured to capture each instance of a job running in a job history table with one or more of a job context, a job start time, and a job end time as job history data.

5. The system according to claim 1, The system is configured to calculate the duration of a single job running on an entity and / or machine based on job history data.

6. The system according to claim 1, Decoupling shift information from utilization events removes transaction bottlenecks.

7. The system according to claim 1, The system is configured to eliminate persistence of utilization event durations by decoupling shift information from utilization events using separate job history tables and utilization tables.

8. The system according to claim 1, The system is configured to provide periodic job history data using query-triggered computations.

9. The system according to claim 1, The system is configured to perform one or more delayed updates to the job history table to allow related production and utilization events to stabilize over a period of time.

10. The system according to claim 1, The system is configured to reduce the number of updates to the job history table by performing one or more delayed updates.

11. The system of claim 1, Wherein the system is configured to modify the time data without affecting the utilization event and / or forcing the updating of the utilization event.

12. The system of claim 1, The system is configured to reduce maintenance overhead by not artificially splitting utilization events at the beginning of a new time period.

13. The system of claim 1, The system is configured to eliminate the need to persist data by computing utilization event durations in response to queries.

14. The system of claim 1, Wherein the system is configured to reduce overhead of related processing during runtime when performing one or more operations including splitting, merging, updating, and deleting utilization events by providing decoupled tables or by uncoupling two or more tables.

15. The system of claim 1, Wherein the system is configured to prevent negative durations by eliminating race conditions by not updating durations in one or more databases.

16. The system of claim 1, Wherein the system is configured to identify an instance of a job that ran on an entity based on a single record in a job history table without combining / truncating one or more records from a legacy schema.

17. The system of claim 1, The system includes providing a view of production and utilization information over a given time period.

18. The system of claim 1, Wherein the system is configured to delay a periodic time period in a dynamic job history table including production and utilization information by a predetermined time period.

19. The system of claim 18, The predetermined time period is 1-5 hours.

20. The system of claim 18, wherein the time period is a volatile time period, and The variable time period includes the utilization status of the machine and / or entity and / or the production of jobs running on the machine and / or entity.

Images