DSXchange

Hi,

We recently installed Data Stage 8.1 server edition on our Linux redhat ver 4 64 bit OS.

I am in a process of converting all my existing jobs (Data Stage 8.0.1 32 bit on Redhat Linux 4 32 bit OS) to DS8.1.

I am having a problem with a job running in 8.1 with warning logs.

I have a cusomer file (fixed width seq file) loading into a relational table (informix database). when I run this job, I am getting below error in ver 8.1 and same will run without any warnings in DS8.0.1.

If a file has 1000 rows in DS8.0.1 it process all 1000 rows where as in DS8.1 it does 800 rows and gives 200 rows as a illegal datetime value.

I checked the data and I did not find any problem with the data. I was working IBM support for last 2 week on this issue no help.

Please help me if anybody came across this kind problem

see below is the error messge with data
Regards
sripad



CustDBUpdate..trcustdb_out.trcustdb_tostage: DSD.BCIPut call to function SQLExecute failed.
SQL statement:INSERT INTO transdw:informix.stage_customer(cust_gen_key, cust_key, all_cust_key, prnt_id, prnt_id_own, store_num, tr_cust_num, mbs_cust_num, hh_id, prefix, first_name, mi, last_name, suffix, gender, loyalty_id, company_name, company_flag, addr_1, addr_2, addr_3, city, state, zip_code, zip_4, county, country, h_area, h_phone, h_ext, w_area, w_phone, w_ext, f_area, f_phone, f_ext, o_area, o_phone, o_ext, email, email_act, tr_email, oth_email, inval_email, birth_date, initial_trans_dt, last_trans_dt, us_mail_opt_in, us_rent_opt_in, email_opt_in, e_rent_opt_in, e_opt_in_date, last_maint_type, last_maint_dt, level_name, level_number, description, mbs_upd) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client]Illegal date/time value

cust_gen_key = 0
cust_key = 0
all_cust_key = 1
prnt_id = 0084878248
prnt_id_own = "M"
store_num = 00140
tr_cust_num = 0140016331
mbs_cust_num = NULL
hh_id = 0079524475
prefix = ""
first_name = "DANA"
mi = ""
last_name = "DIAZ"
suffix = ""
gender = "F"
loyalty_id = ""
company_name = NULL
company_flag = ""
addr_1 = "15707 CITRON AVE"
addr_2 = ""
addr_3 = ""
city = "FONTANA"
state = "CA"
zip_code = "92335"
zip_4 = "4493"
county = ""
country = "US"
h_area = "909"
h_phone = "6976663"
h_ext = ""
w_area = ""
w_phone = ""
w_ext = ""
f_area = ""
f_phone = ""
f_ext = ""
o_area = ""
o_phone = ""
o_ext = ""
email = "KOBESGIRL@MSN.COM"
email_act = ""
tr_email = "N"
oth_email = "N"
inval_email = ""
birth_date = NULL
initial_trans_dt = NULL
last_trans_dt = 02/20/2009
us_mail_opt_in = "Y"
us_rent_opt_in = "Y"
email_opt_in = "N"
e_rent_opt_in = "N"
e_opt_in_date = 06/07/2009
last_maint_type = "A"
last_maint_dt = 2009-06-09
level_name = "CUST"
level_number = 2
description = "DIAZ, DANA"
mbs_upd = "N"

The first 'problem' I see is three dates in two different formats:

last_trans_dt = 02/20/2009
e_opt_in_date = 06/07/2009
last_maint_dt = 2009-06-09

You need to be consistent.

chulett wrote:The first 'problem' I see is three dates in two different formats:

last_trans_dt = 02/20/2009
e_opt_in_date = 06/07/2009
last_maint_dt = 2009-06-09

You need to be consistent. ...

Criag

But the rows successfully inserted has exact same format and it works, so why it works for some data and not for other and the same work perfectly in 8.0.1 version

I will try changing all dates in the same format

Thanks
sripad

sripad wrote:

chulett wrote:The first 'problem' I see is three dates in two different formats:

last_trans_dt = 02/20/2009
e_opt_in_date = 06/07/2009
last_maint_dt = 2009-06-09

You need to be consistent. ...

Criag

But the rows successfully inserted has exact same format and it works, so why it works for some data and not for other and the same work perfectly in 8.0.1 version

I will try changing all dates in the same format

Thanks
sripad

Thanks Criag it worked

Good. It's not uncommon for things that 'worked' in older versions to stop working in newer ones as they tighten up code or address bugs for things that never really should have worked in the older version.

Can you please mark the thread as Resolved using the big button at th top of the screen? Thanks.