Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-12-22
2004-05-04
Metjahic, Safet (Department: 2171)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000
Reexamination Certificate
active
06732084
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates to systems and methods for performing queries on data stored in a database, and in particular to a method and system for executing SQL trigger actions in parallel.
2. Description of the Related Art
A trigger in a database provides that upon the occurrence of an event (such as an update of a record or tuple in a database), various actions are executed (referred to as trigger actions). Trigger actions can contain further modifications to a database. However, in a distributed and/or parallel database system, records and tuples may be spread out across various data servers and data storage units and the event or trigger may be fired from anywhere in the system. In such a situation, the prior art does not provide the ability to execute the trigger actions across the necessary data servers from anywhere in the system (e.g., where the triggering event (or tuple modification that caused the trigger to fire) is located). These problems may be better understood by describing databases and triggers.
Databases
The ability to manage massive amounts of information has become a virtual necessity in business today. The information and data are often stored in related files. A set of related files is referred to as a database. A database management system (DBMS) creates and manages one or more databases. Today, DBMSs can manage any form of data including text, images, sound and video. Further, large-scale integrated DBMSs provide an efficient, consistent, and secure means for storing and retrieving the vast amounts of data.
Certain computer languages have been developed and utilized to interact with and manipulate the data. For example, SQL (Structured Query Language) is a language used to interrogate and process data in a relational database (a database in which relationships are established between files and information stored in the database). Originally developed for mainframes, most database systems designed for client/sever environments support SQL. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Thus, methods and functions may embed and utilize SQL commands.
To expedite the processing of information, databases may be parallelized such that data is distributed across multiple locations or data servers. When a query is executed, the query may be parallelized and run on each of the data servers. Such query parallelization provides for running the same query on multiple small portions of data in parallel rather than running the one query over one large portion of data sequentially.
Triggers
A trigger provides that whenever any tuple or record in a DBMS is updated, some condition is checked. If the condition is true, then a “trigger action” is executed. A trigger action can be any arbitrary SQL operation, stored procedure (see description below), user defined function (see description below), or any action desired. For example, in a Teradata® Object Relational (TOR) database (discussed in detail below), the trigger may use any of TOR's object relational features. Further, each trigger action in turn can cause its own triggers to fire. On a parallel or distributed DBMS the event (e.g., the update of a tuple) occurs on a particular data server (i.e., the data server where the tuple is located). Where the conditions are checked and where the trigger action is fired or invoked from may be anywhere in the system. However, the trigger action may need to execute a query on the entire database that is distributed across other data servers. The prior art does not provide a method for executing trigger actions across multiple data servers when the trigger is fired anywhere in a system. For example, when a trigger is fired from a particular data server, the prior art does not provide any method for executing the trigger action across all of the data servers.
Stored Procedures
Triggers may invoke the same set of commands/actions (or the same set of commands/actions with different parameters) at varying times and locations of a program. In such a situation, the set of commands/trigger actions may be placed into a stored procedure. A stored procedure is a batch of SQL statements stored in a database/on a server, that may be partially or fully processed/compiled before it is stored (or upon its first invocation). Additionally, a stored procedure is a method or procedure written in any programming language that is partially or fully processed/compiled before it is stored (or upon its first invocation).
Stored procedures may be called directly from a client or from a database trigger and are often stored on the server. A database trigger, as described above, is a user defined mechanism that causes a stored procedure to automatically initiate and execute upon the occurrence of the user specified events in the database (i.e., when the trigger “fires”). Thus, the trigger may not fire unless the event(s) specified by the user occurs. For example, a user may define a trigger to automatically fire whenever a user updates, deletes, or inserts data of a specific tuple.
Since stored procedures are typically stored on a data server, they are available to all clients and do not need to be replicated in each client. Further, by storing the stored procedure on the server, when the stored procedure is modified, all clients automatically get/have access to the new version. This saves programming effort especially when different client user interfaces and development systems are used. Further, this allows stored procedures to be an easy mechanism for sharing complex queries and functions between multiple applications. Additionally, SQL and stored procedures may call other stored procedures or cause other triggers to fire and may be written independently from (and without knowledge of) the underlying DBMS.
A stored procedure may be partially or completely processed/compiled before it is stored on the database. Consequently, the stored procedure does not have to be parsed and compiled each time it is invoked. Further, because a stored procedure is stored in a compiled format, it executes faster than if its constituent commands were executed individually.
Alternatively, a stored procedure may not be compiled prior to storage but may be automatically compiled the first time the procedure is invoked. As part of such a compilation, a query execution plan may be generated. The query execution plan describes the order in which tables are to be accessed and the indexes to be used. Further, the query execution plan is optimized for the stored procedure parameters and data in the database tables at the time the stored procedure is first executed.
A stored procedure may be invoked by its name. The caller can pass parameters to and receive results from the stored procedure. A user can create and name a stored procedure to execute specific database queries and perform other database tasks. For example, a user may create a stored procedure that returns the number of videos of a particular movie remaining in a video store for the video title that is specified at the time the stored procedure is called.
Stored procedures may also maintain the integrity of the database and prevent unauthorized users from modifying certain entries. For example, a user may be given the right to call a stored procedure that updates a table or set of tables but denied the right to update the tables directly.
Stored procedures may be created using a variety of mechanisms. The following format may be utilized to declare a stored procedure:
create proc {procedure name}
as
{statement of block of statements}
For example the following stored procedure called myproc will return the number of Casablanca videos left in a video store as well as other movie titles, the rental price of those movie titles, and the location of those videos when Humphrey Bogart is an actor in the movie:
create proc myproc
as
begin
select inv_videos
from video
where title_id=“Casablanca”
s
Chen Jian-Jun
Kabra Navin
Nag Biswadeep
Patel Jignesh M.
Yu Jie-Bing
Chen Te Y.
Gates & Cooper LLP
Metjahic Safet
NCR Corporation
LandOfFree
Method and apparatus for parallel execution of trigger actions does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Method and apparatus for parallel execution of trigger actions, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and apparatus for parallel execution of trigger actions will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3257286