10 Replies Latest reply on Oct 5, 2010 7:59 PM by Bo Sun

    Teiid jdbc connection questions

    Ramesh Reddy Master

      This is question from "teiid-users" list, moving it here for better user experience and search capabilities.

       

      Hi, Teiid users:

      I started Teiid exploration and try to run the Quick start sample.

      So far I have:

      1) installed Jboss and Teiid run-time, and started Jboss with VDB DynamicPortfolio active.
      2) installed Derby DB and populated sample tables/data, and started its NetworkServer.
      3) I tested the Derby DB, I can select from tables

      I have following questions hopefully you can help:

      1) Please confirm that Teiid metadata repository save in xml formats in  <jboss.home>/server/default/deploy directory (v.s. in a relational DB in MetaMatrx scenario).

      2) in Sample README.txt file: it suggests to test Teiid VDB connection with following command:

      $./run.sh localhost 31000 dynamicportfolio "select stock.* from product, (call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock where product.symbol=stock.symbol"

      It seems not working for me. What is the way you normally testing VDB connection?

      3) I tried to use Squirrel to connect to teiid jdbc with following details:

      Driver file: teiid-7.1.0.Final-client.jar
      Class Name: org.teiid.jdbc.TeiidDriver URL:jdbc:teiid:DynamicPortfolio@mm://aistelab01:31000  (aistelab01 is the server where teiid installed)
      username: admin
      password: teiid

      I got error: Teiid: unable to find a component used authenticate on to Teiid.
      I am not able to jdbc connect to Derby DB also.

      Please help

      Best regards,

       

      Bo Sun

        • 1. Re: Teiid jdbc connection questions
          Ramesh Reddy Master

          Bo,

          1) Please confirm that Teiid metadata repository save in xml formats in  <jboss.home>/server/default/deploy directory (v.s. in a relational DB in MetaMatrx scenario).

           

          Teiid supports a new feature called "Dynamic VDB" apart from the VDB you can create using the Teiid Designer. This XML file is represents the Dynamic VDB. Read about it here. This XML file just represents the information about the sources you are integrating, not their metadata. Metadata of these sources are loaded at the time of VDB deployment, or server is started. You have an option to cache the metadata after the first time it is loaded too.

           

          2) in Sample README.txt file: it suggests to test Teiid VDB connection with following command:

          $./run.sh localhost 31000 dynamicportfolio "select stock.* from product, (call MarketData.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) stock where product.symbol=stock.symbol"

          It seems not working for me. What is the way you normally testing VDB connection?

          What is the error you are seeing? The above is a simple java program making a JDBC connection. You can use any JDBC client to check the connection. Teiid also has a monitoring tool, you can use to check if your VDB is deployed and data sources are configured correctly. Check out http://localhost:8080/admin-console assuming you deployed your JBoss AS on "localhost". If a VDB is "inactive" for any reason, it will not let a user connect to it either. Fix any errors and try again.

           

          I got error: Teiid: unable to find a component used authenticate on to Teiid.
          I am not able to jdbc connect to Derby DB also.

          Try connecting to the Derby directly using SquirreL. This MUST succeed. Then have you created the "-ds.xml" file for this Derby database and deployed in the Teiid to be used with this example? There is sample "-ds.xml" file provided in the example. Copy it and modify it for your machine settings. If still does not work, post the stacktrace errors, may be we can help.

           

          The Designer specific VDB creation is same, however the deployment and configuration of sources  is different than MetaMatrix in Teiid.

           

          Thank you for using Teiid.

           

          Ramesh..

          • 2. Re: Teiid jdbc connection questions
            Bo Sun Newbie

            Ramesh,

             

            thanks for the prompt answers, they are great!

             

            By following your instruction, let me first verify if the VDB is deployed and data source are configured correct.

             

            After I started JBOSS AS (run.sh),  I see following msg in the log:

             

            13:45:01,469 INFO  [RUNTIME] VDB "DynamicPortfolio.1[MarketData[text-connector/file/java:marketdata-file], Accounts[derby-connector/derby/java:PortfolioDS], SYS[SYS/SYS/SYS], pg_catalog[]]" deployed in active state.

            the msg gave me positively info that the VDB are deployed correctly. However, when I try the :

            http://aistelab01:8080/admin-console as you recommended. I got "Internet expoloer cannot display the webpage" error.

            so I started to focus on making admin-console working.

            1) the admin-console.war directory is under deploy directory.

            2) is 8080 the correct port #, how can I find out the port#. 

            3) is there any extra steps to install admin-console....

             

            Please let me know. thanks.

             

            Best regards,

             

            Bo Sun

            • 3. Re: Teiid jdbc connection questions
              Ramesh Reddy Master

              Bo,

               

              If have not changed any settings the default port would be 8080. In that case the only thing that is not working is the host name resolution of "aistelab01".

               

              So, if it is your own machine, you can try substituting with 127.0.0.1 or localhost. If this is remote server, then make sure this name is correct and reachable by issuing a ping.  You can see the messages in console where the JBoss AS started, to see which host and port it is started on, and try using them in your connect url.

               

              Ramesh..

              • 4. Re: Teiid jdbc connection questions
                Bo Sun Newbie

                Ramesh,

                 

                when I started the JBoss AS, i got:

                 

                10:30:07,027 INFO  [config] Initializing Mojarra (1.2_12-b01-FCS) for context '/admin-console'
                10:30:08,610 INFO  [TomcatDeployment] deploy, ctxPath=/
                10:30:08,652 INFO  [TomcatDeployment] deploy, ctxPath=/jmx-console
                10:30:08,900 INFO  [RUNTIME] VDB "DynamicPortfolio.1[MarketData[text-connector/file/java:marketdata-file], Accounts[derby-connector/derby/java:PortfolioDS], SYS[SYS/SYS/SYS], pg_catalog[]]" deployed in active state.
                10:30:10,132 INFO  [Http11Protocol] Starting Coyote HTTP/1.1 on http-127.0.0.1-8080
                10:30:10,153 INFO  [AjpProtocol] Starting Coyote AJP/1.3 on ajp-127.0.0.1-8009

                1) aistelab01 is spelled right, as I can ping it.aistelab01 is a remote Linux server.

                2) http://aistelab01.nam.nsroot.net:8080/admin-console is the url. I tried http://aistelab01:8080/admin-console also. No page is shown.

                3) I tried command: "netstat -a |grep 8080", nothing is found.

                 

                Please advise.

                 

                Best regards,

                 

                Bo

                • 5. Re: Teiid jdbc connection questions
                  Ramesh Reddy Master

                  Bo,

                   

                  By default if you do not provide the bind address JBoss AS starts on 127.0.0.1. Since you want specific address try starting the JBoss AS as

                   

                  ./run.sh -b aistelab01.nam.nsroot.net

                   

                  Then try the above URLs

                  • 6. Re: Teiid jdbc connection questions
                    Bo Sun Newbie

                    Wow! it works! now I am able to connect to admin-console. thanks alot, Ramesh.

                     

                    I noted from admin-console that "Data Services" is unavailable. I search the run.sh log. and found:

                     

                    ...............

                    15:55:50,319 ERROR [AbstractKernelController] Error installing to Start: name=RuntimeEngineDeployer state=Create
                    org.jboss.netty.channel.ChannelException: Failed to bind to: /169.193.184.113:31000
                            at org.jboss.netty.bootstrap.ServerBootstrap.bind(ServerBootstrap.java:306)
                            at org.teiid.transport.SocketListener.<init>(SocketListener.java:95)
                    ..........

                     

                    15:27:27,993 INFO  [RUNTIME] VDB "DynamicPortfolio.1[MarketData[text-connector/file/java:marketdata-file], Accounts[derby-connector/derby/java:PortfolioDS], SYS[SYS/SYS/SYS]]" deployed in active state.
                    15:27:28,035 ERROR [ProfileServiceBootstrap] Failed to load profile: Summary of incomplete deployments (SEE PREVIOUS ERRORS FOR DETAILS):

                    DEPLOYMENTS IN ERROR:
                      Deployment "RuntimeEngineDeployer" is in error due to the following reason(s): java.net.BindException: Address already in use

                    15:27:28,044 INFO  [Http11Protocol] Starting Coyote HTTP/1.1 on http-aistelab01.nam.nsroot.net%2F169.193.184.113-8080
                    15:27:28,070 INFO  [AjpProtocol] Starting Coyote AJP/1.3 on ajp-aistelab01.nam.nsroot.net%2F169.193.184.113-8009

                    ...............

                    1) please be noted that 169.193.184.113 is aistelab01.

                    2) I guess you may add more parameters for command run.sh to work out the error?

                    3) Is there a help for run.sh.... so I can debug myself in the future.

                    4) Since I am not able to use jdbc connection from my desktop SQuirreL to Linix (aistelab01) derby DB. I suspect when I run 'startNetworkServer'... should I add some parameters....

                     

                    thanks.

                    Bo

                    • 7. Re: Teiid jdbc connection questions
                      Ramesh Reddy Master

                      Bo,

                       

                      Looks like there is another process already running on that machine that uses the port number "31000" that is default port used by Teiid. Do you by chance also have MetaMatrix running on it? If it is, either you can shut that instance down, or try starting the Teiid on a different port.

                       

                      Edit the <jboss-install>/server/default/deploy/teiid/teiid-jboss-beans.xml file, and find "31000" port in this xml file and replace with some other port. Save and restart the JBoss AS. Note that when you connect using JDBC to Teiid you need to use this new port number in your connection URL.

                       

                       

                      Ramesh..

                      • 8. Re: Teiid jdbc connection questions
                        Bo Sun Newbie

                        yes, we do have MetaMatrix running on this same server. So I updated <jboss-install>/server/default/deploy/teiid/teiid-jboss-beans.xml file with port number "31001". This brought the 'Data Services" up!

                         

                        So the good news follows... I am able to connect to Teiid DynamicPortfolio VDB from SQuirreL ... a great break-through! and I am able to view Metadata from Derby DB (Accounts) and  file (MarketData)!!

                         

                        then I headed to run SQL command:

                        select * from product;

                        it gave me error as follows:

                         

                        Error: Error Code:0 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:0 Message:Error Code:0 Message:Unable to get managed connection for PortfolioDS; - nested throwable: (javax.resource.ResourceException: Unable to get managed connection for PortfolioDS)
                        SQLState:  50000
                        ErrorCode: 0

                         

                        Although I checked the admin-console that the PortfolioDS is up status. But I guess the connection from Teiid to derby is not correct. Also the jdbc connection from SQuirreL to derby is not working! Hmm...any suggestion?

                         

                        thanks, Ramesh.

                         

                        Bo

                        • 9. Re: Teiid jdbc connection questions
                          Ramesh Reddy Master

                          Cool, good progress.

                           

                          Either Derby DB is not started, or your client configuration is wrong. Do you have Derby installed on the same machine? Did you start it? what is exception you get when connected with SquirreL? Did you place the "derbyclient.jar" in the "<jboss-as>/server/default/lib" directory?

                           

                          If you are running the Derby on the same machine as Teiid, then you need to edit/add 'derby.properties' in the root Derby directory and add the following line

                           

                          derby.drda.host=aistelab01.nam.nsroot.net

                           

                           

                          I start Derby like this

                           

                          export DERBY_HOME=~/tools/db-derby-10.6.1.0-bin

                          bin/startNetworkServer

                           

                          Make sure your in your -ds.xml file defined for "derby" also contains the name as "aistelab01.nam.nsroot.net" for the hostname.

                           

                          Ramesh..

                          • 10. Re: Teiid jdbc connection questions
                            Bo Sun Newbie

                            it finally works like a charm! My jdbc connection issues are resolved. Thanks a lot, Ramesh.