THINKing in WEBFOCUS

1. THINKing in WEBFOCUS Walter F. Blood Information Builders Nashville User Forum February 23, 2012

2. Output Generator Report Generator Matrix Thinking in WebFocus Retrieve DEFINE Screening Analysis FOCUS or External Sort Aggregation COUNT/MIN/MAX TOTAL Screens COMPUTE Format Extracts

3. Thinking in WebFOCUSAnalysis • Open and parse Master File Descriptions • Verify DBA access to file(s) • Determine I/O access modules required • Process DEFINE field expressions for pertinent files • Read and parse the request • Check DBA and set up access restrictions at SEGMENT and FIELD levels

4. Thinking in WebFOCUSAnalysis • Activate segments: referenced subtree(s) • Smallest subtree which contains “root” and every segment referenced directly or indirectly (HEADING PARENT AND GRAND CHILD) • Activate fields (real and DEFINEd) • Read USE list and/or check Access Files and FILEDEF/ALLOCATES • Open and Verify files

5. Thinking in WebFOCUSRetrieval • Retrieval is “Top Down, Left Right” within the referencedsub-tree • Unique segments are promoted into path of parent and are never missing • IF/WHERE tests on database fields are evaluated upon retrieval • DEFINE fields are evaluated if needed • IF/WHERE tests are evaluated on DEFINE fields on segment by segment basis, AFTER all other selection tests are passed • Each Path is processed independently, and fields are merged on common sort fields

6. Thinking in WebFOCUSRetrieval - SQL • SQL interface retrieval optimized • WebFOCUS converted to SQL • Levels of optimization • Join • Selection • Aggregation • DEFINEd fields • Unoptimized – done in WebFOCUS • Returns data at the Internal Matrix level or before

7. JOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS AJ JOIN EMP_ID IN EMPLOYEE TO ALL EMP_ID IN KIDS AS BJ EMP_ID FIRST_NAME LAST_NAME KU KM COVER_DT TYPE_COVER FAMILY EMP_ID SPOUSE EMP_ID CHILD_DOB CHILD_NAME K K Thinking in WebFOCUSStructures SH2

8. Thinking in WebFOCUSStructures SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I +-----------------+-----------------+ I II I SEGS I SEGK I INSSEG 02 I KU 03 I KM 04 I SH2 .............. .............. ************** :EMP_ID :K :EMP_ID ::K *COVER_DT ** :SPOUSE : :CHILD_DOB :: *TYPE_COVER ** : : :CHILD_NAME :: *FAMILY ** : : : :: * ** : : : :: * ** :............: :............:: *************** JOINED SPICE .............: ************** JOINED KIDS SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I +-----------------+ I I I SEGS I INSSEG 02 I KU 03 I SH2 .............. ************** :EMP_ID :K *COVER_DT ** :SPOUSE : *TYPE_COVER ** : : *FAMILY ** : : * ** : : * ** :............: *************** JOINED SPICE ************** JOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS J1 JOIN EMP_ID IN EMPLOYEE TO ALL EMP_ID IN KIDS AS J2 Limit 1024 segments in structure Limit 1023 Joins

9. Thinking in WebFOCUSDEFINE • Segment is determined by • WITH field • Fields used in expression • Constant expressions are evaluated when file is opened (segment 0) • DEFINEs are evaluated ONLY if required by request • DEFINEs are evaluated at detail level only Each DEFINE field is associated with a specific segment

10. Thinking in WebFOCUSOrder of Retrieval – FOCUS Files • Root segment instances are obtained in physical order • Lower level instances obtained in SEGTYPE order within parent segment • TABLEF • Root and child segment instances are obtained in SEGTYPE order • No sorting • TABLE/GRAPH/MATCH

11. Thinking in WebFOCUSOrder of Retrieval – FOCUS Files • Fieldname is not indexed Segment containing “fieldname” becomes ROOT of the view, and is retrieved physically • Fieldname is indexed Segment containing “fieldname” is accessed via the equality test on INDEX • Other segments become children of the new root segment • TABLE FILE filename.fieldname

12. Thinking in WebFOCUSOrder of Retrieval – FOCUS Files Alternate Physical View is created through the referenced segment highest in the hierarchy SET AUTOINDEX=ON Alternate Indexed View is created if there is an Equality test specified for an indexed field on the referenced segment highest in the hierarchy SET AUTOSTRATEGY=ON Equality IF/WHERE test on primary key field in logically retrieved segment will terminate “chain chasing” as soon as possible SET AUTOPATH = ON

13. 1 STATE=IL, DATE=0002 Continue Search 2 STATE=IL, DATE=0001 Continue Search 3 STATE=IL, DATE=9912 Continue Search 4 STATE=IL, DATE=9911 Next Parent 5 STATE=MI, DATE=9911 Next Parent STATE IL MI DATE 9911 0001 0002 9912 9909 9910 9910 9911 Thinking in WebFOCUSAutostrategy– FOCUS Files WHERE DATE EQ ‘9912’

14. Retrieval stops if: Set FIXRETRIEVAL = ON Thinking in WebFOCUSFIXRETRIEVE – SUFFIX=FIX SET FIXRETRIEVE=ON • Equality IF/WHERE test on primary specified sort field in single segment FIX file will terminate retrieval as soon as possible FILE=TESTFI,SUFFIX=FIX SEGNAME=TESTSEG,SEGTYPE=S1 FIELD=COUNTRY,E01,A10,A10,$ FIELD=CAR ,E02,A16,A16,$ ENGLAND JAGUAR ENGLAND JENSEN ENGLAND TRIUMPH FRANCE PEUGEOT ITALY AUDI WHERE COUNTRY EQ ‘FRANCE’

15. Thinking in WebFOCUSMultiple Paths • Retrieval is performed for each path separately • Unique segments are always in the path of their parent • Unique segments are SEGTYPE = U, KU, DKU, KLU • JOIN TO creates DKU segments • JOIN TO ALL creates DKM segments • TABLE/MATCH/GRAPH • Instances from each path will be merged in the sort process by the common parent(s) • TABLEF does not merge data from multiple paths • Alternate file views can be used to create single paths • SET MULTIPATH controls multiple path retrieval

16. Thinking in WebFOCUSMultiple Paths Airport DEPART_TIME DEP_FLIGHT DEPART_TO ARRIVE_TIME ARR_FLIGHT ARR_FROM

17. Thinking in WebFOCUSMultiple Paths ARRIVE_TIME ARR_FLIGHT ARR_FROM DEFINE FILE AIRPORTS.ARRIVE_TIME DELAY=DEPART_TIME – ARRIVE_TIME; END TABLE FILE AIRPORTS.ARRIVE_TIME PRINT DEP_FLIGHT DEPART_TIME WHERE AIRPORT EQ ‘O’’HARE’ WHERE DELAY GT 60 WHERE DEPART_TO EQ ‘LAX’ WHERE ARR_FROM EQ ‘TOR’ END Airport DEPART_TIME DEP_FLIGHT DEPART_TO

18. Thinking in WebFOCUSMultiple Paths FAMILY FAMILY INVESTMENT FAMILY COLLEGE

19. FAMILY FAMILY COLLEGE FAMILY INVESTMENT Thinking in WebFOCUSMultiple Paths Instances 1 2 3 A I B M C I B M C D

20. Thinking in WebFOCUSMultiple Paths TABLE FILE SAMPLE PRINT COLLEGE INVEST BY FAMILY WHERE COLLEGE EQ ‘B’ WHERE INVEST EQ ‘I’ END SET MULTIPATH=SIMPLE SET MULTIPATH=COMPOUND • FAMILY COLLEGE INVEST • ------ ------- ------ • B I • B . • . I • FAMILY COLLEGE INVEST • ------ ------- ------ • B I

21. Thinking in WebFOCUSRetrieval - Short Paths • High level segments with missing referenced descendents rejected (short path) • SET ALL=ON -- JOIN LEFT_OUTER • High level segments with missing referenced descendents accepted (missing fails IF/WHERE tests) • SET ALL=PASS • High level segments with qualified or missing referenced descendents accepted (missing passes IF/WHERE tests) • SET ALL=OFF -- JOIN INNER

22. Thinking in WebFOCUSRetrieval - Short Paths • Referenced, not present, Unique Segments are defaulted to blanks for alpha fields, zeros for numerics • Unique segments do not create short paths. Therefore, ALL settings are irrelevant for Unique segments. • Note: Missing referenced descendents of missing unique segments revert to ALL=OFF logic • Unique segments (U, KU, DKU, and KLU) are never considered missing

23. Thinking in WebFOCUSInternal Matrix Generation • One row for each distinct combination of sort keys • One column for each verb object • Computed columns, row and column totals, subtotals, and summaries are not yet done • All data is in internal form • The output of the SORT/MERGE phase is conceptually a matrix

24. Thinking in WebFOCUSInternal Matrix - Contents • Verb Objects (both SUM and COUNT are calculated) • Fields following a verb • Fields used in COMPUTEs not previously mentioned • Fields used in HEADINGs or FOOTINGs • Fields used in SUBHEADs or SUBFOOTs, not previously mentioned • MISSING fields are not counted Sort Fields • BY fields • ACROSS fields (used as low order BY at sort phase) • FOCLIST (verb is PRINT or LIST)

25. Thinking in WebFOCUSMultiple Verb Sets - limits • Up to 16 verbs with associated BY fields can be specified • Up to 128 sort fields may be specified • Up to 1024verb objects may be specified

26. SUM SALES BY DIVISION BY YEAR LIST SALES BY DIVISION BY YEAR FOCLIST SALES YEAR SALES Thinking in WebFOCUSMultiple Verb Sets SUM SALES BY DIVISION DIV SALES

27. Thinking in WebFOCUSInternal Matrix - Sorting • Aggregation is performed as record is sorted, with FOCUS sort • Aggregation is performed by external sort if SET EXTAGGR = ON • Numeric Fields are added. Alpha fields being SUMmed, return the LAST value within the BY field, the FST value with external sorts • (SET SUMPREFIX=LST if required) • All verb objects are also COUNTed

28. BINs FOCSORT FOCSORT SORT MERGE FOCSORT BINs SORT MERGE BINs Thinking in WebFOCUSMerge – FOCUS Sort

29. FOCSORT BINs ~ First 5000 Records FOCSORT S001WK01 SORT MERGE S001WK02 S001WK03 EXTSORT S001WK04 BINs Subsequent Records S001WK05 S001WK06 S001WK07 S001WK08 S001WK09 Thinking in WebFOCUSEXTSORT = ON, AUTOTABLEF = OFF FOCSORT

30. OFFLINE BINs ~ First 5000 Records S001WK01 S001WK02 S001WK03 Or… S001WK04 Extract Subsequent Records S001WK05 S001WK06 S001WK07 S001WK08 S001WK09 Or… Hotscreen Thinking in WebFOCUSEXTSORT = ON, AUTOTABLEF = ON EXTSORT

31. FML/EMR Processor COMPUTE’s IF/WHERE TOTAL Thinking in WebFOCUSOutput Stage BY TOTAL SORT Internal Matrix Totals… Secondary Sort Post Matrix Processing FOCSML Extract Report Formatting/Stylsheets HOLD/PCHOLD/SAVE

32. Thinking in WebFOCUSBY TOTAL TABLE FILE EMPDATA SUM SALARY CNT.PIN COMPUTE AVGSAL=SALARY/CNT.PIN; AS 'AVE,SALARY' BY HIGHEST TOTAL AVGSAL NOPRINT BY DEPT END PAGE 1 PIN AVE DEPT SALARY COUNT SALARY ---- ------ ----- ------ ACCOUNTING $283,300.00 5 $56,660.00 SALES $395,200.00 7 $56,457.14 MARKETING $570,700.00 11 $51,881.82 CUSTOMER SUPPORT $198,400.00 4 $49,600.00 PROGRAMMING & DVLPMT $182,300.00 4 $45,575.00 PERSONNEL $216,800.00 5 $43,360.00 CONSULTING $126,300.00 3 $42,100.00 ADMIN SERVICES $56,200.00 2 $28,100.00

33. FML/EMR Processor COMPUTE’s IF/WHERE TOTAL Thinking in WebFOCUSOutput Stage BY TOTAL SORT Internal Matrix Totals… Secondary Sort Post Matrix Processing FOCSML Extract Report Formatting/Stylesheets HOLD/PCHOLD/SAVE

34. Thinking in WebFOCUSON Sortfield Options • Subtotaling – BY and ACROSS • SUBTOTAL and SUB-TOTAL • RECOMPUTE and SUMMARIZE • MULTILINE suppresses operation if only single detail line • RECAP (COMPUTE) • SUBFOOT/SUBHEAD

35. Thinking in WebFOCUSTotaling * NOTOTAL Suppresses Grand Totals

36. Thinking in WebFOCUSON SortfieldOptions - Format • PAGE-BREAK [REPAGE] • NOSPLIT • **FOLD-LINE • **SKIP-LINE • UNDERLINE ** May be specified on verb-objects

37. Thinking in WebFOCUSON TABLE Options • Totaling – ON TABLE… • COLUMN-TOTAL • ROW-TOTAL • SUMMARIZE • NOTOTAL • RECAP • [PAGE-BREAK AND ] SUBFOOT/SUBHEAD

38. Thinking in WebFOCUSHEADingsand FOOTings • References to fields in SUBHEAD and SUBFOOT become verb objects only if not previously mentioned • Fields used in HEADINGs and SUBHEADs are taken from the first line within the group (BY phrase or page) • References to fields in HEADING and FOOTING become verb objects with the first verb

39. Thinking in WebFOCUSON TABLE Extract Options • Extracts • ON TABLE HOLD [FORMAT …] • ON TABLE PCHOLD [FORMAT…] • ON TABLE SAVE [FORMAT…] • SET commands specific to format • Interface SET • WebFOCUS SET

40. Thinking in WebFOCUS Questions?