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

[DA Morgan]

Knut Stolze wrote:

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.

Thanks. It seems likely your analysis is correct.
--
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

[peteh]

DA Morgan wrote:

1. What version Oracle 9.2.0.7.0 on AIX
2. Dates, in Oracle, are not strings ... use TO_DATE Of course I understand this. I have run the following query via
SQLPlus:
select constrstockoutd ate
from stsc.skustatsta tic
where constrstockoutd ate >= TO_DATE('31-DEC-9999');
~
CONSTRSTO
---------
16-APR-39

As you can see, the column "constrstockout date" (defined as an Oracle
DATE), appears to meet criteria suggesting that it is larger than
Oracle's maximum allowable value for a DATE column. The column is
populated by a 3rd party application for which I do not have the code
(we are working with the vendor - who as it turns out is working with
Oracle).
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.

If you will look at the thread history, you will see that the problem
1st manifested itself via DB2 federated access to the data in question.
It was only after seeing funky things in native Oracle queries that I
started questioning Oracle iself. As far as taking the issue to
comp.databases. oracle.server, I hate to x-post and find the discourse
in this group to be slightly more civilized and interested in
problem-solving. We are able to work around the problem in our DB2
environment by filtering on date ranges outside of the year 0001 and
9999 (which might be necessary anyway since Oracle supports negative
year values representing years BC).

Please feel free to post any or all of this thread in
comp.databases. oracle.server

Pete H

[peteh]

Knut Stolze wrote:

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.

Thanks Knut - but I think the wrapper is doing its job throwing a -180
for a non-valid DB2 date/timestamp format (see my post below). The only
mystery to me at this point is how that one row of data greater than
12/31/9999 got into the source dbms.

Pete H

[Serge Rielau]

peteh wrote:

Knut Stolze wrote:

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.

Thanks Knut - but I think the wrapper is doing its job throwing a -180
for a non-valid DB2 date/timestamp format (see my post below). The only
mystery to me at this point is how that one row of data greater than
12/31/9999 got into the source dbms.

Yep. Do you have a corrupted database which has remained undetected, or
was it just a bug (which may or may not have long been fixed) in Oracle
on the INSERT/UPDATE statement.
Not knowing the misc. bits configurations of Oracle. Are sanity bits and
checksums always on in Oracle?
Could it be that e.g. half a page was written... (e.g. DB2 protects
against that by toggling the first and last bit of a page).
I guess punting to the Oracle NG may be the right action.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[DA Morgan]

peteh wrote:

DA Morgan wrote:

1. What version

Oracle 9.2.0.7.0 on AIX

2. Dates, in Oracle, are not strings ... use TO_DATE

Of course I understand this. I have run the following query via
SQLPlus:
select constrstockoutd ate
from stsc.skustatsta tic
where constrstockoutd ate >= TO_DATE('31-DEC-9999');
~
CONSTRSTO
---------
16-APR-39

As you can see, the column "constrstockout date" (defined as an Oracle
DATE), appears to meet criteria suggesting that it is larger than
Oracle's maximum allowable value for a DATE column. The column is
populated by a 3rd party application for which I do not have the code
(we are working with the vendor - who as it turns out is working with
Oracle).

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.

If you will look at the thread history, you will see that the problem
1st manifested itself via DB2 federated access to the data in question.
It was only after seeing funky things in native Oracle queries that I
started questioning Oracle iself. As far as taking the issue to
comp.databases. oracle.server, I hate to x-post and find the discourse
in this group to be slightly more civilized and interested in
problem-solving. We are able to work around the problem in our DB2
environment by filtering on date ranges outside of the year 0001 and
9999 (which might be necessary anyway since Oracle supports negative
year values representing years BC).

Please feel free to post any or all of this thread in
comp.databases. oracle.server

Pete H

Looks like a problem specific to an earlier version of Oracle as you can
see:

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Jun 14 09:50:38 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
2 where datecol >= TO_DATE('31-DEC-9999');

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

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