Hi,

When using PostgreSQL database, SQLJ schema is required to use Java user-defined functions (UDFs). To create SQLJ schema, there is no issue with Linux, or PostgreSQL 9.6, but there is an issue with a combination of Windows and PostgreSQL 11. In this post, I will share a workaround how to set up a SQLJ schema on Windows and PostgreSQL 11.

Starting with Pega 8.3, Pega Platform installer started to include modules for PostgreSQL 11.5 as below.

Per installation guide, you need "pljava.dll", "pljava.jar", and "install.sql" to set up a SQLJ schema on Windows. However, you find only "pljava.jar" in the 11.5 directory. "pljava.dll" and "install.sql" are missing.

Just for experimental purposes, I have borrowed "pljava.dll" and "install.sql" from 9.6 directory instead, and gave it a try. Unfortunately, it didn't work. Below error was displayed.

Resolution:

The root cause is, correct "pljava.dll" is not packaged in the installer. This is a known issue and will be fixed from Pega 8.7. As a workaround, you can borrow "pljava.dll" from Pega 8.6 Personal Edition (Standard Edition wouldn't work). Also you can borrow "install.sql" script from 9.6 directory in the same version of Pega that you are trying to install.

How to set up SQLJ schema (full steps):

1. Install Microsoft Visual C++ (MSVC) runtime libraries to your database server. You can download MSVC Redistribuable package from Microsoft web site, or you can also use the one in the Pega installer. Install MSVC even if it is already installed, and restart your system.

2. Install JRE to your database server and add %JRE_HOME%\bin\server to your environment variable (*).

3. Make sure you have "jvm.dll" in the %JRE_HOME%\bin\server directory.

4. Copy "pljava.jar" from ResourceKit\PLJava\11.5\Windows directory to C:\Program Files\PostgreSQL\11\lib directory.

5. Copy "pljava.dll" from Pega 8.6 Personal Edition installed environment to C:\Program Files\PostgreSQL\11\lib directory. This file is located at PRPCPersonalEdition\pgsql\lib. Be noted that Pega 8.6 Standard Edition's one can't be used here.

6. Edit "postgresql.conf" in the C:\Program Files\PostgreSQL\11\data directory as follows. Be noted in Windows you need to place backslash twice, not once. After you save the file, restart PostgreSQL service.

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
pljava.classpath = 'C:\\Program Files\\PostgreSQL\\11\\lib\\pljava.jar'
pljava.vmoptions = '-Xms32M -Xmx64M -XX:ParallelGCThreads=2'
work_mem = 5MB
pljava.libjvm_location = '%JRE_HOME%\\bin\\server\\jvm.dll'

7. Copy "install.sql" script from 9.6 directory of your Pega installer to pgAdmin Query Tool, and run the script. This should succeed without an error.

* Notes (added on 12/2/2021):

Above JRE setting works fine as long as you are using Java version 8. JRE used to be included in JDK until this version.

After Java version 8, not only Oracle stopped including JRE in JDK, they stopped providing downloadable JRE packages. So if you are using Java version 11, which is the next and current supported version of LTS (Long Term Support), you won't be able to find "jre" directory in your JDK, nor can you install JRE by itself. You'll need to install JDK to database server (even though you don't need to develop / compile Java source on database server). Now you will need to use jvm.dll under %JAVA_HOME%bin\server where %JAVA_HOME% is the JDK's home directory. This jvm.dll exists in JDK as well.

You will need to modify the file path to jvm.dll from %JRE_HOME% to %JAVA_HOME%. Alternatively, you can also specify the full path instead of environment variable (ex. pljava.libjvm_location = 'C:\\Program Files\\Java\\jdk-11.0.13\\bin\\server\\jvm.dll').

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here
pljava.classpath = 'C:\\Program Files\\PostgreSQL\\11\\lib\\pljava.jar'
pljava.vmoptions = '-Xms32M -Xmx64M -XX:ParallelGCThreads=2'
work_mem = 5MB
pljava.libjvm_location = '%JAVA_HOME%\\bin\\server\\jvm.dll'

The latest version of installation guide still talks about JRE, and I have reported this as a manual bug. This should be fixed in the future release.

Hope this helps.

Thanks,