Problem in Date From DB2/UDB

ragunathan13 · Post by **ragunathan13** » Wed Nov 08, 2006 5:42 am

hi All..

I read records from DB2 using DB2/UDB stage and loaded into Dataset. In this some Date fields are there. When i view the records from DB2\UDB stage i got actual date present in database. But when the same records are viewed in Dataset stage the dates are existing in the previous day's date.

Pls confirm on the solution for the above issue.

Regards
Raguanthan J

tagnihotri · Post by **tagnihotri** » Wed Nov 08, 2006 7:05 am

Can you please elaborate on "But when the same records are viewed in Dataset stage the dates are existing in the previous day's date"

ragunathan13 wrote:hi All..

I read records from DB2 using DB2/UDB stage and loaded into Dataset. In this some Date fields are there. When i view the records from DB2\UDB stage i got actual date present in database. But when the same records are viewed in Dataset stage the dates are existing in the previous day's date.

Pls confirm on the solution for the above issue.

Regards
Raguanthan J

ragunathan13 · Post by **ragunathan13** » Wed Nov 08, 2006 7:57 am

hi tagnihotri

Date in Database as 2000-10-12

When i view from DB2/UDB Stage Date is 2000-10-12 only

But after running the job if i view the same record in Dataset the Date is coming like 2000-10-11.

Thanks and Advanced Thanks
Ragunathan J

Raghavendra · Post by **Raghavendra** » Wed Nov 08, 2006 8:05 am

Ragunathan,

can you check whether there is any processing on the date field.
Do u have any transformers where you will be doing some processing on the date field before mapping to the dataset.

ragunathan13 · Post by **ragunathan13** » Wed Nov 08, 2006 8:09 am

Hi Raghavendra

No., i just extract the data from db2 database using DB2/UDB Stage and simply loaded into Dataset. There is no transformer stage like.

Raghavendra · Post by **Raghavendra** » Wed Nov 08, 2006 8:35 am

Are you looking at the correct dataset..
Or check if any other job is updating the dataset.

tagnihotri · Post by **tagnihotri** » Wed Nov 08, 2006 8:37 am

To be honest, this sounds ghostly! as in if there is no processing from extraction to landing then how come -1 day

Can not think of anything first hand, but if it wud be me I would read the date as varchar or char and try writting it to seq file, just to point where the issue is!

ragunathan13 wrote:hi tagnihotri

Date in Database as 2000-10-12

When i view from DB2/UDB Stage Date is 2000-10-12 only

But after running the job if i view the same record in Dataset the Date is coming like 2000-10-11.

Thanks and Advanced Thanks
Ragunathan J

ragunathan13 · Post by **ragunathan13** » Wed Nov 08, 2006 8:50 am

hi

i looking at correct dataset only Raghavendra..

If i read it as a varchar or Char, it gives a correct date, When i read it as a Date then only this this issue.

tagnihotri · Post by **tagnihotri** » Wed Nov 08, 2006 9:01 am

As I understand this, if you read the data from the dataset setting date fields as char the values are correct ? If yes then I will say there is no harm to right them as char only, guys please correct me if I am wrong!

ragunathan13 wrote:hi

i looking at correct dataset only Raghavendra..

If i read it as a varchar or Char, it gives a correct date, When i read it as a Date then only this this issue.

ragunathan13 · Post by **ragunathan13** » Wed Nov 08, 2006 9:26 am

its ok that there is no harm in using as char, i want to know the reason of this problem, why it is not properly resulting in case of using 'date'

Krazykoolrohit · Post by **Krazykoolrohit** » Wed Nov 08, 2006 10:04 am

can you check at the table level, if there is any restriction or preprocessing involved with this field

ragunathan13 · Post by **ragunathan13** » Wed Nov 08, 2006 11:46 pm

Krazykoolrohit wrote:can you check at the table level, if there is any restriction or preprocessing involved with this field

No, There is no restriction or preprocessing involved with this field. Simply Load the date fiels as 'date' and Read it as 'date'.

tagnihotri · Post by **tagnihotri** » Thu Nov 09, 2006 12:04 am

hmmm as you are able to read data well, the possiblity of pre-processing was thin.

ragunathan13 wrote:
Krazykoolrohit wrote:can you check at the table level, if there is any restriction or preprocessing involved with this field
No, There is no restriction or preprocessing involved with this field. Simply Load the date fiels as 'date' and Read it as 'date'.

Andet · Post by **Andet** » Sat Nov 11, 2006 6:26 pm

Did you check for triggers?
What is the column definition of the date in the table vs. the definition in DataStage?
Are you extracting from the table using a select * or select Col name, or select coalesce(colname,'2000-01-01')
Or anything like that?
How do you specify the date files on the sequential output stage? Do you specify any formats?

Ande

ragunathan13 · Post by **ragunathan13** » Mon Nov 13, 2006 12:18 am

Andet wrote:Did you check for triggers?
What is the column definition of the date in the table vs. the definition in DataStage?
Are you extracting from the table using a select * or select Col name, or select coalesce(colname,'2000-01-01')
Or anything like that?
How do you specify the date files on the sequential output stage? Do you specify any formats?

Ande

Hi Ande

The column definition of the table and dataset both are date(default formet yyyy-om-dd)

From database extract like select col name1, col name 2..

i did't specify any formats(all are defaults)

DSXchange

Problem in Date From DB2/UDB

Problem in Date From DB2/UDB

Re: Problem in Date From DB2/UDB