Configuring Clients for Database Gateway for Websphere MQ 19c

In this post, I will try to explain the upgrade of Database Gateway for MQ to version 19c.
It comes with two problems that makes an upgrade from e.g. Database Gateway 12c to 19c a bit more challenging than one would expect.

No support for PDBs

The first problem to address is that Database Gateway setup scripts for the clients do still have no support for Pluggable Databases (PDBs). Already the 12c version scripts, used to deploy the code in a client database had to be manually edited to correct the connection string. Back then this was still understandable as the major bulk of existing client database was a single instance database. 
In version 19c, I would expect to see some kind of support for the use of PDBs, but there is not. In both versions you still have to edit the deployment files when using it with a PDB. Otherwise the packages will be installed in the CDB, where they are not accessible from the PDBs.

The first thing that needs to be fixed, is that the pgmdeploy.sql program simply exits without doing a thing. This is caused by the "whenever sqlerror exit" in the beginning of the script, followed by a connection attempt using the "scott/tiger" account, that (should) nowhere exist. 
The general way to solve this is to comment out the line that has this connect command. It is around line 65 in the script. Simply put two dashes in front of it, to comment it out.

The second line that needs to be edited is found on line 111 of the pgmdeploy script. There the connection string used, should be appended with the PDB name the installation will take place in.

Change the lines:

PROMPT Verifying SYS Password...
connect SYS/&INPASS2 as sysdba

into

PROMPT Verifying SYS Password...
connect SYS/&INPASS2@<PDB NAME> as sysdba

For the pgmundeploy.sql the same needs to be done on line 91.

Remark: There were the scripts used with a single instance database were able to continue, by simply using the [Enter] key only, with a PDB you actually have to specify a SYS password.

Now the deploy and undeploy scripts should work OK, for Oracle 19c clients, but.....


No support for lower level databases

We all know that Oracle 12.1 is out of support, not even speaking about Oracle 11 databases.
When you because of that, decide to upgrade Database Gateway 12c to Database Gateway 19c, you will run into a major but solvable issue, when the client databases you use are on Oracle 12.1 or even lower.

If you however start using Database Gateway now for the first time and you decide to start with the latest (19c) release, then this issue is not easy to solve.

The problem is that as of release 12.2 ( 19c is 12.2.0.4), the DBMS_STANDARD package has been extended with a new variable ORA_MAX_NAME_LEN and some dependent functions. 
Oracle Database Gateway 19c uses this new variable, which does not exist on lower level databases. 

This makes the deploy of the PGM packages on Oracle 11 and Oracle 12.1 database impossible.
Only if you have or can get the deploy scripts for an earlier Database Gateway release then you can use Database Gateway 19c


Solution

To use an Oracle Database 11g or 12.1 with Database Gateway 19c, you should use the Database Gateway 12.1 deploy scripts.
As soon as they are deployed, you need to replace the library definition for LIBDG4MQ that has been created, making it point to the new 19c location of the library. 

Use a statement similar to this and execute it as SYS in the client database ( PDB or single instance):

create or replace library LIBDG4MQ as
'[your dbgateway_home]/lib/libdg4mqc.so'
transactional; 

Afterwards recompile all PGM packages under the SYS schema and your done. Note that the PGM_UTL8 package remains Invalid. This is expected.