Washington Area Informix Users Group

Home

About Us

Upcoming Events

Current Newsletter

Newsletter Archive

Presentations

Become a Member

Sponsors

Links

July 1999 Newsletter

Volume 9, No. 3


Highlights of This Issue

WAIUG Meeting Wednesday June 16,1999 by Peter Wages

Mid-Atlantic PeopleSoft / Informix (PeopleMix) Special Interest Group (SIG) By Nadia Skiscim

Jselect - A Java processor for SQL SELECT statements by Sujit Pal

Checking Informix Ontape Backups with Archecker by Lester Knutsen

Troubleshooting and Maintaining Your Servers by Ron Flannery

Next Meeting Agenda - September 15, 1999

  • News from the Informix World Wide User Conference - Solutions Portal 99
  • Informix i.Reach - Web based Document Management

Informix i.Reach is designed to enable departments, divisions, and entire enterprises to build a Web site for the centralized management of large collections of documents through a combination of Informix products and the expertise of i.Informix Consulting Services. Its simple Web browser interface allows anybody within an organization to easily contribute documents to an Intranet, extranet and internet Web site, and keep the content up-to-date. Informix i.Reach increases the value of information, increases a customer's ROI, and provides a competitive advantage.

Date and Time: September 15, 1999, 9:00 a.m. to 12:00 noon

Location: Informix Software Corporation
8065 Leesburg Pike, Suite 600, Vienna, VA 22182

The meeting is open to everyone. Please RSVP to 703-256-0267, so we can keep a count.


A Two Day User Group Technical Conference
Solutions for Informix Users - Forum 2000

The Washington Area Informix User Group would like to invite you to attend our fifth technical conference and forum. This will be an exciting event that includes technical presentations, practical training sessions, exhibits, demos, and a chance to meet and network with other Informix database developers, programmers, DBA's, and users.

Location: Fairview Park Marriott, 3111 Fairview Park Drive, Falls Church VA 

Date: Friday and Saturday, February 10/11, 2000, 8:00 am to 5:00pm

At the last forum we had over 230 participants, 16 speakers and 14 exhibitors. Participants said they learned more practical information in the forum than in any other event. Invitations for this forum are being sent to over 3,000 Informix users.

Planned Session Topics:

Data Warehousing Database Performance Tuning

Database Security and Administration Optimizing SQL Programming New Options for 4GL Web Database Development

Watch our web site for more details!

Newsletter Sponsorship

The user group has been supported by many companies over the years. We would like to thank the following companies for sponsoring this issue:

  • Advanced DataTools Corporation
  • Business Objects
  • FuGEN Technologies
  • NFL Player Benefits

User Group Activities

Quarterly Meetings: The Washington Area Informix User Group (WAIUG) holds quarterly meetings alternating between sites in the District, Maryland, and Virginia. The focus of the meetings is to share information about new products, present solutions to problems, and provide technical tips to Informix programmers, system designers and database administrators. We feature guest speakers on topics of interest to Informix users. Meetings usually last from 9 am to 12 noon and are open to all Informix users. Please call 703-256-0267, or check our web page for the current schedule.

Quarterly Newsletter: All members receive a quarterly newsletter filled with articles on how to get the most from your Informix database and products. Articles have included tips on optimizing your database design, 4GL programming techniques, data warehousing, and DBA monitoring tools. Several authors from our newsletter have been selected to be published in the Informix Tech Notes and the Informix Systems Journal. Our web site contains an archive of all the newsletters since 1993.

One Day Forums and Training Days: The WAIUG has held four one day Technical Forums. The past Forums have averaged over 200 participants, with excellent speakers and exhibitors. Members report this is the most-anticipated and most practical user group event of the year. The forums have included technical sessions on Optimizing INFORMIX-4GL, INFORMIX-OnLine DSA Tuning, and INFORMIX-OnLine 8.0 New Features. The Forum also features a mini-exposition hall with exhibitors of products that work with Informix.  This is a great oppurtinuity to network with other database developers, administrators, and users, and learn the newest technology to get the most from your database systems. 

Web Page - www.iiug.org/~waiug/: In August 1995, we launched a Web Page for the Washington Area Informix User Group. The purpose of this page is to be able to provide the latest information to our members and the public on user group activities and tips on using Informix software. We converted all our newsletters from the past three years to web documents and they are available for on-line viewing. This is the place to find the latest news about our meetings, future plans, links to other Informix-related material and catch up on back issues of our newsletter. We have a link to the Informix FAQ, and will be adding links to other Informix resources on the net. 

International Informix User Group: The WAIUG has been an active participant in the formation of the International Informix User Group (IIUG). All local members are automatically members of the international organization. The mission of IIUG is to support Informix users worldwide. The IIUG maintains a web site of information and technical tips for Informix users, and an ftp site of public domain software that works with Informix databases. For more information on the IIUG, visit the web page at http://www.iiug.org.






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 
 

Performing Routine Informix Tasks

There are several things that an administrator can do to monitor the performance of database servers. Regularly checking your databases and servers can help prevent performance bottlenecks and more serious problems. This section outlines some tasks that can be performed on a regular basis. The sidebar by Clem Akins, Informix Technical Support Engineer, provides some basic tips on the process involved with tuning a system.
 

How to tune your system.

It is important to use automated tools to provide a numerical history of your system. I was at a customer site who got a call from the IS manager. The manager swore that the tuning changes we made two weeks before had cut the speed of his application in half. When she provided a graph of the application speeds, the daily graph showed an almost flat trend--no performance loss at all. She did have a bad day that ran at double the normal time for a little while, but even that was not as low as it had been before our changes.

The moral of the story is to use science, not gut intuition, to tune your system. Gather data via computer programs, graph your statistics, track them in good times and bad, and map the changes to their resulting measurable effects on the system.
 

Using the Banner Line

A quick way to check on the status of your Informix instance is to run the command onstat-. This displays a simple banner line that indicates current status. The banner line is displayed on many onstat commands. Here is an example of this output of onstat-:
 

INFORMIX-OnLine Version 7.30.UC1 -- On-Line -- Up 2 days 13:58:15 -- 10336 Kbytes

The information includes:

  • Informix version: 7.23.UC1
  • Status of the instance: On-Line
  • Length of time the instance has been online: Up 2 days 13:58:15
  • Amount of memory being used by the instance: 10336 Kbytes

  •  
There can be one more line in the display of onstat -. It will indicate if Informix is "blocked" because of a checkpoint, long transaction, media failure, and other reasons. The above line indicates a healthy server.

Ron Flannery has been an Informix user for over 10 years. He has worked as a DBA, developer, Informix administrator, and project manager. He is President and co-founder of the Michigan Informix User Group and is a board member of the International Informix User Group. He is writing an Informix Press book called Informix Handbook. The book is a comprehensive reference on Informix SQL, administration, Web, and application development; details can be found at www.informixhandbook.com. Ron can be reached at rflanner@speedlink.net or 248-887-8470x40.
 
 
 


This newsletter is published by the Washington Area Informix User Group
Lester Knutsen, President/Editor
Washington Area Informix User Group
4216 Evergreen Lane, Suite 136, Annandale, VA 22003
Phone: 703-256-0267

 

 

lester@advancedatatools.com