© 2002 International Business Machines Corporation. All rights reserved.

The following article was previously published in the Informix Tech Notes quarterly journal. The Tech Notes archive covers the years 1991 to 2001. To get a reprint of an article from 1998 or before, please contact Technical Support at tsmail@us.ibm.com

Introduction

Editor's Note: This article is an excerpt from Sari Nathans' book, SAP R/3 for the Informix DBA (Prentice Hall PTR; ISBN: 0130225541; November 26, 1999) .

The key to supporting and maintaining any SAP R/3 environment is to be proactive in the maintenance and support of the system. Preventing potential problems in the R/3 Informix® Dynamic Server® (IDS) database environment is as important as fixing problems. Preventive administration is achieved by constantly managing and monitoring certain aspects of the database system for details such as optimal response times for database access and update activities, database growth, and database integrity. The following Tasks and ctivities section shows various functions that need to be performed at specific times by the Informix database administrator.

This article will discuss the following:

• Tasks and activities
• Tools
• Client copies
• Database copies
• Database growth

Back to top

Tasks and activities

The tasks and activities described in this section are the activities the Informix database administrator needs to perform in order to manage and maintain an optimal R/3 Informix database environment and the required administration tasks needed to support the R/3 environment. These do not include other critical items that the R/3 administrator performs in their normal routine to administer the R/3 system, such as checking that the R/3 system is up and accessible and performing optimally.

Performing these tasks are recommended for supporting a production system. You may decide that some of the system tasks are not required in all system environments, such as a sandbox, or that the frequency indicated is not appropriate for all the systems in your landscape. Some decisions, such as the topics listed under Initial Database Setup and Maintenance Planning, need to be part of your system architecture and design. These are general recommendations that should be implemented in the environments that are appropriate for your site. Remember that a development environment is your production environment if it is the only environment being used. It can be critical to the timeline of the project if a development environment becomes unavailable to users during the development phase.

Initial database setup and maintenance planning

Your answers to the questions in Table 1 below can affect how you set up the Informix environment after the initial installation of the R/3 application. Most of these questions deal with the logical log setup, archive and backup requirements, and database growth. It is important to ask management and the functional teams these questions AND TO GET ANSWERS. The answers will vary by the SAP System ID (SID). You should work with the R/3 administrator and the UNIX administrator at your site to develop a cohesive strategy for some of these operational issues.

Daily Checks and Tasks

• Check that the database is on line and available.
• If R/3 is available then, normally, the R/3 Informix database is on line and available.
• Execute the onstat - command to verify that the Informix database is on line, and execute the onstat -u command to verify that the user sapr3 is connected to the database.
• Use SAPDBA to verify that the user sapr3 is connected.
• Check that all the dbspaces are on line, as appropriate, using the onstat -d command. All dbspaces being on line and available is also dependent on the $ONCONFIG ONDBSPACEDOWN parameter setting.
• If the ONDBSPACEDOWN is set to a value other than 1 (abort), there may be dbspaces that are marked as down when this system is on line.
• Perform consistency checks of the Informix catalog and reserve tables using oncheck -cc and oncheck -cr.
• Check the status of any nightly scheduled R/3 background or UNIX cron jobs using the R/3 transactions DB13 or SM37, or UNIX commands.
• Verify that the logical logs are backed up and freed sufficiently.
• Use the R/3 transactions DB12, DB13 or SM37 if you are using onarchive.
• Execute onstat -l to verify that the logical logs are being freed sufficiently and onstat -m to verify that the logical logs are being backed up successfully.
• If using a third-party storage management tool, check the reports from that tool.
• Perform a database archive and verify the database archive has completed successfully.
• Execute the onstat -m command to verify that an Archive has completed successfully.
• If using a third-party storage management tool, check the reports from that tool.
• Check the online message log for problem messages using the onstat -m command or the R/3 transaction ST04.
• Check for mail messages in Informix UNIX mail.
• Check for Informix-related messages in /var/adm/messages file (the filename may vary by operating system).
• Monitor that the Informix system is performing optimally using the R/3 transaction ST04.
• Monitor R/3 queries, as needed, using R/3 transactions ST04 and ST05.
• Review the SAP R/3 database (call and response) timings using R/3 transactions ST03 and ST04.
• Execute UPDATE STATISTICS for a specific deviation level or for specific tables using SAPDBA or R/3 transactions DB13, DB20.
• Use the R/3 transaction TU01 to determine the tables that have the highest update activity.
• Be as proactive as possible in investigating on line bottlenecks and possible issues.

Weekly Checks and Tasks

• Perform weekly database archives and logical log backups. These can be scheduled using R/3 transaction DB13 if your backup and archive method is onarchive.
• Verify that the weekly database archive and the logical log backups have completed successfully.
• Use R/3 transactions DB12, DB13 or SM37 if you are using onarchive.
• Execute onstat -l to verify that the logical logs are being freed sufficiently and onstat -m to verify that an archive has completed successfully and that the logical logs are being backed up successfully.
• If using a third party storage management tool, check the reports from that tool.
• Check that all the dbspaces are less than 80% full (or your selected critical point level) using SAPDBA or the R/3 transaction DB02.
• Monitor the database growth using the R/3 transaction DB02 or SAPDBA.
• Release unused memory using the onmode -F command.
• Perform database consistency checks using SAPDBA or the Informix oncheck utility.
• Send archive and backup media to offsite storage as appropriate to your site storage requirements.
• Check and remove Informix dumps, if not needed by Informix technical support, from the Informix dump directory.
• If using onarchive, remove /tmp/ARC* and /tmp/oncat* files.

Monthly or Quarterly Checks and Tasks

• Recycle tape backup media to proper tape retention cycles.
• Cleanup the $INFORMIXDIR message and console files.
• Schedule table reorganizations using SAPDBA, as necessary.
• Execute UPDATE STATISTICS (force) for the entire database, if applicable, using SAPDBA or the R/3 transaction DB13.
• Perform database system checks using SAPDBA, the R/3 transactions DB13 or DB16, or the R/3 line command infcfgcheck.

Prior to Heavy Load Activity and Client Transports

• Investigate if changing logical logs to /dev/null is an option.
• Increase the number of logical logs if necessary (even if they are set to /dev/null).
• Increase the number of LOCKS in $ONCONFIG.
• Perform a level 0 archive.

After Heavy Load Activity, Client Transports, and Client Deletes

• Execute UPDATE STATISTICS (force) for all the updated tables.
• Check that all the dbspaces are on line (see note under Daily Checks and Tasks regarding dbspaces being on line).
• Check that all the dbspaces are less than 80% full (or your selected critical point level).
• Reduce the number of LOCKS in $ONCONFIG (if increased).
• Change the LTAPEDEV parameter value back to the original setting if it was changed prior to the activity.
• Perform a level 0 archive.

As-Required Activities

• Perform an Informix database software upgrade.
• Perform a database copy.
• Perform a database restore.

Back to top

Tools

Many of the activities indicated above can be accomplished in a multiple of different ways and with different tools: SAPDBA, R/3 Computing Center Management System (CCMS), and Informix utilities. There is functionality overlap between the different methods and they all complement each other. The question is which tool should be used for which function? The rule of thumb is to use R/3 for monitoring and SAPDBA and the Informix tools for modifying database parameters and the database. But, as always, the best tool to use is the tool that you are most comfortable using. Table 2 details which tools can be utilized for various activities.

The R/3 transactions offer the consistency and familiarity of the R/3 interface and are sufficient for monitoring the Informix database environment when the R/3 system is available. SAPDBA and the Informix tools can be used for routine and non-routine database administrative tasks and can be used when R/3 is unavailable.

Back to top

Client copies

There are times when you need to create a client that looks just like another client in the same SID or in a different SID. Performing this task is accomplished using the client copy transactions in R/3. The R/3 administrator will most likely perform the actual client copy transactions and procedures. The database administrator needs to be aware of some items that will affect the database and the environments.

1. The system from which you are copying is called the source client and the system to which you are copying is called the target system. When you copy one client to another within the same SID, the source and target systems are the same. This is shown in Figure 1.
   Figure 1: Client Copy within the Same SID
   
   
2. If you are copying a production client to another client in a different SID there will be production data in a non-production SID. This may cause security issues that should be addressed.
3. Sufficient space should be allocated in the dbspaces in the target system to complete the client copy. If you are just copying configuration data, the space requirements are much less than if you are copying master and transaction data. Generally, for configuration data only, approximately 300 megabytes of free space is required throughout the dbspaces. Master and transaction data always go together, as one cannot be copied without the other. Regarding copying master and transaction data, the larger the client from which you are copying, the more space that will be required in the target system. Copying master and transaction data space requirements will depend on the amount of data in the source client. Master and transaction data normally will increase the size of psapbtab and psapstab dbspaces. To estimate the amount of space required for a specific client copy, a test client copy can be performed prior to the actual client copy.
4. Client-independent objects such as ABAP/4 programs, tables, and structures will not be copied during a client copy.
5. Both the target and the source system should not be used during the client copy (to prevent database inconsistencies and locks). Larger client copies can consume large amounts of processing time and may impact the use of the source and target systems for a greater length of time.
6. If the client that is being created already existed in the SID and is being overlaid, you will need to increase the number of LOCKS in the $ONCONFIG file and, if possible, set the LTAPEDEV parameter value to /dev/null so as not to run out of logical logs. Or, you can increase the number of logical logs if the LTAPEDEV parameter cannot be set to /dev/null. In essence, re-creating a client is first deleting all the data for the client and then inserting all the data for the client. You may want to suggest to the R/3 administrator using R3TRANS to delete the client first to cut down the time of the actual client copy.
7. Review the most current OSS notes regarding client copies.
8. Additional information about client copies can be found in the SAP help documentation.

Back to top

Database copies

There are times when a client copy is not sufficient to copy data from one SID to another. Sometimes you need a copy of all of the client-independent and client-dependent objects, or you need to copy multiple clients, or you need a new test or training system. The easiest way to do this is by copying the entire Informix R/3 database. Using the database copy procedure, the target system database is totally replaced with a copy of the source system database. When the copy is complete the target system database name will be the same as the source system database name. You should not create a production system using a database copy; use the proper transport methods for creating production environments.

There are several things to consider when planning a database copy to populate an R/3 database:

• Where are you copying the database?
• Are you overlaying another SID database?
• Do you have a backup of the original SID database so you can restore the database, if needed?
• Do you have hardware and resources to create a new R/3 SID and database?
• The database disk requirements from the source system must be the same on the target system.
• Is R/3 already installed on the target system? If not, it should be installed first.
• Is this a one-time occurrence or does the possibility exist that this will happen often?
• If you are copying the production system, will there be security issues due to the type of data in the system?
• Will the new system be part of the R/3 landscape and need to be incorporated in the transport process?
• If you are changing the hardware platform or migrating from another database to Informix, hence a heterogeneous system copy, you will need to request a migration key from SAP for certain R/3 releases.
• The Informix database version must be the same for the source and target database systems in the homogeneous database copy.
• A new SAP license will be needed for the target SID.

There are different ways to copy an R/3 Informix database. You can use SAP tools or Informix tools. Two methods are noted below:

1. Homogeneous database copy using SAP R/3 tools. This is the only method supported by SAP.
2. Informix database archive of the source SID database, and Informix database restore to the target SID database.

The procedures described here should be sufficient to complete the database copy. In addition, there are various Informix utilities to copy the data from one system to another that are not discussed here.

For All Methods

1. Determine the disk space requirements for the target database system from the source database system. Obtain a copy of the SAPDBA dbspace information or a copy of the onstat -d from the source database system. Create the same dbspace and chunk information on the target system as that of the source system.
2. An R/3 installation needs to be completed on the target system. Although you do not need to install the database delivered with the R/3 installation kit, it is recommended that you do. Complete the target system installation, including the database build, and test the target system. Verify that the target system works properly prior to overlaying the database.
3. Run UPDATE STATISTICS and data consistency checks on the source Informix R/3 database. Copying inconsistent or erroneous data will only give you two copies of inconsistent and erroneous data.
4. Review the current OSS notes regarding database copies, homogeneous system copies, or heterogeneous systems copies (e.g., 89698, 89188).

SAP Database Copy

The SAP database copy uses the R3INST utility and is similar to a standard R/3 installation. When performing an R/3 installation you can choose the Load SAP-Data from existing SAP R/3-System option. This will prevent the SAP delivered database on the CD-ROMs included in the installation kit from being built. To stress the point, it is recommended to install the database from the installation CDs so that you can verify that the R/3 system is properly installed.

The steps below make up the SAP database copy process:

• Export the database source and create the export control files. Creating the control files and exporting the database is performed using R3INST for R/3 releases prior to 4.x and R3SETUP for R/3 releases 4.x and after.
• Transfer the database export and control files from the source SID system to the target SID system. This can be done using file transfer protocol (FTP), copying the files from one machine to another, or by sharing the files across the machines using network file services (NFS). It is usually better to copy the files using FTP rather than using the network to access the files.
• Load the exported source SID database into the target SID. The database import is performed using R3INST for R/3 releases prior to 4.x and R3SETUP for R/3 releases 4.x and after.
• Create an archive of the new database.
• Start R/3 and perform an installation and consistency check using the R/3 transaction SICK. The R/3 administrator should review the General Subsequent Actions section in the R/3 Homogeneous System Copy manual for the R/3 release you are working with and perform any necessary tasks and reconfigurations.

Please refer to the R/3 Homogeneous System Copy manual for your R/3 release for step-by-step details of the first three items detailed above.

Informix Database Restore Method

When working with very large databases, the database restore method is one of the fastest ways to copy one R/3 system to another R/3 system. Using this method means the target R/3 SID and database name will be the same as the source system name. This also means that the source system and the target system must be two separate physical machines. The target system database will be exactly the same size as the source system database and the dbspace and chunk information must match exactly between the two systems. The following are the steps for the Informix database restore copying method:

1. Create a level 0 archive of the source system Informix database.
2. Create the dbspaces and chunks on the target system with the target system SID name.
3. If the target SID name does not match the source SID name, create a symbolic link in the /informix directory for the target SID to match the source SID.

   su - informix
   cd /informix
   ln -s targetSID sourceSID

4. Install the target R/3 system using R3INST if it is not already installed.
5. Alter the $ONCONFIG and sqlhosts files as necessary.
6. Restore the level 0 archive to the target system.
7. If you do not want the target SID to have the same name as the source SID you can rename the database.

   dbaccess-

   • rename database source SID to target SID ;
   • ctrlC

   Renaming the database will not change the internal Informix reserve pages that contain the original source SID path names. To change the internal Informix reserve pages, Informix technical support needs to be involved (using the Informix technical support tbchksap utility). You cannot delete the symbolic link in the /informix directory unless the internal reserve pages match the SID name. You will also not be able to use SAPDBA to perform dbspace extensions. The Informix utility, onspaces, or onmonitor, must be used to create, add, and drop chunks and dbspaces.

8. Run UPDATE STATISTICS and data consistency checks of the new database (if this had not been recently done on the source database).
9. Create an archive of the new database.
10. Start R/3 and perform an installation and consistency check using the R/3 transactions SICK and DB02 --> Check. The R/3 administrator should review the General Subsequent Actions section in the R/3 Homogeneous System Copy manual for the R/3 release you are working with and perform any necessary tasks and reconfigurations.

Back to top

Database growth

Database layout, size and growth have a large effect on the overall performance of the database. Though you are concerned with database layout, size and growth in all your R/3 environments, usually your production environment is the most important environment for these factors. Sometimes a sandbox or development R/3 environment is installed with the installation default values for dbspace sizes just to get the environment up, running, and available. This should never be the case for a production R/3 environment.

One of the objectives in laying out the database dbspaces across the available disks and controllers is to provide adequate space for even growth of the dbspaces across the disks and controllers so as not to introduce I/O bottlenecks or disk hot spots. Database growth should be regularly monitored using the R/3 transaction DB02 or SAPDBA. Examine the dbspace and table history for fast growing tables and be as proactive as possible in allocating additional chunks, implementing table fragmentation, or detaching indexes, as required. Monitor dbspace usage for I/O bottlenecks and move dbspaces to other disks if values are skewed or if bottlenecks are found.

Back to top

Summary

Many of the administration and housekeeping functions related to an R/3 environment are not very different from the same tasks required in other Informix application environments. The difference is the size of the R/3 application itself and the manner in which administration and monitoring are performed in an R/3 environment.

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Tasks and activities
• Tools
• Client copies
• Database copies
• Database growth
• Summary
• About the author
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers