1   //The simplest JAVA SP

2   import java.sql.*;

3 

4   public class INSERT_JAVASP

5   {

6     public static void iNSERT_JAVASP  (String input) throws SQLException,

 	Exception

7     {

8       int errorCode;

9  

10      try

11      {

12        // get caller's connection to the database

13   Connection con = DriverManager.getConnection("jdbc:default:connection");

14     

15        String query = "INSERT INTO CWYLAW.StoreData (c) VALUES (?)";

16

17        PreparedStatement pstmt = con.prepareStatement(query);

18        pstmt.setString(1, input);

19        pstmt.executeUpdate();

20  

21      }   

22      catch (SQLException sqle)

23      {

24        errorCode = sqle.getErrorCode();

25        throw new SQLException( errorCode   " FAILED" ); 

26      }

27    }

28  }
																		

																				



CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'

;
																		

																				

$ db2 "CALL SHAKEBS.TESTPROC('hello', 'world', 1, 2.5, ?, 'testing')"
																		

																				



D:\>javac INSERT_JAVASP.java



D:\>copy INSERT_JAVASP.class "C:\Program Files\IBM\SQLLIB\Function"

        1 file(s) copied.



D:\>db2 -tvf Create.ddl

CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'



DB20000I  The SQL command completed successfully.



D:\>db2 call INSERT('D')

SQL4301N  Java or .NET interpreter startup or communication failed, reason

code "0".  SQLSTATE=58004
																		

																				



D:\>db2 get dbm cfg 



          Database Manager Configuration



     Node type = Enterprise Server Edition with local and remote clients



Database manager configuration release level         = 0x0a00



Maximum total of files open            (MAXTOTFILOP) = 16000

CPU speed (millisec/instruction)          (CPUSPEED) = 9.368161e-007

Communications bandwidth (MB/sec)   (COMM_BANDWIDTH) = 1.000000e 002



Max number of concurrently active databases  (NUMDB) = 8

Data Links support                       (DATALINKS) = NO

Federated Database System Support        (FEDERATED) = NO

Transaction processor monitor name     (TP_MON_NAME) =



Default charge-back account        (DFT_ACCOUNT_STR) =



Java Development Kit installation path    (JDK_PATH) = C:\PROGRA~1\IBM\

SQLLIB\java



...
																		

																				



D:\>db2 update dbm cfg using JDK_PATH C:\PROGRA~1\IBM\SQLLIB\java\jdk



DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command 

completed successfully.





D:\>db2stop force

09/25/2005 14:33:16     0   0   SQL1064N  DB2STOP processing was successful.

SQL1064N  DB2STOP processing was successful.



D:\>db2start

09/25/2005 14:33:46     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.



D:\>db2 connect to sample





   Database Connection Information



 Database server        = DB2/NT 8.2.3

 SQL authorization ID   = SHAKEBS

 Local database alias   = SAMPLE



D:\>db2 call INSERT_JAVASP('D')



  Return Status = 0



D:\>db2 "select * from CWYLAW.StoreData"



C

---

D



  1 record(s) selected.
																		

																				



$ which java

/wsdb/v81/bldsupp/AIX/jdk1.4.1/bin/java



$ java -version



java version "1.4.1"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1)

Classic VM (build 1.4.1, J2RE 1.4.1 IBM AIX build ca1411-20030930 (JIT enabled:

jitc))



$ db2level



DB21085I  Instance "dbguest4" uses "64" bits and DB2 code release 

"SQL08022" with level identifier "03030106".

Informational tokens are "DB2 v8.1.1.88", "s050422", "U800789", and FixPak "9".

Product is installed at "/usr/opt/db2_08_01".





$ db2 connect to sample



   Database Connection Information



 Database server        = DB2/AIX64 8.2.2

 SQL authorization ID   = DBGUEST4

 Local database alias   = SAMPLE



$ db2 "call out_language(?)"

SQL4301N  Java or .NET interpreter startup or communication failed, reason

code "0".  SQLSTATE=58004
																		

																				



$ db2 connect to sample



   Database Connection Information



 Database server        = DB2/AIX64 8.2.2

 SQL authorization ID   = DBGUEST4

 Local database alias   = SAMPLE



$ db2 "call out_language(?)"



  Value of output parameters

  --------------------------

  Parameter Name  : LANGUAGE

  Parameter Value : JAVA



  Return Status = 0



$ which java

/wsdb/v81/bldsupp/AIX5L64/jdk1.4.1/bin/java



$ java -version

java version "1.4.1"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1)



Classic VM (build 1.4.1, J2RE 1.4.1 IBM AIX 5L for PowerPC (64 bit JVM) 

build ca

ix641411-20030930 (JIT enabled: jitc))
																		

																				



2005-10-02-18.42.36.052560-240 E226800A732        LEVEL: Error (OS)

PID     : 191200               TID  : 1           PROC : db2fmp

INSTANCE: dbguest4             NODE : 000

FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:130

CALLED  : OS, -, dlopen

OSERR   : ENOEXEC (8) "Cannot run a file that does not have a valid format."

MESSAGE : Attempt to load specified library failed.

DATA #1 : Library name or path, 55 bytes

/wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a

DATA #2 : shared library load flags, PD_TYPE_LOAD_FLAGS, 4 bytes

2

DATA #3 : String, 145 bytes



	0509-022 Cannot load module 

   /wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a.

	0509-124 The program is a discontinued 64-bit object file.



2005-10-02-18.42.36.053802-240 E227533A860        LEVEL: Error (OS)

PID     : 191200               TID  : 1           PROC : db2fmp

INSTANCE: dbguest4             NODE : 000

FUNCTION: DB2 UDB, oper system services, sqloLoadModule, probe:140

CALLED  : OS, -, dlopen

OSERR   : ENOEXEC (8) "Cannot run a file that does not have a valid format."

MESSAGE : Attempt to load specified library augmented with object name failed.

DATA #1 : Library name or path, 65 bytes

/wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a(shr_64.o)

DATA #2 : shared library load flags, PD_TYPE_LOAD_FLAGS, 4 bytes

262146

DATA #3 : String, 231 bytes

	0509-022 Cannot load module 

	   /wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a(shr_64.o).

	0509-153   File /wsdb/v81/bldsupp/AIX/jdk1.4.1/jre/bin/classic/libjvm.a is 

	    not an archive or the file could not be read properly.



2005-10-02-18.42.36.058868-240 I228394A367        LEVEL: Error

PID     : 191200               TID  : 1           PROC : db2fmp

INSTANCE: dbguest4             NODE : 000

FUNCTION: DB2 UDB, oper system services, sqloJVMstart, probe:30

MESSAGE : sqloloadmodule failed.  RC:

DATA #1 : Hexdump, 4 bytes

0x0FFFFFFFFFFFC080 : 870F 009B                                  ....



2005-10-02-18.42.36.059205-240 I228762A362        LEVEL: Error

PID     : 191200               TID  : 1           PROC : db2fmp

INSTANCE: dbguest4             NODE : 000

FUNCTION: DB2 UDB, oper system services, sqloJAttach, probe:5

MESSAGE : JVM startup failed.  RC:

DATA #1 : Hexdump, 4 bytes

0x0FFFFFFFFFFFF3E0 : FFFF EF34                                  ...4



2005-10-02-18.42.36.059511-240 I229125A363        LEVEL: Error

PID     : 191200               TID  : 1           PROC : db2fmp

INSTANCE: dbguest4             NODE : 000

FUNCTION: DB2 UDB, BSU Java support, sqlejAttach, probe:10

MESSAGE : Error from sqloJAttach.  RC:

DATA #1 : Hexdump, 4 bytes

0x0FFFFFFFFFFFF4A0 : FFFF EF34                                  ...4



2005-10-02-18.42.36.060331-240 I229489A372        LEVEL: Severe

PID     : 251500               TID  : 1           PROC : db2agent (SAMPLE)

INSTANCE: dbguest4             NODE : 000         DB   : SAMPLE

APPHDL  : 0-7                  APPID: *LOCAL.dbguest4.051002224226

FUNCTION: DB2 UDB, routine_infrastructure, sqlerGetFmpThread, probe:20

RETCODE : ZRC=0xFFFFFBEE=-1042



2005-10-02-18.42.36.066498-240 I229862A314        LEVEL: Warning

PID     : 124744               TID  : 1           PROC : db2sysc

INSTANCE: dbguest4             NODE : 000

MESSAGE : Removing FMP from pool

DATA #1 : Hexdump, 16 bytes

0x0FFFFFFFFFFFE090 : 0000 0000 0000 0000 0002 EAE0 0002 49B8    ...I.



2005-10-02-18.44.20.194287-240 I230177A348        LEVEL: Event

PID     : 120486               TID  : 1           PROC : db2flacc

INSTANCE: dbguest4             NODE : 000

FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam, probe:30

CHANGE  : CFG DBM: "JDK_path" From: "/wsdb/v81/bldsupp/AIX/jdk1.4.1" To: 

    "/wsdb/v81/bldsupp/AIX5L64/jdk1.4.1"
																		

																				



D:\>javac INSERT_JAVASP.java



D:\>copy INSERT_JAVASP.class "C:\Program Files\IBM\SQLLIB\Function"

        1 file(s) copied.     



D:\>db2 -tvf Create.ddl

CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'



DB20000I  The SQL command completed successfully.





D:\>db2 call INSERT('A')

SQL4301N  Java or .NET interpreter startup or communication failed, 

reason code "4".  SQLSTATE=58004
																		

																				



D:\>db2 get dbm cfg 



          Database Manager Configuration



     Node type = Enterprise Server Edition with local and remote clients



 Database manager configuration release level            = 0x0a00



 Maximum total of files open               (MAXTOTFILOP) = 16000

 CPU speed (millisec/instruction)             (CPUSPEED) = 9.368161e-007

 Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e 002



...



 Database monitor heap size (4KB)          (MON_HEAP_SZ) = 66

 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 1

 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0

 Size of instance shared memory (4KB)  (INSTANCE_MEMORY) = AUTOMATIC

 Backup buffer default size (4KB)            (BACKBUFSZ) = 1024

 Restore buffer default size (4KB)           (RESTBUFSZ) = 1024



...


																		

																				



D:\>db2 update dbm cfg using JAVA_HEAP_SZ 512

DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed

successfully.

  

D:\>db2stop force

09/25/2005 14:33:16     0   0   SQL1064N  DB2STOP processing was successful.

SQL1064N  DB2STOP processing was successful.



D:\>db2start

09/25/2005 14:33:46     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.



D:\>db2 connect to sample



   Database Connection Information



 Database server        = DB2/NT 8.2.3

 SQL authorization ID   = SHAKEBS

 Local database alias   = SAMPLE



D:\>db2 call INSERT_JAVASP('A')



  Return Status = 0



D:\>db2 "select * from CWYLAW.StoreData"



C

---

A



  1 record(s) selected.
																		

																				



D:\>javac INSERT_JAVASP.java



D:\>copy INSERT_JAVASP.class "C:\Program Files\IBM\SQLLIB\Function"

        1 file(s) copied.

        

D:\>db2 -tvf Create.ddl

CREATE PROCEDURE INSERT_JAVASP (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT_JAVASP!iNSERT_JAVASP'



DB20000I  The SQL command completed successfully.





D:\>db2 call INSERT('D')

SQL4301N  Java or .NET interpreter startup or communication failed, 

reason code "-4301".  SQLSTATE=58004
																		

																				



D:>set | more



ALLUSERSPROFILE=C:\Documents and Settings\All Users

APPDATA=C:\Documents and Settings\Administrator\Application Data

CLASSPATH=.;C:\Progra~1\IBM\SQLLIB\java\db2jcc.jar;C:\Progra~1\IBM\SQLLIB\

java\sqlj.zip;C:\Progra~1\IBM\SQLLIB\java\common.jar;C:\Progra~1\IBM\SQLLIB\

java\db2jcc_license_cisuz.jar;C:\Progra~1\IBM\SQLLIB\java\db2jcc_license_cu.jar

...



D:\>set CLASSPATH=%CLASSPATH%;C:\Progra~1\IBM\SQLLIB\java\db2java.zip
																		

																				



D:\>db2 call INSERT_JAVASP('D')



  Return Status = 0



D:\>db2 "select * from CWYLAW.StoreData"



C

---

D



  1 record(s) selected.
																		

																				

   

1    import java.sql.*;

2         

3    public class Query

4    {

5       public static void query ( int id , String[] s1 ) throws 

            SQLException, Exception

6      {

7           // Get connection to the database

8           Connection con = 

            DriverManager.getConnection("jdbc:default:connection");

9           PreparedStatement stmt = null;

10          String errorLabel =  null;

11          String sql;

12                  

13          

14          sql = "SELECT NAME FROM STAFF WHERE ID = ?";

15          stmt = con.prepareStatement( sql );     

16          stmt.setInt(1, id);   

17          ResultSet rs = stmt.executeQuery();

18                  

19          if (!rs.next()) {

20             // set errorCode to SQL0100 to indicate data not found

21             errorLabel = "SQL0100 : NO DATA FOUND, QUERY RETURNS 

               EMPTY RESULT SET";

22             throw new SQLException(errorLabel);

23          } else {

24             // move to first row of result set

25             s1[0] = rs.getString(1);                

26          }   

27                  

28                  

29          // clean up resources

30          rs.close();

31          stmt.close();

32          con.close();

33                           

34          }

35   }



D:\>db2 -tvf Create.ddl

CREATE PROCEDURE CWYLAW.QUERY (IN ID INT, OUT NAME CHAR(9))

SPECIFIC QUERY

DYNAMIC RESULT SETS 0

NOT DETERMINISTIC

LANGUAGE JAVA

EXTERNAL NAME 'Query.query'

FENCED

THREADSAFE

PARAMETER STYLE JAVA



DB20000I  The SQL command completed successfully.





D:\>db2 call query(5, ?)

SQL4302N  Procedure or user-defined function "CWYLAW.QUERY", 

specific name "QUERY" aborted with an exception "SQL0100 : 

NO DATA FOUND, QUERY RETURNS EMPTY RESULT".  SQLSTATE=38501



D:\>db2 call query(10, ?)



  Value of output parameters

  --------------------------

  Parameter Name  : NAME

  Parameter Value : Sanders



  Return Status = 0
																		

																				

   

2005-10-02-21.51.36.325000-240 I79282H396         LEVEL: Warning

PID     : 2140                 TID  : 2684        PROC : db2fmp.exe

INSTANCE: DB2                  NODE : 000

FUNCTION: DB2 UDB, BSU Java support, sqlejCallJavaRoutine_dll, probe:315

MESSAGE : Exception thrown during routine invocation:

DATA #1 : Hexdump, 4 bytes

0x01ACF5EC : D480 5501                                  ..U.



2005-10-02-21.51.36.335000-240 E79680H375         LEVEL: Warning

PID     : 2140                 TID  : 2684        PROC : db2fmp.exe

INSTANCE: DB2                  NODE : 000

FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10

MESSAGE : ADM10000W  A Java exception has been caught.  The Java stack 

          traceback has been written to the db2diag.log.



2005-10-02-21.51.36.345000-240 I80057H475         LEVEL: Warning

PID     : 2140                 TID  : 2684        PROC : db2fmp.exe

INSTANCE: DB2                  NODE : 000

FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10

DATA #1 : String, 112 bytes

java.sql.SQLException: SQL0100 : NO DATA FOUND, QUERY RETURNS EMPTY 

  RESULT SET at Query.query(Query.java:22)

DATA #2 : Hexdump, 4 bytes

0x01ACF424 : 0000 0000                                  ....



2005-10-02-21.51.36.355000-240 I80534H384         LEVEL: Warning

PID     : 2140                 TID  : 2684        PROC : db2fmp.exe

INSTANCE: DB2                  NODE : 000

FUNCTION: DB2 UDB, routine_infrastructure, sqlerJavaCallRoutine, probe:30

MESSAGE : Error from DB2ER CallUDF.  RC:

DATA #1 : Hexdump, 4 bytes

0x01ACF97C : 32EF FFFF                                  2...



2005-10-02-21.51.36.365000-240 I80920H959         LEVEL: Error

PID     : 3632                 TID  : 2840        PROC : db2bp.exe

INSTANCE: DB2                  NODE : 000

APPID   : *LOCAL.DB2.051003014530

FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10

DATA #1 : Hexdump, 136 bytes

0x0012FC90 : 5351 4C43 4120 2020 8800 0000 32EF FFFF    SQLCA   ....2...

0x0012FCA0 : 4600 4357 594C 4157 2E51 5545 5259 FF51    F.CWYLAW.QUERY.Q

0x0012FCB0 : 5545 5259 FF53 514C 3031 3030 203A 204E    UERY.SQL0100 : N

0x0012FCC0 : 4F20 4441 5441 2046 4F55 4E44 2C20 5155    O DATA FOUND, QU

0x0012FCD0 : 4552 5920 5245 5455 524E 5320 454D 5054    ERY RETURNS EMPT

0x0012FCE0 : 5920 5245 5355 4C54 5351 4C45 4A45 5854    Y RESULTSQLEJEXT

0x0012FCF0 : 0000 0000 0000 0000 0000 0000 0000 0000    ................

0x0012FD00 : 0000 0000 0000 0000 2020 2020 2020 2020    ........        

0x0012FD10 : 2020 2033 3835 3031                           38501
																		

																				



$ javac SQL4304RC1.java



$ cp SQL4304RC1.class ~/sqllib/function



$ db2 -tvf CreateSP_wrong.ddl

CREATE PROCEDURE SQL4304RC1 (IN INPUT int)

SPECIFIC SQL4304RC1

DYNAMIC RESULT SETS 1

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE DB2GENERAL

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'SQ4304RC1!abend'



DB20000I  The SQL command completed successfully.





$ db2 "call SQL4304RC1(3)"

SQL4304N  Java stored procedure or user-defined function "SHAKEBS.SQL4304RC1",

specific name "SQL4304RC1" could not load Java class "SQ4304RC1", reason code

"1".  SQLSTATE=42724
																		

																				



$ db2 drop procedure SQL4304RC1

DB20000I  The SQL command completed successfully.



$ db2 -tvf CreateSP.ddl

CREATE PROCEDURE SQL4304RC1 (IN INPUT int)

SPECIFIC SQL4304RC1

DYNAMIC RESULT SETS 1

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE DB2GENERAL

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'SQL4304RC1!abend'



DB20000I  The SQL command completed successfully.





$ db2 "call SQL4304RC1(3)"



  Result set 1

  --------------



  ID     NAME      DEPT   JOB   YEARS  SALARY    COMM

  ------ --------- ------ ----- ------ --------- ---------

     180 Abrahams      38 Clerk      3  12009.75    236.50

     230 Lundquist     51 Clerk      3  13369.80    189.65



  2 record(s) selected.



  Return Status = 0
																		

																				



$ javac SQL4304RC2.java



$ cp SQL4304RC2.class ~/sqllib/function



$ db2 -tvf CreateSP_wrong.ddl

CREATE PROCEDURE SQL4304RC2 (IN INPUT int)

SPECIFIC SQL4304RC2

DYNAMIC RESULT SETS 1

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE DB2GENERAL

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'SQL4304RC2!abend'



DB20000I  The SQL command completed successfully.



$ db2 "call SQL4304RC2(3)"

SQL4304N  Java stored procedure or user-defined function "SHAKEBS.SQL4304RC2",

specific name "SQL4304RC2" could not load Java class "SQL4304RC2", reason code

"2".  SQLSTATE=42724
																		

																				



1    //The simplest JAVA SP

2    import java.sql.*;

3    import COM.ibm.db2.app.*;

4     

5    public class SQL4304RC2 extends COM.ibm.db2.app.StoredProc

6    { 

7      public void abend  (int input) throws SQLException,Exception

8      {  

9        int errorCode;

10   

11       try

12       {

13         // get caller's connection to the database

14         Connection con = DriverManager.getConnection("jdbc:default:connection");

15     

16         String query = "SELECT * FROM STAFF where YEARS = ?";

17

18         PreparedStatement pstmt = con.prepareStatement(query);

19         ResultSet rs = null;

20         pstmt.setInt(1, input);

21         rs = pstmt.executeQuery();

22    

23       } 

24       catch (SQLException sqle)

25       {

26         errorCode = sqle.getErrorCode();

27         throw new SQLException( errorCode   " FAILED - "   sqle.getMessage()); 

28       }  

29     }

30   }
																		

																				



$ javac SQL4304RC2.java



$ cp SQL4304RC2.class ~/sqllib/function



$ db2 "call SQL4304RC2(3)"



  Result set 1

  --------------



  ID     NAME      DEPT   JOB   YEARS  SALARY    COMM

  ------ --------- ------ ----- ------ --------- ---------

     180 Abrahams      38 Clerk      3  12009.75    236.50

     230 Lundquist     51 Clerk      3  13369.80    189.65



  2 record(s) selected.



  Return Status = 0
																		

																				

     

D:\>sqlj INSERT.sqlj

   

D:\>db2sqljcustomize -user cwylaw -password xxxxxxxxx -url 

   jdbc:db2://claw.torolab.ibm.com:50000/sample INSERT_SJProfile0

[ibm][db2][jcc][sqlj]

[ibm][db2][jcc][sqlj] Begin Customization

[ibm][db2][jcc][sqlj] Loading profile: INSERT_SJProfile0

[ibm][db2][jcc][sqlj] Customization complete for profile 

    INSERT_SJProfile0.ser

[ibm][db2][jcc][sqlj] Begin Bind

[ibm][db2][jcc][sqlj] Loading profile: INSERT_SJProfile0



[ibm][db2][jcc][sqlj] Driver defaults(user may override): BLOCKING ALL 

    VALIDATE BIND STATICREADONLY YES

[ibm][db2][jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND

[ibm][db2][jcc][sqlj] Binding package INSERT01 at isolation level UR

[ibm][db2][jcc][sqlj] Binding package INSERT02 at isolation level CS

[ibm][db2][jcc][sqlj] Binding package INSERT03 at isolation level RS

[ibm][db2][jcc][sqlj] Binding package INSERT04 at isolation level RR

[ibm][db2][jcc][sqlj] Bind complete for INSERT_SJProfile0



D:\>jar -cvf INSERT.jar *.class *.ser

added manifest

adding: INSERT.class(in = 1192) (out= 684)(deflated 42%)



D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 

'INSERTJAR')

DB20000I  The CALL command completed successfully.



D:\>db2 -tvf Create.ddl

CREATE PROCEDURE INSERT (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT.INSERT'



DB20000I  The SQL command completed successfully.



D:\>db2 call INSERT('abc')

SQL4306N  Java stored procedure or user-defined function "CWYLAW.INSERT",

specific name "INSERT" could not call Java method "INSERT", 

signature "(Ljava/lang/String;)V".  SQLSTATE=42724
																		

																				



//The simplest SQLJ SP

import java.sql.*;

import sqlj.runtime.*; 

import sqlj.runtime.ref.*; 

 

public class INSERT 

{

  public static void iNSERT (String input) throws SQLException, Exception

 

    { 

    	#sql { INSERT INTO CWYLAW.StoreData (c) VALUES (:input) }; 

    }  

}
																		

																				



CREATE TABLE StoreData (c char(3));
																		

																				



CREATE PROCEDURE INSERT (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT.iNSERT'

;
																		

																				

D:\>db2 drop procedure INSERT

DB20000I  The SQL command completed successfully.



D:\>db2 call sqlj.remove_jar('INSERTJAR')

DB20000I  The CALL command completed successfully.



D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 'INSERTJAR')

DB20000I  The CALL command completed successfully.



D:\>db2 -tvf Create.ddl

CREATE PROCEDURE INSERT (IN INPUT CHAR(3))

SPECIFIC INSERT

DYNAMIC RESULT SETS 0

DETERMINISTIC

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO DBINFO

FENCED

THREADSAFE

MODIFIES SQL DATA

PROGRAM TYPE SUB

EXTERNAL NAME 'INSERT.iNSERT'



DB20000I  The SQL command completed successfully.

D:\>db2 call INSERT('abc')



  Return Status = 0

  

D:\>db2 select * from StoreData



C

---

abc



  1 record(s) selected.
																		

																				



SQL4306N  Java stored procedure or user-defined function "CWYLAW.INSERT",

specific name "INSERT" could not call Java method "INSERT", signature

"(Ljava/lang/String;)V".  SQLSTATE=42724