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
, constrstocklowqty
, date(constrstocklowdate)
, constrstocklowdur
, constrstocklowqty
, date(constrstockoutdate)
, constrstockoutdur
, maxconstrcovdur
, date(maxconstrcovdate)
from fdr.manu_skustatstatic
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_skustatstatic
data buffer 600
redirect_part_output
/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 14 4405
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
> > 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
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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
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 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
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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
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
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
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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
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 constrstockoutdate
from stsc.skustatstatic
where constrstockoutdate >= TO_DATE('31-DEC-9999');
~
CONSTRSTO
---------
16-APR-39
As you can see, the column "constrstockoutdate" (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
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
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/
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 constrstockoutdate from stsc.skustatstatic where constrstockoutdate >= TO_DATE('31-DEC-9999'); ~ CONSTRSTO --------- 16-APR-39
As you can see, the column "constrstockoutdate" (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.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: uthuras |
last post by:
Greetings all,
Is it possible to have federated db feature implemented among DB2
family? I intend to create federated within DB2 databases. I have 2
databases TestA and TestB. I have some base...
|
by: stevenkblack |
last post by:
I just installed Information Integrator on my machine Windows XP db2
8.2.1. I want to connect to Oracle 9i. The closest thing I saw when
trying to create the wrapper was Oracle8. It says the...
|
by: Michael Rudolph |
last post by:
Hi newsgroup,
I have an issue with the configuration of a DB2 federated database
(WebSphere Information Integrator) in conjunction with the relational
wrapper for Oracle on AIX. DB2 seems to not...
|
by: Michael Rudolph |
last post by:
Hi,
at the moment i try to use the federated database feature (DB2/NT 8.2.3)
to use a remote host db2 (DB2 OS/390 7.1.2).
I am unsure what privileges are needed for the host db2 user.
A...
|
by: P. Adhia |
last post by:
Sorry for quoting an old post and probably I am reading out of context
so my concern is unfounded. But I would appreciate if I can get someone
or Serge to confirm. Also unlike the question asked in...
|
by: Frank Swarbrick |
last post by:
Is Websphere Information Integrator supported on Linux for zSeries?
Here's the deal... We are a VSE mainframe shop. We do not currently have
access to any relational databases from VSE. We use...
|
by: Praveen_db2 |
last post by:
Hi All
DB2 8.1.3,Windows
I have 2 databases in a single instance, say DB_1 and DB_2.I need to query
a table from DB_1 in DB_2.I am try to use a nickname for it.But nickname
creation is not...
|
by: mitek |
last post by:
Hi, All.
Is federated procedures between DB2 databases currently not supported
in WebSphere Federation Server 9.1 ???
I recieve next error when trying to create DB2 federated procedure :
...
|
by: peteh |
last post by:
Hi All;
I have tried to find this info on the IBM site, but alas... We are
running DB2 v9.1.1 and making extensive use of Information Integrator
(now known as Websphere Federation Server?!) to...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |