Method and system for managing external routines in a database management system

Abstract

A method for managing an external routine in a computer implemented database management system includes creating a first table for storing external routines in a data store coupled to the database management system. An external routine is stored in the first table so that the database management system is allowed to automatically manage any modification related to the external routine and to control access to the external routine such that administrative support is minimized.

1 . A method for managing an external routine in a computer implemented database management system, the method comprising: 
 creating a first table in a data store coupled to the database management system, the first table for storing at least one external routine;    storing the external routine in the first table; and    allowing the database management system to automatically manage any modification related to the external routine and to control access to the external routine such that administrative support is minimized.    
     
     
         2 . The method of  claim 1  wherein the external routine is either a stored procedure or a user defined function.  
     
     
         3 . The method of  claim 1  further comprising: 
 providing a data definition language statement that includes an identifier of the external routine; and    storing the identifier of the external routine in at least one catalog and in the first table such that the identifier in the at least one catalog refers to the identifier of the external routine in the first table.    
     
     
         4 . The method of  claim 1  wherein the external routine includes an identifier and a body portion comprising an implementation of the external routine, and storing the external routine in the first table includes: 
 storing the identifier in a first column; and    storing the body portion in a second column as a database object.    
     
     
         5 . The method of  claim 4  wherein the database object is a binary large object.  
     
     
         6 . The method of  claim 4  wherein the implementation of the external routine includes program code in a language environment associated with the external routine.  
     
     
         7 . The method of  claim 6  wherein storing the body portion in the second column includes compiling the program code prior to storing the body portion in the second column and storing the compiled program code in the second column such that the external routine can be executed directly.  
     
     
         8 . The method of  claim 7  further comprising: 
 providing a plurality of predefined compilation modules in the database management system, wherein each compilation module is associated with a language environment; and    utilizing the predefined compilation module associated with the language environment of the external routine to compile the program code.    
     
     
         9 . The method of  claim 7  further comprising: 
 providing a plurality of external compilation modules, wherein each external compilation module is associated with a language environment;    creating a mapping in the database management system that maps each language environment to the associated external compilation module;    using the mapping to invoke the external compilation module associated with the language environment of the external routine; and    utilizing the invoked external compilation module to compile the program code.    
     
     
         10 . The method of  claim 7  further including: 
 providing a plurality of execution engines, wherein each execution engine is associated with a language environment;    creating a mapping in the database management system that maps each language environment to the associated execution engine;    receiving by the database management system a call to execute the external routine;    using the mapping to invoke the execution engine associated with the language environment associated with the called external routine; and    utilizing the invoked execution engine to execute the compiled program code of the external routine.    
     
     
         11 . A computer readable medium containing program instructions for managing an external routine in a computer implemented database management system, the program instructions for: 
 creating a first table in a data store coupled to the database management system, the first table for storing at least one external routine;    storing the external routine in the first table, and    allowing the database management system to automatically manage any modification to the database related to the external routine and to control access to the external routine such that administrative support is minimized.    
     
     
         12 . The computer readable medium of  claim 11  wherein the external routine is either a stored procedure or a user defined function.  
     
     
         13 . The computer readable medium of  claim 11  further comprising instructions for: 
 providing a data definition language statement that includes an identifier of the external routine; and    storing the identifier of the external routine in at least one catalog and in the first table such that the identifier in the at least one catalog refers to the identifier of the external routine in the first table.    
     
     
         14 . The computer readable medium of  claim 11  wherein the external routine includes an identifier and a body portion comprising an implementation of the external routine, and storing the external routine in the first table includes: 
 storing the identifier in a first column; and    storing the body portion in a second column as a database object.    
     
     
         15 . The computer readable medium of  claim 14  wherein the database object is a binary large object.  
     
     
         16 . The computer readable medium of  claim 14  wherein the implementation of the external routine includes program code in a language environment associated with the external routine.  
     
     
         17 . The computer readable medium of  claim 16  wherein storing the body portion in the second column includes compiling the program code prior to storing the body portion in the second column and storing the compiled program code in the second column such that the external routine can be executed directly.  
     
     
         18 . The computer readable medium of  claim 17  further comprising instructions for: 
 providing a plurality of predefined compilation modules in the database management system, wherein each compilation module is associated with a language environment; and    utilizing the predefined compilation module associated with the language environment of the external routine to compile the program code.    
     
     
         19 . The computer readable medium of  claim 17  further comprising instructions for: 
 providing a plurality of external compilation modules, wherein each external compilation module is associated with a language environment;    creating a mapping in the database management system that maps each language environment to the associated external compilation module;    using the mapping to invoke the external compilation module associated with the language environment of the external routine; and    utilizing the invoked external compilation module to compile the program code.    
     
     
         20 . The computer readable medium of  claim 17  further including instructions for: 
 providing a plurality of execution engines, wherein each execution engine is associated with a language environment;    creating a mapping in the database management system that maps each language environment to the associated execution engine;    receiving by the database management system a call to execute the external routine;    using the mapping to invoke the execution engine associated with the language environment associated with the called external routine; and    utilizing the invoked execution engine to execute the compiled program code of the external routine.    
     
     
         21 . A system for managing an external routine to be utilized by a database management system, the system comprising: 
 a server computer system coupled to a plurality of client systems via a network;    at least one data store coupled to the server computer system, wherein the data store includes a first table for storing at least one external routine; and    a database management system in the server computer system that includes a routine manager,    wherein the routine manager automatically manages any modification to the database related to the external routine and controls access to the external routine such that administrative support is minimized.    
     
     
         22 . The system of  claim 21  wherein the external routine is either a stored procedure or a user defined function.  
     
     
         23 . The system of  claim 21  wherein the external routine includes an identifier and a body portion comprising an implementation of the external routine, and the routine manager stores the identifier in a first column of the first table and stores the body portion in a second column of the first table as a database object.  
     
     
         24 . The system of  claim 23  wherein the implementation of the external routine includes program code in a language environment associated with the external routine.  
     
     
         25 . The system of  claim 24  wherein the routine manager compiles the program code prior to storing the body portion in the second column and stores the compiled program code as a binary large object in the second column such that the external routine can be executed directly.  
     
     
         26 . The system of  claim 25  wherein the database management system further includes a plurality of predefined compilation modules, wherein each compilation module is associated with a language environment, and the routine manager utilizes the predefined compilation module associated with the language environment of the external routine to compile the program code.  
     
     
         27 . The system of  claim 25  wherein the server computer system includes a plurality of compilation modules, wherein each compilation module is associated with a language environment and the database management system includes a mapping that maps each language environment to the associated external compilation module such that the routine manager uses the mapping to invoke the compilation module associated with the language environment of the external routine to compile the program code.  
     
     
         28 . The system of  claim 25  wherein the server computer system includes a plurality of execution engines, wherein each execution engine is associated with a language environment and the database management system includes a mapping that maps each language environment to the associated execution engine such that when a call to execute the external routine is received, the routine manager uses the mapping to invoke the execution engine associated with the language environment associated with the called external routine to execute the compiled program code of the external routine.