Pinned topic DB2 connection problems when running a project on Tomcat

Paoloc,

Is it possible this is a case issue? I think JNDI is case sensitive and your database looks like it is NOVA02 and your JNDI name is nova02. I am not sure but maybe its worth a try to put them all in the same case just to see.

James

RBD 8.5.1, WAS 7

Hi Paoloc,

Some things to check:

- did you copy the db2-drivers to the tomcat-server?

- are you using the same version of tomcat on both servers?

- are you using the same db2-drivers on both servers?

Regards,

Bernd.

• Berndyman 120000Q3TE
• ‏2013-07-29T12:21:22Z

Hi Paoloc,

Some things to check:

- did you copy the db2-drivers to the tomcat-server?

- are you using the same version of tomcat on both servers?

- are you using the same db2-drivers on both servers?

Regards,

Bernd.

More...

I have the db2 drivers in the projects WEB-INF/lib and no db2 drivers in Tomcat

Tomcat 6.0.37 on development and 6.0.35 on runtime. 

• paoloc 1200006HSR
• ‏2013-07-29T14:40:48Z

I have the db2 drivers in the projects WEB-INF/lib and no db2 drivers in Tomcat

Tomcat 6.0.37 on development and 6.0.35 on runtime. 

More...

Paoloc,

A couple of other questions:

a.) I assume the context.xml looks the same?

b.) you are connecting to localhost:50000 which means Tomcat and the database are on the same IP address.  Is this the case for the production system? 

c.)  Are you sure the userid "x" has been defined in the target iSeries machine...with that password and it has not expired, etc.?

Mark

• markevans 120000NQBF
• ‏2013-07-29T20:58:47Z

Paoloc,

A couple of other questions:

a.) I assume the context.xml looks the same?

b.) you are connecting to localhost:50000 which means Tomcat and the database are on the same IP address.  Is this the case for the production system? 

c.)  Are you sure the userid "x" has been defined in the target iSeries machine...with that password and it has not expired, etc.?

 

Mark

More...

Mark!

Yes the two servers are identical.

Only one difference:

The developping PC have user x as Aministrator. The runtime as another user Administrator.

When I install DB2 in the rubtime server I use user:db2admin password:db2admi.

After installation I execute in the command editor the attached command that contains the grant comman for all tables for user x.

The command ends OK and user x have all permissions.

Therefor the only difference is that in the runtime server the user x is not defined at system level.

Is this the problem?

paoloc

• paoloc 1200006HSR
• ‏2013-07-31T07:46:08Z

Mark!

Yes the two servers are identical.

Only one difference:

The developping PC have user x as Aministrator. The runtime as another user Administrator.

When I install DB2 in the rubtime server I use user:db2admin password:db2admi.

After installation I execute in the command editor the attached command that contains the grant comman for all tables for user x.

The command ends OK and user x have all permissions.

Therefor the only difference is that in the runtime server the user x is not defined at system level.

Is this the problem?

paoloc

 

More...

Paoloc,

Yes that would be the problem as I understand things.   The GRANT only says IF user x logs in that it has authority to use the tables.  

But the login has to be successful first, so the userid id you are using (x in this case) needs to exist  on that system and have the same password set that you are sending in.

Mark

• markevans 120000NQBF
• ‏2013-07-31T13:49:20Z

Paoloc,

 

Yes that would be the problem as I understand things.   The GRANT only says IF user x logs in that it has authority to use the tables.  

But the login has to be successful first, so the userid id you are using (x in this case) needs to exist  on that system and have the same password set that you are sending in.

Mark

More...

Mark!

I do not understand anything anymore !!

I am referring only to the development machine.

I changed context.xml to:

The db2admin user exist on the system with password:db2admin.

Now the error is the same as:

https://www.ibm.com/developerworks/community/forums/html/topic?id=1e495dfa-a3dd-46fc-b975-f76d3ae1a038&ps=25

where MESSAGGI.MECODORG is the first filed for the first table opened by the project.

Is this a DB2 or EGL or Tomcat problem?

paoloc

• paoloc 1200006HSR
• ‏2013-08-01T14:29:01Z

Mark!

I do not understand anything anymore !!

I am referring only to the development machine.

I changed context.xml to:

<?xml version="1.0" encoding="UTF-8"?><Context>

<Resource auth="Container" driverClassName="com.ibm.db2.jcc.DB2Driver" maxActive="4" maxIdle="2" maxWait="5000" name="jdbc/nova02" password="db2admin" type="javax.sql.DataSource" url="jdbc:db2://localhost:50000/NOVA02:retrieveMessagesFromServerOnGetMessage=true;" username="db2admin"/>

</Context>

The db2admin user exist on the system with password:db2admin.

Now the error is the same as:

https://www.ibm.com/developerworks/community/forums/html/topic?id=1e495dfa-a3dd-46fc-b975-f76d3ae1a038&ps=25

where MESSAGGI.MECODORG is the first filed for the first table opened by the project.

Is this a DB2 or EGL or Tomcat problem?

paoloc

More...

Paoloc,

I am having a hard time keeping track of the problems you are having .

This thread originally was that you could not connect to the production server due to the following message.  You were not having any problem on the development machine:

EGL0505E Can not connect to jdbc/nova02: Cannot create PoolableConnectionFactory ([jcc][t4][2013][11249][3.63.75] Authorization for the failed connection.Reason: ID user or password invalid. ERRORCODE=-4214, SQLSTATE=28000) EGL0002I L'errore si è verificato in quantiMessaggi durante l'elaborazione della funzione messUrgenti.

After the exchange of replies in this thread, it seems the reason was that the "x" userid was not defined in the production system.   I assumed your approach to fix the problem for production was to define the "x" userid.

In the other thread, you just a different server (not your development one) you got the -206 message.

I think now you are saying you changed the userid you are connecting with from 'x" to "db2admin" on your development machine and now you are getting a -206.

Can you clear up what you are seeing? 

Also, have you granted the same permissions to db2admin as you have for "x"?   Not sure if that has anything to with it..but it could.

I don't think this is a "problem" with any of the 3 products you listed.   I think it is just a problem getting all of the settings/permissions/userids in sync.

• markevans 120000NQBF
• ‏2013-08-01T15:54:31Z

Paoloc,

I am having a hard time keeping track of the problems you are having .

This thread originally was that you could not connect to the production server due to the following message.  You were not having any problem on the development machine:

EGL0505E Can not connect to jdbc/nova02: Cannot create PoolableConnectionFactory ([jcc][t4][2013][11249][3.63.75] Authorization for the failed connection.Reason: ID user or password invalid. ERRORCODE=-4214, SQLSTATE=28000) EGL0002I L'errore si è verificato in quantiMessaggi durante l'elaborazione della funzione messUrgenti.

After the exchange of replies in this thread, it seems the reason was that the "x" userid was not defined in the production system.   I assumed your approach to fix the problem for production was to define the "x" userid.

In the other thread, you just a different server (not your development one) you got the -206 message.

I think now you are saying you changed the userid you are connecting with from 'x" to "db2admin" on your development machine and now you are getting a -206.

Can you clear up what you are seeing? 

Also, have you granted the same permissions to db2admin as you have for "x"?   Not sure if that has anything to with it..but it could.

I don't think this is a "problem" with any of the 3 products you listed.   I think it is just a problem getting all of the settings/permissions/userids in sync.

 

 

More...

Thanks Mark !

I'm testing this problem on the development PC because I think that if I solve it on this pc, I are close to the solution. On the development I have always worked with user x. Now, changing with user db2admin in context.xml  the problem arises.

As You can see on Image01 both users , x and db2admin, exist and are declared Arministrators and db2admin has the password: db2admin.

On DB2 db2admin has all grant privilegies (Image02) as well as x (Image03).

As You say it should work but instead with db2admin on context.xml I report the error -206.

I can not understand.

paoloc

• paoloc 1200006HSR
• ‏2013-08-01T16:39:00Z

Thanks Mark !

I'm testing this problem on the development PC because I think that if I solve it on this pc, I are close to the solution. On the development I have always worked with user x. Now, changing with user db2admin in context.xml  the problem arises.

As You can see on Image01 both users , x and db2admin, exist and are declared Arministrators and db2admin has the password: db2admin.

On DB2 db2admin has all grant privilegies (Image02) as well as x (Image03).

As You say it should work but instead with db2admin on context.xml I report the error -206.

I can not understand.

paoloc

 

More...

paoloc,

I don't specifically what is causing the issue or why it would change when you use a different userid, but I am pretty sure it has to do with the fact that you have qualified column names.

In other words, you are qualifying the column names with the table name (MESSAGGI.MECODORG). 

It is like that in the SQL statement because the column name references in the record include the table name as well.

At least for this query to see if it resolves it...can you remove the MESSAGI. from all the column name references clauses in the SELECT statement  .  You will also need to remove them from the record defintion..as the implicit INTO matches the "from list" with the columnName property to know which host variable to store the result in.

Doing this could mess up other queries...unless this is the only one using qualified column names.

If the one works, you might need to see if someone with better DB2 skills (maybe through a PMR) can explain why it is changing based on userid.

One question that I just thought of... any chance you have a view defined??

• markevans 120000NQBF
• ‏2013-08-01T16:52:05Z

paoloc,

I don't specifically what is causing the issue or why it would change when you use a different userid, but I am pretty sure it has to do with the fact that you have qualified column names.

In other words, you are qualifying the column names with the table name (MESSAGGI.MECODORG). 

It is like that in the SQL statement because the column name references in the record include the table name as well.

At least for this query to see if it resolves it...can you remove the MESSAGI. from all the column name references clauses in the SELECT statement  .  You will also need to remove them from the record defintion..as the implicit INTO matches the "from list" with the columnName property to know which host variable to store the result in.

Doing this could mess up other queries...unless this is the only one using qualified column names.

If the one works, you might need to see if someone with better DB2 skills (maybe through a PMR) can explain why it is changing based on userid.

One question that I just thought of... any chance you have a view defined??

 

 

More...

Hi Mark!

you're right , the problem was the table name before the field name.

Unfortunately I have to tell you again that this is a EGL problem.

Up to version 7,x the generate tables from DB2 was:

<filedName> <fieldName> {column="<tableName>.<fieldName>", maxLen=, isSqlNullable=};

From version 8, the generate tables from DB2 are:

<filedName> <fieldName> {column="<fieldName>", maxLen=, isSqlNullable=};

tha tableName has ben removed !!!

I had to revise all EGL record definiion and then all "get" instructions into all programs using at least a few days of work that is, most of the time saved by using EGL.

The problem was that the automatic (???) conversion from version 7 to version 8 did not work. Herefore it is a further egls problem. Do you not think so?

Now it works well (I've only tried with the first jsp) but I have a long and humiliating work.

Thank you anyway for your time which is always absolute.

paoloc

• paoloc 1200006HSR
• ‏2013-08-02T08:43:06Z

Hi Mark!

you're right , the problem was the table name before the field name.

Unfortunately I have to tell you again that this is a EGL problem.

Up to version 7,x the generate tables from DB2 was:

<filedName> <fieldName> {column="<tableName>.<fieldName>", maxLen=, isSqlNullable=};

From version 8, the generate tables from DB2 are:

<filedName> <fieldName> {column="<fieldName>", maxLen=, isSqlNullable=};

tha tableName has ben removed !!!

I had to revise all EGL record definiion and then all "get" instructions into all programs using at least a few days of work that is, most of the time saved by using EGL.

The problem was that the automatic (???) conversion from version 7 to version 8 did not work. Herefore it is a further egls problem. Do you not think so?

Now it works well (I've only tried with the first jsp) but I have a long and humiliating work.

Thank you anyway for your time which is always absolute.

paoloc

More...

Paoloc, 

I suspect we changed the wizard in V8 as qualifying with the table name is not the usual way people write SQL unless they are doing Joins and need to identify the table the column goes with. 

This would never be something EGL would change in the SQL since we would have no way of knowing if this was something someone explicitly coded in the SQL statement.

Finally, I don't think this is an EGL problem as it worked in v8 as long as you used the "x" userid.

As I stated yesterday, if making that change worked, I suggested you open a PMR against DB2 or get further DB2 help from someone with more knowledge of DB2 to determine why it is not working when you change the userid.

I would pursue this before you go change all the SQL statements and record definitions.   But that is up to you.

.

• markevans 120000NQBF
• ‏2013-08-02T16:08:46Z

Paoloc, 

I suspect we changed the wizard in V8 as qualifying with the table name is not the usual way people write SQL unless they are doing Joins and need to identify the table the column goes with. 

This would never be something EGL would change in the SQL since we would have no way of knowing if this was something someone explicitly coded in the SQL statement.

Finally, I don't think this is an EGL problem as it worked in v8 as long as you used the "x" userid.

As I stated yesterday, if making that change worked, I suggested you open a PMR against DB2 or get further DB2 help from someone with more knowledge of DB2 to determine why it is not working when you change the userid.

I would pursue this before you go change all the SQL statements and record definitions.   But that is up to you.

 

.

 

More...

Mark! 

I spoke with db2 specialists and they said that the old formulation of the sql <nameTable>.<nameFiel> ,can give unexpected future problems.that neither you nor I, we met up to now.

I need to change all the egl code, is the most strenuous but the safest solution.

Thanks for the help paoloc

• paoloc 1200006HSR
• ‏2013-08-05T06:20:17Z

Mark! 

I spoke with db2 specialists and they said that the old formulation of the sql <nameTable>.<nameFiel> ,can give unexpected future problems.that neither you nor I, we met up to now.

I need to change all the egl code, is the most strenuous but the safest solution.

Thanks for the help paoloc

More...

Paoloc,

thanks for the response and checking with your DB2 specialists.

One thought that might reduce the changes you have to make.

Can you change just the "from" clause in the SQL statements to something like:

from x.messagi as messagi  

or

from x.messagi messagi

So that the correlation id in the column names is explicit.

Using your original statement:

IF this works, then it might allow you to leave the columnNames the way they are...and only have to change the FROM clause in the explicit statements and the "tablenames" clause in the EGL record definitions for implicit SQL statements

(tablenames would be something like this)

Mark

• markevans 120000NQBF
• ‏2013-08-05T13:29:24Z

Paoloc,

 

thanks for the response and checking with your DB2 specialists.

One thought that might reduce the changes you have to make.

Can you change just the "from" clause in the SQL statements to something like:

from x.messagi as messagi  

or

from x.messagi messagi

So that the correlation id in the column names is explicit.

Using your original statement:

get messaggi with

#sql{

select

MESSAGGI.MECODORG, MESSAGGI.MEUTEINP, 

MESSAGGI.MEUTEOUT, MESSAGGI.MEDATE, MESSAGGI.MESPERIC, 

MESSAGGI.METESTO, MESSAGGI.METIPO, MESSAGGI.MESTATO, 

MESSAGGI.MEOGGETTO, MESSAGGI.MECOGNOME

from X.MESSAGGI MESSAGGI

where MESSAGGI.MECODORG=:organizzazione

and MESSAGGI.MEUTEOUT=:questoutente

and MESSAGGI.METIPO=:tipoMessaggio

and MESSAGGI.MESTATO <>:cancellati

and MESSAGGI.MESTATO <>:cancMittente

order by

MESSAGGI.MECODORG, MESSAGGI.MEUTEINP, 

MESSAGGI.MEUTEOUT, MESSAGGI.MEDATE, MESSAGGI.MESPERIC asc

};

IF this works, then it might allow you to leave the columnNames the way they are...and only have to change the FROM clause in the explicit statements and the "tablenames" clause in the EGL record definitions for implicit SQL statements

(tablenames would be something like this)

 

record Messaggi type sqlRecord { 

        tablenames=[["X.MESSAGGI","MESSAGGI"]],

        keyItems=[MECODORG, MEUTEINP, MEUTEOUT, MEDATE, MESPERIC]

    } 

 

 

Mark

More...

Mark!

paoloc

• paoloc 1200006HSR
• ‏2013-08-09T08:49:57Z

Mark!

thanks for the advice but I had already started to clear the table name before the name of the field.

Now I'm done and everything works perfectly.

Thank you for your attention.

paoloc

More...

Glad it is working for you.