Ingres 0212

MK
MK in the Ingres Environment

Version8.40212
Copyright © 2003 by SSA Global Technologies, Inc.
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any
way or by any means, including, without limitation, photocopying or recording, without the prior written consent of
SSA Global Technologies, Inc.
Important Notices
The material contained in this publication (including any supplementary information) constitutes and contains
confidential and proprietary information of SSA Global Technologies, Inc.
By gaining access to the attached, you acknowledge and agree that the material (including any modification,
translation or adaptation of the material) and all copyright, trade secrets and all other right, title and interest therein,
are the sole property of SSA Global Technologies, Inc. and that you shall not gain right, title or interest in the material
(including any modification, translation or adaptation of the material) by virtue of your review thereof other than the
non-exclusive right to use the material solely in connection with and the furtherance of your license and use of
software made available to your company from SSA Global Technologies, Inc. pursuant to a separate agreement
(“Purpose”).
In addition, by accessing the enclosed material, you acknowledge and agree that you are required to maintain such
material in strict confidence and that your use of such material is limited to the Purpose described above.
Although SSA Global Technologies, Inc. has taken due care to ensure that the material included in this publication is
accurate and complete, SSA Global Technologies, Inc. cannot warrant that the information contained in this
publication is complete, does not contain typographical or other errors, or will meet your specific requirements. As
such, SSA Global Technologies, Inc. does not assume and hereby disclaims all liability, consequential or otherwise,
for any loss or damage to any person or entity which is caused by or relates to errors or omissions in this publication
(including any supplementary information), whether such errors or omissions result from negligence, accident or any
other cause.
Trademark Acknowledgements
SSA GT and SSA Global Technologies are trademarks of SSA Global Technologies, Inc. in the United States and/or
other countries.
Microsoft, Windows, Windows NT and the Windows logo are registered trademarks of Microsoft Corporation in the
United States and/or other countries.
UNIX is a registered trademark of The Open Group in the United States and/or other countries.
Oracle is a registered trademark of the Oracle Corporation in the United States and/or other countries.
All other company, product, trade or service names referenced may be registered trademarks or trademarks of their
respective owners.
Contents
Chapter 1: Introduction
In This Guide................................................................................ 1–1
Intended Audience .......................................................................... 1–2
How This Guide Is Organized ................................................................ 1–3
Additional References ........................................................................ 1–4
Chapter 2: Ingres Database Driver Overview

Introduction ................................................................................. 1–1
How the Driver Works ....................................................................... 1–2
Key Files .................................................................................... 1–3
Chapter 3: Configuring Ingres for MK

Introduction ................................................................................. 3–1
Disk Requirements ........................................................................... 3–2
Configuring Ingres for MK in a UNIX Environment ............................................. 3–3
Configuring Ingres for MK in a Windows NT Environment ..................................... 3–12
Chapter 4: Mapping MK to Ingres

Introduction ................................................................................. 4–1
Table Naming Convention .................................................................... 4–2
MK and Ingres Columns ..................................................................... 4–3
MK and Ingres Indexes ....................................................................... 4–4
Data Types .................................................................................. 4–9
Contents iii
Chapter 5: Using MK with Ingres
Introduction ................................................................................ 5–1
Defining the Database in MK ................................................................. 5–2
Linking Companies to Ingres ................................................................. 5–3
Optimizing Database Structure ............................................................... 5–5
Chapter 6: Maintaining Driver Files

Introduction ................................................................................ 6–1
Creating Databases .......................................................................... 6–2
User File.................................................................................... 6–3
Group File .................................................................................. 6–4
Storage File ................................................................................. 6–5
Maintenance Tools .......................................................................... 6–7
Chapter 7: Tuning the Ingres Database

Introduction ................................................................................ 7–1
Tuning the Database ......................................................................... 7–2
Maintaining the Database .................................................................... 7–3
Setting Up Database and Table Alternate Location Parameters................................... 7–4
Chapter 8: Troubleshooting
Introduction ................................................................................ 8–1
Error Mapping and Reporting ................................................................ 8–2
Timeout and Deadlocking Problems .......................................................... 8–6
Appendix A: Files and Executables

Executables ................................................................................. A–1
Driver Files ................................................................................. A–3
Table Definition (tabledef6.0) ................................................................. A–4
IPC File (ipc_info) ........................................................................... A–5
iv MK in the Ingres Environment

Appendix B: Table and Index Parameters
Table Parameters ............................................................................ B–2
List of Parameters............................................................................ B–3
Appendix C: Installation and Server Parameters

Ingres Logging System ....................................................................... C–1
Database Server Parameters .................................................................. C–2
Appendix D: Running MK Database Utilities in a Windows NT

Dual Server Environment
BDBPRE.EXE and BDBPOST.EXE Utilities .................................................... D–2
OPING_MODIFY.EXE Utility ................................................................ D–3
GENLIST_OPING.CMD Utility .............................................................. D–4
Appendix E: Row Level Locking

Setting Row Level Locking in Ingres ........................................................... E–2
Setting Row Level Locking in MK ............................................................. E–3
Contents v
Chapter
Introduction
1
In This Guide
This document provides information specific to the Ingres database driver for
MK (Manufacturing Knowledge). The Ingres database driver serves as the
connecting layer between the Ingres database and MK.
This guide describes installation and maintenance of the Ingres database driver,
and provides guidelines for using it in an Ingres environment and for optimizing
its performance.
This guide does not, however, provide information on Ingres database

administration or database management. For related information, refer to your
Ingres documentation.
Introduction 1–1
Intended Audience
Intended Audience
This guide is intended for system administrators and system programmers who
are responsible for installing and maintaining the Ingres database, the Ingres
database driver, and MK. Before using this guide, you should be familiar with
the following:
■ Operating system and common commands (or the POSIX shell if installing
on a POSIX-compliant system)
■ System administration tools
■ Ingres database and Ingres documentation
■ Structured Query Language (SQL)
■ MK concepts (such as users, companies, etc.)
■ Menu navigation skills
1–2 MK in the Ingres Environment

How This Guide Is Organized
How This Guide Is Organized

The information in this guide is organized as follows:
Chapter 1, Introduction, describes the purpose of this guide, what you should
know before getting started, and which additional documents you should
reference.
Chapter 2, Ingres Database Driver Overview, provides a brief overview of
Ingres database driver and how it is used.
Chapter 3, Preparing Ingres for MK, recommends how Ingres parameters
should be set, describes how to define the Ingres environment variables, and
explains how to install the Ingres database driver.
Chapter 4, Mapping MK to Ingres, describes the naming conventions and data
types that the MK Ingres driver controls as the translator between MK and
the Ingres database.
Chapter 5, Using MK with Ingres, describes how to add or modify specific MK
data to begin using the Ingres database.
Chapter 6, Maintaining Driver Files, describes files that allow users to access
MK and describes the tools available to maintain these files.
Chapter 7, Tuning, provides methods of maximizing the UNIX Ingres driver
performance.
Chapter 8, Troubleshooting, lists some of the errors that you may encounter in
the Ingres environment and describes some possible solutions to specific
problems.
Appendix A, Files and Executables, describes the various executables and
files required by the Ingres driver.
Appendix B, Table and Index Parameters, describes the parameters used for
creating UNIX tables and indexes.
Appendix C, Installation and Server Parameters, provides additional
information and guidelines about UNIX Ingres parameters.
Introduction 1–3
Additional References
Additional References
Related documentation is listed below:
Ingres Installation and Operation Guide
Getting Started with Ingres II
Ingres Database Administrator’s Guide
Hardware and operating system documentation

Chapter
Ingres Database Driver Overview

2
Introduction ................................................................................. 1–1

How the Driver Works ....................................................................... 1–2
Dynamic SQL............................................................................ 1–2
Fastpath API ............................................................................ 1–2
Key Files .................................................................................... 1–3
Ingres Database Driver Overview 2–i

Chapter
Ingres Database Driver Overview

2
Introduction
Using MK with Ingres involves a communication layer referred to as the
database driver. The database driver is designed to meet the specific needs of a
particular database management system, so the MK application can operate in
the same manner regardless of the database environment. To this end, the driver
allows MK to access data stored in an Ingres database.
The MK shell surrounds or insulates the MK application and is responsible for

communication with the operating system, MK user interface driver, and
database driver.
The relationship between Ingres and MK is depicted in the following diagram:
MK Shell
Database Driver
SQL/Fastpath I-Record
Ingres
Database
Ingres Database Driver Overview 1–1

How the Driver Works
How the Driver Works

The Ingres driver uses Embedded SQL (also known as ESQL) and Fastpath API
calls to communicate with the Ingres database. ESQL allows you to embed SQL
statements into the Ingres driver host language, which is C. You can mix ESQL
statements with Fastpath calls and other host language statements. An
important feature of ESQL is that it allows generation of SQL statements at
runtime. This is called Dynamic SQL.
Dynamic SQL
Dynamic SQL is used to perform database actions. When a user requests table
actions (for example, create table or drop table) the database driver generates an
SQL statement and executes the statement with any input parameters.
Fastpath API
Fastpath API calls are substituted for dynamic SQL whenever possible to reduce
the time and effort needed to satisfy a database request.
If execution is successful, the driver passes the results and requested data back to
MK by way of the indexed record (I-Record). If the operation fails, an error code
is generated and passed back to MK.

Key Files
Key Files
In addition to the files Ingres requires, an MK installation uses several key files
to manage access to the database. These files are shown in the diagram below
and described in the table that follows. The files are discussed in greater detail
in the “Maintaining Driver Files” chapter.
tabledef6.0
oping_users ipc_info
Ingres oping_groups MK
Database oping_storage
Not
Specific to the
Database
Ingres Driver
Specific
Key File Description

oping_users Contains information about all MK users who are going to
access the Ingres driver.
oping_groups Defines groups of MK users and contains all Ingres
database names accessible by MK.
oping_storage Describes database storage specifications for MK tables and
indexes.
In addition, MK uses the files below to link tables to a particular database driver
and to identify the communication protocol to be used between the shell and
driver:
Key File Description

tabledef6.0 Since MK can use multiple database systems, a table can exist in
any one of the systems, such as Informix, Ingres, or Oracle. The
tabledef6.0 file identifies the type of the driver to be used for
accessing a table, plus other driver parameters. (Refer to the
“Files and Executables” appendix.)
ipc_info This file defines the directory path name for various driver
executables and the protocols used for communication between
the MK shell and driver processes. (Refer to the “Files and
Executables” appendix.)
For additional information about these and other MK files, refer to the MK
System Administrator’s Guide.
Ingres Database Driver Overview 1–3

Chapter
Configuring Ingres for MK

3
Introduction ................................................................................. 3–1

Disk Requirements ........................................................................... 3–2
Configuring Ingres for MK in a UNIX Environment ............................................. 3–3
Installing Ingres ......................................................................... 3–3
Configuring the Ingres Database for MK ................................................... 3–5
Installing the Ingres Driver ............................................................... 3–9
Adding Users and Groups ............................................................... 3–11
Configuring Ingres for MK in a Windows NT Environment ..................................... 3–12
Setting Ingres Installation Parameters ..................................................... 3–12
Configuring Ingres Parameters for MK .................................................... 3–13
Creating an Ingres Database for MK ...................................................... 3–13
Verifying the Ingres Installation .......................................................... 3–14
Adding MK Users and Groups ........................................................... 3–15
Configuring Ingres for MK 3–i

Chapter
Configuring Ingres for MK

3
Introduction
This chapter contains an overview of how to configure Ingres for MK. The first
part of this chapter describes how to do this in the UNIX environment, and the
second part of this chapter describes how to do this in the Windows NT
environment.
Configuring Ingres for MK 3–1

Disk Requirements
Disk Requirements
Consider the following when using an Ingres database:
Requirement Approximate Disk Space

Empty and test databases 200 Mb (each)
Ingres DBMS 100 Mb
Ingres transaction log At least 500 Mb
Ingres checkpoints Same as database size
If you have multiple disks, you can reduce input/output bottlenecks by careful
software placement. The following graphic illustrates some possible
configurations.
Disk 1 Disk 2
MK Users
UNIX or NT Data
2 Disk
System Ingres
Ingres log
Checkpoints
Disk 1 Disk 2 Disk 3

MK Ingres Users
3 Disk
System UNIX or NT Ingres log Data
Checkpoints
Disk 1 Disk 2 Disk 3 Disk 4

UNIX or NT MK Ingres Data
4 Disk
System Users Ingres log
Checkpoints
Disk 1 Disk 2 Disk 3 Disk 4 Disk 5

UNIX or NT MK Ingres Data Checkpoints
5 Disk
System
Users Ingres log
Note: This diagram pertains only to non-RAID configurations.

Configuring Ingres for MK in a UNIX Environment

In general, installing MK in an Ingres environment involves the following steps:
1. Installing Ingres
2. Installing and initializing the Ingres database driver
3. Mapping MK data to Ingres
4. Creating MK companies and tables
The first two steps are described in this section. Steps 3 and 4 are discussed in
the following chapters and should be performed as part of your post-installation
process.
Before You Begin Before installing the Ingres database driver, the Ingres database must be
installed. If the Ingres database is not installed on your system, follow the
instructions below.
Installing Ingres
Install Ingres using the standard database installation instructions for your
system. Make a note of the setting of the UNIX environment variable
II_SYSTEM for later reference. Note that the release of Ingres you are installing
uses shared (dynamically linked) libraries. The shared library path environment
variable for your operating system must be defined in your environment before
attempting to use Ingres or the Ingres driver and utilities.
The shared library environment variable for the HP-UX system is SHLIB_PATH.
For AIX, the shared library environment variable is LIBPATH. For DEC, Sun,
and Linux, the shared library environment variable is LD_LIBRARY_PATH.
Using your previously defined II_SYSTEM environment variable, execute one of

the commands below at your operating system prompt and/or define it in your
UNIX shell startup script.
■ For the C shell:

setenv SHLIB_PATH /lib:$II_SYSTEM/ingres/lib:$SHLIB_PATH
■ For the Korn shell:

export SHLIB_PATH=/lib:$II_SYSTEM/ingres/lib:$SHLIB_PATH
These environment variables must be set for all MK users who need to access
Ingres through the MK driver. You can set the variables quickly in the tabledef
file for those users. Run the Maintain Database Definitions (ttaad4110m000)
session and specify the SHLIB_PATH variable in the parameter field as follows:
II_SYSTEM=<Ingrespath>,SHLIB_PATH=/lib:$II_SYSTEM/ingres/lib

Here is an example:
II_SYSTEM=/disk2/Ingres,SHLIB_PATH=lib:$II_SYSTEM/ingres/lib
Substitute the location of your Ingres system files for <ingrespath> in the
parameter line. For complete instructions on defining the Ingres database in
MK, refer to the “Using MK with Ingres” chapter.
Once you have entered the parameter field in the Maintain Database Definitions
(ttaad4110m000) session, save your changes and run the Convert to Runtime
Data Dictionary (ttaad4200m000) session to update the tabledef file. Exit MK.
The next time you start MK, Ingres will be accessible through the driver.
Note: Refer to the “Using MK with Ingres” chapter for information on how to
access the Maintain Database Definitions (ttaad4110m000) and Convert to
Runtime Data Dictionary (ttaad4200m000) sessions.
Setting Ingres Parameters
During the installation, you will need to set values for a number of parameters.
Use the table below to set the recommended values for Ingres installation
parameters. These parameters are organized according to their stage in the
installation. You can configure some of these values at install time or any of
them (except character set) after installation with the cbf utility.
Parameter Recommended Value

Character set ISO 88591 (default)
Log file size (Mb) At least 500 Mb
Number of concurrent sessions Three to five times the number of MK
users
ANSI SQL 92 Compliance No

Configuring the Ingres Database for MK
After you install Ingres, you need to configure it for MK. This section describes
what you need to do, including:
■ Configure installation
■ Create Ingres users
■ Create the Ingres database for MK
■ Verify the Ingres installation
■ Install the MK Ingres driver
■ Map MK users to Ingres databases
Configuring Ingres Parameters for MK

Before creating your Ingres database, run the cbf utility to modfify the default
values of some of the Ingres parameters. Use the table below to set the
recommended values for the parameters.

default_page_size 2048
system_isolation read_committed
system_readlock nolock
max_tuple_length 4096
DMF Cache 4K ON
System_Maxlocks 50
Cursor_limit 128
Per_tx_limit At least 1024

Creating Ingres Users and an Ingres Database for MK
For an initial installation, all users are mapped to a single database. The
database administrator (DBA) of this database and the name of the database
must be the same (for example, mk). (The DBA of all Ingres databases used by
MK must be a user with the same name as the database.) This section
summarizes the steps you need to take before installing the MK Ingres driver.
⇒To create the database:

1. Authorize users root, bsp, and mk to access the Ingres database. Create
these users before running the MK installation program (mkinstall6.0) or the
Ingres driver installation program (oping_inst6.0).
To create these users:
■ Log on as ingres.
■ Make sure that the Ingres server is running; if it is not, enter the
command:
ingstart
2. To access the Ingres administration program enter the command:

accessdb
The following form appears:
You are running the ACCESSDB program. In this program, you can control
access to INGRES, specify which databases may be accessed by which
Authorized Identifiers (users or public) and display summary information about
all users or databases. In addition, you can add Location Name to MK and extend
databases to other areas (disk volumes or directories). This Ingres installation
has been configures to use case insensitive delimited usernames. Your effective
INGRES username is ingres.
Databases : List all databases.

View/Modify Information about individual databases.
Locations : List all locations.
Create/Modify/Delete a location definition.
Users : List all users.

Create/Modify/Delete an INGRES user.
Help : Access the INGRES help facility.

Quit : Exit from ACCESSDB
Databases Locations Users Help Quit :

3. Select Users from the main menu.

All current users defined to Ingres are displayed. The following form
appears:
ACCESSDB - Users Catalog
User Name
$Ingres
bill
bap
frank
Ingress
bsp
bsp
mmx01
root
Create Edit Delete SQLscript Help End
4. Select Create to display the following form.

ACCESSSDB - User Information
User Name: bsp
Profile for User: _____________________________
Default Group: _____________________________
Expire Date: _____________________________
Permissions: Create Database: Y Operator: N

Security Administrator: Y Set Trace Flags: N
Maintain Locations: N Maintain Users: Y
Databases Owned Authorized Database
Save Help End Password Privileges ListChoice : _
5. Accept the default permissions for user mk.

6. Set the following permissions for user bsp:
Create Database: Y
Security Administrator: Y
Maintain Locations: N
Operator: N
Set Trace Flags: N
Maintain Users: Y
7. Select Save to save the settings, then End to exit.
After you have added the mandatory users root, bsp, and mk, continue
adding MK user names if they are known. They can have the same
permissions as user mk (default permissions).
For more information on using accessdb, refer to the “Authorizing User
Access” chapter in Ingres Database Administrator’s Guide.

8. Create the Ingres database mk (while still logged in as ingres) by entering:

createdb -umk mk
The createdb command shown above creates the database in a default

location where the iidbdb database was created. If you want to create a
database in any other location or want to relocate the checkpoint, journal,
and dump files, specify an alternate location for these files using the
following syntax:
createdb -d <location name> -c <location name>
-j <location name> -b <location name>
Refer to the “Using Alternate Locations” chapter in Ingres Database

Administrator’s Guide for more information about locations.
Verifying the Ingres Installation
The following steps verify that the Ingres database has been installed correctly:
1. Make sure you are logged in as ingres.
2. Enter the following command:
sql iidbdb
Successful execution of this statement verifies:

■ Ingres has been installed
■ The Ingres executables are in your path
■ Environment variable II_SYSTEM is set
■ The Ingres database server is running
3. Verify that the database mk exists and is owned by the user mk. At the
prompt (*), type:
select name,own from iidatabase order by name\g
Executing. . .
name own
————————————————————
iidb $ingres
mk mk
4. Verify that the users root, bsp, and mk exist as Ingres users. At the prompt
(*), type:
select name from iiuser order by name\g\q
Executing. . .
name
——————————
bsp
ingres
mk
root
(4 rows)

Installing the Ingres Driver
Once you have defined users to Ingres and created the Ingres database, you need
to install the Ingres database driver:
1. Use the su command to become the root user:
su root
2. Change to the $BSE/bin directory:

cd $BSE/bin
3. Change the owner and permissions on the oping_exec6.0 file as follows:

chown root oping_exec6.0
chmod 4750 oping_exec6.0
4. This must be done after installation because the last step of the installation
process changes the file ownership to bsp. Verify the file permissions you
have set. The permissions should be:
-rwsr-x--- 1 root bsp (size) (date) oping_exec6.0
5. Add the ingres user to the bsp group in the /etc/groups file (if you have not
already done so).
6. Exit the shell started by su.
exit
7. Log in as ingres.
Note: On some System V-based operating systems, you may need to use the
newgrp bsp command to change your primary group after logging in.
8. Run the oping_inst6.0 script as shown below to link MK to Ingres:
oping_inst6.0

The following is displayed:

I N S T A L L I N G R E S D R I V E R
This script will create the initial group and users
for installing the MK INGRES driver.
The following operations will be performed:
- Update files 'oping_users' and 'oping_groups' with
default values.
- Update file 'oping_storage' with default values.
- Default Values
Group Name : 'mk'
Users belonging to this group : 'root', 'bsp'
Creating Initial Group and Users
Initial Group in INGRES
Enter group name : mk
Default Configuration
The ‘group’ is the name of Initial INGRES group : 'mk'

the Ingres database and the Initial INGRES users : 'bsp', 'root'
owner (DBA) of the Update 'oping_users' and 'oping_groups' with defaults (Y/N/E) : Y
database where your MK
tables are to be created. Updating files oping_groups and oping_users
Adding group 'mk'
No tables owned by mk
Group added.
Adding user 'root' to group 'mk' Done.
User added to group.
Adding user 'bsp' to group 'mk' Done.
User added to group.
Append defaults to the file 'oping_storage' (Y/N/E) : Y
It is recommended that you
Adding following default lines to 'oping_storage'
take the default storage
tdilc101:*:T:group:1:10:STRUCTURE btree LEAFFILL 80 PAGESIZE 4096
structures at this point. These tdilc101:*:I::1::STRUCTURE btree LEAFFILL 80 PAGESIZE 4096
will be the default values *:*:T:group:1:10:STRUCTURE btree nojournaling
used when creating MK *:*:I::1::STRUCTURE btree
tables in Ingres.
Please check if you agree with the defaults.
MK INGRES driver installation completed.
9. If you wish to modify these settings (for example, to enable Ingres

journaling), modify the $BSE/lib/openingres/oping_storage file now.
Review these settings before creating tables for your main companies. (Refer
to the “Table and Index Parameters” appendix.)

Adding Users and Groups
The next step is to add all of your users to the mmx group. To do this, use the
oping_admin6.0 administration tool.
1. Enter the following command:
oping_admin6.0
The following menu appears:
M A I N M E N U
Maintain MK Ingres Driver

1. Add user to group
2. Remove user from group
3. Add group
4. Remove group
5. Edit storage information
6. List users
7. List groups
8. List storage
9. Enter Shell
E Exit
Choice:
2. Choose option (1) to add MK users:

Enter user name : user1..........
Enter group name : mk............
Add all of the Ingres users that you created when setting up the database.
Specify the user name and then the group name you entered previously. Do
not attempt to add user mmx to the group.
For more information about the user, group, and storage files, refer to
the“Maintaining Driver Files” chapter.
Next Step
Refer to the MK Installation and Update Guide for information about how to
complete the installation and about the activities you should perform after
installing MK. Before creating tables, refer to the following “Mapping MK to
Ingres” and “Using MK with Ingres” chapters.

Configuring Ingres for MK in a Windows NT Environment

Preparing your Ingres environment for an installation of MK involves the
following steps:
■ Setting Ingres installation parameters
■ Configuring Ingres parameters for MK
■ Creating an Ingres database for MK
■ Verifying the Ingres installation
■ Adding MK users and groups
Setting Ingres Installation Parameters
Use the table below to set the recommended values for Ingres installation
parameters. These parameters are organized according to their stage in the
installation. You can configure some of these values at install time or any of
them (except Character Set) after installation with the cbf utility.

Character set IBM PC850
Log File size (Mb) At least 500 megabytes
Number of concurrent sessions Three times the number of MK users
ANSI SQL 92 Compliance No

Configuring Ingres Parameters for MK

Before creating your Ingres database, run the cbf utility to modfify the default
values of some of the Ingres parameters. Use the table below to set the
recommended values for the parameters.

default_page_size 2048
system_isolation read_committed
system_readlock nolock
max_tuple_length 4096
DMF Cache 4K ON
System_Maxlocks 50
Cursor_limit 128
Per_tx_limit At least 1024
Creating an Ingres Database for MK
Refer to Chapter 2, “Before Installating MK“ of the MK Installation and Upgrade

Guide for Windows NT for information on how to do this.

Verifying the Ingres Installation
The following steps verify that the Ingres database has been installed correctly:
1. Make sure you are logged in as ingres.
2. Enter:
sql iidbdb
Successful execution of this statement verifies the following:
■ Ingres has been installed
■ The Ingres executables are in your path
■ The environment variable II_SYSTEM is set
■ The Ingres database server is running
3. Verify that the mandatory users, mkadmin and mk, exist as Ingres users. At
the prompt (*), type:
select name from iiuser order by name\g\q
Executing. . .
name
——————————
mkadmin
ingres
mk
(3 rows)
4. If you wish to modify any settings, for instance to enable Ingres journaling,
you should modify the %BSE%\lib\openingres\oping_storage file. You
should review these settings before creating tables for your Ingres
companies.

Adding MK Users and Groups
The next step is to add all of your MK users to group mk. To do this use the
administration tool oping_maint directly from the command line. Use the syntax
shown for the following maintenance options:
■ Add user to group:

oping_maint -a <MK user> -G <group> [-B <%BSE% path>] –y <MK system name>
Adds the user to the %BSE%\lib\openingres\oping_users file and to the

group.
■ Add group:
oping_maint -A <group> [-B <%BSE% path>]
–y <MK system name>
Creates a group in Ingres, grants privileges on MK tables to the group, and

adds the group to the %BSE%\lib\openingres\oping_groups file.
Add all of the Ingres users that you created when setting up the database.
Specify the user name and then the group name entered above. Do not
attempt to add user mmx to the group.
For more information about the user, group, and storage files, see the
“Maintaining Driver Files” chapter.

Chapter
Mapping MK to Ingres
4
Introduction ................................................................................. 4–1

Table Naming Convention .................................................................... 4–2
MK and Ingres Columns ..................................................................... 4–3
Column Name Conventions............................................................... 4–3
Hash Column Name Convention .......................................................... 4–3
MK and Ingres Indexes ....................................................................... 4–4
Single or No Index Optimization .......................................................... 4–5
Double Index Optimization ............................................................... 4–6
Hash Column Size ....................................................................... 4–7
Index Ordering .......................................................................... 4–8
Index Uniqueness ........................................................................ 4–8
Field Numbering......................................................................... 4–8
Data Types .................................................................................. 4–9
Dates ................................................................................... 4–9
Strings (char) ........................................................................... 4–10
Mapping MK to Ingres 4–i

Chapter
Mapping MK to Ingres
4
Introduction
Before creating tables for MK, you should be familiar with certain MK
requirements. This chapter discusses the mapping between MK and Ingres for:
■ Tables
■ Indexes
■ Columns
■ Data types
MK stores table columns and index information in its data dictionary. The
Ingres driver uses this information for various database actions. However, the
Ingres driver cannot use this information directly because fields in MK use
certain characters that are invalid for Ingres and some data types (such as date)
are represented differently in MK and Ingres.
In order to store and/or write the MK data, the driver maps the information into
a format suitable for Ingres. All Ingres database actions use this mapped
information.
In MK, every table is identified by a two-character package code, a three-

character module code, and a three-digit table number. To be consistent, both
MK tables and index names in Ingres have these identifiers.
Mapping MK to Ingres 4–1

Table Naming Convention
Table Naming Convention

To comply with MK table naming conventions, Ingres table names use the
following naming format:
t<package code><Ingres data dictionary table name><3 digit company number>
For example, MK table name tiitm001 with company number 100 has the
corresponding Ingres table ttiitm001100. Note that the company number always
contains three digits, using zeros if necessary.
The Ingres driver adds the owner name to a private table name to differentiate
the private table from the group table in the same database.
The following table contains examples of UNIX MK table names in Ingres:
Ingres Table Name MK Table Name

ttiitm001100 table: tiitm001
company number: 100
sequence number: 001
module: itm
package code: ti
table type: t
group table
bobttiitm001100 table: tiitm001
company number: 100
sequence number: 001
module: itm
package code: ti
table type: t
private table created by user bob
The following table contains examples of Windows NT MK table names in

Ingres:
Table Name MK
ttiitm001100 table: tiitm001
company number: 100
package code: ti
group table
bobttiitm001100 table: tiitm001
company number: 100
package code: ti
private table created by user bob

MK and Ingres Columns
MK and Ingres Columns

Each MK data dictionary column has a corresponding Ingres column. If an MK
column has a depth greater than 1 (array), each subsequent depth is a separate
Ingres column.
If you do not SELECT the index optimization technique for an index and that
index is a duplicate, a pseudo column named t_sequence is created. This column
is added to the index to make the index unique.
Column Name Conventions
An Ingres column is named by preceding the data dictionary column name by

“t_”. For example, the table tiitm001 has a field named ‘item’. The
corresponding Ingres column name is ‘t_item’.
If the data dictionary field has a depth greater than 1, each depth is a separate
Ingres column. If the data dictionary type is BDB_ARRAY, each of the Ingres
columns is named using the corresponding depth. For example, if data
dictionary field ctod has depth 7, there are seven Ingres columns with names
t_ctod_1, t_ctod_2, ...t_ctod_7. If the data dictionary type is not BDB_ARRAY,
each Ingres column takes the corresponding data dictionary field name.
Ingres does not allow a period (.) as part of a column name. Therefore, if a data
dictionary field name contains a period, the period is replaced by the underscore
character (_) in the corresponding Ingres column name.
Hash Column Name Convention
A hash column is created when the table has index optimization. For more
information on index optimization, refer to the “Tuning” chapter.
The hash column name contains its corresponding index number. When single
column index optimization is used, there is a single hash column per index. The
column name is hash<index number> (for example, hash1, hash2). Thus, hash1 is
created for index 1, and hash2 for index 2.
When double index column optimization is used, two hash columns are created
per data dictionary index, one of which is in ascending order and the other in
descending order. For the ascending order column, the naming convention is
the same as the single index column optimization convention. For the
descending order column, the name has a “d” suffix. Thus, for index 1, hash
column names are hash1 and hash1d.

MK and Ingres Indexes

An Ingres index name can contain up to 32 characters. The driver creates the
index name using package code, table name, company number, index number,
index order, and table owner name, according to the following example:
Index name = package code, such as ti
+ tablename, such as itm001
+ company number, such as 100
+ index number, such as 2
+ character a if ascending index, or d if descending index
+ as many leading characters from table owner name as
possible
Note: If the table owner name length would cause the index name to exceed its
32 character maximum, the owner name is truncated.
The following example shows how an index name, tiitm0011002amk, is created

from these parameters:
■ Package code—ti
■ Tablename—itm001
■ Company number—100
■ Index number—2
■ Ascending order—a
■ Owner—mk

Single or No Index Optimization
With single or no index optimization, the index ordering can be ascending only.
Also, the primary key is part of the base table structure. If the primary key has
either single or no index optimization, only the ascending index is created. This
is part of the base table structure. All other indexes are named with the naming
convention described in the previous example, depending on their index
optimization level.
Using the previous example as a model, if the primary index has single or no
index optimization, index 2 has double index optimization, and index 3 has
single or no index optimization, then the indexes have the following names:
Index Name
Index 1 Ascending index not named as it is part of base table
structure.
There is no descending index.
Index 2 tiitm0011002ammx (ascending)
tiitm0011002dmmx (descending)

Double Index Optimization
If the primary index has double index optimization, the ascending index is part
of the base table structure. The descending index (created in ascending order on
descending hash columns) has the following name:
<package><table name><company number><i#><sort><owner>
Other indexes use the previously described naming convention, depending on

their index optimization level. For example, if the primary index has double
index optimization, index 2 has double index optimization, and index 3 has
single or no index optimization, the indexes have the following names:
Index Name
Index 1 Ascending index not named as it is part of base table
structure.

Hash Column Size
If an index is created with index column optimization, additional column(s) are

added to the table when you create it. The size of the hash column depends on
the types of columns participating in the index. The following table defines the
size of a hash column for various data types:
Field Type Hash Column Size

char 1
char(n) n
short 3
date 4
long 5
float (digv + diga + 2) / 2
double (digv + diga + 2) / 2
If an index consists of many fields, the size of the hash column equals the total of
the sizes required for each field. For example, if the index consists of a long field
size of 5 and a char field size of 1, the hash column size is 6. If the index has
single index optimization, there is one hash column. For indexes with double
index optimization, two such columns are created.

Index Ordering
In Ingres, the default order when creating an index is ascending. There is no

provision for a descending index. The descending hash column is a 1’s
complement of the ascending column with an ascending index, thereby
simulating a descending index.
Index Uniqueness
All indexes built by the Ingres driver are unique with the no duplicate clause if
the user does not select the index optimization option for the index. For all
duplicate indexes (according to the MK data dictionary), the driver internally
adds a system-maintained table key to all user-defined index columns and
makes the index unique. A system-maintained table key is a type of unique
number generated by Ingres.
If the user selects index optimization, the primary index parts are added to the
duplicate index; this makes the index unique.
Field Numbering
The field number of each field in an index is stored as part of the Ingres index
information. If a field having a depth greater than 1 is part of the index, Ingres
uses the following logic:
A field having a depth of n creates n columns in the Ingres table. For
example, if field 3 in the data dictionary has a depth of 3, it creates 3 fields in
the Ingres table—field 3, field 4, and field 5.
If this field is part of the index, the index description contains all
corresponding Ingres field numbers; in this example, 3, 4, and 5.
The index length includes the length of each of these fields.
When the index is created, the corresponding field name is used for each field
number.

Data Types
Data Types
The following table shows the mapping between MK and Ingres data types:
MK Ingres
BDB_CHAR integer1
BDB_ENUM integer1
BDB_SHORT smallint (integer2)
BDB_LONG integer4
BDB_MAIL integer4
BDB_TIME integer4
BDB_TEXT integer4
BDB_BITSET integer4
BDB_DATE date
BDB_FLOAT float4
BDB_DOUBLE float8
BDB_STRING char
Dates
In MK, the date data type is stored as the number of days from the year 0001.
Ingres stores a date as the number of days since January 1, 1582. Conversion
from the MK date to the Ingres date involves getting the day of the month, the
month, and the year from the MK date (using the MK function) and passing
these to an Ingres function that returns a corresponding Ingres date.
Conversion from Ingres to MK follows the reverse path. In MK, date 0 means
that the date is not specified. In this case, minimum values are used to create the
Ingres date:
day of month = 1,month = 1,year = 1582
Similarly, when the following is retrieved from Ingres, the MK date is set to zero:
day of month = 1,month = 1,year = 1582

Data Types
Strings (char)
The MK BDB_STRING data type is mapped to the Ingres char data type. When
storing data for type char, Ingres may strip trailing blanks to save disk space.
When retrieving the data, the Ingres driver replaces the blanks before passing
the data back to the user. You never see this truncation.

Chapter
Using MK with Ingres

5
Introduction ................................................................................. 5–1

Defining the Database in MK ................................................................. 5–2
Linking Companies to Ingres ................................................................. 5–3
Optimizing Database Structure................................................................ 5–5
Using MK with Ingres 5–i

Chapter
Using MK with Ingres

5
Introduction
You need to add or modify some MK data before you can create any Ingres
tables:
1. Add Ingres as a database in the Maintain Database Definitions
(ttaad4110m000) session.
2. Create at least one company with the Maintain Companies (ttaad1100m000)
session.
3. Link the company to the Ingres database with the Assign Tables to
Databases (ttaad4111m000) session.
4. Run the Convert to Runtime Data Dictionary (ttaad4200m00) session.
After you have completed these steps, you can post data to the Ingres database.
Note: Run the MK-Ingres binaries and the Ingres database at matching patch
levels. Check the binary patch level information for UNIX from the output after
using the “-v” option. Use properties/version to check the Windows NT binary
patch level.
Using MK with Ingres 5–1

Defining the Database in MK
Defining the Database in MK

After you have installed both Ingres and MK, you need to define the database
within MK.
⇒ To add Ingres as an MK database:

1. Run the Maintain Database Definitions (ttaad4110m000) session.
2. Identify the database.

3. In the Parameter field, specify the II_SYSTEM environment variable and its
value.
4. You can also specify the MMX_PREFETCH, ING_LOCKMODE, and other
environment variables that you want defined when the driver starts by
adding them to the parameter field. Use a comma (,) as a separator with no
spaces between the fields and commas.

Linking Companies to Ingres

⇒ To create new companies or convert existing companies to Ingres:
1. Run the Maintain Companies (ttaad1100m000) session.
Make sure that the company you want to create has been entered in the
company tables.
2. If Ingres has not been added already, add Ingres as a database in the
Maintain Database Definitions (ttaad4110m000) session. Refer to the
previous section, Defining the Database in MK.
3. Run the Assign Tables to Databases (ttaad4111m000) session to specify that
the database for the company is Ingres.

4. In either the Windows NT or UNIX environment, run the Convert to

Runtime Data Dictionary (ttaad4200m000) session.
You are now ready to post data to the Ingres database.

Optimizing Database Structure
Optimizing Database Structure

Once data has been posted to the database, you should perform some clean-up
activities to better organize the storage structure and optimize performance. To
do this, generate a list of Ingres tables, then use the sysmod command to modify
MK tables to optimize query processing.
Note: If you are running MK in a dual-server Windows NT environment, refer

to Appendix D for information.
⇒ To optimize database structure:

1. Login as mk administrator (you may need to define the ingres environment
variables).
5. If you have not created the table list, run the genlist utility:
For UNIX Only $BSE/etc/genlist_ing6.0 <company number> <database> >tablelist
For Windows NT Only %BSE%\etc\genlist_oping <company number> <database> >tablelist
The BSE variable represents the directory where the MK application

software is stored.
This command generates a list of MK tables that exist in the Ingres database.
Update this list whenever you create new tables for the company.
The table list is also useful when using some of the MK database
management tools, such as bdbpre and oping_modify (refer to the “Using
Database Tools” chapter in MK Administrator’s Guide for more information
about MK database utilities).
3. Run oping_modify utility with the following options:
For UNIX Only oping_modify6.0 -B -I<tablelist> -p<package combination> -C<company
number>
For Windows NT Only oping_modify -B -I<tablelist> -p<package combination>

-C<company number>
4. Run optimizedb to optimize this system db:

optimizedb -zk iidbdb
optimizedb –zk mk
5. Log in as ingres.
6. Run sysmod to modify the database:
sysmod iidbdb
sysmod mk

Chapter
Maintaining Driver Files

6
Introduction ................................................................................. 6–1

User Administration ..................................................................... 6–1
Creating Databases .......................................................................... 6–2
User File .................................................................................... 6–3
Group File .................................................................................. 6–4
Storage File ................................................................................. 6–5
Maintenance Tools ........................................................................... 6–7
MK Optimization Sessions (UNIX Only) ................................................... 6–7
UNIX Interactive Maintenance ............................................................ 6–8
Command Line Maintenance ............................................................. 6–11
Maintaining Driver Files 6–i

Chapter
Maintaining Driver Files

6
Introduction
Normally, all Ingres users share tables and must have specific privileges to
access these tables. There are three files used to assign these privileges to MK
driver users and to link MK driver users to Ingres databases:
■ oping_users (user file)
■ oping_groups (group file)
■ oping_storage (storage file)
These files reside in $BSE/lib/openingres and can be created and maintained

using MK optimization sessions. This chapter describes the content and
structure of these files and how to use the database administration tools to
maintain them.
User Administration
MK users are mapped to Ingres databases or ‘groups’. Each group can have
many users. When creating a user, the group to which the user belongs is also
specified. An MK group maps a user to an Ingres database. All objects in the
database are owned by the database administrator (DBA).
User administration has the following prerequisites:

■ For security reasons, only the Ingres database administrator (UNIX or NT
login ingres) can use the User Administration options.
■ User ingres should own the user and group file, and should be the only user
to have write permission.
Maintaining Driver Files 6–1

Creating Databases
Creating Databases
In Ingres, any properly authorized user can create a database. The user is the
owner and has DBA privileges. A user can create and own more than one
database. The objects can be accessed using dot notation, as in the following
examples:
mk.ttiitm001100
frank.ttccom001200
However, because the LOCK TABLE command does not support the dot
notation, private tables can be created with the user name included in the table
name (such as frankttccom001200). This avoids using the dot notation and
makes the names unique. Group tables are created the normal way, without
user names. Refer to the Table Names section in the “Mapping MK to Ingres”
chapter.

User File
User File
The user file contains information about all MK users who may access Ingres
tables. This information is stored in the $BSE/lib/openingres/oping_users file.
Each line in this file is made up of four fields, separated by a colon (:).
For example:
➀ MK user—name by which the user is known to the MK optimization. This

should be a valid UNIX or NT login name.
➁ Ingres user—user name that is accessing the database. This is the same as
the MK user name.
➂ Encrypted Password—user’s encrypted password. It is used by some
databases but not by Ingres. For Ingres, the encrypted password is
generated but ignored.
Since the Ingres driver does not use this password, you do not have to assign
one when creating a user with the maintenance facility. The utility assigns a
dummy password.
➃ Group Name—group to which the user belongs. This should be a valid
UNIX or NT login name and a valid Ingres database name. MK defines the
group in oping_groups and maps the user name to a group in oping_users.

Group File
Group File
The group file contains information about Ingres groups in MK optimization.
These groups specify the name of the database in which MK optimization finds
the Ingres tables. This information is stored in
$BSE/lib/openingres/oping_groups. Each line in this file is made up of two
fields, separated by a colon (:). For example:
mk:GP'8!a'o(V<>!IhY'5gapjx-YuXw5A_a
Group Name Group Password
Group Name. The group name should be a valid Ingres database name with
the DBA name the same as the database name.
Group Password. This is the group’s encrypted password. Currently, the

Ingres driver does not use this password, so you do not have to assign one when
creating a group with the maintenance facility. The utility assigns a dummy
password.

Storage File
Storage File
The storage file contains information about the storage structure of MK
optimization and Ingres tables, and specifies various object parameters. The
Ingres driver refers to this file when creating objects and executing queries. For
each table, you can specify whether it is a private or group table by using the
private and group keywords.
The $BSE/lib/openingres/oping_storage file holds the storage information.

Each line in this file consists of seven fields, each separated by a colon (:). The
format of the line is as follows (fields in square brackets [ ] are optional;
wildcards (*) can be used for table name and company):
[{<user>}]<table name>:<company number>:<entry/object type>:
<owner type>:<index optimization level>:<refresh time>:
[<storage>]
For example:
tiitm001:999:T:group:1:10:STRUCTURE btree nojournaling
*:*:T:group:1:10:STRUCTURE btree nojournaling
*:*:I::1::STRUCTURE btree
The fields are described in the following table:
Field Description
User Table information can be linked to a specific user by indicating the user’s login
name in braces ({}). Specifications then only apply to the defined user. A list of
users separated by commas (,) can also be specified. For example:
{frank,brad,will}.....
If a user is not specified, the information applies to all users.
Module/Table name Specifies the MK table. Available options are:
■ * (all tables in all modules)
■ All tables in a module, specified by the 5-letter package/module code (for
example, tfacp, tdsls)
■ Module code and table number (for example, tiitm005, tccom001)
You can specify several table names, separated by commas.
Company number Specifies the company number. Available options are:
■ * (all company numbers)
■ Specific company number (3 digits)
You can specify several company numbers, separated by commas.

Storage File
Field Description
Entry/Object type Indicates the object the line relates to:
■ Table and all indexes (*)
■ Table only (T)
■ All indexes of the table (I)
■ One or more indexes of the table (In, where n is the number of the index)
You can specify a list of indexes, separated by commas, for example, I1,I2.
Owner type Specifies whether the table belongs to a single person or to a group. The
following options are available:
■ Private (the table belongs to a single person)
■ Group (the table can be accessed by the entire group)
Owner type only relates to tables; when used for an index, it is ignored.
Index optimization Specifies the index optimization level:
1 = Single index optimization.
2 = Double index optimization.
The index optimization level changes row size and may add extra secondary
indexes requiring more disk space.
Note: Currently only single index optimization is supported.
Refresh time By setting a refresh time (in seconds) you determine the validity period of a
query’s result set. You can only have refresh time for tables; for indexes it is
ignored. The default value is 10.
You can change refresh time to control fetch optmization and caching.
Storage Specifies the table’s storage structure.
Refer to the “Table and Index Parameters” appendix for a complete list of the
storage types and parameters used to create tables in the UNIX environment.
Note: Only btree storage structure is supported.
Note: A default table and index entry (covering all user/tables/customer

numbers) must exist. The default table entry uses single index optimization,
btree structure, 10-second refresh time, and no journaling. The default entry for
table structure should be btree and is defined during Ingres driver installation.
Note: While editing the storage parameter file, make sure that all lines end with
a new line character.

Maintenance Tools
Maintenance Tools
You can maintain the user, group, and storage files with a special administration
tool. Depending on the user interface you want to use, you can access this tool in
several ways. To maintain the driver files, you can use any of the following:
− Interactive utility ( in the UNIX environment)
Enter the oping_admin6.0 command
− Command line utility
Enter the oping_maint6.0 command
− MK optimization sessions in package tt, module dba (refer to the next
section, MK Optimization Sessions)
Before maintaining users and groups, the corresponding group database should
be created.
Note: To use any of the UNIX administration utilities, you must be logged in as
user ingres (in the UNIX bsp group). Before adding groups or users with these
tools, they must be defined as valid UNIX and Ingres users. The user
administration options only manage table permissions.
MK Optimization Sessions (UNIX Only)
As part of its system management functionality, MK optimization provides

several sessions for maintaining database information. To access the
administration tools from within MK optimization, use the Maintain Ingres
Parameters (mttdba1000m000) menu.
From this menu, select the appropriate sessions for adding, removing, and
reporting user and group information and for editing and viewing the storage
file. For information on how to use these sessions, refer to the online help for
individual sessions and to the documentation for the implementation functions
Ingres Database Driver Management (coms8700) and Ingres Database
Administration (com8701).
MK optimization also provides sessions for performing some database

administration tasks, such as adding new Ingres users or authorizing access to
Ingres databases. To perform these database administration tasks, select
Maintain Ingres Parameters, Ingres Utilities, then select an appropriate session.
Note: To access a menu using the P function, you must add a sequence number
to the menu number to distinguish members of parallel menu sets. This number
displays on the top-right corner of the menu. To access the menu above, press P
from any menu and enter mttdba1000m0001. The last digit, the added sequence
number, represents menu one of two.

Maintenance Tools
UNIX Interactive Maintenance
The UNIX oping_admin6.0 executable is stored in $BSE/bin. Before using this

command, the Ingres database server should be up and running. If the Ingres
database is installed and operational, enter oping_admin6.0 at the operating
system prompt to access the interactive Ingres maintenance command. (You are
prompted for the BSE environment variable, if it is not already set.)
The following form appears:
M A I N M E N U
Ingres Driver Maintenance

---------------------------------
1. Add user to group
2. Remove user from group
3. Add group
4. Remove group
5. Edit storage information
6. List users
7. List groups
8. List storage
9. Enter Shell
E Exit
Choice: 5
From this menu, select the operation you want to perform. Descriptions of these
options follow.
Add User to Group This option adds the user to the oping_users file and grants the user access to all
group tables using the following Ingres command:
ALTER USER <user> WITH PROFILE=<profile name>
Enter the group and MK optimization user name (this is the same as the Ingres
user name). The following restrictions apply:
■ The group should exist in the group file oping_groups.
■ The user name cannot be an existing group name or the same as the DBA
name; the user name must be known to the Ingres DBMS (accessdb).
■ The Ingres user should have access to the group database.
■ The MK optimization user should not already exist in the user file.
If any of the above checks fail, then the operation fails with an appropriate error
message. Otherwise, the user is granted permissions on the group tables and
added to the user file.
For administrator-level privileges, the user can access help from Ingres utilities,
such as accessdb.

Maintenance Tools
Remove User from This option revokes all privileges to group tables for a user and automatically
Group deletes the user’s line in oping_users. It runs the following Ingres command:
ALTER USER <user> WITH NOPROFILE
The name of the user is the MK optimization user name; you cannot use
wildcards. If the MK optimization user does not exist in the user file, then the
operation fails and gives an appropriate error message. Otherwise, the user is
disassociated with the Ingres profile having privileges on the group tables, and
the user’s entry is deleted from the user file. After removing the user, the DBA
should revoke the user’s access permissions to Ingres.
Add Group This option adds the group to the oping_groups file. The following restrictions
apply:
■ No group or Ingres user should exist with the same name as the group
name.
■ The group must be a valid Ingres database name.
■ The database for the group must exist.
Remove Group This option deletes a group from the oping_groups file. The following
restrictions apply:
■ The group should exist in the group file.
■ A group can only be deleted if it does not contain any users. This option
only deletes a line from oping_groups; it does not delete the group’s tables
or the Ingres login name.
It is the responsibility of the Ingres administrator to remove the group database

and its Ingres login.
Edit Storage This option allows you to edit the storage file using the vi editor.
Information
List Users This option lists all the users in the oping_users file. For each user, this option
displays the MK optimization name, and the Ingres name and group.
List Groups This option lists all groups in the oping_groups file, including each group name
(database) and all of its users.
List Storage This option retrieves information for a table or index from the oping_storage file.
Enter the following values (no blanks or wildcards allowed):
■ User name (UNIX)
■ Table name
■ Company number

Maintenance Tools
■ Object desired (such as T, I, I1,I2...) as described in the table:
Object Description
T Table information, as well as information for all indexes on
those tables, is displayed.
I The information for all indexes on that table is displayed.
In The information for a specific index (such as I4) is displayed.
The system then displays the following:

■ Table owner (private/group)
■ Index optimization level (0, 1, or 2)
■ Refresh time (does not apply to indexes)
■ Other specifications used in the creation of the table or index
When displaying an index:

■ An object entry in a storage file is qualified by the owner name, the table
name, and the company number. If an exact entry is not found in the storage
file for an object, the default entries are used if they exist.
■ If index optimization is not specified in an index entry, the one in the
corresponding table entry is used as the default.
■ If default information is being shown (because there is no specific entry for
the object), then the heading of the information is shown as Default Index
Information.
■ If some indexes have a specific entry for a table, and there is also a default
entry, and if the object selected is T or I, then the default index specifications,
as well as the specific entries, are shown. For example, if the storage file
contains:
{bsp1}tiitm998:000:Il,I2,I3::1::STRUCTURE btree
*:*:T:group:1:5:STRUCTURE hash nojournaling
{bsp1}*:*:I::::
For user ‘bsp1,’ table ‘tiitm998,’ company number ‘000,’ and object ‘I,’ the default
index specifications are shown first, then the index specifications for I1, I2, and I3
are shown.
Enter Shell This option creates a child shell. When you log out of that shell (using exit or
Ctrl-D), you return to the main menu.
Exit Exits the maintenance utility and returns you to the UNIX shell.

Maintenance Tools
Command Line Maintenance
Use oping_maint6.0 to maintain the MK optimization Ingres files directly from

the command line. Use the specific syntax below for each maintenance option.
⇒ To add user to group:

oping_maint6.0 -a <MK optimization user> -G <group> [-B <$BSE path>]
Adds the specified user to $BSE/lib/openingres/oping_users and adds the user

to the group profile.
⇒ To remove user from group:

oping_maint6.0 -d <MK optimization user> -G <group> [-B <$BSE path>]
Disassociates a user from the group profile and automatically deletes the user’s
line in oping_users.
⇒ To add a group:
oping_maint6.0 -A <group> [-B <$BSE path>]
Creates a group and profile in Ingres, grants privileges on MK tables to the

profile, and adds the group to the $BSE/lib/openingres/oping_groups file.
⇒ To remove a group:
oping_maint6.0 -D <group> [-B <$BSE path>]
Revokes all privileges to group tables for the group and automatically deletes
the group’s entry in oping_groups.
⇒ To list users:
oping_maint6.0 -l [-B <$BSE path>]
Lists all the users in the oping_users file. For each user, displays the MK
optimization name, and Ingres name and group.
⇒ To list groups:
oping_maint6.0 -L [-B <$BSE path>]
Lists all groups in the oping_groups file, including each group name and all of
its users.

Chapter
Tuning the Ingres Database

7
Introduction ................................................................................. 7–1

Tuning the Database ......................................................................... 7–2
Rebalancing ............................................................................. 7–2
Maintaining the Database .................................................................... 7–3
Performing System Backups .............................................................. 7–3
Checkpointing ........................................................................... 7–3
Setting Up Database and Table Alternate Location Parameters ................................... 7–4
Tuning the Ingres Database 7–i

Chapter
Tuning the Ingres Database

7
Introduction
This chapter tells you how to tune and maintain the Ingres database.
Tuning the Ingres Database 7–1

Tuning the Database
Tuning the Database

You should tune your Ingres database periodically (approximately once a week).
Tuning the database helps maintain peak performance of SQL queries,
particularly after adding or deleting companies.
⇒To tune the Ingres database (iidbdb):

Run the Ingres optimizedb and sysmod utilities.
optimizedb –zk iidbdb
optimizedb –zk mk
sysmod iidbdb
sysmod mk
Since this is an administrative step, MK services should be turned off when you
run these commands.
You may need to stop the Ingres RMCMD process. For more details, refer to the
Ingres Database Administrator’s Guide.
Rebalancing
You should periodically re-balance the b-tree structure in tables that are
relatively dynamic, such as tdsls045 (sales orders), tisfc001(production orders),
and tiitm001 (item master). The frequency with which you should perform this
task and the tables to which this procedure applies vary with each
implementation of MK.
Use the MK utility, oping_modify.exe, to perform this re-balancing. On a dual-

server system, you must run this utility from the database server (See Appendix
D for more information).
You can apply the oping_modify utility to individual company tables or a list of
tables:
oping_modify –B –Ntdsls045 –p<package combination>
–C500
oping_modidfy –B –I<tablelist> -p<package combination> -C500
This modifies both the base table and all indices.
In addition, you can use the -X parameter to only rebuild indices:

oping_modify –X –I<tablelist> -C500

Maintaining the Database
Maintaining the Database

This section describes backup and checkpointing.
Performing System Backups
It is recommended that you periodically complete and verify full system

backups. Before performing a backup, you must shut down MK and Ingres
services and purge processed. After performing a backup, you must start up MK
and Ingres services. Since the MK installation can involve several servers
(application and multiple database servers), you must control the time and order
of these shutdown and startup processes on all servers.
Checkpointing
In addition to or in place of file backup, Ingres supports a checkpoint utility:

ckpdb. In addition, you can control journaling with the Ingres utility, alterdb.
Use the rollforwarddb utility to recover from online checkpoints. You can use
the infodb and auditdb utilities to display journaling information.
Refer to your Ingres System Reference Guide for instructions on these utilities.
The checkpoint, journal and audit locations are generally specified when Ingres
is installed.

Setting Up Database and Table Alternate Location Parameters
Setting Up Database and Table Alternate Location

Parameters
Ingres has a limit of 2 gigabytes on the size of any given physical table. Certain
tables associated with AWM and other MK commercial functions will grow
indefinitely, unless periodically archived through the MK system. Also it is
customary for system administrators to purge the print device queue (session
ttaad3221m000) approximately weekly.
If for some reason, you expect a table to grow beyond this Ingres 2 GB limit, you
need to request an alteration to the storage structure for the table so the table
spans multiple locations.
⇒To alter a table's location:

1. Make sure that all MK services are stopped.
2. Run the copydb Ingres utility to create the copy.in and copy.out command
files for the sql utility:
copydb mk “ttdinv800500” –umk
3. Alter the copy.in procedure to modify the storage configuration to span

multiple locations:
. . .
location = ( ii_database, ii_database_1 )
. . .
Remember that you must perform this editing for both the base table
specification and the indices.
4. Modify the %BSE%\lib\openingres\oping_storage file to reflect the
multiple locations. This text should be added at the top of the file, not the
bottom:
tdinv800:500:T:group:1:10:STRUCTURE btree LOCATION (ii_database,
ii_database_1)
tdinv800:500:I::1::STRUCTURE btree LOCATION (ii_database, ii_database_1)

Setting Up Database and Table Alternate Location Parameters
5. Use the accessdb Ingres utility to specify the new database location,
ii_database_1, for Ingres. This is a two-part process described more fully in
the Ingres Database Administrators Guide. Basically, using accessdb, the
administrator:
■ Requests to create the location (ii_database_1), specifying the folder
where the secondary location is to be located and the flags to associate
with the location (for example, journaling). You can specify the file
location (or area) of the database as an explicit NT folder location, or you
can use the Iingres utility, ingsetenv, to specify an Ingres environmental
variable (for example, II_DATABASE) that specifies the location area.
■ After the location is defined, use the Databases, Extend option of
accessdb to add the new “database” location to the Ingres database (for
example, mk).
6. Perform the copy.out part of the conversion:
sql mk –umk < copy.out
7. If step 6 completes successfully, then you can drop the existing table:
sql mk –umk
drop table ttdinv800500\g
\q
8. Perform the copy.in step to re-create the table and its indices:
sql mk –umk < copy.in

Chapter
Troubleshooting
8
Introduction ................................................................................. 8–1

Error Mapping and Reporting ................................................................ 8–2
Driver Errors ............................................................................ 8–2
Database Errors .......................................................................... 8–3
Timeout and Deadlocking Problems ........................................................... 8–6
Database Tuning ......................................................................... 8–6
Testing Database Changes ................................................................ 8–6
Troubleshooting 8–i
Chapter
Troubleshooting
8
Introduction
This chapter provides tips for troubleshooting in the MK Ingres environment. It
includes information about database errors and some potential solutions to
specific problems.
Troubleshooting 8–1
Error Mapping and Reporting

There are four categories of errors for MK:
Operating system errors Codes 1–99
MK database errors Codes 100–899
Network errors Codes 900–999
Ingres driver errors Codes >999
If a SQL statement generates an error, an error code is returned. The Ingres

driver maps this to a corresponding MK database error code, and that error code
is sent back to MK.
However, some Ingres errors do not have corresponding MK error codes. These
codes are passed back to MK after adding 1000 to the absolute value of the error
code.
The Ingres driver logs each error that it encounters into a log file called
$BSE/log/log.ingres. Analyze any errors using this file along with other log files
and $II_SYSTEM/ingres/files/errlog.log from Ingres.
Driver Errors
If an error code greater than 1000 is returned, you can determine the nature of
the error using the following methods:
Ingres error - 1000 = Database error
The following example illustrates how to interpret an Ingres error:

Error no: 3102
Error: (3102 - 1000) = 2102: Column not found
Note: When a fatal error occurs, more information is stored in the log files in the
$BSE/log directory. For example, if bdbpost6.0 causes an error, this is reported
in the log.bdbpost6.0 file. The log files are very useful for tracking down the
cause of errors.

Database Errors
Refer to the following table for a description of generic database errors:
Code Error Name Error Description

100 EDUPL Duplicate value
101 ENOTOPEN Table is not open
102 EBADARG Bad argument
103 EBADKEY Bad key
104 ETOOMANY Too many files open
105 EBADFILE Bad ISAM file format
106 ENOTEXCL Table is not exclusively locked
107 ELOCKED Record is locked
108 EKEXISTS Key already exists
109 EPRIMKEY Primary key
110 EENDFILE End of file reached
111 ENOREC No record found
112 ENOCURR No current record
113 EFLOCKED Table is locked
114 EFNAME File name too long
116 EBADMEM Cannot allocate memory
117 EBADCOLL Bad custom collating
123 ENOSHMEM No shared memory initialized
125 ENONFS Cannot use nfs
127 ENOPRIM No primary key
129 EUSER Too many users
131 ENOFREE No free disk space
132 EROWSIZE Row size too big
133 EAUDIT Audit trail exists
134 ENOLOCKS No more locks
135 ENOTBL No more tables for commit
136 ENOSPACE No space in shared memory

137 ENOADDR No space in before/after image
table
138 ENOFTBL No space for table entry
139 ENOCOMMIT No free commits available
140 ETRANSON Invalid operation when
transaction is on
141 ETRANSOFF Invalid operation when
transaction is off
142 EADMON Some administrative process is
running
143 ENOFLUSH No flusher entry available
201 EROWCHANGED Record changed after delayed
lock
202 EDBLOCKED Database is locked
203 ETRANSACTIONON Action not allowed within
transaction
204 EISREADONLY Transaction is read only
205 ENOTINRANGE Out of range
206 ENOTLOCKED Record is not locked
207 EAUDIT Error of audit trailer
208 EPERMISSION Permission denied
209 EMIRROR Error on mirroring of database
321 Failed to acquire a lock
322 Index handle not created
(internal error)
335 Data conversion error
336 Data conversion error
345 Requested operation not defined
(internal error)
357 Failed to start transaction
501 EMEMORY Internal memory error
502 EON Already logged on


503 EBADADRS Illegal address
504 EBADFLD Undefined column
505 ENOSERVER No server specified
506 ENOTABLE Table does not exist
507 ETABLEEXIST Table already exists
508 ENOTON Not logged on to a database
509 EBADCURSOR Bad memory cursor
510 EDBNOTON Database server not on
511 EWRONGVERSION Version of client versus server
not correct
512 EDDCORRUPT Corrupted data dictionary
513 ENODD Data dictionary not found
514 ESECURITY Security error
522 EBDBCLDISCONNECT Database server disconnected
600 EREFERENCE General reference error
601 EREFLOCKED Reference table is locked
602 EUNDEFREF Reference is not defined
604 EREFUPDATE Cannot update reference
605 EREFEXISTS Reference exists, cannot delete
606 EREFNOTEXISTS Reference does not exist
607 ENOREFTBL Reference table not found
608 ENOREFCNT No reference counter
609 EUPDREFCNT Error at update of reference
counter
850 EABORTONLOCK Automatic rollback on lock error
851 EABORTONDEADLOCK Automatic rollback on deadlock
Timeout and Deadlocking Problems
Timeout and Deadlocking Problems

The most likely areas of contention are those records that are created by multiple
users at the same time. For example, sales order lines and purchase order lines
are frequently areas of contention since new lines are added to the next available
page, and multiple users may be simultaneously competing for a lock on that
page. This section recommends modifications you can make to reduce
deadlocking and time-out problems (850 and 851 error codes).
Database Tuning
You can reduce locking and timeout problems by periodically tuning your Ingres
database. Refer to Chapter 7, “Tuning the Ingres Database,” for more
information.
You can also use Ingres to set up row level locking for some highly used tables.
Refer to Appendix E, “Row Level Locking,” for more information.
Testing Database Changes
When testing these database changes, the test database should be representative
of your production database. For example, if you expect 100 concurrent users on
the production database and are doing benchmarks with 30 concurrent users, the
benchmark database should be at least one third of the size of the eventual
production database.
Similarly, if you expect production users to choose among 500 items or 250
suppliers, the test database should have a proportionate number of items and
suppliers. If users are all using the same item or supplier during benchmark
testing, the benchmark will show more locks and deadlocks than production.
Making the pilot database as representative as possible will help ensure the
reliability of the results.

Appendix
Files and Executables

A
Executables ................................................................................ A–1

Driver Files ................................................................................ A–3
Table Definition (tabledef6.0) ................................................................ A–4
IPC File (ipc_info) .......................................................................... A–5
Files and Executables A–i

Appendix
Files and Executables

A
This appendix lists various files and executables required by the Ingres driver
and the directories where they are located.
Executables
The files listed in the table below are the Ingres executables used by MK.
Executable File Description

oping_srv6.0 The UNIX Ingres driver.
Location: $BSE/bin.
oping_srv.exe The Windows NT Ingres driver.
Location: %BSE%\bin.
oping_exec6.0 Auxiliary UNIX file used by the driver. This
executable should be owned by root and should have
the setuid bit on (permission 4750; refer to the
installation procedure in the “Preparing Ingres for
MK” chapter).
Location: $BSE/bin.
oping_exec.exe Auxiliary Windows NT file used by the driver.
oping_inst6.0 The UNIX script for installing the Ingres driver,
setting up the initial database group, required users,
and default table storage specifications.
Location: $BSE/bin.
Files and Executables A–1

Executables
Executable File Description

oping_admin6.0 The UNIX shell script for administration. This script
calls oping_maint6.0.
Location: $BSE/bin.
oping_maint6.0 This is a UNIX program internally called by
oping_admin6.0 for performing various driver
administration functions, such as adding users to a
group.
Location: $BSE/bin.
oping_maint.exe This is a Windows NT program internally called by
oping_admin6.0 for performing various driver
administration functions, such as adding users to a
group.
oping_modify6.0 UNIX utility for Ingres table and database
housekeeping.
Location: $BSE/bin.
oping_modify.exe Windows NT utility for Ingres table and database
housekeeping.
Note: The oping_admin6.0, oping_maint6.0, and oping_modify6.0 tools are

only required for administration. The driver is not affected by the presence or
absence of these files.
A–2 MK in the Ingres Environment

Driver Files
Driver Files
The files listed below are the MK Ingres driver files.
Driver File Description

oping_groups This file contains all Ingres database (group) names
accessible by MK users. For information about the
structure and maintenance of this file, refer to the
Location: $BSE/lib/openingres (UNIX) or
%BSE%\lib\openingres (NT)
oping_users This file contains the information about all MK users that
are going to access the Ingres driver. This file maps MK
users to an Ingres database (group). For information about
the structure and maintenance of this file, refer to the
oping_storage This file contains database storage specifications for MK
tables and indexes. For information about the structure and
maintenance of this file, refer to the “Maintaining Driver
Files” chapter.
tabledef6.0 This file identifies the type of the driver to be used for
accessing a table, plus other driver parameters. It is
maintained through the Assign Tables to Databases
(ttaad4111m000) session.
Location: $BSE/lib (UNIX) or %BSE%\lib (NT)
ipc_info This file defines the path name for the driver executables
and the protocols used for communication between the MK
shell and driver processes. For information about the
structure and use of this file, refer to MK System
Administrator’s Guide.
Location: $BSE/lib (UNIX) or %BSE%\lib (NT)

Table Definition (tabledef6.0)
Table Definition (tabledef6.0)

Each line in this file consists of four fields separated by a colon ( : ). The format
of the line is as follows:
[{<user>}]<table_name>:<company number>:<driver specification>:<audit trail>
In addition to specifying the database driver as Ingres, you can use this file to
assign values to driver environment variables such as:
Variable Description
II_SYSTEM This variable identifies one instance of an Ingres
installation on a system. It should contain the full
path name of the Ingres installation directory.
When starting the Ingres driver, the MK shell reads
and sets this environment variable so that the Ingres
driver uses the corresponding installation.
ING_TIMEOUT The number of seconds that a statement should be
retried before timing out when a resource lock is
encountered.
MMX_PREFETCH The number of rows to prefetch:
1—no prefetch of rows.
0—the database determines the number of rows to
prefetch.
>1—indicates the number of rows to prefetch.
ING_LOCKMODE The level of locking. Set to row for row-level (record-
level) locking. The default is page-level locking.
UNIX Example In this UNIX example, table tiitm990xxx (all companies) is in the Ingres database;
all tiitm999xxx tables are on the system darkman; and tiitm055xxx tables are
created in INFORMIX, Ingres, and on the remote machine, Viper. All other
tables are in Ingres.
tiitm990:*:ingres(II_SYSTEM=/disk1,ING_TIMEOUT=10):N
tiitm999:*:darkman
tiitm055:*:informix&ingres&viper
*:*:ingres(II_SYSTEM=/darktools):N
For more information about defining databases and the tabledef6.0 file, refer to
the “Managing Databases” chapter in MK System Administrator’s Guide.
Windows NT Example In this example, all tables and all companies (represented by *:*) are in the Ingres
database, which is installed on drive E of the server star. The Audit Trail is set to
No.
*:*:ingres(II_SYSTEM=e:\oping):N
For more information about defining databases and the tabledef6.0 file, see
System Administrator’s Guide, Volume 2, Database Management.
A–4 MK in the Ingres Environment

IPC File (ipc_info)
IPC File (ipc_info)

Information about the database communication method is stored in the ipc_info
file, which contains the following:
■ Driver name (name of driver specified in tabledef6.0)
■ Operation mode
■ Semaphore key
■ Message key
■ Communication protocol
■ Path of the server program, which may include environment variables such
as $BSE (UNIX) or ${BSE} (NT)
For example, a typical ipc_info file might look like this:
ba6.0 s 601 610 p ${BSE}/bin/ba6.0

audit s 501 510 p ${BSE}/bin/audit_srv6.0
bx6.0 s 302 320 p ${BSE}/bin/bx6.0
ingres s 305 350 p ${BSE}/bin/oping_srv6.0
1 2 3 4 5 6
1 Driver name 4 Message key

2 Operation mode 5 Protocol (s/p)
3 Semaphore key 6 Path of executable server program
For more information about defining databases and the ipc_info file, refer to the
“Managing Databases” chapter in MK System Administrator’s Guide.

Appendix
Table and Index Parameters

B
Table Parameters ............................................................................ B–2

List of Parameters............................................................................ B–3
Table and Index Parameters B–i

Appendix
Table and Index Parameters

B
This appendix describes the parameters used for creating UNIX tables and
indexes. The Ingres driver can control various Ingres-specific parameters used
for creating tables and indexes. These parameters are specified in the
$BSE/lib/openingres/oping_storage file.
Table and Index Parameters B–1

Table Parameters
Table Parameters
The table parameters below can be controlled through this storage file.
Parameters can be specified in any order or combination. Some parameters
require keywords that can be typed in either uppercase or lowercase as follows:
STRUCTURE/structure
LOCATION/location
NOJOURNALING/nojournaling
JOURNALING/journaling
DUPLICATE/duplicate
NODUPLICATE/noduplicate
INDEX/index
NOINDEX/noindex
FILLFACTOR/fillfactor*
ORDER/order*
MINPAGES/minpages*
MAXPAGES/maxpages*
LEAFFILL/leaffill*
NONLEAFFILL/nonleaffill*
NOCOMPRESSION/nocompression*
COMPRESSION/compression*
Note: The parameters marked with an asterisk (*) should be used only when
modifying an existing table using oping_modify.
B–2 MK in the Ingres Environment

List of Parameters
List of Parameters
The following describes each table or index parameter and provides examples of
their usage:
STRUCTURE Identifies the kind of storage structure needed.

{bob}tiitm998:*:T:group:1:10:STRUCTURE btree
{bob}tiitm:*:I::1::STRUCTURE btree
In this example, table ‘tiitm998’ for all company numbers is created with the
btree structure. Indexes for this table are also created with the btree structure.
Use the following storage structures to create or modify tables:

■ btree
■ heap
■ hash
■ heapsort
■ isam
A secondary index cannot have a heap or heapsort storage structure. All

columns of all tables have the NOT–NULL clause.
The default storage structure, written by oping_inst6.0, for the table created by
the Ingres driver is btree.
LOCATION Specifies the location where the new table is created. The location name(s) must
be defined on MK, and the database must have been extended to the
corresponding area(s). If no location name is specified, the default area for the
database is assumed.
{bob}tiitm998:*:T:group:1:10:STRUCTURE btree \
LOCATION (location1, location2 )
NOJOURNALING Indicates that journaling should be disabled for the table(s). This is the default.
LOCATION (location1, location2 ) NOJOURNALING

List of Parameters
JOURNALING Ingres uses a circular log file. To save your transaction log records, use the
Ingres journaling facility. Each database has its own journal files, if journaling is
enabled. The default is nojournaling.
LOCATION (location1, location2 ) JOURNALING
The journal file contains a record of all changes (insert, update, and delete) made
to that database since the last checkpoint was taken. The archiver process
(dmfacp) removes completed transactions from the transaction log file and
writes them to the corresponding journal files for the database. This facility is
especially useful after a disk crash to reconstruct the table. It can also log the
history of changes made to the table.
For a complete explanation of journaling and nojournaling, refer to Ingres

Database Administrator’s Guide.
DUPLICATE Indicates whether the table can contain duplicate rows. It cannot be used with a
heap storage structure. The default is noduplicate.
NODUPLICATE Indicates that the table cannot contain duplicate rows. It cannot be used with a
heap storage structure. The default is noduplicate.
LOCATION ( location1, location2 ) NODUPLICATE
INDEX Use I to refer to an index. Specify the base table structure and index table
structure for the table. Enter the following lines in oping_storage:
{bob}tiitm999:000:T:group:1:10:STRUCTURE btree \
LOCATION ( location1, location2 )
{bob}tiitm999:000:I::1::STRUCTURE btree
Then run oping_modify6.0 to modify the base table structure from heap to
another structure type (such as btree) and to create all indexes.

List of Parameters
NOINDEX Use NOINDEX to avoid creating indexes.
For the fastest loading of bulk data, use bdbpost –f. This creates tables and
inserts rows immediately without creating the indexes on the tables. The
example that follows illustrates this strategy.
Assume you are creating table ‘ttiitm999000’ from a sequential dump called
‘tiitm999.dump’. Use the information below to avoid creating an index.
■ Enter the following lines in the oping_storage file:
{bob}tiitm999:000:T:group:1:10:STRUCTURE btree
LOCATION ( location1, location2 ) NOINDEX
■ Run bdbpost:
bdbpost6.0 -C100 < tiitm999.dump
■ After the bdbpost operation is finished, change the

$BSE/lib/openingres/oping_storage file for this particular entry and
remove the NOINDEX parameter.
FILLFACTOR Specifies the percentage (1 to 100) of each primary data page that should be filled
with rows under ideal conditions.
FILLFACTOR 80
FILLFACTOR does not have any effect when a new empty table is created, but it
can be used to reorganize the table when doing housekeeping activity.
Note: Be careful when using fillfactors. Large ones can overflow the data pages
and degrade performance for a non-uniform key distribution.
Below are the default FILLFACTOR values for different storage structures:
Storage Structure FILLFACTOR Value

btree 80
compressed btree 100
LEAFFILL Specifies the maximum percentage value to fill index leaf pages. Leaf index
pages are the index pages directly above the data pages. The default value is
70%. This parameter only applies to the btree structure.
LEAFFILL 80

List of Parameters
NONLEAFFILL Specifies the maximum percentage value to fill non-leaf index pages. These are
the pages above the leaf pages. The default value is 80%. This parameter applies
only to the btree structure.
NONLEAFFILL 70
{bob}tiitm998:*:I:group:::STRUCTURE btree \
NONLEAFFILL 70
Note: Both the LEAFILL and NONLEAFFILL parameters assist in controlling

locking contention in btree index pages.
NOCOMPRESSION Specifies no key and data compression. This is the default behavior of the
driver.
NONLEAFFILL 70 NOCOMPRESSION
COMPRESSION Specifies key and data compression.
Several examples are shown below:

■ Compress both key and data:
NONLEAFFILL 70 COMPRESSION (key, data)
■ Compress only data:

NONLEAFFILL 70 COMPRESSION (nokey, data)
■ Compress only key:

NONLEAFFILL 70 COMPRESSION (key, nodata)
The following table lists the valid types of compressions for Ingres storage
structures:
Storage Base/Secondary Data Key Compression

Structure Table Compression
Hash Base table Yes No
Secondary table Yes No
Heap Base table Yes No
Secondary table No No
Btree Base table Yes Yes
Secondary table No Yes
ISAM Base table Yes No
Secondary table Yes No
Note: MK uses only btree.

Appendix
Installation and Server Parameters

C
Ingres Logging System ....................................................................... C–1

Database Server Parameters .................................................................. C–2
Installation and Server Parameters C–i

Appendix
Installation and Server Parameters

C
This appendix provides additional information and guidelines about Ingres
parameters. Use this information with the Ingres Database Administrator’s Guide
and Ingres Installation and Operations Guide when configuring the Ingres server.
Ingres Logging System

The following parameters are specified at installation time. You can change
them by running iistartup -init.
Parameter Recommendation
Log buffers Set to 32. Increasing the log buffers improves logging
efficiency but may increase log fragmentation.
Log buffer size Set to 8. Increasing the log buffers improves logging
efficiency but may increase log waits.
Installation and Server Parameters C–1

Database Server Parameters

The parameters in the table that follows are best set by running the ingres cbf
utility.
Note: The server options listed here are for a 128 Ingres user server. Trace
points QS510 and DM420 have played a key role in determining the appropriate
settings. For more information about these parameters, refer to Ingres Systems
Reference Guide.
The following parameter values are only recommendations and may not be
optimal for your system. Consult an Ingres DBA for configuring specific
parameters for your system.
connect_limit 3 to 5 times the number of MK users.
fast_commit Set this parameter to reduce writes from Ingres transaction log file to
disk. It cannot be used in a VAXcluster installation, but it may be used
on any node of the cluster with a non-clustered Ingres installation. It
compliments the write_behind and shared_cache options.
write_behind Set to 4. Spreads I/O over time by asynchronously writing “dirty” pages
(modified pages that have not been written) to the databases. It speeds
work at consistency points by reducing the work to be done and is
usually used with fast_commit. If write_behind is set too high, your
system can become I/O bound. If write_behind is set too low, your DMB
cache may occasionally fill up.
qsf_pool_size Set to 7,800,000. The default is 100 kb+ (session x 40 kb). Use the QS510
trace point to determine usage.
The MK driver takes advantage of repeated queries. A large cache is
highly recommended. Use this calculation for optimal performance: 100
kb+ (session x 60 kb)
dmf_group_size Set to 16. Represents the size of group buffers used in read-ahead
operations.
C–2 MK in the Ingres Environment

dmf_group_count Set this to (number of sessions/4). The number of sessions can be
considered to be the same as the connect_limit.
dmf_cache_size Set to 8192. The default is (128 kb+ 4 x sessions). You should increase
this value, if memory permits, to improve server efficiency and to lower
disk I/O. Use QS510 and DM420 to determine the optimal number.
To figure the size of the DMF cache, note that the unit is 2K pages, so a
cache of size 8192 takes up 16 MB of shared memory. To reach the screen
where this parameter can be changed in CBF, select DBMS Server,
Configure, Cache, 2K cache, Configure, Derived. Do not let
dmf_wb_start get greater than 1500 and do not let dmf_wb_end get
greater than 1000 as these are computed from dmf_cache_size. Change
their values manually if needed.
dmf_count_read_ahead Set to 128. This is the maximum number of group buffers. Group buffers
are used when chunks of data are selected.
dmf_size_read_ahead Set to 32. Should be set to 64 if using a 64-bit machine. Any size larger
than 32 on a 32-bit machine may cause bit masking information to be lost.
The DMF marks the last bit when the page is used. This is the group
buffer size in pages; a reasonable starting value is 16; if many locations
are used, 32 might be more appropriate.
dmf_wb_start Set to the value to start flushing dirty pages (such as 4,000).
dmf_wb_end Set to the value to end flushing dirty pages (such as 1,600).
dmf_mlimit Set to the maximum number of modified pages.
rdf_memory Set to 50% of qsf_pool_size (such as 4,000,000). The RDF facility caches
table structure information. Most queries access this facility twice before
any database manipulation. Use the QS510 trace point to determine RDF
hits and increase or decrease memory size as necessary.
rdf_max_tbls Set to the number of tables to cache. In this example, the recommended
setting is 2,560.
rdf_max_cols Set to the average number of columns in tables. Recommended setting is
30.
qef_qep_size Use the trace point QS510 to find the optimum setting. In this example,
the recommended setting is 8,192. The default is 2560.
Installation and Server Parameters C–3

Appendix Running MK Database Utilities in a
Windows NT Dual Server
D Environment
BDBPRE.EXE and BDBPOST.EXE Utilities .................................................... D–2

OPING_MODIFY.EXE Utility ................................................................ D–3
GENLIST_OPING.CMD Utility .............................................................. D–4
Running MK Database Utilities in a Windows NT Dual Server Environment D–i

Appendix Running MK Database Utilities in a
Windows NT Dual Server
D Environment
The following commands require additional setup when running MK in a
Windows NT dual server environment:
■ bdbpre.exe
■ bdbpost.exe
■ genlist_oping
■ oping_modify
Running MK Database Utilities in a Windows NT Dual Server Environment D–1

BDBPRE.EXE and BDBPOST.EXE Utilities
BDBPRE.EXE and BDBPOST.EXE Utilities

The bdbpre.exe and bdbpost.exe utilities should be run on the application server.
Since the database server is on another node, you must supply logon information
in order for the utility to connect to the database server.
These utilities require the standard MK connect mechanism, which are the
remote user files, %BSE%/lib/user/r<user>. You can use the MK
camk3rem.exe utility to create these files. The MK installation process generally
creates these files for mkadmin on the application server. It is important that for
a particular user that r<user> files are created on both the application and
database servers. The first r<user> file allows the application server to connect
to the database server in order to start the proper database driver. The second
r<user> is required to allow the database driver running on the database server
to access %BSE%/lib/user/u<user> files on the application server. Generally, it
is only necessary to create the pair of r<user> files for user mkadmin. If another
user wants to run these utilities, he can force the USER to mkadmin:
set USER=mkadmin
bdbpost -k -n -c402 –y<MK system name> -F tables.pre
There is an option available for these utilities to specify the database driver to be
used for the utility. For example:
bdbpost -d ingres -n -c402 –y<MK system name> -F tables.pre
Do not use this option on the application server unless the specified database
driver exists and is runnable on the application server.
D–2 MK in the Ingres Environment

OPING_MODIFY.EXE Utility
OPING_MODIFY.EXE Utility
This utility is always run on the database server. It needs to access u<user>
information, which exists only on the application server. Therefore, the setup
must include not only specifying the user and having a r<user> file, but also
specifying the application server node for the application. Therefore, the usual
calling sequence for this utility in a dual server environment is:
set USER=mkadmin
set BS_HOST=APP
oping_modify -B –I <tablelist> -C402
where APP is the application server name.
Running MK Database Utilities in a Windows NT Dual Server Environment D–3

GENLIST_OPING.CMD Utility
GENLIST_OPING.CMD Utility
This utility is run on the database server. It generates a list of tables belonging to
a particular company.
⇒To run the genlist_oping utility:

1. As mkadmin, log on to the DB console.
2. Using the Windows NT Explorer, create a mapping to disk share
\\APP\MMX. Assume that the drive letter is 'm'.
3. Open an MS DOS Prompt window. Enter the following commands:
Set PATH=%PATH%;m:\bse6.0\bin;m:\bse6.0\etc.
Set USER=mkadmin.
genlist_oping <table> mk > <tablefile>
D–4 MK in the Ingres Environment

Appendix
Row Level Locking

E
Setting Row Level Locking in Ingres ........................................................... E–2

Setting Row Level Locking in MK ............................................................. E–3
Row Level Locking E–i

Appendix
Row Level Locking

E
This appendix describes how to perform row level locking. This procedure is for
Ingres version 2.0 or later.
We recommend that an entire company not have row level locking turned on. It
is better to turn row level locking on for selected tables with heavy usage. Some
examples are tcmcs047, tdsls045, tdpur045.
Before You Begin It is strongly recommended that you back up the Ingres database.
Row Level Locking E–1

Setting Row Level Locking in Ingres
Setting Row Level Locking in Ingres

Before You Begin You must be logged on as Ingres. If you are in a dual server Windows NT
environment, you must be on the database server. The 2k DMF Cache must be
enabled for the tables that use page level locks.
⇒To set row level locking in Ingres:

1. Use the Ingres cbf utility to set the DBMS Cache to at least a 4k size. We
recommend a 16k size due to the overhead that row level locking incurs:
2. Shutdown Ingres and restart to apply the changes made using cbf.
E–2 MK in the Ingres Environment

Setting Row Level Locking in MK

⇒To set row level locking in MK:
1. Run the Maintain Database Definitions (ttaad4110m000) session to create a
new database definition.
For a Single Server In the parameter field, add the following to the end of the data already in the
Environment field. If no data is in the parameter field, add the following line without the first
comma:
,ING_LOCK_MODE=row,MMX_PREFETCH=1
For a Dual Server In the parameter field, add the following:

Environment
<server name>[@<MK system id>]!II_SYSTEM=<Ingres path>
,ING_LOCK_MODE=row,MMX_PREFETCH=1
where
■ server name — name of the machine on the network. The name returned by
the hostname works.
■ MK system id — name assigned to the MK installation. On UNIX, it is the
token passed as the –r parameter to MKspwnsrvr6.0. On NT, it is defined in
the registry under the
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\"MK
Server" \Systems key.
■ Ingres path — the path into the Ingres installation.

2. Use the Assign Tables to Databases (ttaad4111m000) session to assign the

database definition to a table:
To assign the database definition to a table, complete the fields as follows:

■ All Comp—no
■ Company—company number
■ Table Selection—specified table
■ Table/Module—table name to be converted to row level locking
■ Database—database number created in step 1
Once this has been completed Zoom to the Convert to Runtime DD session
and enter Y in the Choice field. This writes the information to
%BSE%\lib\tabledef6.0. For example:
tcmcs047:200:ingres(ING_LOCK_MODE=row,MMX_PREFETCH=1):N
tisfc001:200:ingres(ING_LOCK_MODE=row,MMX_PREFETCH=1):N
*:*:ingres:N
E–4 MK in the Ingres Environment

3. Edit the %BSE%\lib\openingres\oping_storage file to define the PAGESIZE

and LEAFFILL for tables. The PAGESIZE must be at least 4096 and a
LEAFFILL factor of 80 must be applied for the tables which have row level
locking turned on.
■ The following example shows table tcmcs047 in all companies will have
journaling turned on and a page size of 4096:
tcmcs047:*:T:group:1:10:STRUCTURE btree LEAFFILL 80 PAGESIZE 4096
journaling
tcmcs047:*:I::1::STRUCTURE btree LEAFFILL 80 PAGESIZE 4096
■ The following example shows table tcmcs047 in company 200 only will
have journaling turned on and a page size of 4096:
tcmcs047:200:T:group:1:10:STRUCTURE btree LEAFFILL 80 PAGESIZE 4096
journaling
tcmcs047:200:I::1::STRUCTURE btree LEAFFILL 80 PAGESIZE 4096
Any special oping_stroage lines must be added before the default lines of:
4. Run oping_modify –B –p<package combination of company> –

N<tablename> –C<company number>. For example:
oping_modify –p817US –Ntcmcs047 –C200
Tables will now have row level locking turned on.

Ingres 0212

Uploaded by

Copyright:

Available Formats

Ingres 0212

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Ingres 0212

Uploaded by

Copyright:

Available Formats

MK

MK in the Ingres Environment

Chapter 2: Ingres Database Driver Overview

Chapter 3: Configuring Ingres for MK

Chapter 4: Mapping MK to Ingres

Chapter 6: Maintaining Driver Files

Chapter 7: Tuning the Ingres Database

Appendix A: Files and Executables

iv MK in the Ingres Environment

Appendix C: Installation and Server Parameters

Appendix D: Running MK Database Utilities in a Windows NT

Appendix E: Row Level Locking

This guide does not, however, provide information on Ingres database

1–2 MK in the Ingres Environment

How This Guide Is Organized

1–4 MK in the Ingres Environment

Ingres Database Driver Overview

Introduction ................................................................................. 1–1

Ingres Database Driver Overview 2–i

Ingres Database Driver Overview

The MK shell surrounds or insulates the MK application and is responsible for

The relationship between Ingres and MK is depicted in the following diagram:

Ingres Database Driver Overview 1–1

How the Driver Works

1–2 MK in the Ingres Environment

Key File Description

Key File Description

Ingres Database Driver Overview 1–3

Configuring Ingres for MK

Introduction ................................................................................. 3–1

Configuring Ingres for MK 3–i

Configuring Ingres for MK

Configuring Ingres for MK 3–1

Requirement Approximate Disk Space

Disk 1 Disk 2 Disk 3

Disk 1 Disk 2 Disk 3 Disk 4

Disk 1 Disk 2 Disk 3 Disk 4 Disk 5

Note: This diagram pertains only to non-RAID configurations.

3–2 MK in the Ingres Environment

Configuring Ingres for MK in a UNIX Environment

Using your previously defined II_SYSTEM environment variable, execute one of

■ For the C shell:

■ For the Korn shell:

Configuring Ingres for MK 3–3

Setting Ingres Parameters

Parameter Recommended Value

3–4 MK in the Ingres Environment

Configuring the Ingres Database for MK

Configuring Ingres Parameters for MK

Parameter Recommended Value

Configuring Ingres for MK 3–5

Creating Ingres Users and an Ingres Database for MK

⇒To create the database:

2. To access the Ingres administration program enter the command:

The following form appears:

Databases : List all databases.

Users : List all users.

Help : Access the INGRES help facility.

Databases Locations Users Help Quit :