This will walk you through "compiling" an embedded SQL Java program. See the directories

• ~db21/sqllib/samples/java/

for examples and compilation instructions for Java. See below for another embedded SQL Java program (that is, a SQLJ program).

To get a better overview and rationnal of the steps to pre-compile, bind, and compile an embedded SQL program, look over the guide for embedded SQL C anc C++ programs. Here, I shall just give instructions how to do the equivalent for embedded SQL Java programs. Also, the procedure for Java is not nearly as clear as for C/C++. (Isn't it always like that with Java?!)

First things first. To bind, compile, and later run your Java APPs, you will need to log onto one of the machines sen01 through sen20 or dem01 through dem23 in the ARIEL laboratory. The machine ella will not work. I repeat, the machine ella will not work. (Read on and you will learn why.)

1. Connect.

   Unlike with the C/C++ procedure, it is not necessary to to be connected to the database during the pre-compile, bind, and compile steps. We shall specify the database directly in the commands.

   However, you will need to have "primed" your shell by running

   % source ~db21/sqllib/db2cshrc

   (See DB2 at ARIEL.) This sets all the library paths and so forth that will be needed.

2. Pre-compile.

   Prepare your SQLJ proram. It is convention to name one's embedded SQL Java programs with a ".sqlj" at the end to indicate that it is an SQL-Java hybrid program. To pre-compile, say

   % sqlj -url=jdbc:db2:database program.sqlj

   Replace database with the name of the appropriate database (for instance, c3421a00) and program with the name of your program. The pre-compiler will create two files: program.java and program_SJProfile0.ser. The first is a pure Java program made from program.sqlj by replacing all the SQL code with appropriate Java function calls to interact with the database system. When we compile later, the appropriate libraries for these calls will be compiled in.

3. Binding.

   The bind step creates a package in DB2. An application program (APP) must have a package counterpart within DB2 to function. The package defines in essence a profile of the APP for the database. In "binding" the package, one can define many aspects of how the APP should be run (for instance, isolation levels), and permissions are checked and verified by DB2 at this stage. Whenever the APP is run later, it will be run with respect to the database as the DB2 user who owns the package to which it is bound.

   To bind your Java APP (and hence, to create a package) say

   % urun db2profc -url=jdbc:db2:database program_SJProfile0

   Various options can be added on the command above. These effect the package created, which in turn effects how the APP is run with respect to the database. Issue the command

   % db2profc

   by itself, if you are curious. It reports that

   db2profc = java COM.ibm.db2.sqlj.DB2SQLJInstaller

   whatever that means! Anyway this is effectively the binder for Java APPs.

   The package created will have the name program_SJ0. Packages are like tables in a DB2 database. They are objects which belong to schemas. To see one's current packages, type

   % db2 list packages

   To drop a package, say

   % db2 drop package

   Of course, the associated APP will no longer work. One would have to bind it again before it will work again. Also note that there is no "create package ...". Packages are created via the binding procedure above.

   Okay, so what is with the urun command? It allows the process to run with unlimited bounds on virtual memory. (Student accounts have a bound placed on this.) On ella, the urun command is not available to override this. (Too many people use ella, so programs that are major virtual memory hogs would be trouble.) On sen01 through sen20 and dem01 through dem23, however, the urun command is available to override this. Java, especially in this context, is a major virtual memory hog.

4. Compile.

   Finally, we are at the stage to compile the program. Yes, Java is interpreted, but you will need to compile into byte code.

   % javac program.java

   All the libraries, includes, and such needed for the compilation and linking are known to javac from program.java. This will create a program.class.

If all went well, you now have a working executable APP. To run your Java APP say

% urun java program arguments...

Again, you will undoubtably need urun as Java is such an amazing resource hog.

Here is an example embedded SQL Java program. It does not do much, but is instructive. It does the same thing as the example C program in the embedded SQL C guide. Try pre-compiling / compiling it. Use the database c3421a00 for this. It does not matter if you are in one of the other sections (section B / c3421b00); you can use c3421a00 for trying this. The APP queries a table sailor in schema techstu. User techstu has granted select privileges to all on table sailor, so the bind step will be legal.

// ===========================================================================
// sage.sqlj
// EXAMPLE of an embedded SQL Java Program for DB2.
// 
// Connect to database c3421a00 for this regardless of your section.
// 
// This APP takes one argument on the command line, a sailor's SID.  It
// then finds the sailor SID's age out of the table TECHSTU.SAILOR (in
// database c3421a00) and reports it.  A dumb and not very interesting APP,
// but it shows how things are done.
// 
// P. Godfrey
// 2013 Mar 26 [revised]
// 1999 Dec    [original]
// ===========================================================================

import java.sql.*;
import java.io.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

// ===========================================================================
// MAIN
// ===========================================================================

class sage 
{   static
    {   try
        {   Class.forName ("com.ibm.db2.jcc.DB2Driver").newInstance ();
        } 
        catch (Exception e)
        {   System.out.println ("\n  Error loading DB2 Driver...\n");
            System.out.println (e);
            System.exit(1);
        }
    }

    public static void main(String argv[])
    {   try 
        {   System.out.println ("SAGE in SQLJ");

            String url = "jdbc:db2:c3421a00";     // URL is jdbc:db2:dbname
            Connection con = null;          

            // Set the connection with default id/password
            con = DriverManager.getConnection(url);  

            // Set the default context
            DefaultContext ctx = new DefaultContext(con);            
            DefaultContext.setDefaultContext(ctx);

            // Read in the query argument off the command line.
            int sid = 0;
            if (argv.length > 0) {
                sid = Integer.parseInt(argv[0]);
            } else {
                throw new Exception("Which SID?\n");
            } 

            String sname = null;
            short age = 0;
 
            // Find the name and age of sailor SID.
            #sql { SELECT SNAME, AGE INTO :sname, :age
                   FROM TECHSTU.SAILOR
                   WHERE SID = :sid } ;

            System.out.println ("Sailor " + sname + "'s age is " + age + ".");
            System.out.println ("Executed Successfuly");
            System.out.println ("Bye");
        }

        catch( Exception e )
        {
            System.out.println (e);
        }
    }
}

The instance of the table sailor at the time of writing is

SNAME           SID         RATING AGE   
--------------- ----------- ------ ------
yuppy                    22      1     20
lubber                   31      1     25
guppy                    44      2     31
rusty                    58      3     47

So once you have a clean compile, if you ask for example

% urun java sage 44