Method and system for managing connections to a database management system

[0002] The present application is a continuation of U.S. patent application Ser. No. 09/104,879, now issued as U.S. Pat. No. 6,112,196.

[0003] The present invention relates to database management systems and more particularly to a method and system for more efficiently managing connections to database management systems, thereby improving throughput and response time.

[0004] Recently the use of the internet has greatly increased. The internet is a collection of networks over which information can be exchanged. The World Wide Web (“WWW”) is a facility which links locations on the internet using a particular protocol. The WWW utilizes hypertext markup language (“HTML”) and hypertext transfer protocols (“HTTP”) in order to transfer data. The internet allows a user on a first system, known as a client, to access information on another system, known as a server. Typically, the client includes a browser through which the user can view information provided by the server. The server typically includes server software which allows communication to clients on the internet. The server software typically includes run time structures including an initialization thread, a listener thread, and a plurality of worker threads. The initialization thread initializes the server. The listener thread listens for work requests arriving from the internet. The worker threads execute work requests for the server software, including work requests that may require the use of other applications residing on a server.

[0005] The information provided by the server could be obtained from one or more database management systems. Database management systems are used by computer systems in order to store information and access the stored information. A database management system is software, which manages one or more databases. A database is a collection of information. Typically, a request for information is received by the server software residing on the server. Such a request for information residing in a database is known as a database request. The database request is assigned to a worker thread for processing. In order for the worker thread to access information in a database, a connection between the worker thread and a database management system is created. Note that there may be multiple instances of the database management system on the server. Once the connection is created, the database request can be executed. When processing of the database request is completed, the connection is then typically terminated.

[0006] Although the worker thread can use the database management system to execute the database request, creating connections to the database management system is a complex process and can require the execution of an extremely large number of program instructions. As a result, the creation of connections involves significant overhead and may be very time consuming. Each time another database request requiring use of a database management system is received by the server software and assigned to a worker thread, a new connection must be generated. This is true even if the same database management system is to be used to process the database request. Consequently, processing of requests may be slowed.

[0007] Accordingly, what is needed is a system and method for more efficiently managing connections. It would also be beneficial if the system and method allowed requests to be processed more efficiently. The present invention addresses such a need.

[0008] The present invention provides a method and system for processing a request utilizing a database management system in a computer system. The database management system manages at least one database. At least one instance of the database management system is available. The computer system includes a plurality of worker threads. The method and system comprise assigning the request to a worker thread of the plurality of worker threads. The worker thread is for aiding in execution of the request. The method and system also comprise providing a connection to a particular instance of the database management system for the worker thread and associating the connection with the worker thread if the worker thread has not previously used the particular instance of the database management system. The method and system further include reusing a connection to a particular instance of the database management system that is associated with the worker thread if the worker thread has previously used the particular instance of the database management system.

[0009] According to the system and method disclosed herein, the present invention can reuse connections, thereby increasing system throughput and response time.

[0010] FIG. 1 is a block diagram of a system for accessing a database via the internet.

[0011] FIG. 2 is a flow chart depicting a conventional method for processing a database request.

[0012] FIG. 3 is a block diagram of a system in which the present invention is utilized.

[0013] FIG. 4 is a block diagram of the run time structures for a system for more efficiently managing connections in accordance with the present invention.

[0014] FIG. 5 is a flow chart depicting a method for processing database requests in accordance with the present invention.

[0015] FIG. 6 is a flow chart depicting a preferred method for determining if the desired connection was previously generated.

[0016] FIG. 7 is a flow chart depicting a method for creating a connection in accordance with the present invention.

[0017] FIG. 8 is a flow chart depicting a method for associating a connection with a worker thread in accordance with the present invention.

[0018] FIG. 9 is a flow chart depicting a method for reusing a connection associated with a worker thread in accordance with the present invention.

[0019] The present invention relates to an improvement utilizing a database. The following description is presented to enable one of ordinary skill in the art to make and use the invention and is provided in the context of a patent application and its requirements. Various modifications to the preferred embodiment will be readily apparent to those skilled in the art and the generic principles herein may be applied to other embodiments. Thus, the present invention is not intended to be limited to the embodiment shown but is to be accorded the widest scope consistent with the principles and features described herein.

[0020] FIG. 1 is a block diagram of a system 10 for utilizing databases via the internet. The system 10 includes clients 12, 14, and 16. The clients 12, 14, and 16 are coupled with a server 20 via the internet 18. In order to access information through the server 20, the client 12, 14, or 16 sends a request (not shown) to the server 20. The server 20 includes a plug-in 28 and server software 30. The plug-in 28 may provide additional functionality to the server 20 through interactions with the server software 30 and other applications. The server software 30 includes run time structures also depicted in FIG. 1. The run time structures include a listener thread 32, a plurality of worker threads 34, and an initialization thread 36. The listener thread 32 listens for incoming requests. Each worker thread 34 aids in processing an incoming request. The initialization thread 36 initializes the server software 30. The server 20 is be coupled to databases 22, 24, and 26. Each of the databases 22, 24, and 26 holds information.

[0021] FIG. 2 is a flow chart of a conventional method 50 for processing a request which utilizes a database, for example database 22. Such a request is typically known as a database request. The database request typically uses a Structured Query Language (“SQL”) statement to perform operations in the database. The database request also includes a user identification which typically identifies the user at the client 12, 14, or 16, making the database request. The database request also typically identifies a section of a plan (not shown) that a database management system (not shown) will use to execute the database request. The database management system manages the corresponding database 22. A plan is typically a plurality of run time structures for a database management system which is used to allow execution of SQL statements. When the database request is actually executed, the section of the plan identified in the database request will be used to execute the database request.

[0022] The conventional method 50 commences when a database request for information stored in one of the databases 22, 24, or 26 is received by the server software 30 via step 52. Because the database request is for information stored in a database 22, 24, or 26, the database request utilizes the corresponding database management system. The listener thread 32 typically receives incoming requests being received by the server software 30. Once a database request is received, the worker thread 34 is assigned to aid in processing the database request via step 54. A connection between the worker thread 34 and a database management system (not shown) is created via steps 56, 58, and 60. Note that multiple database management systems can exist on the server 20 at the same time. The worker thread 34 is authorized to use the appropriate instance of the database management system via step 56. In one conventional database management system and operating system, DB2 and OS/390 by International Business Machines, Inc., respectively, this step of authorizing a particular worker thread to use a particular instance of a database management system, called a database subsystem, is known as an “Identify” step. A user identification for the user sending the database request is then associated with the connection being created, via step 58. For DB2, the step 58 is known as “Sign On”. The plan for processing the database request is then identified via step 60. For DB2, the step 60 is known as “Create Thread”. Through steps 56, 58, and 60, the connection between the worker thread 24 and an instance of the database management system is generated. The database request is then executed via step 62. Once the database request has been executed, the connection is terminated, via step 64. When a new request utilizing the database management system is received, the conventional method 50 is repeated.

[0023] Although the system 10 and the conventional method 50 function, those with ordinary skill in the art will realize that the conventional method 50 can be relatively slow. In particular, creating a connection using steps 56, 58, and 60 is costly and may be time consuming. Although each of the three steps used to create a connection, Identify, Sign On, and Create Thread, require time to be executed, the steps do not consume the same amount of system resources when being completed. Typically, the Identify step is the most costly to complete. The Create Thread step requires the next largest amount of system resources to complete. The Sign On step requires the least amount of system resources and time of the three steps to complete. The three steps 56, 58, and 60 together require the execution of a large number of instructions and are relatively costly.

[0024] One of ordinary skill in the art will also realize that the conventional method 50 is carried out each time that a request utilizing a database 22, 24, or 26 is received. Thus, one of ordinary skill in the art will recognize that a significant amount of system resources is consumed each time a database request is processed. The first time that a database 22, 24, or 26 is accessed via an instance of the corresponding database management system, the conventional method 50 is extremely time consuming. This is because a large number of instructions are executed to create the first connection. When another database request is received, the conventional method 50 is carried out again and another connection created. Formation of subsequent connections to the instance of the database management system generally requires the execution of fewer program instructions. However, the creation of subsequent connections is still quite expensive. Thus, each time another database request requiring use of the database management system is received by the server software, a new connection is generated via the conventional method 50. Consequently, processing of subsequent database requests remains costly.

[0025] The present invention provides for a method and system for processing a request utilizing a database management system in a computer system. The database management system manages a database. At least one instance of the database management system is available. The computer system includes a plurality of worker threads. The method and system comprise assigning the request to a worker thread of the plurality of worker threads. The worker thread is for aiding in execution of the request. The method and system also comprise providing a connection to a particular instance of the database management system for the worker thread and associating the connection with the worker thread if the worker thread has not previously used the particular instance of the database management system. The method and system further include reusing a connection to a particular instance of the database management system that is associated with the worker thread if the worker thread has previously used the particular instance of the database management system.

[0026] The present invention will be described in terms of a particular database, a particular architecture, and requests received over the internet. However, one of ordinary skill in the art will readily recognize that this method and system will operate effectively for other types of databases, other architectures, and other requests received from within a single network or computer system. In addition, the present invention will be described in terms of a system having one database and a corresponding database management system residing on the server. However, one of ordinary skill in the art will realize that the present invention will operate effectively for a system having multiple databases, multiple database management systems, and database management systems which reside elsewhere. The present invention will also be described in the context of a single instance of the database management system, or subsystem. However, one of ordinary skill in the art will realize that the method and system are consistent with the use of multiple database subsystems. The present invention is also depicted as having a single connection between a worker thread and a single database subsystem. However, one of ordinary skill in the art will readily realize that the method and system are consistent with a worker thread having connections to multiple database subsystems. In addition, one of ordinary skill in the art will realize that the method and system are consistent with multiple database subsystems utilizing a database.

[0027] To more particularly illustrate the method and system in accordance with the present invention, refer now to FIG. 3 depicting one embodiment 100 of the environment in which the present invention is used. The system 100 includes clients 102, 104, and 106. The clients 102, 104, and 106 are coupled with a server 110 via the internet 108. In order to access information through the server 110, the client 102, 104, or 106 sends a request (not shown) to the server 110. The server 110 includes a plug-in 130, server software 120, and a database management system 141. The plug-in 130 may provide additional functionality to the server 110 through interactions with the server software 120 and other applications. In a preferred embodiment, one such additional function relates to use of the database 140. Preferably, the plug-in 130 is NET.DATA, provided by International Business Machines, Inc. However, the server 110 could include other plug-ins (not shown). The server 110 is coupled to a database 140. The database management system 141 manages the database. Preferably, the database management system 141 is DB2. Thus, the database management system 141 is preferably a relational database management system having rows and columns and using relational techniques for storing and retrieving data. However, the server 110 could include or be coupled to other database management systems (not shown) which may or may not be relational database management systems. The present invention is consistent with the use of other database management systems (not shown).

[0028] FIG. 4 depicts a block diagram of the run time structure 150 of the server 110 in accordance with the present invention. The run time structure 150 of the server 100 includes an initialization thread 122, a listener thread 124, and a plurality of worker threads 126. The listener thread 122 listens for incoming requests. Each worker thread 126 aids in processing an incoming request. The worker thread includes a service block 134. The service block is from code contained in the plug-in 130 and is invoked when a request, often termed a database request, utilizes the database 140. The initialization thread 122 initializes the server 110 for service block 134 processing. The initialization thread 122 is, therefore, responsible for generating the worker threads 126 and the listener thread 124. The initialization thread 122 includes a service initialization block 132 generated from code contained in the plug-in 130. The service initialization block 132 is used during initialization of the server 110. The service initialization block 132 creates a key during initialization of the server 110. The key is utilized in a preferred embodiment of the present invention and is further described below. Also in a preferred embodiment, the present invention is an internet connection server application programming interface (“ICAPI”) solution.

[0029] The run time structure 150 of the server 100 further includes a connection 160 between the database subsystem 142 and the worker thread 126, and worker thread specific data 152. Preferably, the worker thread specific data 152 is created for each worker thread 126. The worker thread specific data 152 includes data about the connections, such as the connection 160, associated with a corresponding worker thread 126. In a preferred embodiment, the worker thread specific data 152 includes a connection list 154 for the corresponding worker thread 126. Also in a preferred embodiment each worker thread 126 may have a corresponding connection list 154. Each element in the connection list 154 data relating to a connection 160 between the worker thread 126 and a particular database subsystem 142. Thus, for each connection 160 from the worker thread 126, the connection list 154 includes an element. The value of the key for the worker thread, discussed above, indicates the address at which the connection list 154 is located. Thus, the worker thread specific data 142 also preferably includes a value (not shown) of the key.

[0030] The run time structure 150 of the server 100 further includes a database subsystem 142. The database subsystem 142 is an instance of the database management system 141 on the server 110 and is concurrently accessible by multiple users. In addition, because multiple instances of the database management system 141 are allowed, multiple database subsystems 142 may exist. The database subsystem 142 includes an attach facility 144 and a plan 146. The plan 146 associated with and used by the database subsystem 142 is the plan for the plug-in 130. The attach facility includes application program interfaces (APIs) used to allow a connection 160 between the database subsystem 142 and the worker thread 126. Note that although FIG. 4 depicts a single connection between a particular worker thread 126 and a single database subsystem 142, multiple connections could exist between a worker thread 126 and a plurality of database subsystems 142. In addition, a plurality of worker threads 126 could exist.

[0031] FIG. 5 depicts a method 200 in accordance with the present invention for processing a request using the database management system 141. Preferably, the method 200 is executed using the service block 134 of the worker thread 126. The database request preferably includes a user identification that identifies the user or client 102, 104, or 106 generating the database request. The method 200 commences after the database request for information in the database 140 that requires the use of the database management system 141 received by the listener thread 124. A worker thread 124 is assigned to the database request, via step 202.

[0032] It is determined if the connection 160 between the worker thread 126 and the database subsystem 142 has previously been generated, via step 204. In one embodiment, this determination is made by determining whether the worker thread specific data 152 exists and what the elements of the worker thread specific data 152 are. If the connection 160 was not previously created, then the connection 160 to the database subsystem 142 is provided via step 210. The connection 160 is associated with the corresponding worker thread 126 via step 220. If the connection 160 was previously created, then the connection 160 is reused via step 230. The database request is then executed via step 240.

[0033] FIG. 6 depicts a preferred embodiment of the step 204 of determining if the connection 160 between the worker thread 126 and the database subsystem 142 was previously generated. It is determined whether a connection list 154 exists for the worker thread 126, via step 205. If the connection list 154 does not exist, then it is determined in step 207 that the worker thread does not have the desired connection 160 associated with it. If the connection list 154 exists, then in step 206 it is determined if the connection list 154 associated with the worker thread 126 includes an element for the desired connection 160. If the connection list 154 does not include an element for the connection 160, then in step 207 it is determined that the connection 160 was not previously generated. If it is determined that an element for the connection 160 exists, then it is determined in step 208 if the connection 160 is still valid. If the database management system 141 was restarted between the time when the connection 160 was generated or last used and when step 208 is being performed, the connection 160 may no longer be valid. The connection 160 should, therefore be recreated. In a preferred embodiment, therefore, if the connection 160 is determined to be not valid in step 208, the connection 160 will be determined to be not previously generated in step 207. If, however, the connection 160 is valid, then the connection 160 will be determined to have been previously generated, via step 209. Also note that in a preferred embodiment, each connection 160 is between a particular worker thread 126 and a particular database subsystem 142. Thus, in a preferred embodiment, a separate element in the connection list 154 is saved for each connection made from a particular worker thread 126 to a different database subsystem (not shown). Thus, in a preferred embodiment, if the worker thread 126 was previously only connected to another database subsystem or subsystems (not shown), the desired connection will be determined to not have been previously generated in step 204.

[0034] FIG. 7 depicts a more detailed flow chart of one embodiment of the step 210 for creating the connection 160 when it has been determined that the connection 160 was not previously created. The worker thread 126 is identified as a thread which is authorized to use the database subsystem 142, via step 212. Thus, in a preferred embodiment, step 212 is an Identify step. The user identification provided in the database request is then associated with the connection 160 via step 214. Thus, in a preferred embodiment, step 214 is a Sign On step. The plan 146 corresponding to the database 142 subsystem is then associated with the connection 160, via step 216. Thus, in a preferred embodiment, step 216 is a Create Thread step. Consequently, the connection 160 is created.

[0035] FIG. 8 depicts a more detailed flow chart of one embodiment of the step 220 of associating the connection 160 created in step 210 with the worker thread 126. Information relating to the connection is stored in the thread specific data 152, via step 222. In a preferred embodiment, the step of storing information relating to the connection 160 includes storing this information in an element of the connection list 154. In a preferred embodiment, the step of storing the information relating to the connection 160 also includes associating an address of a location of the connection list 154 by storing a value of the key. Preferably, the information includes the user identification, the plan 146, and the identity of the database subsystem 142. The identity of the database subsystem 142 is used in part because multiple instances of the database management system 141 may be available on the same server. Note that if the connection 160 is the first connection being created by the server 110, then the element for the connection 160 is the first element in the connection list 154. The connection 160 is then marked as the current connection via step 224. In a preferred embodiment, step 224 includes marking the element for the connection 160 as the current connection. Note that as more connections (not shown) to other database subsystems (not shown) are associated with the worker thread 126, the number of elements in the connection list 154 grows. Eventually, there will be an element in the connection list 154 for each of the database subsystems 142 which the worker thread 126 will use.

[0036] FIG. 9 depicts a more detailed flow chart of one embodiment of the step 230 of reusing the connection 160. Because the connection 160 is being reused, a previous user identification and previous plan (not shown) are associated with the connection 160 in the worker thread specific data 152. The previous user identification and previous plan are those which were associated with the connection 160 when the connection 160 was last used. The data relating to the connection is retrieved from the worker thread specific data 152, via step 232. In a preferred embodiment, this includes getting the data relating to the element corresponding to the connection 160 in the connection list 154 and marking the element as the current connection. If the user identification for the current database request is different from the previous user identification, then via step 234 the user identification for the current database request is associated with the connection 160. Thus, in a preferred embodiment, the Sign On step is repeated to change the user identification if the current database request is from a new user. If the plan is different from the previous plan, then via step 236 the plan is associated with the connection 160. Thus, in a preferred embodiment, the Create Thread step is repeated if the plan 146 is different from the previous plan. Note that since the database management system 141 generally includes a single plan for the plug-in 130, the previous plan will generally be the same as the plan 146. Thus, the Create Thread step will generally not be repeated in step 236.

[0037] Associating the connection 160 with the worker thread 126 allows the worker thread 126 to reuse connection 160 for subsequent database requests. Associating the connection 160 with the worker thread 126 saves information relating to the connection 160 in the worker thread specific data 152, which corresponds to a particular worker thread 126, across uses of the connection 160. The connection 160 can, therefore, be reused.

[0038] Reuse of the connection 160 simplifies processing of a database request because a connection does not have to be created each time the worker thread 126 processes a request which uses a database subsystem 142. This means that the Identify step, the Sign On step, and the Create Thread step may not need to be repeated each time the worker thread 126 processes a request using the database subsystem 142. Thus, because the connection 160 can be reused, the Identify step, which identifies the worker thread 126 as being authorized to use the database subsystem 142, need not be carried out each time the worker thread 126 utilizes the database subsystem 142. The time and cost to process the request using the worker thread 126 and the database subsystem 142 is thereby reduced. Where the same plan or the same user identification is used, the time and cost to process the database request is further reduced because the Sign On and Create Thread steps need not be repeated. Consequently, the time and cost required for the worker thread 126 to process a request utilizing the database subsystem 142 is decreased.

[0039] As execution of the server software 120 continues, a connection 160 corresponding to each worker thread 126 will be established to each database subsystem which the worker thread 126 will use. When all such connections are created, the system 100 will reach a steady state. Once the steady state is reached, the Identify step (step 212) may no longer be required for any of the worker threads 126. For example, in a preferred embodiment, the Identify step will only be performed if the connection 160 is lost, for example because the database management system 141 restarts. Thus, each worker thread 126 may be capable of reaping the benefits of reusing the corresponding connection 160 to each data base subsystem 142. Consequently, the time and cost to process any request utilizing any database subsystem 142 is reduced. When the database management system 141 includes a single plan, the time and cost to process a database request is further reduced because the Create Thread step (step 216 or 234) may not need to be repeated for each database request. Where the same user having the same user identification makes multiple database requests, the time and cost to process each database request is further reduced. This reduction in time and cost occurs because the Sign On step (step 212 or 232) may not be repeated for each database request. As discussed above, it is believed that the step that will most frequently be performed, even at steady state, is the Sign On step. This step is also generally the least time consuming and least costly. Thus, the method and system in accordance with the invention also reduce the number of times the more time consuming and costly steps are performed, particularly during steady state. Consequently, requests utilizing a database 140 will be more efficiently processed.

[0040] In a preferred embodiment, the method 200 is performed using the service block 134 of the worker thread 126. As discussed above, the service block 134 corresponds to the plug-in 130 and is invoked when the request utilizes the database 140. In one embodiment, the service block 134 may be invoked when a request which does not utilize the database 140 is received. In processing such a request, the server 110 would ignore the presence of the connection list 154 and not perform the method 200.

[0041] Also in a preferred embodiment, the user identification is used by the database subsystem 142 to determine if a particular user has access to a portion of the database 140. If a user is not determined to be authorized, then the user may not use or change that portion of the database 140. In accordance with the present invention, the information in the element of the connection list 154 marked as current includes the user identification. Thus, it can be determined if the user identification for the current database request is the same as a previous user identification for the previous database request. If so, the previous user identification can by used by the database subsystem 142 to determine if the user has access to a particular portion of the database 140. If not, the current user identification for the current database request is used by the database subsystem 142 to determine if the user has access to a particular portion of the database 140. Consequently, the security of the database 140 may be maintained.

[0042] Also in a preferred embodiment, the present invention is scaleable. The preceding description can be viewed as relevant to a particular address space of the server 110. Certain operating system software, such as the preferred operating system OS/390, may provide a mechanism for creating new address spaces to handle new requests when all of the worker threads 126 are already being used. Certain operating system software, such as the preferred operating system OS/390, may also contract the number of address spaces if the worker threads in an address space are idle for a particular amount of time. In the preferred embodiment, the present invention can be invoked by the plug-in 130 in each of the address spaces. Thus, the ability to reuse connections can be extended as the workload changes.

[0043] A method and system has been disclosed for processing requests utilizing a database. Although the present invention has been described in accordance with the embodiments shown, one of ordinary skill in the art will readily recognize that there could be variations to the embodiments and those variations would be within the spirit and scope of the present invention. Accordingly, many modifications may be made by one of ordinary skill in the art without departing from the spirit and scope of the appended claims.