Regular Contributor.. rvaghela Regular Contributor..
Regular Contributor..
1071 views

SQL query operations failing while connecting to DB2 databse through OO

Jump to solution

Hi All,

I am trying to connect to DB2 database through HPOO to fetch infromation.  I am using operation “SQL Query All Rows” which is available in path /Base [1.9.1]/Library/Operations/Databases/JDBC/SQL Query All Rows.  I am getting below error while making a connection to the DB.

 

{exception=java.lang.ClassNotFoundException: com.ibm.db2.jcc.DB2Driver

                at java.net.URLClassLoader.findClass(URLClassLoader.java:381)

                at java.lang.ClassLoader.loadClass(ClassLoader.java:424)

                at java.lang.ClassLoader.loadClass(ClassLoader.java:357)

                at java.lang.Class.forName0(Native Method)

                at java.lang.Class.forName(Class.java:264)

                at com.iconclude.content.actions.sql.services.databases.DB2Database.setUp(DB2Database.java:21)

                at com.iconclude.content.actions.sql.services.ConnectionService.setUpConnection(ConnectionService.java:99)

                at com.iconclude.content.actions.sql.services.SQLQueryAllRowsService.execQueryAllRows(SQLQueryAllRowsService.java:31)

                at com.iconclude.content.actions.sql.SQLQueryAllRows.execute(SQLQueryAllRows.java:92)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:497)

                at com.hp.oo.sdk.plugins.abstracts.BaseActionPlugin.execute(BaseActionPlugin.java:53)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:497)

                at com.hp.oo.maven.PluginAdapterImpl.executePlugin(PluginAdapterImpl.java:328)

                at com.hp.oo.maven.PluginAdapterImpl.execute(PluginAdapterImpl.java:248)

                at com.hp.oo.execution.control.actions.contentexecution.ContentExecutionActions.executeContentAction(ContentExecutionActions.java:97)

                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:497)

                at io.cloudslang.worker.execution.reflection.ReflectionAdapterImpl.executeControlAction(ReflectionAdapterImpl.java:62)

                at io.cloudslang.worker.execution.services.ExecutionServiceImpl.executeStep(ExecutionServiceImpl.java:326)

                at io.cloudslang.worker.execution.services.ExecutionServiceImpl.execute(ExecutionServiceImpl.java:80)

                at io.cloudslang.worker.management.services.SimpleExecutionRunnable.executeRegularStep(SimpleExecutionRunnable.java:167)

                at io.cloudslang.worker.management.services.SimpleExecutionRunnable.run(SimpleExecutionRunnable.java:120)

                at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

                at java.util.concurrent.FutureTask.run(FutureTask.java:266)

                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

                at io.cloudslang.worker.management.services.WorkerThreadFactory$1.run(WorkerThreadFactory.java:33)

                at java.lang.Thread.run(Thread.java:745)

;returnCode=-1;returnResult=com.ibm.db2.jcc.DB2Driver;sessionId=;Result=com.ibm.db2.jcc.DB2Driver;}

 

I would like to know how HPOO will establish a JDBC connection to any of the DB.  From where it will load the DB drivers and class to establish a connection with remote DB.  Do I have to place a db driver file in any location on HPOO server so that HPOO can load the driver from there. What input needs to be given for below two parameters.

 

dbClass

dbURL

 

I have given below inputs currently for above two parameters but it is not working.

 

dbClass : com.ibm.db2.jcc.DB2Driver

dbURL : jdbc:db2://<hostname>:<portnumber>/<DBname>

0 Likes
1 Solution

Accepted Solutions
Micro Focus Expert
Micro Focus Expert

Re: SQL query operations failing while connecting to DB2 databse through OO

Jump to solution

Hi,

If you open the properties on the JDBC folder you find instructions how to manage the db2 jdbc connector:

"By default, we only include the drivers for Oracle, MSSQL and Sybase. To enable other database type support like MySQL, DB2 or Netcool, you have two options:
Copy the JDBC connector e.g. mysql-connector-java-5.1.29-bin.jar into the installation folder of studio <STUDIO_HOME>.
2.1. For versions up to OO 10.01:
In studio : the JDBC driver would need to be copied to <STUDIO_HOME>/debugger/lib
In central: add a line to file <CENTRAL_HOME>/conf/central-wrapper.conf like this: "wrapper.java.classpath.4=c:\mysql-connector-java-5.1.29-bin.jar" and then restart OO central service.

2.2. For versions greater and including OO 10.02:
In studio: Create a folder in <STUDIO_HOME>\lib\ and name it ext. Create a folder in <STUDIO_HOME>\lib\ext\ and name it <Group Id>_<Artifact Id>_<Version>. Example: <STUDIO_HOME>\lib\ext\com.hp.oo_oo-sql-legacy-plugin_1.0.136 (where 1.0.136 is your actual content pack plugin version). Then copy the mysql connector in that folder.
For central: <OO_HOME>\central\tomcat\temp\ext\<Group Id>_<Artifact Id>_<Version>. Example: <OO_HOME>\central\tomcat\temp\ext\com.hp.oo_oo-sql-legacy-plugin_1.0.136 (where 1.0.136 is your actual content pack plugin version). Then copy the mysql connector in that folder.
For RAS: <OO_HOME>\ras\lib\ext\<Group Id>_<Artifact Id>_<Version>. Example: <OO_HOME>\ras\lib\ext\com.hp.oo_oo-sql-legacy-plugin_1.0.136 (where 1.0.136 is your actual content pack plugin version). Then copy the mysql connector in that folder.

2.3 For versions greater and including OO 10.20 you can create the folder without the version i.e. <Group Id>_<Artifact Id> so you will not need to modify the folder name every CP upgrade. Using <Group Id>_<Artifact Id>_<Version> is also supported but you need to provide the version of your actual content pack plugin version. If you are adding new custom content please take into consideration that this is under the assumption that every plugin exist only in one CP and exist only with one version.

In the example above where we have the plugin com.hp.oo_oo-sql-legacy-plugin_1.0.136 the group_id is com.hp.oo, the artifact_id is oo-sql-legacy-plugin and the version is 1.0.136.
Also the content pack plugin version will be the base content pack version deployed. Can be taken from any SQL operation in the JDBC folder. Also you can find there the group id and the artifact id. As stated in 2.3 in latest releases of OO, the version is not needed anymore"

Hope it helps,

Lucian

4 Replies
Micro Focus Expert
Micro Focus Expert

Re: SQL query operations failing while connecting to DB2 databse through OO

Jump to solution

Hi,

If you open the properties on the JDBC folder you find instructions how to manage the db2 jdbc connector:

"By default, we only include the drivers for Oracle, MSSQL and Sybase. To enable other database type support like MySQL, DB2 or Netcool, you have two options:
Copy the JDBC connector e.g. mysql-connector-java-5.1.29-bin.jar into the installation folder of studio <STUDIO_HOME>.
2.1. For versions up to OO 10.01:
In studio : the JDBC driver would need to be copied to <STUDIO_HOME>/debugger/lib
In central: add a line to file <CENTRAL_HOME>/conf/central-wrapper.conf like this: "wrapper.java.classpath.4=c:\mysql-connector-java-5.1.29-bin.jar" and then restart OO central service.

2.2. For versions greater and including OO 10.02:
In studio: Create a folder in <STUDIO_HOME>\lib\ and name it ext. Create a folder in <STUDIO_HOME>\lib\ext\ and name it <Group Id>_<Artifact Id>_<Version>. Example: <STUDIO_HOME>\lib\ext\com.hp.oo_oo-sql-legacy-plugin_1.0.136 (where 1.0.136 is your actual content pack plugin version). Then copy the mysql connector in that folder.
For central: <OO_HOME>\central\tomcat\temp\ext\<Group Id>_<Artifact Id>_<Version>. Example: <OO_HOME>\central\tomcat\temp\ext\com.hp.oo_oo-sql-legacy-plugin_1.0.136 (where 1.0.136 is your actual content pack plugin version). Then copy the mysql connector in that folder.
For RAS: <OO_HOME>\ras\lib\ext\<Group Id>_<Artifact Id>_<Version>. Example: <OO_HOME>\ras\lib\ext\com.hp.oo_oo-sql-legacy-plugin_1.0.136 (where 1.0.136 is your actual content pack plugin version). Then copy the mysql connector in that folder.

2.3 For versions greater and including OO 10.20 you can create the folder without the version i.e. <Group Id>_<Artifact Id> so you will not need to modify the folder name every CP upgrade. Using <Group Id>_<Artifact Id>_<Version> is also supported but you need to provide the version of your actual content pack plugin version. If you are adding new custom content please take into consideration that this is under the assumption that every plugin exist only in one CP and exist only with one version.

In the example above where we have the plugin com.hp.oo_oo-sql-legacy-plugin_1.0.136 the group_id is com.hp.oo, the artifact_id is oo-sql-legacy-plugin and the version is 1.0.136.
Also the content pack plugin version will be the base content pack version deployed. Can be taken from any SQL operation in the JDBC folder. Also you can find there the group id and the artifact id. As stated in 2.3 in latest releases of OO, the version is not needed anymore"

Hope it helps,

Lucian

Regular Contributor.. rvaghela Regular Contributor..
Regular Contributor..

Re: SQL query operations failing while connecting to DB2 databse through OO

Jump to solution

hi Lucian,

I have tried below mentioned steps but now i am getting below error.  i verfied that there is no firewall between hPOO studio and tagert DB server. i am able to connect on the DB port manually.  Any further help is appreciated.

 

{exception=java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
 at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
 at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
 at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
 at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
 at java.sql.DriverManager.getConnection(DriverManager.java:664)
 at java.sql.DriverManager.getConnection(DriverManager.java:247)
 at com.iconclude.content.actions.sql.dbconnection.DBConnectionManager.getPlainConnection(DBConnectionManager.java:549)
 at com.iconclude.content.actions.sql.dbconnection.DBConnectionManager.getConnection(DBConnectionManager.java:216)
 at com.iconclude.content.actions.sql.services.ConnectionService.obtainConnection(ConnectionService.java:140)
 at com.iconclude.content.actions.sql.services.ConnectionService.setUpConnection(ConnectionService.java:111)
 at com.iconclude.content.actions.sql.services.SQLQueryAllRowsService.execQueryAllRows(SQLQueryAllRowsService.java:31)
 at com.iconclude.content.actions.sql.SQLQueryAllRows.execute(SQLQueryAllRows.java:92)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:497)
 at com.hp.oo.sdk.plugins.abstracts.BaseActionPlugin.execute(BaseActionPlugin.java:53)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:497)
 at com.hp.oo.maven.PluginAdapterImpl.executePlugin(PluginAdapterImpl.java:328)
 at com.hp.oo.maven.PluginAdapterImpl.execute(PluginAdapterImpl.java:248)
 at com.hp.oo.execution.control.actions.contentexecution.ContentExecutionActions.executeContentAction(ContentExecutionActions.java:97)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:497)
 at io.cloudslang.worker.execution.reflection.ReflectionAdapterImpl.executeControlAction(ReflectionAdapterImpl.java:62)
 at io.cloudslang.worker.execution.services.ExecutionServiceImpl.executeStep(ExecutionServiceImpl.java:326)
 at io.cloudslang.worker.execution.services.ExecutionServiceImpl.execute(ExecutionServiceImpl.java:80)
 at io.cloudslang.worker.management.services.SimpleExecutionRunnable.executeRegularStep(SimpleExecutionRunnable.java:167)
 at io.cloudslang.worker.management.services.SimpleExecutionRunnable.run(SimpleExecutionRunnable.java:120)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
 at java.util.concurrent.FutureTask.run(FutureTask.java:266)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at io.cloudslang.worker.management.services.WorkerThreadFactory$1.run(WorkerThreadFactory.java:33)
 at java.lang.Thread.run(Thread.java:745)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
 at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:392)
 at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:434)
 at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:687)
 at oracle.net.ns.NSProtocol.connect(NSProtocol.java:247)
 at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
 at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
 ... 39 more
Caused by: java.net.ConnectException: Connection refused: connect
 at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
 at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:85)
 at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
 at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
 at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
 at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
 at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
 at java.net.Socket.connect(Socket.java:589)
 at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:150)
 at oracle.net.nt.ConnOption.connect(ConnOption.java:133)
 at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:370)
 ... 44 more

state:08006;returnCode=-1;returnResult=IO Error: The Network Adapter could not establish the connection;sessionId=;Result=IO Er

0 Likes
AndreiTruta Outstanding Contributor.
Outstanding Contributor.

Re: SQL query operations failing while connecting to DB2 databse through OO

Jump to solution

It might worth sharing the changes that you have performed based on Lucian's input.

What driver are you using?

PS: Not sure if it is the logs but I see above the oracle driver being used. I can only assume you should use one of these: http://www-01.ibm.com/support/docview.wss?uid=swg21363866 

Andrei Vasile Truta
0 Likes
Regular Contributor.. rvaghela Regular Contributor..
Regular Contributor..

Re: SQL query operations failing while connecting to DB2 databse through OO

Jump to solution

hi Andrei,

Thanks for your response.  My issue got resolved. i have performed the steps given by Lucian and after that there was an issue with one of the input due to which i was getting network adaptor error. i have corrected that and it started working for me.

Regards

Ravindra

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.