Configuration highlights
Lock scheme
Sybase offers three 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
Number of locks
You may encounter the beast labeled 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
Distributed transactions
Sybase 15.0.2. Developer Edition supports Distributed Transaction Management (DTM) and other features described in the 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 Programmer's Reference and the ASE 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'
Known anomalies
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 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 jBPM3 Text Columns.
Gaps in the auto-generated IDs
Some users in the 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 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 Sybase Manuals for more information and for configuration options.
Past pests (which may bite again)
JDBC drivers
At least two viable driver options exist for Sybase.
- jConnect, the driver delivered with Sybase ASE
- 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)
...
Wide table support
Since version 12.5, Sybase ASE provides 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 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 jConnect version to VERSION_6. Failure to do so may result in data truncation, as reported in JBPM-703.
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 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 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.