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

Administrator Administrator do-not-reply at jboss.com
Thu Jan 20 11:06:34 EST 2011

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_1251/html/locking/X30621.htm 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/errMessageAdvRes/BACBJHDF.htm 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.1502/html/aselxqig/aselxqig5.htm 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/prjdbc/CHDIFEIE.htm Programmer's Reference and the ASE  http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31650.1502/html/dtm/BHCHBBBD.htm 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

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:
> An 'identity gap' is a large, sudden, and unexpected jump of values in an identity column, usually as a result of a shutdown with nowait, although it can also occur as a result of certain dump/load scenarios.
Refer to the  http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/20271;pt=17866/* 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/title.htm 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/prjdbc/BHCJEABE.htm 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.prjdbc/html/prjdbc/X15095.htm 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.0.sqlug/html/sqlug/sqlug212.htm Transact-SQL user guide:
> Use the variable-length binary type, +varbinary(n)+, for data that is expected to vary greatly in length. Storage size is the actual size of the data values entered, not the column length. *Trailing zeros are truncated*.
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

Create a new document in jBPM at Community
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20110120/0499ed59/attachment-0001.html 

More information about the jboss-user mailing list