CMVC FREQUENTLY ASKED QUESTIONS: HOW TO PERFORM TYPICAL OPERATIONS WITH INFORMIX 7 REVISION: 28-DEC-1999 Document Number TR 29.3228 Angel Rivera, Kevin Postreich CMVC Direct Customer Support IBM Software Solutions Research Triangle Park, North Carolina, USA Copyright (C) 1999, IBM All rights reserved. DISCLAIMER: This technical report is not an official publication from the CMVC group. The authors are solely responsible for its contents. ii CMVC FAQ: Informix ABSTRACT This technical report provides a collection of hints and tips for CMVC family administrators that use the Informix database manage- ment system (DBMS), Version 7. Some of the scenarios are: o Installing and configuring Informix. o Starting and stopping Informix. o Creating a CMVC family under Informix. o Backup and restore of Informix databases. o Accessing the CMVC tables. o Error messages and how recover from them. ITIRC KEYWORDS o CMVC o Informix ABSTRACT iii iv CMVC FAQ: Informix ABOUT THE AUTHORS 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. KEVIN POSTREICH Mr. Postreich is a staff software engineer with the VisualAge TeamConnection development group. He joined IBM in 1980 as an electronic engineer in Charlotte, North Carolina. He relocated to RTP as an MVS systems programmer. Mr. Postreich is currently a member of the TeamConnection development/test team, and a member of the direct customer support team. ABOUT THE AUTHORS v FIRST REVISION, DECEMBER 1999 Updated appropriate sections to include HP-UX 11 and Informix Client SDK 2.20 (which replaces ESQL/C) and Dynamic Server 7.31. CONTENTS ABSTRACT . . . . . . . . . . . . . . . . . . . . . . . . . III ITIRC KEYWORDS . . . . . . . . . . . . . . . . . . . . . iii ABOUT THE AUTHORS . . . . . . . . . . . . . . . . . . . . . . V Angel Rivera . . . . . . . . . . . . . . . . . . . . . . . v Kevin Postreich . . . . . . . . . . . . . . . . . . . . . . v FIGURES . . . . . . . . . . . . . . . . . . . . . . . . . . IX 1.0 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Disclaimers . . . . . . . . . . . . . . . . . . . . . 1 1.2 Year 2000 readiness for CMVC and Informix . . . . . . 2 1.2.1 CMVC 2.3.1 provides support for the Year 2000 . . . 2 1.2.2 Informix 7.2 and 7.3 are Year 2000 ready . . . . . 3 1.3 How to get the most up to date version of this technical report. . . . . . . . . . . . . . . . . . . . . . 3 1.4 Acknowledgements . . . . . . . . . . . . . . . . . . . 3 2.0 INSTALLING INFORMIX . . . . . . . . . . . . . . . . . . 5 2.1 Number of licenses needed for Informix . . . . . . . . 5 2.2 Order of installation . . . . . . . . . . . . . . . . 6 2.2.1 Need to install Informix ESQL/C 7.2 or higher for resetAge and vcPath . . . . . . . . . . . . . . . . . . . 6 2.3 One-time system activities to prepare for the installation . . . . . . . . . . . . . . . . . . . . . . . 6 2.4 Installing ESQL/C (new name: Informix Client SDK) . . 7 2.5 Installing SQL . . . . . . . . . . . . . . . . . . . . 8 2.6 Installing IDS (Informix Online Dynamic Server) . . . 9 3.0 CONFIGURING INFORMIX . . . . . . . . . . . . . . . . . 11 3.1 Choose names for configuration items . . . . . . . . 11 3.2 Set environment variables . . . . . . . . . . . . . 13 3.3 Allocate disk space for data storage . . . . . . . . 14 3.4 Prepare the ONCONFIG configuration file . . . . . . 15 3.5 Update the /etc/services file . . . . . . . . . . . 17 3.6 Prepare the Connectivity File SQLHOSTS . . . . . . . 17 3.7 Update the /etc/hosts.equiv and $INFORMIXDIR/.rhosts file . . . . . . . . . . . . . . . . . . . . . . . . . . 18 3.8 Initialize OnLine . . . . . . . . . . . . . . . . . 18 3.9 Restart OnLine and find out its status . . . . . . . 19 4.0 STARTING AND STOPPING THE INFORMIX DBMS . . . . . . . 21 4.1 State diagram for the Informix DBMS . . . . . . . . 21 4.2 How to start the Informix DBMS . . . . . . . . . . . 22 4.3 How to stop the Informix DBMS . . . . . . . . . . . 22 4.4 How to query the status of the Informix DBMS . . . . 22 4.5 How to verify the integrity of the Informix DBMS . . 23 4.6 How to find out the version of the Informix DBMS . . 23 Contents vii 5.0 CREATING A DATABASE FOR A CMVC FAMILY . . . . . . . . 25 5.1 Activities to be performed by root . . . . . . . . . 25 5.2 Activities to be performed by the Informix user id. 25 5.3 Activities to be performed by the CMVC family user id. 26 6.0 BACKUP AND RESTORE . . . . . . . . . . . . . . . . . . 29 6.1 Backup of Informix databases . . . . . . . . . . . . 29 6.2 Restore of Informix databases . . . . . . . . . . . 30 7.0 ACCESSING THE INFORMIX DATABASE TABLES . . . . . . . . 31 7.1 How to use interactive isql to gain access to the database . . . . . . . . . . . . . . . . . . . . . . . . 31 7.1.1 Need to setup the proper TERM variable to use isql or dbaccess . . . . . . . . . . . . . . . . . . . . . . 31 7.2 How to use a script to execute SQL commands . . . . 32 7.2.1 Korn shell script: informixScript . . . . . . . . 33 7.3 How to use isql with a file with SQL commands . . . 35 8.0 COMMON ERROR MESSAGES WITH INFORMIX . . . . . . . . . 37 8.1 How to use onmonitor to modify parameters . . . . . 37 8.1.1 Stop the CMVC family . . . . . . . . . . . . . . 37 8.1.2 Find out if there is a dedicated dbspace for the family. . . . . . . . . . . . . . . . . . . . . . . . . 38 8.1.3 Using onmonitor . . . . . . . . . . . . . . . . . 38 8.1.4 Adding a chunk to a database . . . . . . . . . . 39 8.1.5 Modifying the logging to "Unbuffered ANSI" . . . 39 8.1.6 Restart the CMVC family . . . . . . . . . . . . . 40 8.2 If $INFORMIX_DIR/bin/isql does not exist, create link from dbaccess . . . . . . . . . . . . . . . . . . . . . . 40 8.3 During mkdb, problem about the CMVC family not being a trusted user . . . . . . . . . . . . . . . . . . . . . . 41 8.4 During mkdb, problem with no more locks during creation of views . . . . . . . . . . . . . . . . . . . . 41 8.5 Errors when the Informix database is not in "on-line" mode . . . . . . . . . . . . . . . . . . . . . . . . . . 42 8.6 System rollback error when using mkdb . . . . . . . 43 8.7 Need to use onsoctcp for interprocess communication 44 8.8 Problem with unbuffered setting for database logging 45 8.9 Increase logical logs to avoid problems with Level -check . . . . . . . . . . . . . . . . . . . . . . . . . 45 9.0 COPYRIGHTS, TRADEMARKS AND SERVICE MARKS . . . . . . . 47 viii CMVC FAQ: Informix FIGURES 1. State diagram for the Informix DBMS . . . . . . . . . 21 2. Korn shell script: informixScript . . . . . . . . . . 34 Figures ix x CMVC FAQ: Informix 1.0 INTRODUCTION This technical report provides a collection of hints and tips for CMVC family administrators that use the Informix database manage- ment system (DBMS), version 7. The chapters in this TR are organized in the expected sequence of utilization of Informix from a CMVC point of view: o Chapter 2.0, "Installing Informix" on page 5 provides some overall recommendations for installing Informix. o Chapter 3.0, "Configuring Informix" on page 11 describes how to configure Informix in order to properly create and support CMVC 2.3.1 families. o Chapter 4.0, "Starting and stopping the Informix DBMS" on page 21 describes how to start, stop, query the status, verify, and find out the version of the Informix DBMS. Chapter 5.0, "Creating a database for a CMVC family" on page 25 describes how to create a CMVC family under Informix. o Chapter 6.0, "Backup and restore" on page 29 describes how to perform a backup of the database and how to restore it. o Chapter 7.0, "Accessing the Informix database tables" on page 31 provides procedures for accessing the database tables and views used by CMVC. o Chapter 8.0, "Common error messages with Informix" on page 37 describes the most common error messages that a CMVC family administrator may encounter when working with Informix, and how to recover from them. 1.1 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 authors are solely responsible for its con- tents. o This technical report was prepared when working with Informix Dynamic Server: - 7.23 in AIX 4.2.1 - 7.24 in HP-UX 10.20 Introduction 1 - 7.31 in HP-UX 11.00 Therefore, if you have a different version of the mentioned software, then you may expect some differences in the infor- mation or in the procedures described in this technical report. o This technical report covers information that has the author has gathered thru the years while working with the CMVC tech- nical 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 Informix. In some cases, the procedures will not be 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, Informix and the appropriate operating system. This technical report is not a substitute to the information provided by Informix, CMVC and the appropriate operating system. Please refer to the appropriate documentation pro- vided with the corresponding software. 1.2 YEAR 2000 READINESS FOR CMVC AND INFORMIX 1.2.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 2 CMVC FAQ: Informix 1.2.2 Informix 7.2 and 7.3 are Year 2000 ready _______________________________________________ Informix 7.2 and 7.3 are Year 2000 ready, according to the infor- mation provided in: http://www.informix.com/informix/products/year2000.htm 1.3 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/trcmfinf.txt For the list of available technical reports, see the file: ftp://ftp.software.ibm.com/ps/products/cmvc/doc/tr/README.index.txt 1.4 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. Introduction 3 4 CMVC FAQ: Informix 2.0 INSTALLING INFORMIX This chapter provides some overall recommendations when installing Informix. You need to consult the following manual (or its equivalent) for the details on the requirements and installation steps: o AIX and HP-UX 10.20 Informix, Unix Products, Installation Guide, Version 7.21 o HP-UX 11.00 For the new Client SDK: Informix Client Products, Installation Guide, for UNIX, Version 2.2 For SQL: Informix, Unix Products, Installation Guide, Version 7.21 For Dynamic Server: Installation Guide, for Informix Dynamic Server, on UNIX, Version 7.3 It is assumed that this is the first installation of Informix in your server. That is, this TR does not explain how to perform advanced installation/upgrade tasks. 2.1 NUMBER OF LICENSES NEEDED FOR INFORMIX The minimum number of user licenses required by Informix to run 4 CMVC daemons and all the other CMVC tools is shown below: 1 license for isql/dbaccess 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. Installing Informix 5 2.2 ORDER OF INSTALLATION This is the order of installation for the Informix products in our development/test machines: 1. ESQL/C (New name: Informix Client SDK) Starting with Informix 7.2, the component ESQL/C became a software requirement for running the CMVC tools: vcPath and resetAge. o AIX: 7.24 (old name: ESQL/C) o HP-UX 10.20: 7.23 (old name: ESQL/C) o HP-UX 11.00: 2.20 (new name: Informix Client SDK) 2. Informix SQL o AIX: 7.20 o HP-UX 10.20: 7.20 o HP-UX 11.00: 7.20 3. Informix Online Dynamic Server (IDS) o AIX: 7.24 o HP-UX 10.20: 7.23 o HP-UX 11.00: 7.31 2.2.1 Need to install Informix ESQL/C 7.2 or higher for resetAge _________________________________________________________________ and vcPath __________ Due to a change in library files between Informix 7.1 and 7.2, it is necessary to install the extra product Informix ESQL/C (new name: Informix Client SDK) when running on Informix 7.2 or higher, in order to properly use resetAge or vcPath. Otherwise, you will be getting a core dump and messages such as: /usr/lib/dld.sl: Can't open shared library: /usr/informix/lib/esql/libixglx.sl /usr/lib/dld.sl: No such file or directory. 2.3 ONE-TIME SYSTEM ACTIVITIES TO PREPARE FOR THE INSTALLATION 1. Login as root. 2. Create a new group, such as "informix". 3. Create a new user id, such as "informix" that belongs to the "informix" group. This will be known as "the Informix user id". The home directory used in this document is /usr/informix; this is the value for the environment variable $INFORMIXDIR. 6 CMVC FAQ: Informix 2.4 INSTALLING ESQL/C (NEW NAME: INFORMIX CLIENT SDK) 1. Login as root. 2. Set up the environment variables: INFORMIXDIR=/usr/informix export INFORMIXDIR PATH=$INFORMIXDIR/bin:$PATH export PATH 3. Change to the $INFORMIXDIR directory: cd $INFORMIXDIR 4. Mount the CD-ROM, such as: AIX: mount -v'cdrfs' -r'' /dev/cd0 /cdrom HP-UX: mount /dev/dsk/c1t2d0 /cdrom 5. Transfer the software from the CD-ROM to the current direc- tory. Use the statement included in the serial-number keycard provided by Informix: o For ESQL/C: AIX: cpio -icdumvB < /dev/cd0 HP-UX: cpio -icdumvB < "/cdrom/ESQL.CPI;1" HP-UX: Notice that because the CD-ROM that we received was not prepared in a CD-ROM driver/software that sup- ported the Backridge standard, then the double quotes had to be given for the file name. - Execute the installation script: ./installesql Provide the data requested from the installation script. The data can be found in the serial-number keycard provided by Informix. o For Client SDK: HP-UX: tar -xvf /cdrom/CLISDK/CLISDK.TAR - Execute the following installation script: ./installclientsdk Notice that the installesql script is an identical copy of installclientsdk. Thus, there is no need to run installesql. Installing Informix 7 Answer "no" to the rather confusing question: Is ClientSDK being installed along with Informix Dynamic Server with Universal Data Option (Release 9, required to be run as user "informix"? Provide the data requested from the installation script. The data can be found in the serial-number keycard provided by Informix. 6. Un-mount the CD-ROM: umount /cdrom 2.5 INSTALLING SQL 1. Login as root. 2. Set up the environment variables: INFORMIXDIR=/usr/informix export INFORMIXDIR PATH=$INFORMIXDIR/bin:$PATH export PATH 3. Change to the $INFORMIXDIR directory: cd $INFORMIXDIR 4. Mount the CD-ROM, such as: AIX: mount -v'cdrfs' -r'' /dev/cd0 /cdrom HP-UX: mount /dev/dsk/c1t2d0 /cdrom 5. Transfer the software from the CD-ROM to the current direc- tory. Use the statement included in the serial-number keycard provided by Informix, such as: AIX: tar -xvf /dev/cd0 HP-UX: tar -xvf "/cdrom/SQL.TAR;1" HP-UX: Notice that because the CD-ROM that we received was not prepared in a CD-ROM driver/software that supported the Backridge standard, then the double quotes had to be given for the file name. 6. Execute the installation script: ./installsql 8 CMVC FAQ: Informix Provide the data requested from the installation script. The data can be found in the serial-number keycard provided by Informix. 7. Un-mount the CD-ROM: umount /cdrom 2.6 INSTALLING IDS (INFORMIX ONLINE DYNAMIC SERVER) 1. Login as root. 2. Set up the environment variables: INFORMIXDIR=/usr/informix export INFORMIXDIR PATH=$INFORMIXDIR/bin:$PATH export PATH 3. Change to the $INFORMIXDIR directory: cd $INFORMIXDIR 4. Mount the CD-ROM, such as: AIX: mount -v'cdrfs' -r'' /dev/cd0 /cdrom HP-UX: mount /dev/dsk/c1t2d0 /cdrom 5. Transfer the software from the CD-ROM to the current direc- tory. Use the statement included in the serial-number keycard provided by Informix, such as: AIX, Online 7.2: cpio -icdumvB < /dev/cd0 HP-UX, Online 7.2: cpio -icdumvB < /cdrom/ONLINE/ONLINE.CPI HP-UX, Dynamic 7.3: cpio -icdumvB < /cdrom/SERVER/IDS.CPI 6. Execute the installation script for Online Dynamic Server 7.2: ./installonline Provide the data requested from the installation script. The data can be found in the serial-number keycard provided by Informix. 7. Execute the installation script for Dynamic Server 7.3: ./installserver Installing Informix 9 Provide the data requested from the installation script. The data can be found in the serial-number keycard provided by Informix. 8. Un-mount the CD-ROM: umount /cdrom 10 CMVC FAQ: Informix 3.0 CONFIGURING INFORMIX This chapter describes how to configure Informix in order to properly support CMVC 2.3.1 families. The instructions from the following manual allow you to build an OnLine database server that is suitable for a few users and data- bases of a moderate size: INFORMIX-OnLine Dynamic Server Administrator's Guide, Volume 1, Version 7.x Chapter 3, "Installing and Configuring OnLine" 3.1 CHOOSE NAMES FOR CONFIGURATION ITEMS To perform the tasks in this section you need to login as the Informix user id (such as informix). In this section you will choose names for your configuration file, your database server and your service name. The details for the configuration of the files mentioned in this section will be explained later; this section shows only how the items are used. o Configuration file (ONCONFIG) Choose a name for your ONCONFIG configuration file to indi- cate how the file is used. In this document, a variation (ppc3) of the hostname (oem-ppc3) is used as an identifier. For example, in the .profile file, the following environment variable will indicate the file name: ONCONFIG=onconfig.ppc3 export ONCONFIG The actual location of the ONCONFIG configuration file will be: $INFORMIXDIR/etc/$ONCONFIG In this example, once the values are substituted, the full path for the configuration file will be: /usr/informix/etc/onconfig.ppc3 o Database server (DBSERVERNAME) Choose a name for your OnLine database server, which will be known as DBSERVERNAME. You will use this value in many places: Configuring Informix 11 - ONCONFIG configuration file: The DBSERVERNAME is defined in the ONCONFIG configuration file which is located in $INFORMIXDIR/etc/$ONCONFIG. In this document, a variation (oem_ppc3) of the hostname (oem-ppc3) is used. DBSERVERNAME oem_ppc3 # Name of default database server - INFORMIXSERVER environment variable in .profile for the Informix user id. In this document, a variation (oem_ppc3) of the hostname (oem-ppc3) is used. INFORMIXSERVER=oem_ppc3 export INFORMIXSERVER - INFORMIXSERVER environment variable in .profile for the CMVC family user id. In this document, a variation (oem_ppc3) of the hostname (oem-ppc3) is used. export INFORMIXSERVER=oem_ppc3 - SQLHOSTS file The DBSERVERNAME is used in the first column of the SQLHOSTS file ($INFORMIXDIR/etc/sqlhosts). For example, the DBSERVERNAME name is "oem_ppc3": oem_ppc3 onsoctcp oem-ppc3 online3 o Service name The service name will identify the particular Informix OnLine database in a given host. The service name MUST be unique and should NOT be set to an existing user id. That is, do NOT use the user id informix, nor any of the CMVC family names that will use Informix. If you use an existing user id for the Informix service name, you will have connectivity problems that are extremely diffi- cult to diagnose: 0010-381 cmvc client software is not compatible with the cmvc server software for this action. 0010-256 An error ... The service name, which in this document will be "online3", is used in the following files: - /etc/services The service name is properly defined in /etc/services, for example: 12 CMVC FAQ: Informix online3 1301/tcp # informix service-name If the service name is not present in /etc/services, then you will encounter the following runtime problem with CMVC: 0010-063: Database error -931, cannot locate %s server/tcp servers in /etc/services - Connectivity file (SQLHOSTS) The service name is used in the fourth (last) column of the SQLHOSTS file ($INFORMIXDIR/etc/sqlhosts). For example, the service name is "online3": oem_ppc3 onsoctcp oem-ppc3 online3 3.2 SET ENVIRONMENT VARIABLES The default profile provided by the Informix installation is in Bourne shell. The following environment variables for the Informix user id need to be correctly set before you can initialize the Informix OnLine master database: o INFORMIXDIR: it should have the full pathname of the direc- tory where the Informix products are installed. It is the directory just above the "bin" directory, in that way, this variable can be used to specify the Informix bin directory as follows: INFORMIXDIR=/usr/informix export INFORMIXDIR o ONCONFIG: it specifies the name of the active ONCONFIG con- figuration file. If this variable is not present, then OnLine uses the configuration values from the file $INFORMIXDIR/etc/onconfig. ONCONFIG=onconfig.ppc3 export ONCONFIG Thus, the location of the ONCONFIG configuration file is: $INFORMIXDIR/etc/$ONCONFIG o PATH: it should include $INFORMIXDIR and $INFORMIXDIR/bin PATH=$PATH:.:$INFORMIXDIR:$INFORMIXDIR/bin export PATH Configuring Informix 13 o INFORMIXSERVER: it specifies the name of the default database server. You set it to the same value used in the DBSERVERNAME configuration parameter in the ONCONFIG file. If this variable is not set, then OnLine does not build the sysmaster tables, and the ON-Monitor and DB-Access utilities will not work. INFORMIXSERVER=oem_ppc3 export INFORMIXSERVER o TERM, TERMCAP or TERMINFO, INFORMIXTERM: They specify the type of terminal interface. o A typical usage of these variables in the .profile is shown below. INFORMIXDIR=/usr/informix export INFORMIXDIR ONCONFIG=onconfig.ppc3 export ONCONFIG INFORMIXSERVER=oem_ppc3 export INFORMIXSERVER INFORMIXTERM=termcap export INFORMIXTERM TERMCAP=$INFORMIXDIR/etc/termcap export TERMCAP TERM=vt100 export TERM o Logout and login again to refresh the environment by using the most up to date version of the profile. o Verify the settings: set | grep -i INF 3.3 ALLOCATE DISK SPACE FOR DATA STORAGE In this document, "cooked file space" is used for the Informix "chunks" which are the biggest unit of physical disk storage. Cooked file space refers to ordinary Unix files. It is necessary to create the initial "chunk", which for handling CMVC families needs to take at least 30 MB of disk space, which is specified by the ROOTSIZE parameter in the ONCONFIG configura- tion file. 14 CMVC FAQ: Informix Informix recommends to create the initial chunk in a directory that is NOT the home directory of user informix ($INFORMIXDIR). Furthermore, the file should be in a place where user informix should have write permission. To create the initial chunk for Informix, do the following as user root: # cd /disk2 # or the appropriate directory # cat /dev/null > informix_chunk # chmod 660 informix_chunk # chown informix:informix informix_chunk # ls -l informix_chunk -rw-rw---- 1 informix informix 0 Jun 22 12:36 informix_chunk 3.4 PREPARE THE ONCONFIG CONFIGURATION FILE The ONCONFIG configuration file contains values for parameters that describe the OnLine environment. This file resides in $INFORMIXDIR/etc. Do not alter the default onconfig.std, instead, copy it into a new file with the name that you have selected already (such as ppc3), and then modify the new file: $ login informix $ cd etc $ cp onconfig.std onconfig.ppc3 It is necessary to customize some parameters in this file. In our case, the parameters that were changed are shown below. Consult the Informix documentation in case that you need to modify other variables to suit your environment. o Title of the ONCONFIG configuration file. Although it is a comment, it is important to be consistent: # Title: onconfig.ppc3 o ROOTPATH It is the full directory pathname of the cooked file that you just created: ROOTPATH /disk2/informix_chunk # Path for device containing root db o ROOTSIZE Indicates the initial size for the chunk. In order to accom- modate CMVC databases of moderate size, the value needs to be changed from the default 20000 (20 MB) to at least 30000 (30 Configuring Informix 15 MB) to avoid a problem of not enough space in the root dbspace during the Informix initialization command, which is "oninit -i". ROOTSIZE 30000 # Size of root dbspace (Kbytes) o LOGSIZE The default value is 500 KB. The following entry sets it to 4.5 MB which is needed in order to allow the CMVC Level -check and level -commit command (these commands use a lot of log space): LOGSIZE 4500 # Logical log size (Kbytes) o TAPEDEV and LTAPEDEV Setting these parameters to /dev/null allows OnLine to behave as if tape drives were present and log files were being backed up, but in fact the output tape is discarded. With these settings you cannot restore data. TAPEDEV /dev/null # Tape device path LTAPEDEV /dev/null # Log tape device path This initial setting is OK for an initial configuration, but eventually you will need to change them in order to properly backup and restore data. For more information see Chapter 20, "What is logging?" from the INFORMIX-OnLine Dynamic Server, Archive and Backup Guide. o SERVERNUM Change it to some integer between 0 and 255, because each OnLine instance must have a distinct value. It is safer to not keep the default value of 0 to avoid future collisions with another instance: SERVERNUM 123 # Unique id corresponding to a OnLine instance This variable is not the same as the "service name" in /etc/services. o DBSERVERNAME This is the value provided by the $INFORMIXSERVER environment variable: DBSERVERNAME oem_ppc3 # Name of default database server o LOCKS 16 CMVC FAQ: Informix Change the default 2000 to 3000 to avoid problems when exe- cuting the CMVC mkdb command which is used to create the database for the CMVC family: LOCKS 3000 # Maximum number of locks 3.5 UPDATE THE /ETC/SERVICES FILE The service name, which in this document will be "online3", is used in the following files: o /etc/services The service name is properly defined in /etc/services, for example: online3 1301/tcp # informix service-name If the service name is not present in /etc/services, then you will encounter the following runtime problem with CMVC: 0010-063: Database error -931, cannot locate %s server/tcp servers in /etc/services 3.6 PREPARE THE CONNECTIVITY FILE SQLHOSTS The $INFORMIXDIR/etc/sqlhosts file contains information that allows a client application to connect to an Informix database server. An example of an active line in that file is shown below: oem_ppc3 onsoctcp oem-ppc3 online3 It is important to keep the following in mind: o You can separate the fields with spaces or tabs. o The value in the first column is the Informix DB server. This is the value of the environment variable INFORMIXSERVER in the .profile for both the Informix user id and all the CMVC families. By the way, this value is defined in the var- iable DBSERVERNAME, in the ONCONFIG configurable file such as $INFORMIX_HOME/etc/onconfig.ppc3 o The value in the second column determines the shared-memory connection. It is extremely important to specify in the sqlhost file that the interprocess communication for Informix should be done Configuring Informix 17 via TCP/IP sockets (value for 2nd column: onsoctcp) and not via the default shared-memory connection (value: onipcshm). o The value in the third column indicates the name of the host. In this example is "oem-ppc3" and it is the short name, and not the fully qualified name "oem-ppc3.raleigh.ibm.com". o The value in the fourth column indicates the Informix Service Name. which should be also in the /etc/services file. In this example is "online3". 3.7 UPDATE THE /ETC/HOSTS.EQUIV AND $INFORMIXDIR/.RHOSTS FILE Ensure that the following files are properly set with the same information: o /etc/hosts.equiv (you need to be root) o $INFORMIXDIR/.rhosts (you need to be the Informix user id) The contents should be as follows (example): oem-ppc3.raleigh.ibm.com informix oem-ppc3 informix Where the second item in each row is the Informix user id. 3.8 INITIALIZE ONLINE Once all the configuration files are ready, now you must ini- tialize the disk space that is used by the OnLine database server, by executing the following command (as the Informix user id): oninit -i If there are problems, such as oninit is not recognized as being in the path, then check for the group of the Informix user id, which must be "informix". 18 CMVC FAQ: Informix 3.9 RESTART ONLINE AND FIND OUT ITS STATUS After OnLine is initialized, stop it, restart it and find out its running status: 1. Login as the Informix user id. 2. Stop OnLine: onmode -k If there is an error saying: shared memory not initialized for INFORMIXSERVER 'oem_hp15' Then this means that Informix Dynamic Server was not started. If this is the first attempt to start the server, then it is very likely that the /etc/services was not updated with the 'online3' (or similar) to help with the connectivity required by Informix. 3. Start OnLine: oninit 4. Find out its status: onstat | more The important thing here is that you should not see an error message that Informix user id could not connect to the data- base or that the shared memory was not initialized. Configuring Informix 19 20 CMVC FAQ: Informix 4.0 STARTING AND STOPPING THE INFORMIX DBMS This chapter describes how to start, stop, query the status, verify, and find out the version of the Informix DBMS. 4.1 STATE DIAGRAM FOR THE INFORMIX DBMS The figure Figure 1 shows the main states in which an Informix DBMS can be at a given point in time, and the commands that are used to trigger the state transitions. +--------------+ oninit +--------------+ | |--------->---------| | | Off-line | | On-line | | |---------<---------| | +--------------+ onmode -k +--------------+ Figure 1. State diagram for the Informix DBMS NOTES: 1. You need to be the Informix user id in order to issue the commands for starting or stopping the DBMS. 2. Note the difference between the use of 'on-line' and 'OnLine' in the above diagram: o 'On-line' means that the DBMS is active. o 'OnLine' refers to the Informix OnLine Dynamic Server (DBMS). 3. After executing any of the commands above, you can execute 'onstat' next. The header on the onstat output gives the current operating mode. 4. Another way to find out if the Informix DBMS is active, is by using the following command: ps -ef | grep oninit If you do not see any processes related to oninit, then the Informix DBMS is not active. Starting and stopping the Informix DBMS 21 4.2 HOW TO START THE INFORMIX DBMS The command to start the Informix database management system is: oninit The state of the DBMS will move from off-line to on-line. 4.3 HOW TO STOP THE INFORMIX DBMS The command to stop the Informix database management system is: onmode -k The state of the DBMS will move from on-line to off-line. 4.4 HOW TO QUERY THE STATUS OF THE INFORMIX DBMS The command to query the status of the Informix database manage- ment system is: onstat | more The important thing here is that you should not see an error message that Informix user id could not connect to the database. The onstat utility reads shared-memory structures and provides statistics about OnLine that are accurate at the instant that the command executes. There are many options flags that provide different types of information but of interest are: o The flag -c is used to get more information on the configura- tion file ($INFORMIXDIR/etc/$ONCONFIG). o The flag -d is used to get more information on the dbspace chunks, and general information. o The flag -D is used to get more information on the dbspace chunks, and pages reads/writes. o The flag -k is used to get more information on the locks held. If onstat is issued when Informix is down it will display one of these error messages: shared memory not initialized for INFORMIXSERVER 'oem_hp03 or onstat: Cannot attach to shared memory. errno = 22 22 CMVC FAQ: Informix For more information, refer to the Informix-OnLine Dynamic Server Administrator's Guide, Volume 2, Chapter "OnLine Utilities". 4.5 HOW TO VERIFY THE INTEGRITY OF THE INFORMIX DBMS The command to verify the integrity of the Informix database man- agement system is: oncheck The oncheck utility can be used to: o Check specified disk structures for inconsistencies. o Repair index structures found to contain inconsistencies. o Display information about disk structures. There are many options flags that provide different types of information but of interest are: o The flag -pe is used to display information about the dbspace chunk and extents. o The flags -pt and -pT are used to display space usage. For more information, refer to the Informix-OnLine Dynamic Server Administrator's Guide, Volume 2, Chapter "OnLine Utilities". 4.6 HOW TO FIND OUT THE VERSION OF THE INFORMIX DBMS To find out the version of the Informix DBMS, perform the fol- lowing: 1. Login into the Informix user id. 2. Issue one of the following commands: o oninit -V The output will have something like this: INFORMIX-OnLine Version 7.24.UC5 Software Serial Number xxxxxxxxxxx o esql -V The output will have something like this: INFORMIX-ESQL Version 7.24.UC5 Software Serial Number xxxxxxxxxxx Starting and stopping the Informix DBMS 23 When using the Informix Client SDK 2.2, which replaced the old ESQL/C, then the output will look like this: INFORMIX-ESQL Version 9.20.UC2 Software Serial Number xxxxxxxxxxx It seems that the new Client SDK 2.20 corresponds to the old ESQL/C 9.20. 24 CMVC FAQ: Informix 5.0 CREATING A DATABASE FOR A CMVC FAMILY This chapter describes how to create a CMVC family under Informix. This is an overview on how to create a CMVC family that uses an Informix database. For more information see the manual CMVC Server Administration and Installation, 2.3. 5.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 as the informix user id. In this example, the family name is "cmpc3inf" and it belongs to the group "informix". 3. Add the new CMVC family user id in /etc/hosts. 4. Add the new CMVC family user id in /etc/services. 5. Add the following entries for the CMVC family into /etc/hosts.equiv: oem-ppc3.raleigh.ibm.com cmpc3inf oem-ppc3 cmpc3inf 6. Logout as root. 5.2 ACTIVITIES TO BE PERFORMED BY THE INFORMIX USER ID. 1. Login as the Informix user id. 2. Add to $INFORMIXDIR/.rhosts the same changes made to the /etc/hosts.equiv file: oem-ppc3.raleigh.ibm.com cmpc3inf oem-ppc3 cmpc3inf 3. When creating a regular database space for the CMVC family, do not use the root dbspace for the Informix user id, instead, do the following: a. Create a directory where to store the regular dbspace, such as: Creating a database for a CMVC family 25 mkdir /home2/informix/cmvc-space You have to choose a place where you have enough file system space for the database. b. Create file where to store the regular dbspace, such as: touch /home2/informix/cmvc-space/cmpc3inf c. You may need to change the file permissions for the file created in the previous step in order for the next step to be successful. chmod 777 /home2/informix/cmvc-space/cmpc3inf d. Use the onspaces utility to create the regular dbspace. In the example below the dbspace is named "cmpc3inf" and with a size of 2.5MB (parameter: -s 5000) and offset of 0 (parameter: -o 0). onspaces -c -d cmpc3inf -p /home2/informix/cmvc-space/cmpc3inf -s 5000 -o 0 The value for the parameter "-d" needs to be used with the INFORMIX_DBSP environment variable in the .profile of the CMVC family. See 5.3, "Activities to be performed by the CMVC family user id.." You may need to follow the advice for the warning message from Informix: ** WARNING ** A level 0 archive of Root DBSpace will need to be done. e. Verify that the new regular dbspace is active by using: onstat -d 4. Logout as Informix user id. 5.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.informix .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 Informix. 26 CMVC FAQ: Informix 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 Informix are defined. Some of these variables may not be part of the original profile: export INFORMIXDIR=/usr/informix export INFORMIXSERVER=oem_ppc3 export INFORMIX_DBSP=cmpc3inf export INFORMIXTERM=terminfo export SQLEXEC=$INFORMIXDIR/lib/sqlrm export TERMCAP=/usr/informix/etc/termcap export TERM=vt100 The value for the INFORMIX_DBSP environment variable is the value used with the parameter "-d" when the Informix user id used the the command "onspaces" to create a regular dbspace. See 5.2, "Activities to be performed by the Informix user id." on page 25. You may need to use the proper TERM value for your situation. 5. After editing the profile, exit your session and login again in order to have a clean environment. 6. Create the CMVC family. At this time, all the environment variables and the configuration items should be ready for creating the CMVC family: a. mkfamily b. mkdb -d c. cmvcd cmpc3inf 2 d. You can now use the CMVC family. e. To monitor the usage of the CMVC daemons do: monitor 1 Press an alphanumerical key to stop the monitor command. f. To stop the CMVC daemons do: stopCMVC cmpc3inf Creating a database for a CMVC family 27 28 CMVC FAQ: Informix 6.0 BACKUP AND RESTORE This chapter describes how to perform a backup of the database and how to restore it. There are several ways of performing a backup/restore in Informix. In this chapter describes only the method that uses dbexport and dbimport. 6.1 BACKUP OF INFORMIX DATABASES 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: $ mkdir $HOME/backup $ chmod 777 $HOME/backup 4. Use the DBEXPORT command to export the database for the CMVC family into a directory named $HOME/backup/$LOGNAME.exp: $INFORMIXDIR/bin/dbexport -o $HOME/backup $LOGNAME 5. Wait for the following message that indicates that the backup was completed: dbexport completed 6. The text that is shown in the screen while the backup was being performed is stored in $HOME/backup/dbexport.out 7. The backup via dbexport consists of a directory "$HOME/backup/$LOGNAME.exp" that has many ASCII files, one that has a file extension of "sql" and many files that have a file extension of "unl" for each table in the database. A complete list is shown below: acces00100.unl cmpc3inf.sql files00113.unl level00105.unl tests00125.unl appro00101.unl compm00107.unl files00114.unl notes00119.unl track00126.unl appro00102.unl compo00108.unl fix__00115.unl notif00120.unl users00127.unl autho00103.unl confi00109.unl histo00116.unl path_00121.unl verif00129.unl cfgco00130.unl coreq00110.unl hosts00117.unl relea00122.unl versi00128.unl cfgre00131.unl defec00111.unl inter00118.unl seque00123.unl chang00106.unl envir00112.unl level00104.unl sizes00124.unl Backup and restore 29 6.2 RESTORE OF INFORMIX DATABASES Use the DBIMPORT command to import the exported data for the CMVC family. 1. If you are restoring a database into an existing one, then it is necessary to drop/delete the database: $ rmdb If the database exists, then the following error will occur when trying to restore the database from the backup file: *** create database 330 - Cannot create or rename database. 100 - ISAM error: duplicate value for a record with unique key. 2. If INFORMIX_DBSP is not set, run the following: $INFORMIXDIR/bin/dbimport -i $HOME/backup -l -ansi $LOGNAME 3. If INFORMIX_DBSP is set, run the following (in one single line): $INFORMIXDIR/bin/dbimport -i $HOME/backup -l -ansi -d $INFORMIX_DBSP \ $LOGNAME 4. Wait for the restore to be completed: dbimport completed 5. The above commands will create a database with the name of the CMVC family with log (-l) and mode ansi (-ansi). The data is loaded into INFORMIX_DBSP or into the root dbspace if INFORMIX_DBSP was not specified. 30 CMVC FAQ: Informix 7.0 ACCESSING THE INFORMIX DATABASE TABLES This chapter provides procedures for accessing the database tables and views used by CMVC. 7.1 HOW TO USE INTERACTIVE ISQL TO GAIN ACCESS TO THE DATABASE Sometimes it is necessary to access the Informix database used by the CMVC family in order to query the entries for a certain table or view. To gain access to the Informix database, do the following: 1. Login to an account that has access to Informix, such as the CMVC family user id or the Informix user id. 2. Enter: isql familyName 3. To look at all the entries for a table (such as Users), do: a. Select Query-Language. b. Select New. c. Specity the query (for example, to show all users, sorted by login): select * from Users where login like '%' order by login d. Press ESC to exit the edit mode. e. Select Run. You will see the output of the query. f. Select Next or Restart to navigate thru the results. g. Select Exit several times until you get out of isql. 7.1.1 Need to setup the proper TERM variable to use isql or ____________________________________________________________ dbaccess ________ If you enter the command "isql" and if the following error message is displayed: Accessing the Informix database tables 31 The type of your terminal is unknown to the system Then you need to setup the TERM environment variable to something that Informix will understand. It is recommended that you modify the profile to include the environment variables mentioned below. There are 2 methods: o Using terminfo: export INFORMIXTERM=terminfo export TERM=vt100 o Using termcap: INFORMIXTERM=termcap export INFORMIXTERM TERMCAP=/usr/informix/etc/termcap export TERMCAP TERM=vt100 export TERM 7.2 HOW TO USE A SCRIPT TO EXECUTE SQL COMMANDS The Korn shell script shown in 7.2.1, "Korn shell script: informixScript" on page 33 can be used to interact with the Informix dbaccess 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 needs to terminate with a semicolon, to allow multiple SQL comands to be issued in the same script: dbaccess $1 >> $LOG 2>&1 <> $LOG 2>&1 <