DB2 Hints for CS338

• For CS338, DB2 runs on rees, and only on rees.
• Certain UNIX environment variables must be set up in order for you to use DB2. Shell commands to set up these variables can be found in the files:

  ~db2cs338/sqllib/db2profile (for users of sh or bash) and

  ~db2cs338/sqllib/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

. ~db2cs338/sqllib/db2profile

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

source ~db2cs338/sqllib/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:

rees:~ - 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 => select * from cs338.vendor where vname = 'Esso'

VNO         VNAME                CITY       VBAL
----------- -------------------- ---------- ------------
          3 Esso                 Montreal           0.00
          4 Esso                 Waterloo           2.25

  2 record(s) selected.

db2 => connect reset
DB20000I  The SQL command completed successfully.
db2 => quit
DB20000I  The QUIT command completed successfully.
rees:~ - 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:

rees:~ - 301> db2 connect to cs338

   Database Connection Information

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

rees:~ - 302> db2 "select * from cs338.vendor where vname = 'Esso'"

VNO         VNAME                CITY       VBAL
----------- -------------------- ---------- ------------
          3 Esso                 Montreal           0.00
          4 Esso                 Waterloo           2.25

  2 record(s) selected.

rees:~ - 303> db2 connect reset
DB20000I  The SQL command completed successfully.
rees:~ - 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:

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

For example:

rees:~ - 308> more do-query.txt
connect to cs338
select * from cs338.vendor where vname = 'Esso'
connect reset

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

   Database Connection Information

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



VNO         VNAME                CITY       VBAL
----------- -------------------- ---------- ------------
          3 Esso                 Montreal           0.00
          4 Esso                 Waterloo           2.25

  2 record(s) selected.


DB20000I  The SQL command completed successfully.

rees:~ - 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 Command Reference. You may find

rees:~ - 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:

rees:~ - 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


select * from cs338.vendor where vname = 'Esso'

VNO         VNAME                CITY       VBAL
----------- -------------------- ---------- ------------
          3 Esso                 Montreal           0.00
          4 Esso                 Waterloo           2.25

  2 record(s) selected.


connect reset
DB20000I  The SQL command completed successfully.

rees:~ - 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


select * from cs338.vendor where vname = 'Esso'

VNO         VNAME                CITY       VBAL
----------- -------------------- ---------- ------------
          3 Esso                 Montreal           0.00
          4 Esso                 Waterloo           2.25

  2 record(s) selected.


connect reset
DB20000I  The SQL command completed successfully.

rees:~ - 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 sample database containing the tables from the notes and the sample database for Assignment 2. Use the connect reset command to disconnect from whichever database you are currently connected to:

db2 => connect reset

• Table names in DB2 have two parts: schemaName.tableName. The tables of the sample database are in cs338 schema. Thus, in order to look at everything in the sample database's transaction table, you would use the SQL command:

select * from cs338.transaction

If you get tired of explicitly naming the schema, you can tell DB2 which schema to assume using the set schema command, e.g.,

db2 => connect to cs338

   Database Connection Information

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

db2 => select * from transaction
SQL0204N  "TRG.TRANSACTION" is an undefined name.  SQLSTATE=42704
db2 => set schema cs338
DB20000I  The SQL command completed successfully.
db2 => select * from transaction

TNO         VNO         ACCNUM      TDATE      AMOUNT
----------- ----------- ----------- ---------- --------
       1001           2         101 01/15/1994    13.25
       1002           2         103 01/16/1994    19.00
       1003           3         101 01/15/1994    25.00
       1004           4         102 01/20/1994    16.13
       1005           4         103 01/25/1994    33.12

  5 record(s) selected.

db2 =>

• In general, you may want to know what tables exist in the database that you have connected to.   There are several ways to do this.  Probably the simplest way is to use DB2's list command, like this:

db2 => connect to cs338

   Database Connection Information

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

db2 => list tables for schema cs338

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
CUSTOMER                        CS338           T     2001-05-08-21.33.31.886865
TRANSACTION                     CS338           T     2001-05-08-21.33.32.246662
VENDOR                          CS338           T     2001-05-08-21.33.31.373345

  3 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:

db2 => select tabname from syscat.tables where tabschema = 'CS338'

TABNAME
--------------------------------------------------------------------------------
CUSTOMER
TRANSACTION
VENDOR

  3 record(s) selected.

db2 =>

This tells you the names of all of the tables in the CS338 schema, which contains the sample database. You can find descriptions of other catalog tables and views in Appendix D of the DB2 DB2 SQL Reference Manual
 
• 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 cs338.transaction

Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- -----
TNO                            SYSIBM    INTEGER                   4     0 No
VNO                            SYSIBM    INTEGER                   4     0 No
ACCNUM                         SYSIBM    INTEGER                   4     0 No
TDATE                          SYSIBM    DATE                      4     0 Yes
AMOUNT                         SYSIBM    DECIMAL                   6     2 No

  5 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 vname + 1 from cs338.vendor
SQL0402N  The data type of an operand of an arithmetic function or operation
"+" is not numeric.  SQLSTATE=42819

 
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: T. Grove, based on similar material developed by K. Salem for CS448/648.
Last Updated: 2003-5-5