UNLOAD Duplicates on TIME column

Steve Grimes

UNLOAD Duplicates on TIME column
Hello, DB2 V7.1, z/OS 1.4 here.

After our Load utility choked with duplicates from an unloaded (UNLOAD
utility) production table, I tried the following cross-system loader.

EXEC SQL
DECLARE C1 CURSOR FOR
SELECT * FROM DBP1DDF.DBAPROD.SFT_AWAR_DETL
ORDER BY 1, 2, 3, 4, 5, 6, 7
ENDEXEC
LOAD DATA REPLACE INCURSOR(C1) LOG NO NOCOPYPEND
INTO TABLE DBATEST.SFT_AWAR_DETL

It's apparantly choking for the same reason. It seems the table has a
TIME column as one of its primary index columns. The seconds are being
stripped. The data in the unloaded (UNLOAD utility) file is something
like 08:54 AM The duplicate rows have the same hours and minutes,
and differ only by the seconds.

I'm looking at the details of the UNLOAD utility in the manual, and will
probably have to step up to specifying each column to be unloaded, playing
with the TIME EXTERNAL (length) specification to try and get it to work.
My question is this:

Does anyone know of a quicker, easier, more generic work-around?

TIA!

Stg

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: UNLOAD Duplicates on TIME column
(in response to Steve Grimes)
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT cola, ... , char(col_time, ISO) , ...
FROM ...
?

A better option would be to change your default time format to ISO
- but that does depend on whether your installation can handle the
change.

James Campbell

On 30 Nov 2005 at 17:10, [login to unmask email] wrote:

>
>
> Hello, DB2 V7.1, z/OS 1.4 here.
>
> After our Load utility choked with duplicates from an unloaded (UNLOAD utility) production table,
> I tried the following cross-system loader.
>
> EXEC SQL
> DECLARE C1 CURSOR FOR
> SELECT * FROM DBP1DDF.DBAPROD.SFT_AWAR_DETL
> ORDER BY 1, 2, 3, 4, 5, 6, 7
> ENDEXEC
> LOAD DATA REPLACE INCURSOR(C1) LOG NO NOCOPYPEND
> INTO TABLE DBATEST.SFT_AWAR_DETL
>
> It's apparantly choking for the same reason. It seems the table has a TIME column as one of its
> primary index columns. The seconds are being stripped. The data in the unloaded (UNLOAD
> utility) file is something like 08:54 AM The duplicate rows have the same hours and minutes,
> and differ only by the seconds.
>
> I'm looking at the details of the UNLOAD utility in the manual, and will probably have to step up
> to specifying each column to be unloaded, playing with the TIME EXTERNAL (length)
> specification to try and get it to work. My question is this:
>
> Does anyone know of a quicker, easier, more generic work-around?
>
> TIA!
>
> Stg --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L
> list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-
> l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm