Creating SSH tunnels for DB2 and other multiport databases

Some databases require connections to multiple ports simultaneously. DB2 is a good example of this as you can see in the table below.

The above table has been reproduced from the IBM documentation for explanatory purposes. The complete documentation can be found here:
Ports That Must Be Open to Make an ODBC, Data Transfer, OLE DB, ADO.Net, or JDBC Connection through a Firewall
https://www.ibm.com/support/pages/ports-must-be-open-make-odbc-data-transfer-ole-db-adonet-or-jdbc-connection-through-firewall

The challenge arose when trying to create an SSH tunnel from Data Virtuality to a DB2. I mistakenly created a single SSH tunnel for port 449.  When I tried to connect, DB2 would refuse the connection because the driver could not establish connections on the other ports.

The solution turned out to be quite simple. Create SSH tunnels for each port.

create procedure views.create_db2_tunnels() as
begin

    loop on (
        SELECT cast(item as integer) as port 
        FROM (
            CALL UTILS.arrayToTable(items => (449, 8470, 8475, 8476, 8471))
        ) x
    ) as cur
    begin
        
        call "SYSADMIN_VDB.createSSHTunnel"(
            "name" => 'ssh_db2_' || cur.port,
            "localHost" => 'localhost', -- this will usually be localhost
            "localPort" => cur.port,
            "remoteHost" => '10.11.12.13', -- this is the IP address of the DB2 server
            "remotePort" => cur.port,
            "host" => 'ec2-user@3.4.5.6', -- This is IP address of the SSH proxy (aka SSH jump box)
            "sshKeyPairId" => 1 -- I created an RSA key pair inside of DV and this is the ID
        );
    
    end

end;;

For additional information see our documentation:

https://support.datavirtuality.com/hc/en-us/community/posts/115011850026-New-feature-Build-in-support-for-SSH-tunnels

https://docs.datavirtuality.com/v3/ssh-tunnel