Federated (information integrator) load from cursor - Oracle date problem

[peteh]

Hi All;
We have many production jobs that "load from cursor" to a UDB/AIX 8.2
(with dpf) data warehouse from source tables residing Oracle 9i. Since
Oracle dates are (roughly) equivalent to DB2 timestamps, we frequently
use the date() function to "convert" from the Oracle date datatype to
the DB2 date datatype.

We have used this technique on over 20 Oracle tables for several months
with no problem. One table in particular fails with a sqlcode -180
(invalid db2 date value). Does anyone why we might be seeing this?.
Could it be that Oracle supports dates outside the range of that which
is supported by db2? Any help appreciated. Job output below...

Pete H
-------------------------------------------------
declare fedcurs cursor for
Select item
, loc
, constrstocklowq ty
, date(constrstoc klowdate)
, constrstocklowd ur
, constrstocklowq ty
, date(constrstoc koutdate)
, constrstockoutd ur
, maxconstrcovdur
, date(maxconstrc ovdate)
from fdr.manu_skusta tstatic
where item not like 'ITC%'
and item not like 'RM%'
and item not like 'PGM%'
and item not like 'IGP%'
and item not like 'ITM%'
;
SQLCODE is: 0

sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL08023
sqlerrd : (1) 0 (2) 0 (3) 511397
(4) 180107 (5) 0 (6) 1
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 00000
SQL stmt is:
load from fedcurs of cursor warningcount 2
messages /xxxx/prod/syslog/irw147.2006-06-05-02:55:08/irw147.loadmsgs
replace into STAGE.manu_skus tatstatic
data buffer 600
redirect_part_o utput
/xxxx/prod/syslog/irw147.2006-06-05-02:55:08/load.log
;
SQLCODE is: -180

sqlcaid : SQLCA sqlcabc: 136 sqlcode: -180 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLSNERR
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 22007

[Knut Stolze]

peteh wrote:

Hi All;
We have many production jobs that "load from cursor" to a UDB/AIX 8.2
(with dpf) data warehouse from source tables residing Oracle 9i. Since
Oracle dates are (roughly) equivalent to DB2 timestamps, we frequently
use the date() function to "convert" from the Oracle date datatype to
the DB2 date datatype.

We have used this technique on over 20 Oracle tables for several months
with no problem. One table in particular fails with a sqlcode -180
(invalid db2 date value). Does anyone why we might be seeing this?.
Could it be that Oracle supports dates outside the range of that which
is supported by db2?

You get the same error if you run the query as is (w/o cursor or the load)
on the command line? If yes, then you should try to narrow down which rows
are failing and then have a look at the Oracle DATE value there.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[peteh]

> > snip<<

We have used this technique on over 20 Oracle tables for several months
with no problem. One table in particular fails with a sqlcode -180
(invalid db2 date value). Does anyone why we might be seeing this?.
Could it be that Oracle supports dates outside the range of that which
is supported by db2?

You get the same error if you run the query as is (w/o cursor or the load)
on the command line? If yes, then you should try to narrow down which rows
are failing and then have a look at the Oracle DATE value there.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Thanks Knut;
Yes - the query fails with or without the load. Turns out that although
Oracle claims to support a max date value of 9999-12-31, we managed to
get a single row into this table that even Oracle can't show us! When
querying Oracle directly with a WHERE clause of "date > '31-DEC-9999'",
we see a single row with a value of '16-APR-39' (?) Go figure... Assume
this is an Orace bug (say it ain't so!). Google shows at least one
other person encountering this mystery in the oracle group... Thanks
for your help. Looks like we'll need to hit up Oracle support and/or
filter for dates between '0001-01-01' and '9999-12-31'

Pete H

[DA Morgan]

peteh wrote:

snip<<
We have used this technique on over 20 Oracle tables for several months
with no problem. One table in particular fails with a sqlcode -180
(invalid db2 date value). Does anyone why we might be seeing this?.
Could it be that Oracle supports dates outside the range of that which
is supported by db2?

You get the same error if you run the query as is (w/o cursor or the load)
on the command line? If yes, then you should try to narrow down which rows
are failing and then have a look at the Oracle DATE value there.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Thanks Knut;
Yes - the query fails with or without the load. Turns out that although
Oracle claims to support a max date value of 9999-12-31, we managed to
get a single row into this table that even Oracle can't show us! When
querying Oracle directly with a WHERE clause of "date > '31-DEC-9999'",
we see a single row with a value of '16-APR-39' (?) Go figure... Assume
this is an Orace bug (say it ain't so!). Google shows at least one
other person encountering this mystery in the oracle group... Thanks
for your help. Looks like we'll need to hit up Oracle support and/or
filter for dates between '0001-01-01' and '9999-12-31'

Pete H

I suspect the problem is operator error rather than Oracle:

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 12 13:26:29 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE datedemo (
2 datecol DATE);

Table created.

SQL> INSERT INTO datedemo
2 (datecol)
3 VALUES
4 (TO_DATE('31-DEC-9999', 'DD-MON-YYYY'));

1 row created

SQL> commit;

Commit complete.

SQL> SELECT TO_CHAR(datecol , 'DD-MON-YYYY')
2 FROM datedemo;

TO_CHAR(DATECOL ,'DD-
--------------------
31-DEC-9999

SQL>
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Knut Stolze]

DA Morgan wrote:

peteh wrote:

snip<<
We have used this technique on over 20 Oracle tables for several months
with no problem. One table in particular fails with a sqlcode -180
(invalid db2 date value). Does anyone why we might be seeing this?.
Could it be that Oracle supports dates outside the range of that which
is supported by db2?
You get the same error if you run the query as is (w/o cursor or the
load)
on the command line? If yes, then you should try to narrow down which
rows are failing and then have a look at the Oracle DATE value there.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Thanks Knut;
Yes - the query fails with or without the load. Turns out that although
Oracle claims to support a max date value of 9999-12-31, we managed to
get a single row into this table that even Oracle can't show us! When
querying Oracle directly with a WHERE clause of "date > '31-DEC-9999'",
we see a single row with a value of '16-APR-39' (?) Go figure... Assume
this is an Orace bug (say it ain't so!). Google shows at least one
other person encountering this mystery in the oracle group... Thanks
for your help. Looks like we'll need to hit up Oracle support and/or
filter for dates between '0001-01-01' and '9999-12-31'

Pete H

I suspect the problem is operator error rather than Oracle:

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 12 13:26:29 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE datedemo (
2 datecol DATE);

Table created.

SQL> INSERT INTO datedemo
2 (datecol)
3 VALUES
4 (TO_DATE('31-DEC-9999', 'DD-MON-YYYY'));

1 row created

SQL> commit;

Commit complete.

SQL> SELECT TO_CHAR(datecol , 'DD-MON-YYYY')
2 FROM datedemo;

TO_CHAR(DATECOL ,'DD-
--------------------
31-DEC-9999

You have a different scenario here. The OP has a query like

SELECT datecol
FROM datedemo
WHERE datecol > '31-DEC-9999'

(whatever date format that is supposed to be)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[peteh]

peteh wrote:

snip<< Yes - the query fails with or without the load. Turns out that although
Oracle claims to support a max date value of 9999-12-31, we managed to
get a single row into this table that even Oracle can't show us! When
querying Oracle directly with a WHERE clause of "date > '31-DEC-9999'",
we see a single row with a value of '16-APR-39' (?) Go figure... Assume
this is an Orace bug (say it ain't so!). Google shows at least one
other person encountering this mystery in the oracle group... Thanks
for your help. Looks like we'll need to hit up Oracle support and/or
filter for dates between '0001-01-01' and '9999-12-31'

Pete H

DA Morgan wrote: I suspect the problem is operator error rather than Oracle:

BIG SNIP> --

Hi Daniel;
Your example seems to have little relevance to the problem I'm seeing.
If you'll notice in my previous post, Oracle returns the row with a
WHERE clause of date > '31-DEC-9999'" and shows a value of '16-APR-39'
in the date that meets this criteria. I stand by my assertion that
this certainly APPEARS to be a bug...

Pete H

[DA Morgan]

Knut Stolze wrote:

DA Morgan wrote:

peteh wrote:

> snip<<
> We have used this technique on over 20 Oracle tables for several months
> with no problem. One table in particular fails with a sqlcode -180
> (invalid db2 date value). Does anyone why we might be seeing this?.
> Could it be that Oracle supports dates outside the range of that which
> is supported by db2?
You get the same error if you run the query as is (w/o cursor or the
load)
on the command line? If yes, then you should try to narrow down which
rows are failing and then have a look at the Oracle DATE value there.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Thanks Knut;
Yes - the query fails with or without the load. Turns out that although
Oracle claims to support a max date value of 9999-12-31, we managed to
get a single row into this table that even Oracle can't show us! When
querying Oracle directly with a WHERE clause of "date > '31-DEC-9999'",
we see a single row with a value of '16-APR-39' (?) Go figure... Assume
this is an Orace bug (say it ain't so!). Google shows at least one
other person encountering this mystery in the oracle group... Thanks
for your help. Looks like we'll need to hit up Oracle support and/or
filter for dates between '0001-01-01' and '9999-12-31'

Pete H

I suspect the problem is operator error rather than Oracle:

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 12 13:26:29 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE datedemo (
2 datecol DATE);

Table created.

SQL> INSERT INTO datedemo
2 (datecol)
3 VALUES
4 (TO_DATE('31-DEC-9999', 'DD-MON-YYYY'));

1 row created

SQL> commit;

Commit complete.

SQL> SELECT TO_CHAR(datecol , 'DD-MON-YYYY')
2 FROM datedemo;

TO_CHAR(DATECOL ,'DD-
--------------------
31-DEC-9999

You have a different scenario here. The OP has a query like

SELECT datecol
FROM datedemo
WHERE datecol > '31-DEC-9999'

(whatever date format that is supposed to be)

A string is not a date. The above query may work with an implicit
type conversion but Oracle clearly states that this is neither
recommended nor guaranteed to work.

But again this seems like operator error. See the following:
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jun 13 09:12:01 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc datedemo
Name Null? Type
----------------------------------------- -------- ------------------
DATECOL DATE

SQL> select * from datedemo;

DATECOL
---------
31-DEC-99

SQL> SELECT datecol
2 FROM datedemo
3 WHERE datecol > TO_DATE('31-DEC-9999');

no rows selected

SQL>

--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[DA Morgan]

peteh wrote:

peteh wrote:

> snip<<
Yes - the query fails with or without the load. Turns out that although
Oracle claims to support a max date value of 9999-12-31, we managed to
get a single row into this table that even Oracle can't show us! When
querying Oracle directly with a WHERE clause of "date > '31-DEC-9999'",
we see a single row with a value of '16-APR-39' (?) Go figure... Assume
this is an Orace bug (say it ain't so!). Google shows at least one
other person encountering this mystery in the oracle group... Thanks
for your help. Looks like we'll need to hit up Oracle support and/or
filter for dates between '0001-01-01' and '9999-12-31'

Pete H

DA Morgan wrote:

I suspect the problem is operator error rather than Oracle:

BIG SNIP> --

Hi Daniel;
Your example seems to have little relevance to the problem I'm seeing.
If you'll notice in my previous post, Oracle returns the row with a
WHERE clause of date > '31-DEC-9999'" and shows a value of '16-APR-39'
in the date that meets this criteria. I stand by my assertion that
this certainly APPEARS to be a bug...

Pete H

1. What version
2. Dates, in Oracle, are not strings ... use TO_DATE
3. Why is this in the DB2 usenet group? If you wish to pursue it lets
be courteous and take it to comp.databases. oracle.server.
--
Daniel A. Morgan
University of Washington
da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Knut Stolze]

DA Morgan wrote:

3. Why is this in the DB2 usenet group? If you wish to pursue it lets
be courteous and take it to comp.databases. oracle.server.

Pete was accessing an Oracle DB through the federated capabilities of DB2
(or rather Information Integrator). So the problem may have been with the
DB2->Oracle wrapper, which we could rule out.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Knut Stolze]

DA Morgan wrote:

SQL> SELECT datecol
2 FROM datedemo
3 WHERE datecol > TO_DATE('31-DEC-9999');

What does the query return if you omit the TO_DATE function call?

I'm asking because there may be an issue in the Oracle wrapper. If so, it
would be good to get it fixed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany