dbload script generation
I've posted several messages in this forum about all of the problems
I've had getting our company's data from Informix SE 4.x to Informix
SE 7.x. Dozens of tables had corrupted data (mostly bad dates
01/00/0000), systables listed several "orphan" tables with no matching
*.dat/*.idx files, etc. dbimport would run for hours, hit something
it didn't like and bomb out. The problem had to be fixed, and
dbimport started from scratch to run for hours again until it hit the
next piece of bad data which had to be fixed.
This was frustrating (to say the least). So... I make a little C
program to parse the *.sql file generated by dbexport and create a
dbload script (listing all of the database tables/column counts and
unload file names). At least now if it bombs out, I can start from
where it left off instead of from the beginning. I'm including the C
source below, I'm sure it can be improved upon, but it's really helped
me with this problem. Hope it's useful to someone else also!
(disclaimer: Use at your own risk, not responsible for any damage,
yada yada yada). Usage: makedbload dbexportfile.sql - Will create a
dbload script called command.sql. BTW: Tested on AIX 4.x and Informix
7.x
------------------------------------------------------------------------------
#include <stdio.h>
void makecomfile(char *tabname, char *ncols, char *ufile)
{
FILE *fp;
char buf [100];
fp = fopen("command.sql", "at");
if (fp == NULL)
{
printf("\\nError opening %s\\n", ufile);
return;
}
strcpy(buf, "FILE \\"");
strcat(buf, ufile);
strcat(buf, "\\" DELIMITER \\"|\\" ");
strcat(buf, ncols);
strcat(buf, ";\\n");
fputs(buf, fp);
strcpy(buf, "INSERT INTO ");
strcat(buf, tabname);
strcat(buf, ";\\n");
fputs(buf, fp);
strcpy(buf, " ");
strcat(buf, "\\n");
fputs(buf, fp);
fclose(fp);
printf("\\n%s %s %s", tabname, ncols, ufile);
}
void findtablecols(char *buf, char *uFile, char *nCols)
{
int i, j, k, len;
char uFileName [100];
char nc [25];
for (i = 0; i < strlen(buf); ++i)
{
if (buf [i] == '.')
{
k = 0;
for (j = i + 1; buf [j] != ' '; ++j)
{
uFileName [k++] = buf [j];
}
uFileName [k] = 0;
continue;
}
if (!memcmp(&buf [i], "number of columns", 17))
{
k = 0;
for (j = i + 20; buf [j] != ' '; ++j)
{
nc [k++] = buf [j];
}
nc [k] = 0;
break;
}
}
strcpy(uFile, uFileName);
strcpy(nCols, nc);
}
char * findunlfile(char *buf)
{
int i, j, k, len;
long r;
char uFileName [100];
char numRows [25];
FILE *fp1;
char buf1 [1024];
for (i = 0; i < strlen(buf); ++i)
{
if (buf [i] == '=')
{
k = 0;
for (j = i + 2; buf [j] != ' '; ++j)
{
uFileName [k++] = buf [j];
}
uFileName [k] = 0;
break;
}
}
return uFileName;
}
int main(int argc, char **argv)
{
FILE *fpSQLFile;
FILE *fpUNLFile;
char buf1 [1024];
char buf2 [1024];
char unlFileName [1024];
int i;
char tabname [25];
char nCols [25];
char uFile [100];
char sqlFile [100];
if (argc < 2)
{
printf("\\nUSAGE: makedbload <dbexport.sql file>\\n");
return -1;
}
strcpy(sqlFile, argv [1]);
fpSQLFile = fopen(sqlFile, "rt");
if (fpSQLFile == NULL)
{
printf("\\nERROR: Opening %s. Program Terminated...\\n",
sqlFile);
return -1;
}
unlink("command.sql");
while (!feof(fpSQLFile))
{
fgets(buf1, 1023, fpSQLFile);
strcpy(buf2, buf1);
i = 0;
for(i = 0; buf2 [i] != 0; ++i)
{
strncpy(unlFileName, &buf2 [i], 5);
unlFileName [5] = 0;
if (!strcmp(unlFileName, "TABLE"))
{
findtablecols(buf2, tabname, nCols);
break;
}
strncpy(unlFileName, &buf2 [i], 6);
unlFileName [6] = 0;
if (!strcmp(unlFileName, "unload"))
{
{
strcpy(uFile, findunlfile(buf2));
makecomfile(tabname, nCols, uFile);
break;
}
}
}
printf("\\n");
fclose(fpSQLFile);
return 0;
}
-------------------------------------------------------------------------
I, too, have a (slightly different) method for dynamically generating
.cmd files for dbload; it relies on
select systables.tabname, systables.ncols
from systables
where systables.tabid > 99
and systables.ncols not in (
select sysviews.tabid
from sysviews
where sysviews.tabid > 99)
;
The query is prepared, declared, opened, and looped with "while
(!sqlca.sqlcode) {." In that loop a .cmd file is written for every table
in the data base named "tabname.cmd."
The utility is named "cmdfil," and it is expected by a Korn shell
program that is "driven" by the .unl files I want loaded (there can be
hundreds of .cmd files, but who cares -- all you want to do is load a
particular .unl or group of .unl files so generate the .cmd files, use
'em, then blow them away). The Korn shell program loops through the .unl
files using the substring function to isolate the table name then uses
that to build and execute a dbload command.
A simple Korn shell program to do this looks like
#!/bin/ksh
# shift to get the argument
shift $((${OPTIND}-1))
# get one?
if [ "${*}" ]
then
export DBNAME=${*}
else
print "You must supply a data base name"
exit 1
fi
# run the cmdfil utility
cmdfil -d ${DBNAME}
# load the files
for file in *.unl
do
# use substring to isolate the table name from the .unl file
name=${file%%.unl}
dbload -d ${DBNAME} -c ${name}.cmd -l ${name}.err -e 100 -n 10000
done
# clean up after ourselves
rm *.cmd
If anybody's interested in cmdfil.ec I'll be happy to send it.
BTW: In reviewing the code, the strcpy(buf2, buf1) in main is unnecessary, left over from some earlier code. Several others things could be cleaned up also, but it all compiles and works properly.
> If anybody's interested in cmdfil.ec I'll be happy to send it. Good idea, very slick! I am interested in looking at cmdfile.ec, at your leisure please email to wolphie@hotmail.com (Joe). Thank you