[Geoserver-users] Unable to connect to DB2

Luca_Morandini2 · April 8, 2006, 3:47pm

Folks,

I'm trying to connect to DB2, but so far without success:
1) I downloaded DB2 8.2
2) Added Spatial Extender
3) Imported a shapefile
4) Dropped relevant JARs into GeoServer
5) Restarted GeoServer
6) Tried to create a datastore (in GeoServer 1.3)

but...
"Connection Parameters are invalid"

Here's the log:
form connection params { user="db2admin", passwd="db2admin", host="bruegel", port="50000", database="gstest", tabschema="DB2GSE", dbtype="DB2"}
6428497 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
6428499 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.

Any clue ?

Regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Chris_Holmes1 · April 8, 2006, 5:35pm

David Adler is our DB2 module maintainer, perhaps he has some insight.

Chris

Luca Morandini wrote:

Folks,

I'm trying to connect to DB2, but so far without success:
1) I downloaded DB2 8.2
2) Added Spatial Extender
3) Imported a shapefile
4) Dropped relevant JARs into GeoServer
5) Restarted GeoServer
6) Tried to create a datastore (in GeoServer 1.3)

but...
"Connection Parameters are invalid"

Here's the log:
form connection params { user="db2admin", passwd="db2admin", host="bruegel", port="50000", database="gstest", tabschema="DB2GSE", dbtype="DB2"}
6428497 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
6428499 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.

Any clue ?

Regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Chris Holmes
The Open Planning Project
thoughts at: http://cholmes.wordpress.com

David_Adler · April 8, 2006, 8:14pm

Luca, where did you get the message "Connection Parameters are invalid"?

You should not be using a tabschema of DB2GSE - this is reserved for Spatial Extender objects.

How did you import the shapefile and what table schema did you use?

Normally after you get the trace message "No available connections ...", there should be a number of FINE trace records for org.geotools.data.db2.DB2SpatialCatalog listing the spatial columns.

What exact version of GeoServer are you using? I tried it today on my (older) GeoServer 1.3RC1 environment and it worked fine. I will try to download and install the latest GeoServer 1.3 code.

David

At 01:35 PM 4/8/2006, Chris Holmes wrote:

David Adler is our DB2 module maintainer, perhaps he has some insight.

Chris

Luca Morandini wrote:

Folks,
I'm trying to connect to DB2, but so far without success:
1) I downloaded DB2 8.2
2) Added Spatial Extender
3) Imported a shapefile
4) Dropped relevant JARs into GeoServer
5) Restarted GeoServer
6) Tried to create a datastore (in GeoServer 1.3)
but...
"Connection Parameters are invalid"
Here's the log:
form connection params { user="db2admin", passwd="db2admin", host="bruegel", port="50000", database="gstest", tabschema="DB2GSE", dbtype="DB2"}
6428497 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
6428499 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
Any clue ?
Regards,
--------------------
Luca Morandini
www.lucamorandini.it
--------------------

-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Chris Holmes
The Open Planning Project
thoughts at: http://cholmes.wordpress.com

David_Adler · April 8, 2006, 8:57pm

I installed GeoServer 1.3.0 and the "DB2 extras". Most of the files in the WEB-INF\lib directory have dates of 2/6/2006 except gt2-db2.jar which has 1/13/2006. db2jcc.jar has a date of 1/22/2006 (FP11).

Although it is unlikely to have any impact on this problem, did you download and install the latest DB2 and Spatial Extender fixpacks? If you are running on Windows, you need to install separate fixpack files. For other platforms it is a single install file.

David

At 01:35 PM 4/8/2006, Chris Holmes wrote:

David Adler is our DB2 module maintainer, perhaps he has some insight.

Chris

Luca Morandini wrote:

Folks,
I'm trying to connect to DB2, but so far without success:
1) I downloaded DB2 8.2
2) Added Spatial Extender
3) Imported a shapefile
4) Dropped relevant JARs into GeoServer
5) Restarted GeoServer
6) Tried to create a datastore (in GeoServer 1.3)
but...
"Connection Parameters are invalid"
Here's the log:
form connection params { user="db2admin", passwd="db2admin", host="bruegel", port="50000", database="gstest", tabschema="DB2GSE", dbtype="DB2"}
6428497 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
6428499 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
Any clue ?
Regards,
--------------------
Luca Morandini
www.lucamorandini.it
--------------------

-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
Chris Holmes
The Open Planning Project
thoughts at: http://cholmes.wordpress.com

Luca_Morandini2 · April 9, 2006, 6:42pm

David Adler wrote:

Luca, where did you get the message "Connection Parameters are invalid"?

Both in the web interface and in the log file.

You should not be using a tabschema of DB2GSE - this is reserved for Spatial Extender objects.

Ok, but the error remains even when I avoid to insert the tabschema,

How did you import the shapefile and what table schema did you use?

From the control center, and the schema is DB2GSE.

What exact version of GeoServer are you using? I tried it today on my (older) GeoServer 1.3RC1 environment and it worked fine. I will try to download and install the latest GeoServer 1.3 code.

1.3.0 PR1

Anyway, this is the complete Log listing (FINE level) produced when I try to connect a few times. As you may notice, the first time a failure loading the db2jcct2 library is issued... though I copied both db2jcc.jar and db2jcc_license_cu.jar in the lib directory of GeoServer.

20:01:23.642 EVENT Started org.mortbay.jetty.Server@anonymised.com
form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="gstest", tabschema="", dbtype="DB2"}
1432261 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Created new DB2ConnectionPoolDataSource for dbUrl jdbc:db2://bruegel:50000/gstest
1432280 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
1432452 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
com.ibm.db2.jcc.a.SqlException: Failure in loading T2 native library db2jcct2
         at com.ibm.db2.jcc.t2.a.a(a.java:31)
         at com.ibm.db2.jcc.t2.T2Configuration.<clinit>(T2Configuration.java:75)
         at com.ibm.db2.jcc.DB2PooledConnection.<init>(DB2PooledConnection.java:69)
         at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnectionX(DB2ConnectionPoolDataSource.java:147)
         at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnection(DB2ConnectionPoolDataSource.java:112)
         at org.geotools.data.jdbc.ConnectionPool.getConnection(ConnectionPool.java:121)
         at org.geotools.data.jdbc.JDBC2DataStore.createConnection(JDBC2DataStore.java:130)
         at org.geotools.data.jdbc.JDBC1DataStore.getConnection(JDBC1DataStore.java:1021)
         at org.geotools.data.db2.DB2DataStore.<init>(DB2DataStore.java:119)
         at org.geotools.data.db2.DB2DataStoreFactory.createDataStore(DB2DataStoreFactory.java:119)
         at org.vfny.geoserver.action.data.DataDataStoresEditorAction.execute(DataDataStoresEditorAction.java:130)
         at org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:98)
         at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
         at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
         at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
         at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
         at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:360)
         at org.mortbay.jetty.servlet.WebApplicationHandler$Chain.doFilter(WebApplicationHandler.java:328)
         at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:122)
         at org.mortbay.jetty.servlet.WebApplicationHandler$Chain.doFilter(WebApplicationHandler.java:320)
         at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:272)
         at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:558)
         at org.mortbay.http.HttpContext.handle(HttpContext.java:1717)
         at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:549)
         at org.mortbay.http.HttpContext.handle(HttpContext.java:1667)
         at org.mortbay.http.HttpServer.service(HttpServer.java:863)
         at org.mortbay.http.HttpConnection.service(HttpConnection.java:773)
         at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:937)
         at org.mortbay.http.HttpConnection.handle(HttpConnection.java:790)
         at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:201)
         at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:289)
         at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:455)
com.ibm.db2.jcc.a.SqlException: Failure in loading T2 native library db2jcct2
         at com.ibm.db2.jcc.t2.a.a(a.java:31)
         at com.ibm.db2.jcc.t2.T2Configuration.<clinit>(T2Configuration.java:75)
         at com.ibm.db2.jcc.DB2PooledConnection.<init>(DB2PooledConnection.java:69)
         at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnectionX(DB2ConnectionPoolDataSource.java:147)
         at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnection(DB2ConnectionPoolDataSource.java:112)
         at org.geotools.data.jdbc.ConnectionPool.getConnection(ConnectionPool.java:121)
         at org.geotools.data.jdbc.JDBC2DataStore.createConnection(JDBC2DataStore.java:130)
         at org.geotools.data.jdbc.JDBC1DataStore.getConnection(JDBC1DataStore.java:1021)
         at org.geotools.data.db2.DB2DataStore.<init>(DB2DataStore.java:119)
         at org.geotools.data.db2.DB2DataStoreFactory.createDataStore(DB2DataStoreFactory.java:119)
         at org.vfny.geoserver.action.data.DataDataStoresEditorAction.execute(DataDataStoresEditorAction.java:130)
         at org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:98)
         at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
         at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
         at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
         at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
         at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:360)
         at org.mortbay.jetty.servlet.WebApplicationHandler$Chain.doFilter(WebApplicationHandler.java:328)
         at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:122)
         at org.mortbay.jetty.servlet.WebApplicationHandler$Chain.doFilter(WebApplicationHandler.java:320)
         at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:272)
         at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:558)
         at org.mortbay.http.HttpContext.handle(HttpContext.java:1717)
         at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:549)
         at org.mortbay.http.HttpContext.handle(HttpContext.java:1667)
         at org.mortbay.http.HttpServer.service(HttpServer.java:863)
         at org.mortbay.http.HttpConnection.service(HttpConnection.java:773)
         at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:937)
         at org.mortbay.http.HttpConnection.handle(HttpConnection.java:790)
         at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:201)
         at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:289)
         at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:455)
form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="gstest", tabschema="", dbtype="DB2"}
1456376 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
1456378 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="gstest", tabschema="", dbtype="DB2"}
1466104 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
1466106 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="gstest", tabschema="", dbtype="DB2"}
1471492 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
1471494 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.

Thanks for your help,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 9, 2006, 6:44pm

David Adler wrote:

I installed GeoServer 1.3.0 and the "DB2 extras". Most of the files in the WEB-INF\lib directory have dates of 2/6/2006 except gt2-db2.jar which has 1/13/2006. db2jcc.jar has a date of 1/22/2006 (FP11).

Although it is unlikely to have any impact on this problem, did you download and install the latest DB2 and Spatial Extender fixpacks? If you are running on Windows, you need to install separate fixpack files. For other platforms it is a single install file.

Yes I did: Fixpack 11.

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 10, 2006, 12:24pm

David Adler wrote:

Hi Luca, thanks for this information - it is very helpful.

It appears that it is not finding db2jcct2.dll which is in \sqllib\bin. It you can get \sqllib\bin in your path, it may work.

I'm trying to connect from a Linux client (with GeoServer) to a Windows server (with DB2), hence I cannot implement your suggestion.

Anyway, I tried with uDIG on Windows, and the same happened... even when I put db2jcct2.dll in the path ! (there was a chain of other DLLs still missing).

This is a bit puzzling to me as I thought this was using the JDBC type 4 driver which I thought was "pure" Java. This will take some investigation.

Puzzling indeed, type4 was supposed to be an *universal* driver .

Can you post the results of running the following command:
db2jcc -configuration

Hmm... did you mean:
java -jar db2jcc.jar -configuration
?

If so, the answer is:
Failed to load Main-Class manifest attribute from
db2jcc.jar

Regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

David_Adler · April 10, 2006, 5:11pm

At 08:24 AM 4/10/2006, Luca Morandini wrote:

David Adler wrote:

Hi Luca, thanks for this information - it is very helpful.
It appears that it is not finding db2jcct2.dll which is in \sqllib\bin.
It you can get \sqllib\bin in your path, it may work.

I'm trying to connect from a Linux client (with GeoServer) to a Windows server (with DB2), hence I cannot implement your suggestion.

I'm not currently running GeoServer on Linux so don't have any good ideas. Did you install the DB2 client on Linux? This should ensure that the right libraries are in the right places. (You would need a DB2 for Linux install image. I don't think you would need to install Spatial Extender on Linux).

Anyway, I tried with uDIG on Windows, and the same happened... even when I put db2jcct2.dll in the path ! (there was a chain of other DLLs still missing).

uDIG also works fine for me on Windows. Do you have the DB2 client installed on this system?

This is a bit puzzling to me as I thought this was using the JDBC type 4 driver which I thought was "pure" Java. This will take some investigation.

Puzzling indeed, type4 was supposed to be an *universal* driver .

db2jcc.jar constitutes the DB2 "Universal JDBC Driver" which includes both type 2 and type 4 interfaces. The documentation states that db2jcct2.dll is needed for the type 2 driver. No mention about this for the type 4 driver interface. I'm trying to find out more internally from DB2 JDBC development.

Can you post the results of running the following command:
db2jcc -configuration

I thought you were running on Windows. On Linux it should be:
java com.ibm.db2.jcc.DB2Jcc -configuration
which probably assumes that db2jcc is in your classpath.

Unfortunately, one of my Linux systems is broken and the other is booted in Windows and can't be switched right now.

Hmm... did you mean:
java -jar db2jcc.jar -configuration
?

If so, the answer is:
Failed to load Main-Class manifest attribute from
db2jcc.jar

Regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Luca_Morandini2 · April 10, 2006, 6:31pm

David Adler wrote:

At 08:24 AM 4/10/2006, Luca Morandini wrote:

David Adler wrote:

Hi Luca, thanks for this information - it is very helpful.
It appears that it is not finding db2jcct2.dll which is in \sqllib\bin.
It you can get \sqllib\bin in your path, it may work.

I'm trying to connect from a Linux client (with GeoServer) to a Windows server (with DB2), hence I cannot implement your suggestion.

I'm not currently running GeoServer on Linux so don't have any good ideas. Did you install the DB2 client on Linux? This should ensure that the right libraries are in the right places. (You would need a DB2 for Linux install image. I don't think you would need to install Spatial Extender on Linux).

BTW, I wasn't able to find the client download on the IBM site, and the minimum installation for DB2 (on Windows at least) is 336 MB ! Is there anything lighter than that ?

Anyway, I tried with uDIG on Windows, and the same happened... even when I put db2jcct2.dll in the path ! (there was a chain of other DLLs still missing).

uDIG also works fine for me on Windows. Do you have the DB2 client installed on this system?

No, I thought it wasn't necessary.

This is a bit puzzling to me as I thought this was using the JDBC type 4 driver which I thought was "pure" Java. This will take some investigation.

Puzzling indeed, type4 was supposed to be an *universal* driver .

db2jcc.jar constitutes the DB2 "Universal JDBC Driver" which includes both type 2 and type 4 interfaces. The documentation states that db2jcct2.dll is needed for the type 2 driver. No mention about this for the type 4 driver interface. I'm trying to find out more internally from DB2 JDBC development.

Thanks.

I thought you were running on Windows. On Linux it should be:
java com.ibm.db2.jcc.DB2Jcc -configuration
which probably assumes that db2jcc is in your classpath.

Here you are:
java -cp db2jcc.jar com.ibm.db2.jcc.DB2Jcc -configuration
[ibm][db2][jcc] BEGIN TRACE_DRIVER_CONFIGURATION
[ibm][db2][jcc] Driver: IBM DB2 JDBC Universal Driver Architecture 2.8.46
[ibm][db2][jcc] Compatible JRE versions: { 1.3, 1.4, 1.5 }
[ibm][db2][jcc] Target server licensing restrictions: { z/OS: disabled; SQLDS: disabled; iSeries: disabled; DB2 for Unix/Windows: disabled; Cloudscape: disabled }
[ibm][db2][jcc] Range checking enabled: true
[ibm][db2][jcc] Bug check level: 0xff
[ibm][db2][jcc] Default fetch size: 64
[ibm][db2][jcc] Default isolation: 2
[ibm][db2][jcc] Collect performance statistics: false
[ibm][db2][jcc] No security manager detected.
[ibm][db2][jcc] Detected local client host: cranach/192.168.12.2
[ibm][db2][jcc] Access to package sun.io is permitted by security manager.
[ibm][db2][jcc] JDBC 1 system property jdbc.drivers = null
[ibm][db2][jcc] Java Runtime Environment version 1.4.2_07
[ibm][db2][jcc] Java Runtime Environment vendor = Sun Microsystems Inc.
[ibm][db2][jcc] Java vendor URL = http://java.sun.com/
[ibm][db2][jcc] Java installation directory = /j2sdk1.4.2_07/jre
[ibm][db2][jcc] Java Virtual Machine specification version = 1.0
[ibm][db2][jcc] Java Virtual Machine specification vendor = Sun Microsystems Inc.
[ibm][db2][jcc] Java Virtual Machine specification name = Java Virtual Machine Specification
[ibm][db2][jcc] Java Virtual Machine implementation version = 1.4.2_07-b05
[ibm][db2][jcc] Java Virtual Machine implementation vendor = Sun Microsystems Inc.
[ibm][db2][jcc] Java Virtual Machine implementation name = Java HotSpot(TM) Client VM
[ibm][db2][jcc] Java Runtime Environment specification version = 1.4
[ibm][db2][jcc] Java Runtime Environment specification vendor = Sun Microsystems Inc.
[ibm][db2][jcc] Java Runtime Environment specification name = Java Platform API Specification
[ibm][db2][jcc] Java class format version number = 48.0
[ibm][db2][jcc] Java class path = db2jcc.jar
[ibm][db2][jcc] Java native library path = /j2sdk1.4.2_07/jre/lib/i386/client:/j2sdk1.4.2_07/jre/lib/i386:/j2sdk1.4.2_07/jre/../lib/i386::/usr/local/apps/ArcIMS/lib:/usr/local/apps/ArcIMS/bin:/usr/local/apps/oracle/product/10.1.0/db_1/lib:/lib:/usr/libexport:/usr/local/apps/oracle/product/10.1.0/db_1/lib/gcc3
[ibm][db2][jcc] Path of extension directory or directories = /j2sdk1.4.2_07/jre/lib/ext
[ibm][db2][jcc] Operating system name = Linux
[ibm][db2][jcc] Operating system architecture = i386
[ibm][db2][jcc] Operating system version = 2.6.9-1.667
[ibm][db2][jcc] File separator ("/" on UNIX) = /
[ibm][db2][jcc] Path separator (":" on UNIX) = :
[ibm][db2][jcc] User's account name = root
[ibm][db2][jcc] User's home directory = /root
[ibm][db2][jcc] User's current working directory = /usr/local/apps/jettydev/webapps/geoserver/WEB-INF/lib
[ibm][db2][jcc] Dumping all system properties: { java.runtime.name=Java(TM) 2 Runtime Environment, Standard Edition, sun.boot.library.path=/j2sdk1.4.2_07/jre/lib/i386, java.vm.version=1.4.2_07-b05, java.vm.vendor=Sun Microsystems Inc., java.vendor.url=http://java.sun.com/, path.separator=:, java.vm.name=Java HotSpot(TM) Client VM, file.encoding.pkg=sun.io, user.country=US, sun.os.patch.level=unknown, java.vm.specification.name=Java Virtual Machine Specification, user.dir=/usr/local/apps/jettydev/webapps/geoserver/WEB-INF/lib, java.runtime.version=1.4.2_07-b05, java.awt.graphicsenv=sun.awt.X11GraphicsEnvironment, java.endorsed.dirs=/j2sdk1.4.2_07/jre/lib/endorsed, os.arch=i386, java.io.tmpdir=/tmp, line.separator=\n, java.vm.specification.vendor=Sun Microsystems Inc., os.name=Linux, sun.java2d.fontpath=, java.library.path=/j2sdk1.4.2_07/jre/lib/i386/client:/j2sdk1.4.2_07/jre/lib/i386:/j2sdk1.4.2_07/jre/../lib/i386::/usr/local/apps/ArcIMS/lib:/usr/local/apps/ArcIMS/bin:/usr/local/apps/oracle/product/10.1.0/db_1/lib:/lib:/usr/libexport:/usr/local/apps/oracle/product/10.1.0/db_1/lib/gcc3, java.specification.name=Java Platform API Specification, java.class.version=48.0, java.util.prefs.PreferencesFactory=java.util.prefs.FileSystemPreferencesFactory, os.version=2.6.9-1.667, user.home=/root, user.timezone=, java.awt.printerjob=sun.print.PSPrinterJob, file.encoding=UTF-8, java.specification.version=1.4, java.class.path=db2jcc.jar, user.name=root, java.vm.specification.version=1.0, java.home=/j2sdk1.4.2_07/jre, sun.arch.data.model=32, user.language=en, java.specification.vendor=Sun Microsystems Inc., java.vm.info=mixed mode, java.version=1.4.2_07, java.ext.dirs=/j2sdk1.4.2_07/jre/lib/ext, sun.boot.class.path=/j2sdk1.4.2_07/jre/lib/rt.jar:/j2sdk1.4.2_07/jre/lib/i18n.jar:/j2sdk1.4.2_07/jre/lib/sunrsasign.jar:/j2sdk1.4.2_07/jre/lib/jsse.jar:/j2sdk1.4.2_07/jre/lib/jce.jar:/j2sdk1.4.2_07/jre/lib/charsets.jar:/j2sdk1.4.2_07/jre/classes, java.vendor=Sun Microsystems Inc., file.separator=/, java.vendor.url.bug=http://java.sun.com/cgi-bin/bugreport.cgi, sun.io.unicode.encoding=UnicodeLittle, sun.cpu.endian=little, sun.cpu.isalist= }
[ibm][db2][jcc] END TRACE_DRIVER_CONFIGURATION

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 11, 2006, 9:43am

David Adler wrote:

Can you try the attached jar and see if it fixes your problem?

I did this, and, actually, it didn't complain anymore about the native code.
But, unfortunately, it still complains (in the web interface) about the wrong connection parameters, here's the log (I tried with both both lower- and uppercase database name):

form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="gstest", tabschema="", dbtype="DB2"}
568907 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Created new DB2ConnectionPoolDataSource for dbUrl jdbc:db2://bruegel:50000/gstest
568911 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
568947 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="GSTEST", tabschema="", dbtype="DB2"}
676071 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Created new DB2ConnectionPoolDataSource for dbUrl jdbc:db2://bruegel:50000/GSTEST
676075 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
676077 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

David_Adler · April 11, 2006, 1:05pm

I can see the error message one gets in the GeoServer GUI by specifying invalid parameters. It is surprising that there isn't more helpful information in the trace when the parameters are invalid.

Can you try the attached an updated gt2-db2 which should return an additional trace line if the connection fails - it should detect invalid server name, socket failure on port, database not found and invalid user/password as distinct problems.

I assume that you can 'ping bruegel' successfully from the system where you are running GeoServer. You could try replacing 'bruegel' with the IP address of your server in your GeoServer datasource creation, although I doubt this will make a difference.

Other possibilities:
1. Firewall on client restricting port 50000 access
2. Firewall on server restricting port 50000 access
3. Service not correct in \etc\services on server. Should have the entry:
db2c_DB2 50000/tcp
4. Service name not set in DB2 server configuration. Run in a DB2 command window on server
db2 get dbm cfg | grep SVC
should return
(SVCENAME) = db2c_DB2
5. DB2COMM environment variable not set correctly
run the command
db2set
It should return, among other things:
DB2COMM=TCPIP

BTW, it doesn't cause an error, but if you don't specify tabschema, you won't get any layers recognized. tabschema is case sensitive.

I'm sorry you are encountering such difficulties with this - it isn't usual.

At 05:43 AM 4/11/2006, Luca Morandini wrote:

David Adler wrote:

Can you try the attached jar and see if it fixes your problem?

I did this, and, actually, it didn't complain anymore about the native code.
But, unfortunately, it still complains (in the web interface) about the wrong connection parameters, here's the log (I tried with both both lower- and uppercase database name):

form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="gstest", tabschema="", dbtype="DB2"}
568907 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Created new DB2ConnectionPoolDataSource for dbUrl jdbc:db2://bruegel:50000/gstest
568911 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
568947 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
form connection params { user="db2admin", passwd="adminpw", host="bruegel", port="50000", database="GSTEST", tabschema="", dbtype="DB2"}
676071 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Created new DB2ConnectionPoolDataSource for dbUrl jdbc:db2://bruegel:50000/GSTEST
676075 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
676077 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

gt2-db2.jar (29.9 KB)

Luca_Morandini2 · April 11, 2006, 1:55pm

David Adler wrote:

I can see the error message one gets in the GeoServer GUI by specifying invalid parameters. It is surprising that there isn't more helpful information in the trace when the parameters are invalid.

Can you try the attached an updated gt2-db2 which should return an additional trace line if the connection fails - it should detect invalid server name, socket failure on port, database not found and invalid user/password as distinct problems.

Did it, and managed to build (yes !) a datastore... but not a feature type (log follows this message):

I assume that you can 'ping bruegel' successfully from the system where you are running GeoServer. You could try replacing 'bruegel' with the IP address of your server in your GeoServer datasource creation, although I doubt this will make a difference.

Other possibilities:
1. Firewall on client restricting port 50000 access
2. Firewall on server restricting port 50000 access

The firewall is external to this client-server connection.

3. Service not correct in \etc\services on server. Should have the entry:
db2c_DB2 50000/tcp

It has.

4. Service name not set in DB2 server configuration. Run in a DB2 command window on server
db2 get dbm cfg | grep SVC
should return
(SVCENAME) = db2c_DB2

It does.

5. DB2COMM environment variable not set correctly
run the command
db2set
It should return, among other things:
DB2COMM=TCPIP

It does.

BTW, it doesn't cause an error, but if you don't specify tabschema, you won't get any layers recognized. tabschema is case sensitive.

Right, I put "DB2GSE".

I'm sorry you are encountering such difficulties with this - it isn't usual.

It happens
BTW, thanks for taking the time to trying solve my problem.

Regards,

1905599 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
1905601 [FINE] org.geotools.data.jdbc.ConnectionPool - Getting available connection.
1905604 [FINE] org.geotools.data.jdbc.ConnectionPool$ConnectionListManager - Connection closed - adding to available connections.
1905607 [INFO] org.geotools.data.db2.DB2DataStoreFactory - Successfully created DB2Datastore for: jdbc:db2://bruegel:50000/gstest
1905608 [FINE] org.geotools.data.jdbc.ConnectionPool - Getting available connection.
1905615 [FINE] org.geotools.data.jdbc.ConnectionPool$ConnectionListManager - Connection closed - adding to available connections.
org.geotools.data.SchemaNotFoundException: Feature type could not be found for COMUNI
         at org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getPkColumnInfo(DefaultFIDMapperFactory.java:299)
         at org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getMapper(DefaultFIDMapperFactory.java:94)
         at org.geotools.data.db2.DB2FIDMapperFactory.getMapper(DB2FIDMapperFactory.java:76)
         at org.geotools.data.jdbc.JDBC1DataStore.buildFIDMapper(JDBC1DataStore.java:1065)
         at org.geotools.data.jdbc.FeatureTypeHandler.getFIDMapper(FeatureTypeHandler.java:233)
         at org.geotools.data.jdbc.FeatureTypeHandler.getFeatureTypeInfo(FeatureTypeHandler.java:199)
         at org.geotools.data.jdbc.FeatureTypeHandler.getSchema(FeatureTypeHandler.java:168)
         at org.geotools.data.jdbc.JDBC1DataStore.getSchema(JDBC1DataStore.java:388)
         at org.vfny.geoserver.action.data.DataFeatureTypesNewAction.execute(DataFeatureTypesNewAction.java:86)
         at org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:98)
         at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
         at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
         at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
         at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
         at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
         at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:360)
         at org.mortbay.jetty.servlet.WebApplicationHandler$Chain.doFilter(WebApplicationHandler.java:328)
         at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:122)
         at org.mortbay.jetty.servlet.WebApplicationHandler$Chain.doFilter(WebApplicationHandler.java:320)
         at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler.java:272)
         at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:558)
         at org.mortbay.http.HttpContext.handle(HttpContext.java:1717)
         at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.java:549)
         at org.mortbay.http.HttpContext.handle(HttpContext.java:1667)
         at org.mortbay.http.HttpServer.service(HttpServer.java:863)
         at org.mortbay.http.HttpConnection.service(HttpConnection.java:773)
         at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:937)
         at org.mortbay.http.HttpConnection.handle(HttpConnection.java:790)
         at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:201)
         at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:289)
         at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:455)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC: SYSIBM.SQLPRIMARYKEYS;PRIMARYKEYS;SYSIBM:CLI:-805
         at com.ibm.db2.jcc.a.rf.d(rf.java:1396)
         at com.ibm.db2.jcc.b.jb.l(jb.java:356)
         at com.ibm.db2.jcc.b.jb.e(jb.java:92)
         at com.ibm.db2.jcc.b.w.e(w.java:72)
         at com.ibm.db2.jcc.b.cc.i(cc.java:202)
         at com.ibm.db2.jcc.a.rf.q(rf.java:1363)
         at com.ibm.db2.jcc.a.sf.d(sf.java:2350)
         at com.ibm.db2.jcc.a.tf.X(tf.java:188)
         at com.ibm.db2.jcc.a.t.a(t.java:7199)
         at com.ibm.db2.jcc.a.t.d(t.java:6561)
         at com.ibm.db2.jcc.a.t.getPrimaryKeys(t.java:6489)
         at org.geotools.data.jdbc.fidmapper.DefaultFIDMapperFactory.getPkColumnInfo(DefaultFIDMapperFactory.java:253)

--------------------
Luca Morandini
www.lucamorandini.it
--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 11, 2006, 3:47pm

David Adler wrote:

Dare I ask what allowed you to get past the initial problem?

Of course you may dare: it was the wrong user/password (it worked after I inputted the Windows administrator's login/password).

After it created the connection, did it put a list of features in the trace?

Yes.

What were the subsequent steps that you went through up to the point of the failure?

I tried to make a featuretype out of the datastore. BTW, it shows "Feature type could not be found for COMUNI" in the web interface.

Do you have a primary key column defined on your table? I assume your table name is DB2GSE.COMMUNI.

SE made one: SE_ROW_ID

I strongly recommend that you import your data using a different schema name than DB2GSE, although I don't think it is related to this problem.

I will try this.

One thing you could try is to issue the following command in a DB2 command window on your server in the sqllib\bnd directory
connect to <dbname>
db2 bind @db2ubind.lst blocking all grant public
db2 bind @db2cli.lst blocking all grant public

Did you mean "from a command prompt" ?
Actually, when I tried that from clp it didn't work, while it did from the "DOS" window. Anyway, It didn't work.

Regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 12, 2006, 2:14pm

David Adler wrote:

If you are on Windows, Start->IBM DB2->Command Line Tools->Command Window
Are you sure you connected and bound to the right database?

I did the following:
1) Opened "Command window"-2
2) Issued: cd ..\bnd
3) Issued: db2 onnect to gstest
4) Issued: db2 bind @db2ubind.lst blocking all grant public
5) Issued: db2 bind @db2ubind.lst blocking all grant public

Sorry to bother you, but I'm just a beginner when it comes to DB2.

I could produce a similar problem in GeoServer by dropping all the DB2 CLI-related packages; doing the bind removed the problem.

Not on my machine
BTW, I didn't specify an SRS for the COMUNI table: could this explain the problem ?

Some other diagnostic steps:
On your server, in a DB2 Command Window
- go to sqllib\db2
- erase db2diag.log
- run the command:
db2 update dbm cfg using DIAGLEVEL 4
- run GeoServer to recreate the problem
- look in db2dia.log for '805' error message with some additional description

I did this, but no "805" showed up

And the "Feature type could not be found for COMUNI" is still there... though, when creating the datastore, GeoServer logs messages that seem to indicate a success:

21482060 [INFO] org.vfny.geoserver.servlets.AbstractService - Service handled
WARN [PoolThread-6] (HttpMethodBase.java:694) - Going to buffer response body of large or unknown size. Using getResponseAsStream instead is recommended.
form connection params { user="Administrator", passwd="password", host="bruegel", port="50000", database="GSTEST", tabschema="DB2GSE", dbtype="DB2"}
29828065 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Created new DB2ConnectionPoolDataSource for dbUrl jdbc:db2://bruegel:50000/GSTEST
29828745 [INFO] org.geotools.data.db2.DB2ConnectionFactory - Successfully obtained DB2 ConnectionPool
29828811 [FINE] org.geotools.data.jdbc.ConnectionPool - No available connections, creating a new one.
29830161 [FINE] org.geotools.data.db2.DB2SpatialCatalog - Spatial column: DB2GSE.COMUNI(SHAPE); srid=0 UNSPECIFIED=null:0
29830249 [FINE] org.geotools.data.jdbc.ConnectionPool$ConnectionListManager - Connection closed - adding to available connections.
29830409 [INFO] org.geotools.data.db2.DB2DataStoreFactory - Successfully created DB2Datastore for: jdbc:db2://bruegel:50000/GSTEST
typeNames : ("COMUNI")

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 12, 2006, 9:44pm

David Adler wrote:

Did you still get the SQLException with the 805 as you got before?
If so, where in the trace does it occur?

No, I don't.

The trace entry for DB2SpatialCatalog shows the EPSG number of each
layer it finds if the data in the db2 column has an SRID other than 0
or SRS_NAME other than DEFAULT_SRS.

You can't create a feature type with an SRS of 0. The GeoServer
dialog should give you a message about unknown EPSG_NUMBER.

Hmmm... it doesn't, it just refuses to create the new featuretype.

What data are you working with? When you import it into DB2 Spatial
Extender, you need to specify an SRS_NAME. You shouldn't use
DEFAULT_SRS. If your data is in latitude/longitude for WGS84, you
should use the WGS84_SRS_1003. This has an EPSG number of 4326.

Right, but I my data have linear coordinates, and I wasn't able to find any projected SRS in the import geographic data dialog; hence, I used DEFAULT_SRS (by the way, my data are in WGS84 UTM Zone 32N).

It is not essential but it is a good idea to register the spatial
column with DB2 Spatial Extender. Run db2se register_spatial_column for the parameters - it is pretty obvious.

The spatial column appears as registered in the control center.

regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 13, 2006, 12:46pm

David Adler wrote:

Hi Luca, I think we are close.

You must have a defined EPSG number for the SRS in GeoServer - it won't create a feature type otherwise.

I've followed your instructions, but when I tried to create a new SRS, it gives me:
GSE0207N A VALUES statement failed. SQLERROR = "SQL0805N Package "DB2GSE.GSESCFCT 0X4141414141594955" was not found. SQLSTATE=51002".

It seems I have do add some more DB2 packages...

Regards,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Luca_Morandini2 · April 13, 2006, 8:22pm

David Adler wrote: Hmmm, it is strange that you would get this error
if you were able to successfully import the shapefile previously.

The usual approach to fix this is to rebind the spatial extender: -
cd to \sqllib\bnd - connect to database - db2 bind @db2gse.lst

I now succeeded in the creation of a new SRS
I then imported my shapefile and tried to make a featuretype out of it: no avail (same error "Feature type could not be found for COMUNI").

But sometimes the error you report is due to an inconsistent DB2
installation.

What do you get when you run the command: db2level

DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08024" with
level identifier "03050106".
Informational tokens are "DB2 v8.1.11.973", "s060120", "WR21365", and FixPak
"11".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB".

If you list the contents of \sqllib\function, do all the DLLs have
the same date? The db2gse*.dll should have the same date as the
other DLLS.

Yes: 22 Jan 06.

BTW, I'll be out of office for the next 10 days, hence, I'll try this later on.

Thanks for your patience,

--------------------
Luca Morandini
www.lucamorandini.it
--------------------

Marcelo · September 3, 2024, 2:57am

Hi

My name is Marcelo, I am a systems analyst at SANEAGO, and I am responsible for technical support for the IBM i operating system with DB2/400 database.

In our company, we work with tools that use georeference/geospatial data, and we are working to implement the Geoserver tool, but when we searched for an extension that communicates with our DB2/400 database, we did not find it. We tried to use the extension developed by Mr. David Adler, but it did not work.

The extension developed by Mr. David Adler uses the “Universal JDBC Driver - DB2 Connect” present in the db2jcc.jar package and accesses the DB2GSE tabschema. Now, in our IBM i environment, we use the jt400.jar package to connect to DB2/400 and the georeference/geospatial views are in the QSYS2 tabschema.

I would like to know if it is possible to adapt the current DB2 extension to our DB2/400 environment?

Thank you very much!