Database Configuration Guide

CHAPTER 8

Configuring Sybase Adaptive Server Enterprise Databases

This chapter describes how to use Sybase Adaptive Server Enterprise databases with the Novell exteNd Application Server. It includes information about using Adaptive Server Enterprise as a SilverMaster or deployment database. It contains these sections:

NOTE:   Sybase Adaptive Server Enterprise was formerly known as Sybase SQL Server.

For more information    For the latest information on supported database versions, see the Novell exteNd Application Server Release Notes.

 
Top of page

Setting up Sybase Adaptive Server Enterprise databases

This section describes how to create a SilverMaster database (and deployment databases) using Sybase Adaptive Server Enterprise.

First, install the Sybase client onto the machine where the Novell exteNd Application Server will reside. Then perform these steps:

  1. Install the Sybase jConnect classes and stored procedures.

  2. Configure the Sybase server.

  3. Create database devices and the actual SilverMaster database as a Sybase database.

  4. Create a database login to be used by the application server.

  5. Use the database with the application server.

 
Top of section

Step 1: install Sybase jConnect

You must install and configure the jConnect driver on the machine where you will be installing the application server. This driver must be configured before you install the application server.

NOTE:   It's recommended that you use jConnect 5.x.

Procedure To install Sybase jConnect:

  1. Install the jConnect driver.

    For more information    For more information about Sybase jConnect, see www.sybase.com.

  2. Make sure the location of the jConnect driver is specified in the AGCLASSPATH variable on your application server machine. For Windows, AGCLASSPATH is a system environment variable. For UNIX, you'll find it in the .agprofile file. For NetWare, you need to use the setenv command to set AGCLASSPATH (setenv AGCLASSPATH=path1;path2;path3;...).

    If you are using jConnect 4:

      AGCLASSPATH=installDir\jConnect-version\classes 
    

    If you are using jConnect 5:

      AGCLASSPATH=installDir\jConnect-version\classes\jconn2.jar 
    

    For more information    For more information on listing JDBC driver files via AGCLASSPATH, see the chapter on data source configuration in the Administrator's Guide.

    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 the application server.

  3. Install the jConnect stored procedures onto the Sybase server.

    For more information    For more information, see the Sybase jConnect for JDBC Installation Guide or www.sybase.com.

 
Top of section

Step 2: configure the Sybase server

You must configure the Sybase server to support the application 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.

Procedure To configure the Sybase server:

  1. 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.

  2. Right-click the TEMPDB database and select Properties.

  3. From the Usage tab, check the Total Allocated value.

    If the TEMPDB Total Allocated value is less than 30 MB:

  4. Right-click the Sybase server and select Configure.

  5. Scroll down until you see the value Total Memory.

    The value for total memory is in 2K pages, so the numeric value shown times 2 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 13,000 by the Total Memory amount (in kilobytes). This will give you the percentage that you should enter as 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 18,000 kilobytes). If you divide 13,000 by 18,000, the value for Procedure Cache Percent equals .72 or 72%. Enter 72 as the new value for the Procedure Cache Percent.

  6. Restart the Sybase server for the change to take effect.

 
Top of section

Step 3: create the SilverMaster database in Sybase

Before you can install the application server, 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.

Procedure To create the SilverMaster database:

  1. Connect to Sybase using SA or an account with SA equivalence.

  2. From Sybase Central, connect to the destination Sybase server and select Database Devices.

  3. Create a new 30 MB database device called SilverMasterData.

  4. When this process completes, create another new 30 MB device called SilverMasterLog.

  5. From the Databases folder in Sybase Central, create a new database called silvermaster (this is case sensitive).

  6. Create the database using the two devices that you created above, using all 30 MBs of each respective device for data and for log.

  7. Once the database has been created, right-click the database and select Properties.

  8. From the Options tab, select Truncate Log on Checkpoint.

 
Top of section

Step 4: create the Sybase login for the application server

Once the jConnect driver is installed and the SilverMaster database has been created, you must create a Sybase login for use by the application server.

Procedure To create the Sybase login:

  1. From Sybase Central, connect to Sybase using SA or an account with SA equivalence.

  2. 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.

  3. Set the default database for this login to silvermaster and complete the login creation process.

  4. From the Databases folder, right-click the silvermaster database and select Properties.

  5. Click the Change button to change the owner of the database from SA to the smuser login.

You also need to define logins for each deployment database you will use. You must specify CREATE TABLE permission if you are going to store the application server's system tables in the deployment database (this is the default behavior for deployment databases that you add to the server).

 
Top of section

Step 5: use the database with the application server

Now you're ready to access the Sybase Adaptive Server Enterprise database from the application server. This enables you to do any of the following, as appropriate:

For more information    For more information, see the chapter on data source configuration in the Administrator's Guide.

 
Top of page

Troubleshooting

 
Top of section

Adaptive Server Enterprise troubleshooting

This section describes the known problems and workarounds for Sybase Adaptive Server Enterprise databases with the Novell exteNd Application Server.

JZ0C0 errors

You may receive error messages from jConnect with an error code of JZ0C0 and the error message 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 technical support.

If you cannot update your Sybase server version, separate the application server's 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, Novell exteNd 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 the application server.

Primary keys and foreign keys

The primary and foreign keys 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, newer Sybase products, including jConnect, do not recognize keys created that way. You can determine whether your database uses these stored procedures by examining the DDL used to create the 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 above: 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 that creates a database to the newer format. To use this tool, follow these guidelines after backing up your database:

  1. From PowerDesigner's menu, choose File/Reverse Engineering.

  2. Enter either the file containing your database's DDL or the name of an ODBC driver to connect directly to your database.

  3. Choose Database/Generate Database to create a new DDL that will define primary and foreign keys with the correct syntax.

  4. As with any tool, double-check and test the new syntax for any possible omissions.

 
Top of section

jConnect troubleshooting

If the jConnect stored procedures are not properly installed and the problem is not detected, you can have problems adding a database.

One way

Do this to verify that jConnect is properly installed:

Another way

Another way to determine if jConnect is successfully installed is to try running the sample jConnect applications (make sure you have installed the samples: check that a classes\sample directory exists). Type the following lines at a DOS prompt.

These commands will start an application that can launch a variety of jConnect samples (click File/Open Sample).



Copyright © 2004 Novell, Inc. All rights reserved. Copyright © 1997, 1998, 1999, 2000, 2001, 2002, 2003 SilverStream Software, LLC. All rights reserved.  more ...