Instances

This section provides an overview and discusses instances.

Operating System administrators (for UNIX logged in as root, for Windows logged on as Administrator) may create one or more DB2 LUW instances to support their PeopleSoft environment. If you only have one computer to house both production and development database, PeopleSoft recommends that you create at least two DB2 UDB instances, one for your development database(s) and one for production. If you have more than one computer for the PeopleSoft environment use, PeopleSoft further recommends that the production instance be created on a separate machine for performance and security reasons.

Image: DB2 LUW instance housing a collection of databases sharing the same engine and database manager parameters yet with each database having its own DB2ADM, specific database configuration, catalog tables, and log files

The following graphic shows a conceptual view of a DB2 LUW instance. Each instance is a collection of databases sharing the same DB2 UDB engine and set of configuration tuning parameters called "database manager parameters." These parameters control a variety of system resources, such as communication buffer sizes, TCP/IP service name, and memory allocations. SYSADM authority controls all databases in an instance. DB2ADM controls the resources within a particular database.

DB2 LUW instance housing a collection of databases sharing the same engine and database manager parameters yet with each database having its own DB2ADM, specific database configuration, catalog tables, and log files

Each database within an instance is, to a large extent, self-contained, having its own set of system catalog tables, configuration tuning parameters, tablespaces, and log files.

Each PeopleSoft application is installed entirely into a single DB2 LUW database. To simplify administration, it is recommended that you create all the PeopleSoft non-production databases (such as Upgrade, Demo, and Development) within one DB2 LUW instance. Setting up the production database in a separate instance by itself will provide you with greater flexibility in administration.

In DB2 LUW, SYSADM owns all databases in an instance. For this reason, to secure access to the production database, consider maintaining separate production and development instances. In this way, you can restrict SYSADM in production and be less restrictive in development.

If your site uses a single instance standard, you must restrict SYSADM authority—keeping in mind the additional burden this places on your DBA to support development and production environments.

Note: Administrators with DB2 UDB for z/OS experience should note the difference between DB2 z/OS and DB2 LUW in the way SYSADMs are created. In DB2 z/OS, an “Install SYSADM” is specified during DB2 UDB installation and other SYSADMs are granted using an SQL Grant SYSADM statement.

In DB2 LUW, the instance owner is the de facto “Install SYSADM” and other SYSADMs are created by assigning its group ID to the same primary group as the instance owner.

To create an instance, you can use the command db2icrt. Once the instance is created, you must assign different TCP/IP port numbers for the respective DB2 UDB instances.

To register the pair of TCP/IP ports, you edit the services file. Both the UNIX Server (/etc/services) and Windows (\windows\systemXX\drivers\etc\services) service files must specify the following: 

db2dudb 50000/tcp#DB2 Client Application Enabler-Dev
db2pudb 50010/tcp#DB2 Client Application Enabler-Prod

Note: The names db2dudb and db2pudb are user-defined.

Each instance requires a SERVICENAME, which points to a unique entry in the service file.

On the DB2 LUW Server, update the Database Manager SVCENAME Configuration Parameter:

db2 update dbm cfg using SVCENAME db2dudb

If you created a second instance, you’d have to update the Database Manager SVCENAME Configuration Parameter on that instance using another service name:

db2 update dbm cfg using SVCENAME db2pudb

The following are other considerations related to instances:

  • sqllib is the root of the DB2 UDB directories created for each DB2 UDB instance. Enter echo $DB2INSTANCE to find the currently attached DB2 UDB instance.

  • Use db2ilist to list all the instances configured for this machine.

  • DB2 UDB system commands begin with the prefix db2 (for example, db2start and db2stop for starting and stopping DB2 UDB) and are entered at the command window.

  • DB2 Command Line Processor (CLP) is a DB2 UDB interface for executing utilities, updating the system configuration, executing SQL, and for getting online help. CLP is the functional equivalent of SPUFI in the DB2 z/OS environment.

    • Type “db2 ?” at the command prompt for general CLP syntax.

    • Use db2 "? command” to get help on a particular CLP command.

    • If you need to execute several lengthy SQLs, you can create a text file which contains all the SQLs and invoke the db2 CLP with the –f switch, such as db2 –tvf job.txt.

  • As an alternative to the Command Line Processor to issue SQL commands, IBM provides the Control Center, a graphical interface for database administrative tasks. The Control Center is a Java application or can be run as a Java applet within a browser. To start the Control Center, enter the command db2cc at a command prompt.