[jboss-user] [jBPM] - jBPM3 on Sybase

Administrator Administrator [http://community.jboss.org/people/admin] modified the document: "jBPM3 on Sybase" To view the document, visit: http://community.jboss.org/docs/DOC-12936 -------------------------------------------------------------- *** #Configuration_highlights Configuration highlights **** #Lock_scheme Lock scheme **** #Number_of_locks Number of locks **** #Distributed_transactions Distributed transactions *** #Known_anomalies Known anomalies **** #Page_size Page size **** #Gaps_in_the_autogenerated_IDs Gaps in the auto-generated IDs *** #Past_pests_which_may_bite_again Past pests (which may bite again) **** #JDBC_drivers JDBC drivers **** #Wide_table_support Wide table support **** #Binary_datatypes Binary datatypes h3. Configuration highlights h4. Lock scheme Sybase offers three http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_125... locking schemes: allpages, datapages and datarows. The locking scheme can be indicated at table creation time, and falls back to a server-wide configuration parameter. When the server is first installed, the +lock scheme+ parameter is set to allpages. The allpages scheme may be too coarse to applications like jBPM where many small rows share a single page. Our tests with concurrent job processing reveal that page locking is prone to deadlock. Our suggestion is converting execution and log tables to datarows locking for alleviating the incidence of deadlocks. Such a conversion is not recommended for definition tables as they are mostly read only and do not offer any gains in exchange for the extra overhead. The locking scheme for jBPM tables created with the SQL data definition script distributed with the product can be changed on an individual basis using the alter table command. alter table table_name      lock {allpages | datapages | datarows} Changing the server-wide configuration parameter is not advisable in a production environment as it might impact other databases. However, should you find yourself in need for a quick fix for deadlocks in development, you can set the parameter using the *sp_configure* procedure. sp_configure 'lock scheme', 0, datarows h4. Number of locks You may encounter the beast labeled http://infocenter.sybase.com/help/topic/com.sybase.dc00729_1500/html/errM... Error 1204 under heavy job execution. 04:31:30,309 WARN  (main) [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1204, SQLState: ZZZZZ 04:31:30,309 ERROR (main) [org.hibernate.util.JDBCExceptionReporter] ASE has run out of LOCKS. Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure ASE with more LOCKS. The number of locks available is controlled by the configuration parameter *number of locks*. To chech the current value, call the  *sp_configure* procedure. [sybase]$ isql -Usa Password: <sa-password> 1> sp_configure 'number of locks' 2> go Parameter Name        Default     Memory Used Config Value Run Value --------------------- ----------- ----------- ------------ ----------- number of locks            10000        1630        10000       10000 To fix the problem, try doubling the number of locks. 1> sp_configure 'number of locks', 20000 2> go h4. Distributed transactions Sybase 15.0.2. Developer Edition supports Distributed Transaction Management (DTM) and other features described in the http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00468.150... Quick Installation Guide. The developer edition is available as a free download on Linux and does not require installing a license file. Use the *sp_lmconfig* procedure to determine which edition of Adaptive Server your are running. Sybase returns EE, SE, DE or XE based on the edition. sp_lmconfig 'edition' DTM features are +not+ enabled by default. The instructions for enabling DTM are provided in the jConnect http://infocenter.sybase.com/help/topic/com.sybase.dc39001_0605/html/prjd... Programmer's Reference and the ASE http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31650.150... Using DTM Features guide, and summarized here. Enable basic DTM Features and transaction coordination with the *sp_configure* procedure. Restart Adaptive Server for the changes to take effect. sp_configure 'enable DTM', 1 sp_configure 'enable xact coordination', 1 Grant the *dtm_tm_role* to any user who will participate in distributed transactions with the *sp_role* procedure. sp_role 'grant', 'dtm_tm_role', 'jbpmtest' h3. Known anomalies h4. Page size Sybase 15 installations have a default page size of 4KB. The page size impacts applications because it imposes a limit on the length of a record. The row size for a few jBPM tables could exceed the 4KB page size if every column was occupied in full. The DBMS warns about this potential issue after running the SQL data definition script supplied with the jBPM distribution. Warning: Row size (14372 bytes) could exceed row size limit, which is 4012 bytes. It is worth emphasizing that Sybase will only fail to write a record that +actually+ exceeds the size limit. In the jBPM case, the limit is unlikely to be crossed because tables with large row sizes map complete class hierarchies. Each concrete subclass occupies only a subset of the table columns. While switching to a larger page size is unnecessary for general use, you might want to change the type of VARCHAR(4000) columns to TEXT if your application manipulates data that in fact exceeds the row size limits. According to the http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0/title.htm documentation, Sybase stores text data in a list of pages that are separate from the rest of the table. For guidance on the type change procedure, refer to http://community.jboss.org/docs/DOC-16177 jBPM3 Text Columns. h4. Gaps in the auto-generated IDs Some users in the http://www.jboss.com/index.html?module=bb&op=viewtopic&t=89119 forum reported seeing a sudden jump in the generated IDs, such as select ID_ from JBPM_PROCESSINSTANCE                1                2                3                4 5000000000000002 This is a known issue with ASE, known as the 'identity gap'. As described in an http://www.sypron.nl/idgaps.html article by Rob Verschoor on the subject: Refer to the http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__B... Sybase Manuals for more information and for configuration options. h3. Past pests (which may bite again) h4. JDBC drivers At least two viable driver options exist for Sybase. * http://infocenter.sybase.com/help/topic/com.sybase.help.jconnjdbc_6.05/ti... jConnect, the driver delivered with Sybase ASE * http://jtds.sourceforge.net/ jTDS, an open souce driver for Sybase and MS SQL The jBPM team made extensive testing with jConnect 6.05 and jTDS 1.2.2. Both were found to be useable with jBPM 3.2.4 and up. However, jTDS failed inside the application server, due to incomplete XA support in the data source. The failure manifests itself as follows. org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: I/O Error: Unknown packet type 0x0)   at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:144)   ... Caused by: java.io.IOException: Unknown packet type 0x0   at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:851)   ... h4. Wide table support Since version 12.5, Sybase ASE provides http://infocenter.sybase.com/help/topic/com.sybase.dc39001_0605/html/prjd... wide table support, which means larger limits than in previous versions. * Tables can contain 1,024 columns. * varchar and varbinary columns can contain more than 255 bytes of data. * Column names more than 30 character long. The larger limits are relevant to jBPM in the following ways. * Several columns have long names. * Binary data associated to a process are stored in blocks whose size defaults to 1024 KB. Wide table support is related to the underlying http://jtds.sourceforge.net/faq.html#longColumnNames TDS protocol version. Both jConnect and jTDS support TDS 5.0 and wide limits starting from version 6 and 0.3 respectively. In order for wide limits to be enacted, both the server and the driver must support them. If you must use jConnect 5.5, you can still take advantage of wide table support by setting the http://infocenter.sybase.com/help/topic/com.sybase.help.jconnjdbc_5.5.prj... jConnect version to VERSION_6. Failure to do so may result in data truncation, as reported in http://jira.jboss.com/jira/browse/JBPM-703 JBPM-703. h4. Binary datatypes The database schema generated by Hibernate includes a few varbinary columns. The manner Sybase handles varbinary data affects jBPM as it results in data truncation in valid data being lost. From the http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15... Transact-SQL user guide: The above +feature+ was brought to our attention in https://jira.jboss.org/jira/browse/JBPM-702 JBPM-702. The solution consisted in restoring the truncated zeros while retrieving the binary data from Java code. Nevertheless you must be aware that the stored data is not identical to the original data. -------------------------------------------------------------- Comment by going to Community [http://community.jboss.org/docs/DOC-12936] Create a new document in jBPM at Community [http://community.jboss.org/choose-container!input.jspa?contentType=102&am...]