Unique identification of SQL cursor occurrences in a repetitive, nested environment

• US 6,959,305 B2
• Filed: 12/10/2002
• Issued: 10/25/2005
• Est. Priority Date: 12/21/2001
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cotner, Curt L., Bird, Paul M., Vincent, Timothy J.
• Primary Examiner(s): Corrielus, Jean M.
• Application Number:
  US10/317,234
• Publication Number:
  US 20030120636A1
• Time in Patent Office: 1,050 Days
• Field of Search: 707/104.1, 707/101, 707/10, 707/2, 707/3
• US Class Current: 707/702
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: Y10S 707/954 Relational
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A cursor query identifier is defined to uniquely identify cursors in a relational database system that supports nested routine invocation. The identifier includes a cursor nesting level value that corresponds to the nested level at which the cursor is opened. The identifier also includes a invocation identifier that corresponds to the routine within which a cursor is opened. The third part of the identifier is a unique cursor counter. The definition of the cursor query identifier permits cursors to be opened within nested levels of the same relational database context and to be uniquely identified for reference by database system and by applications accessing the system.

Claims

1. A database management system supporting nested invocation of user-defined routines and the definition of cursors, and the system carrying out execution steps in response to requests received from applications and from user-defined routines, the system comprising

means for associating a unique identifier with each cursor occurrence request received by the system, the unique identifier including a first value corresponding to the nesting level of the source process from which the request originated, and a second value uniquely identifying the source process, relative to other sources at the same nesting level;

and

means for passing the unique identifier to the requesting process in response to the cursor occurrence request, wherein the request is received from a requesting process executing an application or a cursor-opening user-defined routine, and wherein the unique identifier associated with each cursor occurrence further comprises a third value uniquely identifying the cursor occurrence relative to other cursor occurrences with the same cursor name taking place in the execution of the cursor-opening user-defined routine or application.

2. The system of claim 1 wherein the system is an SQL system.

3. A database management system, the system supporting nested invocation of a set of user-defined routines by an application, and supporting the definition of cursors in applications, the system comprising:

means for associating a unique identifier with each cursor occurrence in the execution of a routine invoked directly or indirectly by an executing application, the unique identifier comprising

a first value corresponding to the nesting level of the executing application; and

a second value corresponding to the sequence of the executing application relative to other applications at the same nesting level; and

means for passing the unique identifier associated with each cursor occurrence to the executing application, wherein the executing application directly or indirectly invokes a cursor-opening user-defined routine, and in which the unique identifier associated with each cursor occurrence further comprises a third value corresponding to the sequence in which the cursor occurrence is found relative to other cursor occurrences in the execution of the cursor-opening user-defined routine.

4. The system of claim 3 wherein the system is an SQL system.

5. The system of claim 3 in which the means for associating a unique identifier further comprises:

means for defining and maintaining a set of nesting control block data structures, each nesting control block being associated with a unique nesting level of invoked user-defined routines, and comprising a nesting level identifier having a value corresponding to the associated nesting level and further comprising an invocation counter having a value corresponding to the sequence in which user-defined routines are executed at the associated nesting level.

6. The system of claim 5 in which the means for associating a unique identifier further comprises

means for defining and maintaining a set of invocation control block data structures, each invocation control block being associated with a unique invoked user-defined routine, each invocation control block comprising

an invocation control block nesting identifier having a value derived from the nesting level identifier in the nesting control block associated with the nesting level of the unique invoked user-defined routine,

an invocation identifier having a value derived from the invocation counter in the nesting control block associated with the nesting level of the unique invoked user-defined routine, and

a cursor counter having a value corresponding to the sequence in which cursors are opened.

7. A database management system, the system supporting nested invocation of a set of user-defined routines by an application, and supporting the definition of cursors in applications, the system comprising:

means for defining and maintaining a set of nesting control block data structures, each nesting control block being associated with a unique nesting level of invoked user-defined routines, and comprising a nesting level identifier having a value corresponding to the associated nesting level and containing an invocation counter having a value corresponding to the sequence in which user-defined routines are executed at the associated nesting level,

means for defining and maintaining a set of invocation control block data structures, each invocation control block being associated with a unique invoked user-defined routine, each invocation control block comprising

an invocation control block nesting identifier having a value derived from the nesting level identifier in the nesting control block for the nesting level of the unique invoked user-defined routine,

an invocation identifier having a value derived from the invocation counter in the nesting control block for the nesting level of the unique invoked user-defined routine, and

a cursor counter having a value corresponding to the sequence in which cursors are opened in the unique invoked user-defined routine, means for associating a unique identifier with each cursor occurrence in the execution of a routine invoked by an executing application, the unique identifier comprising

a first value derived from the invocation control block nesting identifier for the invocation control block associated with the executing application,

a second value derived from the invocation identifier for the invocation control block associated with the executing application, and

a third value derived from the cursor counter for the invocation control block associated with the executing application, and means for passing the unique identifier associated with each cursor occurrence to the executing application.

8. The system of claim 7 wherein the executing application invokes, directly or indirectly, a cursor-opening user-defined routine.

9. The system of claim 7 wherein the database management system is an SQL system.

10. A computer program product for use in a database management system, the system supporting nested invocation of a set of user-defined routines by an application and supporting the definition of cursors, the computer program product comprising a computer usable medium having computer readable code means embodied in said medium for associating a unique identifier with each cursor occurrence in the execution of an executing application, comprising

computer readable program code means for defining a first value in the unique identifier corresponding to the nesting level of the executing application,

computer readable program code means for defining a second value in the unique identifier corresponding to the sequence in which the executing application is invoked by the executing application, relative to other executing applications at the same nesting level, and

computer readable program code means for passing the unique identifier associated with each cursor occurrence to the executing application, wherein the executing application invokes, directly or indirectly, a cursor-opening user-defined routine, and in which the computer readable code means embodied in said medium for associating a unique identifier with each cursor occurrence further comprises unique identifier associated with each cursor occurrence further comprises computer readable program code means for defining a third value corresponding to the sequence in which the cursor occurrence is found relative to other cursor occurrences in the execution of the cursor-opening user-defined routine.

11. The computer program product of claim 10 in which the computer readable code means embodied in said medium for associating a unique identifier with each cursor occurrence in the execution of a cursor-opening user-defined routine invoked directly or indirectly by an executing application further comprises:

computer readable program code means for defining and maintaining a set of nesting control block data structures, each nesting control block being associated with a unique nesting level of invoked user-defined routines, and comprising a nesting level identifier having a value corresponding to the associated nesting level and further comprising an invocation counter having a value corresponding to the sequence in which user-defined routines are executed at the associated nesting level.

12. The computer program product of claim 11 in which the computer readable code means embodied in said medium for associating a unique identifier with each cursor occurrence in the execution of a cursor-opening user-defined routine invoked directly or indirectly by an executing application further comprises

computer readable program code means for defining and maintaining a set of invocation control block data structures, each invocation control block being associated with a unique invoked user-defined routine, each invocation control block comprising

an invocation control block nesting identifier having a value derived from the nesting level identifier in the nesting control block associated with the nesting level of the unique invoked user-defined routine,

an invocation identifier having a value derived from the invocation counter in the nesting control block associated with the nesting level of the unique invoked user-defined routine, and

a cursor counter having a value corresponding to the sequence in which cursors are opened in the execution of the unique invoked user-defined routine.

13. The computer program product of claim 10 wherein the database management system is an SQL system.

14. A computer program product for use in a database management system, the system supporting nested invocation of a set of user-defined routines by an application and supporting the definition of cursors in applications, the computer program product comprising a computer usable medium having computer readable code means embodied in said medium for associating a unique identifier with each cursor occurrence in the execution of an executing application, comprising

computer readable program code means for defining and maintaining a set of nesting control block data structures, each nesting control block being associated with a unique nesting level of invoked user-defined routines, and comprising a nesting level identifier having a value corresponding to the associated nesting level and containing an invocation counter having a value corresponding to the sequence in which user-defined routines are executed at the associated nesting level,

computer readable program code means for defining and maintaining a set of invocation control block data structures, each invocation control block being associated with a unique invoked user-defined routine, each invocation control block comprising

an invocation control block nesting identifier having a value derived from the nesting level identifier in the nesting control block for the nesting level of the unique invoked user-defined routine,

an invocation identifier having a value derived from the invocation counter in the nesting control block for the nesting level of the unique invoked user-defined routine, and

a cursor counter having a value corresponding to the sequence in which cursors are opened in the unique invoked user-defined routine,

computer readable program code means for associating a unique identifier with each cursor occurrence in the execution of an executing application, the unique identifier comprising

a first value derived from the invocation control block nesting identifier for the invocation control block associated with the executing application,

a second value derived from the invocation identifier for the invocation control block associated with the executing application, and

a third value derived from the cursor counter for the invocation control block associated with the executing application, and

computer readable program code means for passing the unique identifier associated with each cursor occurrence to the executing application.

15. The computer program product of claim 14 wherein the executing application invokes, directly or indirectly, a cursor-opening user-defined routine.

16. The computer program product of claim 14 wherein the database management system is an SQL system.

17. A method for defining a set of cursor query identifiers in a database management system, the system supporting nested invocation of a set of user-defined routines by an application, and supporting the definition of cursors in applications, the method comprising:

generating a nesting control block data structure for each unique nesting level of invoked user-defined routines, the nesting control block comprising a nesting level identifier having a value corresponding to the associated nesting level and further comprising an invocation counter having a value corresponding to the sequence in which user-defined routines are executed at the associated nesting level,

generating an invocation control block data structure for each invoked user-defined routine, each invocation control block comprising

an invocation control block nesting identifier having a value derived from the nesting level identifier in the nesting control block for the nesting level of the unique invoked user-defined routine,

an invocation identifier having a value derived from the invocation counter in the nesting control block for the nesting level of the unique invoked user-defined routine, and

a cursor counter having a value corresponding to the sequence in which cursors are opened in the unique invoked user-defined routine, and

defining a unique cursor query identifier in the set of cursor query identifiers for each cursor occurrence in the execution of an executing application, the definition of the unique cursor query identifier further comprising the steps of

deriving a first value component for the cursor query identifier from the invocation control block nesting identifier for the invocation control block associated with the executing application,

deriving a second value component for the cursor query identifier from the invocation identifier for the invocation control block associated with the executing application, and

deriving a third value component for the cursor query identifier from the cursor counter for the invocation control block associated with the executing application.

18. The method of claim 17 wherein the executing application invokes, directly or indirectly, a cursor-opening user-defined routine.

19. The method of claim 17 wherein the database management system is an SQL system.