IBM DB2 / UDB

Owned by Elena Sviridenko (Unlicensed)
Last updated: Oct 22, 2019 by Alexandr Kudentsov (Unlicensed)


To connect to an IBM DB2 database, it is necessary to have a DB2 instance installed on your local machine.
To use temporary tables, it is necessary to create a ‘user temporary tablespace’. The tablespace should, ideally, be placed in a bufferpool with a pagesize greater than the default of 4k.
For example,

CONNECT TO stores;
CREATE USER TEMPORARY TABLESPACE
user_temp1;

Schema Objects Ownership

By default, all database references will assume that the schema objects are owned by querix. For example, the statement:

SELECT * FROM customer

will be treated as

SELECT * FROM querix.customer

To change the assumed schema owner in the DB2 interface, you can use the environment variable DB2_OWNER_ENV. For example:

export DB2_OWNER_ENV=scott

Windows IBM DB2 Connections

Installation

These instructions relate to the database installation procedure as it may be done from a downloaded self-extracting zip file.

    1. Extract all the installation files to the default location determined by DB2.
    2. Browse the extraction directory to find and run the file Setup.exe.
    3. In the Welcome window choose the option to install a nonpartitioned version of Enterprise Server.

The prerequisites for this installation are:
• NT v4 & Service Pack 6a or higher
• Windows NET (32-bit + 64-bit)

  1. In the IBM DB2 Setup Launchpad window, click the Install Products button.
  2. In the Setup window, click the Next button.
  3. In the DB2 Setup Wizard window, Welcome screen, allow the preparation process to complete, then click the Next button.
  4. In the license agreement window, read the agreement and choose the “I accept the terms of the License Agreement” radio button, then click the Next button.
  5. In the Select Installation Type window, choose the ‘Typical’ radio button, then click the Next button.
  6. In the Warning dialog box, click the OK button.
  7. In the Select the Installation Action window, check the ‘Install DB2 Enterprise Server Edition on this computer’ checkbox and click the Next button.
  8. In the Select How This Computer Will be Used window, choose the ‘Single-partition database environment’ radio button, and click the Next button.
  9. In the Select Installation Folder window, select the drive from the Drive drop-down menu. To change the installation directory, click the Change button and browse for the correct location. Then click the Next button.
  10. In the Set User Information for the DB2 Administration Server window, type the following details:
    In the Domain field, type the name of the domain on an NT server (optional)
    In the Username field, type the name of the person using this database
    In the Password field, type a password for the user to use
    In the Confirm field, retype the same password
    Then click the Next button.
  11. In the Set up the Administration Contact List window, you are asked to create or identify a list of people to be notified in case of a database malfunction. You can either setup a new list or identify an existing list on the database server. If the list of people is to be notified by email, check the Notification SMTP Server checkbox and type the name of the SMTP server into the field. Then click the Next button.
  12. In the Warning dialog box, click the OK button.
  13. In the Configure DB2 Instances window, click the Protocols button to select from a list of connection methods, and click the Startup button, to choose whether DB2 will be started at system startup, or needs a manual startup. Then click the Next button.
  14. In the Prepare the DB2 Tools Catalog window, choose the appropriate radio button for whether or not a catalog of installed DB2 tools is created, then click the Next button.
  15. In the Specify a Contact for Health Monitor Notification window, either choose the radio button to set the Administration contact for this database instance or the ‘defer this task until installation is complete’ button. then click the Next button. If you choose to set the Administration details at this time, you will need to type a name and email address in the appropriate fields.
  16. In the Start Copying Files window, check the list of files to be installed, and click the Install button.
  17. In the Setup is Complete window, click the Finish button.

Connection Setup

  1. In the IBM DB2 First Steps window, select Create Sample Database from the menu on the left-hand side.
  2. In the Creating Selected Sample Database window, allow the database to be created (which may take over one minute) then click the OK button.
  3. Open Hydra Studio, and from the Tools menu, select Database Connections.
    The Querix Connect window is displayed
  4. Click the ODBC tab and then click the ODBC Administrator button. The ODBC Data Source Administrator window is displayed.
  5. Select the System DSN tab, and click the Add button. The Create New Data Source window is displayed.
  6. From the list displayed, select IBM DB2 ODBC, and click the Finish button. The ODBC IBM DB2 Driver – Add window is displayed.
  7. In the Datasource Name field, type the name of the database to which you want to connect.
    To use the Querix Test Connection application, type the name ‘sample’ which is the name of the sample database that was set up after installation.
  8. In the Database Alias field, select the alias of the database.
    Again, to use the sample database to test the connection, select SAMPLE.
  9. In the Description field, type some text to identify this database connection setup.
  10. Click the OK button. It is also necessary to set some environment variables correctly in order for Hydra to be able to connect to the DB2 instance.
  11. From the Windows desktop Start menu, choose Control Panel and then click the System icon. The System Properties window is displayed.
  12. Choose the Advanced tab and then click the Environment Variables button. The Environment Variables window is displayed.
  13. In the System Variables list, find the variables that relate to DB2.
    The variable BD2INSTANCE should be set to DB2, and the variable DB2TEMPDIR should be set to C:\Program Files\IBM\DB2. It should now be possible to run the test connection application successfully.

Unix/Linux IBM DB2 Connections

Assuming that you have a DB2 instance installed on your machine, all that needs to be done to set up a connection is to set two environment variables to the right values. These environment variables are DB2DIR, the path to the DB2 installation, and DB2INST, the name of the database server instance.
These two lines of code should be written in the following formats, and placed in a file location which should be specified by your system administrator.

DB2DIR=location_path_of_installation
DB2INST=database_server_instance_name

Alternatively, the variables can be set for the duration of the current terminal session by placing the command ‘export’ followed by a space, at the front of each line.

Related articles