<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="http://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
jBPM3 on Sybase
</h3>
<span style="margin-bottom: 10px;">
modified by <a href="http://community.jboss.org/people/admin">Administrator Administrator</a> in <i>jBPM</i> - <a href="http://community.jboss.org/docs/DOC-12936">View the full document</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p><div class="toc" style="border: 1px dashed black; padding: 10px;"><ul><ul><ul><li>
<a class="jive-link-anchor-small" href="#Configuration_highlights">Configuration highlights</a>
</li>
<ul><li>
<a class="jive-link-anchor-small" href="#Lock_scheme">Lock scheme</a>
</li>
<li>
<a class="jive-link-anchor-small" href="#Number_of_locks">Number of locks</a>
</li>
<li>
<a class="jive-link-anchor-small" href="#Distributed_transactions">Distributed transactions</a>
</li>
</ul><li>
<a class="jive-link-anchor-small" href="#Known_anomalies">Known anomalies</a>
</li>
<ul><li>
<a class="jive-link-anchor-small" href="#Page_size">Page size</a>
</li>
<li>
<a class="jive-link-anchor-small" href="#Gaps_in_the_autogenerated_IDs">Gaps in the auto-generated IDs</a>
</li>
</ul><li>
<a class="jive-link-anchor-small" href="#Past_pests_which_may_bite_again">Past pests (which may bite again)</a>
</li>
<ul><li>
<a class="jive-link-anchor-small" href="#JDBC_drivers">JDBC drivers</a>
</li>
<li>
<a class="jive-link-anchor-small" href="#Wide_table_support">Wide table support</a>
</li>
<li>
<a class="jive-link-anchor-small" href="#Binary_datatypes">Binary datatypes</a>
</li>
</ul></ul></ul></ul></div></p><h3 id="Configuration_highlights">Configuration highlights</h3><h4 id="Lock_scheme">Lock scheme</h4><p>Sybase offers three <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/X30621.htm">locking schemes</a>: <tt>allpages</tt>, <tt>datapages</tt> and <tt>datarows</tt>. 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 <em>lock scheme</em> parameter is set to <tt>allpages</tt>. The allpages scheme may be too coarse to applications like jBPM where many small rows share a single page.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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.</p><pre class="jive-pre"><code class="jive-code jive-sql"><span class="jive-sql-keyword">alter</span> <span class="jive-sql-keyword">table</span> table_name
     lock {allpages | datapages | datarows}
</code></pre><p>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 <strong>sp_configure</strong> procedure.</p><pre class="jive-pre"><code class="jive-code">sp_configure 'lock scheme', 0, datarows
</code></pre><h4 id="Number_of_locks">Number of locks</h4><p>You may encounter the beast labeled <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.dc00729_1500/html/errMessageAdvRes/BACBJHDF.htm">Error 1204</a> under heavy job execution.</p><pre class="jive-pre"><code class="jive-code jive-java">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.
</code></pre><p>The number of locks available is controlled by the configuration parameter <strong>number of locks</strong>. To chech the current value, call the  <strong>sp_configure</strong> procedure.</p><pre class="jive-pre"><code class="jive-code">[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</code></pre><p>To fix the problem, try doubling the number of locks.</p><pre class="jive-pre"><code class="jive-code">1> sp_configure 'number of locks', 20000
2> go</code></pre><h4 id="Distributed_transactions">Distributed transactions</h4><p>Sybase 15.0.2. Developer Edition supports Distributed Transaction Management (DTM) and other features described in the <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00468.1502/html/aselxqig/aselxqig5.htm">Quick Installation Guide</a>. The developer edition is available as a free download on Linux and does not require installing a license file.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Use the <strong>sp_lmconfig</strong> procedure to determine which edition of Adaptive Server your are running. Sybase returns EE, SE, DE or XE based on the edition.</p><pre class="jive-pre"><code class="jive-code">sp_lmconfig 'edition'</code></pre><p>DTM features are <em>not</em> enabled by default. The instructions for enabling DTM are provided in the jConnect <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.dc39001_0605/html/prjdbc/CHDIFEIE.htm">Programmer's Reference</a> and the ASE <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc31650.1502/html/dtm/BHCHBBBD.htm">Using DTM Features</a> guide, and summarized here.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Enable basic DTM Features and transaction coordination with the <strong>sp_configure</strong> procedure. Restart Adaptive Server for the changes to take effect.</p><pre class="jive-pre"><code class="jive-code">sp_configure 'enable DTM', 1
sp_configure 'enable xact coordination', 1
</code></pre><p>Grant the <strong>dtm_tm_role</strong> to any user who will participate in distributed transactions with the <strong>sp_role</strong> procedure.</p><pre class="jive-pre"><code class="jive-code">sp_role 'grant', 'dtm_tm_role', 'jbpmtest'</code></pre><h3 id="Known_anomalies">Known anomalies</h3><h4 id="Page_size">Page size</h4><p>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.</p><pre class="jive-pre"><code class="jive-code">Warning: Row size (14372 bytes) could exceed row size limit, which is 4012 bytes.
</code></pre><p>It is worth emphasizing that Sybase will only fail to write a record that <em>actually</em> 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.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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 <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0/title.htm">documentation</a>, 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 <a class="jive-link-wiki-small" href="http://community.jboss.org/docs/DOC-16177">jBPM3 Text Columns</a>.</p><h4 id="Gaps_in_the_autogenerated_IDs"><span>Gaps in the auto-gener</span><span>ated IDs</span></h4><p>Some users in the <a class="jive-link-external-small" href="http://www.jboss.com/index.html?module=bb&op=viewtopic&t=89119">forum</a> reported seeing a sudden jump in the generated IDs, such as</p><pre class="jive-pre"><code class="jive-code">select ID_ from JBPM_PROCESSINSTANCE
               1
               2
               3
               4
5000000000000002
</code></pre><p>This is a known issue with ASE, known as the 'identity gap'. As described in an <a class="jive-link-external-small" href="http://www.sypron.nl/idgaps.html">article</a> by Rob Verschoor on the subject:</p><blockquote class="jive-quote"><p>An 'identity gap' is a large, sudden, and unexpected jump of values in an identity column, usually as a result of a <span style="font-family: courier new,courier;"><span class="code">shutdown with nowait</span></span>, although it can also occur as a result of certain dump/load scenarios.</p></blockquote><p>Refer to the <a class="jive-link-external-small" href="http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/20271;pt=17866/*">Sybase Manuals</a> for more information and for configuration options.</p><h3 id="Past_pests_which_may_bite_again">Past pests (which may bite again)</h3><h4 id="JDBC_drivers">JDBC drivers</h4><p>At least two viable driver options exist for Sybase.</p><ul><li><a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.help.jconnjdbc_6.05/title.htm">jConnect</a>, the driver delivered with Sybase ASE</li><li><a class="jive-link-external-small" href="http://jtds.sourceforge.net/">jTDS</a>, an open souce driver for Sybase and MS SQL</li></ul><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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.</p><pre class="jive-pre"><code class="jive-code">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)
  ...
</code></pre><h4 id="Wide_table_support">Wide table support</h4><p>Since version 12.5, Sybase ASE provides <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.dc39001_0605/html/prjdbc/BHCJEABE.htm">wide table support</a>, which means larger limits than in previous versions.</p><ul><li class="fi"><p>Tables can contain 1,024 columns.</p></li><li class="ds"><p><tt>varchar</tt> and <tt>varbinary</tt> columns can contain more than 255 bytes of data.</p></li><li class="ds"><p>Column names more than 30 character long.</p></li></ul><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>The larger limits are relevant to jBPM in the following ways.</p><ul><li>Several columns have long names.</li><li>Binary data associated to a process are stored in blocks whose size defaults to 1024 KB.</li></ul><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Wide table support is related to the underlying <a class="jive-link-external-small" href="http://jtds.sourceforge.net/faq.html#longColumnNames">TDS protocol</a> version. Both jConnect and jTDS support TDS 5.0 and wide limits starting from version 6 and 0.3 respectively.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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 <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/topic/com.sybase.help.jconnjdbc_5.5.prjdbc/html/prjdbc/X15095.htm">jConnect version to VERSION_6</a>. Failure to do so may result in data truncation, as reported in <a class="jive-link-external-small" href="http://jira.jboss.com/jira/browse/JBPM-703">JBPM-703</a>.</p><h4 id="Binary_datatypes"><span>Binary datatypes</span></h4><p>The database schema generated by Hibernate includes a few <span style="font-family: courier new,courier;">varbinary</span> columns. The manner Sybase handles varbinary data affects jBPM as it results in data truncation in valid data being lost. From the <a class="jive-link-external-small" href="http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug212.htm">Transact-SQL user guide:</a></p><blockquote class="jive-quote"><p>Use the variable-length binary type, <em>varbinary(n)</em>, 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. <strong>Trailing zeros are truncated</strong>.</p><ul></ul></blockquote><p>The above <em>feature</em> was brought to our attention in <a class="jive-link-external-small" href="https://jira.jboss.org/jira/browse/JBPM-702">JBPM-702</a>. 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.</p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Comment by <a href="http://community.jboss.org/docs/DOC-12936">going to Community</a></p>
<p style="margin: 0;">Create a new document in jBPM at <a href="http://community.jboss.org/choose-container!input.jspa?contentType=102&containerType=14&container=2034">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>