Load in Partitioned TS

Mohammed Nayeem

Load in Partitioned TS
Hi

I was trying to LOAD data in table ( partitoned TS) and needed to REPLACE
existing entire data using LOAD utility with REPLACE option.
REPLACE option deletes existing data before reloading
and during BUILD phase I am getting Unique key violations
and these unique key violations are corrected in INDEXVAL phase.
My question is why I am getting this with REPLACE option which actually
deletes data and it seems it does not deletes index data.
In such cases when it is required to REPLACE data , what is the best solution.
Do I need to drop indexes and re-create/rebuild OR drop table and recreate
it.
When if it arises to do in prod region , then how????

I appreciate your input.

Thanks & Regards
Nayeem



Kurian B

Re: Load in Partitioned TS
(in response to Mohammed Nayeem)
The REPLACE key word should be after the part number.

//DSNUPROC.SYSIN DD *
LOAD DATA INDDN SYSREC00 LOG NO
INTO TABLE creator.Your table
PART 001 RESUME NO REPLACE

Thnaks
Bejoy



From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
11:22 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Fax to:
Subject: Load in Partitioned TS


Hi

I was trying to LOAD data in table ( partitoned TS) and needed to REPLACE
existing entire data using LOAD utility with REPLACE option.
REPLACE option deletes existing data before reloading
and during BUILD phase I am getting Unique key violations
and these unique key violations are corrected in INDEXVAL phase.
My question is why I am getting this with REPLACE option which actually
deletes data and it seems it does not deletes index data.
In such cases when it is required to REPLACE data , what is the best
solution.
Do I need to drop indexes and re-create/rebuild OR drop table and
recreate
it.
When if it arises to do in prod region , then how????

I appreciate your input.

Thanks & Regards
Nayeem








Mohammed Nayeem

Re: Load in Partitioned TS
(in response to Kurian B)
Hi Bejoy

Thanks for early response.
If I define REPLACE keyword according to your syggestion , then will I should
not
get index key violation (right) ???

Thanks
Nayeem

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/26/2000
11:29 AM ---------------------------


Kurian B <[login to unmask email]> on 12/26/2000 11:29:25 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Load in Partitioned TS



The REPLACE key word should be after the part number.

//DSNUPROC.SYSIN DD *
LOAD DATA INDDN SYSREC00 LOG NO
INTO TABLE creator.Your table
PART 001 RESUME NO REPLACE

Thnaks
Bejoy



From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
11:22 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Fax to:
Subject: Load in Partitioned TS


Hi

I was trying to LOAD data in table ( partitoned TS) and needed to REPLACE
existing entire data using LOAD utility with REPLACE option.
REPLACE option deletes existing data before reloading
and during BUILD phase I am getting Unique key violations
and these unique key violations are corrected in INDEXVAL phase.
My question is why I am getting this with REPLACE option which actually
deletes data and it seems it does not deletes index data.
In such cases when it is required to REPLACE data , what is the best
solution.
Do I need to drop indexes and re-create/rebuild OR drop table and
recreate
it.
When if it arises to do in prod region , then how????

I appreciate your input.

Thanks & Regards
Nayeem













RICHARD E MOLERA

Re: Load in Partitioned TS
(in response to Mohammed Nayeem)
Mohammed,

Have you verified that the input file does not contain duplicate key entries?

If duplicate unique key entries are present within the input file, then DB2 is
performing as designed.

Also, if you want to replace the entire tablespace (pre-delete all existing
rows) then the simplest load statement is:

LOAD DATA REPLACE LOG NO INDDN SYSREC00
INTO TABLE creator.table_name

Hope this helps and good luck!

Rick Molera

OS390 DB2 DBA







Mohammed Nayeem <[login to unmask email]> on 12/26/2000 12:22:40 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: RICHARD E MOLERA/SallieMae)
Subject: Load in Partitioned TS



Hi

I was trying to LOAD data in table ( partitoned TS) and needed to REPLACE
existing entire data using LOAD utility with REPLACE option.
REPLACE option deletes existing data before reloading
and during BUILD phase I am getting Unique key violations
and these unique key violations are corrected in INDEXVAL phase.
My question is why I am getting this with REPLACE option which actually
deletes data and it seems it does not deletes index data.
In such cases when it is required to REPLACE data , what is the best solution.
Do I need to drop indexes and re-create/rebuild OR drop table and recreate
it.
When if it arises to do in prod region , then how????

I appreciate your input.

Thanks & Regards
Nayeem








Mohammed Nayeem

Re: Load in Partitioned TS
(in response to RICHARD E MOLERA)
Hi Rick

Thanks for the response.
Pre-deleting is taking hell of time when i try to do using qmf.
So I thought to use REPLACE option in LOAD.
And there are no dup's rec's in th input file and I am trying to replace
existing
data and why should I use PART Keyword any how data will go according to the
definition of clustering index in corresponding partitions. But why i'am getting
unique key violations in BUILD phase.

Thanks
Nayeem



Kurian B

Re: Load in Partitioned TS
(in response to Mohammed Nayeem)
Hi,
I am sorry, i thought you are replacing the partition. Try dropping the
index and load. Thanks



From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
11:35 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Fax to:
Subject: Re: Load in Partitioned TS


Hi Bejoy

Thanks for early response.
If I define REPLACE keyword according to your syggestion , then will I
should
not
get index key violation (right) ???

Thanks
Nayeem

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on
12/26/2000
11:29 AM ---------------------------


Kurian B <[login to unmask email]> on 12/26/2000 11:29:25 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Load in Partitioned TS



The REPLACE key word should be after the part number.

//DSNUPROC.SYSIN DD *
LOAD DATA INDDN SYSREC00 LOG NO
INTO TABLE creator.Your table
PART 001 RESUME NO REPLACE

Thnaks
Bejoy



From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
11:22 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Fax to:
Subject: Load in Partitioned TS


Hi

I was trying to LOAD data in table ( partitoned TS) and needed to REPLACE
existing entire data using LOAD utility with REPLACE option.
REPLACE option deletes existing data before reloading
and during BUILD phase I am getting Unique key violations
and these unique key violations are corrected in INDEXVAL phase.
My question is why I am getting this with REPLACE option which actually
deletes data and it seems it does not deletes index data.
In such cases when it is required to REPLACE data , what is the best
solution.
Do I need to drop indexes and re-create/rebuild OR drop table and
recreate
it.
When if it arises to do in prod region , then how????

I appreciate your input.

Thanks & Regards
Nayeem








the










RICHARD E MOLERA

Re: Load in Partitioned TS
(in response to Kurian B)
Mohammed,

I agree with your approach, totally! Using the REPLACE keyword is the way to go
if you want to pre-delete all the rows of the table ( regardless of whether the
tablespace is partitioned or not - Just make sure a segmented or simple
tablespace contains only ONE table before using this option ).

Anyway, if I were you I would write the discarded "duplicate" rows to a file and
review them to ensure they are not duplicates.

//SYSDISC DD
DSN=dsn.name,DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(1000,100),RLSE),UNIT=sysda

Also, another thing to remember / check: A table can have multiple unique
indices so the duplicate key problem may be occurring on another index defined
as unique; not the index you think it is occurring on. Check all indices
defined on the table and then verify again that the data does not violate any of
the unique index definitions.

Good luck,

Rick






Mohammed Nayeem <[login to unmask email]> on 12/26/2000 12:50:23 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: RICHARD E MOLERA/SallieMae)
Subject: Re: Load in Partitioned TS



Hi Rick

Thanks for the response.
Pre-deleting is taking hell of time when i try to do using qmf.
So I thought to use REPLACE option in LOAD.
And there are no dup's rec's in th input file and I am trying to replace
existing
data and why should I use PART Keyword any how data will go according to the
definition of clustering index in corresponding partitions. But why i'am getting
unique key violations in BUILD phase.

Thanks
Nayeem








Mohammed Nayeem

Re: Load in Partitioned TS
(in response to RICHARD E MOLERA)
Yep , only one table exist in partitioned TS and (1 partitioning index + 5
other indexes ).
My question was with REPLACE option of LOAD , it look like it is not deleting
index data but deleting only table data from the TS before RELOAD phase.
Might be b'cos indexes are in diff TS's , so for REPLACing entire data
how to empty index entries before Reload goes?????

Thanks
Nayeem



Basheer Shaik

Re: Load in Partitioned TS
(in response to Kurian B)
HI There

I am getting Lots Of mail about DB2.. PLEASE Remove my e-mail Id from List.

Thanks Much....
Bye

>From: RICHARD E MOLERA <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Load in Partitioned TS
>Date: Tue, 26 Dec 2000 13:08:56 -0500
>
>Mohammed,
>
>I agree with your approach, totally! Using the REPLACE keyword is the way
>to go
>if you want to pre-delete all the rows of the table ( regardless of
>whether the
>tablespace is partitioned or not - Just make sure a segmented or simple
>tablespace contains only ONE table before using this option ).
>
>Anyway, if I were you I would write the discarded "duplicate" rows to a
>file and
>review them to ensure they are not duplicates.
>
>//SYSDISC DD
>DSN=dsn.name,DISP=(NEW,CATLG,CATLG),SPACE=(CYL,(1000,100),RLSE),UNIT=sysda
>
>Also, another thing to remember / check: A table can have multiple unique
>indices so the duplicate key problem may be occurring on another index
>defined
>as unique; not the index you think it is occurring on. Check all indices
>defined on the table and then verify again that the data does not violate
>any of
>the unique index definitions.
>
>Good luck,
>
>Rick
>
>
>
>
>
>
>Mohammed Nayeem <[login to unmask email]> on 12/26/2000 12:50:23 PM
>
>Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>To: [login to unmask email]
>cc: (bcc: RICHARD E MOLERA/SallieMae)
>Subject: Re: Load in Partitioned TS
>
>
>
>Hi Rick
>
>Thanks for the response.
>Pre-deleting is taking hell of time when i try to do using qmf.
>So I thought to use REPLACE option in LOAD.
>And there are no dup's rec's in th input file and I am trying to replace
>existing
>data and why should I use PART Keyword any how data will go according to
>the
>definition of clustering index in corresponding partitions. But why i'am
>getting
>unique key violations in BUILD phase.
>
>Thanks
>Nayeem
>
>
>
>the
>
>
>
>
>
>
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com



Kurian B

Re: Load in Partitioned TS
(in response to Mohammed Nayeem)
With REPLACE option it will delete the indexes and rebuild after the load,
if you want to replace the entire table. It works fine several times with
me. I did't understand "Might be b'cos indexes are in diff TS's... " I
hope this may be diff. vsam datasets(index space). To me it does't matter,
b'cos you created index against the table. Correct me if i am wrong. If
you still have problem try dropping the indexes and load.
Thanks
Bejoy



From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
12:27 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Fax to:
Subject: Re: Load in Partitioned TS


Yep , only one table exist in partitioned TS and (1 partitioning index + 5
other indexes ).
My question was with REPLACE option of LOAD , it look like it is not
deleting
index data but deleting only table data from the TS before RELOAD phase.
Might be b'cos indexes are in diff TS's , so for REPLACing entire data
how to empty index entries before Reload goes?????

Thanks
Nayeem








Sanjeev (CTS) S

Re: Load in Partitioned TS
(in response to Basheer Shaik)
Hi all,
I think there is something which is missing when it is looked. Nayeem, try
using the discard dataset and have a look at the discard dataset output to
get which all rows are violating the uniqueness. I am sure there are lots of
fixes are available for lots of problem but this looks silly to me that
REPLACE will remove the data from tablespace and not from index. IBM is
never famous for designing such an inconsistent thing i.e DB2, which is
highly consistent and when it is not, it forces us to take some action.
Theoritically and practically....it doesn't make sense to me removing the
tablespace data and not the index data using REPLACE option. There is not a
single place where index makes sense if it is not associated with any
table/tablespace. Index is nothing without data. It is in the different
dataset by design and design also includes that it is made from tablespace
data.
You can also try dropping all the unique indexes, use LOAD REPLACE and then
create all the unique indexes. Let's see what happens when we do this.
Now let us look at the possibilities, which i am not sure if it can happen
but few guesses--: if there was inconsistency in the index and tablespace
data and table was started with access force(someone may not be aware of
this when it was done) . And you are trying to use LOAD REPLACE, is it
possible that index entries corresponding to only the data will be removed.
I am not sure but i think without looking the record by records DB2 formats
the pages if LOAD REPLACE is used.

Please correct me if i am wrong and let us come out with something concrete
on this. One more thing is that Partitioned tablespace only contains one
table(with multiple partitions).

Happy Holidays and Happy New Year.

HTH
Regards
Sanjeev

> -----Original Message-----
> From: Kurian B [SMTP:[login to unmask email]
> Sent: Wednesday, December 27, 2000 1:10 AM
> To: [login to unmask email]
> Subject: Re: Load in Partitioned TS
>
> With REPLACE option it will delete the indexes and rebuild after the load,
> if you want to replace the entire table. It works fine several times with
> me. I did't understand "Might be b'cos indexes are in diff TS's... "
> I
> hope this may be diff. vsam datasets(index space). To me it does't matter,
> b'cos you created index against the table. Correct me if i am wrong. If
> you still have problem try dropping the indexes and load.
> Thanks
> Bejoy
>
>
>
> From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
> 12:27 PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
> Fax to:
> Subject: Re: Load in Partitioned TS
>
>
> Yep , only one table exist in partitioned TS and (1 partitioning index + 5
> other indexes ).
> My question was with REPLACE option of LOAD , it look like it is not
> deleting
> index data but deleting only table data from the TS before RELOAD phase.
> Might be b'cos indexes are in diff TS's , so for REPLACing entire data
> how to empty index entries before Reload goes?????
>
> Thanks
> Nayeem
>
>
>
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Mohammed Nayeem

Re: Load in Partitioned TS
(in response to Sanjeev (CTS) S)
Thanks Sanjeev ,

Problem is solved.

One of the index's was not correct ,i .e for uniqueness one more column on that
particular index was required and that was missing and that's why I was getting
unique index key violations.
Corrected that index and reloaded with replace option successfully.

Thanks and wish you all a happy and properous new year 2001.

With regards

Nayeem

---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/28/2000
08:31 AM ---------------------------


"S, Sanjeev (CTS)" <[login to unmask email]> on 12/26/2000 10:52:00 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Load in Partitioned TS



Hi all,
I think there is something which is missing when it is looked. Nayeem, try
using the discard dataset and have a look at the discard dataset output to
get which all rows are violating the uniqueness. I am sure there are lots of
fixes are available for lots of problem but this looks silly to me that
REPLACE will remove the data from tablespace and not from index. IBM is
never famous for designing such an inconsistent thing i.e DB2, which is
highly consistent and when it is not, it forces us to take some action.
Theoritically and practically....it doesn't make sense to me removing the
tablespace data and not the index data using REPLACE option. There is not a
single place where index makes sense if it is not associated with any
table/tablespace. Index is nothing without data. It is in the different
dataset by design and design also includes that it is made from tablespace
data.
You can also try dropping all the unique indexes, use LOAD REPLACE and then
create all the unique indexes. Let's see what happens when we do this.
Now let us look at the possibilities, which i am not sure if it can happen
but few guesses--: if there was inconsistency in the index and tablespace
data and table was started with access force(someone may not be aware of
this when it was done) . And you are trying to use LOAD REPLACE, is it
possible that index entries corresponding to only the data will be removed.
I am not sure but i think without looking the record by records DB2 formats
the pages if LOAD REPLACE is used.

Please correct me if i am wrong and let us come out with something concrete
on this. One more thing is that Partitioned tablespace only contains one
table(with multiple partitions).

Happy Holidays and Happy New Year.

HTH
Regards
Sanjeev

> -----Original Message-----
> From: Kurian B [SMTP:[login to unmask email]
> Sent: Wednesday, December 27, 2000 1:10 AM
> To: [login to unmask email]
> Subject: Re: Load in Partitioned TS
>
> With REPLACE option it will delete the indexes and rebuild after the load,
> if you want to replace the entire table. It works fine several times with
> me. I did't understand "Might be b'cos indexes are in diff TS's... "
> I
> hope this may be diff. vsam datasets(index space). To me it does't matter,
> b'cos you created index against the table. Correct me if i am wrong. If
> you still have problem try dropping the indexes and load.
> Thanks
> Bejoy
>
>
>
> From: Mohammed Nayeem <[login to unmask email]>@RYCI.COM> on 12/26/2000
> 12:27 PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc:
> Fax to:
> Subject: Re: Load in Partitioned TS
>
>
> Yep , only one table exist in partitioned TS and (1 partitioning index + 5
> other indexes ).
> My question was with REPLACE option of LOAD , it look like it is not
> deleting
> index data but deleting only table data from the TS before RELOAD phase.
> Might be b'cos indexes are in diff TS's , so for REPLACing entire data
> how to empty index entries before Reload goes?????
>
> Thanks
> Nayeem
>
>
>
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------

This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply e-mail
and
destroy all copies of the original message. Any unauthorised review, use,
disclosure,
dissemination, forwarding, printing or copying of this email or any action taken
in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------