Deleting records while loading

Shankar Mridha

Deleting records while loading
Hi All,
I am facing a problem while loading data from flat files to DB2 for OS/390.
I am having three tables A,B and C in the tablespace tblspc1. table A and C
are independent and table B has a referential integrity with foreign keys
referncing tables A and C resp. When we load A it gave copy pending status.
then we load C it also gave us copy pending status. Then when we tried
loading table B it didnt loaded a single row b'cos of referntial integrity.
ANd when we look into table A there were no records in it i.e. all the rows
from the table is deleted . The job by which we ran the load job tells us
that all the records are deleted from the tablespace . We are using the
following parameters and we are using the IBM Load utility to load the data.

LOAD DATA
REPLACE
LOG NO

So can anyone give me some clue as to what should be corrected to get my
work done..
Thanks
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428



Ruediger Kurtz

Re: Deleting records while loading
(in response to Shankar Mridha)
Shankar,

you are aware of the fact that 'LOAD REPLACE' works on a
tablespace-basis, not on a table-basis; thus, loading Table A using LOAD
REPLACE deletes all records in ANY table in the tablespace; if you check
the output from "LOAD TABLE A" you should see that all records had been
loaded successfully (most likely anyway). Then a "LOAD TABLE B REPLACE"
deletes all records from the tablespace and loads the records into table
b and so forth.
You should load table A using LOAD REPLACE and on all other LOAD-Jobs
LOAD RESUME YES would be advisable.

Hth Ruediger Kurtz


--
>"""""<
!_ _!
(o) (o)
oOOO--(_)--OOOo------------------------------------------------------*
* *
* Rüdiger Kurtz *
* *
* HUK-Coburg Phone: 09561/96-3914 *
* Versicherungen-Bausparen Fax : 09561/96-3678 *
* Abt. Informatik - Betrieb (Datenbank-Administration) *
* Bahnhofsplatz Mail: [login to unmask email] *
* 96450 Coburg *
* *
* .oooO *
*--( )--Oooo.------------------------------------------------------*
\ ( ( )
\_) ) /
(_/



teldb2kals

Re: Deleting records while loading
(in response to Ruediger Kurtz)
Shankar,

If the tablespace is empty initially, load table A first with LOAD
REPLACE. If specify LOG NO, the tablespace is set to copypending. If u
dont want the copypending status set, use NOCOPYPEND option. Then load
tables C and B with RESUME YES and REPLACE NO options.

When u load with REPLACE, it replaces all the existing rows in the
tablespace. So when u do a second load, the data loaded initially will
be lost. RESUME YES tells DB2 that u r trying to load into a non-empty
tablespace.

Please refer to the Utilities Manual for more details on LOAD.

Cheers,
Kals

-----Original Message-----
From: Shankar Mridha [SMTP:[login to unmask email]
Sent: Monday, December 17, 2001 5:15 PM
To: [login to unmask email]
Subject: Deleting records while loading
Importance: High

Hi All,
I am facing a problem while loading data from flat files to DB2 for
OS/390. I am having three tables A,B and C in the tablespace tblspc1.
table A and C are independent and table B has a referential integrity
with foreign keys referncing tables A and C resp. When we load A it
gave copy pending status. then we load C it also gave us copy pending
status. Then when we tried loading table B it didnt loaded a single row
b'cos of referntial integrity. ANd when we look into table A there were
no records in it i.e. all the rows from the table is deleted . The job
by which we ran the load job tells us that all the records are deleted
from  the tablespace . We are using the following parameters and we are
using the IBM Load utility to load the data.
LOAD DATA  
REPLACE    
LOG NO     
 
So can anyone give me some clue as to what should be corrected to get
my work done..
Thanks
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428



----------------
Powered by telstra.com



Vijayababu Sriramulu

Re: Deleting records while loading
(in response to teldb2kals)
Hi shankar,

Can you please check if you have ctreated all your tables in a SIMPLE
TABLESPACE.

Vijay




teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Re: Deleting records while loading
Data Base
Discussion
List
<[login to unmask email]
M>


12/17/01 01:05
PM
Please respond
to DB2 Data
Base
Discussion
List





Shankar,

If the tablespace is empty initially, load table A first with LOAD
REPLACE. If specify LOG NO, the tablespace is set to copypending. If u
dont want the copypending status set, use NOCOPYPEND option. Then load
tables C and B with RESUME YES and REPLACE NO options.

When u load with REPLACE, it replaces all the existing rows in the
tablespace. So when u do a second load, the data loaded initially will
be lost. RESUME YES tells DB2 that u r trying to load into a non-empty
tablespace.

Please refer to the Utilities Manual for more details on LOAD.

Cheers,
Kals

-----Original Message-----
From: Shankar Mridha [SMTP:[login to unmask email]
Sent: Monday, December 17, 2001 5:15 PM
To: [login to unmask email]
Subject: Deleting records while loading
Importance: High

Hi All,
I am facing a problem while loading data from flat files to DB2 for
OS/390. I am having three tables A,B and C in the tablespace tblspc1.
table A and C are independent and table B has a referential integrity
with foreign keys referncing tables A and C resp. When we load A it
gave copy pending status. then we load C it also gave us copy pending
status. Then when we tried loading table B it didnt loaded a single row
b'cos of referntial integrity. ANd when we look into table A there were
no records in it i.e. all the rows from the table is deleted . The job
by which we ran the load job tells us that all the records are deleted
from  the tablespace . We are using the following parameters and we are
using the IBM Load utility to load the data.
LOAD DATA
REPLACE
LOG NO

So can anyone give me some clue as to what should be corrected to get
my work done..
Thanks
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428



----------------
Powered by telstra.com



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]




********************************************
This electronic mail message is intended solely for the named recipients
and may contain confidential and proprietary business information of eFunds
Corporation and all its subsidiaries. If you are not a named recipient,
please notify the sender immediately. You may not disclose the contents to
any other person; use this electronic mail message or its contents for any
other purpose; or further store or copy its contents in any medium
********************************************


Vijayababu Sriramulu

Re: Deleting records while loading
(in response to Vijayababu Sriramulu)
Shankar,

Try to create all your tables in a SEGMENTED TABLESPACE. Do the same
process as you did before. This is probably going to resolve your problem.

Regards
Vijay





teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Re: Deleting records while loading
Data Base
Discussion
List
<[login to unmask email]
M>


12/17/01 01:05
PM
Please respond
to DB2 Data
Base
Discussion
List





Shankar,

If the tablespace is empty initially, load table A first with LOAD
REPLACE. If specify LOG NO, the tablespace is set to copypending. If u
dont want the copypending status set, use NOCOPYPEND option. Then load
tables C and B with RESUME YES and REPLACE NO options.

When u load with REPLACE, it replaces all the existing rows in the
tablespace. So when u do a second load, the data loaded initially will
be lost. RESUME YES tells DB2 that u r trying to load into a non-empty
tablespace.

Please refer to the Utilities Manual for more details on LOAD.

Cheers,
Kals

-----Original Message-----
From: Shankar Mridha [SMTP:[login to unmask email]
Sent: Monday, December 17, 2001 5:15 PM
To: [login to unmask email]
Subject: Deleting records while loading
Importance: High

Hi All,
I am facing a problem while loading data from flat files to DB2 for
OS/390. I am having three tables A,B and C in the tablespace tblspc1.
table A and C are independent and table B has a referential integrity
with foreign keys referncing tables A and C resp. When we load A it
gave copy pending status. then we load C it also gave us copy pending
status. Then when we tried loading table B it didnt loaded a single row
b'cos of referntial integrity. ANd when we look into table A there were
no records in it i.e. all the rows from the table is deleted . The job
by which we ran the load job tells us that all the records are deleted
from  the tablespace . We are using the following parameters and we are
using the IBM Load utility to load the data.
LOAD DATA
REPLACE
LOG NO

So can anyone give me some clue as to what should be corrected to get
my work done..
Thanks
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428



----------------
Powered by telstra.com



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]




********************************************
This electronic mail message is intended solely for the named recipients
and may contain confidential and proprietary business information of eFunds
Corporation and all its subsidiaries. If you are not a named recipient,
please notify the sender immediately. You may not disclose the contents to
any other person; use this electronic mail message or its contents for any
other purpose; or further store or copy its contents in any medium
********************************************


Vijayababu Sriramulu

Re: Deleting records while loading
(in response to Vijayababu Sriramulu)
Sorry shankar. Looks like SEGMENTED TABLESPACE also does the same thing.
Deletes all the rows from all the tables in that tablespace. As Kals say,
try using the RESUME YES options.

Sorry for the inconvenience.





teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Re: Deleting records while loading
Data Base
Discussion
List
<[login to unmask email]
M>


12/17/01 01:05
PM
Please respond
to DB2 Data
Base
Discussion
List





Shankar,

If the tablespace is empty initially, load table A first with LOAD
REPLACE. If specify LOG NO, the tablespace is set to copypending. If u
dont want the copypending status set, use NOCOPYPEND option. Then load
tables C and B with RESUME YES and REPLACE NO options.

When u load with REPLACE, it replaces all the existing rows in the
tablespace. So when u do a second load, the data loaded initially will
be lost. RESUME YES tells DB2 that u r trying to load into a non-empty
tablespace.

Please refer to the Utilities Manual for more details on LOAD.

Cheers,
Kals

-----Original Message-----
From: Shankar Mridha [SMTP:[login to unmask email]
Sent: Monday, December 17, 2001 5:15 PM
To: [login to unmask email]
Subject: Deleting records while loading
Importance: High

Hi All,
I am facing a problem while loading data from flat files to DB2 for
OS/390. I am having three tables A,B and C in the tablespace tblspc1.
table A and C are independent and table B has a referential integrity
with foreign keys referncing tables A and C resp. When we load A it
gave copy pending status. then we load C it also gave us copy pending
status. Then when we tried loading table B it didnt loaded a single row
b'cos of referntial integrity. ANd when we look into table A there were
no records in it i.e. all the rows from the table is deleted . The job
by which we ran the load job tells us that all the records are deleted
from  the tablespace . We are using the following parameters and we are
using the IBM Load utility to load the data.
LOAD DATA
REPLACE
LOG NO

So can anyone give me some clue as to what should be corrected to get
my work done..
Thanks
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428



----------------
Powered by telstra.com



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]




********************************************
This electronic mail message is intended solely for the named recipients
and may contain confidential and proprietary business information of eFunds
Corporation and all its subsidiaries. If you are not a named recipient,
please notify the sender immediately. You may not disclose the contents to
any other person; use this electronic mail message or its contents for any
other purpose; or further store or copy its contents in any medium
********************************************


Sanjeev (CTS) S

Re: Deleting records while loading
(in response to Shankar Mridha)
Other than what Ruediger and Kals have described, i would like to add one
more point. It looks from your question that you are thinking RI to be
responsible for all these results. If RI was responsible it would have put
the table in CHECK PENDING not the COPY PENDING. So, COPY PENDING after Load
comes when we specify LOG NO. Other problems must already have been handled
after both the replies by Ruediger and Kals.

With Best Regards,
Sanjeev



> -----Original Message-----
> From: Shankar Mridha [SMTP:[login to unmask email]
> Sent: Monday, December 17, 2001 11:45 AM
> To: [login to unmask email]
> Subject: Deleting records while loading
> Importance: High
>
> Hi All,
> I am facing a problem while loading data from flat files to DB2 for
> OS/390. I am having three tables A,B and C in the tablespace tblspc1.
> table A and C are independent and table B has a referential integrity with
> foreign keys referncing tables A and C resp. When we load A it gave copy
> pending status. then we load C it also gave us copy pending status. Then
> when we tried loading table B it didnt loaded a single row b'cos of
> referntial integrity. ANd when we look into table A there were no records
> in it i.e. all the rows from the table is deleted . The job by which we
> ran the load job tells us that all the records are deleted from the
> tablespace . We are using the following parameters and we are using the
> IBM Load utility to load the data.
> LOAD DATA
> REPLACE
> LOG NO
>
> So can anyone give me some clue as to what should be corrected to get my
> work done..
> Thanks
> Shankar Mridha
> Senior Consultant-Somerfield Project
> Zensar Technologies Limited
> Mile Post # 4, Pune-Nagar Road
> Pune - 411014
> Ph: +91-20-6633711-2 Ext 2139 (Off)
> Ph: +91-20-6632019 (Res)
> Mobile: +91-98220-66428
>
>
>

Shankar Mridha

Re: Deleting records while loading
(in response to Vijayababu Sriramulu)
Thanks for all your valuable responses. It really helped with what Kals has
said.
Thanks again.
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428


-----Original Message-----
From: Vijayababu Sriramulu [mailto:[login to unmask email]
Sent: Monday, December 17, 2001 2:01 PM
To: [login to unmask email]
Subject: Re: Deleting records while loading


Sorry shankar. Looks like SEGMENTED TABLESPACE also does the same thing.
Deletes all the rows from all the tables in that tablespace. As Kals say,
try using the RESUME YES options.

Sorry for the inconvenience.






teldb2kals

<[login to unmask email] To: [login to unmask email]

LSTRA.COM> cc:

Sent by: DB2 Subject: Re: Deleting records
while loading
Data Base

Discussion

List

<[login to unmask email]

M>





12/17/01 01:05

PM

Please respond

to DB2 Data

Base

Discussion

List








Shankar,

If the tablespace is empty initially, load table A first with LOAD
REPLACE. If specify LOG NO, the tablespace is set to copypending. If u
dont want the copypending status set, use NOCOPYPEND option. Then load
tables C and B with RESUME YES and REPLACE NO options.

When u load with REPLACE, it replaces all the existing rows in the
tablespace. So when u do a second load, the data loaded initially will
be lost. RESUME YES tells DB2 that u r trying to load into a non-empty
tablespace.

Please refer to the Utilities Manual for more details on LOAD.

Cheers,
Kals

-----Original Message-----
From: Shankar Mridha [SMTP:[login to unmask email]
Sent: Monday, December 17, 2001 5:15 PM
To: [login to unmask email]
Subject: Deleting records while loading
Importance: High

Hi All,
I am facing a problem while loading data from flat files to DB2 for
OS/390. I am having three tables A,B and C in the tablespace tblspc1.
table A and C are independent and table B has a referential integrity
with foreign keys referncing tables A and C resp. When we load A it
gave copy pending status. then we load C it also gave us copy pending
status. Then when we tried loading table B it didnt loaded a single row
b'cos of referntial integrity. ANd when we look into table A there were
no records in it i.e. all the rows from the table is deleted . The job
by which we ran the load job tells us that all the records are deleted
from  the tablespace . We are using the following parameters and we are
using the IBM Load utility to load the data.
LOAD DATA
REPLACE
LOG NO

So can anyone give me some clue as to what should be corrected to get
my work done..
Thanks
Shankar Mridha
Senior Consultant-Somerfield Project
Zensar Technologies Limited
Mile Post # 4, Pune-Nagar Road
Pune - 411014
Ph: +91-20-6633711-2 Ext 2139 (Off)
Ph: +91-20-6632019 (Res)
Mobile: +91-98220-66428



----------------
Powered by telstra.com



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]




********************************************
This electronic mail message is intended solely for the named recipients
and may contain confidential and proprietary business information of eFunds
Corporation and all its subsidiaries. If you are not a named recipient,
please notify the sender immediately. You may not disclose the contents to
any other person; use this electronic mail message or its contents for any
other purpose; or further store or copy its contents in any medium
********************************************


http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]