Explain an error message

[Robert Stearns]

I am getting:
[IBM][CLI Driver][DB2/LINUX] SQL0408N A value is not compatible with
the data type of its assignment target. Target name is "". SQLSTATE=42821

from the following statement:
MERGE INTO is2.animals t1
USING nullid.nalfherd_in t2
ON t1.bhid = t2.bhid
WHEN MATCHED THEN UPDATE SET
t1.color=t2.color,
t1.tattoo = cast(coalesce(t2.prefix,'') concat
coalesce(t2.animalid,'') concat
coalesce(t2.byr_code,'') AS char(9)),
t1.TATTOO_LOC = t2.TATTOO_LOC,
t1.ASSOC = 'LM',
t1.PREFIX = t2.REG_PREFIX,
t1.regnum = t2.regisno;

Furthermore, if I omit the last assignment, the statement works. I see
no cause for the message unless it is the "NOT NULL" in one table and
not the other.

The relevant DDL follows:

CREATE TABLE nullid.nalfherd_in
(prefix CHARACTER(4),
animalid CHARACTER(4),
byr_code CHARACTER(2),
tattoo_vent CHARACTER(1),
sex CHARACTER(1),
tattoo_loc CHARACTER(1),
reg_prefix CHARACTER(3) NOT NULL,
regisno character(10) NOT NULL,
namex CHARACTER(25),
sire_reg_prefix CHARACTER(3),
sire_regisno character(10),
dam_reg_prefix CHARACTER(3),
dam_regisno character(10),
-- dam_breeds deleted
-- dam_yob deleted
owner_assn CHARACTER(1),
ownerx INTEGER,
mating CHARACTER(1),
brd_in_date DATE,
brd_out_date DATE,
dob DATE,
bw SMALLINT,
preg_days SMALLINT,
ce CHARACTER(1),
-- damage_yrs deleted
breeds CHARACTER(8),
color CHARACTER(1),
percent1 DECIMAL(5, 1),
twin_code CHARACTER(1),
hps CHARACTER(1),
et CHARACTER(1),
bld_case_no INTEGER,
bld_case_id CHARACTER(3),
birth_adj SMALLINT,
disposal CHARACTER(1),
disposal_date DATE,
bld_reason CHARACTER(1),
-- daage_days deleted
wean_flag CHARACTER(1),
year_flag CHARACTER(1),
wean_date DATE,
wean_days SMALLINT,
foster_flag CHARACTER(1),
creep_flag CHARACTER(1),
wean_wt SMALLINT,
wean_adj SMALLINT,
wean_hip_ht DECIMAL(4, 1),
wean_frame_score DECIMAL(4, 1),
wean_irr_flag CHARACTER(1),
wean_work_group INTEGER,
year_date DATE,
year_days SMALLINT,
year_wt SMALLINT,
year_adj SMALLINT,
year_hip_ht SMALLINT,
year_frame_score SMALLINT,
scrotal DECIMAL(5, 2),
scrotal_adj DECIMAL(5, 2),
pelvic_width DECIMAL(4, 1),
pelvic_height DECIMAL(4, 1),
year_irr_flag CHARACTER(1),
year_work_group INTEGER,
year_adg DECIMAL(5, 2),
year_adg_ratio SMALLINT,
year_wda DECIMAL(5, 2),
year_wda_ratio SMALLINT,
scan_date DATE,
scan_age SMALLINT,
scan_irr_flag CHARACTER(1),
scan_work_group INTEGER,
rump_act DECIMAL(5, 2),
rump_adj DECIMAL(5, 2),
rump_ratio SMALLINT,
fat_act DECIMAL(5, 2),
fat_adj DECIMAL(5, 2),
fat_ratio SMALLINT,
rea_act DECIMAL(5, 1),
rea_adj DECIMAL(5, 1),
rea_ratio SMALLINT,
imf_act DECIMAL(5, 2),
imf_adj DECIMAL(5, 2),
imf_ratio SMALLINT,
epd_ced SMALLINT,
epd_ced_acc CHARACTER(3),
epd_bw DECIMAL(5, 1),
epd_bw_acc CHARACTER(3),
epd_ww SMALLINT,
epd_ww_acc CHARACTER(3),
epd_yw SMALLINT,
epd_yw_acc CHARACTER(3),
epd_milk SMALLINT,
epd_milk_acc CHARACTER(3),
epd_cem SMALLINT,
epd_cem_acc CHARACTER(3),
epd_sc DECIMAL(5, 1),
epd_sc_acc CHARACTER(3),
epd_stay SMALLINT,
epd_stay_acc CHARACTER(3),
epd_doc SMALLINT,
epd_doc_acc CHARACTER(3),
epd_cw SMALLINT,
epd_cw_acc CHARACTER(3),
epd_rea DECIMAL(5, 2),
epd_rea_acc CHARACTER(3),
epd_fat DECIMAL(5, 2),
epd_fat_acc CHARACTER(3),
epd_marb DECIMAL(5, 2),
epd_marb_acc CHARACTER(3),
daughters INTEGER,
epd_date DATE,
dna_case_no INTEGER,
dna_case_id CHARACTER(3)
)
DATA CAPTURE NONE;

----------------------------------------------------------------

CREATE TABLE animals
(
BHID BIGINT NOT NULL,
CONTROLLER INTEGER NOT NULL,
LOCATION INTEGER NOT NULL,
TATTOO CHARACTER(9),
TATTOO_LOC CHARACTER(1),
ASSOC CHARACTER(5),
PREFIX CHARACTER(5),
REGNUM character(10),
TAG CHARACTER(5),
SEX CHARACTER(1),
BIRTH_DATE DATE,
NAMEX VARCHAR(50),
ACTIVEX CHARACTER(1),
SIRE_BHID BIGINT NOT NULL,
DAM_BHID BIGINT NOT NULL,
RECIP_BHID BIGINT NOT NULL,
ELECTRONIC_ID VARCHAR(30),
COMMENTX VARCHAR(240),
DISPOSAL_CODE SMALLINT,
DISPOSAL_DATE DATE,
COLOR CHAR(2),
HPS CHAR(1),
MATING CHAR(1),
BREED_1 CHAR(2),
PCT_1 DECIMAL(5,2),
BREED_2 CHAR(2),
PCT_2 DECIMAL(5,2),
BREED_3 CHAR(2),
PCT_3 DECIMAL(5,2),
BREED_4 CHAR(2),
PCT_4 DECIMAL(5,2),
BLOOD_TYPED CHARACTER(1),
BLOOD_CASE_NUMB CHARACTER(8),
DNA_TESTED CHARACTER(1),
DNA_CASE_NUMB CHARACTER(8),
OLD_TAG CHARACTER(5),
BANGS_ID CHARACTER(12),
deal_desc varchar(1000)
)
DATA CAPTURE NONE
IN USERSPACE1;