Service to extract JDBC connection info

, ,
1

Hi all,

Could you please let me know how to extract list of JDBC connection from IS. Is there any service to do that? How does DSP page extract when we click view JDBC connection in IS?

2

I know pub.art.service:listAdapterServices. but i want to know the host name and DB service name and other connection properties. I can see it from IS. But as part pf DB migration i need to details from around 42 servers

3

Hi Surya,

which type of DB Migration are referring to?
Is it just version update and/or moving to a new host or are you changing database vendor?

You can investigate the node.ndf of the Connection and decode the Base64-coded Value.
In this decoded structure you will find all the parameters of the connection.

Regards,
Holger

4

Hi Holger, Thank you for the advice and explanation.

Migrating from oracle 10g to 11c. Could you please let me know the path for node.ndf file? I need connection properties like ‘host name’,‘portnumber’,‘other properties’,‘user’ and details like that from webMethods adapter for JDBC tab. I am using 9.8 version currently. As part of this migration I need to take backup of existing JDBC connections and adapter details and verify later once the migration is complete. Could you please let me know how to take the backup?

Thanks
Surya

5

Hi Surya,

the node.ndf is located under /IntegrationServer/instances/default/packages//ns//.

The package names, the folder names and the Connection Name can be obtained from JDBC Adapter page or from Designer → ServiceDevelopment View.

Regards,
Holger

6

Thank you for the information. I know where to check for the details but in case of more number of adapters its difficult to check one by one and copy the JDBC Connection details and keep a back up since we have many Connections and lots of servers. Can you advise any simple idea?

7

Hi Surya,

you can take an archive of the packages where the connections reside.
Remember to disable them first.

If you want to check the parameters by a service you can list the connections by using ART Built-In-Services and XQL (XML Query Language).

Outline:
list all connections for type JDBC
LOOP over connection list
get node.ndf from filesystem.
get base64 coded property from it by XQL
decode base64
Store the xml somewhere
LOOP end

You will find Built-In-Services for this in the IS Built-In-Services reference.

Regards,
Holger

8

Hi Surya,

We list JDBC adapter connection info by:

Invoke pub.art:listRegisteredAdapters
Loop over results.
Invoke pub.art.connection:listAdapterConnections (adapterTypeName is from listRegisteredAdapters).
Loop over results.
Invoke wm.art.admin.connection:getResourceConfiguration (connectionAlias is from listAdapterConnections).
Get values from ‘parameters’ output.

-Mary

9

Hi Surya,
Forgot to mention that we write this to .csv file and store in a spreadsheet.
-Mary

10

Hi Mary,

Thank you. Could you please tell me the location or path of the spreadsheet where to find it?

Thanks
Surya

11

Hi Surya,
Once you have your adapters list in a string (with new line and comma delimiters) you can invoke pub.file:stringToFile which will create a file in the IntegrationServer directory. Then you can move it to wherever you like. In our case, we attach and send via email.
-Mary

12

What is the encoding that we should use for base64 decoding of the IRTNODE_PROPERTY value? I’ve tried the default (lots of extra spaces and unknown characters), UTF-16 (ArrayIndexOutOfBoundsException thrown by pub.string:base64Decode), and UTF-8 (same as the default).

13

Hi Don,

as per documentation base64Decode does not have an input for decoding, but only an output.
Use the content of this field as input for base64Encode when re-encoding after changing.
Default encoding is ASCII according to the Built-In-Services Reference.
When decoding the IRTNODE_PROPERTY this should more likely return UTF-8 as encoding as this is a XML structure internally.

Regards,
Holger

14

Holger,
What documentation are you looking at that shows base64Decode does not have an input for decoding? I’m looking directly at the service itself in Designer (v9.12). The service has two inputs: ‘string’ and ‘encoding’ (both strings) and one output: ‘value’ (a byte string).

15

Hi @Surya_RK2,

You don’t need to read the node.ndf files, you can capture all the information you want from the following services (reading from the connection page dsp):

  • pub.art.connection:listAdapterConnections
  • wm.art.admin.connection:getResourceConfiguration (not visible, use a pub.remote:invoke to call it)

And if you have all your remote servers accessible from an IS, you can do remote invokes to recover all their info.

Good luck,

1 Like
16

I forgot, to save a backup of all those connections, create a Deployer project.
You can even create a substitution template to change any of those values on deployment.

1 Like
17

I am successfully using those services and it works for every piece of information except the database password. I was hoping that decoding the info would allow me to extract that. From the bits I’ve been able to obtain through the partial working of the decode service, that info isn’t available. Is that true? If so, is there an alternative approach to getting the db password that goes with the user the above services pull out?

18

Hi Don,

I doubt that this will not be possible as the Passwords are stored as Hashes in a separate file under IS config folder, which is not human readable. The connection node.ndf only knows about the hash identifier to check the password against.

As this might lead to a security issue there are no direct services available to read this password database file.
When using Deployer, it will transport the password behind the scenes which can be substituted during deployment as Gerardo mentioned. But for this to work correctly you will have to make sure that the connections are disabled when taking the build otherwise the deployment might take terribly long when IS tries to start the connection with the original parameters (potentially incorrect for the target environment) before substituting them and then restarting the connection.

Therefore we have documented all the connection parameters for our JDBC, MQSeries and SAP connections and listeners in an internal encrypted Excel-table for which only a few guys are having access who are involved in installing and maintaining the instances.

Regards,
Holger

19

It is possible If you really want to do this! Take a look at:
pub.security.outboundPasswords:getPassword

You’ll also need to use
pub.security.util:convertSecureString

From memory without confirming, I think these are WmRoot services so bear that in mind in terms of interface compatibility between versions/etc, and any ‘support’ issues that may come from using these.