art 1. Implementing Your Design ght IBM Corp 1993-2002
Part 1. Implementing Your Design © Copyright IBM Corp. 1993 - 2002 1
Chapter 1. Before Creating a database After determining the design of your database, you must create the database and the objects within it. These objects include schemas, database partition groups, table spaces, tables, views, wrappers, servers, nicknames, type mappings, function mappings, aliases, user-defined types(UDTs), user-defined functions(UDFS), automatic summary tables(ASTs), triggers, constraints, indexes, and packages. You can create these objects using SQL statements in the command line processor and through SQL statements in applications For information on SQL statements, refer to the SQL Reference manual. For information on command line processor commands, refer to the Command Reference manual. For information on application programming interfaces (APIs), refer to the Administrative API Reference manual Another way you can create database objects is through the Control Center The Control Center can be used instead of the SQL statements, command line processor commands, or APIs In this chapter the method for completing tasks using the Control Center is highlighted by placing it within a box. This is followed immediately by a comparable method using the command line, sometimes with examples. I some cases, there may be tasks showing only one method. When working with the Control Center, recall that you can use the help there to provide more detail than the overview information found here This chapter focuses on the information you should know before you create a database with all of its objects. There are several prerequ topics as well as several tasks you must perform before creating a database The chapter following this one contains brief discussions of the various object that may be part of the implementation of your database design. The final chapter in this part presents topics you must consider before yo alter a database and then explains how to alter or drop database objects For those areas where DB2 Universal Database interacts with the operating system, some of the topics in this and the following chapters may present operating system-specific differences. You may be able to take advantage of native operating system capabilities or differences beyond those offered by DB2 UDB. Refer to your Quick Beginnings manual and operating system documentation for precise differences. right IBM Corp. 1993-2002 3
Chapter 1. Before Creating a Database After determining the design of your database, you must create the database and the objects within it. These objects include schemas, database partition groups, table spaces, tables, views, wrappers, servers, nicknames, type mappings, function mappings, aliases, user-defined types (UDTs), user-defined functions (UDFs), automatic summary tables (ASTs), triggers, constraints, indexes, and packages. You can create these objects using SQL statements in the command line processor and through SQL statements in applications. For information on SQL statements, refer to the SQL Reference manual. For information on command line processor commands, refer to the Command Reference manual. For information on application programming interfaces (APIs), refer to the Administrative API Reference manual. Another way you can create database objects is through the Control Center. The Control Center can be used instead of the SQL statements, command line processor commands, or APIs. In this chapter the method for completing tasks using the Control Center is highlighted by placing it within a box. This is followed immediately by a comparable method using the command line, sometimes with examples. In some cases, there may be tasks showing only one method. When working with the Control Center, recall that you can use the help there to provide more detail than the overview information found here. This chapter focuses on the information you should know before you create a database with all of its objects. There are several prerequisite concepts and topics as well as several tasks you must perform before creating a database. The chapter following this one contains brief discussions of the various objects that may be part of the implementation of your database design. The final chapter in this part presents topics you must consider before you alter a database and then explains how to alter or drop database objects. For those areas where DB2 Universal Database interacts with the operating system, some of the topics in this and the following chapters may present operating system-specific differences. You may be able to take advantage of native operating system capabilities or differences beyond those offered by DB2 UDB. Refer to your Quick Beginnings manual and operating system documentation for precise differences. © Copyright IBM Corp. 1993 - 2002 3
As an example, Windows supports an application type known as a"service DB2 for Windows will have each DB2 instance defined as a service. a service can be started automatically at system boot, by a user through the Services control panel applet, or by a Windows 32-bit application that uses the service functions included in the Microsoft Windows 32-bit application programming interface(API). Services can execute even when no user is logged on to the system Unless specifically mentioned, references to Windows 9x will refer to Windows 98, and Windows ME. References to windows nt will refer to Windows NT, Windows 2000, Windows XP, and Windows. NET. References to Windows will mean all supported Windows operating systems Prerequisites for Creating a Database Before you implement a database, you should understand the following Starting DB2 UDB on UNIX Starting DB2 UDB on Windows"on page 5 Multiple Instances of the Database Manager"on page 6 Grouping objects by schema"on page 8 Parallelism"on page 9 Enabling data partitioning in a database"on page 13 Stopping a DB2 instance on UNIX on page 15 Starting DB2 UDB on UNIX You may need to start or stop DB2 during normal business operations; for example, you must start an instance before you can perform the following Connect to a database on the instance Precompile an application Bind a package to a database Access host databases Prerequisites To start a DB2 instance on your system 1. Log in with a user ID or name that has SYSADM, SYSCTRL, or SYSMAINT authority on the instance; or log in as the instance owner. 2. Run the startup script as follows: INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell) 4 Administration Guide: Implementation
As an example, Windows supports an application type known as a “service”. DB2 for Windows will have each DB2 instance defined as a service. A service can be started automatically at system boot, by a user through the Services control panel applet, or by a Windows 32-bit application that uses the service functions included in the Microsoft Windows 32-bit application programming interface (API). Services can execute even when no user is logged on to the system. Unless specifically mentioned, references to Windows 9x will refer to Windows 98, and Windows ME. References to Windows NT will refer to Windows NT, Windows 2000, Windows XP, and Windows .NET. References to Windows will mean all supported Windows operating systems. Prerequisites for Creating a Database Before you implement a database, you should understand the following prerequisite tasks: v “Starting DB2 UDB on UNIX” v “Starting DB2 UDB on Windows” on page 5 v “Multiple Instances of the Database Manager” on page 6 v “Grouping objects by schema” on page 8 v “Parallelism” on page 9 v “Enabling data partitioning in a database” on page 13 v “Stopping a DB2 instance on UNIX” on page 15 Starting DB2 UDB on UNIX You may need to start or stop DB2 during normal business operations; for example, you must start an instance before you can perform the following tasks: v Connect to a database on the instance v Precompile an application v Bind a package to a database v Access host databases. Prerequisites: To start a DB2 instance on your system: 1. Log in with a user ID or name that has SYSADM, SYSCTRL, or SYSMAINT authority on the instance; or log in as the instance owner. 2. Run the startup script as follows: . INSTHOME/sqllib/db2profile (for Bourne or Korn shell) source INSTHOME/sqllib/db2cshrc (for C shell) 4 Administration Guide: Implementation
where INSTHOME is the home directory of the instance you want to use Use one of these two methods to start the instance 1. To start the instance using the Control Center: Expand the object tree until you see the Instances folder b. Right-click the instance that you want to start, and select start from the pop-up 2. To start the instance using the command line, enter: db2start Related tasks: Stopping a DB2 instance on UNIX on page 15 tting the current instance" on page 28 Starting DB2 UDB on Windows"on page 5 Starting DB2 UDB on Windows You may need to start or stop DB2 during normal business operations; for example, you must start an instance before you can perform the following tasks. Connect to a database on the instance Bind a package to a database Access host databases Prerequisites: In order to successfully launch DB2 as a service from db2start, the user account must have the correct privilege as defined by the Windows nT operating system to start a Windows service. The user account can be a member of the Administrators, Server Operators, or Power Users group Procedure: se one of these two methods to start the instance Chapter 1. Before Creating a Database 5
where INSTHOME is the home directory of the instance you want to use. Procedure: Use one of these two methods to start the instance: 1. To start the instance using the Control Center: a. Expand the object tree until you see the Instances folder. b. Right-click the instance that you want to start, and select start from the pop-up menu. 2. To start the instance using the command line, enter: db2start Related tasks: v “Stopping a DB2 instance on UNIX” on page 15 v “Setting the current instance” on page 28 v “Starting DB2 UDB on Windows” on page 5 Starting DB2 UDB on Windows You may need to start or stop DB2 during normal business operations; for example, you must start an instance before you can perform the following tasks: v Connect to a database on the instance v Precompile an application v Bind a package to a database v Access host databases. Prerequisites: In order to successfully launch DB2 as a service from db2start, the user account must have the correct privilege as defined by the Windows NT operating system to start a Windows service. The user account can be a member of the Administrators, Server Operators, or Power Users group. Procedure: Use one of these two methods to start the instance: Chapter 1. Before Creating a Database 5
1. To start the instance using the Control Center a. Expand the object tree until you see the Instances folder. b. Right-click the instance that you want to start, and select start from the pop-up 2. To start the instance using the command line, enter db2start The db2start command will launch db2 as a windows service DB2 on Windows can still be run as a process by specifying the"/D" switch when invoking db2start. DB2 can also be started as a service using the Control NET START CO ommand When running in a partitioned database environment, each database partition server is started as a windows service. You can not use the"/D" switch to start DB2 as a process in a partitioned database environment Related tasks Starting DB2 UDB on UNIX on page 4 Stopping a DB2 instance on UNIX on page 15 Stopping a DB2 instance on Windows"on page 16 Multiple Instances of the Database Manager Multiple instances of the database manager may be created on a single server This means that you can create several instances of the same product on a physical machine, and have them running concurrently. This provides flexibility in setting up environments You may wish to have multiple instances to create the following environments. Separate your development environment from your production environment eparately tune each for the specific applications it will service. Protect sensitive information from administrators. For example, you may wish to have your payroll database protected on its own instance so that owners of other instances will not be able to see payroll data. ote:(On UNIX operating systems only: To prevent environmental conflicts between two or more instances, you should ensure that each instance has its own home filesystem. Errors will be returned when the home 6 Administration Guide: Implementation
1. To start the instance using the Control Center: a. Expand the object tree until you see the Instances folder. b. Right-click the instance that you want to start, and select start from the pop-up menu. 2. To start the instance using the command line, enter: db2start The db2start command will launch DB2 as a Windows service. DB2 on Windows can still be run as a process by specifying the ″/D″ switch when invoking db2start. DB2 can also be started as a service using the Control Panel or ″NET START″ command. When running in a partitioned database environment, each database partition server is started as a Windows service. You can not use the ″/D″ switch to start DB2 as a process in a partitioned database environment. Related tasks: v “Starting DB2 UDB on UNIX” on page 4 v “Stopping a DB2 instance on UNIX” on page 15 v “Stopping a DB2 instance on Windows” on page 16 Multiple Instances of the Database Manager Multiple instances of the database manager may be created on a single server. This means that you can create several instances of the same product on a physical machine, and have them running concurrently. This provides flexibility in setting up environments. You may wish to have multiple instances to create the following environments: v Separate your development environment from your production environment. v Separately tune each for the specific applications it will service. v Protect sensitive information from administrators. For example, you may wish to have your payroll database protected on its own instance so that owners of other instances will not be able to see payroll data. Note: (On UNIX® operating systems only:) To prevent environmental conflicts between two or more instances, you should ensure that each instance has its own home filesystem. Errors will be returned when the home file system is shared. 6 Administration Guide: Implementation