Join is returning a default date

[Marley777]

DB2 table has a valid date, but the join is returning 1901/01/01.

[Nageshsunkoji]

DB2 table has a valid date, but the join is returning 1901/01/01.

More Information is required to analyse this problem.

More inputs will results great output.

[talk2shaanc]

assuming you have design, like below

db2----|
seq----|join stage----seq file

1. now you have left outer join, with seq file on left and db2 on the right.
2. assuming db2 has two columns, 1st is a key column and 2nd is the date column.
3. now in db2 stage or join stage or o/p seq file stage you didnt define date column as nullable.
4. So after a unsuccessful join, a record is assigned a default date by db2 as your date column is not nullable . I am quite sure that your join is not working, though to you it looks.

[kumar_s]

Data might be valid, but how about join?

[talk2shaanc]

Data might be valid, but how about join?

I dont concur with you. He is saying date looks good on the table, but after join its defaulted. A db2 table cannot store a invalid value in a date column.

[Marley777]

assuming you have design, like below

db2----|
seq----|join stage----seq file

1. now you have left outer join, with seq file on left and db2 on the right.
2. assuming db2 has two columns, 1st is a key column and 2nd is the date column.
3. now in db2 stage or join stage or o/p seq file stage you didnt define date column as nullable.
4. So after a unsuccessful join, a record is assigned a default date by db2 as your date column is not nullable . I am quite sure that your join is not working, though to you it looks.

Here's a little more history:
It is a left outer with the DB2 stage on the right.

We were running this job(join) in Datastage on another lpar/server, and it worked fine. The date fields were returned properly.

Datastage was installed on another lpar/server, and we tried running the same job. All fields return in the join were ok except the date fields, and they came back as 1901/01/01.

[kumar_s]

Is the fomat of date mentioned in the job/Project varied?

[Marley777]

The date is defined as a 'date' field.

[talk2shaanc]

In the new server, what is the default date format specified ? "YYYY-MM-DD" or "YYYY/MM/DD" or something else...if both the server has same default format set at "project" level..then ideally you shouldn't be having problem...