CSE532 Project Hints Page

Content index:

• How to connect to DB2 through command line?
• How to connect to DB2 through JDBC?
• DB2 experiences
• How to implement UDFs on the DB2 server?
• How to create a webpage using your www account?
• How to write a Java CGI program?
• Can I work from home? (and other FAQs)

How to connect to DB2 through command line?

1. Your DB2 userid and a password will be supplied to you.

2. Using the unix host (sbpub1~sbpub7), you can connect through the DB2 command line.
   For students using PCs in room 1239, go to Step 13 below.

3. First source the db2 cshrc file to set up the environment. This is very important as all your paths as defined at this stage. If you skip this then db2 will not run. The command to source the cshrc file is
   $> source ~db2inst1/db2cshrc
   to make sure type:
   $> which db2
   the correct output is
   $> /home/users/db2inst1/sqllib/bin/db2
   Tip: To avoid executing this step each time you login, put command source ~db2inst1/db2cshrc in your file .cshrc under your home directory. This will get it executed automatically.

4. You start the db2 command line processor with the 'db2' command
   $> db2

5. You should get a 'db2=>' prompt

6. Now you are in the db2 command processor. From here you can run db2 shell commands as well as SQL commands.

7. To connect to your database:
   db2=>connect to <your_database> user <your_DB2_userid> using <your_DB2_password>
   Your database name is the same as your DB2 userid.

8. To create a table:
   db2=>create table test (num int)

9. To insert values in a table:
   db2=>insert into test values(10)

10. To view the contents of a table
    db2=> select * from test

11. You can also define UDTs (User Defined Types) and UDFs(User Defined Functions). For example, db2 => create distinct type mp3 as blob(100M)
db2 => create function mplay(mp3) returns integer external name 'MP3!play' language java parameter style db2general no sql deterministic no external action not fenced

12. To quit the db2 shell type
    db2=> quit

13. From the PCs in Room 1239 you will first log on with your department username and password, then select Start->Programs->Database->IBM DB2-> Command Line Processor. Now go back to Step 7 above.

How to connect to DB2 through JDBC?

10/8/02

Please see new instructions at http://www.translab.cs.sunysb.edu/website/db2/db2connect.html.

The old instructions below have some problems because the configurations have changed.

1. Under your home directory, copy the jdbc zip file: Get http://ug.cs.sunysb.edu/~kifer/cse532/project/db2java.zip and put it in your account at ~/cgi-bin/ on intrawww.
2. Go to cgi-bin: cd  ~/cgi-bin
3. Unzip it: unzip db2java.zip. A subdirectory called COM will be created which contains the JDBC driver for DB2.
4. Read the file http://ug.cs.sunysb.edu/~kifer/cse532/project/db2.java to understand how to connect to DB2 in Java using JDBC and read section how to write a Java CGI program.

DB2 experiences

• ROW is supported in DB2 although the syntax is a bit different, I have tried
  

  create type Address_t AS (street VARCHAR(30), number CHAR(15)) MODE DB2SQL
  

  create table student (name VARCHAR(20), add Address_t)
  

  create type USAddress_t UNDER Address_t AS(zip CHAR(10), state VARCHAR(20)) MODE DB2SQL
  

  and they work. LISTOF and SETOF are not supported.

How to implement UDFs on the DB2 server?

1. UDF class files must be defined as a package in the format below:
   package cse532.cse532XX;
   where XX is the individual DB2 account number, i.e. cse532-1 or cse53240 for example.
2. The CREATE FUNCTION declaration in the program that registers the UDF must use the following format for the EXTERNAL NAME clause:
   EXTERNAL NAME 'cse532.cse532XX.UDFclassfile!UDFfunction'
   where UDFclassfile is your UDF class file and UDF function is the name of the method that implements the function (and again XX is the individual course account number).
3. To compile and run on the NT machines in 1239 you should first run C:\Program Files\SQLLIB\bin\SETPATH.BAT. If ever there is a problem compiling they should run "which javac" and make certain they are using the version installed under the SQLLIB tree. On the Solaris machines you must run ~db2inst1\db2cshrc.
4. To upload your UDF class files onto the DB2 server, do the following:
   • ftp to sbgraddb2.cs.sunysb.edu
   • login with username cse532 password it08nc1q.
   • cd to cse532XX where XX is the individual account
   • use BIN mode
   • upload class file(s)
5. Please note, to protect your work you should upload ONLY class files and never .java files.  It is possible to that other students overwrite your files but if you always keep a local copy of your most recent UDF class file then you would be able to re-upload it at any time and not be held up in your work. Or a better idea is to choose a file name which other people will not choose, for example by incorporating your account name into it.
6. To see a working example of the above do the following:
   • copy UDFsrv.java and UDFcli.java from the sqllib tree to somewhere in your home directory. The sqllib tree is located in /home/users/db2inst1/sqllib/samples/java on the Solaris machines.
   • make the package modifications detailed in 1 and 2 above.
   • recompile as in 3 above.
   • ftp as in 4 above the resulting UDFsrv.class and People.class files.
   • run "java UDFcli DB2username DB2password" from the command line, where DB2username is your DB2 username and so forth.

How to create a webpage?

1. You will do your web work on intrawww.cs.sunysb.edu, which is INTERNAL to the CS dept network. Unlike last semester, You don't have another www account. All work should be done in your department account or CSE532 course account (for non-CS students).
2. Login into one of the sbpub machine, say sbpub7 using your department account.
3. Under the home directory (make sure you chmod your home directory to 755) make a directory called public_html, chmod it to 755 so that it is accessible from the web.
4. Under the public_html, create index.html, also chmod it to 755.
5. Point your browser to http://intrawww.cs.sunysb.edu/~yourlogin/index.html and you will see your homepage. (Make sure you type "~" before your login name.)
   For example,
   
   sbpub7.1 pwd
   /home/stufs1/yourlogin
   sbpub7.2 mkdir public_html
   sbpub7.3 chmod 755 public_html
   sbpub7.4 cd public_html
   sbpub7.5 cat > index.html
   Welcome to Stony Brook (HTML).
   sbpub7.6 cat index.html
   Welcome to Stony Brook.
   sbpub7.7 chmod 755 index.html

How to write a Java CGI program?

1. Login into one of the sbpub machines, say sbpub7 using your department account.
2. Under the HOME directory (not under public_html) make a directory called cgi-bin, chmod it to 755 so that it is accessible from the web.
3. Under cgi-bin, create test.cgi, also chmod it to 755, it is a wrapper for test.class. This sample file is available at http://ug.cs.sunysb.edu/~kifer/cse532/project/test.cgi-sample
4. Under cgi-bin, create test.java and compile it.  DO NOT PUT ANY SOURCE CODE HERE FOR SAFETY PURPOSE: just copy the class files here. This sample file is available at http://ug.cs.sunysb.edu/~kifer/cse532/project/test.java
5. Point your browser to http://intrawww.cs.sunysb.edu/yourlogin-cgi-bin/test.cgi and you will see your result.

Frequently asked questions

1. Can I work from home?
   With restrictions. For security reasons,  you can not access intrawww.cs.sunysb.edu from a browser outside the CS network. You can open an X window on one of the sbpub.cs machines and work this way, provided you have the appropriate software on your home machine.
2. I have problem with your examples. I point the browser to http://intrawww.cs.sunysb.edu/~mylogin/index.html, however seems to me it is not accessible. Can you help me out?
   You need to access it from the public lab.
3. I don't like this intrawww thing, can I use other web server with cgi support and point it to you during demonstration?
   No, you cannot since intrawww is setup with UDF support.