BMC Load and RI

John Alexander

BMC Load and RI
Hi Colleagues,
I am interested in your opinion & feedback when using BMC Load utility
on a table with RI .

Recently I was testing both BMC & IBM 'Load Resume' on a 20 million row
child table, and found that BMC Load ;
- Will load the data even if the parent keys are missing !!!
- Will always place the table in 'Check Pend' state regardless
Where IBM Load with 'ENFORCE CONSTRAINTS' will save you from both of the
above.

The Elapsed/CPU times in my test were 7.2 min/2.8 min for IBM Load, &
6.6 min/1.5 min for BMC Load.

The slight performance advantage of BMC Load when RI exists, is
completely stripped away when you take into account the time it will
take to run 'Check Data'.

Ps. We are on DB2 z/OS V7 , & BMC Load+ V8.3


Regards
John




***********************************************************
CAUTION: This email and files included in its transmission
are solely intended for the use of the addressee(s) and may
contain information that is confidential and privileged.
If you receive this email in error, please advise us
immediately and delete it without copying the contents
contained within. Woolworths Limited (including its group
of companies) do not accept liability for the views
expressed within or the consequences of any computer
viruses that may be transmitted with this email. The
contents are also subject to copyright. No part of it
should be reproduced, adapted or transmitted without the
written consent of the copyright owner.
***********************************************************

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Raymond Bell

Re: BMC Load and RI
(in response to John Alexander)
Hi John,

This doesn't help your immediate problem but the latest release of
Loadplus, V9.1.00, which came out in August this year supports the
checking of RI constraints during the load. Pretty cool when you
consider we operate outside DB2.

Hope this is at least of some consolation.

Cheers,


Raymond
PS. V9.1.00 of Loadplus is fine with DB2 V7, V8 and V9. Oops, sorry; 9
PPS. I had to remove your original post to slip in under the 500 lines
limit. Although I suspect someone can't count...

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Vickers

Re: BMC Load and RI
(in response to Raymond Bell)
John,
We mainly use unload/loads for data migration, so we will always load the
parent and we turn off image copies and check data and just start
access(force) on the tablespaces when we are done.

But, I found this: I am not dead sure what the BMC manual is getting at
here, kinda sounds like you need to load the parent at the same time, but
also sounds like if you have the high-speed apply piece, it may preserve
RI ? So if you have the High-Speed Apply piece, maybe you could try it
out on a smaller table.

"LOADPLUS has no ENFORCE CONSTRAINTS option for referential integrity
violations. However, for a load job that uses High-speed Apply (an
SQLAPPLY load),
the High-speed Apply architecture preserves referential integrity
relationships.
Therefore, when referential integrity is involved and you specify ORDER
YES,
LOADPLUS sorts your data by table so that LOADPLUS loads parent tables
before
their child tables."

Mark.




Alexander John <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/21/2007 12:27 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
BMC Load and RI






Hi Colleagues,
I am interested in your opinion & feedback when using BMC Load utility on
a table with RI .
Recently I was testing both BMC & IBM ?Load Resume? on a 20 million row
child table, and found that BMC Load ;
- Will load the data even if the parent keys are missing !!!
- Will always place the table in ?Check Pend? state regardless
Where IBM Load with ?ENFORCE CONSTRAINTS? will save you from both of the
above.
The Elapsed/CPU times in my test were 7.2 min/2.8 min for IBM Load, & 6.6
min/1.5 min for BMC Load.
The slight performance advantage of BMC Load when RI exists, is completely
stripped away when you take into account the time it will take to run
?Check Data?.
Ps. We are on DB2 z/OS V7 , & BMC Load+ V8.3

Regards
John

***********************************************************
CAUTION: This email and files included in its transmission
are solely intended for the use of the addressee(s) and may
contain information that is confidential and privileged.
If you receive this email in error, please advise us
immediately and delete it without copying the contents
contained within. Woolworths Limited (including its group
of companies) do not accept liability for the views
expressed within or the consequences of any computer
viruses that may be transmitted with this email. The
contents are also subject to copyright. No part of it
should be reproduced, adapted or transmitted without the
written consent of the copyright owner.
***********************************************************

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Randy Bright

Re: BMC Load and RI
(in response to Mark Vickers)
See Raymond's reply about the RI feature of LoadPlus that was release
with V9.1 in August. Thanks, Raymond. You beat me to the punch.

Maybe it's not too clear from the documentation, but the statement below
about sorting parent before child tables is in the case where you happen
to be loading the parent and child concurrently. It is not meant to
imply you have to load the parent with the child. RI checking will
occur when loading only the child table in SQLAPPLY in all currently
supported release and in V9.1 with standard RESUME YES.

Hope this clarifies. If you have any further questions, don't hesitate
to E-Mail me directly or call our support line.

Randy Bright
Development Architect
DB2 Utilities
BMC Software, Inc.

[login to unmask email]
P.S. I got the same line count issue when I tried to reply. It said my
original reply was 912 lines. I counted approximately 200 lines.

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, December 21, 2007 7:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] BMC Load and RI


John,
We mainly use unload/loads for data migration, so we will always load
the parent and we turn off image copies and check data and just start
access(force) on the tablespaces when we are done.

But, I found this: I am not dead sure what the BMC manual is getting at
here, kinda sounds like you need to load the parent at the same time,
but also sounds like if you have the high-speed apply piece, it may
preserve RI ? So if you have the High-Speed Apply piece, maybe you
could try it out on a smaller table.

"LOADPLUS has no ENFORCE CONSTRAINTS option for referential integrity
violations. However, for a load job that uses High-speed Apply (an
SQLAPPLY load),
the High-speed Apply architecture preserves referential integrity
relationships.
Therefore, when referential integrity is involved and you specify ORDER
YES,
LOADPLUS sorts your data by table so that LOADPLUS loads parent tables
before
their child tables."

Mark.



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

John Alexander

Re: BMC Load and RI
(in response to Randy Bright)
Really appreciate all the feedbacks.

I am not sure if we can run a higher BMC Load version than V83 with DB2
V7, but I will check it out, but even if we can, I do not think our DB2
Sysprog would install it now, he just upgraded all our BMC utilities in
preparation for DB2 V8 upgrade, which is long over due for us!!.

I don't think we are licensed for SQL Apply, but I'll check that as well
, also BMC Load V8.3 gives you error BMC50980E when you include parm
"ENFORCE CONSTRAINTS"

Personally from a data integrity point of view, I think if you go down
the track of 'Start Force' or 'Repair Checkpend' when you are running a
Load utility that will not honour RI, then you are asking for trouble! I
guess its best to be safe than sorry.


Merry Christamas everyone, & best wishes for 2008.


Regards
John



-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email] On Behalf Of Bright, Randy
Sent: Saturday, 22 December 2007 6:13 AM
To: [login to unmask email]
Subject: Re: [DB2-L] BMC Load and RI

See Raymond's reply about the RI feature of LoadPlus
that was release with V9.1 in August. Thanks, Raymond. You beat me to
the punch.

Maybe it's not too clear from the documentation, but the
statement below about sorting parent before child tables is in the case
where you happen to be loading the parent and child concurrently. It is
not meant to imply you have to load the parent with the child. RI
checking will occur when loading only the child table in SQLAPPLY in all
currently supported release and in V9.1 with standard RESUME YES.

Hope this clarifies. If you have any further questions,
don't hesitate to E-Mail me directly or call our support line.

Randy Bright
Development Architect
DB2 Utilities
BMC Software, Inc.

[login to unmask email]
P.S. I got the same line count issue when I tried to
reply. It said my original reply was 912 lines. I counted
approximately 200 lines.


From: DB2 Data Base Discussion List
[mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Friday, December 21, 2007 7:05 AM
To: [login to unmask email]
Subject: Re: [DB2-L] BMC Load and RI


John,
We mainly use unload/loads for data migration, so we
will always load the parent and we turn off image copies and check data
and just start access(force) on the tablespaces when we are done.

But, I found this: I am not dead sure what the BMC
manual is getting at here, kinda sounds like you need to load the parent
at the same time, but also sounds like if you have the high-speed apply
piece, it may preserve RI ? So if you have the High-Speed Apply piece,
maybe you could try it out on a smaller table.

"LOADPLUS has no ENFORCE CONSTRAINTS option for
referential integrity
violations. However, for a load job that uses High-speed
Apply (an SQLAPPLY load),
the High-speed Apply architecture preserves referential
integrity relationships.
Therefore, when referential integrity is involved and
you specify ORDER YES,
LOADPLUS sorts your data by table so that LOADPLUS loads
parent tables before
their child tables."

Mark.


***********************************************************
CAUTION: This email and files included in its transmission
are solely intended for the use of the addressee(s) and may
contain information that is confidential and privileged.
If you receive this email in error, please advise us
immediately and delete it without copying the contents
contained within. Woolworths Limited (including its group
of companies) do not accept liability for the views
expressed within or the consequences of any computer
viruses that may be transmitted with this email. The
contents are also subject to copyright. No part of it
should be reproduced, adapted or transmitted without the
written consent of the copyright owner.
***********************************************************

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Raymond Bell

Re: BMC Load and RI
(in response to John Alexander)
Hey Randy,

Apologies for stealing your thunder. Just got a bit trigger-happy
mentioning the feature. Won't happen again (yeah, right).

John,

I think I mentioned before (not sure) but V9.1 of LoadPlus is fine with
DB2 V7, V8 and 9. So, other pressures permitting, you certainly could
upgrade LoadPlus while at your current DB2 version. Although I suspect
getting the DB2 migration complete is more important - six months to go
and counting...

You're right in that ENFORC(E)ing CONSTRAINTS with LoadPlus V8.3 gives
an error, as it ain't supported 'til V9.1. And yes, better to leave the
decision as to what to do about any potential RI-orphaned child rows
down to the user, rather than just saying, 'Oh, sod it, it'll be OK,
let's -STArt the bugger ACCESS(FORCE and be done with it'. But the
whole question goes away with LoadPlus V9.1.

Oh, and as Randy alluded, there is an excellent 24x7 support line ready
to catch questions like this. You've bought the software, right? So
why not tap the resource? Give them a call, ping an e-mail or do it
online at the Support website. Speaking of the website, there's a
section entitled Product Availability and Compatibility which tells you
what versions of BMC software are OK with what versions of DB2 and z/OS.
Useful stuff.

Anyway, this is getting dangerously close to a vendor plug ('Ooh, isn't
our Support great?', etc) so I'll give it a rest now.

Cheers, and a belated Merry Christmas,


Raymond
PS. Had to cut your latest note as, with yours and mine, DB2-L counted
632 lines. Rubbish...

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
< http://www.idug.org/lsidug > www.idug.org under the Listserv tab. While
at the site, you can also access the IDUG Online Learning Center, Tech
Library and Code Place, see the latest < http://www.idug.org/lsconf >
IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on < http://www.idug.org/lsms > Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms