CMVC FREQUENTLY ASKED QUESTIONS: HOW TO PERFORM TYPICAL OPERATIONS WITH SYBASE 11 Document Number TR 29.3284 Angel Rivera CMVC Direct Customer Support IBM Software Solutions Research Triangle Park, North Carolina, USA Copyright (C) 2000, IBM. All rights reserved. REVISION: February 2001: Add handling of development components. DISCLAIMER: This technical report is not an official publication from the CMVC group. The author is solely responsible for its contents. ii CMVC FAQ: Sybase ABSTRACT This technical report provides a collection of hints and tips for CMVC family administrators that use the Sybase database manage- ment system (DBMS), Version 11. Some of the scenarios are: o Installing and configuring Sybase o Starting and stopping Sybase. o Creating a CMVC family under Sybase. o Backup and restore of Sybase databases. o Accessing the CMVC tables. o Error messages and how recover from them. ITIRC KEYWORDS o CMVC o Sybase ABSTRACT iii iv CMVC FAQ: Sybase ABOUT THE AUTHOR ANGEL RIVERA Mr. Rivera is an Advisory Software Engineer and team lead for the CMVC Direct Customer Support team. He joined IBM in 1989 and since then has worked in the development and support of library systems. Mr. Rivera has an M.S. in Electrical Engineering from The Univer- sity of Texas at Austin, and B.S. in Electronic Systems Engi- neering from the Instituto Tecnologico y de Estudios Superiores de Monterrey, Mexico. ABOUT THE AUTHOR v vi CMVC FAQ: Sybase CONTENTS ABSTRACT . . . . . . . . . . . . . . . . . . . . . . . . . III ITIRC KEYWORDS . . . . . . . . . . . . . . . . . . . . . iii ABOUT THE AUTHOR . . . . . . . . . . . . . . . . . . . . . . V Angel Rivera . . . . . . . . . . . . . . . . . . . . . . . v FIGURES . . . . . . . . . . . . . . . . . . . . . . . . . VIII 1.0 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . 1 1.1 User roles in Sybase and CMVC . . . . . . . . . . . . 1 1.2 Updates to the CMVC User's Reference manual . . . . . 2 1.3 Disclaimers . . . . . . . . . . . . . . . . . . . . . 2 1.4 Year 2000 readiness for CMVC and Sybase . . . . . . . 3 1.4.1 CMVC 2.3.1 provides support for the Year 2000 . . . 3 1.4.2 Sybase 11 is Year 2000 ready . . . . . . . . . . . 3 1.5 How to get the most up to date version of this technical report. . . . . . . . . . . . . . . . . . . . . . 3 1.6 Acknowledgements . . . . . . . . . . . . . . . . . . . 4 2.0 INSTALLING SYBASE . . . . . . . . . . . . . . . . . . . 5 2.1 Development components . . . . . . . . . . . . . . . . 5 2.2 Number of Sybase licenses needed by CMVC . . . . . . . 5 2.3 Pre-installation tasks performed by root . . . . . . . 6 2.4 Pre-installation tasks performed by the Sybase user id 7 2.5 Installation tasks performed by the Sybase user id . . 7 2.6 Installing Sybase Servers . . . . . . . . . . . . . 10 2.7 Post-installation tasks . . . . . . . . . . . . . . 12 2.8 Configuration items to remember . . . . . . . . . . 13 3.0 STARTING AND STOPPING THE SYBASE SERVERS . . . . . . . 15 3.1 State diagram for Sybase Servers (Adaptive and Backup) 15 3.2 How to start the Sybase Adaptive Server . . . . . . 16 3.3 How to start the Sybase Backup Server . . . . . . . 16 3.4 How to stop the Sybase Adaptive Server . . . . . . . 17 3.5 How to stop the Sybase Backup Server . . . . . . . . 17 3.6 How to handle shared memory files used by Sybase . . 18 3.7 How to query the status of the Sybase servers . . . 18 3.8 How to find out the version of Sybase . . . . . . . 19 4.0 CREATING A DATABASE FOR A CMVC FAMILY . . . . . . . . 21 4.1 Activities to be performed by root . . . . . . . . . 21 4.2 Activities to be performed by the Sybase user id. . 21 4.3 Activities to be performed by the CMVC family user id. 22 5.0 CONFIGURING SYBASE . . . . . . . . . . . . . . . . . . 25 5.1 How to see what devices are defined in Sybase? . . . 25 5.2 How to find which device numbers are in used in Sybase? . . . . . . . . . . . . . . . . . . . . . . . . . 25 Contents vii 5.3 How to setup the 2 devices for a CMVC database in Sybase? . . . . . . . . . . . . . . . . . . . . . . . . . 25 5.4 How to dump the transaction log in Sybase? . . . . . 26 5.5 How to move the transaction log to a new device in Sybase? . . . . . . . . . . . . . . . . . . . . . . . . . 27 5.6 How to add more DASD to SYBASE_DBDEV? . . . . . . . 27 5.7 How to drop a device in Sybase? . . . . . . . . . . 28 5.8 How to drop a normal database in Sybase? . . . . . . 28 5.9 How to drop a suspect database in Sybase? . . . . . 28 5.10 How to find out how much space is used in the syslog in Sybase? . . . . . . . . . . . . . . . . . . . . . . . 28 5.11 How to create a database in Sybase? . . . . . . . . 29 6.0 BACKUP AND RESTORE . . . . . . . . . . . . . . . . . . 31 6.1 Backup of Sybase databases . . . . . . . . . . . . . 31 6.2 Restore of Sybase databases . . . . . . . . . . . . 32 7.0 ACCESSING THE SYBASE DATABASE TABLES . . . . . . . . . 35 7.1 How to use isql to gain access to the database . . . 35 7.2 How to insert a new Host entry into the Hosts table 35 7.3 How to look at the contents of Hosts table for first user . . . . . . . . . . . . . . . . . . . . . . . . . . 36 7.4 How to exit from the database . . . . . . . . . . . 37 7.5 How to use a script to execute SQL commands . . . . 37 7.5.1 Korn shell script: sybaseScript . . . . . . . . . 38 7.6 How to use isql with a file with SQL commands . . . 39 8.0 COMMON ERROR MESSAGES WITH SYBASE . . . . . . . . . . 41 8.1 Server error 4002 when starting cmvcd . . . . . . . 41 8.2 SQL Run-Time Error -22 when executing stopCMVC . . . 42 8.3 SQL Smg 1801: Database already exists when using mkdb 42 8.4 How to dump the transaction log in Sybase due to notifyd . . . . . . . . . . . . . . . . . . . . . . . . . 43 8.5 After changing IP address or hostname of host, Server does not start . . . . . . . . . . . . . . . . . . . . . 43 9.0 COPYRIGHTS, TRADEMARKS AND SERVICE MARKS . . . . . . . 45 FIGURES 1. Portion of a sample .profile for the Sybase user id . . 7 2. Stage diagram for the Sybase Adaptive Server . . . . . 15 viii CMVC FAQ: Sybase 1.0 INTRODUCTION This technical report provides a collection of hints and tips for CMVC family administrators that use the Sybase database manage- ment system (DBMS), version 11. The chapters in this TR are organized in the expected sequence of utilization of Sybase from a CMVC point of view: o Chapter 2.0, "Installing Sybase" on page 5 provides some overall recommendations for installing Sybase. o Chapter 3.0, "Starting and stopping the Sybase servers" on page 15 describes how to start, stop, query the status, and find out the version of the Sybase DBMS. Chapter 4.0, "Cre- ating a database for a CMVC family" on page 21 describes how to create a CMVC family under Sybase. o Chapter 5.0, "Configuring Sybase" on page 25 describes how to perform configuration activities for Sybase to properly create and support CMVC 2.3.1 families. o Chapter 6.0, "Backup and restore" on page 31 describes how to perform a backup of the database and how to restore it. o Chapter 7.0, "Accessing the Sybase database tables" on page 35 provides procedures for accessing the database tables and views used by CMVC. o Chapter 8.0, "Common error messages with Sybase" on page 41 describes the most common error messages that a CMVC family administrator may encounter when working with Sybase, and how to recover from them. 1.1 USER ROLES IN SYBASE AND CMVC In this TR, we will use the same terminology for the user roles as explained in the Chapter "Overview" of the Sybase manual: o Operating System Administrator (root). This user maintains the operating system with superuser or "root" privileges. o System Administrator (sa) This user maintains the Adaptive Server system; the login name is "sa". This login is specific to the Adaptive Server and is used to login to the Adaptive Server using the isql command. This login is not a UNIX login. Introduction 1 o Sybase Login (login) It is a UNIX login ("sybase") that owns all the Sybase installation directories and files, and performs the instal- lation and upgrading of Adaptive Server. o CMVC family user id It is a UNIX login that owns a particular CMVC family, including the directories and files and database, and per- forms maintenance activities. 1.2 UPDATES TO THE CMVC USER'S REFERENCE MANUAL In the CMVC User's Reference manual, the note on page 53 of Chapter 7 is incorrect (section "4.4.2 Database information on CMVC Tables for queries"). The following is the correct information: ORACLE 7 INFORMIX SYBASE DB2 -------- ------------- ------- ------------ number integer int int varchar2 varchar varchar varchar long text in table text long varchar 1.3 DISCLAIMERS To avoid misunderstandings with the purpose of this technical report and to better understand its scope, the following dis- claimers are in order: o This technical report is not an official publication from the CMVC group. The author is solely responsible for its con- tents. o This technical report was prepared when working with Sybase 11.9.2 in Solaris 2.6. Therefore, if you have a different version of the mentioned software, then you may expect some differences in the information or in the procedures described in this technical report. o This technical report covers information that the author has gathered thru the years while working with the CMVC technical support team. o It is the intention of this technical report to provide recommendations and guidelines that can be helpful to CMVC administrators when using CMVC families where the database is stored in Sybase. In some cases, the procedures will not be 2 CMVC FAQ: Sybase exhaustive, and will just show the overall sequence that has worked before, which might be different in your case. o Real values that were used in our setup will be used in this technical report. Thus, you will need to customize the com- mands that you issue to reflect the values that are mean- ingful to your setup. o It is assumed that the reader has knowledge of CMVC, Sybase and the appropriate operating system. This technical report is not a substitute to the information provided by Sybase, CMVC and the appropriate operating system. Please refer to the appropriate documentation pro- vided with the corresponding software. 1.4 YEAR 2000 READINESS FOR CMVC AND SYBASE 1.4.1 CMVC 2.3.1 provides support for the Year 2000 ____________________________________________________ CMVC 2.3.1 is a new version-release-modification of CMVC (branched from CMVC 2.3.0.24) which uses 4 digits to represent the years instead of the 2 digits used in 2.3.0 and previous ver- sions. Thus, CMVC 2.3.1 provides support for the Year 2000. For more details on CMVC and its Year 2000 readiness status, see the file: ftp://ftp.software.ibm.com/ps/products/cmvc/README.year2000.txt 1.4.2 Sybase 11 is Year 2000 ready ___________________________________ Sybase 11 is Year 2000 ready, according to the information pro- vided in: http://www.sybase.com/inc/corpinfo/year2000_index.html 1.5 HOW TO GET THE MOST UP TO DATE VERSION OF THIS TECHNICAL REPORT. The most up to date version of this technical report can be obtained from the IBM CMVC ftp site at URL: ftp://ftp.software.ibm.com/ps/products/cmvc/doc/tr/trcmfsyb.txt For the list of available technical reports, see the file: ftp://ftp.software.ibm.com/ps/products/cmvc/doc/tr/README.index.txt Introduction 3 1.6 ACKNOWLEDGEMENTS Many of the questions and answers that are compiled in this tech- nical report were obtained from the CMVC forum in the IBMPC con- ferencing disk and from the CMVC6000 forum in the IBMUNIX conferencing disk. We want to thank the main participants in these electronic forums for their support! We want to thank in particular the following co-workers: o Edna Wong Kyu, OEM Lab in IBM RTP, North Carolina, USA. o Keith Purcell, OEM Lab in IBM RTP, North Carolina, USA. o Lee Perlov, Websphere/VisualAge TeamConnection Services, IBM RTP, North Carolina, USA. 4 CMVC FAQ: Sybase 2.0 INSTALLING SYBASE This chapter provides some overall recommendations when installing Sybase. You need to consult the following manuals (or the equivalent for your operating system): o For the details on the requirements and installation steps: Installing Adaptive Server Enterprise on Sun Solaris (September 1998 edition) This manual will be referenced in this document as the "Sybase Installation Guide". o For the installation of the development components: Installing Open Client/Server Products on UNIX Platforms It is assumed that this is the first installation of Sybase in your server. That is, this TR does not explain how to perform advanced installation/upgrade tasks. It is highly recommended that you open 2 windows, one for the tasks to be performed by root and the other for the tasks to be performed by the Sybase user id. This will help you a lot, because you have to change the userid quite frequently. 2.1 DEVELOPMENT COMPONENTS In order to develop code that uses Sybase (such as the CMVC server for Sybase), it is necessary to install the following development components: o Open Client/C Developer's Kit (Version 11.1.11) o Embedded SQL/C (Version 11.1.0) 2.2 NUMBER OF SYBASE LICENSES NEEDED BY CMVC The minimum number of user licenses required by Sybase to run 4 CMVC daemons and all the other CMVC tools is shown below: Installing Sybase 5 1 license for isql to query the database 1 license for the notify daemon 1 license for chfield 1 license for other CMVC tools, such as vcPath 4 one license for each cmvcd daemon. --- 8 Total However, the number of user licenses required depends on the number of CMVC server daemons started by the CMVC administrator. This number varies depending on the number of CMVC clients that simultaneously request service from the CMVC server. 2.3 PRE-INSTALLATION TASKS PERFORMED BY ROOT 1. Login as root. 2. Ensure that there is enough shared memory for Sybase Adaptive Server. Verify that the /etc/system (kernel) has at least 29 MB for the following variable: set shmsys:shminfo_shmmax = YYY Also ensure that there are at lest 6 shared memory segments: set shmsys:shminfo_shmseg = XXX If you modify this file, then you need to reboot in order to pick up the latest kernel changes. 3. Change the umask to ensure that group and other have read and execute permissions, but not write permission: umask 022 4. Create a new user id, such as "sybase" that belongs to the "sybase" group (you may need to create this group too). This userid will be known as the "Sybase user id" or "Sybase login". The home directory for this user id will be $SYBASE: /export/home/sybase This directory needs to have enough space to hold the Sybase components. 5. Proceed with 2.4, "Pre-installation tasks performed by the Sybase user id" on page 7. 6 CMVC FAQ: Sybase 2.4 PRE-INSTALLATION TASKS PERFORMED BY THE SYBASE USER ID 1. Login as the Sybase user id. 2. Change the umask to ensure that group and other have read and execute permissions, but not write permission: umask 022 3. Set environment variables in the .profile as shown in Figure 1. This is only the top portion of the file. #!/usr/bin/ksh # Set up environment variables for Sybase export SYBASE=/export/home/sybase export DSQUERY=hostName # specify here your Sybase server Other environment variables: export PATH=/usr/bin:/usr/ucb:/etc:/usr/sbin:. export PATH=$SYBASE/bin:$SYBASE/install:$PATH export LD_LIBRARY_PATH=$SYBASE/lib:$LD_LIBRARY_PATH export DISPLAY=hostName:0.0 # where hostName is a valid one set -o vi # To allow vi commands for line command retrieval Figure 1. Portion of a sample .profile for the Sybase user id 4. Logout and login again to refresh the environment for the Sybase user id. 5. Proceed with 2.5, "Installation tasks performed by the Sybase user id." 2.5 INSTALLATION TASKS PERFORMED BY THE SYBASE USER ID This section describes how to unload the Sybase code from the CD-ROM into disk, by using an X-Windows Environment: 1. Login as the Sybase login. 2. Place the Adaptive Server product CD in the CD-ROM drive. Installing Sybase 7 Instead of the Adaptive Server CD, you can place the CD that has the development components such as: o Open Client/C Developer's Kit o Embedded SQL/C The Solaris operating system automatically mounts the CD. The "File Manager - sybasecd" screen is displayed. NOTE: Do not close this window, you will need it to eject the CD later on. 3. At the UNIX prompt, execute the following: cd /cdrom cd cdrom0 ./sybsetup 4. The "sybsetup" screen is displayed. Choose "Unload Sybase Products". 5. The "Installation Destination" screen is displayed. The value of the SYBASE environment variable is used in the entry field. Click the "continue" button, which is the checkmark button. 6. The "Installation Source" screen is displayed. Provide the device media and device location: a. Select CD-ROM (already selected by default). The push button should be darker, which indicates the selection. b. Enter: /cdrom/cdrom0/sybimage Click continue. 7. You might be prompted to enter the "Customer Authorization" string or CAS. If so, then enter it. Click continue. 8. The "Products Selection" screen is displayed. At the top of the screen, verify the data in the section "Installation Information". 8 CMVC FAQ: Sybase o For the Adaptive Server Select the products to be installed. Version Product Name 11.9.2 Adaptive Server Enterprise 11.9.2 Historical Server (this is the Backup Server) o For the development components Version Product Name 11.1.1 Open Client/C 11.1 Embedded SQL/C Click continue. 9. The "Install Products?" screen is displayed. Click continue to start the unloading of the products. 10. The "Installation Status" screen is displayed which will show the status of the unloading of the products. 11. At the end of the unloading process, the "Install sybsetup?" screen is displayed. It is recommended answer "Yes" to store sybsetup in $SYBASE/bin to allow you to run sybsetup without remounting the CD. 12. The "Success" screen should be displayed. Click OK to continue. 13. The "Installation Complete" screen is displayed. Click on exit (the icon that shows exiting from a door). Click on exit again. 14. When you have exited sybsetup, remove the CD from the drive by doing the following: a. From the window where sybsetup was started, issue "cd /" to get out of the /cdrom directory. Otherwise, you will get an error "device busy" when trying to eject the CD. b. Go to the "File Manager -sybasecd" window and select "File - Eject". This will close the File Manager appli- cation and eject the CD. 15. At this point, you have unloaded Adaptive Server onto your system, but you do not have yet a fully functional product. To continue, see 2.6, "Installing Sybase Servers" on page 10. Installing Sybase 9 2.6 INSTALLING SYBASE SERVERS The easiest method for installing a Sybase server will be used, which is "Quick Start installation". This method uses the system defaults. 1. Login as the Sybase login. 2. Determine the location, type and size of each database device. In our case they will be stored in $SYBASE. The database devices are operating system files or portions of a disk (called raw partitions) used to store databases and database objects. Adaptive Server requires the following database devices in a local file system. For details see the table "Adaptive Server database devices" in Chapter "Installing Sybase Servers". o Device "master" to store the system databases. Although Sybase recommends to use a raw partition, in this example, a file will be used instead. o Device "sysprocsdev" to store the sybsystemprocs data- base. o Device "sybsystemdb" to allow the two-phase commit. o Device "sybsecurity" to allow auditing. 3. Start the "srvbuild" utility, by entering: srvbuild 4. The "srvbuild - Select Servers to Build" screen is displayed. Click the check boxes for the following. In this example the Sybase server box is named "oem-sn13" (this is the value for the DSQUERY environment variable). Server type Server name Adaptive Server oem_sn13 Backup Server oem_sn13_back Click OK to continue. 5. The "srvbuild - Server Attribute Editor" screen is displayed for the Adaptive Server. 6. Provide the values for the following fields. In this example the database devices (master and sybsystemprocs) will be installed as files under $SYBASE: Master device path: /export/home/sybase/master.dat Sybsystemprocs device path: /export/home/sybase/systemprocs.dat 10 CMVC FAQ: Sybase For the other attributes, use the default values provided. Notice that the "Interfaces file entry" has the following values in this example: Transport type Host name Port number tli tcp oem-sn13 4100 Click on OK to continue. 7. The "srvbuild question" dialog is displayed to warn you that a file is not recommended for a production Adaptive Server. In this example, this is OK. Thus, click on "yes" to con- tinue. 8. The "srvbuild - Server Attribute Editor" screen is displayed for the Backup Server. 9. Accept the default values provided. Notice that the "Interfaces file entry" has the following values in this example: Transport type Host name Port number tli tcp oem-sn13 4200 Click on "Build Servers!" to continue, 10. The "srvbuild - Status Output" screen is displayed, which shows the progress of building the servers. 11. The "srvbuild question" dialog is displayed with the question about to localize the Adaptive Server to use other languages. In this example, the answer is "no", because srvbuild creates an Adaptive Server with the following defaults: o us_english language. o iso-Latin1 character set. o Binary sort order. 12. Scroll to the bottom of the status output screen, and verify that "Done" is shown as the last entry. This indicates that the installation is complete. Click exit. 13. Continue with 2.7, "Post-installation tasks" on page 12. Installing Sybase 11 2.7 POST-INSTALLATION TASKS 1. Login as the Sybase login. 2. Verify that the servers that were installed are running, by performing one of the following: o Issue showserver: $ $SYBASE/install/showserver The output may look like this (notice that the lines were split in order to fit them into the available width of this document): UID PID PPID C STIME TTY TIME CMD sybase 7893 1 0 15:20:19 pts/5 0:00 sh -c /export/home/sybase/bin/ backupserver -Soem_sn13_back -e/export/home/sybase sybase 7891 7890 0 15:19:03 ? 0:35 /export/home/sybase/bin/dataserver -soem_sn13 -d/export/home/sybase/master.dat sybase 7890 1 0 15:19:03 pts/5 0:00 sh -c /export/home/sybase/bin/ dataserver -soem_sn13 -d/export/home/sybase/maste sybase 7894 7893 0 15:20:19 pts/5 0:00 /export/home/sybase/bin/ backupserver -Soem_sn13_back -e/export/home/sybase/inst o For each server, enter the following command at the UNIX prompt: $SYBASE/bin/isql -Usa -P -Sserver_name Where server_name is "oem_sn13" for Adaptive Server or "oem_sn13_backup" for the Backup Server. If the server is running, you will see the prompt for isql: 1> 3. Change the password for the Sybase System Administrator. In this example it is changed from the default NULL to "sybase": $SYBASE/bin/isql -Usa -P -Soem_sn13 1> sp_password null, sybase 2> go Password correctly set. (return status = 0) 1> quit NOTE: This new password is the value for the CMVC environ- ment variable SYBASE_SA_PASS. 4. To verify that the password is fine, login into isql again: 12 CMVC FAQ: Sybase $SYBASE/bin/isql -Usa -Psybase -Soem_sn13 1> quit 2.8 CONFIGURATION ITEMS TO REMEMBER The important configuration items to remember are the following: o The location of the home directory for the Sybase login is represented by: export SYBASE=/export/home/sybase o The Sybase server is represented by: export DSQUERY=oem-sn13 # specify here your Sybase server o The password for the Sybase Administrator. This will be represented in the .profile for the CMVC family as: export SYBASE_SA_PASS=sybase Installing Sybase 13 14 CMVC FAQ: Sybase 3.0 STARTING AND STOPPING THE SYBASE SERVERS This chapter describes how to start, stop, query the status, and find out the version of the Sybase servers. Because the utilities to start and stop the Sybase Servers are in the $SYBASE/install subdirectory, it is recommended to ensure that this directory is included in the definition for PATH, in that way you can start and stop the Sybase DBMS easier: export PATH=$PATH:$SYBASE/install You can start the Adaptive Server and the Backup Server from the UNIX command line by using runserver files. Each time a server is installed, the srvbuild program creates a runserver file that contains the information required to restart that server. These files are stored in the directory $SYBASE/install. For Adaptive Server and Backup Server, the runserver file is named "RUN_servername", where servername is the name of the server. 3.1 STATE DIAGRAM FOR SYBASE SERVERS (ADAPTIVE AND BACKUP) The figure Figure 2 shows the main states in which a Sybase Server (Adaptive or Backup) can be at a given point in time, and the commands that are used to trigger the state transitions. +--------------+ RUN_servername & +--------------+ | |--------->---------| | | Off-line | | On-line | | |---------<---------| | +--------------+ isql shutdown +--------------+ Figure 2. Stage diagram for the Sybase Adaptive Server NOTES: 1. You need to be the Sybase user id in order to issue the com- mands for starting or stopping the Sybase servers. Starting and stopping the Sybase servers 15 3.2 HOW TO START THE SYBASE ADAPTIVE SERVER To start the Sybase Adaptive Server do the following: 1. Login into the Sybase user id. 2. Start the Sybase Adaptive server. In this example to invoke the runserver files ensure that you specify the processing in the background, in that way you can exit from the Sybase window. RUN_oem_sn13 & Wait for the end of the startup process, which should look like this: 00:00000:00001:2000/01/22 17:13: server 'bin_iso_1' (ID = 50) 00:00000:00001:2000/01/22 17:13: server on top of default character set: 00:00000:00001:2000/01/22 17:13: server 'iso_1' (ID = 1). 00:00000:00001:2000/01/22 17:13: server Loaded default Unilib conversion handle. The state of the DBMS will move from off-line to on-line. 3.3 HOW TO START THE SYBASE BACKUP SERVER To start the Sybase Backup Server do the following: 1. Login into the Sybase user id. 2. Start the Sybase Backup server. In this example to invoke the runserver files ensure that you specify the processing in the background, in that way you can exit from the Sybase window. RUN_oem_sn13 back & Wait for the end of the startup process, which should look like this: Logging Backup Server messages in file '/export/home/sybase/install/oem_sn13_back.log' The state of the Sybase Backup Server will move from off-line to on-line. 16 CMVC FAQ: Sybase 3.4 HOW TO STOP THE SYBASE ADAPTIVE SERVER The preferred method of stopping Adaptive Server is to use the isql "shutdown" command: 1. Login into the Sybase user id. 2. Execute the following: $ isql -Usa -Psybase -Soem_sn13 1> shutdown 2> go Server SHUTDOWN by request. The SQL Server is terminating this process. CT-LIBRARY error: ct_results(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect CT-LIBRARY error: ct_cancel(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect Notice that the CT-LIBRARY errors are normal behavior. 3.5 HOW TO STOP THE SYBASE BACKUP SERVER To stop the Backup Server, the Sybase manuals recommend to use the isql "shutdown" command as described in 3.4, "How to stop the Sybase Adaptive Server," but in this example, there was an error message: 1. Login into the Sybase user id. 2. Execute the following: . $ isql -Usa -Psybase -Soem_sn13_back 1> shutdown 2> go No language handler installed. Language cmd: shutdown 1> quit 3. Thus, if you are getting this error too, then perform this step. Identify the process id (pid) for the Backup Server, then issue the kill command: Starting and stopping the Sybase servers 17 $ showserver UID PID PPID C STIME TTY TIME CMD sybase 18044 18043 0 15:51:38 pts/6 0:00 /export/home/sybase /bin/backupserver -Soem_sn13_back -e/export/home/sybase/inst $ kill 18044 $ showserver UID PID PPID C STIME TTY TIME CMD [1] + Done RUN_oem_sn13_back & 3.6 HOW TO HANDLE SHARED MEMORY FILES USED BY SYBASE When Adaptive Server starts, it creates the following files in $SYBASE to store information about the shared memory segments that it uses: SERVER_NAME.krg When Adaptive Server is shut down in a normal manner, the shared memory files are automatically removed. However, if Adaptive Server crashes or is killed with the "kill -9" command, then these files are not deleted. You need read and write permissions on these files to restart Adaptive Server in order to overwrite them. 3.7 HOW TO QUERY THE STATUS OF THE SYBASE SERVERS One way to find out if the Sybase servers are active, is by fol- lowing these steps: 1. Login as the Sybase login. 2. Issue showserver: $ $SYBASE/install/showserver The output may look like this (notice that the lines were split in order to fit them into the available width of this document): 18 CMVC FAQ: Sybase UID PID PPID C STIME TTY TIME CMD sybase 7893 1 0 15:20:19 pts/5 0:00 sh -c /export/home/sybase/bin/ backupserver -Soem_sn13_back -e/export/home/sybase sybase 7891 7890 0 15:19:03 ? 0:35 /export/home/sybase/bin/dataserver -soem_sn13 -d/export/home/sybase/master.dat sybase 7890 1 0 15:19:03 pts/5 0:00 sh -c /export/home/sybase/bin/ dataserver -soem_sn13 -d/export/home/sybase/maste sybase 7894 7893 0 15:20:19 pts/5 0:00 /export/home/sybase/bin/ backupserver -Soem_sn13_back -e/export/home/sybase/inst If you do not see any processes related to Sybase, then the Sybase servers are not active. 3.8 HOW TO FIND OUT THE VERSION OF SYBASE To find out the version of Sybase, perform the following: 1. Login into the Sybase user id. 2. Ensure that the Sybase Adaptive Server is on line. 3. Issue the following: $ isql -Usa -Psybase -Soem_sn13 1> select @@version 2> go The output is different for Sybase 11.0.2 and 11.9.2: o For Sybase 11.0.2: SQL Server/11.0.2/P/Sun_svr4/OS 5.4/1/OPT/Tue Sep 10 14:03:23 PDT 1996 o For Sybase 11.9.2: Adaptive Server Enterprise/11.9.2/1031/P/Sun_svr4/OS 5.5.1 Starting and stopping the Sybase servers 19 20 CMVC FAQ: Sybase 4.0 CREATING A DATABASE FOR A CMVC FAMILY This chapter describes how to create a CMVC family under Sybase. This is an overview on how to create a CMVC family that uses a Sybase database. For more information see the manual CMVC Server Administration and Installation, 2.3. This chapter also explains how to find out which devices (see 5.1, "How to see what devices are defined in Sybase?" on page 25) have been defined, and how to expand them (see 5.6, "How to add more DASD to SYBASE_DBDEV?" on page 27). 4.1 ACTIVITIES TO BE PERFORMED BY ROOT 1. Login as root. 2. Create the CMVC family user id, which should belong to the same group to which the Sybase user id belongs and to group 0. In this example, the family name is "cms13syb" and it belongs to the groups "sybase" and "0". 3. Add the new CMVC family user id in /etc/hosts. 4. Add the new CMVC family user id in /etc/services. 5. (Optional) Create a directory where to store the family data- base device and the log device. You have to choose a place where you have enough file system space for the database. Create a directory that can be easily recognized, as "cmvc- space", and which should be owned by the Sybase user id: cd /export/home2 mkdir cmvc-space 6. Logout as root. 4.2 ACTIVITIES TO BE PERFORMED BY THE SYBASE USER ID. It is very important to avoid using the default system devices (which is used by the Sybase Adaptive Server itself) when cre- ating the database for a family. This family database needs to have its own table and index database devices, and they can be created by performing the following steps. Creating a database for a CMVC family 21 1. Login as the Sybase user id. 2. Find out more about the devices that are defined in Sybase. See 5.1, "How to see what devices are defined in Sybase?" on page 25. 3. Because a CMVC family database needs 2 Sybase device numbers that are not in use, it is necessary to identify those device numbers that are currently in use. See 5.2, "How to find which device numbers are in used in Sybase?" on page 25. 4. Create the database device and the log device for the CMVC family. See 5.3, "How to setup the 2 devices for a CMVC database in Sybase?" on page 25. 5. Logout as the Sybase user id. 4.3 ACTIVITIES TO BE PERFORMED BY THE CMVC FAMILY USER ID. 1. Login as the CMVC family user id. 2. Copy the sample profile for a CMVC family: cp /usr/lpp/cmvc/install/profile.sybase .profile The above command uses the default location for CMVC. All the CMVC related environment variables are already listed and documented in the sample profile for a CMVC family that uses Sybase. 3. Modify the .profile. Follow the instructions in the comments in the header of the file. 4. Ensure that all the environment variables related to Sybase are defined: ## ## Sybase setup ## ## Specify the home directory for the Sybase user id. export SYBASE=/export/home/sybase ## Specify the password for the CMVC family. ## It is NOT the password for the Sybase user id. export SYBASE_PASS=year99 ## Specify the password for the Sybase Administrator (not a Unix login) ## It is NOT the password for the Sybase user id. export SYBASE_SA_PASS=sybase 22 CMVC FAQ: Sybase ## Specify the Sybase Adaptive Server name. export DSQUERY=SYBASE ## To setup the devices, do the following and change the variables: ## xxx (family name), YYY (file path) ## ## isql -Usa -P$SYBASE_SA_PASS -S$DSQUERY ## disk init name="cms13syb_dev", ## physname="/export/home/sybase/cmvc_space/cms13syb_dev.dev", ## vdevno=8, size=65536 ## go ## disk init name="cms13syb_log", ## physname="/export/home/sybase/cmvc_space/cms13syb_log.log", ## vdevno=9, size=65536 ## go ## export SYBASE_DBDEV="cms13syb_dev=8" export SYBASE_LOGDEV="cms13syb_log=9" ## ## Modification of PATH ## PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin PATH=$PATH:$CMVC_HOME/install:$CMVC_HOME/bin:$CMVC_HOME/samples PATH=$PATH:$SYBASE/bin:$SYBASE/install PATH=$PATH:$HOME/bin:. export PATH export LD_LIBRARY_PATH=$SYBASE/lib:$LD_LIBRARY_PATH The value for the SYBASE_DBDEV and SYBASE_LOGDEV environment variables will be used to create the database for the CMVC family user id. 5. May need to setup LD_LIBRARY_PATH to $SYBASE/lib in Solaris. export LD_LIBRARY_PATH=$SYBASE/lib:$LD_LIBRARY_PATH 6. After editing the profile, exit your session and login again in order to have a clean environment. 7. Create the CMVC family. At this time, all the environment variables and the configuration items should be ready for creating and starting the CMVC family with 2 server daemons: a. mkfamily b. mkdb -d c. cmvcd cms13syb 2 d. notifyd Creating a database for a CMVC family 23 e. You can now use the CMVC family. f. To monitor the usage of the CMVC daemons do: monitor 1 Press an alphanumerical key to stop the monitor command. g. To stop the CMVC daemons do: stopCMVC cms13syb 24 CMVC FAQ: Sybase 5.0 CONFIGURING SYBASE This chapter describes some configuration tasks for Sybase in order to properly create and support CMVC 2.3.1 families. 5.1 HOW TO SEE WHAT DEVICES ARE DEFINED IN SYBASE? You can do the following in isql: $ isql -U sa -P sybase 1> sp_helpdevice 2> go You may need to use the sybaseScript to redirect the output to a file, for better reading. See 7.5.1, "Korn shell script: sybaseScript" on page 38. Also, see 5.2, "How to find which device numbers are in used in Sybase?." 5.2 HOW TO FIND WHICH DEVICE NUMBERS ARE IN USED IN SYBASE? You can do the following in isql: $ isql -U sa -P sybase > select distinct low/16777216 from sysdevices order by low > go The output may look like this: 0 1 (2 rows affected) 5.3 HOW TO SETUP THE 2 DEVICES FOR A CMVC DATABASE IN SYBASE? Before you can setup the two devices that are needed for a CMVC database in Sybase, you need to know which device numbers are already in use and then you can select a number that is not already in use; see 5.2, "How to find which device numbers are in used in Sybase?" for more details. To setup the devices, do the following and change the variables: Configuring Sybase 25 isql -U sa -P sybase -S$DSQUERY 1> disk init name="cms13syb_dev", 2> physname="/export/home/sybase/cms13syb_dev.dev", 3> vdevno=4, size=2048 4> go 1> disk init name="cms13syb_log", 2> physname="/export/home/sybase/cms13syb_log.log", 3> vdevno=5, size=2048 4> go 1> quit Then you can export the following variables in the .profile of the CMVC family: export SYBASE_DBDEV="cms13syb_dev=4" export SYBASE_LOGDEV="cms13syb_log=5" The details for "disk init" are: o This command allocates space for the device; you can use either a "cooked" (a file system) or a "raw" physical device (such as /dev/syb_dev). In the example above it is a cooked device. o Specify a number for "vdevno" that has not been used before. o The size is specified in 512-byte blocks. 5.4 HOW TO DUMP THE TRANSACTION LOG IN SYBASE? PROBLEM In case that you get the following error: 0010-061 Database error, 1105 (Can't allocate space for object '' in database 'tempdb' because the 'system' segment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.), has occurred. ACTION 1. Login into the family account. 2. Stop the cmvc daemons for the family. 3. Perform the following (first check the database space then dump) 26 CMVC FAQ: Sybase isql -Usa -P$SYBASE_SA_PASS 1> sp_spaceused 2> go 1> dump transaction your_db_name_that_report_full with no_log 2> go You may get a warning message about the syslog being dumped. This is OK. 4. Exit isql 1> quit 5. Start the CMVC family daemons. 5.5 HOW TO MOVE THE TRANSACTION LOG TO A NEW DEVICE IN SYBASE? 1. Run: sp_logdevice dbName, device_name 2. Process enough transactions to fill current log. 3. Wait until all currently active transactions are done. 4. Run: dump transaction 5. To verify, run: sp_helplog 5.6 HOW TO ADD MORE DASD TO SYBASE_DBDEV? o Login as the Sybase user id. o isql -Usa -Psybase o Dump the transaction log (replace "familyName"): dump transaction familyName with no_log go o Display the usage of database space: Configuring Sybase 27 use familyName go sp_spaceused go o For example, extend the database "cms13syb" on device "cms13syb_dev" by 2 MB: use master go alter database cms13syb on cms13syb_dev=2 go o exit 5.7 HOW TO DROP A DEVICE IN SYBASE? You can use: sp_dropdevice deviceName You cannot drop a device that is in use by a database. 5.8 HOW TO DROP A NORMAL DATABASE IN SYBASE? You can use: isql -U sa -P password > drop database databaseName > go 5.9 HOW TO DROP A SUSPECT DATABASE IN SYBASE? You can use: isql -U sa -P password > dbcc dbrepair (databaseName, dropdb) > go 5.10 HOW TO FIND OUT HOW MUCH SPACE IS USED IN THE SYSLOG IN SYBASE? From the desired database, run: 28 CMVC FAQ: Sybase isql -U sa -P password > dbcc checktable(syslogs) > go 5.11 HOW TO CREATE A DATABASE IN SYBASE? Determine how much space you will need and search the devices to find out if there is space in them for the database. If there is not enough space in master, then you will need to specify a dif- ferent device; for details see 5.3, "How to setup the 2 devices for a CMVC database in Sybase?" on page 25. Once you have identified the device to use, then use the appro- priate device number in the following command: create database dbName on some_device_name=3 log on another_device_name=2 The above will create a database named dbName on a device previ- ously created called some_device_name which is 3 Mb in size (this command does not take 512-byte blocks). The log parameter will place the database log on a device named another_device_name and will allocate 2 Mb for it. Configuring Sybase 29 30 CMVC FAQ: Sybase 6.0 BACKUP AND RESTORE This chapter describes how to perform a backup of the database and how to restore it. 6.1 BACKUP OF SYBASE DATABASES This section describes how to create a dump device by using the SP_ADDUMPDEVICE. and how to use the DUMP DATABASE command to dump the database to the device recently created. For more details see the Sybase documentation, section "Dumping and Loading: SQL Server Scheme". 1. Login as the CMVC family administrator ($LOGNAME): 2. Stop the CMVC family: stopCMVC $LOGNAME 3. Create a directory where to store the backup files: $ $HOME/backup $ chmod 777 $HOME/backup 4. If using Sybase 11 you must start the Backup Server: a. login as the Sybase user id b. cd install c. RUN_SYB_BACKUP & 5. Invoke isql: $ isql -Usa -P$SYBASE_SA_PASS 6. Run the following command to back up the family database. You need to replace FAMILY with the name of your family and specify the actual directory where you want the backup file to be saved. o For Sybase 4.9 or 10 sp_addumpdevice "disk", dobackup, "/export/home/family/backup/family.bak", 2 go dump database family to dobackup go Backup and restore 31 Where the number 2 in sp_addumpdevice is the control type. o For Sybase 11 sp_addumpdevice "disk", dobackup, "/export/home/family/backup/family.bak" go dump database family to dobackup go Wait until you get the following message that indicates that the backup procedure is done: Backup Server: 3.42.1.1: DUMP is complete (database familyName). Now you have a backup copy of your database in the file /export/home/family/backup/family.bak. NOTES: 1. You can also dump the database to a tape device by changing "disk" to "tape", "/home/family/backup/family.bak" to the device name of the tape and the controller number parameter 2 to another number which must be between 3 and 8 (tape volume) in the above command. 2. Use SP_DROPDEVICE DEVICENAME to drop dump devices. 6.2 RESTORE OF SYBASE DATABASES Use the LOAD DATABASE command to restore the database for the CMVC family. For more details see the Sybase documentation, section "Dumping and Loading: SQL Server Scheme". 1. Always make sure that the destination database must be large enough to hold the amount of storage space that was actually allocated to the dumped database. 2. Login as the CMVC family administrator ($LOGNAME): 3. Stop the CMVC family: stopCMVC $LOGNAME 4. If appropriate, simulate the loss of the database and then recreate the basic one: $ rmdb $ mkdb -d 32 CMVC FAQ: Sybase 5. If using Sybase 11 you must start the Backup Server: a. login as the Sybase user id b. cd install c. RUN_SYB_BACKUP & 6. Ensure that you have the appropriate dump device defined in Sybase. This dump device should have the backup of the data- base of the CMVC family. 7. Run the following command to restore the Sybase database: $ isql -Usa -P$SYBASE_SA_PASS 1> load database family from dobackup 2> go Where family is the name of the CMVC family. 8. In Sybase 11, wait for the following message that indicates that the restore was completed: Backup Server: 3.42.1.1: LOAD is complete (database familyName). 9. In Sybase 11, you need to have stop and restart the SYBASE servers in order to change the status from offline to online. Then you can restart the CMVC family. Backup and restore 33 34 CMVC FAQ: Sybase 7.0 ACCESSING THE SYBASE DATABASE TABLES This chapter provides procedures for accessing the database tables and views used by CMVC. The concrete example of adding a new host list entry is shown; although we discourage the manual modification of the database, in some situations (such as after renaming the hostName) it is necessary to add a host list entry directly into the database. The section 7.5, "How to use a script to execute SQL commands" on page 37 shows a Korn shell script that can be used to query the database. 7.1 HOW TO USE ISQL TO GAIN ACCESS TO THE DATABASE Sometimes it is necessary to access the Sybase database used by the CMVC family in order to query the entries for a certain table or view. To gain access to the Sybase database, do the following: 1. Login to the CMVC family user id. 2. Enter the following, where $SYBASE_PASS is the password for the CMVC family user id, and not the Sybase Administrator, nor the Sybase user id. $SYBASE/bin/isql -U $LOGNAME -P $SYBASE_PASS 3. After this, you will see the prompt: 1> 4. Now you can enter other SQL commands. You do not need to add a semicolon; but you need to use the keyword "go" in order to commit the transaction. 5. Use "quit" to exit from isql. 7.2 HOW TO INSERT A NEW HOST ENTRY INTO THE HOSTS TABLE 1. Login to the CMVC family. 2. isql -U$LOGNAME -P$SYBASE_PASS 3. Perform the following statements via isql: Accessing the Sybase database tables 35 1> insert into Hosts (userId, name, login, address) 2> values (1, 'example.cary.ibm.com', 'loginId', 0) 3> go NOTES: a. b. The userId value of 1 is the id associated with the ori- ginal CMVC_SUPERUSER. c. During mkdb, the value for "name" (with a value shown here of 'example.cary.ibm.com') is $HOSTNAME, where this value is obtained by means of executing: /usr/bin/hostname $CLIENT_HOSTNAME d. During mkdb, the value for "login" is obtained from $CLIENT_LOGIN 4. Continue with 7.3, "How to look at the contents of Hosts table for first user." 7.3 HOW TO LOOK AT THE CONTENTS OF HOSTS TABLE FOR FIRST USER 1. Perform the following statements inside isql: 1> select name,login from Hosts where userId=1 2> go NOTES: a. Refer to the CMVC User's Reference, Chapters 6 and 7 for the field names and sizes. b. Sybase is case sensitive for names of objects. The headers are always shown in the case that was used to create them. 2. An example of the Output from the SQL command: login name ------------------------------- ----------------------------------------------- ------------------------------------------------------ cms13syb oem-sn13 loginId example.cary.ibm.com 36 CMVC FAQ: Sybase This mean that the host name is example.cary.ibm.com and that the login is loginId. 3. Continue with 7.4, "How to exit from the database." 7.4 HOW TO EXIT FROM THE DATABASE 1. Perform the following statements from isql: 1> quit 7.5 HOW TO USE A SCRIPT TO EXECUTE SQL COMMANDS The Korn shell script shown in 7.5.1, "Korn shell script: sybaseScript" on page 38 can be used to interact with the Sybase isql tool to issue SQL commands and to store the output in an output file. In this example, the following SQL command will be executed (show all users, sorted by login): select * from Users where login like '%' order by login Actually, the above command needs to be specified between 2 spe- cific lines in the Korn script; each line contains the delimiter !!!. The following lines show the correct location; notice that the select statement does not terminate with a semicolon, but you need to use the keyword "go" in order to commit the transaction and to allow multiple SQL commands to be issued in the same script: isql -U $1 -P$SYBASE_PASS >> $LOG 2>&1 <> $LOG 2>&1 <