Configuring Sybase 12 for use with WebSphere Application Server

This article describes how to do the following:

  • Create, configure, and verify the operation of a Sybase server.
  • Create and configure a database named WAS40 and the Sybase users required by WebSphere Application Server.

The procedures in this article assume that you have installed Sybase and any required ESD fix.

Creating, configuring, and verifying the operation of a Sybase server

Create, configure, and verify the operation of a Sybase server by performing the following steps:

  1. Ensure that you are logged in as the user sybase.
  2. Ensure that the DISPLAY, TERM, and JAVA_HOME environment variables are set properly.
  3. Start the Sybase Adaptive Server setup and configuration utility by entering the following command:
    $ asecfg
  4. In the ASE Setup and Configuration window, click Configure a new server.
  5. In the srvbuild-Select Servers to Build window, click the radio button Adaptive Server and type the server name. The name must match the value that you defined for the DSQUERY environment variable.
  6. Click OK. The srvbuild-Server Attribute Editor window opens.
  7. In the Master device path field, enter the value home_directory/master, where the home_directory is the installation directory of the Sybase software and, in this example installation, the home directory of the user sybase.
  8. Accept the default values for the Master device size (MB) and Master database size (MB) fields.
  9. In the Sybsystemprocs device path field, enter the value home_directory/sybsystemprocs. As before, home_directory is the installation directory of the Sybase software and the home directory of the user sybase.
  10. Accept the default values for the other fields and click Edit Advanced Adaptive Server Attributes. The srvbuild-Server Attribute Editor window opens.
  11. In the Sybsystemdb (two-phase commit) device path field, enter the value home_directory/sybsystemdb.
  12. Accept the default values for the other fields and click Build Server!. The srvbuild-Status Output window opens, showing the status of the various configuration tasks as they run.
  13. Near the end of the configuration process, the srvbuild-question window opens, asking whether you want to localize your Adaptive Server to use a language other than U.S. English or to use a different default character set or sort order. For this example configuration, click No. (If you need to change these parameters, click Yes and refer to the Sybase installation documentation for more information.)
  14. If the configuration is successful, the following message is displayed in the srvbuild-Status Output window:
    Server 'server_name' was successfully created.
    Done
  15. Click OK to close the srvbuild-Status Output window.
  16. In the srvbuild-Select Servers to Build window, click Exit.
  17. A srvbuild-question window opens, asking whether you want to exit from the utility. Click Yes.
  18. If the ASE Setup and Configuration window continues to be displayed, click Exit.
  19. Use the following procedure to check the operation of the server you created:
    1. As the user sybase, use the following command to log into the Adaptive Server as the user sa and start the Sybase isql interactive utility. The variable home_directory is the installation directory of the Sybase software and, in this example configuration, the home directory of the user sybase. The variable server_name is the name of the server you just created.
      $ home_directory/OCS-12_0/bin/isql -Usa -P -Sserver_name

      You see the isql prompt:

      1>

      Note: The Adaptive Server installation and setup processes require certain user roles. Different user roles own different responsibilities and privileges. The user sybase is the UNIX login account that owns all of the Sybase installation directories and files, sets permissions on those directories and files, and performs the installation and upgrading of Adaptive Server. The user sa, created when you install the Sybase software, is not a UNIX login account; it is specific to Adaptive Server and is used to log in to Adaptive Server with the isql command. It is the Sybase system administrator in charge of creating user accounts, assigning permissions on databases, and creating new databases.

    2. Shut down the server by entering these commands:
      1> shutdown
      2> go

      Text similar to the following is displayed:

      Server SHUTDOWN by request.
      The SQL Server is terminating this process. . .
    3. Navigate to the home_directory/ASE-12_0/install directory by entering the following command:
      $ cd home_directory/ASE-12_0/install
    4. Start the server by entering the following command:
      $ startserver -f RUN_server_name
      where server_name is the value that you set for the DSQUERY environment variable.

      Check the messages that appear to ensure that no errors are reported.

    5. Press Return when a line similar to the following is displayed:
      00:00000:00001:2000/05/09 13:19:14.32 server    'iso_1' (ID = 1).
  20. As the user sybase, use the following procedure to ensure that any installed fix was applied correctly to the Sybase base installation.
    1. Type the following command to launch the Sybase isql interactive utility:
      $ home_directory/OCS-12_0/bin/isql -Usa -P -Sserver_name

      You see the isql prompt:

      1>

    2. Type the following commands:
      1> select @@version
      2> go

      Text similar to the following appears. Note that the installed patch appears in this listing.

      Adaptive Server Enterprise/12.0/P/SWR 8774 ESD 1/Sun_svr4/OS  \
      5.6/1580/32bit/FBO/Tue Dec  7 03:10:20 1999                                                                                                                                                        (1 row affected)
    3. If necessary, enter the following command to exit the isql utility:
      1> quit
  21. To use the jConnect 5.2 Java Database Connectivity (JDBC) driver, set the JDBC_HOME and CLASSPATH environment variables by doing the following (for this example installation, assume the use of jConnect 5.x):
    1. Set JDBC_HOME to the directory where you have installed jConnect (in this example installation, home_directory/jConnect-5_2).
    2. Set CLASSPATH to the location of your jConnect JAR file (in this example installation, home_directory/jConnect-5_2/classes/jconn2.jar).
    3. To enable the jConnect verification steps performed in Step 22, append CLASSPATH with home_directory/jConnect-5_2/classes.
    4. Log out and log back in as the user sybase to enable the changes to the environment.
  22. To verify that the jConnect driver is operating correctly, test the installation by running the supplied Version program. The Version program connects to a demonstration database that Sybase makes available on the Internet. Therefore, you must have Internet access to run the program successfully. To run the Version program, do the following:
    1. Navigate to the directory represented by the JDBS_HOME environment variable (in this example installation, home_directory/jConnect-5_2) by entering the following command:
      $ cd home_directory/jConnect-5_2
    2. Enter the following command to run the Java program:
      $ java sample2.SybSample Version

      The SybSample window appears, which displays source code in the top pane, text in the middle pane, and status information in the bottom pane. If you see the following text in the middle Sample Output pane, jConnect has been installed correctly:

      Using JDBC provider version 5.2
      jConnect  (TM) for JDBC(TM)/5.2. . .
      
  23. As the user sybase, run the instmsgs.ebf script to update your SQL server messages to the latest installed fix level. Save the output of this step to an operating system output file.
    $ home_directory/OCS-12_0/bin/isql -Usa -P -Sserver_name -n \
    -ihome_directory/ASE-12_0/scripts/instmsgs.ebf -ooutput_file
  24. As the user sybase, use the following commands to create a password for the user sa. (Immediately after a new installation, there is no password on the sa account, and one must be created.) Passwords beginning with numerals must be enclosed in quotation marks (").
    $ home_directory/OCS-12_0/bin/isql -Usa -P  \
    -Sserver_name> sp_password null, new_sa_password

Creating and configuring the WAS40 database and Sybase users required for WebSphere Application Server

Before you can effectively run WebSphere Application Server, you must create a Sybase database named WAS40 and the Sybase users EJSADMIN and EJB, which are required by WebSphere Application Server. To perform these tasks, complete the following steps:

  1. Type the following command to launch the Sybase isql interactive utility:
    $ home_directory/OCS-12_0/bin/isql -Usa -Pnew_sa_password -Sserver_name

    You see the isql prompt:

    1>

  2. Initialize a database device named WASDEV by entering the following commands:
    1> disk init name = 'WASDEV',
    physname = '/home_directory/was.dat',
    vdevno = 3,
    size = 5000
    2> go

    In these commands, the value for the name option is the device name, the value for the physname option is the name of the raw disk partition or operating system file, the value for the vdevno option is the identifying number for the database device, and the value for the size option is the size of the database device in 2-KB blocks.

    Note: The value of the vdevno option must be set to the next available (unused) device. To list the devices in use, enter the following commands:

    1> sp_helpdevice
    2> go

    The value of 5000 for the size option is equivalent to 10 MB. You might need to specify a higher value for production use. The Sybase default is 2 MB, which is too small for use with WebSphere Application Server.

  3. Enter the following commands to create the database WAS40 on the WASDEV database device and allocate 10 MB of space to the database. The database name must be in uppercase letters.
    1> create database WAS40 on WASDEV = 10
    2> go

    Text similar to the following is displayed:

    CREATE DATABASE: allocating 4864 pages on disk 'WASDEV'
  4. Change to the new database by entering the following commands:
    1> use WAS40
    2> go
  5. Create the Sybase login ID and password for WebSphere Application Server by entering the following commands. You will use this database login ID and password when you install WebSphere Application Server. The login ID must be in uppercase letters. The password must be a minimum of six characters.
    1> sp_addlogin EJSADMIN, 6-or-more-character_password, WAS40
    2> go
  6. Add the user EJSADMIN to the database WAS40 by entering the following commands:
    1> sp_adduser EJSADMIN
    2> go
  7. Create a Sybase login ID for enterprise beans by entering the following commands. You will use this login ID and password to access your data source for enterprise beans in WebSphere Application Server. The login ID must be in uppercase letters. The password must be a minimum of six characters.
    1> sp_addlogin EJB, 6-or-more-character_password, WAS40
    2> go
  8. Add the user EJB to the database WAS40 by entering the following commands:
    1> sp_adduser EJB
    2> go
  9. Grant the users EJSADMIN and EJB all object access permissions to the database WAS40 by entering the following commands:
    1> grant all to EJSADMIN, EJB
    2> go
  10. Grant the user EJB the privileges of dtm_tm_role by entering the following commands. (You will enable Data Transaction Management (DTM) in Step 13.
    1> grant role dtm_tm_role to EJB
    2> go
    1> COMMIT
    2> go
  11. Use the following commands to turn on the database option trunc log on chkpt. Turning on this option ensures that committed transactions are removed from the transaction log when the CHECKPOINT checking process occurs. (Transactions are removed from the log file in this case only if 50 or more rows exist in the log.)

    1> use master
    2> go
    1> sp_dboption WAS40, "trunc log on chkpt", true
    2> go

    The following text is displayed:

    Database option 'trunc log on chkpt' turned ON for database 'WAS40'.
    Run the CHECKPOINT command in the database that was changed.
    (return status = 0)
  12. Enter the following commands to complete the configuration:
    1> COMMIT
    2> go
    1> use WAS40
    2> go
    1> COMMIT
    2> go
    1> CHECKPOINT
    2> go
  13. To enable Data Transaction Management (DTM), perform the following steps:
    1. Turn on the enable DTM configuration parameter by entering the following commands:
      1> sp_configure "enable DTM", 1
      2> go
    2. To enable the option to take effect, shut down the database server by entering the following commands:
      1> shutdown
      2> go
    3. Restart the server by entering the following command:
      $ home_directory/ASE-12_0/install/startserver -f RUN_server_name