JSPWiki now supports DB2!

I have been briefly working with JSPWiki 2.4.103 and have a DB2 database supporting the page and attachment storage. I have to admit that we are integrating our Syspedia repository product with JSPWiki to support business users with the technical IT Syspedia information. Since clients have literally millions of metadata items about programs, JCL, files and elements in Syspedia we needed DB2's robust database performance and support.

It was quite easy to set up JSPWiki with DB2.

Quick start

• Get DB2 working in your environment.
• Set up a JNDI datasource for your JSPWiki application
• Define the database, tables and indexes.
• Setup jdbcprovider.properties,jdbcprovider.db2.properties etc...
• It works!

Installation Process

• Install Tomcat (I used 5.027) and get it up and running on your system.
• Install JSPWiki (I used v2.4.103) and set the jspwiki.properties file to use regular storage.
  • Make sure it works with regular file storage first to get any problems resolved.
• Install DB2 (I used the free full function non-expiring DB2-C Edition) It is the IBM DB2 Express-C 9, a free download available from here.
  • http://www.ibm.com/developerworks/downloads/im/udbexp/
  • Run through the DB2 first steps to verify DB2 is working.
  • Make sure to copy the DB2 JDBC drivers into Tomcat!
  • These DB2 Driver files are usually on Windows C:\Program Files\IBM\SQLLIB\java
    • db2java.jar, db2jcc.jar, db2_license_c.jar, db2_license_cu.jar
    • I copied four DB2 JAR files into the tomcat\common\lib so they are available for all server applications. You can also copy them into the WEB-INF\lib of the JSPWiki application.
• Define a JNDI datasource context within Tomcat JSPWiki application.
  • These screen prints might help.
  • psiwiki2 is the database schema and table creator name.
  • dave is the id that has all privileges to the database and its tables
  • set up at least 2 connections to be recycled by the user's SQL statements
  • specify the timeout to be something short (i.e. 60 seconds)
  • specify a SQL query that is TESTED and simple (typing mistakes are killer ;-) )
  • 

• Define a database with the two JSPWiki tables (WIKI_PAGE and WIKI_ATT) and the appropriate indexes. Create statments attached below.
  • NOTE: I am going to put the user access into DB2 tables in the future.
  • NOTE: I am thinking of redefining the indexes for performance improvements but am still testing the new definitions.

• Use the JDBCProvider (4-29-2007 Version) and redefine your jspwiki.properties file to use the JNDI context defined earlier. Follow the install directions preciously and the migration instructions if you already have JSPWiki pages.
  • Attached is my working jspwiki.properties file.

• Use the jdbcprovider.db2.properties file attached below that contains the standard DB2 SQL statements and syntax for referencing your JSPWiki pages. NOTE: The db2properties file does not read all the pages into the server memory. Since we have literally millions of pages this would cause the server to crash. So searching may be be a bit impacted.
  • Attached is my working jdbcproviders.db2.properties file
    • NOTE: The tables creator is specified in the jdbcprovider.db2.properties SQL statements.

And it all works!

If it doesn't work

• First look at the JSPWiki.log for any errors or SQL error messages.
• Next look at the Tomcat Logs for any JNDI set up problems. Verify your DB2 java Drivers are available and the JNDI SQL statement works!
• Next copy one of the jdbcproviders.db2.properties SQL statments and try it directly within DB2.
• Next verify the authority access to the DB2 database. Sign on to DB2 as the JNDI id and try it, you'd be surprised how many times this is screwed up.
• Next look at the OS logs for any messages from Tomcat, DB2 or security.

We are successfully using this to connect to DB2 for z/OS v8 from JSPWiki v2.4 running in a tomcat container as a z/OS batch job.

When using DB2 for z/OS, you don't specify the database in the connection string but rather the DB2 location (which is probably the DB2 subsystem ID - have a look in SYSIBM.LOCATIONS).

--AnonymousMe, 22-Sep-2007