Configuring and testing DB2 UDB 7.2

This article describes how to do the following:

  • Create a DB2 instance named db2inst1 and an administration server named db2as.
  • Configure the database manager to use shared memory.
  • Verify installation of DB2.
  • Verify connection to the database.
  • Configure WebSphere Application Server when dropping and reinstalling the was40 administrative database.

These instructions assume that DB2 is installed in the default location (/usr/lpp/db2_07_02) and that the required FixPak is installed.

Creating a database instance and administration server

Perform the following steps to create a DB2 instance, and the resources it requires:

  1. Ensure that you are logged into the machine with superuser (root) privileges.
  2. Navigate to the directory containing the DB2 setup utility.
  3. Start the DB2 Setup Utility by entering the following command:
    # ./db2setup
    
  4. Highlight the Create button beside the option labeled To create a DB2 Instance, an Administration Server, or a Data Links Manager Administrator select Create and press Return.
  5. On the Create DB2 Services dialog, highlight the Create a DB2 Instance option and press Return.
  6. On the DB2 Instance dialog, perform the following steps, noting the values that you enter or accept for future reference:
    1. Accept the default value for the User Name option, db2inst1.
    2. Enter a user ID or accept the default user ID by ensuring that the Use default UID option has an asterisk (*) beside it.
    3. Accept the default value for the Group Name option, db2adm.
    4. Enter a group ID or accept the default group ID by ensuring that the Use default GID option has an asterisk (*) beside it.
    5. Accept the default value for the Home Directory option, /home/db2inst1.
    6. Type a password for the user in the Password and Verify Password options. DB2 requires a password of eight or fewer characters.
    7. Highlight OK and press Return.
  7. On the Fenced User dialog, perform the following steps, noting the values that you enter or accept for future reference:
    1. Accept the default value for the User Name option, db2fenc1.
    2. Enter a user ID or accept the default user ID by ensuring that the Use default UID option has an asterisk (*) beside it.
    3. Accept the default value for the Group Name option, db2fadm1.
    4. Enter a group ID or accept the default group ID by ensuring that the Use default GID option has an asterisk (*) beside it.
    5. Enter a home directory or accept the default value for the Home Directory option, /home/db2fenc1.
    6. Type a password for the user in the Password and Verify Password options. DB2 requires a password of eight or fewer characters.
    7. Highlight OK and press Return.
  8. On the DB2 Warehouse Control Database dialog, highlight the option labeled Do not set up DB2 Warehouse Control Database and press Return.
  9. Highlight OK and press Return.
  10. On the Create DB2 Services dialog, highlight the Create the Administration Server option and press Return.
  11. On the Administration Server dialog, perform the following steps, noting the values that you enter or accept for future reference:
    1. Accept the default value for the User Name option, db2as.
    2. Enter a user ID or accept the default user ID by ensuring that the Use default UID option has an asterisk (*) beside it.
    3. Accept the default value for the Group Name option, db2asgrp.
    4. Enter a group ID or accept the default group ID by ensuring that the Use default GID option has an asterisk (*) beside it.
    5. Enter a home directory or accept the default value for the Home Directory option, /home/db2as.
    6. Type a password for the user in the Password and Verify Password options. DB2 requires a password of eight or fewer characters.
    7. Highlight OK and press Return.
  12. A Notice dialog informs you of the value being created for the DB2SYSTEM environment variable. Ensure that OK is highlighted and press Return.
  13. On the Create DB2 Services dialog, highlight OK and press Return.
  14. The Summary Report dialog box opens. Verify the information on the Summary Report dialog and when you have determined that it is correct, ensure that Continue is highlighted and press Return.
  15. A Warning dialog box opens, giving you the option of canceling the configuration processes. Ensure that OK is highlighted and press Return.
  16. A Notice dialog informs you when the processes have completed successfully. Ensure that OK is highlighted and press Return.
  17. The Status Report dialog informs you of process successes and failures. View the Log File for information about how to correct particular failures. Ensure that OK is highlighted and press Return to exit from the Status Report dialog.
  18. On the DB2 Setup Utility dialog, highlight Close and press Return.
  19. On the Notice dialog, ensure that OK is highlighted and press Return.
  20. Ensure that the user named root is a member of the administration server group named db2asgrp by performing the following steps:
    1. Invoke SMIT to change characteristics of a user by entering the following command:
      # smitty chuser
      The Change/Show Characteristics of a User dialog box opens.
    2. In the User NAME field, type root and press Return.
    3. In the ADMINISTRATIVE GROUPS field, ensure that the group db2asgrp is listed. If it is not, append it to the list of groups, and press Return.
    4. When the process is complete, exit from SMIT.
  21. Create symbolic links from the home directory of the instance owner to the DB2 installation directory by executing the db2ln script, as follows:
    # /usr/lpp/db2_07_02/cfg/db2ln
  22. Configure the instance owner db2inst1 to run the db2profile or db2cshrc script at login:
    • For the Korn shell (ksh), add the following text to the .profile file of the instance owner db2inst1. Note the space between the period (.) and the first slash (/).
      . /home/db2inst1/sqllib/db2profile
      
    • For the C shell (csh), add the following line to the .cshrc file of instance owner db2inst1:
      source /home/db2inst1/sqllib/db2cshrc

  23. Configure the user root to run the db2profile or db2cshrc at login by editing the .profile or .cshrc file, depending on the user root's login shell. This is required to install and run WebSphere Application Server.
  24. Log out then log back in for your changes to take effect.

Configuring the database manager to use shared memory

Before starting DB2 on AIX you must configure the database manager to use extended shared memory, as follows:

  1. Log in as the DB2 instance owner, db2inst1, by using the su command, as follows:
    # su - db2inst1

    When you log in as db2inst1, the command prompt changes from the # symbol to a dollar sign ($) to indicate a change in your login identity.

  2. If this is the first time that you have logged in as the DB2 instance owner, you could be prompted to change the password. Enter a new password and press Return. DB2 requires a password of eight or fewer characters.
  3. When prompted, type the new password again and press Return.
  4. Set the EXTSHM environment variable by entering the following commands:
    $ EXTSHM=ON
    $ export EXTSHM
    $ db2set DB2ENVLIST=EXTSHM

    Later, when the administrative server is stopped or the system is shut down, DB2 will stop as well.

    When you back up the system and start DB2, the administrative server might fail with the following error when you try to start it:

    Could not initialize persistent storage for serious events.
    Got exception COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL1224N
    A database agent could not be started to service a request, or was terminated as a result of
    a database system shutdown or a force command. SQLSTATE=55032
    To recover from this problem, enter the above commands that set the EXTSHM environment variable and restart the server.

    Note: Ensure that the EXTSHM environment variable is set each time you start DB2. To prevent the environment variable from being turned off accidentally again, add the above three lines of commands to the db2profile file (assuming the db2profile file is sourced using .profile) to ensure the variable is always valid.

Verifying installation of DB2 UDB

To demonstrate that DB2 is functioning correctly, you will create a sample database and compile and execute a Java application that accesses it. The steps below establish that the environment is set up correctly for DB2 and the IBM Java 2 SDK, and that the JDBC driver is accessible from a Java application.

Perform the following steps to create the sample database and compile and run the Java application:

  1. Ensure that you are logged in as the DB2 instance owner, db2inst1.
  2. Ensure that the DB2 environment has been set up correctly by using the echo command to verify the value of the DB2INSTANCE environment variable, as follows:
    $ echo $DB2INSTANCE

    The correct value returned is db2inst1.

  3. Ensure that the home directory of the instance owner, /home/db2inst1 has write permissions.
  4. Create the sample database by executing the db2sampl script, as follows:
    $ db2sampl

    This process can take several minutes to complete.

  5. Ensure that you are in the instance owner's home directory, /home/db2inst1.
  6. Compile an example Java application by using the javac command, as follows:
    $ javac -d . sqllib/samples/java/DB2Appl.java

    The resulting class file is created in the local directory.

  7. Start DB2 by using the db2start command, as follows:
    $ db2start
    
  8. Run the Java sample by using the java command, as follows:
    $ java DB2Appl

    Correct output resembles the following:

    Retrieve some data from the database...
    Received results:
    empno= 000010 firstname= CHRISTINE
    empno= 000020 firstname= MICHAEL
    empno= 000030 firstname= SALLY
    . . .
    Update the database...
    Changed 1 row.

Creating a database for WebSphere Application Server

Perform the following steps to create a database named was40 and set the DB2 application heap size:

  1. Ensure that you are logged in as the DB2 instance owner, db2inst1.
  2. Ensure that DB2 is running.
  3. Create a database named was40 by using the db2 create database command, as follows:
    $ db2 create database was40

    This process can take several minutes to complete.

  4. Set the application heap size by using the db2 update db config command, as follows:
    $ db2 update db config for WAS40 using applheapsz 256
  5. Stop and start the database for your changes to take effect. If an application heap size of 256 does not work for your system, increase it to 512.

Verifying the connection to the database

Perform the following steps to verify a connection to database named was40:

  1. Ensure that you are logged in as the DB2 instance owner, db2inst1.
  2. Connect to the database named was40 by using the db2 connect command, as follows:
    $ db2 connect to was40

    Correct output resembles the following:

    Database Connection Information
    
    Database server        = DB2/6000 7.2.1
    SQL authorization ID   = DB2INST1
    Local database alias   = WAS40
  3. To disconnect from a database and log out as the DB2 instance owner, type exit at the command prompt.

Configuring WebSphere Application Server when dropping and reinstalling the was40 administrative database

If you drop and recreate the was40 database after you have installed and successfully started the WebSphere Application Server administrative server for the first time, you must reset the values of the com.ibm.ejs.sm.adminServer.createTables flag and the install.initial.config flag. These flags are found in the WebSphere Application Server admin.config file, which is located by default in the /usr/WebSphere/AppServer/bin directory.

You must reset the values of these flags because the WebSphere Application Server product automatically changes their values from true to false when the administration server is started successfully for the first time. The product changes the values of these flags so that the creation of the database tables and installation of the Default Server and sample applications are not repeated with subsequent starts of the administration server.

Perform the following steps to drop, recreate, and set the application heap size for the was40 database and to change the values for the com.ibm.ejs.sm.adminServer.createTables and install.initial.config flags:

  1. Log in as the DB2 instance owner. Logging in as the instance owner places you automatically in the home directory of the instance owner. When you log in as the instance owner, the command prompt changes from the # symbol to a dollar sign ($) to indicate a change in your login identity.
  2. Ensure that DB2 is running or start it by entering the following command:
    $ db2start
  3. Drop, recreate, and set the application heap size for the was40 database by entering the following commands:
    $ db2 drop database was40
    $ db2 create database was40
    $ db2 update db config for WAS40 using applheapsz 256
  4. In order for your changes to take effect, you must start and stop DB2. To do this, enter the following commands:
    $ db2stop
    $ db2start

    If an application heap size of 256 does not work for your system, increase the size to 512.

  5. Log out as the DB2 instance owner by entering the following command:
    $ exit

    The DB2 server remains active unless you stop it by using the db2stop command.

  6. As user root, open the admin.config file in a text editor.
  7. Change the value for the com.ibm.ejs.sm.adminServer.createTables flag from false to true.
  8. Change the value for the install.initial.config flag from false to true.
  9. Save the edited admin.config file.