WAIUG
Meeting, Wednesday June 16,1999
by
Peter Wages
On Wednesday June 16 1999,
The Washington Area Informix Users Group held a regular quarterly meeting.
Nick Nobbe, WAIUG program director chaired the meeting. Nick announced
that elections for the Board will be held at the next meeting in September,
and as, Lester Knutsen, our founding president, wishes to step down, the
user group will need a new president. Lester will continue to actively
participate in the user group and as a board member.
There were two speakers at
the meeting. Lynn Oaks of Informix Software gave an overview of Red Brick
and Michael Crouch introduced the audience to SuperNova. Informix provided
refreshments during the intermission.
Introduction to Red Brick
by Lynn Oaks, Principal Software Engineer,
Informix Software loaks@informix.com
Lynn Oaks, Principal Systems
Engineer at Informix's Vienna Virginia office, gave an overview of Red
Brick's Data warehouse product. Informix bought Red Brick last year. Lynn
started by giving the audience trivia about Red Brick. One is the fact
that Red Brick is the leading data warehouse is Japan.
Red Brick has had a data
warehouse product for approximately 10 years. To support its warehouse
software, Red Brick developed its own database engine, and component tools.
Among the tools are an administrator tool, an Interactive SQL Tool, A Table
Management Utility Loader, a Parallel Table Management Utility, a STAR
Index capability and a Bit Map Index called the Target Index.
Informix Corporation believes
that Red Brick's family of products is a good fit with Informix. A new
version of Informix MetaCube, a comprehensive business intelligence offering,
will incorporate many of the Red Brick capabilities.
Lynn ended her talk by telling
us where the name "Red Brick" came from. The first building, which housed
the company, was a red brick building. The phrase "Red Brick" conjures
up the notions of durability, strength and solidness.
Informix
provided a tremendous table of refreshments.
SuperNova:
Comptetive Edge through Application Integration
by Michael Crouch, Systems
Architect, SuperNova Inc.
michaelc@supernova.com
On SuperNova's web page is
the following message," We're about to tell you what most EAI venders won't.
Plug & Play application integration simply does not work!" With this
quote, Mike Crouch introduced SuperNova to the WAIUG user group. SuperNova,
a privately owned Dutch company, specializes in enterprise application
a development and integration SuperNova aims to cut the software costs
of its customers by providing technology which integrates multiple databases,
operating systems network protocols, user interfaces and Web applications.
SuperNova's integration tool
is the Universal Integration Engine. The engine supports over 25 operating
systems, 28 databases, including Informix,
and has support for Web technologies such as JAVA. SuperNova has announced
support for Linux.
Mike stated that SuperNova
was a small company and that its product was easy to install. He mentioned
that 3M/Imation , a company located in West Virginia, has been quite
happy with the software.
Mid-Atlantic
PeopleSoft / Informix (PeopleMix) Special Interest Group (SIG)
By
Nadia Skiscim
The 6th Mid-Atlantic PeopleSoft
/ Informix (PeopleMix) Special Interest Group (SIG) was held on April 28,
1999 at the GEICO Corporate facility in Bethesda, MD. Bill Heineman was
instrumental in coordinating this meeting; we thank Bill for his assistance
in organizing this meeting and for letting us use the GEICO Executive conference
room. The attendance included over 30 people with several traveling from
Atlanta, Chicago, Kentucky, New York, and Saint Louis.
Kathleen Zimmerman from PeopleSoft
explained the restructure of PeopleSoft Divisions and the "Account Manager"
role. PeopleSoft has restructured its dozen or more IBU into 3 Divisions.
The "Account Manager" role has been restructured into 4 areas: Customer
Care Business Center (CCBC), PS Advisor, Professional Services (i.e. Consulting
Services), and Advantage Services Menu (i.e.: Packaged Services). Application
Bundling is now referred to as "Product Update". These are non-cumulative
and must be applied in order. PeopleTools Updates are cumulative. PT 7.05
expected 5/99. Expiration of PS7 support is unclear - believed to be 7/2000.
This is dependent on the availability of PS8 which is scheduled for 5/2000.
Concerns were expressed about the migration path and time required to migrate
before support expires. A migration path from 7.0 to 8.0 has since been
announced.
Nadia Skiscim from Informix
gave a product demonstration of Visionary, showing how to graphically navigate
the information in your database. At this time this product is not certified
with PeopleSoft but Informix
is working to build this relationship.
Erasmo Acosta from Informix
presented the Informix 7.31 features pertinent to a PeopleSoft installation.
Jon Stone from PeopleSoft
provided highlights of what's new with PeopleSoft and Informix, version
certification schedule, and fielded customer questions.
The remainder of the day
was filled with round table discussion covering technical Q&A and the
SIG organization.
During the Q&A discussion,
Hewitt company related their recent experience upgrading to PeopleSoft
HR version 7.5. Notes follow:
-
use 7.52 or 7.54.10 Tools with
HR
-
set pdqpriority on anything
that's building indexes
-
run update statistics whenever
creating new tables
-
queries are in the conversion;
don't need query definitions, trees, and operator security when you do
an export of the tool set (at least evaluate the need to do this)
-
choose a setid that you're going
to keep
-
alltabs26 - review tables that
might be put in different spaces and do it in this step
-
alltabs30 - split out job and
personal_data to their own files because we were getting long transaction
backouts
-
took tables that we knew were
going to be altered in step 30 and split out into their own space; we put
them in a holding/group space so that the renames put them in the correct
space
-
alltabs30 - alter in place option
must be turned off
-
converted some .dms scripts
to SQL so we could do begin works and commits - chr7505, chr7506, chr7515,
chr7518
-
chr7514.dms - drop pers_data_effdt
indexes first
-
chr7515 - unloaded the data
and loaded it with the parallel loader
-
chr7534 - drop indexes; script
has lousy error handling
-
didn't change the SQR's because
we were able to meet our timeframes; may want to review programs for performance
-
alltabs34 - ethnic group did
not get cleaned up correctly
-
step 35 create views - we just
used the tool instead of the script; needed to check counts because the
tool didn't always create the views
-
step 36 - didn't use the project;
just dropped and recreated all indexes; can use the db schema and parse
out the indexes
-
step 39 - review and may want
to unload and load rather than using the scripts (e.g. the payroll script
took 1 hour to run)
-
DDDAudit doesn't work with the
views
-
ran through the upgrade steps
5 times (payroll database, training database, 2 iterations of the HR database,
production move)
-
applicant_hist view doesn't
have outer join for the disability table
Other issues were discussed
during Q&A. Some Enhancement Requests noted were:
-
Client Monitoring
-
Direct inserts of text type
via products like SQR
-
Suggestions for "FAQ" - Kathleen
Zimmerman of PeopleSoft suggested using PeopleConnection as a repository.
Bill Heineman was elected as
the SIG President! The vice-president nominee is still considering the
position.
Mark Reed of GEICO volunteered
to take ownership of the configuration spreadsheet. This has not been updated
in the last year so we will ask for updates once we discuss the best way
to update, maintain, and disseminate this information. If you have opinions
please feel free to share them with us.
Since PeopleMix will be meeting
at the Informix and PeopleSoft conferences this summer, it was decided
that the next regional meeting will not be held until April 2000 (unless
something exciting happens in 4Q99). Potential agenda topics for the next
meeting include: OLAP tools, archiving, PSoft 8, IDS 9.2, performance tuning
workshop, and enterprise replication.
For more information about
the PeopleMix Special Interest Group please contact Nadia Skiscim at 703-847-3323
or nadias@informix.com.
jselect
- A Java processor for SQL SELECT statements.
Sujit
Pal
Introduction
jselect
is a Java program which accesses an Informix database using Informix's
type 4 JDBC driver, processes the Informix SQL SELECT statement, and returns
the result in either tabular, columnar or pipe-delimited format, with or
without a title, depending on what is specified on the option list.
The jselect program was primarily
designed to monitor databases for a internet application that had been
partitioned across a group of eight Sun E-10000 boxes for performance.
Four of these boxes contain Informix database instances, each with one
or more databases. The monitoring tools needed to access data from the
different database to determine success or failure of certain operations.
They also needed to reside on a single box to provide a single point from
which to monitor the application.
The solution in our case
was to run a server daemon on each of the eight boxes. The daemon would
listen on a predefined port for client requests, and pass the request to
a local jselect program, which would return the results from the database
to the client via the same socket. This is shown below:
Monitoring Client ßà
Socketßà
Server Daemon ßà
jselect ßà
Database
The reason why I wrote jselect
instead of using dbaccess is that dbaccess makes the decisions on how to
format the data based on the width of the returned data set. So there is
usually some post-processing of data involved, which I wanted to avoid.
There were certain ESQL/C solutions freely available on the IIUG software
repository, but they were not options for me, since ESQL/C is not installed
on any of our machines.
Installation and Usage Notes
This
program has been written and tested using the following environment.
Java Development Kit 1.1.6
Informix JDBC driver 1.40.JC2
Informix Dynamic Server 7.30.UC3
Sun Solaris 5.5.1
In order to compile the code,
you would need to have the Java Development Kit and the Informix JDBC driver
installed. Also you would need to make sure that the java compiler (javac)
and the Java Virtual Machine (java) are accessible through your PATH environment
variable.
Finally, you need to make
sure that your CLASSPATH contains a reference to the Informix JDBC driver
(ifxjdbc.jar) and the directory where your jselect.class file will be installed.
For example, if you installed your JDBC driver in /usr/informix/jdbc and
you would be installing your jselect.class file in /home/informix/tools,
then your CLASSPATH should minimally contain the following:
/usr/informix/jdbc/lib/ifxjdbc.jar:/home/informix/tools
To
compile the jselect.java file, simply run the following command at the
prompt:
$ javac jselect.java
This
will create a jselect.class file in the current directory. Move it to the
installation directory referred to in your CLASSPATH.
Here is how you would call
the program.
Usage: java jselect [switch=value
...]
Switches:
| --host or -h |
Host String in the format
hostname:port/database@server, where hostname = machine name, port = TCP
port-# for instance, database = database name, and server = value of INFORMIXSERVER. |
| --auth or -a |
Authentication String in
the format username/password, where username = Unix login name and password
= password for the database user. |
| --style or -s |
Determines the output style.
Valid values are col(umnar), tab(ular) and pip(e-delimited). |
| --title or -t |
Determines if titles appear
in the output. Valid values are yes and no. |
| --query or -q |
SQL query to be executed.
The program does not do any syntax checks, but will return a -201 on invalid
SELECT syntax. |
| --file or -f |
Full path name of a file
that contains the rest of the switches in name=value format, just like
the command line arguments. |
You can use either the GNU-style
-- "long" options or the regular - "short" options or a combination of
the two. You can specify some or all of the switches on the command line
or in the file. In the file, however, you must use the long -- form to
specify arguments. Here is a sample file, called jselect.conf, for example:
-
--host=avalanche:1101/strax@avalanche_soc
-
--auth=sujit/xxx
-
--query=SELECT * FROM systables
WHERE tabid > 99
-
--title=yes
-
--style=tab
Here
is the command line to call jselect with the config file parameters
java
jselect --file=jselect.conf
Here
is an example command line call to jselect without the config file:
java jselect --host=avalanche:1101/strax@avalanche_soc\
--auth=sujit/xxx --title=yes
--style=tab \
--query="SELECT * FROM systables
WHERE tabid > 99"
Out of the switches, the
--style and --title are optional. If they are not specified, then the program
defaults them to --style = tab[ular] and --title = no. All other switches
are mandatory.
The Code
Here
is the code for the jselect program. The code is also available for download
in the IIUG software repository as a shar file.
/*
* jselect.java
*
* This provides a user-interface to an Informix database for
* executing SELECTs using the Informix JDBC driver interface.
*
* Author: Sujit Pal Date Written: May 15 1999
*
*/
import java.util.*;
import java.io.*;
import java.sql.*;
public class jselect
{
// Environment Variables
private static String envHost;
private static String envAuth;
private static String envTitle;
private static String envStyle;
private static String envQuery;
private static String envFile;
private static Properties configFile;
private static int argCount;
private static String hostName;
private static String portNumber;
private static String dbName;
private static String serverName;
private static String userName;
private static String passWord;
private static String sqlQuery;
private static Connection conn;
private static Statement stmt;
/*
* The main method calls the parseArgs() method to scan for
* command line args and the parseConfig() method to load up
* arguments from the command file. It then passes off the
* SQL to the select() method which processes the query on
* the database and returns the results.
*/
public static void main(String [] args)
{
// Read arguments off the command line
parseArgs(args);
parseConfig();
try
{
select(envQuery, envTitle, envStyle);
}
catch (SQLException sqle)
{
System.out.println(sqle.getErrorCode() + ": "
+ sqle.getMessage());
// sqle.printStackTrace();
}
} // main()
/*
* This method picks up the arguments specified on the
* command line.
*/
private static void parseArgs(String [] args)
{
int argCount;
for (argCount=0; argCount < args.length; argCount++)
{
if ((args[argCount].indexOf("--host=") == 0) ||
(args[argCount].indexOf("-h=") == 0))
{
envHost = args[argCount];
envHost = envHost.substring(envHost.indexOf("=") + 1,
envHost.length());
}
if ((args[argCount].indexOf("--auth=") == 0) ||
(args[argCount].indexOf("-a=") == 0))
{
envAuth = args[argCount];
envAuth = envAuth.substring(envAuth.indexOf("=") + 1,
envAuth.length());
}
if ((args[argCount].indexOf("--title=") == 0) ||
(args[argCount].indexOf("-t=") == 0))
{
envTitle = args[argCount];
envTitle = envTitle.substring(envTitle.indexOf("=") + 1,
envTitle.length());
}
if ((args[argCount].indexOf("--style=") == 0) ||
(args[argCount].indexOf("-s=") == 0))
{
envStyle = args[argCount];
envStyle = envStyle.substring(envStyle.indexOf("=") + 1,
envStyle.length());
}
if ((args[argCount].indexOf("--query=") == 0) ||
(args[argCount].indexOf("-q=") == 0))
{
envQuery = args[argCount];
envQuery = envQuery.substring(envQuery.indexOf("=") + 1,
envQuery.length());
}
if ((args[argCount].indexOf("--file=") == 0) ||
(args[argCount].indexOf("-f=") == 0))
{
envFile = args[argCount];
envFile = envFile.substring(envFile.indexOf("=") + 1,
envFile.length());
}
}
} // parseArgs()
/*
* This method reads arguments off the config file if specified
* with a --file= option.
*/
private static void parseConfig()
{
// Read arguments from config file, if they exist. Note
// that command line args will override the ones in the
// config file, so if a command line argument already
// exists, then do not read from the config file.
if (envFile != null)
{
try
{
configFile = new Properties();
configFile.load(new FileInputStream(envFile));
}
catch (FileNotFoundException fnfe)
{
System.out.println("File not found: " + envFile);
usage();
// fnfe.printStackTrace();
System.exit(1);
}
catch (IOException ioe)
{
System.out.println("IO Exception: " + envFile);
usage();
// ioe.printStackTrace();
System.exit(1);
}
if (envHost == null)
{
envHost = (String) configFile.get("--host");
}
if (envAuth == null)
{
envAuth = (String) configFile.get("--auth");
}
if (envTitle == null)
{
envTitle = (String) configFile.get("--title");
}
if (envStyle == null)
{
envStyle = (String) configFile.get("--style");
}
if (envQuery == null)
{
envQuery = (String) configFile.get("--query");
}
}
// Check if any arguments are still empty. If so, print
// usage and exit.
if ((envHost == null) || (envAuth == null) ||
(envQuery == null))
{
usage();
System.exit(1);
}
// Parse the arguments
hostName = envHost.substring(0, envHost.indexOf(":"));
envHost = envHost.substring(envHost.indexOf(":") + 1,
envHost.length());
portNumber = envHost.substring(0, envHost.indexOf("/"));
envHost = envHost.substring(envHost.indexOf("/") + 1,
envHost.length());
dbName = envHost.substring(0, envHost.indexOf("@"));
serverName = envHost.substring(envHost.indexOf("@") + 1,
envHost.length());
// Check if envHost parsed OK. If not print usage and exit.
if ((hostName == null) || (portNumber == null) ||
(dbName == null) || (serverName == null))
{
usage();
System.exit(1);
}
userName = envAuth.substring(0, envAuth.indexOf("/"));
passWord = envAuth.substring(envAuth.indexOf("/") + 1,
envAuth.length());
// Check if envAuth parsed OK. If not print usage and exit.
if ((userName == null) || (passWord == null))
{
usage();
System.exit(1);
}
if (envTitle == null)
{
envTitle = "no";
}
if (envStyle == null)
{
envStyle = "tab";
}
} // parseConfig()
/*
* The select() method opens the connection to the database
* and executes the SQL statement passed in, and returns the
* result to STDOUT.
*/
private static void select(String sqlQuery,
String envTitle, String envStyle) throws SQLException
{
String sqlResult = "";
String url = " ";
int cols = 0;
int pos = 0;
String colName, colValue;
int colHeadSize, colDispSize, colRealSize;
int pad = 0;
try
{
Class.forName("com.informix.jdbc.IfxDriver");
url = "jdbc:informix-sqli://" + hostName +
":" + portNumber + "/" + dbName + ":informixserver=" +
serverName + ";user=" + userName + ";password=" +
passWord;
// System.out.println(url);
conn = DriverManager.getConnection(url);
stmt = conn.createStatement();
}
catch (SQLException sqle)
{
throw sqle;
}
catch (Exception e)
{
System.out.println("Error: " + e.getMessage());
// e.printStackTrace();
}
try
{
if (envStyle.equals("tab"))
{
ResultSet rs = stmt.executeQuery(sqlQuery);
ResultSetMetaData rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
for (pos = 1; pos <= cols; pos++)
{
colName = rsmd.getColumnName(pos);
colHeadSize = rsmd.getColumnDisplaySize(pos);
colRealSize = colName.length();
colHeadSize = (colRealSize >= colHeadSize) ?
colRealSize : colHeadSize;
for (pad = colRealSize; pad <= colHeadSize; pad++)
{
colName += " ";
}
sqlResult += colName;
}
if (envTitle.equals("yes"))
{
sqlResult += "\n";
System.out.println(sqlResult);
sqlResult = "";
}
else
{
sqlResult = "";
}
while (rs.next())
{
for (pos = 1; pos <= cols; pos++)
{
colValue = rs.getString(pos);
if (colValue == null)
{
colValue = " ";
}
colHeadSize = rsmd.getColumnName(pos).length();
colDispSize = rsmd.getColumnDisplaySize(pos);
colRealSize = colValue.length();
colDispSize = (colHeadSize >= colDispSize) ?
colHeadSize : colDispSize;
colDispSize = (colRealSize >= colDispSize) ?
colRealSize : colDispSize;
for (pad = colRealSize; pad <= colDispSize; pad++)
{
colValue += " ";
}
sqlResult += colValue;
}
System.out.println(sqlResult);
sqlResult = "";
}
}
if (envStyle.equals("col"))
{
ResultSet rs = stmt.executeQuery(sqlQuery);
ResultSetMetaData rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
while (rs.next())
{
for (pos = 1; pos <= cols; pos++)
{
colName = rsmd.getColumnName(pos);
colHeadSize = colName.length();
for (pad = colHeadSize; pad <= 18; pad++)
{
colName += " ";
}
colValue = rs.getString(pos);
if (envTitle.equals("yes"))
{
sqlResult = colName + ":" + colValue;
}
else
{
sqlResult = colValue;
}
System.out.println(sqlResult);
}
System.out.println(" ");
}
}
if (envStyle.equals("pip"))
{
ResultSet rs = stmt.executeQuery(sqlQuery);
ResultSetMetaData rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
for (pos = 1; pos <= cols; pos++)
{
colName = rsmd.getColumnName(pos);
sqlResult += colName.trim() + "|";
}
if (envTitle.equals("yes"))
{
sqlResult += "\n";
System.out.println(sqlResult);
sqlResult = "";
}
else
{
sqlResult = "";
}
while (rs.next())
{
for (pos = 1; pos <= cols; pos++)
{
colValue = rs.getString(pos);
if (colValue == null)
{
colValue = "";
}
sqlResult += colValue.trim() + "|";
}
System.out.println(sqlResult);
sqlResult = "";
}
}
stmt.close();
conn.close();
}
catch (SQLException sqle)
{
throw sqle;
}
catch (Exception e)
{
System.out.println("Error: " + e.getMessage());
}
} // select()
private static void usage()
{
System.out.println("Usage: java jselect [switch=value ...]");
System.out.println("Switches:");
System.out.println("--host|-h:\tHost String in the format " +
"hostname:port/database@server,");
System.out.println("\t\twhere hostname = machine name, " +
"port = TCP port-# for instance,");
System.out.println("\t\tdatabase = database name, and " +
"server = value of INFORMIXSERVER");
System.out.println("--auth|-a: Authentication String in the " +
"format username/password,");
System.out.println("\t\twhere username = Linux login name " +
"and password = password");
System.out.println("\t\tfor the database user.");
System.out.println("--style|-s: Determines the output style. " +
"Valid values are col(umnar),");
System.out.println("\t\ttab(ular) and pip(e-delimited).");
System.out.println("--title|-t: Determines if titles appear " +
"in the output. Valid values");
System.out.println("\t\tare yes and no.");
System.out.println("--query|-q: SQL query to be executed. " +
"The program does not do any");
System.out.println("\t\tsyntax checks, but behaves strangely " +
"on invalid SELECT syntax");
System.out.println("--file|-f: Full path name of a file " +
"that contains the rest of the");
System.out.println("\t\tswitches in name=value format, " +
"just like the command");
System.out.println("\t\tline arguments.");
} // usage()
} // JSelect
Other
Uses of this program
As
mentioned above, this program is being used to monitor application events
that could be recorded in different databases on different instances and
on different machines. Another use of this program could be to run DBA
queries off the sysmaster database on a set of remote instances at regular
intervals and collect data in a centralized monitoring database and/or
alarm based on certain predefined criteria. Since I have attempted to make
this tool as general as I could, there could be many other uses it could
be put to.
About
the Author
Sujit
Pal has around 11 years experience in the Computer Industry. He has been
working with Informix since 1991, first as a 4GL and ESQL/C programmer,
then as a Database Administrator since 1993. He is also an Informix Certified
Online Dynamic Server Administrator. He works for Techna International
Corporation as a Database Consultant. His current client is Bank of America,
where he works with the Interactive Home Banking Project development team.
He can be reached via email at sujitpl@ibm.net.
Checking
Informix Ontape Backups with Archecker
by
Lester Knutsen
Archecker is a new utility
that ships with IDS 7.3 to provide a way for you to check an Informix backup
made with ontape and verify that the tape is usable for a restore. Archecker
is undocumented in 7.3 (the document was not done in time for the release)
but will be fully documented in 7.31. Without this utility, the only way
to verify a backup has been to do a full restore. Archecker allows you
to verify every backup, right after it has been made. You can even check
the backup on another system so it does not impact your production systems.
On a critical production system I suggest taking each backup tape to another
system and verifying that the backup was successful. This article is about
how to use Archecker.
The archecker is designed
to validate a level 0 archive with little impact on a production system.
It will ensure that all data required to restore a system exists on the
archive tapes in the correct format. It will detect pages that are missing
or unreadable from the tape and identify which tables are affected. It
can also verify data in similar fashion to the command "oncheck -cd." It
also has an option to write a dot on the screen after reading every 1GB
of data from the tape. This lets you know that the program is doing something.
As a rule, if it took two hours to make your ontape backup, it will take
archecker about 2 hours to verify the tape.
To use it you need to set
up a configuration file in $INFORMIXDIR/etc called ac_config.std. The following
is the one I use. AC_TAPEDEV and AC_TAPEBLOCK must match your onconfig.
Archeck Configuration File
#**************************************************************************
# Title: ac_config.std
# Description:
# Default ac_config.std for archecker archive utility
#
#**************************************************************************
AC_MSGPATH /tmp/ac_msg.log # archecker message log
AC_STORAGE /tmp # Directory used for temp storage
AC_VERBOSE 1 # 1 verbose messages 0 terse messages
AC_TAPEDEV /dev/rmt/0 # Must match TAPEDEV in onconfig
AC_TAPEBLOCK 64 # Must match TAPEBLK in onconfig
#**************************************************************************
Configuration file parameters
AC_STORAGE - This is the
name of the directory where archecker temporary files are kept. The amount
of space required will be determined by the number of chunks and the number
of tables. You will need a lot of free space in this filesystem. To estimate
I recommend having 1MB of free space for every 2GB of dbspace on your system.
If this directory is not set it will default to your current directory.
AC_MSGPATH - Location and
pathname of archecker's message log. All error and status messages will
be placed in this file.
AC_TAPEDEV - The name of
the tape device to be used for reading and checking the archive.
AC_TAPEBLOCK - The size of
the tape block in KB. It must match the blocksize from the ONCONFIG file
used for the archive. If it does not match you will get an error that will
indicate the correct blocksize to use.
Archecker command line options
-
archecker -b -D -d -R -F -v -s -t -T -V
-
-b Direct XBSA access
-
-D Delete old files from
previous run and exit
-
-d Delete old files from
previous run
-
-F Retrieve list of pages
off the archive
-
-P Read Performance info
only
-
-R Restart the checking
-
-s Print status message
to the screen
-
-t Read the tape directly
-
-T Restart at a specific
tape
-
-v Verbose mode, print dots
to the screen for every 25MB of data read
-
-V Display version
-
Running Archecker
To run archecker insert a
tape in your tape drive and type:
archecker -tdsv
These are the basic command
line options I use, and it tells archecker to read a tape, delete any old
files from a previous run, print a status message and dots to indicate
the progress. At the first prompt press <RETURN> (not a "1" like it
leads you to believe). Archecker creates a file /tmp/ac_msg.log with all
the information. One word of warning: you need enough free space for AC_STORAGE
as it copies parts of your tape to disk while it works.
Archeck Screen Display
-
informix@atlas>archecker
-tdsv
-
Informix
Dynamic Server Version 7.30.UC2
-
Program
Name: archecker
-
Version:
4.3
-
Released:
03/25/98 04:16
-
Compiled:
03/25/98 04:20 on SunOS 5.4 Generic_101945-27
-
-
AC_STORAGE
/tmp
-
AC_MSGPATH
/tmp/ac_msg.log
-
AC_VERBOSE
on
-
-
Please
put in tape number 1.
-
Type
1 <return> or 0 to end:
-
Archecker is an easy way to make sure your backups are working.
Lester
Knutsen
Advanced
DataTools Corporation
Phone:
703-256-0267
Web:
www.advancedatatools.com
Email:
lester@advancedatatools.com
Troubleshooting
and Maintaining Your Servers
by
Ron Flannery
Things to consider before
creating your Informix instance.
Proper planning can help
you make excellent longterm
decisions for the usage of disks, memory, CPU, and database configuration.
This article helps you make these decisions. The old adage says;:
"prevention is the best medicine." This is just as true in Informix server
administration. It might be easy to install Informix, bring it online,
and create some databases, but that's only the beginning. A key to long-term
success is creating an environment that is constantly being tuned for its
own needs. In this section, I'll discuss some of the ways to build for
the future of your databases.
Setting
the Initial Parameters
There
are many things to consider when creating your Informix instance. Properly
configuring certain parameters is key to long-term
success. It may seem like a lot of work now, but it can certainly be easier
than recreating your whole instance after it has been online for awhile.
Some of the most important parameters to estimate are:
-
Growth of your tables.
-
Insert, update, and delete
activity.
-
The most active tables.
-
Level of database update during
peak hours.
-
Number of users that will concurrently
access the data.
-
Types
of things the users will need from the databases, both short and long term.
-
The configuration of your operating
system and computer. The needs for this might change, depending on the
needs of your database.
You can use these estimates
to set the following crucial parameters in the instance configuration (onconfig
file):
-
Size and location of dbspaces,
including root (ROOTPATH)
-
Number of locks (LOCKS)
-
Location and size of the logical
and physical logs (LOGFILES, LOGSIZE, PHYSDBS, PHYSFILE)
-
Location and size of the temp
dbspaces (DBSPACETEMP)
-
Configuration of buffers and
LRUs and other shared memory parameters (BUFFERS, CKPTINTVL, CLEANERS,
LRU_MAX_DIRTY, LRU_MIN_DIRTY, LRUS)
-
Number of CPUS on your system
and how Informix will use them (AFF_NPROC, AFF_SPROC, MULTIPROCESSOR, NUMAIOVIPS,
NUMCPUVPS, SINGLE_CPU_VP)
-
Amount of memory on your system
and Informix instance (SHMADD, SHMTOTAL, SHMVIRTSIZE)
-
Your needs for DSS (data warehouse,
for example) applications (DS_MAX_QUERIES, DS_MAX_SCANS, DS_TOTAL_MEMORY)
Of course it's almost impossible
to set the perfect values for these parameters before you actually have
users on the system. In fact, the rest of this chapter will discuss ways
to monitor these and other parameters, changing them if necessary.
For now, please remember that the initial configuration can greatly reduce
the amount of work needed later. A proper initial setup will also make
the ongoing maintenance a process of improving rather than fixing.
Creating a new Informix instance
can involve a lot of steps that can be difficult to repeat. Sure, you can
take notes about what you did, but if you have to re-create the instance
for some reason, it can be easy to miss a step. Rather than using onmonitor
or manually typing each command, I suggest creating a shell script to create
the initial dbpaces and other parameters. For example, the following lines
will add a new dbspace with a size of 2 gigabytes and then add a chunk
to it:
Your script should include
all of the dbspaces and chunks that you think will be needed for
your system.
It is probably best to ensure
you are using the latest version of Informix for your hardware and operating
system. Each version improves on past versions, fixing bugs along the way.
You can check the current production information by visiting the Informix
Web site at www.informix.com.
Setting Up an Alarm Program
Informix allows you to provide
a program or shell script that can take certain actions in the case of
problems. If certain Informix or operating system errors occur, this program
is executed. The full pathname to this program is provided in the onconfig
file as the parameter ALARMPROGRAM. The program can allow you to do things
like page the Informix administrator or display a message on the system
console. The program is passed the parameters in the order that they are
summarized in Table 23.1.
Table
23.1Values Passed to the ALARMPROGRAM.
Summary - Description
Severity - The severity of the event, from 1-5. The values
are summarized: 1=small configuration changes; 2=informational message
(no error) about routine events; 3=attention--something has occurred that
requires attention but does not prevent use of system; 4=emergency--something
happened that could compromise the data or the instance; 5=fatal error
that causes the database server to go offline.
Class ID - A numerical representation of the error that occurred. The
Informix-Dynamic Server Administrator’s Guide summarizes a list of over
20 different error numbers and descriptions. The description of the associated
error message is actually given in the next parameter, “Class Message.”
Class Message - The text of the message that represents Class ID. An
example is “Logical Logs are full - Backup is needed.”
Specific Message - More detail on the error that occurred. This is likely
the same message that is written to the Informix log file.
Extra Info Path - If appropriate, pathname to a file that contains more
information about the error.
For
example, if you want to send an e-mail to the Informix administrator for
error levels of at east 3, and send a message to her pager for levels 4
and 5, you could create a shell script as in Listing 26.1 and set ALARMPROGRAM
to point to it.
Listing
26.1 -- A sample alarm program.
-
-
#/bin/sh
-
SEVERITY=$1
# first argument to this shell script
-
CLASS_ID=$2
-
CLASS_MSG=$3
-
SPECIFIC_MSG=$4
-
INFO_PATH=$5
-
if
[ $SEVERITY -ge 4 ] # severity greater than or equal to 4 - a bad thing
-
then
-
SEV_MSG="******
THIS ERROR REQUIRES IMMEDIATE ATTENTION!! *******"
-
else
-
SEV_MSG="WARNING:"
-
fi
-
#
place datetime and error message information in the variable MY_MSG
-
echo
"
-
INFORMIX
MESSAGE AT `date`:
-
$SEV_MSG
-
SEVERITY
LEVEL: $SEVERITY
-
CLASS
ID: $CLASS_ID CLASS MSG: $CLASS_MSG
-
SPECIFIC
MSG: $SPECIFIC_MSG
-
EXTRA
INFO: $INFO_PATH" > /tmp/$$.log # write message to a temp file
-
if
[ $SEVERITY -ge 3 ] # severity greater than or equal to 3
-
then
-
mail
dba_list < /tmp/$$.log # send msg from file to email alias dba_list
-
if
[ $SEVERITY -ge 4 ] # severity greater than or equal to 4
-
then
-
dba_pager.sh
5551212911 # run shell script to dial dba pager with return phone# + 911
-
fi
-
fi
-
#
write to a system log file
-
cat
/tmp/$$.log >> /usr/informix/logs/err_msg.log
-
/bin/rm
/tmp/$$.log # clean up
When
a condition occurs to signal an alarm, this shell script will be called,
triggering the appropriate events. Setting up this script will notify people
immediately, helping correct the problems and avoid destructive downtime.
Suppose this script is called ifmxalarm.scr.
The entry in the onconfig
file that tells Informix to use this script is as follows:
ALARMPROGRAM
ifmxalarm.scr
