Using DB2 on DataStar

Getting an account

To access database on DataStar, users need to have their DB2 accounts setup. DB2 users accounts are the same as their DataStar login accounts if they are granted login permissions.

Accessing a DB2 database interactively

You can access DB2 to perform interactive SQL queries either using the client installed on DataStar, or via a client that you need to install locally. Either way, you will need to know which instance and database you want to connect, as well as the username/password to access the data source.

The main reasons to use a database interactively is to quickly get small pieces of information from it, or to test/prototype SQL queries to be included in a program or web interface. Be careful when you construct your queries, in particular if you are going to perform complex joins across tables.

Using the DB2 client on DataStar

To use the DB2 client on DataStar, users need to have regular login as well as DB2 accounts.

Log on the system using SSH protocol version 2 and using compression:
% ssh -2CX user@dsxxx.sdsc.edu (if you are using OpenSSH)
or
% ssh2 -CX user@dsxxx.sdsc.edu (if using the commercial version of ssh)
and type your login password after the prompt.
If you do not need X11 forwarding, then you can safely omit the X option in the command above.

Note: Some versions of SSH require you to use instead:
% ssh -CX -l user dsxxx.sdsc.edu
You need to set up the database instance environment. To do this, run the start-up script according to your login shell as follows:
$ . INSTHOME/sqllib/db2profile (for sh, ksh, or bash)
or
% source INSTHOME/sqllib/db2cshrc (for csh, or tcsh)
where INSTHOME is the home directory of the instance.
Now you are ready to use the DB2 Command Line Processor(CLP) to connect to your database of choice, and execute SQL statements. You should not use the DB2 GUI client in the login nodes, this client should be run from a local installation (see below).
The db2 command starts the CLP, and there are three ways to execute commands:
- Interactive input mode. Type:
  % db2
  you will see some version and general help information, and will see the following prompt:
  db2=>
  where you can type SQL statements or database commands
- Command mode. In this mode, you must prefix each command with db2. You also need to use quotation marks if there are special characters inside the command line. For example, you need to type:
  % db2 "SELECT * FROM employee"
  because of the * character.
- Batch mode. This mode is very useful if you want to execute several commands in a particular sequence. You will put the commands to be executed in a file and then type:
  % db2 -f filename

Moving data to and from a DB2 database

DB2 UDB uses export utility to take data from the database and create flat files, and three methods to get data into DB2 UDB, insert, import and load. To use these utilities, you must have the priviledges required by the utitilies.

Examples:

Export data from table student to myfile and log error and warning messages in file msg.

% db2 "export to myfile of del messages msg select * from student"

Import data from file myfile and insert into table student.

% db2 import from myfile of del insert into student

where file myfile is a comma-separated file in which non-numeric columns are quoted as follows:

001,"David Land","Computer Science"
002,"Sara Smith","Mathematics"
...

Very fast way to delete all rows in a table:

% rm emptyfile % touch emptyfile
% db2 import from emptyfile of del replace into student

where emptyfile must be an empty file.