6 Replies Latest reply on Feb 1, 2008 12:24 PM by r10

    Transaction Isolation Level in Clearspace

      Hey guys

       

      I've got a problem with CS 1.10 on WAS and DB2 where a DB2 error is thrown when trying to save widgets/widget location:

       

      Error Message: com.jivesoftware.base.database.dao.DAOException: com.ibm.db2.jcc.b.SqlException: [db2][jcc][10120][10898] Invalid operation: result set is closed.

       

      I looked through WidgetDAOImpl (an earlier message I can't find now indicated the problem was at line 360) but I don't see anything obviously wrong there and this code works on other app servers/DBs.  I want to eliminate the transaction isolation level as a cause of the problem (DB2 ships with the most restrictive level set) so I added <transactionIsolationLevel>1</transactionIsolationLevel> to the jive_startup file in the database section.  It didn't work but I'd like to know if that's the correct way to set the isolation level in Clearspace.

       

      Thanks,

      Jay

        • Re: Transaction Isolation Level in Clearspace

          hi Jay,

           

          I'm not sure about the transaction isolation settings, but is there any more information about that error you were seeing available in the logs? I checked in / around line 360 of WidgetDAOImpl and that specific section looks like it's actually getting widget frames, not saving them. 

           

          Cheers,

           

          AJ

          1 person found this helpful
            • Re: Transaction Isolation Level in Clearspace

              Hey Aaron,

               

              Thanks for responding.

               

               

               

              I only saw that error the first time I tried saving widgets, now it doesn't throw a line number.  Anyway I'm running 1.10 so I'm guessing that you're looking at 1.10.1 code.  I've marked line 360 in my 1.10 version below.  You're right it is getting widget frames at that line number.  Based on previous DB2 experience I think it may be that the isolation level is preventing a read of a table that is being read elsewhere.

               

               

               

              
               
                  public Map<Integer, List<WidgetFrameBean>> getWidgetFrames(int parentObjectType,
                          long parentObjectID) throws DAOException
                  {
                      DAOContext context = DAOContextFactory.getDAOContext();
                      PreparedStatement pstmt = null;
                      ResultSet rs = null;
                      Map<Integer, List<WidgetFrameBean>> widgetFrameBeans
                              = new HashMap<Integer, List<WidgetFrameBean>>();
                      WidgetFrameBean widgetFrameBean;
                      try {
                          pstmt = context.getConnection().prepareStatement(GET_WIDGET_FRAMES);
                          pstmt.setInt(1, parentObjectType);
                          pstmt.setLong(2, parentObjectID);
                          rs = pstmt.executeQuery();
                          while (rs.next()) {   *// line 360* 
                              widgetFrameBean = new WidgetFrameBean();
                              // etc. etc.   
                               
                  }
              

               

                • Re: Transaction Isolation Level in Clearspace

                  Hey guys,

                   

                  The solution was to set the WAS datasource custom property resultSetHoldability to 1 from 2.  Fortunately Jeff had seen this error before.  Here's the description of that property:

                   

                  "Determine whether ResultSets are closed or kept open when committing a transaction. The possible values are: 1 (HOLD_CURSORS_OVER_COMMIT), 2

                  (CLOSE_CURSORS_AT_COMMIT)." 

                   

                  That doesn't make much sense to me since CS is currently not doing DB transactions (you guys alluded to this and it was obvious after searching the code), I think it's a bug in the DB2 jcc driver.  We did try  hardcoding the transaction level in the connectionmanager to read uncommitted and it didn't do anything as expected.

                   

                  I'm going to list all the special things we had to do to get  CS 1.10 running smoothly under WAS:

                   

                   

                   

                  • Install Clearspace using clearspace.war.  You have to make sure that you configure WAS for JSP 15 compliance.  It's non-intuitive, here are the steps:

                   

                   

                  1. Select the war from the local filesystem, give a context root (/community 
                                   for example), and check Show me all install options. Next.

                  2. Click generate default bindings and use default virtual host name for the 
                                   virtual host. Next.

                  3. Take the default was.policy file, you won't be turning on J2 security. Next.

                  4. Take all the defaults EXCEPT click precompile JSPs. You don't really need 
                                   this but this option gets you to the option you do need. Next.

                  5. Pick the application server and web server to install on. If you haven't 
                                   hooked a web server into WAS you won't see an option for this. CS runs on 9080 
                                   in this case. Next.

                  6. Leave all the JSP compile options as is EXCEPT change the JDK source level 
                                   to 15. If you don't do this the CS JSP won't compile as they use a lot of new 
                                   JDK 5 syntax (for each, etc.). Next.

                  7. Take the defaults on the JSP reloading page. Next.

                  8. Don't configure a shared library. Next.

                  9. Initialization parameters, no change. Next.

                  10. CS on the default host. Next.

                  11. Last chance to change the context root. Next.

                  12. Summary screen, finish then save.

                  • In the web container custom properties for your app server you need to add the following or every URL in CS throws a 404, com.ibm.ws.webcontainer.invokefilterscompatibility=true .  The fix for this was supposed to be in the latest 6.1 fixpack but it's still not there.  You might want to try it first before setting the property.

                   

                  • In the CS admin set the CS system property jive.compressionFilter.enabled=false

                   

                  • Set the WAS datasource property resultSetHoldability to 1 as described above.

                   

                  • When creating the CS database the jiveOpenSearch table will exceed the default DB2 tablespace and won't be created.  You can either bump the default tablespace up (it's cryptic, don't ask) or use the attached SQL to create a smaller open search table.  Not optimal but it'll get you going.

                   

                  And that's it, simple, my cats could do it!

                   

                  Thanks,

                  Jay

                    • Re: Transaction Isolation Level in Clearspace

                      Thanks Jay! I sent this thread to our docs guy Steve Kang.

                       

                      Cheers,

                       

                      AJ

                        • Re: Transaction Isolation Level in Clearspace

                          Hey AJ,

                           

                           

                           

                          Sure thing, you guys let me know if you have any questions or need any clarifications.  It sounds like you already have a CS on WAS and DB2 section, that's a great idea.  There are enough quirks that it's next to impossible just to install and run without this special knowledge.

                           

                           

                          Thanks,

                           

                           

                          Jay

                            • Re: Transaction Isolation Level in Clearspace

                              Hey guys,

                               

                              I found something else that should be changed prior to install.  In DB2 if you don't specify the size of a blob it defaults to 1MB.  So, it's best to change blob column sizes to north of whatever you think your biggest attachment will be.  The following caps the blob at 50MB (well, close enough):

                               

                               

                               

                               
                               
                               
                              -- ---------------------------------------------------------------------------
                              -- jiveAttachData: Attachment binary data table.
                              -- ---------------------------------------------------------------------------
                              CREATE TABLE jiveAttachData (
                                  attachmentID    BIGINT NOT NULL,
                                  attachmentData  BLOB(50000000) NOT NULL,
                                  CONSTRAINT jiveAttachData_pk PRIMARY KEY (attachmentID)
                              );
                              ALTER TABLE jiveAttachData ADD CONSTRAINT jAttData_aID_fk FOREIGN KEY (attachmentID) REFERENCES jiveAttachment;
                               
                               
                              

                               

                               

                              If you have already run the script and don't want to drop and recreate the table you can try the following alter command.  It worked for me on DB2 9 but I'm not sure if it will on DB2 8 or below.  I haven't had much luck altering tables &lt; DB2 8.   There are four tables that use blobs, I just changed jiveAttachData and jiveDocumentBody.

                               

                               

                               

                               
                               alter table jiveattachdata alter column attachmentdata set data type blob(50000000)    
                               
                              

                               

                               

                              Thanks,

                              Jay