Topic
16 replies Latest Post - ‏2013-08-19T14:14:20Z by markevans
paoloc
paoloc
508 Posts
ACCEPTED ANSWER

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
    131 Posts
    ACCEPTED ANSWER

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-26T22:54:45Z  in response to paoloc

    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
    ACCEPTED ANSWER

    Re: DB2 connection problems when running a project on Tomcat

    ‏2013-07-29T12:21:22Z  in response to paoloc

    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
      508 Posts
      ACCEPTED ANSWER

      Re: DB2 connection problems when running a project on Tomcat

      ‏2013-07-29T14:40:48Z  in response to Berndyman

      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
        2611 Posts
        ACCEPTED ANSWER

        Re: DB2 connection problems when running a project on Tomcat

        ‏2013-07-29T20:58:47Z  in response to paoloc

        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
          508 Posts
          ACCEPTED ANSWER

          Re: DB2 connection problems when running a project on Tomcat

          ‏2013-07-31T07:46:08Z  in response to markevans

          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
            2611 Posts
            ACCEPTED ANSWER

            Re: DB2 connection problems when running a project on Tomcat

            ‏2013-07-31T13:49:20Z  in response to 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
              508 Posts
              ACCEPTED ANSWER

              Re: DB2 connection problems when running a project on Tomcat

              ‏2013-08-01T14:29:01Z  in response to markevans

              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
                2611 Posts
                ACCEPTED ANSWER

                Re: DB2 connection problems when running a project on Tomcat

                ‏2013-08-01T15:54:31Z  in response to 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
                  508 Posts
                  ACCEPTED ANSWER

                  Re: DB2 connection problems when running a project on Tomcat

                  ‏2013-08-01T16:39:00Z  in response to markevans

                  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
                    2611 Posts
                    ACCEPTED ANSWER

                    Re: DB2 connection problems when running a project on Tomcat

                    ‏2013-08-01T16:52:05Z  in response to 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
                      508 Posts
                      ACCEPTED ANSWER

                      Re: DB2 connection problems when running a project on Tomcat

                      ‏2013-08-02T08:43:06Z  in response to markevans

                      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
                        2611 Posts
                        ACCEPTED ANSWER

                        Re: DB2 connection problems when running a project on Tomcat

                        ‏2013-08-02T16:08:46Z  in response to 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
                          508 Posts
                          ACCEPTED ANSWER

                          Re: DB2 connection problems when running a project on Tomcat

                          ‏2013-08-05T06:20:17Z  in response to markevans

                          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
                            2611 Posts
                            ACCEPTED ANSWER

                            Re: DB2 connection problems when running a project on Tomcat

                            ‏2013-08-05T13:29:24Z  in response to 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
                              508 Posts
                              ACCEPTED ANSWER

                              Re: DB2 connection problems when running a project on Tomcat

                              ‏2013-08-09T08:49:57Z  in response to markevans

                              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
                                2611 Posts
                                ACCEPTED ANSWER

                                Re: DB2 connection problems when running a project on Tomcat

                                ‏2013-08-19T14:14:20Z  in response to paoloc

                                Glad it is working for you.