Program and method for establishing a physical database layout on a distributed processor system

• US 5,758,345 A
• Filed: 11/08/1995
• Issued: 05/26/1998
• Est. Priority Date: 11/08/1995
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Wang, James Chien
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Corrielus, Jean M.
• Application Number:
  US08/555,025
• Time in Patent Office: 930 Days
• Field of Search: 395/600, 395/401, 395/405, 395/800, 707/10, 707/200, 707/206
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and program for use with a massively parallel processor (MPP) system or a distributed computer system for providing a physical design layout database across several nodes of the system. A user inputs a first datafile representing the physical configuration of the distributed processor system, and inputs a second datafile representing application tablespaces and/or database system files to be stored in the distributed processor system. The program optionally assigns the tablespaces in the second datafile to the physical configuration of the distributed processor system as specified in the first datafile and per the number of nodes, number of disks per node, and the size of the disks. The user may optionally use the physical design layout of the program, may change a portion of the intermediate physical design layout, or may provide the entire physical database design layout, as desired. The program generates a logical volume map for the tablespaces distributed over the distributed processor system, and further generates scripts to implement the tablespace structure into a physical database.

Claims

1. A method for establishing a physical database layout for use with a distributed processor system, the method comprising the steps of:

inputting a first datafile representing the physical configuration of the distributed processor system;

inputting a second datafile representing tablespaces to be stored in the distributed processor, said second datafile including a name and size of each tablespace in said second datafile;

determining from said first datafile the number of nodes available in the distributed processor system, the number of disks per available node, and the size of each of the disks in the distributed processor system;

performing a PROGRAM DESIGN phase for automatically allocating the tablespaces to the nodes and disks in the nodes determined to be available and updating the second datafile with the allocations;

providing for the modification of the allocation of the tablespaces in said second datafile by a user;

performing an EASY DESIGN phase for automatically assigning each tablespace into a logical volume in disk space of the disks in the distributed processor system, and generating therefrom a logical volume map including an indication of the node, disk, volume group and size of the tablespaces thus assigned;

selecting the nodes on which the tablespaces are going to be assigned;

providing for the modification of the logical volume map by a user; and

performing a DETAIL DESIGN phase for automatically generating scripts for implementing the physical database layout from said logical volume map.

2. The method of claim 1 wherein the selecting of nodes comprises spreading said tablespaces evenly across all of the nodes in said first datafile.

3. The method of claim 1 wherein the selecting of nodes comprises assigning each node in said first datafile with its own unique tablespace.

4. The method of claim 1 wherein the selecting of nodes comprises selecting a subset of nodes from the first datafile to be used with a designated tablespace.

5. The method of claim 1 wherein the EASY DESIGN phase includes striping a very large tablespace wherein the very large tablespace is evenly spread out over the selected nodes.

6. The method of claim 1 wherein the EASY DESIGN phase includes allocating the logical volumes in accordance with a logical volume allocation (LVM) policy.

7. The method of claim 6 wherein the LVM allocation policy allocates the logical volume across a designated set of disks.

8. The method of claim 6 wherein the LVM allocation policy allocates the logical volume across all available disks in a node.

9. The method of claim 6 wherein the LVM allocation policy allocates the logical volume on one disk.

10. The method of claim 1 further comprising designating whether the tablespaces are to be spread among the nodes in either a round robin fashion or a range partition fashion.

11. The method of claim 1 further comprising designating whether each tablespace is to be a read based or a write based tablespace, and wherein said DETAIL DESIGN phase assigns distributed lock manager locks for each tablespace designated as a write tablespace.

12. The method of claim 1 further comprising designating a size for the initial and next extents of a desired tablespace, and further comprises spreading the desired tablespace across disks dependent on the designated initial and next extent sizes.

13. The method of claim 1 wherein said distributed processor system has layers of software for operating said distributed processor system, said layers including an application layer for implementing a database, a parallel operating layer for operating the distributed processor system in a parallel fashion, and a node operating system layer for operating each node, said DETAIL DESIGN phase for generating scripts for the node operating system layer, the parallel operating layer, and the application layer, respectively.

14. The method of claim 1 wherein said EASY DESIGN phase creates a disk report including disk capacity, disk space allocated and disk space available for every node in said first datafile.

15. A method for implementing a physical database layout for use with a distributed processor system, the method comprising the steps of:

inputting a first datafile representing the physical configuration of the distributed processor system;

inputting a second datafile representing tablespaces to be stored in the distributed processor, said second datafile including a name and size of each tablespace in said second datafile;

determining from said first datafile the number of nodes available in the distributed processor system, the number of disks per available node, and the size of each of the disks in the distributed processor system;

performing a PROGRAM DESIGN phase for automatically allocating the tablespaces to the nodes and disks in the nodes determined to be available and updating the second datafile with the allocations;

providing for the modification of the allocation of the tablespaces in said second datafile by a user;

performing an EASY DESIGN phase for automatically assigning each tablespace into a logical volume in disk space of the disks in the distributed processor system, and generating therefrom a logical volume map including an indication of the node, disk, volume group and size of the tablespaces thus assigned;

providing for the modification of the logical volume map by a user; and

performing a DETAIL DESIGN phase for automatically generating scripts for implementing the physical database layout from said logical volume map; and

designating a size for the initial and next extents of a desired tablespace, and spreading the desired tablespace across disks dependent on the designated initial and next extent sizes;

wherein said distributed processor system has layers of software for operating said distributed processor system, said layers including an application layer for implementing a database, a parallel operating layer for operating the distributed processor system in a parallel fashion, and a node operating system layer for operating each node, said DETAIL DESIGN phase for generating scripts for the node operating system layer, the parallel operating layer, and the application layer, respectively, and wherein said application layer is an Oracle layer, said parallel operating layer is a PSSP and VSD layer, and said node operating system layer is an AIX layer, and said DETAIL DESIGN layer generates AIX command scripts, VSD scripts, and Oracle scripts, in that order.

16. A program recorded on a computer readable medium, said program for establishing a physical database layout for use with a distributed processor system by a method comprising the steps of:

inputting a first datafile representing the physical configuration of the distributed processor system;

inputting a second datafile representing tablespaces to be stored in the distributed processor, said second datafile including a name and size of each tablespace in said second datafile;

determining from said first datafile the number of nodes available in the distributed processor system, the number of disks per available node, and the size of each of the disks in the distributed processor system;

performing a PROGRAM DESIGN phase for automatically allocating the tablespaces to the nodes and disks in the nodes determined to be available and updating the second datafile with the allocations;

providing for the modification of the allocation of the tablespaces in said second datafile by a user;

performing an EASY DESIGN phase for automatically assigning each tablespace into at least one logical volume in disk space of the disks in the distributed processor system, and generating therefrom a logical volume map including an indication of the node, disk, volume group and size of the tablespaces thus assigned;

selecting the nodes on which the tablespaces are going to be assigned;

providing for the modification of the logical volume map by a user; and

performing a DETAIL DESIGN phase for automatically generating scripts for implementing the physical database layout from said logical volume map.

17. The program of claim 16 wherein the selecting of nodes comprises spreading said tablespaces evenly across all of the nodes in said first datafile.

18. The program of claim 16 wherein the selecting of nodes comprises assigning each node in said first datafile with its own unique tablespace.

19. The program of claim 16 wherein the selecting of nodes comprises selecting a subset of nodes from the first datafile to be used with a designated tablespace.

20. The program of claim 16 wherein the EASY DESIGN phase includes striping a very large tablespace wherein the very large tablespace is evenly spread out over the selected nodes.

21. The program of claim 16 wherein the EASY DESIGN phase includes allocating the logical volumes in accordance with a logical volume allocation (LVM) policy.

22. The program of claim 21 wherein the LVM allocation policy allocates the logical volume across a designated set of disks.

23. The program of claim 21 wherein the LVM allocation policy allocates the logical volume across all available disks in a node.

24. The program of claim 21 wherein the LVM allocation policy allocates the logical volume on one disk.

25. The program of claim 16 further comprising designating whether the tablespaces are to be spread among the nodes in either a round robin fashion or a range partition fashion.

26. The program of claim 16 further comprising designating whether each tablespace is to be a read based or a write based tablespace, and wherein said DETAIL DESIGN phase assign distributed lock manager locks for each tablespace designated as a write based tablespace.

27. The program of claim 16 further comprising designating a size for the initial and next extents of a desired tablespace, and further comprises spreading the desired tablespace across disks dependent on the designated initial and next extent sizes.

28. The program of claim 16 wherein said distributed processor system has layers of software for operating said distributed processor system, said layers including an application layer for implementing a database, a parallel operating layer for operating the distributed processor system in a parallel fashion, and a node operating system layer for operating each node, said DETAIL DESIGN phase for generating scripts for the node operating system layer, the parallel operating layer, and the application layer, respectively.

30. The program of claim 16 wherein said EASY DESIGN phase creates a disk report including disk capacity, disk space allocated and disk space available for every node in said first datafile.

29. A program recorded on a computer readable medium, said program for implementing a physical database layout for use with a distributed processor system by a method comprising the steps of:

inputting a first datafile representing the physical configuration of the distributed processor system;

inputting a second datafile representing tablespaces to be stored in the distributed processor, said second datafile including a name and size of each tablespace in said second datafile;

determining from said first datafile the number of nodes available in the distributed processor system, the number of disks per available node, and the size of each of the disks in the distributed processor system;

performing a PROGRAM DESIGN phase for automatically allocating the tablespaces to the nodes and disks in the nodes determined to be available and updating the second datafile with the allocations;

providing for the modification of the allocation of the tablespaces in said second datafile by a user;

performing an EASY DESIGN phase for automatically assigning each tablespace into at least one logical volume in disk space of the disks in the distributed processor system, and generating therefrom a logical volume map including an indication of the node, disk, volume group and size of the tablespaces thus assigned;

providing for the modification of the logical volume map by a user; and

performing a DETAIL DESIGN phase for automatically generating scripts for implementing the physical database layout from said logical volume map; and

wherein said distributed processor system has layers of software for operating said distributed processor system, said layers including an application layer for implementing a database, a parallel operating layer for operating the distributed processor system in a parallel fashion, and a node operating system layer for operating each node, said DETAIL DESIGN phase for generating scripts for the node operating system layer, the parallel operating layer, and the application layer, respectively, and wherein said application layer is an Oracle layer, said parallel operating layer is a PSSP and VSD layer, and said node operating system layer is an AIX layer, and said DETAIL DESIGN layer generates AIX command scripts, VSD scripts, and Oracle scripts, in that order.