Topic
  • 16 replies
  • Latest Post - ‏2013-08-19T14:14:20Z by markevans
paoloc
paoloc
530 Posts

Pinned topic DB2 connection problems when running a project on Tomcat

‏2013-07-26T08:27:26Z |

I cannot run a project to a production server due to a connection problem with DB2.

The project run well on the developing machine.

Both windows machines have se same DB2 configuration: same tables names, same users with grant but when I launch the project on the production server i get the error:

 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.

The context.xml :

<?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="xyz" type="javax.sql.DataSource" url="jdbc:db2://localhost:50000/NOVA02:retrieveMessagesFromServerOnGetMessage=true;" username="x"/>
</Context>
Both DB2 installations have configurated the user "x" with same passworg and with grant permissions (see atthached image.

Ho to configure EGL or Tomcat or DB2 to solve the problem?

paoloc

Attachments

  • JBASkeen
    JBASkeen
    137 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-26T22:54:45Z  

    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

  • Berndyman
    Berndyman
    2 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏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.

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-29T14:40:48Z  
    • Berndyman
    • ‏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.

    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. 

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-29T20:58:47Z  
    • paoloc
    • ‏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. 

    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

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-31T07:46:08Z  
    • markevans
    • ‏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

    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

     

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-31T13:49:20Z  
    • paoloc
    • ‏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

     

    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

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-01T14:29:01Z  
    • markevans
    • ‏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

    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

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-01T15:54:31Z  
    • paoloc
    • ‏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

    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.

     

     

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-01T16:39:00Z  
    • markevans
    • ‏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.

     

     

    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

     

    Attachments

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-01T16:52:05Z  
    • paoloc
    • ‏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

     

    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??

     

     

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-02T08:43:06Z  
    • markevans
    • ‏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??

     

     

    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

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-02T16:08:46Z  
    • paoloc
    • ‏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

    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.

     

    .

     

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-05T06:20:17Z  
    • markevans
    • ‏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.

     

    .

     

    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

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-05T13:29:24Z  
    • paoloc
    • ‏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

    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

  • paoloc
    paoloc
    530 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-09T08:49:57Z  
    • markevans
    • ‏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

    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

  • markevans
    markevans
    2885 Posts

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-08-19T14:14:20Z  
    • paoloc
    • ‏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

    Glad it is working for you.