• Please help a newbie with JDBC and DB2

    By Pete Rawlings 1 decade ago

    Environment : Domino Designer  8.5.3 FP4 with XPages ExtensionLibraryOpenNTF-900v00_02.20130515-2200

    I am along in the tooth notes developer , since Domino 3.2. Totally new to Xpages and Javascript. Very impressed and excited abut the Xpages world. Have created simple applications using Notes Views and Forms. My objective now is to work Notes and DB2 toghether. I'm running through proof of concept and want to use JDBC to access a DB2 database (R9) . I am very confused about all the differing instructions and error messages I'm getting. I found simple examples on YouTube saying connect in 5 mins using thcom.ibm.db2.jcc.DB2Driver . I hunted high and low for this. Even installed DB2 AESE Server on test machine. Could not find this anywhere. So gave up on this path.

    Then I went off into the world of this XPages Extension Library figured out how to install and just want to preview in my local system , I have not even branched out into the world of OSGi yet.

    So Extension Library is all installed . Read the Xpages Goes Relational.odp no useful info for a startup in here. So thought I would just try and open the XpagesJDBC.nsf .

    I cannot find any instructions on how to use this database, so jI ust tried previewing the JDBC_Home and immediatley get an Error previewing in the Default Browser  "Cannot find the library com.ibm.xsp.extlib.library, required by the application/xpages/XPagesJDBC.nsf." Page will not preview at all in the Notes Client (where I want to deliver my application).

    I looked all over my system for com.ibm.xsp.extlib.library , not anywhere to be found. Tried to find a download on the web. No joy. 

     I am at a total loss of where to go and would appreciate any pointers to get me going.

    • Quick update Fails on V9.0 as well

      By Pete Rawlings 1 decade ago

      I ran up my other test machine on V9.0 with the latest level of XPages Extensions from OpenNTF . I had read that V9.0 could have the Libabry already included. BUT I still get an error .

      -

      The application/XPages/XPagesJDBC.nsf requires com.ibm.xsp.extlibx.library. This library cannot be found.

      -

      A change but still have an issue. Happy to supply the stack trace if anyone can help.

      • Extlibx

        By Padraic Edwards 1 decade ago

        Hi Pete,

        With Notes/Domino 8.5.3, the Extension Library (ExtLib) is a separate installation that is available on OpenNTF. In Notes/Domino 9.0 a version of the Extension Library became part of the product however we still provide updates to the Extension Library through OpenNTF when fixes are needed. These releases can be installed on top of the IBM supported version of the Extension Library that is in the product in 9.0.

        The RDBMS support is part of the extended components (ExtLibx) of the XPages Extension Library. It is only posted as part of the OpenNTF release of the ExtLib and not included in any product release. In any 9.0 OpenNTF Extension Library release there is a zip called "updateSiteOpenNTFExtended.zip" which contains the "ExtLibx" library. You need to follow these instructions to deploy the library on your server and in your notes client if that is where you application will be running eventually.

        http://www-10.lotus.com/ldd/ddwiki.nsf/dx/Installing_an_XPages_Library_into_Designer

        http://www-10.lotus.com/ldd/ddwiki.nsf/dx/Deploying_XPage_Libraries

        So for 8.5.3 you would install both the ExtLib library and also the ExtLibx library from the "updateSiteOpenNTF.zip" included in an OpenNTF release. For 9.0 you only need to install the ExtLibx library from "updateSiteOpenNTFExtended.zip"

        The DB2 driver can be downloaded from here using your IBM ID

        http://www-01.ibm.com/support/docview.wss?uid=swg21363866

        Some OpenNTF community members and IBM'ers collaborated to write a book on the Extension Library, it has a chapter covering the Relational topic if you can get your hands on it.  Also there is the Mastering XPages book which covers the core XPages runtime.

        It is probably worth reading John Jardin's good tutorial also on this subject->

        http://johnjardin.ukuvuma.co.za/2012/02/29/tutorial-integrating-xpages-with-db2-part-1/


        Thanks,

        Padraic

        • By Rob Porter 9 years ago

          Hi Padraic

          I followed John Jardin's tutorial and tried to use the same technique to connect to DB2 on our IBM AS400. In John's tutorial he uses port 50000 to connect but this threw up a security error. I searched the web and found a confusing amount of info on the subject of ports. I finally settled on port 446 but now I get an error telling me I need a licence to connect via JDBC to the AS400. Here is the entire error message:
          [jcc][t4][10109][10354][4.7.85] The version of the IBM
          Universal JDBC driver in use is not licensed for connectivity to QAS
          databases.
          To connect to this server, please obtain a licensed copy of the IBM DB2
          Universal Driver for JDBC and SQLJ.
          An appropriate license file db2jcc_license_*.jar for this target
          platform must be installed to the application class path.
          Connectivity to QAS databases is enabled by any of the following license
          files: [ db2jcc_license_cisuz.jar ]. ERRORCODE=-4472, SQLSTATE=42968

          My questions are:

          1. should I stick with port 50000?

          2. Do I really need a db2jcc_licence_cisuz.jar file? If so, where does it go? Do I have to put it somewhere in the file system on the Domino server? I've searched the plugin that John's tutorial links to but I can't find any similar files in it (ie. with “licence” in the file name).

          Thanks

          Rob

          • By Padraic Edwards 9 years ago

            Hi Rob,
            I think this is the relevant technote you need to look at in relation to the licence problem.

            http://www-01.ibm.com/support/docview.wss?uid=swg21413734

            I will need to get back you to you on port. Hope this helps for now

            Thanks,
            Padraic

            • By Rob Porter 9 years ago

              Thanks Padraic.

              Our plan is to read and update tables directly on our AS400. I've done this before with ODBC but I'd prefer to use JDBC if possible. The worrying thing about JDBC is how little information I can find on connecting to an AS400 via JDBC. Have you heard of anyone doing this before?

              Thanks
              Rob

              • By B Gleeson 9 years ago

                Hi Rob. On the license file issue, there is a db2jcc_license file included in the java folder of a DB2 installation (the same folder that contains the jdbc driver). What I have done in the past is use the JDBC driver plugin wizard in Designer, and created an OSGI plugin update site containing both the DB2 jdbc jar file AND the license file as well. Installing that update site to the server should resolve that problem.

                I can't speak to the AS400 issue I'm afraid, as I've never tried the relational support with that system.

                • By Rob Porter 9 years ago

                  Thanks for the info. It turns out that there are jar files available specifically for connecting to an AS400. There is a project called JTOpen at http://jt400.sourceforge.net/ which deals with JDBC and systems running IBM i (or i5/OS or OS/400). I downloaded the JTOpen archive and then used the plugin wizard in Designer to create an update site.

                  For anyone reading this who wants to do this themselves, the class in the plugin wizard is com.ibm.as400.access.AS400JDBCDriver and the jar to include is jt400.jar. The connection file (which I called as400.jdbc and is based on John Jardin's tutorial) looks like this:

                  <jdbc>
                  <driver>com.ibm.as400.access.AS400JDBCDriver</driver>
                  <url>jdbc:as400://XXX.XX.XX.XX</url>
                  <user>username</user>
                  <password>password</password>
                  </jdbc>
                  

                  Obviously replace XXX with your AS400's IP address and put in a username and password that has access to your AS400.

                  Fortunately no licence file is required.

                  I'm very pleased with the performance and it's reasonably straightforward to use. To populate a combobox for example requires some simple code like this:

                  <xp:comboBox
                          id="inputContainer"
                           >
                           <xp:selectItem itemLabel="- select -" itemValue=""></xp:selectItem>
                           <xp:selectItems id="selectItems7">
                                    <xp:this.value><![CDATA[#{javascript:var con:java.sql.Connection;
                  try {
                      con = @JdbcGetConnection("as400");
                      var results:Array = @JdbcDbColumn(con, "DOMINO.CONTAINERS", "PRDNO");
                      return results
                  }finally{
                      con.close();
                  }}]]></xp:this.value>
                            </xp:selectItems>
                  
                   </xp:comboBox>
                  

                  Hopefully this will help someone one day :-)

                  • By B Gleeson 9 years ago

                    That's very helpful Rob, thanks. We may have to look to include that info somewhere in our documentation in the future.