CSE532  Project Hints Page

Last updated on October 14 2003

Content index:

Announcements:

How to connect to DB2 through command line?

The system administrator keeps the most up-to-date information on this subject in this place. Here is a summary.

  1. Remember your database name.
  2. Your DB2 login will be the same as your database name.
  3. A six digit password will be supplied to you.
  4. Using the unix host (sbbub0~sbpub9, for students using PCs in room 1239, go to step 15 below) you can connect through the DB2 command line.
  5. 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.

  6. You start the db2 command line processor with the 'db2' command
    $> db2
  7. You should get a 'db2=>' prompt
  8. Now you are in the db2 command processor. From here you can run db2 shell commands as well as sql commands.
  9. To connect to your database:

    db2=>connect to <your_database> user <your_DB2_username> using <your_DB2_password>
    your database and your db2 username are the same and they are your DB2 account, e.g., cse53240.

  10. To create a table:

    db2=>create table test (num int)

  11. To insert values in a table:

    db2=>insert into test values(10)

  12. To view the contents of a table

    db2=> select * from test

  13. 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

    For more infomation regarding how to write IBM DB2's UDT and UDF in Java, check out Manuals for DB2 UDB 8.

  14. To quit the db2 shell type

    db2=> quit

  15. 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

  1. Under your home directory, copy the jdbc zip file: Get http://ug.cs.stonybrook.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://www.translab.cs.stonybrook.edu/db2/samples/db2_samples.zip to understand how to connect to DB2 in Java using JDBC.

 

DB2 experiences

How to create a webpage

  1. You will do your web work on intrawww.cs.stonybrook.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 sbpub9 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.stonybrook.edu/~yourlogin/index.html and you will see your homepage. (Make sure you type "~" before your login name.)
    For example,
           sbpub9.1 pwd
      /home/stufs1/yourlogin
      sbpub9.2 mkdir public_html
      sbpub9.3 chmod 755 public_html
      sbpub9.4 cd public_html
      sbpub9.5 cat > index.html
      Welcome to Stony Brook (HTML).
      sbpub9.6 cat index.html
      Welcome to Stony Brook.
      sbpub9.7 chmod 755 index.html

How to implement UDFs on the DB2 server

See http://www.translab.cs.stonybrook.edu/db2/db2andudf.html

Frequently asked questions.

  1. Can I work from home?
    With restrictions. For security reasons,  you can not access intrawww.cs.stonybrook.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.stonybrook.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.
  4. Information on how to connect to DB/2 from home is here

 

QUIP -- An XQuery implementation from Software AG:

  • Quip is installed in the Graduate NT Lab. You can start Quip by using the shortcut in the Programs item of the Start menu.

    All Quip logfiles are written to "C:\Temp\%USERNAME%".

  • You can also download a copy of Quip from the Software AG site: http://www.softwareag.com/developer/quip/
  • After the installation, double click 'QuipGui' and run the application.
  • Data to be used by the queries will be stored in your files. You must set Quip to use the file system: Make sure that the radio button 'Filesystem' is selected (default).
  • To run the examples that come with the system, enter 'examples' in the field 'Directory:' (this value is preselected).
    Then select the 'open' item of the file menu (or click on the 'open folder' icon) and open any of the *.xquery files in one of the subdirectories, for example: 'usecases\Tree\TREEQ6.xquery'. An XQuery query should appear.
    Press the execute button to run the query.

    • Other useful links: