Table load fails - "Cannot close blob"
Gentleman and Ladies,
On a nightly basis we are unloading data from one table on a remote
system, transmitting the data to our local system, and loading the
data into an analogous table on our local system.
The following error is recevied when loading the local table with
data from a remote database :
database selected.
table dropped
table created
permission revoked
permission granted
603: Cannot close blob
847: Error in load file line 31973
Error in line 1
Near character position 75
Output from finderr
-603 Cannot close blob.
This statement writes a BYTE or TEXT value, and some unexpected
error prevented finishing the creation of the value. Roll back the
current transaction. Check the accompanying ISAM error code for
more information; possibly there has been a hardware error or data
corruption of the blobspace or tblspace. If no direct cause can be
found, note all the circumstances and contact Informix Technical
Support.
-847 Error in load file line number.
There is a problem with the data on the indicated line of the load
data file. The operation stopped after inserting lines up to but not
including the line noted (number-1 rows have been inserted). If this
operation is inside a transaction, roll back the transaction. If not, you
will have to either delete the inserted rows from the table or remove
the used lines from the file before repeating the operation. To correct
the file, look for additional error messages that may help isolate the
problem. Possibly there are not enough, or too many, fields
(delimiters) on the indicated line. Possibly there is a data conversion
problem, for example non-numeric characters in a numeric field, an
improperly-formatted DATETIME value, or a character string that is
too long. Possibly there is a null (zero-length) field in a column
where nulls are not allowed. Edit the load file to correct the problem;
look for similar problems in following lines; then repeat the
operation.
As can be seen below the table is not using blobs, although there is a
type TEXT in table.
drop table loc_in_mast_220;
create table loc_in_mast_220
(
in_id serial not null,
pl_id integer,
cnt_nbr char(8),
item_cde char(20),
sdesc char(40),
ldesc text in table, <<<----------
commodity_cde char(5),
um_invt char(4),
min_qty decimal(9,2),
max_qty decimal(9,2),
qty_backorder decimal(12,2),
eoq decimal(9,2),
category char(4),
criticality_cde char(2),
capital_cde char(2),
class_cde char(4),
sub_class char(4),
id char(3),
local_id char(2),
inspection char(1),
direct char(1),
cet char(10),
blanket_po char(10),
repair_cde char(1),
auto_reorder_cde char(1),
upc char(20),
nsc char(20),
design_usage_nbr integer,
design_usage_char char(10),
beg_invt_cst decimal(12,2),
avg_invt_cst decimal(12,2),
last_invt_cst decimal(12,2),
descrep_amt decimal(12,2),
receipt_overage decimal(12,2),
overage_percent decimal(5,2),
gl_yes_no char(1),
list_cde char(10),
adj_cst decimal(12,2)
);
revoke all on loc_in_mast_220 from "public";
grant select on loc_in_mast_220 to "public" as "dbadmin";
load from "/var/job10_nedb/in/in_mast_220.unl" insert into loc_in_mast_220;
create index ix_220_30 on loc_in_mast_220 (cnt_nbr,item_cde);
create unique index ix_220_10 on loc_in_mast_220 (in_id);
create index ix926_2 on loc_in_mast_220 (pl_id);
Checking table status, in fact only 31972 rows have been loaded,
there should be ~ 50000 rows.
We have checked the load file for missing column delimiters, corrupt data,
etc, and have found nothing appearing out of order.
Paul Kiesel
CADD /CAE - Information Systems
Raytheon Engineers, Phila, Pa, USA
Voice 215-422-3598
E-Mail : kiesel@vishnu.ueci.com