Hello,
Need help on an IMPORT related issue, I'm working on AIX 6.1 with DB2 9.7 FP4 (let me know if you need additional information about environment) and this is my the IMPORT CLP script:
-- STTDM002.CLP
IMPORT FROM "STTDM002.DAT"
OF ASC
MODIFIED BY DATEFORMAT="YYYY-MM-DD" STRIPTBLANKS
METHOD L
(
001 005,
006 035,
036 045,
046 055,
056 065,
066 185,
186 211
)
MESSAGES "STTDM002.LOG"
INSERT INTO STTDM002
(
M002_ABI,
M002_NTAB,
M002_NCOD,
M002_DT_INIVAL,
M002_DT_FINVAL,
M002_DESCR,
M002_TMST
);
--

When i run it (with db2 -tvf STTDM002.CLP) i got this message on the terminal:

Number of rows read = 4803
Number of rows skipped = 0
Number of rows inserted = 4778
Number of rows updated = 0
Number of rows rejected = 25
Number of rows committed = 4803

SQL3107W There is at least one warning message in the message file.

and in the log file i got various error message (repeated):
-- STTDM002.LOG
SQL3148W A row from the input file was not inserted into the table. SQLCODE
"-302" was returned.

SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out
of range for its corresponding use. SQLSTATE=22001

SQL3185W The previous error occurred while processing data from row "4783" of
the input file.
--

Data in the file STTDM002 are good and well formatted so i checked again and I thought to try a LOAD:

LOAD CLIENT FROM "STTDM002.DAT"
OF ASC
MODIFIED BY DATEFORMAT="YYYY-MM-DD" STRIPTBLANKS
METHOD L
(
001 005,
006 035,
036 045,
046 055,
056 065,
066 185,
186 211
)

MESSAGES "STTDM002.LOG"
INSERT INTO STTDM002
(
M002_ABI,
M002_NTAB,
M002_NCOD,
M002_DT_INIVAL,
M002_DT_FINVAL,
M002_DESCR,
M002_TMST
);

LOAD works fine:
Number of rows read = 4803
Number of rows skipped = 0
Number of rows loaded = 4803
Number of rows rejected = 0
Number of rows deleted = 4778
Number of rows committed = 4803

I need IMPORT utility in order to check constraints and because distributed scheduler use only IMPORT scripts (is an enforced policy).
Any suggestions about the error message?


Thanks in advance, regards.

Filippo Conti <filippo.conti.it (AT) gmail (DOT) com> writes:

Sounds like you should open a PMR with IBM.


--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

On 04.04.12 14:16 , Filippo Conti wrote:
Can you please post the table definition.

db2look -d <dbname> -e -t STTDM002

Please post row 500 and row 4783 of the input file as well.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

Il giorno giovedì 5 aprile 2012 06:45:39 UTC+2, Helmut Tessarek ha scritto:
Sure:

CREATE TABLE "SCHEMA "."STTDM002" (
"M002_ABI" CHAR(5) NOT NULL ,
"M002_NTAB" CHAR(30) NOT NULL ,
"M002_NCOD" CHAR(10) NOT NULL ,
"M002_DT_INIVAL" DATE NOT NULL ,
"M002_DT_FINVAL" DATE NOT NULL ,
"M002_DESCR" VARCHAR(120) ,
"M002_TMST" TIMESTAMP )
IN "TBS001" INDEX IN "TBS001X" ;


-- DDL Statements for primary key on Table "SCHEMA "."STTDM002"

ALTER TABLE "SCHEMA "."STTDM002"
ADD CONSTRAINT "STTDM002_PK" PRIMARY KEY
("M002_ABI",
"M002_NTAB",
"M002_NCOD",
"M002_DT_INIVAL",
"M002_DT_FINVAL");

line 500 of STTDM002.DAT is:
<line>
00000N001_CAE C 16231 0001-01-019999-12-31Fabbricazione di porte e finestre in legno (escluse porte blindate) 2012-03-30-16.18.08.000000
</line>

line 4783 of STTDM002.DAT is:
<line>
00000N001_CAE T 0001-01-019999-12-31ATTIVITÀDI FAMIGLIE E CONVIVENZE COME DATORI DI LAVORO PER PERSONALE DOMESTICO; PRODUZIONE DI BENI E SERVIZI INDIFFEREN2012-03-30-16.18.08.000000
</line>

thank you

Hi Filippo,

On 05.04.12 1:42 , Filippo Conti wrote:
Are you using a unicode database?

line 4783, position 73: À <-- this is a 2 byte character in unicode.
The field is a varchar(120) and you try to import 121 bytes.

2 tests (use the one which is easier or faster to do):

1) change M002_DESCR to varchar(240)
2) change character À to x

The error message is a little bit confusing, but I assume it is because the
length is not the number of bytes and the column size is too small.
You should rather get a message like: the data was truncated...

If the tests do not solve the problem, you have found a bug. -> PMR

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

Il giorno giovedì 5 aprile 2012 08:09:22 UTC+2, Helmut Tessarek ha scritto:
You're right =)
Confusing message but why LOAD does not log nothing ?

On 05.04.12 3:16 , Filippo Conti wrote:
LOAD does not go through the engine but right to the tablespace.
This is the reason why you usually need to do a 'set integrity' after a LOAD.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

Il giorno giovedì 5 aprile 2012 09:32:29 UTC+2, Helmut Tessarek ha scritto:
Thanks for fast response, very kind.

Thanks to all folks =)