Re: ESQL/C, Duplication Check & Error -407
>From: uunet!nemo.Colorado.EDU!huangp (Pei-yu Huang)
>Subject: ESQL/C, Duplication Check & Error -407
>Date: Thu, 13 May 1993 13:56:12 GMT
>X-Informix-List-Id: <news.3316>
>
> We use an ESQL/C program to process data into Informix tables.
>To weed out duplication I've tried cursors, selecting into host variables,
>selecting into temp tables, selecting without into clause, and update
>statement. None of them could get me through these some 14,000 records of
>data. It does not bomb at the same piece of data and the great majority
>of errors from numerous runs are -407, with only very few -408. Here is
>my thought: as tables grow large, each select statement takes more
>resources to finish because there are more rows to check. And somehow
>garbage is not collected away. But I close and free cursors when I use
>them, drop temp tables as appropriate, begin and commit work when using
>update statement. Have I missed something? And, could someone please
>explain to me how exactly I should interpret the sqlca.sqlerrd[4], the
>offset of error into the SQL statement? I'm running INFORMIX-ESQL Version
>5.00.UC2 with INFORMIX-SQL Version 4.10.UD2 and INFORMIX-OnLine 5.00.UC2.
I've not seen any responses, possibly because it isn't entirely clear from
the question where the data is being sourced from, but here goes anyway.
I'm not clear whether you are using the ISQL for anything -- you ESQL/C
program would not normally need anything from there. I'm assuming it is
not relevant to this particular problem.
If the data is coming from some external source like a set of ASCII files,
the best way to eliminate the duplicate data is probably by using the Unix
sort program to eliminate the duplicates. With careful use, you can tell
it exactly which fields to sort on, and so on. You can also use awk, sed
and uniq to help identify where the problems are. Incidentally, if your
data is coming from an unreliable source, is delimited, and you are running
into problems with some rows not containing enough fields, you can use the
script below to identify where the trouble is:
cat $* | tr -dc '|\\012' | uniq -c
Given data such as:
1|23|Abyssinia|
1|24|Ethiopia|
2|Sudan|
3|9|Kenya|
4|2|South Africa|
It produces:
2 |||
1 ||
2 |||
This makes it easy to see whether there are problems, and you can use the
counts to identify the lines where the problems occur. The forumlation
above using cat as a pre-processor gets expensive if the data files are in
the tens of megabytes, but you can adapt the script when necessary.
If the data is already in the database, or is being added to data that is
already in the database, then you are back to using pure SQL. There are a
number of tricks you can use, depending on where the data is.
SELECT * FROM TableA
UNION
SELECT * FROM TableB
INTO TEMP TempA;
BEGIN WORK;
DELETE FROM TableA;
INSERT INTO TableA SELECT * FROM TempA;COMMIT WORK;
The UNION eliminates exact duplicate rows. This may not be all that
efficient, and may use huge quantities of temporary disc space, but it is
simple and works. It could also use huge quantities of logical log.
If the problem is duplicate keys (subsets of the columns), then you have to
be trickier:
SELECT * FROM TableB
WHERE NOT EXISTS (SELECT * FROM TableA
WHERE TableA.Keycol1 = TableB.Keycol1
AND TableA.Keycol2 = TableB.Keycol2); INTO TEMP TempA;
INSERT INTO TableA SELECT * FROM TempA;
This uses a correlated sub-query which will be slow. You have to use this
two part operation as you cannot insert into a table which is referenced in
the SELECT statement. You can use this technique in lieu of the UNION
formulation, but (a) you have to list every column in the WHERE clause, and
(b) for each column which allows nulls, you have to use:
AND (TableA.NullColumn = TableB.NullColumn
OR (TableA.NullColumn IS NULL AND TableB.NullColumn IS NULL))
which should be enough to encourage you to use the UNION version -- or not
to allow nulls.
Where there is a single column key, it would be more efficient to use:
INSERT INTO TableA
SELECT * FROM TableB
WHERE Keycol NOT IN (SELECT Keycol FROM TableA);
This avoids the correlated sub-query, and is a good argument for single
column keys.
Given that you are programming in ESQL/C, I don't see any particular reason
why you can't simply use a cursor to select from TableB and insert each
record into TableA. If the insert fails, then you can report the problem
(or ignore it, according to taste). You can use transactions to suit, and
a hold cursor to retain your positioning across transaction boundaries.
This would work in I4GL too.
In all these mechanisms, you need to give careful thought to isolation
levels, logging modes, amount of log used, number of locks used, and both
transaction boundaries and whether table locks should be used (to which
the answer is probably Yes).
If none of these approaches helps, please can you detail your problem more
clearly.
Yours,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>