Connecting to the DB2 Server

Connecting to the DB2 Server

Using ssh

In the CS Lab the secure shell program is called ssh. To connect to a client where DB2 client is running execute

  ssh -l userID@shell01.cs.newpaltz.edu
  
or
  ssh -l userID@resneck???.acsl.newpaltz.edu
  
Coupled with this software there is a nice user interface to connect to the AVLN_LIB database. It is called squirrel-sql. Do a simple attachment do the database from the command line (as below) and then run squirrel-sql.


Connecting to DB2:

Your DB2 userID is "n1234567",which is one digit shy of your normal BannerID. Your password is "s123456", and this uses the last 6 digits of your ssn or smmddyy if you have no social securlty number.

The basic command to connect to DB2 is db2. When you "connect" to DB2 you are actually connecting to a database controlled by the DB2 software. Initially you will always connect to the LIBRARY database. On the machines you are working from the alias of the LIBRARY database is AVLN_LIB. There are a couple of ways to connect to DB2:


  • From the Command Line: In this case you first connect and then execute commands.
      $ db2 "connect to AVLN_LIB user UserID"
      $ db2 "select * from DB2INST1.CARDHOLDER"
      
    You can see the immediate problem with this. Who wants to call CARDHOLDER, DB2INST1.CARDHOLDER? To over come this problem you should specify the schema in which you will work to be DB2INST1. To do this, at the start of each sql session, execute:
      $ db2 "connect to AVLN_LIB user UserID"
      $ db2 "set current schema = db2inst1"
      
    If you are going to get tired typing in these two lines every time you connect to the database them put both lines in a file called db2startup as follows:
    connect to AVLN_LIB user UserID using pw;
    set current schema = db2inst1;
    
    Now, in order to execute both these commands you can tell the db2 command to read the commands it is to execute from a file
    $ db2 -tf db2startup
    
    The -t option tells the db2 command that there may be several commands in the file and these are separated by a semi-colon (;). The -f option tells the db2 command to read the commands it is to execute from the file whose name follows the option list.

    If SQL statements get too long to fit on one line you can put them on several lines as long as you put a back-slash as the last character on the line being continued.

       $ db2 "select bk.* from CARDHOLDER ch, RESERVES r, BOOK bk \
       > where ch.BORROWERID = r.BORROWERID and r.ISBN = bk.ISBN \
       >       and ch.B_NAME = 'john' and B_ADDR = 'New Paltz'"
      
    The problem with entering SQL this way is that if you make a mistake in typing you may need to type the whole thing in again. To overcome this problem it is better to write out the complete query in a file and execute the entire contents of the file. So suppose you create a file called q1.sql and in it you type
       select bk.* from CARDHOLDER ch, RESERVES r, BOOK bk \
           where ch.BORROWERID = r.BORROWERID and r.ISBN = bk.ISBN \
              and ch.B_NAME = 'john' and B_ADDR = 'New Paltz'
      
    In order to execute the SQL in the file you simply execute
       $ db2 -f q1.sql
      
    Notice this time that there is no -t option. An alternative to the above is to prepare the file q1.sql as:
       select bk.* from CARDHOLDER ch, RESERVES r, BOOK bk 
           where ch.BORROWERID = r.BORROWERID and r.ISBN = bk.ISBN 
              and ch.B_NAME = 'john' and B_ADDR = 'New Paltz';
      
    and execute the SQL in the file by simply executing
       $ db2 -tf q1.sql
      

    You can learn more about db2 command line options by typing

       $ db2 ? OPTIONS
      
    Study these options to see how to save your output to a file among other things.
    
    
    
  • From a db2 prompt: In this case you will simply execute
       $ db2
       db2 =>
     
    or
       $ db2  -t
       db2 =>
     
    and this gives you a db2 prompt. Before executing any SQL be sure to connect to the AVLNM_LIB database.
       db2 => connect to AVLN_LIB user UserID using pw;
       db2 => set current schema = db2inst1;
     
    Now you can enter SELECT statements just like before but you don't need the beginning and ending quotes ("..."). The primary problem with this mode of connecting to the database is that you must retype an entire query each time you wish to edit or execute it.

A final topic is that you can have your own copy of the library database in your own default 
schema , whose name is the same as your userID.
loaduserdb.pl avln_lib userID password
if run from the directory with all the .ddl and .db files will recreate a copy of the library database in your own schema. With this copy you can also insert, delete and update tables. This is something you can not do with the tables in the db2inst1 schema. After executing this script you no longer need to change schema.

pletcha@newpaltz.edu