EP1002287A1 - Procede d'extension d'une base de donnees - Google Patents

Procede d'extension d'une base de donnees

Info

Publication number
EP1002287A1
EP1002287A1 EP98946302A EP98946302A EP1002287A1 EP 1002287 A1 EP1002287 A1 EP 1002287A1 EP 98946302 A EP98946302 A EP 98946302A EP 98946302 A EP98946302 A EP 98946302A EP 1002287 A1 EP1002287 A1 EP 1002287A1
Authority
EP
European Patent Office
Prior art keywords
database
upgr
component
stored procedure
new
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Ceased
Application number
EP98946302A
Other languages
German (de)
English (en)
Inventor
Niklas Sinander
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Telefonaktiebolaget LM Ericsson AB
Original Assignee
Telefonaktiebolaget LM Ericsson AB
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Telefonaktiebolaget LM Ericsson AB filed Critical Telefonaktiebolaget LM Ericsson AB
Priority to EP00125246A priority Critical patent/EP1091305B1/fr
Publication of EP1002287A1 publication Critical patent/EP1002287A1/fr
Ceased legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
    • G06F11/14—Error detection or correction of the data by redundancy in operation
    • G06F11/1402—Saving, restoring, recovering or retrying
    • G06F11/1415—Saving, restoring, recovering or retrying at system level
    • G06F11/1433—Saving, restoring, recovering or retrying at system level during software upgrading
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/28—Databases characterised by their database models, e.g. relational or object models
    • G06F16/284—Relational databases
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F2201/00—Indexing scheme relating to error detection, to error correction, and to monitoring
    • G06F2201/80—Database-specific techniques

Definitions

  • the invention relates to a method for upgrading databases including a change of the database format.
  • Relational databases are used to handle the storage and retrieval of data for different applications and enviroments.
  • Databases of this kind run on computer systems.
  • new functionality shall be added to a database, i.e when the database is upgraded, the format in which the data is stored very often has to be changed as well.
  • access to the database must be denied to avoid inconsistency. In other words, it is not allowed to add or change (update) data while the change of the database functionality and format takes place.
  • the database is not accessible during certain phases of an upgrade which phases should be minimized.
  • a conventional solution to the above mentioned problem is to use an additional system during the upgrade.
  • the additional system is a fully operable system with all the functionality of the orginial system.
  • Extra hardware and software is required to log all data changes during the upgrade of the database.
  • the database is copied to the additional system, and a logging mechanism is initiated , for example, in the original system.
  • the upgrade of the database is then performed in the additional system while the original system continues to operate as before but also logs all data changes during the upgrade.
  • all logged data changes are applied to the upgraded database in the addtional system. If the upgrade fails, the addtional system is simply disconnected and the original system continues with its normal operation, however, without additionally logging data changes any longer.
  • the problem to be solved by the invention is to provide a method for upgrading a database including a change of the database format which method avoids extra hardware and software, limits the interuption of the database operation to a minimum, and allows a save return to the original state of the database in case of a failure during the upgrade.
  • a method for upgrading a database using a table for storing data and a stored procedure for processing data stored in said table of said database said database running on a computer system, comprising the steps of: creating new a table in said database; copying and/or transforming data from said table to said new table; adding a new version of said stored procedure to said database; and adding an additional stored procedure to the database which refers to both the previous version and the new version of the stored procedure whenever data is to be processed with the stored procedure, for causing data processing to take place in accordance with the previous version and the new version of the stored procedure.
  • a systemtable For creating a link to said additional stored procedure a systemtable can be used.
  • the systemtable can be a table of the database.
  • a configuration file can be used for creating a link to said additional stored procedure.
  • the configuration file can be stored on a storage medium of the computer system.
  • the above problem is further solved by a method for upgrading a database by replacing an old version with a new version of a component of the database providing database functionality, said database running on a computer system wherein during the upgrade the old version and the new version of the database component providing database functionality are used at least temporarily in parallel and wherein reference to both versions is made through an additional component of the database providing a link to both versions of the database component.
  • the link can be provided by means of a systemtable referring an access to said component of the database to said additional component.
  • the systemtable can be a table of the database.
  • the link can be provided by means of a configuration file referring an access to said component of the database to said additional component.
  • the configuration file is advantageously stored on a storage medium of said computer system.
  • the components of the database are stored procedures, trigger, indexes or tables.
  • the new version of the table may have a format different from the format of the old version of the table.
  • Figure 1 shows a digramm of a computer system hosting a database and workstations accessing the database.
  • Figure 2a shows a diagramm illustrating an upgrade of a database on table level according to the invention.
  • Figure 2b shows a diagramm illustrating an upgrade of a database on page level according to the invention.
  • Figure 3 shows a diagramm of a computer system using a systemtable for upgrading a database according to the invention.
  • Figure 4 shows a diagramm of a computer system using a configuration file for upgrading a database according to the invention.
  • Figure 1 shows a computer system 1 hosting a database 2 on an appropriate storage medium, for example a hard disk or a group of hard disks. Users access the information or data stored in the database, for example, from a terminal or from their workstations 3a, 3b, ... which are connected to the computer system 1 either directly or via a local or wide area network (LAN or WAN) .
  • the database 2 comprises tables, stored procedures, triggers, indexes etc. all of which provide certain database functions, respectively.
  • changes will made to all or some of the components providing database functionality.
  • new and/or additional functions are provided by means of new and/or additional stored procedures, triggers and so on. This sometimes also requires that changes are made to the format of the tables of the database.
  • new tables are created to receive the data stored in the old tables, which are the tables used by the database until the upgrade of the database. Data are copied and/or transformed into the new tables during which process the data are adapted to the new format of the tables if required. To avoid downtime, i.e. periods during which the users cannot access the database, the process of copying and/or transforming data from the old tables to the new tables needs special consideration.
  • data sets of a predetermined granularity are defined within the tables which data sets are handled jointly during the upgrade process to reduced the time periods during which the system is not accessible.
  • FIG. 2a if an upgrade is based on table level (granularity I) , the whole table to be upgraded is modified in one operation. This approach should be choosen where the modification is a fast operation, for example adding a column with a default value. While a table is upgraded, access to the table as a whole is denied to avoid inconsistency. As shown in Figure 2b, if the modification of a table is a more complicated operation that takes longer time, the table is upgraded on page level (granularity II) .
  • a page can be understood to be an internal system unit of a specific size, for example 1024 Bytes, which usually comprises - completely or partly - one or more related information units such as fields or records.
  • both the old and the new tables have to be synchronized as far as data changes and new entries are concerned.
  • the update of data in the old and the new table is an atomic operation, i.e. access is denied to the table or the page until both tables are updated.
  • an update of data is performed in the new tables only where data sets have already been copied and/or transformed from the old tables to the new tables.
  • a first method for handling versions of database components will be described with special emphasis on stored procedures of the database. The skilled person will understand that this process can also be performed for other components of a database comparable to stored procedures.
  • a systemtable is used during the upgrade process which systemtable holds references to all or just thoses stored procedure which are to be upgraded, i.e. replaced by stored procedure of a new version. All or just thoses stored procedure which are to be upgraded are referenced through the systemtable during the upgrade process.
  • a systemtable according to the invention may look as follows:
  • the entries in the systemtable refers only to one set of stored procedures, i.e. the base version.
  • the system looks up the momentarily valid name or version of the stored procedure to which the specific transaction initiated by the workstation is linked. For example, if the access from a workstation requires that stored procedure sp_a is processed the system will look up the valid version of stored procedure sp_a in the systemtable.
  • the valid version of stored procedure sp_a is sp_a_l .0 which is then used by the system to perform the transaction requested by the workstation.
  • new versions of all or some stored procedures are to be installed in the database.
  • a new stored procedure is added to the database.
  • the systemtable is updated with names/versions of the new stored procedures, i.e. the target versions are added.
  • additional stored procedures sp_a_upgr and sp_c_upgr are added to the systemtable.
  • the additional stored procedures sp_a__upgr and sp_c_upgr are used only during the upgrade process and use the new and the old stored procedures to update both the new and the old tables, resepctively, in accordance with transactions initiated by the workstations.
  • the additional stored procedure sp_a_upgr iniates in the database two branches of processing on the basis of base version sp_a__l .0 and new version sp_a_l .1 whereby data is processed and updated in both the new and the old tables of the database system.
  • stored procedure sp_b there exists no additional stored procedure since stored procedure sp_b is not upgraded, i.e. remains the same in the old and the new database.
  • the system will refer to the basic version sp_b in such case and use the base version for both the old and the new tables.
  • a configuration file is used during the upgrade process which file holds references to all or just thoses stored procedure which are to be upgraded, i.e. replace by stored procedure of a new version. All or just thoses stored procedure which are to be upgraded are referenced through the configuration file during the upgrade process.
  • the configuration file is stored on a separate storage media 4 provided in the computer system 1 or the storage media on which the database 2 is also stored.
  • a configuration file according to the invention can be a simple text file and may look as follows:
  • # #ENTRIES name of stored procedure known to the system; #base version; target version; upgrade version sp_a; sp_a_l .0 ; sp_a_l .1 ; sp_a_upgr sp_b; sp_b sp_c; sp__c_l .0 ; sp_c_l .1 ; sp_c_upgr
  • the entries in the configuration file refer only to one set of stored procedures, i.e. the base version.
  • the system looks up the momentarily valid name or version of the stored procedure to which the specific transaction initiated by the workstation is linked. For example, if the access from a workstation requires that stored procedure sp_a is processed the system will look up the valid version of stored procedure sp_a in the configuration file.
  • the valid version of stored procedure sp_a is sp_a_l .0 which is then used by the system to perform the transaction requested by the workstation.
  • new versions of all or some stored procedures are to be installed in the database. It is also possible that a new stored procedure is added to the database.
  • the configuration file is updated with names/versions of the new stored procedures, i.e. the target versions are added.
  • additional stored procedures sp_a_upgr and sp_c_upgr are added to the configuration file. The additional stored procedures sp_a_upgr and sp_c_upgr are used only during the upgrade process and use the new and the old stored procedures to update both the new and the old tables, resepctively, in accordance with transactions initiated by the workstations.
  • the additional stored procedure sp_a_upgr iniates at the workstation two branches of processing on the basis of base version sp_a_l .0 and new version sp_a_l .1 whereby data is processed and updated in both the new and the old tables of the database system.
  • stored procedure sp_b there exists no additional stored procedure since stored procedure sp_b is not upgraded, i.e. remains the same in the old and the new database.
  • the system will refer to the basic version sp_b in such case and use the base version for both the old and the new tables.
  • the switch over from the old stored procedure to the new stored procedures is performed by updating the configuration file and reread it.
  • To update and reread the configuration file is a very fast operation, so that the users of the system will only experience a small delay and no downtime .
  • a third method for handling versions of database components could be a combination of a systemtable and a configuration file.
  • the systemtable will refer to some stored procedures while the configuration file will refer to other stored procedures.
  • the other components of the database can be handled in the same manner.
  • the tables could be referenced through the systemtable or configuaration file and treated similiary as outlined above with respect of stored procedures .

Abstract

Lorsqu'une base de données est en phase d'extension, sa durée d'indisponibilité ainsi que la quantité de logiciel et de matériel utilisée doivent être réduites au minimum. De plus, le retour en arrière doit toujours être possible à n'importe quel moment. Ce procédé consiste d'abord à créer une nouvelle table dans ladite base de données, copier et/ou transformer les données de la dite table à la nouvelle table, ajouter ensuite une nouvelle version de ladite procédure mémorisée à ladite base de données, et enfin, ajouter une procédure mémorisée supplémentaire à la base de données se rapportant à la fois à l'ancienne et à la nouvelle version de la procédure mémorisée à chaque fois que les données sont traitées avec la procédure mémorisée, pour permettre ainsi le traitement des données selon l'ancienne et la nouvelle version de la procédure mémorisée.
EP98946302A 1997-08-08 1998-08-07 Procede d'extension d'une base de donnees Ceased EP1002287A1 (fr)

Priority Applications (1)

Application Number Priority Date Filing Date Title
EP00125246A EP1091305B1 (fr) 1997-08-08 1998-08-07 Procédé d'extension d'une base de données

Applications Claiming Priority (3)

Application Number Priority Date Filing Date Title
DE19734413 1997-08-08
DE19734413A DE19734413C1 (de) 1997-08-08 1997-08-08 Verfahren zum Aufrüsten einer Datenbank
PCT/EP1998/005026 WO1999008206A1 (fr) 1997-08-08 1998-08-07 Procede d'extension d'une base de donnees

Related Child Applications (1)

Application Number Title Priority Date Filing Date
EP00125246.9 Division-Into 2000-11-23

Publications (1)

Publication Number Publication Date
EP1002287A1 true EP1002287A1 (fr) 2000-05-24

Family

ID=7838416

Family Applications (2)

Application Number Title Priority Date Filing Date
EP98946302A Ceased EP1002287A1 (fr) 1997-08-08 1998-08-07 Procede d'extension d'une base de donnees
EP00125246A Expired - Lifetime EP1091305B1 (fr) 1997-08-08 1998-08-07 Procédé d'extension d'une base de données

Family Applications After (1)

Application Number Title Priority Date Filing Date
EP00125246A Expired - Lifetime EP1091305B1 (fr) 1997-08-08 1998-08-07 Procédé d'extension d'une base de données

Country Status (4)

Country Link
EP (2) EP1002287A1 (fr)
AU (1) AU9340598A (fr)
DE (1) DE19734413C1 (fr)
WO (1) WO1999008206A1 (fr)

Families Citing this family (23)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6385770B1 (en) * 1999-01-29 2002-05-07 Telefonaktiebolaget Lm Ericsson (Publ) Software upgrade
US6931630B1 (en) 2000-09-27 2005-08-16 International Business Machines Corporation Method of, system for, and computer program product for providing automatic identification of a computer program code candidate for web deployment or a stored procedure
EP1267263A3 (fr) * 2001-06-11 2006-06-28 Océ-Technologies B.V. Migration en vol par transition incrémentale utilisant des plug-ins pendant la mise à jour
TWI242725B (en) 2001-06-11 2005-11-01 Oce Tech Bv A method for executing a hot migrate operation through an incremental roll-over process that uses migration plug-in means for conversion during an upgrade transition, and a multiprocessing system and a system arranged for implementing such method
DE50103762D1 (de) 2001-06-12 2004-10-28 Sap Ag Verfahren, System und Computerprogrammprodukt zum Ändern der Datenstruktur mit der in einem Computersystem ein Anwendungsprogramm auf Datenbanksysteme zugreift
US8010887B2 (en) 2001-09-21 2011-08-30 International Business Machines Corporation Implementing versioning support for data using a two-table approach that maximizes database efficiency
US7523142B2 (en) 2001-12-17 2009-04-21 Sap Ag Systems, methods and articles of manufacture for upgrading a database with a shadow system
DE10318621A1 (de) 2003-04-24 2004-11-25 Siemens Ag Förderer zum Transportieren von Lastträgern
US7680825B2 (en) 2005-12-30 2010-03-16 Sap Ag Systems and methods for generating tenant-specific properties for use in a provider-tenant environment
US7698284B2 (en) 2005-12-30 2010-04-13 Sap Ag Systems and methods for deploying a tenant in a provider-tenant environment
US7689593B2 (en) 2005-12-30 2010-03-30 Sap Ag Systems and methods for accessing a shared space in a provider-tenant environment
US7917607B2 (en) 2005-12-30 2011-03-29 Sap Ag Software management systems and methods, including use of such systems and methods in a provider-tenant environment
US7693851B2 (en) 2005-12-30 2010-04-06 Sap Ag Systems and methods for implementing a shared space in a provider-tenant environment
US7818740B2 (en) 2006-05-05 2010-10-19 Microsoft Corporation Techniques to perform gradual upgrades
US8069184B2 (en) 2006-12-29 2011-11-29 Sap Ag Systems and methods to implement extensibility of tenant content in a provider-tenant environment
US7933869B2 (en) 2006-12-29 2011-04-26 Sap Ag Method and system for cloning a tenant database in a multi-tenant system
US7739348B2 (en) 2006-12-29 2010-06-15 Sap Ag Systems and methods for accessing a shared space in a provider-tenant environment by using middleware
US8200634B2 (en) 2008-10-08 2012-06-12 Sap Ag Zero downtime maintenance using a mirror approach
US8224828B2 (en) 2009-12-22 2012-07-17 Sap Ag Multi-client generic persistence for extension fields
US9031910B2 (en) 2013-06-24 2015-05-12 Sap Se System and method for maintaining a cluster setup
US9619490B2 (en) * 2014-04-11 2017-04-11 Nutanix, Inc. Mechanism for performing lockless rolling upgrade of NoSQL database
US9858309B2 (en) 2014-10-07 2018-01-02 Sap Se Managing write accesses during database upgrade procedure
US10262054B2 (en) * 2016-01-21 2019-04-16 Microsoft Technology Licensing, Llc Database and service upgrade without downtime

Family Cites Families (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US5287496A (en) * 1991-02-25 1994-02-15 International Business Machines Corporation Dynamic, finite versioning for concurrent transaction and query processing
US5396623A (en) * 1992-10-30 1995-03-07 Bmc Software Inc. Method for editing the contents of a DB2 table using an editproc manager
US5359730A (en) * 1992-12-04 1994-10-25 International Business Machines Corporation Method of operating a data processing system having a dynamic software update facility
US5440735A (en) * 1993-10-08 1995-08-08 International Business Machines Corporation Simplified relational data base snapshot copying
US6112253A (en) * 1995-10-12 2000-08-29 International Business Machines Corporation Object-oriented method maintenance mechanism that does not require cessation of the computer system or its programs

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
See references of WO9908206A1 *

Also Published As

Publication number Publication date
AU9340598A (en) 1999-03-01
WO1999008206A1 (fr) 1999-02-18
EP1091305B1 (fr) 2003-10-29
EP1091305A1 (fr) 2001-04-11
DE19734413C1 (de) 1999-01-07

Similar Documents

Publication Publication Date Title
EP1091305A1 (fr) Procédé d'extension d'une base de données
US8010582B2 (en) Method, system, and program product for migrating data from one data base management system to another data base management system
US5625815A (en) Relational database system and method with high data availability during table data restructuring
US6564215B1 (en) Update support in database content management
US7801850B2 (en) System of and method for transparent management of data objects in containers across distributed heterogenous resources
US6460048B1 (en) Method, system, and program for managing file names during the reorganization of a database object
JP4759570B2 (ja) データベース管理システムにおけるファイル操作のためのロックを提供するための手法
US6088694A (en) Continuous availability and efficient backup for externally referenced objects
US5386559A (en) Variant domains and variant maps in a versioned database management system
AU2005316812B2 (en) Infrastructure for performing file operations by a database server
US7054887B2 (en) Method and system for object replication in a content management system
US7389313B1 (en) System and method for creating a snapshot copy of a database
US9652346B2 (en) Data consistency control method and software for a distributed replicated database system
EP2059881B1 (fr) Procédé de gestion de modification simultanée d'objets de base de données pendant leur développement
US9710262B2 (en) User-configurable database artifacts
CN107077491B (zh) 联机模式和数据变换
KR20030094328A (ko) 저장된 데이터를 재편성하는 시스템 및 방법
Cisco CiscoSecureACS Database Structure
Cisco CiscoSecure ACS Database Structure
Cisco CiscoSecureACS Database Structure
Cisco CiscoSecureACS Database Structure
KR100243113B1 (ko) 데이터베이스 관리 시스템에서 에스큐엘 수준의갱신 연산의 원자성 보장 방법
US7209919B2 (en) Library server locks DB2 resources in short time for CM implicit transaction
Sinclair From Access to SQL Server
Bers et al. Oracle Transparent Gateway for DB2 Installation and User’s Guide, 10g Release 2 (10.2) for IBM z/OS (OS/390) B16220-02

Legal Events

Date Code Title Description
PUAI Public reference made under article 153(3) epc to a published international application that has entered the european phase

Free format text: ORIGINAL CODE: 0009012

17P Request for examination filed

Effective date: 20000112

AK Designated contracting states

Kind code of ref document: A1

Designated state(s): FR GB IT

17Q First examination report despatched

Effective date: 20000809

STAA Information on the status of an ep patent application or granted ep patent

Free format text: STATUS: THE APPLICATION HAS BEEN REFUSED

18R Application refused

Effective date: 20021017