Re: dbload problem

>From: Ian Goddard <igoddard@netcomuk.co.uk> >Date: Sun, 25 Aug 1996 19:31:11 -0700 >X-Informix-List-Id: <news.27415> > >Jeff Craig (settler@dbintellect.com) wrote: >> We are running INFORMIX-Online v7.12.UC3X2 on an IRIX 5.3 >> system, using dbload to load non-contiguous fields from a >> flat file to a single table. When the input fields are >> non-contiguous, dbload starts thrashing in our /tmp directory, >> putting out dbxnnnn_nnn files, running utilization up to >> 80-90%. Is there anyway to change this behavior? I have >> already tried most of the obvious, like setting DBSPACETEMP >> in the onconfig, setting PSORT_DBTEMP, etc. > >My own preference given this requirement would be to write a filter to >turn the source files into Informix load format. I absolutely agree. In fact, I have such a tool in my kit, and I attach a shell archive of the source code for it. It could do with an enhancement to mangle odd input datetime formats into standardized formats, but covers a lot of useful ground otherwise. There is a manual page and a makefile (dbldfmt.mk) included. YOu may have to fiddle with config.h to get it to work -- the code in emalloc.c needs to be upgraded to assume ANSI C as default, still. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> : "@(#)shar.sh 1.9" #! /bin/sh # # This is a shell archive. # Remove everything above this line and run sh on the resulting file. # If this archive is complete, you will see this message at the end: # "All files extracted" # # Created: Mon Aug 26 09:55:59 PDT 1996 by johnl at Informix Software Ltd. # Files archived in this archive: # dbldfmt.1 # dbldfmt.c # dbldfmt.mk # config.h # debug.h # emalloc.h # filter.h # getopt.h # stderr.h # emalloc.c # ffilter.c # range.c # stderr.c # #-------------------- if [ -f dbldfmt.1 -a "$1" != "-c" ] then echo shar: dbldfmt.1 already exists else echo 'x - dbldfmt.1 (3901 characters)' sed -e 's/^X//' >dbldfmt.1 <<'SHAR-EOF' X'\\" @(#)dbldfmt.1 1.2 96/05/29 X'\\" @(#)Manual page: Application -- Part of application X.ds fC "Version: 1.2 (96/05/29) X.TH DBLDFMT 1S "JLSS UNIX Tools" X.SH NAME Xdbldfmt \\(em convert fixed format LOAD file to delimited format X.SH SYNOPSIS X\\fBdbldfmt\\fP [-P p][-D d] -l len \\e X [ -c literal | -d range.dp | -f range] ... [file ...] X.SH DESCRIPTION X\\fBdbldfmt\\fP converts a file with fixed length records separated by Xnewlines into the delimited format used by the Informix LOAD statement Xand the \\fBdbload\\fP and dbimport commands. X.P XThe `-l' option is used to specify the line length and is mandatory. XAny deviations from this length causes the program to stop immediately. XUse programs fixlen(1J) or recsplit(1J) or bsplit(1J) to manipulate Xfiles without delimiters or with variable length records. X.P XThe `-f', `-c' and `-d' can occur multiple times on the command line. XThe `-c' option specifies the text for a constant field, which can be Xuseful to specify missing information. XThe `-d' option allows you to specify a range of columns which form the Xnumber, and it implicitly inserts a decimal point at the position specified Xby `.dp'. XThere is no default value for `.dp'. XThe `-f' option is used to specify one or more ranges of columns to Xbe treated as a single field. XIt also allows literal strings to be embedded in the value. X.P XThe `-P' option allows you to specify a single character which will be Xused as the decimal point in all `-d' fields. XThe `-D' option allows you to override the default delimiter which Xis specified by $DBDELIMITER and defaults to `|'. X.P XA range of columns is a value such as 13, or a column range such as 13-16, Xor a combination of values and column ranges separated by commas, Xor a combination of values separated by literals. XRanges such as -16 are interpreted as 1-16, Xand ranges such as 17- are interpreted as 16-len. X.P XNote that the literal embedding options are rather idiosyncratic. XYour can embed commas or quotes or backslashes or dashes by escaping them. XYou can even get all of these into a field if you are very good at handling Xshell quoting conventions. X.P XIf no input file is specified, the standard input is processed. X.SH EXAMPLES X.eS Xdbldfmt -P',' -D'*' -l 100 -d 1-10.3 -f11-24 -f 22-24,26-37 -c widget \\e X -f 80-83/84-85/86-87 -f 40-50'"\\e\\e'\\e''\\e",-"'60-70 X.eE XThe output data will have fields delimited by '*'; ',' will be used as the Xdecimal point (presumably DBMONEY="DM," or something similar); the lines Xare 100 chars long excluding the newline; the first ten characters are a Xdecimal number with implicit decimal point between characters 7 and 8; Xcharacters 11-24 form the next field; characters 22-24 followed by Xcharacters 26-37 (22-37 omitting 25) form the next field; the next field is Xa constant string 'widget'; the next field is columns 80-87 with slashes in Xthe output between the designated columns; and the last field is columns X40-50 followed by back-slash, single-quote, double-quote, comma, dash and Xcolumns 60-70 (tested with Korn Shell -- that sequence of quotes is Xcontorted enough that it might make a difference). X.P XNote that the decimal field is not validated for all digits. XThere is no built-in limit on the size of the lines or on the number of Xfields, nor on the number of components in a field. XFixed format files exclude blobs by definition! X.SH "BUILDING DBLDFMT" XTo compile with a pre-configured system defined in config.h, use: X.eS Xmake -f dbldfmt.mk CPPFLAGS=-DSYSTEM=HPUX_8 X.eE XIf there is no suitable pre-configured system, modify config.h and build a Xnew system definition. XPlease let me know about any new configurations. XNote that the STR_SYSTEM string is not used in this program. X.SH FILES X.SH "SEE ALSO" X.SH DIAGNOSTICS XShould be self-explanatory. X.SH BUGS XThe syntax required to get quotes and backslashes into a field is Xexcruciating. X.SH AUTHOR XJonathan Leffler X.br XJLSS X.br X10th July 1995 SHAR-EOF chmod 444 dbldfmt.1 if [ `wc -c <dbldfmt.1` -ne 3901 ] then echo shar: dbldfmt.1 unpacked with wrong size fi # end of overwriting check fi #-------------------- if [ -f dbldfmt.c -a "$1" != "-c" ] then echo shar: dbldfmt.c already exists else echo 'x - dbldfmt.c (6909 characters)' sed -e 's/^X//' >dbldfmt.c <<'SHAR-EOF' X/* X@(#)File: dbldfmt.c X@(#)Version: 1.6 X@(#)Last changed: 96/08/26 X@(#)Purpose: Convert fixed-length records to delimited variable-length X@(#)Author: J Leffler X*/ X X/*TABSTOP=4*/ X X/* -- Include Files */ X X#include <ctype.h> X#include <stdio.h> X#include <stdlib.h> X#include <string.h> X#include <assert.h> X#include "emalloc.h" X#include "stderr.h" X#include "filter.h" X#include "getopt.h" X X/* -- Constant Definitions */ X X#define F_CONST 1 X#define F_R