DB2: JAVA subroutines

 The JAR file can be put anywhere on the server.

When it is installed, the INSTALL_JAR command puts the JAR contents exactly where the database needs it.

Also, ensure your JDK_PATH is setup.

[dev2ins2@db2tst601  ~]

$ unzip AESUtility.zip

Archive:  AESUtility.zip

  inflating: AESUtility.jar

Put the AESUtility.jar anywhere and you can delete this file off the server once installed.

To verify java is installed:

$ db2 get dbm cfg | grep -i jdk

 Java Development Kit installation path       (JDK_PATH) = /usr/opt/app/db2/admin/dev2ins2/sqllib/java/jdk64

(1) Connect to the database

[dev2ins2@db2tst601  ~]

$ db2 connect to wcdev2

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.5.9.0

 SQL authorization ID   = DEV2INS2

 Local database alias   = WCDEV2

(2) Install the JAR file to the database

[dev2ins2@db2tst601  ~]

$ db2 "CALL sqlj.install_jar( 'file:/usr/opt/app/db2/admin/dev2ins2/sqllib/java/jdk64/bin/AESUtility.jar', 'ALIAS_EMP_JAR')"

DB20000I  The CALL command completed successfully.

(3) Create the subroutine (in this case it is a function)

$  db2 -tvf /usr/opt/app/db2/admin/dev2ins2/pcr2.sql

CREATE FUNCTION wcs.XAES_DECRYPT (

    IN ciphertext VARCHAR(255),

    IN key VARCHAR(16)

)

RETURNS VARCHAR(255)

LANGUAGE JAVA

PARAMETER STYLE JAVA

NO EXTERNAL ACTION

EXTERNAL NAME 'ALIAS_EMP_JAR:com.tb.db2.utility.functions.AESUtility.decrypt'

NO SQL

FENCED

DB20000I  The SQL command completed successfully.

To see the contents of the JAR file:

$ pwd

/usr/opt/app/db2/admin/dev2ins2/sqllib/function

[dev2ins2@db2tst601  function]

$ jar tvf AESUtility.jar

    25 Mon Nov 18 12:52:14 CST 2024 META-INF/MANIFEST.MF

  1638 Mon Nov 18 12:37:42 CST 2024 com/tb/db2/utility/functions/AESUtility.class

     6 Mon Nov 18 12:15:08 CST 2024 .gitignore

   301 Mon Nov 18 12:15:08 CST 2024 .classpath

   386 Mon Nov 18 12:15:06 CST 2024 .project

To test the function just created:

values  wcs.XAES_ENCRYPT ('test', 'test');