This document describes how to use Sybase Adaptive Server Enterprise databases within the SilverStream Application Server environment. It includes information about using Adaptive Server Enterprise as a SilverMaster database or as an application database.
NOTE Sybase Adaptive Server Enterprise was formerly known as Sybase SQL Server.
For the latest information on supported database versions, see the SilverStream Application Server release notes. In Windows, you can access the release notes from the SilverStream program group or from SilverStream online help.
This chapter includes these sections:
The following sections describe how to create a Sybase Adaptive Server Enterprise SilverMaster database catalog.
You must install the Sybase client onto the machine where the SilverStream Server resides. Once you have done that, the remaining steps include:
For information about installing the SilverStream Server, see Installing SilverStream on Windows or Installing SilverStream on UNIX.
You must install and configure the jConnect driver on the machine on which you will be installing the SilverStream Server. This driver must be configured before you attempt the SilverStream Server installation.
NOTE SilverStream recommends that you use jConnect 5.
For more information about Sybase jConnect, see www.sybase.com/products/internet/jconnect.
If you are using jConnect 4:
If you are using jConnect 5:
NOTE You do not need to configure the CLASSPATH or JDBC_HOME variables mentioned in the Sybase installation notes if you are using the driver exclusively with SilverStream.
isql -Usa -Ppassword -Sservername -isql_server.sql
jview isqlApp -Usa -Ppassword -S jdbc:sybase:tds:[host]:[port]-1
NOTE You need to connect using the SA account or an account with SA privileges to execute the above statement.
You must configure the Sybase server to support the SilverStream Server. These configuration changes involve sizing your Sybase TEMPDB database appropriately and configuring the correct amount of Sybase procedure cache to support the jConnect stored procedures.
To configure the Sybase server:
You need to connect to the Sybase server as SA or an equivalent.
If the TEMPDB Total Allocated value is less than 30 MB:
The value for total memory is in 2K pages, so the numeric value shown times two equals the amount of memory (in kilobytes) currently allocated to Sybase. Take note of this value and then find the value Procedure Cache Percent. This value should be the percentage equivalent to 13 MB of memory.
To find this value, divide 13000 by the Total Memory amount (in kilobytes). This will give you the percentage that you should enter in the Procedure Cache Percent value.
For example, the default amount of memory allocated to Sybase Adaptive Server Enterprise 11.5 is 9000 pages (equivalent to 18000 kilobytes). If you divide 13000/18000, the value for Procedure Cache Percent equals .72 or 72%. Enter 72 as the new value for the Procedure Cache Percent.
Before you can install SilverStream, you must first create the actual Sybase database (and its underlying database devices). You can create these objects using either ISQL or a graphical tool such as Sybase Central. The following procedure describes how to use Sybase Central to create the objects.
To create the SilverMaster database:
When this process completes, create another new 30 MB device called SilverMasterLog.
Now that the jConnect driver is installed and the SilverMaster database has been created, you must create a Sybase login for use by the SilverStream Server.
To create the login:
In addition, you need to define logins for each application database you will use. The user must have a minimum of SELECT permission against all of the tables in the desired Sybase database (you will need UPDATE and DELETE permissions if your SilverStream application supports these data manipulation operations).
This user account must also have CREATE TABLE permission if you are going to store your SilverStream system tables in the application database (this is the default behavior).
This section describes the known problems and workarounds for Sybase Adaptive Server Enterprise databases in the SilverStream Application Server environment.
You may receive error messages from jConnect with an error code of JZ0C0 and an error message stating "Connection already closed." If you receive this error, verify that you are running Sybase Server version 18.104.22.168. If your Sybase version is not 22.214.171.124, obtain the updates through Sybase Support.
If you cannot update your Sybase server version, separate your SilverStream Application System tables from the Sybase database to work around the problem.
Logins, users, and permissions
The easiest way to avoid potential problems with logins, users, and permissions is to use sa. If problems occur while adding a database, SilverStream Technical Support may ask you to try adding the database as sa to determine if the problem is with logins, users, or permissions.
However, in many environments, it is not practical to obtain the sa password. The following SQL examples show you how to create a login and user with the correct permissions for SilverStream.
EXEC sp_addlogin SilverUser','PASSWORD','mydatabase',
EXEC sp_adduser 'SilverUser','SilverUser','public'
GRANT CREATE TABLE TO SilverUser
Primary keys and foreign keys
The primary and foreign key of older databases (from Sybase System 10 and earlier) may still be defined using system stored procedures named sp_primarykey and sp_foreignkey. Sybase no longer recommends creating primary and foreign keys in this manner. In fact, their newer products, including jConnect, do not recognize primary and foreign keys created in this manner. You can determine if your database uses these stored procedures by examining the DDL used to create your database.
This is what the DDL will look like with an older-style primary key:
CREATE TABLE dbo.U_LBX_ADVICE
advice_key smallint NOT NULL,
<PAYMENT_KEY file_key smallint NOT NULL,
batch_key smallint NOT NULL,
line_no varchar(20) NULL,
item_paid_id varchar(20) NULL,
item_paid_amount money NULL
EXEC sp_primarykey 'dbo.U_LBX_ADVICE',
The newer Sybase Adaptive Server Enterprise format for creating primary keys is part of the CREATE TABLE syntax:
create table U_LBX_ADVICE
advice_key smallint not null,
payment_key smallint not null,
file_key smallint not null,
batch_key smallint not null,
line_no varchar(20) null,
item_paid_id varchar(20) null,
item_paid_amount money null,
constraint PK_U_LBX_ADVICE primary key
(advice_key, payment_key, file_key, batch_key)
If your table already exists and you cannot recreate the table with the CREATE TABLE statement, then use ALTER TABLE as shown below:
ALTER TABLE dbo.U_LBX_ADVICE ADD CONSTRAINT newprimarykey PRIMARY KEY NONCLUSTERED (advice_key,payment_key,file_key,batch_key)
Foreign keys define the relationship between tables. The problem with foreign keys is very similar to the one with primary keys described in the previous section: Sybase's jConnect does not recognize foreign keys created in the older System 10 format using sp_foreignkey. This is the older style DDL that would be used to create foreign keys:
exec sp_foreignkey dbo.U_LBX_ADVICE, dbo.U_LBX_BATCH,
The syntax for adding a similarly defined foreign key is:
ALTER TABLE dbo.U_LBX_ADVICE ADD CONSTRAINT myforeignkey FOREIGN KEY (file_key,batch_key)
REFERENCES sqlfinar1.dbo.U_LBX_BATCH (file_key,batch_key)
You can use the Sybase PowerDesigner tool to update the DDL which creates a database to the newer format. To use this tool, follow these guidelines after backing up your database:
If the jConnect stored procedures are not properly installed and the problem is not detected, then you can have problems adding a database.
Do this to verify that jConnect is properly installed:
Another way to determine if jConnect is successfully installed is to try to run the sample jConnect applications (make sure you have installed the samples: check to make sure that a classes\sample directory exists). Type the following lines at a DOS prompt.
set CLASSPATH=installDir\jconnect-version\classes java sample.SybSample Validate -U sa -P password -S jdbc:sybase:Tds:servername:port
set CLASSPATH=installDir\jconnect-version\classes\jconn2.jar java sample2.SybSample Validate -U sa -P password -S jdbc:sybase:Tds:servername:port
These commands will start an application that can launch a variety of sample jConnect applications by clicking File/Open Sample.