<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma",sans-serif;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma",sans-serif;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle23
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle24
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle25
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle26
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle27
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="color:#1F497D">Yea, I know what you are talking about on the reward/effort cursor/select/choose thing. I attacked our month end run changing selects to chooses, (even chooses on un-indexed items, with a remote DB, it pays
to be as selective as possible with what you throw across the wire). (We front end our qtp/quiz calls with a shell wrapper that both hides where the quiz/qtp are running from, as well as giving a start/stop time log, so I knew where to target). Took the
thing down from 14 hours to just over 40 minutes, without changing the core logic in any of the code, just shifting selects into chooses where possible, and changing “select” on compound statements into a set of<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">“select file if” statements. (You can choose on the 1<sup>st</sup> file in the access, and continue with a “select if” or set of “select file if” statements after that).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Times change, rules change, but the rule “used to be”<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Select fires after a record complex is built, (header, detail, reference things).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Select File fires as the file is referenced, and aborts building the rest of the complex.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">So if you start at order_header, link over to order_detail, then to the product master,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">A simple select fires after you have gathered all 3 records. (Send me all the data that I may throw away the 99% I don’t want).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">A choose on order_header only returns orders of interest (say status in “A”).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">A select order_detail will short circuit at order_detail and not grab the product record if the order_detail filters out.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">At the lowest level (product master) it doesn’t really matter if you “select file if” or “select if”, by that time<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">You have built the entire record complex.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Generally pays to choose up front, and select/short circuit building the record complex as quickly as possible, esp. with a non-local database.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Of course, doing something like this “a decade or so ago” leaves you open to the question
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">“What have you done for me, LATELY”.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">IT is a treadmill, it will throw you off if you don’t keep up with the times.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Peter Hofstaetter [mailto:Peter.Hofstaetter@fujixerox.com]
<br>
<b>Sent:</b> Tuesday, November 15, 2016 4:41 PM<br>
<b>To:</b> Ken Langendock <Ken@Langendock.com>; Herald Kaffka <Herald.Kaffka@westfraser.com>; powerh-l@lists.sowder.com<br>
<b>Subject:</b> RE: Oracle 12c<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D">Yes, I know but that is a lot more work and not sure how long we will have the app for. Where performance has been that bad and choose hasn’t helped that much I have replaced the access with SQL.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma",sans-serif">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma",sans-serif"> Ken Langendock [<a href="mailto:Ken@Langendock.com">mailto:Ken@Langendock.com</a>]
<br>
<b>Sent:</b> Wednesday, 16 November 2016 8:13 AM<br>
<b>To:</b> Peter Hofstaetter; 'Herald Kaffka'; <a href="mailto:powerh-l@lists.sowder.com">
powerh-l@lists.sowder.com</a><br>
<b>Subject:</b> RE: Oracle 12c<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">Peter<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">If you are running PH against an Oracle DB, you should be changing all your access statements to CURSOR/FETCH statements.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">WAY faster.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> Peter Hofstaetter [<a href="mailto:Peter.Hofstaetter@fujixerox.com">mailto:Peter.Hofstaetter@fujixerox.com</a>]
<br>
<b>Sent:</b> November 15, 2016 3:42 PM<br>
<b>To:</b> Herald Kaffka <<a href="mailto:Herald.Kaffka@westfraser.com">Herald.Kaffka@westfraser.com</a>>; Ken Langendock <<a href="mailto:Ken@Langendock.com">Ken@Langendock.com</a>>;
<a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a><br>
<b>Subject:</b> RE: Oracle 12c<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D">Thanks guys.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D">You have been a great help.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D">And for Herald, yes we are running a separate DB server on Linux and I am aware of the difference between choose and select. It’s a great trick using choose. Most people don’t even realise you can
choose non index fields to offload the selection process onto the DB server instead of locally by Powerhouse. It has been my mission to improve the performance of all the code written to replace as many selects as possible particularly now that the application
has been running for so long to start seeing the difference with larger table sizes. In some cases reducing reports from minutes to run down to seconds.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma",sans-serif">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma",sans-serif"> Herald Kaffka [<a href="mailto:Herald.Kaffka@westfraser.com">mailto:Herald.Kaffka@westfraser.com</a>]
<br>
<b>Sent:</b> Wednesday, 16 November 2016 1:39 AM<br>
<b>To:</b> Ken Langendock; Peter Hofstaetter; <a href="mailto:powerh-l@lists.sowder.com">
powerh-l@lists.sowder.com</a><br>
<b>Subject:</b> RE: Oracle 12c<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">You may be right.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">You made me go look/check. Thought my dev environments had been upgraded to 12C as part<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Of a machine move/change awhile back, but turns out they are actually still on 11.2.0.4.0.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Application here is a relatively simple (but large) “text based” app. (No BLOBS/CLOBS etal, just numbers, dates, char/varchar fields).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Remember doing some dancing with dates on the migration to oracle (stored as nulls, with the zero’s being used as a runtime replacement within the app, but nothing else “fancy”).<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D">Doubt we will ever go to 12c at this point, application is scheduled to replace by a commercial package within the next year, will probably rest “as is” until then…<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> <a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com">
powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a> [<a href="mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com">mailto:powerh-l-bounces+herald.kaffka=westfraser.com@lists.sowder.com</a>]
<b>On Behalf Of </b>Ken Langendock<br>
<b>Sent:</b> Tuesday, November 15, 2016 8:10 AM<br>
<b>To:</b> 'Peter Hofstaetter' <<a href="mailto:Peter.Hofstaetter@fujixerox.com">Peter.Hofstaetter@fujixerox.com</a>>;
<a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a><br>
<b>Subject:</b> RE: Oracle 12c<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-CA">Do NOT upgrade to Oracle 12C. <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">PH does not work with that version. It does not even talk to it.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">We spent weeks trying to get it to work.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA">Until Unicom gets off their collective butts (excessive fee collection) and releases a new version of PH, stay on Oracle 11G.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> <a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com">
powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</a> [<a href="mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com">mailto:powerh-l-bounces+ken.langendock=rogers.com@lists.sowder.com</a>]
<b>On Behalf Of </b>Peter Hofstaetter<br>
<b>Sent:</b> November 14, 2016 7:32 PM<br>
<b>To:</b> <a href="mailto:powerh-l@lists.sowder.com">powerh-l@lists.sowder.com</a><br>
<b>Subject:</b> Oracle 12c<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-CA"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU">We are running PowerHouse 8.43.D1 on Solaris 10 using the 9.2 client and have upgraded to Oracle 11g and all seems to be OK for almost a year.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU">We are now looking at migrating to Oracle 12c but found that we need to upgrade client as 9.2 will not talk to 12c database which also means that we need to upgrade to Solaris 11 to use the newer clients.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-AU"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU">What we have found is that we cannot find any combination of client that will talk to 12c database without causing errors – mostly
</span><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black">ORA-01024: invalid datatype in OCI call, but have had other errors also.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black">Has anyone migrated to 12c and if so what client are you using and how do you have it configured?<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black">Thanks in advance.<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black">Peter<o:p></o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU" style="font-size:10.0pt;font-family:"Segoe UI",sans-serif;color:black"><o:p> </o:p></span></p>
<p class="MsoNormal" style="text-autospace:none"><span lang="EN-AU"><o:p> </o:p></span></p>
<p><span lang="EN-AU">IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is private and confidential and may contain legally privileged information. It is for the addressee's attention only. If you are not the intended recipient and have
received this transmission, you must not use, edit, print, copy or disclose its contents to any person or disseminate the information contained herein or hereto attached, and you must notify sender immediately by return email and delete this transmission from
your system. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. We have used reasonable efforts to protect this transmission from computer viruses and other malicious software, but no warranty
is made and the sender takes no responsibility for any loss or damage incurred from using this email or the information contained in this email.<o:p></o:p></span></p>
<p><br>
<br>
-------------EOP--------------- <br>
<br>
This e-mail message and any attachments are confidential. Any dissemination or use of this information by a person other than the intended recipient is unauthorized. If you are not the intended recipient, please notify me by return e-mail, do not open any attachment
and delete this communication and any copy. <br>
<br>
Thank you <o:p></o:p></p>
<p><span lang="EN-AU">IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is private and confidential and may contain legally privileged information. It is for the addressee's attention only. If you are not the intended recipient and have
received this transmission, you must not use, edit, print, copy or disclose its contents to any person or disseminate the information contained herein or hereto attached, and you must notify sender immediately by return email and delete this transmission from
your system. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. We have used reasonable efforts to protect this transmission from computer viruses and other malicious software, but no warranty
is made and the sender takes no responsibility for any loss or damage incurred from using this email or the information contained in this email.<o:p></o:p></span></p>
<p><span lang="EN-AU">IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is private and confidential and may contain legally privileged information. It is for the addressee's attention only. If you are not the intended recipient and have
received this transmission, you must not use, edit, print, copy or disclose its contents to any person or disseminate the information contained herein or hereto attached, and you must notify sender immediately by return email and delete this transmission from
your system. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. We have used reasonable efforts to protect this transmission from computer viruses and other malicious software, but no warranty
is made and the sender takes no responsibility for any loss or damage incurred from using this email or the information contained in this email.<o:p></o:p></span></p>
</div>
<p><br>
<br>
-------------EOP--------------- <br>
<br>
This e-mail message and any attachments are confidential. Any dissemination or use of this information by a person other than the intended recipient is unauthorized. If you are not the intended recipient, please notify me by return e-mail, do not open any attachment
and delete this communication and any copy. <br>
<br>
Thank you </p>
</body>
</html>