University of Konstanz Databases & Information Systems Group Prof. M. H. Scholl / Jens Teubner

Information Systems Assignments Winter 2002/03

Using the DB2 Command Line Interface 1

Configuring your Environment to Work with DB2

In order to use the DB2 command line interface, you need to extend your shell’s search path and set some environment variables. The simplest way to do this is to source the script I provided you. Add this line to the file .bashrc.username in your home directory.1 source /home/db2/db2user/db2env The changes become effective after your next login or in newly opened xterms. See if everything is okay by typing in2 $ ::::: type :::: db2 db2 is /usr/IBMdb2/V7.1/bin/db2 $

2

Working With DB2

To start the DB2 command line interface, you type in $ :::: db2 (c) Copyright IBM Corporation 1993,2000 Command Line Processor for DB2 SDK 7.1.0 ... and some messages more ... db2 => You see the DB2 command prompt, db2 =>. At this command prompt, you can type in SQL queries and some DB2 specific database commands. Exit the command line interface with db2 => :::::::::: terminate DB20000I The TERMINATE command completed successfully. $ and you get your Unix shell prompt back. Unfortunately, the DB2 command prompt is not as convenient as the shell prompt you are used to. You’ll find an alternative way to work with DB2 below, be patient. 1 Instead

of username use your Unix login. If the file does not exist yet, just create it. not type in the dollar sign ($). I always use the dollar sign to symbolize your shell’s command prompt. The :::: wavy :::::::: underlined parts are always those that you have to type in; the parts printed in regular typewriter font is the answer you receive from the system. 2 Do

2.1

Connecting to the Database

After you started the command line interface (CLI), you have to connect to a database, before you can execute any SQL queries. The database for this course is called infosys, and you type in connect::: to:::::::::: infosys ::::: user::::::::::: username db2 => :::::::: Enter current password for username : (Enter your database password here) Database Connection Information Database server SQL authorization ID Local database alias

= DB2/LINUX 7.1.0 = USERNAME = INFOSYS

db2 => where username is your Unix login name. Your database password is your regular Unix password.3 As in SQL, all commands and identifiers are caseinsensitive. Before you end your work with the DB2 database, you have to disconnect from the database: db2 => ::::::::::: disconnect:::::::::: infosys DB20000I The SQL DISCONNECT command completed successfully. db2 => After that, you can exit the command line interface with terminate.

2.2 Executing SQL Queries You can simply type in any SQL queries at the DB2 command prompt as long as you are connected to the database. There are some DB2 specific things you need to know: 2.2.1

Schemata

In the lecture you saw simple SQL queries like SELECT * FROM Kurs. “Real” SQL (i. e. the lastest ANSI standard), however, provides the mechanism of ‘schemata’. A schema can be seen as a ‘namespace’ or (in Java terms) ‘package’. Different tables can exist with the same name in different schemata. Every table (in fact, every database object), that is not in your current schema, must be specified with a fully qualified name. A database object is fully qualified by the schema name and a dot in front of the object identifier, like nobody.kurs. (I put the ‘KursDB’ database into the ‘nobody’ schema.) Example: SELECT * FROM nobody.Teilnehmer WHERE Ort = ’Ulm’ 3 The

database password, however, is not automatically updated if you change your Unix password. If you want to change your database password, please contact me. Otherwise, you’ll keep your password for the remainder of the semester.

If you know you’ll be working in a specific schema for a while, you can set your current schema for this database session with db2 => ::: set:::::::::: current :::::::: schema :: =:::::::::::::: schema-name From now on, the schema schema-name will be assumed if you do not fully qualify your database objects. Example: db2 => ::: set:::::::::: current :::::::: schema :: =:::::::: nobody DB20000I The SQL command completed successfully. db2 => ::::::: select::*:::::: from:::::: kurs ::::::: where :::::: titel::: = :::::::::::::::: ’Datenbanken’ KURSNR TITEL ------ -------------------I09 Datenbanken 1 record(s) selected. db2 => 2.2.2

Your Own Schema

The current schema after logging in to the database is your personal schema that has the name of your login name. In your personal schema you also have write access to the database, you can create tables, insert and manipulate data, etc. Your personal schema, however, does not exist, as long as you haven’t explicitly created it. This is why you will get messages like SQL0204N

"USERNAME.KURS" is an undefined name.

SQLSTATE=42704

if you did not fully specify a table name (e. g. nobody.kurs). Before you can create any database objects, you have to create your schema by issuing the command db2 => ::::::: create:::::::: schema:::::::::::::::: authorization::::::::::: username DB20000I The SQL command completed successfully. db2 => From now on, you can create tables, etc. in your schema. You only have to do this step once. The schema will be created permanently in the database; you don’t have to create it for every new database session.

2.3

Statement Termination

Any (SQL) statement is executed as soon as you hit the Enter key. This is pretty convenient for DB2 commands or short SQL queries. The downside is that you can’t type in queries that span more than one line. If you do not like this behavior, invoke the db2 command with the option -t. Any command now has to be terminated by a semicolon (;). If you hit the Enter key without the semicolon, you’ll get a new line on the DB2 command prompt, like this:

db2 => ::::::: select::* db2 (cont.) => ::::: from ::::::::::::::: nobody.kurs; You may make the option -t by setting the Unix environment variable DB2OPTIONS to ‘-t’ (e. g. in your .bashrc.username file).

2.4 Getting Help All the SQL commands are listed in the DB2 SQL Reference Manual that is available on the course website. This should be your primary documentation if you have problems concerning SQL. Additionally there are several DB2 specific commands. These commands allow you to see which tables exist in the database, get index definitions or other system information. You usually won’t need these commands. If you are curious, there are two ways to get documentation: 1. The DB2 Command Reference Manual is the reference to all these commands. Most of the commands that are listed there, however, will not be available to you, as they are intended for the database administrator. Some commands can be executed from the Unix command line, others from within the DB2 command line interface. You can find the Command Reference Manual on the course website. 2. If you type in a question mark (?) within the command line interface, you’ll get a list of all DB2 commands. You get specific help for a single command by typing in ‘? command-name ’. Don’t forget the space between the question mark and the command you want to have documented.

3 Working Efficiently with DB2 Working with the command line interface db2 can be tedious; there’s no command history or completion, no syntax hilighting, etc. There are, however, some ways to make life with DB2 easier: • Work with an open editor in parallel. Edit your queries in a file that you have open in your favorite editor. To execute a query, simply use the copy/paste mechanism of your X11 system. Mark the query text in your editor and paste it into the command line interface with the middle mouse button. It is probably convenient to turn on the semicolon as a statement termination character for this (see above). • State your query on db2’s command line. The db2 utility has the ability to keep your database connection open during its invocations. As long as you haven’t terminated or disconnected, you can exit db2 and re-start it again without losing your database connection. To exit the db2 utility without closing your connection, use the command quit. If you state database commands on the shell command line when you invoke db2 these commands will be executed and db2 will quit immediately. The following session log demonstrates this feature.

$ :::: db2 :::::::::: "connect::: to:::::::::: infosys ::::: user::::::::::: teubner" Enter current password for teubner: Database Connection Information Database server SQL authorization ID Local database alias

= DB2/LINUX 7.1.0 = TEUBNER = INFOSYS

$ :::: db2 ::::::::: "select::*:::::: from:::::::::::::: nobody.kurs ::::::: where :::::::: kursnr :: = :::::::: ’G08’" KURSNR TITEL ------ -------------------G08 Grundlagen I 1 record(s) selected. $ :::: db2 (c) Copyright IBM Corporation 1993,2000 Command Line Processor for DB2 SDK 7.1.0 ... db2 => ::::::: select:::::::::: count(*):::::: from:::::::::::::: nobody.kurs 1 ----------4 1 record(s) selected. db2 => ::::: quit DB20000I The QUIT command completed successfully. $ :::: db2 ::::::::::::: "disconnect::::::::::: infosys" DB20000I The SQL DISCONNECT command completed successfully. I started db2 here several times, sometimes with a query specified on the command line, once in interactive mode. Caution! Be careful with your shell’s command line expansion! If you do not properly quote your query (as seen above), you will get strange results. (If you don’t understand anything here, read it as “Always put your SQL query into double quotes (") when you specify it on the Unix command line.”) • Use the db2batch utility. There’s a second utility from IBM, called db2batch. Intended to measure runtime statistics, you can execute a whole file with SQL queries in batch mode. In your query file, separate your queries with semicolons; comments start with two minus signs (--). See the DB2 Command Reference Manual or invoke db2batch with the option -h to see the options available for the db2batch program.