Installation Guide

Chapter 11 Configuring Sybase Adaptive Server Enterprise Databases

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:

Setting up Sybase Adaptive Server Enterprise databases

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:

Install the Sybase jConnect classes and stored procedures.
Configure the Sybase server.
Create database devices and the actual SilverMaster database as a Sybase database.
Create a database login to be used by the SilverStream Server.
Install the SilverStream Server.

For information about installing the SilverStream Server, see Installing SilverStream on Windows or Installing SilverStream on UNIX.

Step 1: Install Sybase jConnect

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.

Install the jConnect driver.

For more information about Sybase jConnect, see www.sybase.com/products/internet/jconnect.
Set the AGCLASSPATH variable on the machine where your SilverStream Server is running.
If you are using jConnect 4:
```
  AGCLASSPATH=installDir\jconnect-version\classes 
```
If you are using jConnect 5:
```
  AGCLASSPATH=installDir\jconnect-version\classes\jconn2.jar 
```
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.
Install the jConnect stored procedures onto the Sybase Server by executing the following ISQL statement from the c:\program files\sybase\jconnect\sp folder:
```
  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.

Step 2: Configure the Sybase server

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:

From Sybase Central, connect to the destination Sybase server and select Databases.

You need to connect to the Sybase server as SA or an equivalent.
Right-click on the TEMPDB database and select Properties.
From the Usage tab, check the Total Allocated value.

If the TEMPDB Total Allocated value is less than 30 MB:
- Create a new 30 MB database device (from within the Database Devices folder) called TempExtend.
- When this process completes, right-click on the TEMPDB database (from within the Databases folder).
- From the Devices tab, add the new TempExtend device as a DATA device.
Right-click on the Sybase server and select Configure.
Scroll down until you see the value Total Memory.

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.
Restart the Sybase server for the change to take effect.

Step 3: Create the SilverMaster database in Sybase

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:

Connect to Sybase using SA or an account with SA equivalence.
From Sybase Central, connect to the destination Sybase server and select Database Devices.
Create a new 30 MB database device called SilverMasterData.

When this process completes, create another new 30 MB device called SilverMasterLog.
From the Databases folder in Sybase Central, create a new database called silvermaster (this is case sensitive).
Create the database using the two devices that you created above using all 30 MBs of each respective device for data and for log.
Once the database has been created, right-click on the database and select Properties.
From the Options tab, select Truncate Log on Checkpoint.

Step 4: Create the Sybase login for the SilverStream Server

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:

From Sybase Central, connect to Sybase using SA or an account with SA equivalence in order to create the login.
From the Login folder, add a new Login (for example, you could call it smuser). You must assign this account a password of at least six characters.
Set the default database for this login to silvermaster and complete the Login creation process.
From the Databases folder, right-click on the silvermaster database and select Properties.
Click on the Change button to change the owner of the database from SA to the smuser 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).

Troubleshooting

This section describes the known problems and workarounds for Sybase Adaptive Server Enterprise databases in the SilverStream Application Server environment.

JZ0C0 errors

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 11.0.3.2. If your Sybase version is not 11.0.3.2, 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.

Login. The login defines access to an Adaptive Server Enterprise database, as well as the default database and password. Use this SQL script to create a login called SilverUser:
```
  USE master  
  go  
  EXEC sp_addlogin SilverUser','PASSWORD','mydatabase',  
  'us_english',"SilverUser"  
  go  
```
User. A user defines a set of permissions for a particular database, and is associated with a login. Use this SQL script to create a user called SilverUser:
```
  EXEC sp_adduser 'SilverUser','SilverUser','public'  
  go  
  GRANT CREATE TABLE TO SilverUser  
  go  
```

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.

Primary keys

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  
  )  
  go  
  EXEC sp_primarykey 'dbo.U_LBX_ADVICE',  
  advice_key,payment_key,file_key,batch_key

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

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,  
  file_key,  
  batch_key

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)  
  go

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:

From PowerDesigner's menu, choose File/Reverse Engineering.
Enter either the file containing your database's DDL or the name of an ODBC driver to connect directly to your database.
Choose Database/Generate Database to create a new DDL which will define primary and foreign keys with the correct syntax.
As with any tool, double-check and test the new syntax for any possible omissions.

jConnect troubleshooting

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:

Open a DOS window and verify that your AGCLASSPATH environment variable points to your jconnect\classes directory.

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.

If you are using jConnect 4, type the following:

  set CLASSPATH=installDir\jconnect-version\classes 

  java sample.SybSample Validate -U sa -P password -S jdbc:sybase:Tds:servername:port

If you are using jConnect 5, type the following:

  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.