Alarmprogram

This is a Korn shell script that can be called from the engine as part of the online monitoring (ALARMPROGRAM in onconfig). The following files need to configured within the ALARMDIR directory.

.users	Who to email
.Xnotify	Which screens to display the messages to. The file has the following format
	Display_ip OnOff MachineName StartTime EndTime 10.254.3.13:0 ON ProdServ 8 19
.machines	Which machines are monitored
.alarmenv	Standard environment

Big Brother

This script interface to the Big Brother system monitoring software. The scipt provides very basic dbspace and log usage monitoring and was originally written by Steve Holdoway

Other Big Brother scripts can be found at Deadcat.org

4GL Generator

This page details how to the standard Oninit 4GL generator works and how to write templates that can be used by the program. Currently the program is supplied with two templates, one which has no external dependencies, and the other requires the standard Oninit libraries

Currently the scripts do not have an installation scripts although this should be rectified in the future.

Assumptions and Requirements

ESQL development environment is required to compile this program.

The form required by the program is not automatically generated. If the form does not contain all the columns in the table then the generated code will need to be edited to remove these columns.

Installation

The program is proved as a tar archive, once the files have been extracted the program must be complied

c4gl builder.ec convert.c -o build4gl

If the menumarkup template is required the Oninit standard library is required, this is built using the buildlib command. The resulting library, libwfs.a, should then be placed in a directory where it will be used by the compiler.

Creating Templates

The easiest way to create a template is to write a 4GL program, and then remove the database, table and column dependencies and replace then with one the pre-defined variables (| delimited in the template) listed below. The build4gl program will use the new template if the environment variable BUILD4GL is set to the template file and exported.

The following definitions are used by builder.ec

DATABASE	This is the database as specified on the command line
TABLENAME	The table as specified on the command line
QUOTED_TABLENAME	The table as specified on the command line but returned as a " (double quote) delimited string
RECORD_DEFINITION_LISTING	Produces a listing of every column and the column types for the passed table. In the case of SERIAL columns the type is reported as INTEGER
RECORD_LISTING	Produces a listing of the record used to hold the values.
RECORD_LISTING_NO_SERIAL	As above but excludes any column defined as SERIAL
DISPLAY_FIELD_LISTING	Produces a DISPLAY BY NAME listing for every column in the table e.g. DISPLAY BY NAME m_current.<colname>
COLUMN_LISTING	Produces a listing of every column in the tables
QUOTED_COLUMN_LISTING	As above but each column is " (double quoted) delimited
COLUMN_LISTING_NO_SERIAL	As the COLUMN_LISTING but excludes column defined as SERIAL
BEFORE_FIELD	Produces the following lines for each column on the table BEFORE FIELD <colname> DISPLAY BY NAME m_current.<colname>
BEFORE_FIELD_NO_SERIAL	As BEFORE_FIELD but excludes the columns that are defined as SERIAL
AFTER_FIELD	Produces the following lines for each column on the table AFTER FIELD <colname> LET m_current.<colname>=get_fldbuf(<colname>) DISPLAY BY NAME m_current.<colname>
AFTER_FIELD_NO_SERIAL	As AFTER_FIELD but excludes the columns that are defined as SERIAL
REPORT_LISTING	Produces a report line for each column in the following format PRINT COLUMN 5," <colname> [",l_report_rec.<colname>,"]"

Library Code

lib_error

Function: err_msg(l_vpos, l_hpos, l_err_str)

Displays an error message in a window positioned at l_vpos, l_hpos

lib_fatal

Function: err_fatal(l_err_str)

Display fatal error message in the centre of the screen and exits program

lib_flash

Function: flash(l_flash_stat, l_msg_str)

If l_flash_stat is set TRUE the function flash displays a flashing message in a window in the middle of the screen. This window will remain on the screen until another call is made with l_flash_stat set to FALSE, the l_msg_str is irrelevant

lib_keys

Function: setup_keys()

Define all the standard keys such as ACCEPT and configures the default screen setting such MESSAGE LINE.

lib_quit

Function: quit()

Opens a window in the centre of the screen and prompts whether to exit the program or not.

lib_setup

Function: setup_screen(l_title,l_company)

Displays a window across the top of the screen containing l_company (left justified) and the program title (right justified) in the

lib_stat

Function: if_worked(l_status, l_processed, l_target, l_work_stat)

Examines sqlca record and status to check if the correct number of rows where processed. The l_status parameter should be set to the STATUS variable, l_processed is set to sqlca.sqlerrd[3], and l_target is set to the number of rows expected to be processed. If l_target is zero t hen the number of rows processed is not validated.

The returned value (TRUE|FALSE) indicated whether the there was a success.

Example

UPDATE table
   SET colname = "New value"
   WHERE colname = "Old Value"

CALL ifworked(STATUS, sqlca.sqlerrd[3],4,TRUE) RETURNING l_workstat

l_exitstat will be set to TRUE if the update statement worked and the number of rows processed was 4 otherwise it will be set to FALSE.

If work_stat is set to FALSE then the function merely returns, this to make the code easier to follow, but does add an overhead. For example

BEGIN WORK

   UPDATE table
      SET colname = "New value"
      WHERE colname = "Old Value"

   IF ifworked(STATUS, sqlca.sqlerrd[3],4,TRUE) THEN
      UPDATE another_table
         SET colname = "New value"
         WHERE colname = "Old Value"

      CALL ifworked(STATUS, sqlca.sqlerrd[3],0,TRUE) RETURNING l_workstat

   END IF

   IF l_workstat THEN
      UPDATE yet_another_table
         SET colname = "New value"
         WHERE colname = "Old Value"

      CALL ifworked(STATUS, sqlca.sqlerrd[3],0,TRUE) RETURNING l_workstat

   END IF

   IF l_workstat THEN
      COMMIT WORK
   ELSE
      ROLLBACK WORK
   END IF

lib_title

These series of functions control the ‘F-Key’ menus displayed at the bottom of the screen. When a module requests a new set of menu options, the current menu option is noted so that it can be recovered as the end of the module call.

The menu option window is sized depending upon the length of the menu requested. There is no facility for the menu to use more than one line

Function: key_title_table()

Creates a temporary table to contain which menu’s have been used.

Function: key_title(l_addition, l_key_option)

If l_addition set to TRUE then call the function to display the menu and record which menu has been displayed in the temporary table. If l_addition is FALSE then find the previous menu option, remove it from the temporary table and display the menu.

Function: key_title_draw(l_key_option)

Displays the menu option requested by querying the wfsmenu table. If the option is not available a fatal error is generated

lib_valid

Function: valid(l_str)

Defines what should be regarded as a valid input. This is very site dependent.

lib_yes

Function: yesno(l_menu_str)

Prompts the user for a yes/no answer returning TRUE for ‘yes’.

wfsoftware

Contains two global variables that are required by the libraries

g_progname CHAR(30) - the current progname
g_window SMALLINT - True if a ‘F-Key’ menu window is opened

This series of programs provides a front end to the UNIX version control system, SCCS. The full documentation can be seen in the download file but a summary is given below.

snew

This script adds a new file to the specified project/system area. It reports a warning if there are no SCCS keywords. By default the release level will be determined via the contents of the ${SCCSDIR}.release file, but this can over-ridden on the command line by using the -r option.

snew T customer.sql sys06

sget

To extract the latest version of a file from SCCS the sget command is used, the extracted file will be read-only. By default the latest version is extracted but this can be over-ridden on the command line using the -r option.

sget T customer.sql sys06

sedit

To generate a new version of a file, the file must be extracted for editing. This is achieved using the sedit script. Upon extraction the file is automatically placed within the EDITOR of the system, this can be prevented by the command line option -v. To alter historic versions of the file the -r option is used to specify the required release, in these cases SCCS will automatically generated the relevant sub-release information.

sedit -v T customer.sql sys06

sput

To place a file back into SCCS that was previously extracted for editing the script sput is used. CRN information must be provided.

sput T customer.sql sys06

sunget

If the reason a file was extracted for editing no longer exists then SCCS must be informed that no changes are required for the file. This is achieved using sunget.

sunget T customer.sql sys06

shist

SCCS maintains, within its file structure, a history of the changes made to a file. This information can be viewed using the shist command. There are two options , full (-f), or CRN only (-m).

shist -f T customer.sql sys06

This script deletes large numbers of rows from a table efficiently. It relies on a data file that contains a list of data that is to be deleted. The data file contains the primary key for the rows to be deleted, one key per line

Usage: bigdelete <database> <table> <column> <datafile>

Credit Card Number Checker

This function valids a credit card number and true/false depending on the validity of the number.

This script deletes large numbers of rows from a table efficiently. It relies on a data file that contains a list of data that is to be deleted. The data file contains the primary key for the rows to be deleted, one key per line

Usage: bigdelete <database> <table> <column> <datafile>

Control-\ handling

Interrupt handling

This is a pure C function, i.e. no ESQL, that converts the coltype obtained from the syscolumns table and converts it to a string that is meaningful as DDL.

The dbcheck script performs consistency checking across the entire system.

Usage: dbcheck [full | quick]

The passed parameter, quick or full, indicates the level of update statistics to be executed. ‘Full’ uses the statistics script whereas ‘quick’ merely echoes update statistics to the database. Although ‘quick’ executes in considerably less time than ‘full’ it should only be used on test and development databases. On production system the ‘full’ statistics will produce more detailed information for the query optimiser and hence faster execution of the queries.

However, before any of the database checking is started, the script validates the reserved pages of the instance and verifies the free lists and tblspace extents for the system databases.

On completion of the control area testing, each individual database is checked. First the tblspaces are verified, then the catalog areas and finally the data and index pages.

Note: Depending on the version of instance oncheck might require a lock on parts of the database as the system is checked

Database Sizing - Excel spreadsheet

This spreadsheet estimates the space required for the database. Within the spreadsheet are two worksheets that contain the table and index requirement based on either an existing or proposed schema.

This script is useful for debugging why an engine won't start and identifying which parameter(s) is giving problems. It checks basic user configuration against local files and NIS, path permissions and follows the links to the actual devices

This series of 4GL functions where written to control index dropping and rebuilding. Rather than hard-code the DDL for the indices within the program the various functions take a copy of the existing indices into a temporary table and after the real table processing has been completed the data within the temporary table can be used to rebuild the indices.

dyn_idx_copy(<table>, <TRUE|FALSE>)

This function will copy the existing table indices into the temporary table. The temporary will contain the DDL required to put the indices back. the TRUE|FALSE flag indicates whether a the temporary table is to be built (TRUE).

dyn_idx_build(<table>)

This function rebuilds the indices for the tablename passed.

dyn_idx_drop(<table>)

This function drops the indices for the specified table.

getcolname(<tabid>, <colno>)

Converts the colno into a colname.

is_null(<testvar>)

Having work on a number of sites where the logical definition of NULL is not necessary the same as the definition used by Informix, a standard NULL testing function is used.

Note: in the latter engines is_null() is a valid Informix function. If you wish to use our version just rename it

Note: on a pedantic note the use of is_ as a prefix for a function name is out within the C standard as it is reserved for future C code library development

Note: This code doesn't cater completely detached indices, the index will be rebuilt but not as a detached index.

This is a modified version on the standard ESQLC script tweak for GCC under AIX

This script converts a 'standard' unload file into a fixed width datafile. The script is generic and relies on a control file to detail the width of the required tables.

Usage: filepad <controlfile> >output.file

Control File Layout (example)

comment
comment A nice description
comment
Datafile myunload.file
Column 1    20
Column 2    32
Column 3     5

Functions

Virtually all the scripts that are written by Oninit have the same basic header information and start of script logic. To allow for easy of maintenance this logic is usually extracted out into Korn shell functions.

This script provides a basic health check on a running system.

This function provides a front end into the UNIX utility cu and enables numeric messages to be sent to digital pagers. It requires the uucp sub-system to be configured. i.e. within the System file (/usr/lib/uucp/System) an entry is required for each pager message. e.g.

prod_down Any ACU 9600 <pager no>,,, 99999999999999999
prod_tapefail Any ACU 9600 <pager no>,,, 111111111111111111

Although this approach works, it was first used long before SMS messaging had been thought off. It is much easier to use SMS messaging and the software Oninit uses to achieve this is sms_client

This script corrects the permissions on the Informix files. It must be run as root as the setuid must be set on certain files. The permissions for each product are listed in ${INFORMIXDIR}/etc/<filelist>.  It you have doubt as to which install filelist to use it is shown in the install script for the product

Usage: permfix <installfile list>

Restore

Unattended restore script using expect

When running sql files from the command line there is no ability to control whether the alterations should be committed or rolled back. This script allows sql files to be run from the command line but with full transaction control. This is achieved by converting the passed sql file to a stored procedure and then executing it via a control procedure. If an error occurs during the execution the script will abort and rollback any changes made to the database.

Usage: runsql <dbname> <sqlfile>

The most common error is omitting trailing semi-colons from single SQL statements.

Note: the passed control file must adhere to the stored procedure SQL restrictions.

Bulkload

This program is a script that loads large tables into the database avoiding the restrictions due to logs filling up. By default the table is loaded at 10000 rows at a time, but this can be overwritten with a command line option

These commands run be executed using the following command

bulkload [-s <numrows>] <filename> <tabname> <database>

This program provides a setuid wrapper. Many system administrators are not prepared to allow setuid code to be loaded to production environments due to potential security problems. This program doesn't remove these problems but alleviates them by removing the ability for non-root users from finding out what exactly is being executed. System administrators are happy because nobody can alter the control file without their permission as long as they set the permissions to 0400

The format of the control file is as follows

UID GID Command and parameters

For example

1|100|50|/usr/informix/bin/ontape -c
2|100|50|/usr/informix/bin/ontape -s

These commands run be executed using the following command

execcmd 1
execcmd 2 <database>

Database auditing. This is a series of Stored procedures and a script that monitor the databases. The aim is to try and predict when the current dbspace allocation will be exceeded. This is achieved by monitoring the growth rates since the audit was started.

The only table that must be pre-configured is the t9995chunks table. If there is no requirement to monitor sub-systems within the database then this table can be simply populated from the SMI information or else a dbschema. The 'chunk' element of the name is a misnomer that has been left over from earlier versions.

The runaudit script

This script controls everything and is normally run via cron. There is a -r option which forces the audit files to be reset and can only be triggered at the command line.

The SPL

sp9996_start	Forces the clearing of the audit tables
sp9996_dbaudit	Main controlling SPL and summarises at a database subsystem level
sp9996_tabaudit	Summarises at a table level
sp9996_fragaudit	Summarises the fragmented tables

The Tables

t9995chunks	Links the dbspace to a sub-system within the database and a table
t9996dbspace	Contains the summary information by sub-system
t9997tabspace	Contains the summary information for each table
t9993fragspace	Contains the summary information for fragmented table

The script takes a dbschema sql file and splits into separate DDL statements

This script generates the a series of update statistics commands that conform to the recommended practices specified by Informix.

Usage: statistics [-f count] <dbname>

By default, if the scripts encounters an error while processing it will pause and then retry. If after five attempts the error still exists then the scripts will report an error and continue. The default of five retries can be overwritten at the command line using the -f option.

To ensure that all scripts have the correct operating environment there is a standard environment file, .std.env. This is sourced at the beginning of every script. It allows variables to be passed to the calling script without being overwritten but will ensure that all variables that are required are set to a known state. However, in the case of cron jobs then the main login files are examined as part of the start up procedure for the scripts, and so the environment is not set correctly. This is overcome by examining whether the PS1 variable is set. If it is not set then the scripts assume that the calling process is not using the telnet or rlogin daemons.

This file is unique for each server. Any environment changes that take effect as part of the login process must be placed in this file if they are relevant to the cron jobs.