Jim,

Thanks for the information. Just so I understand it better myself, I ran a little test. I created a SQL view over an existing table in my library. I then saved the view to a save file. I deleted the view. I renamed the table the view was built over. I tried to restore the view from the save file. The restore failed because it couldn’t find the table the view was built over. Next, I restored the object specifying a Defer Id. I renamed the table back to the original name. Then, I used the RSTDFROBJ command specifying the Defer Id I used in the restore command. Then, the view was restored. For this test view, I did not do a join, but I figured it would illustrate the problem on the restore if the table was missing.

So, like you said, the restore wants all of the tables to be there unless you specify the Defer Id on the restore command. Thanks for the help.

Dean Eshleman
Software Development Architect
Everence

1110 North Main Street
PO Box 483
Goshen, IN 46527
(800) 348-7468 ext. 3528
(574) 533-9515 ext. 3528
[Join our Talent Network]<http://www.everence.com/careers>


From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim Oberholtzer
Sent: Monday, November 1, 2021 4:00 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Re: Sql views across libraries and restoring from backup

This email originated outside of Everence. Do not click links or attachments unless you recognize the sender.

SQL will find the index/view it needs regardless of the schema it’s in and use it as the optimizer feels appropriate. My comment is based on your question about a restore. There it matters.

Jim Oberholtzer
Agile Technology Architects



On Nov 1, 2021, at 1:44 PM, Dean Eshleman <dean.eshleman@xxxxxxxxxxxx<mailto:dean.eshleman@xxxxxxxxxxxx>> wrote:

Jim and Rob,

Based on the response that Peter and Mark gave, that would lead me to believe that it really doesn't matter which library contains the SQL view since it won't look for the physicals until someone actually uses the view. Does your experience with restores say something different?


Dean Eshleman
Software Development Architect
Everence

1110 North Main Street
PO Box 483
Goshen, IN 46527
(800) 348-7468 ext. 3528
(574) 533-9515 ext. 3528
[Join our Talent Network]<http://www.everence.com/careers>

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Jim Oberholtzer
Sent: Monday, November 1, 2021 1:47 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: [EXTERNAL] Re: Sql views across libraries and restoring from backup

This email originated outside of Everence. Do not click links or attachments unless you recognize the sender.

Yep they did, and it almost always works! (almost is the key word here)
It still requires multiple passes of the restore.

--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


On Mon, Nov 1, 2021 at 12:41 PM Rob Berendt <rob@xxxxxxxxx<mailto:rob@xxxxxxxxx<mailto:rob@xxxxxxxxx%3cmailto:rob@xxxxxxxxx>>> wrote:

A few releases of the OS ago they added an option to defer on a restore to
basically not make you jump through this hoop.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com<http://www.dekko.com><http://www.dekko.com<http://www.dekko.com>>


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx%3cmailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>>> On Behalf Of
Dean Eshleman
Sent: Monday, November 1, 2021 1:34 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx%3cmailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: Sql views across libraries and restoring from backup

CAUTION: This email originated from outside of the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.


Hi,

I was wondering if SQL views that join two files across libraries have the
same constraints as join logical files when it comes to what library they
should be in so that a restore of the view works successfully. In other
words you should place it the library that is restored last alphabetically
so that both physicals are there when it is restored. I know that an SQL
view shows up on the system as a logical file object, but I also know they
have some differences from logical files. TIA

Dean Eshleman
Software Development Architect
Everence

1110 North Main Street
PO Box 483
Goshen, IN 46527
(800) 348-7468 ext. 3528
(574) 533-9515 ext. 3528
[Join our Talent Network]<http://www.everence.com/careers>

Confidentiality Notice: This information is intended only for the
individual or entity named. If you are not the intended recipient, do not
use or disclose this information. If you received this e-mail in error,
please delete or otherwise destroy it and contact us at (800) 348-7468 so
we can take steps to avoid such transmission errors in the future. Thank
you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l><https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l><https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto:support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com<https://amazon.midrange.com><https://amazon.midrange.com<https://amazon.midrange.com>>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l><https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l><https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto:support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com<https://amazon.midrange.com><https://amazon.midrange.com<https://amazon.midrange.com>>

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l><https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx%3cmailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l><https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto:support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com><https://amazon.midrange.com<https://amazon.midrange.com>>

Confidentiality Notice: This information is intended only for the individual or entity named. If you are not the intended recipient, do not use or disclose this information. If you received this e-mail in error, please delete or otherwise destroy it and contact us at (800) 348-7468 so we can take steps to avoid such transmission errors in the future. Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.