DB2: Embedded SQL for Java (SQLJ) 

 
This will walk you through "compiling" an embedded SQLJava program. See the directories
  • ~db2educ/sqllib/samples/java/
for examples and compilation instructions for Java. See below for another embedded SQL Java program (that is, a SQLJ program).

Someone kindly pointed out some good resources for learning JDBC and SQLJ:

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 always like that with Java?!)

Embedded SQL for 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 dem24 in the Ariel laboratory. The machine red will not work. I repeat, the machine red will not work. (Read on and you will learn why.)
  1. Connect. 


    2. 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 ~db2educ/sqllib/db2cshrc 
    (See DB2 at PRISM.) This sets all the library paths and so forth that will be needed. 
  2. Pre-compile. 


    3. 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, c3421a) 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. 


    4. 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 Red, the urun command is not available to override this. (Too many people use Red, so programs that are major virtual memory hogs would be trouble.) On sen01 through sen24 and dem01 through dem24, however, the urun command is available to override this. Java, especially in this context, is a major virtual memory hog. 

  4. Compile. 


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

An example embedded SQL Java program 
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. 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
  
// ===========================================================================
// sage.sqlj
// EXAMPLE of an embedded SQL Java Program for DB2.
// 
// Connect to database GO3421 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 c3421a) and reports it.  A dumb and not very interesting APP,
// but it shows how things are done.
// 
// P. Godfrey
// DEC 1999
// ===========================================================================

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

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

class sage 
{   static
    {   try
        {   Class.forName ("COM.ibm.db2.jdbc.app.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:c3421a";     // 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
you should get the response
SAGE in SQLJ
Sailor guppy's age is 31.
Executed Successfuly
Bye
Do not forget to check out some of the other sample programs found in
  • ~db2educ/sqllib/samples/java/