January 1994 Newsletter

Volume 4, No. 1

Highlights of This Issue

INFORMIX-SE Database Administration Tips By Madhu Reddy

Reading Informix SE audit files by Lester Knutsen

Anonymous FTP Archive for Informix Files by Walt Hultgren

Informix Public Domain Tools by Dave Snyder

Next Meeting - February 9th at 5:00 pm in Rockville, MD

INFORMIX-SE Database Administration Tips

Solution A.

To set the database into the no-log mode - Update the column 'tabtype' of the row "syslog" of the table 'systables' into "T" (update systables set tabtype = "T" where tabname = "syslog").

To set the database back to the log mode - Update the column 'tabtype' of the row "syslog" of the table 'systables' into "L" (Update systables set tabtype = "L" where tabname = "syslog").

Solution B.

To set the database into the no-log mode - delete the row "syslog" from the table 'systables'. (delete from systables where tabname = "syslog"). The database can be set to the log mode by executing the sql command "start database".

Problem 2. When the log space is full, INFORMIX-SE creates a temporary log file. One of our ODBAs assumed that it was a temporary file and deleted the file. The result was that we were not able to open the database. What to do?

Solution: Run the UNIX shell command that displays the file in ASCII format for the systables file "systables.dat" of the database and grep for the "syslog" (strings systables.dat | grep syslog) then it displays the line - syslog "owner" "file name with path". You should be able to open the database by creating an empty file "file name" using the unix command touch or cp.

Problem 3. One day one of our ODBAs created a table 'dummy'. Instead of dropping the table, he just deleted corresponding dat and idx files using the UNIX command "rm". Since the table is not deleted from the system catalogs, INFORMIX-SE gives an error when you try to run the sql command "update statistics". How to fix this?

Solution: Get the dbschema of the table name "dummy" into an sql file, modify the table name into dummy1 (whatever the name you like) create the new table dummy1 (assume the created files for the table are dummy1__102.dat and dummy1__102.idx), select the dirpath of the table "dummy" from systables (assume it is dummy___101), copy dummy1__102.dat to dummy___101 and dummy1__102.idx into dummy___101. Now you should be able to run the command "update statistics" and be able to drop the dummy table.

Alternatively you can achieve the same by deleting the information related to the table 'dummy' from the system catalogs. But it is too risky.

Problem 4. With Government contracts it is a tradition to change the contractors. A Database may be created by the contractor's name to maintain the contracting information. When the contract is awarded to a new contractor, they want to change the database name. How to rename the database ?

Solution: Using unix command (mv) rename the database directory.

Problem 5. It is a requirement in the development environment to create many copies of the database for testing. You can create a database using sql files and populate the database, but it takes more time than a direct file copy. How to make a copy of the existing database?

Solution: Create a directory with the "database".dbs name, copy all the files from the existing database into the newly created "database".dbs directory and modify the "dirpath" of the systables to look for the current path (only for the tables created with the path name). If the existing database is in log mode, then either drop the "syslog" tuple from systable and start the database with a new log file, or create the empty log file and update the "dirpath" of the "syslog" tuple of the table systables.

Problem 6. Before I moved to one of the Government contracts, one of the ODBAs was doing database maintenance. It took 18 hrs to delete 1300 tuples from a table. He needed to delete another 100,000 tuples from the same table and estimate how much time it takes to complete the job. The problem here is every month he was deleting more than 75% of the records and adding new records to the table. Because of this the table space was never reclaimed. What is the best thing to do?

Solution: Unload the required data, drop and recreate the table, and load the data into the table. This process reclaims the unused space and improves the performance. However, using this procedure, the maintenance took only 3 hours.

Problem 7. The system was running fine. Suddenly the system started running too slow. What to do?

Solution: In general, this happens after a lot of activity on the database (either by adding many records or deleting many records). So that system catalogs may not be updated. In this case, run the command "update statistics" against the database.

Problem 8. In some versions of INFORMIX, if the table is owned by another user other than informix, even though informix has the DBA permission, he may not be able to access the table. Assume that the table owner left the company. How to get access to the table?

Solution: Modify the owner of the table files and catalog files into informix. Then it allows you to have your access to the table.

Problem 9. If you are supporting similar applications installed in many locations, then there may be a requirement that some of the tables are to be populated with the same data. What is the best way to do this?

Solution: Create the database table in one location and populate with data. Copy the corresponding tables "dat" and "idx" files into another database location area and replace old "dat" and "idx" files with the new database table files. Don't forget to run the command "update statistics".

Problem 10. If you run into an error and INFORMIX displays C-ISAM error number as zero. How to determine the problem?.

Solution: It happens when there is a problem at the system level. The problem may be in general among of the following. So do the following checks. Check for file permissions for both log and table files, check and increase the ulimit parameters if necessary, and run the fsck on file system and make sure that the file system is fine.

My thanks to Linda Funn for reviewing this document.

American Computer Technology, Inc., Compiling a guide on INFORMIX DBA Tips and can be ordered for $99. This guide provides many tips for DBA on INFORMIX-OnLine and INFORMIX-SE.

Any questions are suggestions can be redirected to Madhu Reddy, American Computer Technology, Inc., 10816 Estate CT, Fairfax, VA 22030, USA, Phone (703) 385-3273.

Reading Informix SE audit files

Twice in the last month I have been asked how to read Informix SE audit files. Informix SE has a feature (this is not available in Informix Online) to create an audit trail of all adds, deletes and updates to a table. This can be used to trace which user is changing critical data. The procedures to create an audit file are simple and well documented. However, it is not well documented on how to read or use the audit file. The SQL syntax to create an audit trail is 'create audit for table_name in "pathname'". The full pathname is required for this command.

An Informix audit file is structured the same as the original data file (.dat file) with a header. One way to access an audit file is to convert it into an Informix database table. Then you can perform searches on the changes made to your data. The following steps will create an Informix database table out of an audit file. As an example, I will use the stores database and the orders table that come with Informix products. I recommend that you try this in a test environment first. To create an audit file on the orders table, type the following SQL command in dbaccess or isql.

create audit for orders in "/usr/lester/demo/orders.audit"

Every change to the orders table will be captured in this file. The next step is to create a way of loading this into a database and using the data.

1. First you need to create an SQL schema for the new audit table. The schema will be based on the table you are auditing with an additional five field header. You can use dbschema to do this by typing:

dbschema -s stores -t orders a_orders.sql

2. Edit the a_orders.sql script and add the additional fields for the audit header. The audit file includes the following five header fields:

a_type		char(2)	Type of record where aa = added, dd =
deleted, rr = before update image, ww =
after update image.
a_time		integer	Integer internal time value.
a_process_id	smallint	Process ID that changed the record.
a_usr_id		smallint	User ID that changed the record.
a_rowid		integer	Original rowid.

You will also need to change the table name in the SQL script produced by dbschema to the name you want to call the audit table. I like to use the old table name with an "a_" prefix. The Index statements will also need to be changed. There must be one index on this table for the next step with bcheck to work. The old unique indexes should be removed because in the audit file the same record could appear for multiple changes. Change the index statements to use the new table you are creating. The following example is the script for the orders table:

create table a_orders   (
	a_type		char(2),
	a_time		integer,
	a_process_id	smallint,
	a_usr_id		smallint,
	a_rowid		integer,
	order_num 	serial not null,
   	order_date 	date,
    	customer_num 	integer,
    	ship_instruct 	char(40),
    	backlog 		char(1),
    	po_num 		char(10),
    	ship_date 	date,
    	ship_weight 	decimal(8,2),
    	ship_charge 	money(6,2),
    	paid_date 	date  );
create index a_order_num_idx on a_orders ( order_num );

3. Create the table with the new name. This should produce an empty table ready to hold your audit file data.

4. Copy the audit file to replace the new table data file. This step will destroy any data in the a_orders.dat table so proceed with caution. Look up the pathname of the data file created for this table. One way is to perform the following select:

select dirpath from systables where tabname = "a_orders"

On my system dirpath was "a_order007". Change to the directory where the database files are located and copy the audit file to replace the a_order007.dat file.

cd stores.dbs
cp /usr/lester/demo/orders.audit a_order007.dat

5. After overwriting the ".dat" file the data and the index will be out of sync. Use bcheck, the Informix index repair tool to fix the index file. Type the following command.

bcheck a_order007
	

select a_type, a_time, a_process_id, a_usr_id, a_rowid, order_num 
from a_orders

a_type a_time    a_process_id  a_usr_id     a_rowid   order_num
aa     759109477     823        200          16        1016
rr     759109502     823        200          15        1015
ww     759109502     823        200          15        1015
dd     759109516     823        200          16        1016

Lester Knutsen

Advanced DataTools Corporation

4510 Maxfield Drive, Annandale, VA 22003

703-256-0267 or email: lester@access.digex.net

Public Domain Software for Informix Users

I had the privilege of pulling together the programs for our Forum 93 diskette. This provided an opportunity to explore and discover sources of public domain software available for Informix users. In case you missed the Forum, the diskette has been uploaded to our BBS and is available for you to download. A BBS Users Guide is included in this newsletter.

There were three main sources that contributed to our diskette besides our own user group. Emory University maintains an FTP archive of Informix Files. The next article is a description of the archive by Walt Hultgren. The Colorado Informix User Group has pulled together a diskette and provided a directory of files for our diskette. And, David Snyder provided four programs that he has developed. One of my favorite programs has become db4glgen, a 4GL source code generator. Include in this newsletter is a description from David of the programs he has developed and how to get them.

Anonymous FTP Archive for Informix Files

An archive of Informix-related files and past articles from the Informix mailing list and the Usenet newsgroup comp.databases.informix is available for anonymous FTP from mathcs.emory.edu (IP 128.140.2.1). Note that this is not the same system as the home of the Informix mailing list.

The top level of the archive is the directory /pub/informix, which contains the following sub-directories:

• das13 - A duplicate of selected portions of the archive maintained on das13.snide.com.

• doc - General documentation files, book lists, events calendars, etc.

• news - Past articles from the Informix mailing list and the Usenet newsgroup comp.databases.informix. Many of these contain source code *not* found in the "pub" or "ugroups" directories.

• pub - Sample source code, utilities, and other files.

• published - Files containing examples, exercise answers, etc., from books and other publications.

• ugroups - Source files, newsletter articles, and other files in collections maintained by various Informix user groups.

If you don't have access to FTP, you can still retrieve files from the archive via e-mail using one of the sites on the Internet that provide mail servers for use by anyone who can't FTP. Such a server acts as an intermediary that takes your request by e-mail, gets the files you want via FTP from the site that you specify, then e-mails those files back to you.

One of the best known FTP mail servers is based at the DEC Western Research Lab in Palo Alto, CA, USA. For more information, send a message consisting of the word "help" to "ftpmail@decwrl.dec.com". Then, using those instructions, GET the files "pub/informix/README" and "pub/informix/ls-lR" as a start.

If you have something that you think might benefit other Informix users, please consider contributing it. The archive has grown to its current size strictly through the generosity of others, and hopefully it will continue to expand. If you have any questions or comments concerning the archive, let me know.

Walt Hultgren Internet: walt@rmy.emory.edu(IP 128.140.8.1)

Emory University UUCP:{...,gatech,rutgers,uunet}!emory!rmy!walt

954 Gatewood Road, NE BITNET: walt@EMORY

Atlanta, GA 30329 USA Voice: +1 404 727 0648

Informix Public Domain Tools

This is a monthly posting from Dave Snyder, keeper of db4glgen. Although this is probably old information for alot of you, I find that about once a month or so, I'm asked, "What is db4glgen?"... "Where can I get dbinfo?" Well here are descriptions of all the Informix tools that I've written and how to get them. Please send all questions and comments about this posting or my Informix tools to dave@das13.snide.com (das13!dave).

• db4glgen - This program is the PD Informix 4GL code generator hacked together by Dave Snyder.

• dbbeauty - This program will capitalize all Informix key words in an Informix-4GL program. Comments and keywords inside double quotes will not be changed.

• dberror - This program prints the error message for an Informix-SQL error code. It also handles ALL-II and SystemV error codes.

• dbform - This package generates "quick & dirty" data forms. It requires that Informix-SQL be installed on your machine.

• dbinfo - This program provides information about an Informix-SQL table. It formats its output like the SQL command "info".

• dblist - This program dumps the contents of an Informix-SQL table to stdout. It has the ability to select an index to sort by and also provides the ability to start at a specific place in the file.

• dbloader & dbunloader - This program generates SQL statements to load "unloaded" Informix-SQL tables into a database. When compiled, dbloader should be linked to dbunloader. The dbunloader program generates SQL statements to unload Informix-SQL tables.

• dblookup - This package generates 4GL code and 4GL forms for pop-up style lookup windows.

• dbports.sh - This archive contains two shell scripts... dbexport and dbimport. If you have Informix 4.00 products then you don't need these scripts. DBEXPORT will create a database schema, unload all tables in your database, and create a script to re-load the unloaded tables. DBIMPORT will create a database with tables from the schema and load all the tables. These scripts require dbschema (from Informix) and dbloader/dbunloader (from archive-server).

• dbrepair - This program will update the "dirpath" column of the Informix-SQL table "systables" from an absolute path name to a relative one. This can only be used if your database engine is Informix-SE.

• dbreserved - This program will scan an Informix-SQL table and print all reserved words to stdout. There are currently 400+ words that are checked.

• dbsyntax - This program checks the syntax of SQL statements without actually executing them. If a file is not specified on the command line, stdin is used instead.

• vie - Vie will allow you to view and correct an Informix-4GL source/form error file using "vi".

The above programs are available from the machines "das13" and "mathcs". You can reach "das13" through the archive-server or ANONUUCP (ANONFTP is available through "mathcs.emory.edu"), the info you need follows...

ANONFTP:

If you've never used anonymous ftp, here is a quick primer, in the form of a sample session, with comments in {}. Lines with a "<<<" at the end are where you type something.

% ftp mathcs.emory.edu {IP Address= 128.140.2.1} <<<
Connected to mathcs.emory.edu.
220 emory FTP server (SunOS 4.1) ready.
Name (mathcs.emory.edu:dave): anonymous	<<<
331 Guest login ok, send ident as password.
Password: dave@snide.com {use your name and host here}<<<
230 Guest login ok, access restrictions apply.
ftp> cd pub/informix				<<<
250 CWD command successful.
ftp> get ls-lR					<<<
200 PORT command successful.
226 ASCII Transfer complete.
ftp> cd das13					<<<
250 CWD command successful.
ftp> get Index					<<<
200 PORT command successful.
226 ASCII Transfer complete.
        .
        .       {repeat this step for each file you want}
        .
ftp> quit					<<<
221 Goodbye.
%

ANONUUCP:

Create one or more of the following entries in your Systems (or L.sys) file:

das13 Any;1 FAST 9600 1-2154614007 "" \r\c in:-\r-in: anonuucp
das13 Any;1 ACU 2400 1-2154614007 "" \r\d\r\c in:-\r-in: anonuucp
das13 Any;1 ACU 1200 1-2154614007 "" \r\d\r\d\r\c in:-\r-in:
anonuucp

This is a call to Philadelphia, PA USA. If you've never used anonymous uucp, try this for your first command...

uucp das13!~/ls-lR /usr/spool/uucppublic

ARCHIVE-SERVER:

To access the archive-server, send mail to: archive-server@das13.snide.com In the Subject: put the word "help" (w/o the quotes). Leave the rest of your mail blank since it'll be ignored anyway. You will receive mail back explaining in detail what the archive-server is and how to use it.

David Snyder @ Snide Computer Services - Folcroft, PA

UUCP: ..!uunet!das13!dave INTERNET: dave.snyder@snide.com

The User Group BBS

The User Group BBS is located on the Internal Revenue Service public bulletin board system. The board is available 24 hours, however, the BBS administrator requests that Non-IRS users utilize the 9600 baud line after 5:00 pm and before 7:00 am weekdays. To access the WAIUG sub-board, log on to the system and at the main menu enter "J WAIUG". The disk from the forum, past newsletters, and several other programs are available for downloading. The following is an extract from the BBS Users Guide.

WHAT IS NEEDED TO CALL THE BULLETIN BOARD SYSTEM?

To call the BBS, you will need a computer (almost any computer will do), communications software like Procomm Plus, a modem attached to both a phone line and your computer, and the phone number of the BBS. The BBS number is 202-219-9977 for 2400 baud modems, 219-9991 for a V.32 9600 baud modem, or 219-9995 for a Telebit Trailblazer (19,200). Check to see that your communciations software is set for the following parameters: Baud Rate:1200, 2400, 9600, or 19,200 , Parity None, Data bits 8, and Stop bits 1.

CALLING THE BBS FOR THE FIRST TIME

Now you are ready to dial the number of the BXR INFO CORNER. When you are connected to the BBS, you will see an identification screen. Read the screen and then press the ENTER key or the <y> to go on to the next screen. You will be asked to answer the following questions. Always press the ENTER key after choosing a menu option or entering information so that your answer will be sent to the computer.

"What is your first name?"

"What is you last name?"

"What is your CITY and STATE?" Type in your city and state.

"<Change name/address>, <Disconnect (don't register)>, <Register>? If you made a mistake in entering either your name or address, choose <c> to change it. Otherwise, choose <r> to register.

"Enter PASSWORD you'll use to log on again? Type in a password that you will remember."

"Re-enter PASSWORD for verification." Type in the same password again.

"Can your terminal display lower case?" Answer yes.

"Graphics wanted: <N>one, <A>scii, <C>olor, <H>elp?

Choosing the <h> for help, here and at other places where the option is available, will give you help with specific functions available on the BBS. Continue answering the questions. The first time on the BBS you will also have to answer the newusers questionnaire.

The information you just entered will be saved so that you need only enter your name and password on subsequent calls. The following information deals with using the features of the BBS.

READING THE BULLETINS

Next you will see a list of bulletins available for reading on- line. Enter the number of the bulletin you wish to read first. If you wish to read another bulletin, enter the number of that bulletin at the prompt. When you have finished reading the bulletins, press the ENTER key to continue. The main menu will appear.

JOINING A CONFERENCE OR SUB-BOARD

A conference is simply a section of the BBS reserved for messages about a particular subject. There are a number of conferences available on the BBS to help keep messages organized. Several sub-boards are also available. Sub-boards contain both a message area and a files area. To join the WAIUG sub-board enter J WAIUG.

If you already know the name of the conference or sub-board you wish to join, type J NAME at the main menu prompt. If you do not know the name of the conference or sub-board, type <j> to see a listing of all the conferences and sub-boards available. To join another conference or sub-board, type <j> followed by a semicolon and the conference name. To return to the main menu level, type MAIN and press the ENTER key.

READING AND ENTERING MESSAGES

Choose <r> from the main menu to read messages. You will be prompted to enter the number of the message you wish to read. If you enter a number followed by a <+>, you will be able to read the messages starting with the number you indicated and going in ascending order. If you enter a number followed by a <->, you will be able to read messages in descending order.

Choose <e> from the main menu to enter a message of your own. You are prompted for the name of the person to whom you want to send the message and the message title. If you are sending a message to an individual instead of all users, that individual must be a registered user or the SYSOP, system operator of the BBS. Then you are asked what kind of security you want for your message. Choose the <r> for receiver if you wish only you and the person to whom it is addressed to be able to read it. Remember that the sysop can read all messages. Choose <u> if you wish all users on the system to be able to read your message. You can then type in your message. Press the ENTER key twice when you have finished. You will see a menu from which you choose <s> to save the message.

LISTING FILES AVAILABLE FOR DOWNLOADING

To upload and download files, choose <f> from the main menu. A new menu appears. If you choose <l>, you will see a list of the main categories of files available for downloading to your system. If you wish to see the actual names of the files in a particular category, type <l;category#>, where "category#" is the number of the category whose contents you wish to see.

DOWNLOADING A FILE FROM THE BBS

When you know the name of the file you wish to download, choose <d> from the files menu. You will be prompted for the file name. Enter the complete file name including the period (.) and the three letter extension if there is one. The BBS will tell you the size of the file and the time required to download it. Then it will pause while you tell your communications program to receive the file. If you are using Procomm Plus, press the PgDn key to get Procomm Plus attention and enter the filename where "filename" is the name you wish to call the file on your system. Choose the protocol type and then press the ENTER key and the transfer will begin.

TERMINATING THE CALL

To end a session on the BBS, choose <g>, for goodbye, from the menu.