DB2 Hints for CS338

• For CS338, DB2 runs on linux.student.cs.uwaterloo.ca, and only on linux.student.cs.uwaterloo.ca. DB2. It is recommended to run DB2 on linux028.student.cs.uwaterloo.ca. If you don't know how to connect to this machine or how to use the UNIX environment, talk come to office hours for the instructor or TAs. Shell commands to set up these variables can be found in the files:
• Certain UNIX environment variables must be set up in order for you to use DB2.

  linux028:~> ps p $$
  will show you which shell you are using. If you're still not sure, you can type bash or tcsh and you'll then be in that shell.

  Shell commands to set up these variables can be found in the files:

  ~cs338/public/db2profile (for users of sh or bash) and

  ~cs338/public/db2cshrc (for users of csh or tcsh).

  You should cause these commands to be executed each time you start a new shell. Users of sh or bash can do this by including the command

. ~cs338/public/db2profile

in their .profile file. Users of csh and tcsh can do this by including

source ~cs338/public/db2cshrc

in their .cshrc (or .tcshrc) file.

If you don't want to bother with the Unix settings, you can just type the appropriate command after you log in.

• There are three ways to issue SQL commands using the DB2 command line processor: interactive mode, command line mode, and batch mode. Note that in the examples that follow, input typed by you is shown in italics.
• In interactive mode, the DB2 command line processor is started, and SQL commands are issued at the DB2 command line processor's prompt (db2 =>). Here is an example:

linux028:~ - 300> db2
(c) Copyright IBM Corporation 1993,2000
Command Line Processor for DB2 SDK 7.1.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to cs338

   Database Connection Information

 Database server        = DB2/SUN 7.1.0
 SQL authorization ID   = CS338
 Local database alias   = CS338

db2 => connect reset
DB20000I  The SQL command completed successfully.
db2 => quit
DB20000I  The QUIT command completed successfully.
linux028:~ - 301>

• In command line mode, SQL commands are issued directly on your UNIX shell command line, prefixed by "db2". This has the disadvantage that you have to type "db2" at the start of every line, but it does allow you to take advantage of any command line editing and history mechanisms that are provided by your shell. Here is an example:

linux028:~ - 301> db2 connect to cs338

   Database Connection Information

 Database server        = DB2/SUN 7.1.0
 SQL authorization ID   = CS338
 Local database alias   = CS338

linux028:~ - 303> db2 connect reset
DB20000I  The SQL command completed successfully.
linux028:~ - 304>

• In batch mode, SQL commands are read by the command line processor from a file. You can create the file using your favorite text editor. To issue SQL commands, use the command line processor's -f option. For example, if do-query.txt is a text file containing SQL commands, you can execute the commands using:

linux028:~ - 304> db2 -f do-query.txt

For example:

linux028:~ - 308> more do-query.txt
connect to cs338
connect reset

linux028:~ - 309> db2 -f do-query.txt

   Database Connection Information

 Database server        = DB2/SUN 7.1.0
 SQL authorization ID   = CS338
 Local database alias   = CS338

DB20000I  The SQL command completed successfully.

linux028:~ - 310>

SQL statements must be entered on a single line, including statements like CREATE TABLE. Since this can be quite cumbersome to type, especially in an text editor like PICO, you can use the backslash character "\" as the statement continuation marker. This will let you create batch files that look reasonable. Here is an example of a long statement that uses continuation markers:

CREATE TABLE foo ( \
   col1 INT, \
   col2 VARCHAR(20) \
  )

• The DB2 command line processor accepts many command line parameters, which can be used to control its behaviour. You can read about these in the DB2 Online Reference. You may find

linux028:~ - 310> db2 -v -f input-file -z output-file

useful. The -v option causes SQL commands you type to be echoed to the output, and the -z filename option causes all of the command line processor's output to be copied into the specified file. For example:

linux028:~ - 311> db2 -v -f do-query.txt -z output.txt
connect to cs338

   Database Connection Information

 Database server        = DB2/SUN 7.1.0
 SQL authorization ID   = CS338
 Local database alias   = CS338


connect reset
DB20000I  The SQL command completed successfully.

linux028:~ - 312> more output.txt
connect to cs338

   Database Connection Information

 Database server        = DB2/SUN 7.1.0
 SQL authorization ID   = CS338
 Local database alias   = CS338

connect reset
DB20000I  The SQL command completed successfully.

linux028:~ - 313>

It is also possible to set options (such as command echoing) while the command line processor is running. You can do this using the UPDATE COMMAND OPTIONS command, e.g.,

db2 => update command options using v on
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

db2 =>

• You must connect to a database before you can issue SQL commands. The name of the database used for CS338 is "cs338". Use the connect command to connect to a database. For example:

db2 => connect to cs338

should connect you to the CS338 database. Use the connect reset command to disconnect from whichever database you are currently connected to:

db2 => connect reset

• Let's create a new table:

  db2 => create table Customer (AccNum integer not null, Cname varchar(20) not null, Prov varchar(20), Cbal decimal(6,2) not null, Climit decimal(4,0) not null, primary key (AccNum))
  

  Then we'll insert some data (create some tuples):

  
  db2 => insert into customer values (101, 'Smitherman', 'QC', 0, 1000)
  
  db2 => insert into customer values (102, 'Harper', 'ON', 1000, 1000)
  
  db2 => insert into customer values (107, 'Sookie', 'BC', -500, 20)
  
  

• In order to look at all user tables (tables you created) in the cs338 database, you would use the DB2 command:

db2 => list tables 

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
CUSTOMER                        JCHAMPAI        T     2012-09-18-17.42.37.855990
FOO                             JCHAMPAI        T     2012-09-18-17.28.07.446586

  2 record(s) selected.

db2 =>

• The list command works by querying the DB2 catalog (data dictionary), which contains tables that describe the database. Instead of using list, you can query catalog tables directly, using SQL, like this (where you replace USERID with your userid in capitals):

  
  DB2 => select tabname from syscat.tables where tabschema = 'USERID' 
  
  TABNAME                                                                                                                         
  --------------------------------------------------------------------------------------------------------------------------------
  CUSTOMER                                                                                                                        
  FOO                                                                                                                             
  
    2 record(s) selected.
  
  

  This tells you the names of all of the tables in your user schema.
   
• Another handy DB2 command is describe, which will tell you the types of all of the columns of any table. For example:

db2 => describe table foo

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1                            SYSIBM    INTEGER                      4     0 Yes   
COL2                            SYSIBM    VARCHAR                     20     0 Yes   

  2 record(s) selected.


db2 =>

• The most comprehensive sources of documentation of DB2 are the on-line manuals.   However, the DB2 command line interpreter can also provide some help directly, via the "?" command.  For example:

db2 => ? list tables
LIST TABLES [FOR {USER | ALL | SYSTEM | SCHEMA schema-name}] [SHOW DETAIL]

db2 =>

describes the allowed syntax for the list tables command.   The "?" command with no arguments will give you a list of known commands.  The "?" command can also provide you with detailed explanations of command execution errors:

db2 => select cname + 1 from customer
SQL0402N  Invalid character found in a character string argument of the 
function "DECFLOAT".  SQLSTATE=2201

 
In this example, an SQL query has failed because of a type mismatch.  The brief error message will usually identify the problem.  However, you can get more information about specific errors using "?":

db2 => ? sql0402n

 SQL0402N The data type of an operand of an arithmetic function
          or operation "" is not numeric.

Explanation:  A nonnumeric operand is specified for the
arithmetic function or operator "".

The statement cannot be processed.

User Response:  Correct the SQL statement syntax so all specified
function or operator operands are numeric.

Federated system users: if the reason is unknown, isolate the
problem to the data source failing the request (see the problem
determination guide for procedures to follow to identify the
failing data source) and examine the operators applied to that
data source.

sqlcode:  -402

sqlstate:  42819

db2 =>

Maintained by: John Champaign, based on similar material developed by Trevor Grove and Ken Salem for CS448/648.
Last Updated: 2012-09-18