Using Postgresql under IBM WebSphere

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Some comments I have inlined...

> I used the JDBC driver of Postgresql 7.1.3 (which I compiled
> from the sources, as explained elsewhere on this list).
> But as far as I can see, my findings still apply in 7.2b3.
>
> 1) In org.postgresql.xa.XADataSourceImpl.getTxRecover(),
> the return statement:

I don't believe (but someone else may know better) that this is complete
anyways. Is it possible to get WebSphere to use a regular jdbc1 driver
(org.postgresql.Driver) rather than a data source? Another option is to use
PoolMan (www.codestudio.com) to get access to the org.postgresql.Driver class
since PoolMan can provide a DataSource object that calls the driver itself.

(I'm working on getting the driver up to jdbc2.0 and jdbc2.0Optional snuff,
but I'm not going to submit more code until after 7.2 is released since my
code is too new. I think to make WebSphere happy, you have to treat the
driver as a JDBC1 level driver directly with webspehere, or use PoolMan to
wrap a JDBC2 level driver/DataSource to make WebSphere happy)

> 3) Websphere frequently logs this warning:
>
> PortabilityLa W CONM0002W: Unrecognized database or driver
> org.postgresql.PostgresqlDataSource; using generic settings
>
> So is seems to me that Websphere expects something from data sources
> that it does not find with the Postgresql one.

Nah, I think its because WebSphere doesn't understand PostgreSQL specific
calls, so its using the driver generically. The DB2 driver has methods that
are specific to DB2 and WebSphere will 'cast' the driver to a know version to
access those methods. This shouldn't be a problem.

> 4) Configuring a Postgresql data source in WebSphere.
[..deleted..]
Thats a good list of info for configuring WebSphere.

> Now in order to get my limited success (inserted one row)
> I had to do a couple of patches that are *certainly* not solutions.
> In fact, those patches are the main reason why I pretend
> that Postgresql currently won't work under WebSphere.
> But they let me go ahead a bit.

> 5) In org.postgresql.xa.XADataSourceImpl.getXAConnection(String, String),
> I replaced the throw statement with 'return getXAConnection();'.

> 6) In org.postgresql.PostgresqlDataSource.getConnection(String, String)
> I supplied default user/password values when one is null,
> like this (that's about in the middle of the method):
>
> if ( user == null || password == null )
> {
> // Supply defaults if needed.
> if (user == null) user = "mf";
> if (password == null) password = "x";
> // throw new PSQLException( "postgresql.ds.userpswd" );
> }

I've posted (for use with 7.3) a class that reads properties for the
postgresql driver which I'm going to update shortly. It will help here by
having a place to look up defaults that the user/admin can control.

> In short, I have problems passing the user/password, regardless of where
> I specify them in my EJB code or in the data source definition in the
> WebSphere admin tool. It seems WebSphere always calls
> getXAConnection(String, String) and
> getConnection(String, String), perhaps with null values?

Does WebSphere require a JDBC2 level driver?

> And I have problems with transactions too, btw.

Are you having problems with the XA code base? I believe that the postgresql
driver code for the XA methods are incomplete. Also, WebSphere is likely
assuming the database is able to do two-phase commits which PostgreSQL
cannot. It would be interesting to see if wrapping with PoolMan provides
enough functionality to keep WebSphere happy enough to complete multiple
transactions.

Virtually,
Ned Wolpert <ned(dot)wolpert(at)knowledgenet(dot)com>

D08C2F45: 28E7 56CB 58AC C622 5A51 3C42 8B2B 2739 D08C 2F45
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE8BnGCiysnOdCML0URAitTAJ0d9ny5/codKN6tx9ZaRZ4u4vCLSACcDmMD
rcN1yN6PbCkrFotU5xHPowc=
=DTGQ
-----END PGP SIGNATURE-----

Hi,

I have recently tried to implement a J2EE/EJB entity bean
working in BMP mode against a Postgresql data base under
WebSphere, specifically IBM WebSphere Single Server 4.0.1
running under Red Hat Linux 7.1. This was just a test,
part of a study/learning project of mine.

I am been only (very) partly successful. In fact,
IMHO WebSphere currently won't work with Postgresql,
unfortunately. Anyway I must postpone this project for now.

Nevertheless, I have a few issues that I think are worth reporting,
** including one bug for sure **.

I used the JDBC driver of Postgresql 7.1.3 (which I compiled
from the sources, as explained elsewhere on this list).
But as far as I can see, my findings still apply in 7.2b3.

1) In org.postgresql.xa.XADataSourceImpl.getTxRecover(),
the return statement:

return (Xid[]) list.toArray();

always throws a ClassCastException, so that's the *bug*
that should be fixed. This alternative works:

Xid [] array = new Xid [list.size()];
return (Xid[]) list.toArray(array);

The WebSphere server often (but not always) calls
this method during its initialization. When it does,
it does not even initialize, because of this exception.

2) Properties files lack the 'postgresql.ds.userpswd' entry.

In 7.2b3, only 'error_de.properties' has it. Suggested:

(English) postgresql.ds.userpswd:The user and/or the password is null.
(French) postgresql.ds.userpswd:Le code d'usager et/ou le mot de passe
est nul.

3) Websphere frequently logs this warning:

PortabilityLa W CONM0002W: Unrecognized database or driver
org.postgresql.PostgresqlDataSource; using generic settings

So is seems to me that Websphere expects something from data sources
that it does not find with the Postgresql one.

4) Configuring a Postgresql data source in WebSphere.

I used the web-based WebSphere Admin tool to carry out this
prerequisite. I want to mention a few things here:

a) First, the Postgresql JDBC driver itself must be defined.
Expand 'Resources', then click on 'JDBC Drivers', and 'New'.

- Field 'Implementation Classname' must be
'org.postgresql.PostgresqlDataSource'.

- It is sufficient to place the jar file of the Postgresql
JDBC driver into the 'lib' subdirectory where WebSphere
is installed. I am not sure about the need/use of
field 'Server Class Path', which can contain anything
as long as directory 'lib' contains the needed jar.
Anyway, if class 'org.postgresql.PostgresqlDataSource'
cannot be found when requested, that will be clear
enough in the WebSphere logs.

b) Once the Postgresql JDBC driver is defined to WebSphere,
an actual data source must be defined under it.

- I used field 'Database Name' to specify the name of the
target Postgresql data base.
- I have specified values for fields 'Default User ID'
and 'Default Password', but I am not sure this was
needed or used (see below).
- I have not played with the many fields under
'Connection Pool Settings', I have only specified
the same values as other data sources already
defined in WebSphere (for the WebSphere samples).
- I have played with 'Driver Specific Settings', though,
and this is my main point here. After you click
'Properties', you may define these 4 properties
that the Postgresql data source actually supports:

Name Java type
serverName java.lang.String
portNumber java.lang.Integer
loginTimeout java.lang.Integer
transactionTimeout java.lang.Integer

I figured them using the Java source of
'org.postgresql.PostgresqlDataSource' class.
Names are case-sensitive. Be sure to specify
the correct data type.

So by specifying an IP address in 'serverName',
I successfully had WebSphere connect to
my Postgresql server on a separate machine of
my private LAN.

If you specify a property name that is not
supported, say XYZ, WebSphere will warn you
in its logs:

PortabilityLa W CONM7002W: Could not find the property XYZ on class
org.postgresql.PostgresqlDataSource

Now in order to get my limited success (inserted one row)
I had to do a couple of patches that are *certainly* not solutions.
In fact, those patches are the main reason why I pretend
that Postgresql currently won't work under WebSphere.
But they let me go ahead a bit.

5) In org.postgresql.xa.XADataSourceImpl.getXAConnection(String, String),
I replaced the throw statement with 'return getXAConnection();'.

6) In org.postgresql.PostgresqlDataSource.getConnection(String, String)
I supplied default user/password values when one is null,
like this (that's about in the middle of the method):

if ( user == null || password == null )
{
// Supply defaults if needed.
if (user == null) user = "mf";
if (password == null) password = "x";
// throw new PSQLException( "postgresql.ds.userpswd" );
}

(Now you know how I found about the missing 'postgresql.ds.userpswd').

In short, I have problems passing the user/password, regardless of where
I specify them in my EJB code or in the data source definition in the
WebSphere admin tool. It seems WebSphere always calls
getXAConnection(String, String) and
getConnection(String, String), perhaps with null values?

And I have problems with transactions too, btw.

Regards,

Marcel Frechette

Wow, that was a quick answer, thank you.

You're asking many questions that I simply do not have answers for yet.
All I can say is that I am pretty sure I can only use DataSource objects
(through JNDI from the WebSphere server), in order to get a Connection,
and not a JDBC driver directly. Also, I remember having seen somewhere
in the WebSphere doc mentions of supported drivers with one-phase commit,
so 2-phases commit would not be a strict requirement.

I will sure look into PoolMan, thanks.

It will be a while though, several days at least, before I
am in a position to resume work on this, sorry.

Thanks again,

Marcel Frechette (marcel(dot)frechette(at)videotron(dot)ca)