Oracle 10

From email:

Oracle (since version 8 ? ) has been recommending to all that we move toward the Cost Based Optimizer (CBO). Prior to this Oracle used (and still can use) the RULE based Optimizer, which pretty much just uses predicate logic and knowledge of indexes to do its job. (Also, the order of tables in the FROM clause is important, and the order of the WHERE clauses)

The CBO attempts to come up with the cheapest row access plan based on the actual cost (CPU and IO) of the plan. To do this, Oracle needs accurate statistics on the tables and indexes involved. These statistics are gathered using the Oracle supplied PLSQL procedures within the DBMS_STATS package. These procedures gather a bunch of different information about the tables and indexes, and can also gather histogram information, which can be invaluable on (mainly indexed) columns where there is highly skewed data. Stats gathering is becoming more automagic as time goes on. Everytime Oracle release a new version, there are new and improved auto options for stats gathering. there is also the possibility (we are not doing this presently) of gathering "system stats" as well. These are separate to the data object stats, and must be gathered by monitoring a live system. These stats attempt to place real time values on IO based on system load. If these are available, the CBO correlates its IO estimates with these real time estimates.

The CBO is not perfect though, and occasionally it will choose a less than optimal access path. This happens mainly because stats usually contain imprecise estimates made during stats gathering. It is possible to collect complete and accurate stats, but this is extremely expensive. To give you an example, it takes over 30 hours to gather stats in HE90U5 (using the auto options, which will estimate some things).

Portal Navigation Structure

select t.portal_name, t.portal_reftype, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_objname as portal_objname, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_label as portal_label, t.portal_ispublic, t.portal_seq_num, t.lastupdoprid, level lev, t.portal_urltext
from sysadm.psprsmdefn t
where t.portal_name = 'EMPLOYEE'
start with t.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR t.portal_objname = t.portal_prntobjname and PRIOR t.portal_name = t.portal_name and PRIOR t.portal_reftype = 'F';

Impact List for Projects

-- 1. Copy the results
-- 2. Paste into Word then: Table/Convert/Text to Table...
-- 3. You may need copy and paste formatting from other cells.

define projectName = 'GUFNGL018'

--select object, type, ' ', 'New', 'Easy', 0, 0, ' ', ' ' from (
select * from (
SELECT
a.projectname,
A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.')|| A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 as Object,
decode(a.objecttype,
'0', 'Record',
'1', 'Index',
'2', 'Field',
'3', 'Field Format',
'4', 'Translate value',
'5', 'Page',
'6', 'Menu',
'7', 'Component',
'8', 'Record Peoplecode',
'9', 'Unknown Object Type',
'10','Query',
'11','Tree Structure',
'12','Tree',
'13','Access Group',
'14','Colours',
'15','Style',
'16','Unknown Object Type',
'17','Business Processes',
'18','Activities',
'19','Roles',
'20','Process Defintions',
'21','Server Definition',
'22','Process Type Definitions',
'23','Job Definitions',
'24','Recurrence Definition',
'25','Message Catalog Entries',
'26','Dimensions',
'27','Cube Definitions',
'28','Cube Instance Definitions',
'29','Business Interlink',
'30','Record SQL',
'31','File Layout Definitions',
'32','Component Interface',
'33','Application Engine Programs',
'34','Application Engine Sections',
'35','Message Node',
'36','Message Channels',
'37','Messages',
'38','Approval Rule Sets',
'39','Unknown Object Type',
'40','Unknown Object Type',
'41','Unknown Object Type',
'42','Unknown Object Type',
'43','Unknown Object Type',
'44','Page Peoplecode',
'45','Unknown Object Type',
'46','Component Peoplecode',
'47','Unknown Object Type',
'48','Component Rec Fld Peoplecode',
'49','Images',
'50','Style Sheet',
'51','HTML',
'52','File Reference',
'53','Permission Lists',
'54','Portal Registry Definitions',
'55','Portal Registry Structures',
'56','URL Definition',
'57','Application Packages',
'58','Unknown Object Type',
'59','Unknown Object Type',
'60','Unknown Object Type',
'61','Archive Template',
'62','Unknown Object Type',
'63','Portal registry User Favourites',
'64','Mobile page',
'PeopleSoft are being smart again with - ' || a.objecttype ) as Type,
decode(UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype <> '52'
and not (objecttype=0 and objectid2 = 2)
and not (objecttype=2 and objectid2 = 102)
union
SELECT
a.projectname,
decode(A.OBJECTVALUE2,' ','',A.OBJECTVALUE2) || decode(A.OBJECTVALUE1,' ','',A.OBJECTVALUE1) as Object,
'File Reference' as Type,
decode(UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype = '52'
)
where projectname like &projectName
--order by 2,1
order by 3,2

Getting SQRs to Publish to the RDS/Report Manager Everytime

See Also: http://capital-it.blogspot.com/2008/09/publishing-files-to-report-managerrds.html

In short if you want a process's output to appear in the RDS it has to end up residing in the same folder that is created for the process at run-time.

For this example we are going to be producing a .ltr file using an SQR process called 'PROCNAME'. In the end we want a file path like this, where 501 is the Process Instance number:

/opt/psoft8/HE900D9A/appserv/prcs/HE90D9A/log_output/SQR_PROCNAME_501/SQR_PROCNAME_501.LTR

Note that up to "log_output' is most likely going to be a system variable, set in something like setfile.sqc so it can be modified for each environment.

Note that the folder name (SQR_PROCNAME_501) must be in this format, where 'SQR' is the Process Type, 'PROCNAME' is the Process Name and '501' is the Process Instance Number.

The file name syntax is the same as the folder name (although at time of writing I think you may actually be able to get away with whatever file name you please).

Update: It appears App Engines require the files to be in the strict format "AE_ProcName_ProcInstance.*" where * is an extension. This was working when output type was Web/TXT.


The SQR code looks something like this:

#DEFINE FILEPREFIX e:\psoft8\he900d9a\appserv\prcs\HE90D9A\log_output\

! Folder
Move '{FILEPREFIX}' to $fileName
Concat 'SQR_PROCNAME_' with $fileName
Concat $prcs_process_instance with $fileName
Concat '\' with $fileName

! File.
Concat 'PROCNAME_' with $fileName
Concat $prcs_process_instance with $fileName
Concat '.LTR' with $fileName

New-Report $fileName

Using Outer Joins AND Effective Dated Logic

How can you use outer joins on a table and still use effective dated logic? Something like this.

Note that the outer join will either return no rows, and thus a NULL value for EFFDT, or it will return a date and subsequent effective dated logic can be used.

AND C.BUSINESS_UNIT (+) = A.BUSINESS_UNIT
AND C.COMMON_ID (+) = A.COMMON_ID
AND C.SA_ID_TYPE (+) = A.SA_ID_TYPE
AND C.ITEM_NBR_CHARGE (+) = A.ITEM_NBR
AND
   (NULL = C.EFFDT
   OR
       (C.EFFDT =
           (SELECT MAX(C_ED.EFFDT)
           FROM sysadm.ps_ITEM_XREF C_ED
           WHERE C_ED.BUSINESS_UNIT = C.BUSINESS_UNIT
           AND C_ED.COMMON_ID = C.COMMON_ID
           AND C_ED.SA_ID_TYPE = C.SA_ID_TYPE
           AND C_ED.ITEM_NBR_CHARGE = C.ITEM_NBR_CHARGE
           AND C_ED.ITEM_NBR_PAYMENT = C.ITEM_NBR_PAYMENT
           AND C_ED.EFFDT <= SYSDATE)))

XML Publisher Example

Example of using PeopleCode to generate XML and then merge with a predefined RTF template.

/* GUSAFN02 - HEUP Project, Michael Nitschke 27.11.2007 */
import PSXP_RPTDEFNMANAGER:*;
import GU_EN_ENROLMENTS:*;

Declare Function DeleteLocalFile PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;
Declare Function GetDirSeparator PeopleCode PSXPFUNCLIB.FUNCLIB FieldFormula;

/* Function to populate a node based on given SQL. 
   &node:     Create the node in the location you want and pass through to be populated.
   &recName: Record that node data will be based on.  Must also match data structure returned from &sql.
   &sql:     SQL that will populate &rec and the node. */
Function AddNodeFromSQL(&node As XmlNode, &recName As string, &sqlName As string)
 Local Record &rec = CreateRecord(@("Record." | &recName));
 Local SQL &sql = GetSQL(@("SQL." | &sqlName), &emplid);
 While &sql.Fetch(&rec)
    Local XmlNode &nodeRow = &node.AddElement(&node.NodeName | "Row");
    For &f = 1 To &rec.FieldCount
       Local XmlNode &nodeFld = &nodeRow.AddElement(&node.NodeName | "." | &rec.GetField(&f).Name);
       &nodeFld.NodeValue = &rec.GetField(&f).Value;
    End-For;
 End-While;
End-Function;

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

&emplid = %EmployeeId;
Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
Local XmlDoc &xmlDoc;
&xmlDoc = CreateXmlDoc("<?xml version='1.0'?><ACADEMIC_CHARGES/>");

/* Create header, config, programs, charges, unapplied charges and discounts xml elements, including data. */
Local XmlNode &nodeHeader = &xmlDoc.DocumentElement.AddElement("Header");
AddNodeFromSQL(&nodeHeader, "GU_STMT_HDR_DW", "GU_STMT_HDR");
Local XmlNode &nodeConfig = &xmlDoc.DocumentElement.AddElement("Config");
AddNodeFromSQL(&nodeConfig, "GU_STMT_CFG_DTL", "GU_STMT_CFG_DTL_AC");
Local XmlNode &nodeProgram = &xmlDoc.DocumentElement.AddElement("Program");
AddNodeFromSQL(&nodeProgram, "GU_STMT_PROG_DW", "GU_STMT_PROG");
Local XmlNode &nodeCharges = &xmlDoc.DocumentElement.AddElement("Charges");
AddNodeFromSQL(&nodeCharges, "GU_STMT_CHRG_DW", "GU_STMT_CHARGES");
Local XmlNode &nodeChargesGST = &xmlDoc.DocumentElement.AddElement("ChargesGST");
AddNodeFromSQL(&nodeChargesGST, "GU_STMT_CGST_DW", "GU_STMT_CHARGES_GST");
Local XmlNode &nodeUnappliedCharges = &xmlDoc.DocumentElement.AddElement("UnappliedPayments");
AddNodeFromSQL(&nodeUnappliedCharges, "GU_STMT_CHRG_DW", "GU_STMT_UNAPPLIED_PAYMENTS");
Local XmlNode &nodeDiscounts = &xmlDoc.DocumentElement.AddElement("Discounts");
AddNodeFromSQL(&nodeDiscounts, "GU_STMT_CHRG_DW", "GU_STMT_DISCOUNTS");
Local XmlNode &nodeMessages = &xmlDoc.DocumentElement.AddElement("Messages");
AddNodeFromSQL(&nodeMessages, "GU_STMT_MSG_DW", "GU_STMT_MESSAGES");

/* Create report definition and connect to the generated XML document. */
&rptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn("GU_ACAD_CHRG");
&rptDefn.Get();
&rptDefn.SetRuntimeDataXMLDoc(&xmlDoc);

/* Create output directories. */
&sDirSep = GetDirSeparator();
&RptOutputDir = GetEnv("PS_SERVDIR") | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | UuidGen();
&OutputDir = &RptOutputDir | &sDirSep | "RptInst";
&DataDir = &RptOutputDir | &sDirSep | "Data";
CreateDirectory(&OutputDir, %FilePath_Absolute);
CreateDirectory(&DataDir, %FilePath_Absolute);

/* Process. */
&rptDefn.OutDestination = &RptOutputDir;
&rptDefn.ProcessReport("", %Language_User, %Date, "");
CommitWork();

/* Display. */
&rptDefn.DisplayOutput();

/* Cleanup. */
DeleteLocalFile(&sOutputFile, %FilePath_Absolute);

Array example

Nice little example of using arrays in PeopleCode. Probably a more elegant, 2 dimension solution but this works just fine.

This example was required to handle overriding GL distribution by Elements, which are on level 3. and element can appear on level 3 under many level 2 rows. Code loops through all possible rows on level 3 and sums total for each unique Element found. Must add to 100.

/* Check that sum of each given element, across all pin rows, adds to 100%. */

/* 2 arrays to hold all elements and sum of their percentages. */
&arrElements = CreateArray();
&arrPercent = CreateArray();

/* Populate the arrays. */
Local Rowset &rsGP_GL_DIST_DTL = GetRowset(Scroll.GP_GL_DIST_DTL);
For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
   Local Rowset &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
   
   /* Check there are actually any element rows (if one does they all do, vice-versa). Populate arrays if found.*/
   If &rsGP_GL_DIST_PIN(1).GP_GL_DIST_PIN.PIN_NUM.Value = 0 Then
      &hasElements = False;
      Break;
   Else
      &hasElements = True;
      For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
         &element = &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value;
         &percent = &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Value;
         &found = &arrElements.Find(&element);
         If &found = 0 Then
            &arrElements.Push(&element);
            &arrPercent.Push(&percent);
         Else
            &arrPercent [&found] = &arrPercent [&found] + &percent;
         End-If;
      End-For;
   End-If;
   
End-For;

/* Check for any elements that are <> 100% and advise with warning msg & highlighting the field. */
If &hasElements Then
   For &k = 1 To &arrPercent.Len
      
      &err = False;
      If &arrPercent [&k] <> 100 Then
         &err = True;
      End-If;
      
      For &i = 1 To &rsGP_GL_DIST_DTL.ActiveRowCount
         &rsGP_GL_DIST_PIN = &rsGP_GL_DIST_DTL(&i).GetRowset(Scroll.GP_GL_DIST_PIN);
         For &j = 1 To &rsGP_GL_DIST_PIN.ActiveRowCount
            If &rsGP_GL_DIST_PIN(&j).GP_PIN_DESCR_VW.DESCR.Value = &arrElements [&k] Then
               If &err Then
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.SetCursorPos(%Page);
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style = "PSERROR";
                  Error "Sum percentages of Element '" | &arrElements [&k] | "' do not total 100%.";
               Else
                  &rsGP_GL_DIST_PIN(&j).GP_GL_DIST_PIN.PCT_TO_ALLOCATE.Style = "PSEDITBOX";
               End-If;
            End-If;
         End-For;
      End-For;
      
   End-For;
End-If;