Transcript FOCUS 7.6 Product Update

FOCUS 7.6 Product Update
Walter F. Blood
Information Builders
DN 9602 000.0001
‘Highway Blues’ from the “Speaking Out” album
Copyright 2007, Information Builders. Slide 1
Mark Seales, composer; Ernie Watts, saxophone
FOCUS Release 7.6 Product Update
Parade of New Features
Copyright 2007, Information Builders. Slide 2
FOCUS 7.6 Product Update - Over 100 New Features
PERFORMANCE BOOSTERS
TABLE REPORTING
DATABASE and MASTER
101 20% CPU reduction
102 Unlimited FOCSORT
103 HOLD FMT [X]FOCUS 3x faster
104 WHERE optimization
105 Faster COMPUTEs in MODIFY
401 DATETRAN function
402 PLUS OTHERS
403 Summaries on ACROSS fields
404 IF LIKE/UNLIKE
405 SET KEEPFILTERS
406 BY Hierarchy WHEN SHOW
407 XIRR and MIRR functions
408 EDIT nested in DECODE
409 SET ACROSSLINE
410 SET CDN=QUOTEP
411 FOC227 continues processing
412 SET SHOWBLANKS
413 &VAR.QUOTEDSTRING
414 -INCLUDE fully qualified filename
415 Summary command combinations
416 SET SUMMARYLINES=EXPLICIT
417 PUTDDREC, CLSDDREC functs
418 STRREP function
419 &FOCFEXNAME
420 ? SET BY CATEGORY
601 XFOCUS for FDS
602 Filters in MFD
603 Multi-dimensional index (MDI)
604 Business Views
605 HOLD FORMAT FOCUS Extens
606 DATETIME format extensions
607 System &vars in mfd DEFINEs
608 GROUP ELEMENTS
609 Multilingual TITLE,DESC,REMARK
610 Multi-field JOIN for FOCUS/Fix files
INTERFACES
MODIFY/MAINTAIN
501 HOLD FORMAT SAME_DB
502 Cluster JOINs
503 Oracle 10G interface
504 SET OPTIMIZATION=NOAGGR
505 &DBMSERR, &DBMSLIST
801 Multiple TX fields in FIXFORM
802 DB_LOOKUP function
803 MODIFY of FIX files
804 SET PERMPASS=DBA
OUTPUT MECHANISMS
201 Structured HOLD files
202 BY HIERARCHY for non-cubes
203 Settable LRECL for HOLD files
204 FMLINFO FORLIST/CAPTION
205 HOLD FORMAT PPT
206 SET ERROROUT=OVERRIDE
207 FML INDENT, LIST
208 ON TABLE SET BYPANEL
209 SET EMPTYREPORT=ANSI
210 SAVB FORMAT INTERNAL
211 EXL2K Table of Contents
212 EXL2K Named Ranges
213 SET DEFECHO=ON/ALL
OPERATING SYSTEM
301 FOCREPLAY
302 DYNAM for HFS
303 Long DDNAMES
RAISED LIMITS
701 MFD with 256 segments
702 JOIN 63 files
703 MATCH 16 files
704 Dialog Manager decimal precision
705 32k bytes in TABLE
706 32k alpha fields
707 2k-long fieldnames
Copyright 2007, Information Builders. Slide 3
FOCUS 7.6 Product Update - Over 100 New Features
PERFORMANCE BOOSTERS
TABLE REPORTING
DATABASE and MASTER
421 SET CNOTATION = EXPLICIT
422 SET CNOTATION = PRINTONLY
423 DEFINE FILE * CLEAR
424 NEXTR and HMASK for datetime
425 .RNK suffix for ranking
611 CHECK DB
612 COMPUTES in MFD
613 Outer JOIN support
614 CHKINDEX
615 CHKLRECL
OUTPUT MECHANISMS
214 SET PDFLINETERM
RAISED LIMITS
708 FML raised limit for rows
709 LINREC/DATREC increase
710 Read up to 64K record length
711 32K-long amper variables
712 FOCEXEC width unlimited
INTERFACES
OPERATING SYSTEM
304 ?SITECODE
506 Adabas dynamic CALLTYPE
MODIFY/MAINTAIN
507 Adabas FETCHJOIN, SQL NULL 805 CREATE FILE with DROP
508 PASSRECS for Adabas, IMS, VSAM 806 SET MODCOMPUTE = NATV
509 IMS variable length segments
510 DB2 foreign key support
511 VSAM RRDS support
Copyright 2007, Information Builders. Slide 4
When it’s CPU charges!
Copyright 2007, Information Builders. Slide 5
Release 7.6
Copyright 2007, Information Builders. Slide 6
FOCUS 7.6 Product Update
CPU Reduction
Average 20% faster than FOCUS 7.3 !!
New ‘C’ compiler for MVS and VM
Includes all new features of WebFOCUS 7.6
$
$
$
$
$
$
$
$
Copyright 2007, Information Builders. Slide 7
FOCUS 7.6 Product Update
CPU Reduction
CPU Benchmark
Tests
% of 7.6 Improvement
7.3.6
7.2.11
7.1.1
Large MODIFY Load
22%
16%
11%
Large MODIFY UPDATE
30%
26%
23%
REBUILD
39%
53%
45%
Large TABLE SUM
7%
16%
16%
Large TABLE WHERE
8%
7%
9%
JOIN TABLE
18%
10%
13%
MATCH
8%
37%
19%
HOLD OUTPUT
44%
37%
47%
MAINTAIN
38%
29%
30%
See White Paper for performance details
Copyright 2007, Information Builders. Slide 8
FOCUS 7.6 Product Update
New Product Performance Boosters
FOCUS 20% faster overall
thanks to IBM/C compiler
All ON by default
HOLD FORMAT [X]FOCUS
redesigned, 3x faster
Faster COMPUTES
in MODIFY
Unlimited FOCSORT
(>2gig, MORE, MATCH)
New WHERE
optimizer
FOCUS 7.6 Product Update
Structured HOLD FiIes
VSAM
DB2
Oracle
FOCUS
XFOCUS
VSAM
VSAM
HOLD Files currently
remove all structure
ON TABLE HOLD
DB2
Oracle
FOCUS
XFOCUS
Copyright 2007, Information Builders. Slide 10
FOCUS 7.6 Product Update
Structured HOLD Files - Retain the Structure
FOCUS Database
A
B
C
E
D
Plain
HOLD File
A B C D
TABLE FILE
PRINT A B C D
IF C EQ 5
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET EXTRACT [ON or *]
Structured
END
AHOLD File
HOLD FORMAT options
Alpha [X]FOCUS Binary
B
C
C=5
D
 Retains knowledge of one-to-many relationships of original file
 Use * to tailor field list with EXCLUDE Other SHF features will be added later
Copyright 2007, Information Builders. Slide 11
FOCUS 7.6 Product Update
Structured HOLD Files - Move Data to the User
Deliver structured mainframe data
to the pc for deeper analysis and
presentation via
A
B
C
E
D
Copyright 2007, Information Builders. Slide 12
FOCUS 7.6 Product Update
Business “View” of a Database
A VIEW into the original file – no new data, no separate extraction
 Reflect the business instead of physical field arrangement
 Have as many different Business Views as required. Direct via USE.
 BV recognized only for extract (TABLE[F], GRAPH, MATCH, MORE)
 JOIN a Business View to other files
 Limit the fields accessible in the main file, without DBA
 Make OS impose a View via security
Business View
Original
MFD
MFD
FILE=anyname,
VIEW_OF=ORIG
FILE=ORIG,
SUFFIX=any
FOCUS 7.6 Product Update
Business “View” of a Database
FILE=ORIG, SUFFIX=FOC FILE=BV, VIEW_OF=ORIG
SEGNAME=SEG1, …
SEGNAME=SEGMENT1
FIELD=SOCSECNO, …
FIELD=FULLNAME,,BELONGS_TO=NAME,$
FIELD=NAME, …
FIELD=SSN,,BELONGS_TO=SOCSECNO,$
FIELD=SALARY, …
FIELD=COMPANY,,TITLE=‘CO NAME’,$
Business View
SEGNAME=SEG2, PARENT=SEG1
omits SALARY
FIELD=COMPANY, …
Rename/rearrange original fields without touching original file
Each Business View field relates (maps) to an original field
TABLE with new field names translates to original names;
• attributes and relationships taken from original field
New DEFINEs permitted in Business View mfd
FOCUS 7.6 Product Update
Business Views
FILE=BV, VIEW_OF=ORIG
SEGNAME=SEGMENT1
FIELD=FULLNAME,,BELONGS_TO=NAME,$
FIELD=SSN,,BELONGS_TO=SOCSECNO,$
FIELD=COMPANY,,TITLE=‘CO NAME’,$
SET FOCTRANSFORM = ON
TABLE FILE BV
PRINT FULLNAME BY SSN
END
Performs field mapping when
different names are used.
Names the same? Turn OFF
 Refer to field name in Business View
 Maps to field in original file
 BV supports TITLE to override original
FOCUS 7.6 Product Update
SET DMPRECISION
-SET &VAR1 = 1 + (2 / 3) ;
Don’t forget
the –RUN !!
SET DMPRECISION = 5
5 decimal
places
-RUN
-SET &VAR2 = 1 + (2 / 3) ;
-TYPE VAR1=&VAR1 VAR2=&VAR2
VAR1=1
VAR2=1.66666
 Retain decimal places in Dialog Manager calculation results
 Up to 9 decimals
 Stored as characters, not internal decimal numbers
FOCUS 7.6 Product Update
Group the Other Values Into One More Row
Office
ABC
MNO
DEF
JKL
GHI
PQR
VWX
STU
Sales
10,000
6,000
9,000
7,000
8,000
5,000
3,000
4,000
TABLE FILE
PRINT OFFICE
BY HIGHEST 5 SALES PLUS OTHERS
END
Sum of
5000+4000+3000
10,000
9,000
8,000
7,000
6,000
12,000
ABC
DEF
GHI
JKL
MNO
OTHERS
PLUS OTHERS creates one more row of all records that
do not fall into the explicit rows displayed
Works with RANKED, IN-GROUPS-OF, IN-RANGES-OF
Does not work with TILES, BY TOTAL)
FOCUS 7.6 Product Update
HOLD FORMAT [X]FOCUS – Faster + Extended
Unique’s
TABLE FILE CAR BY COUNTRY
COMPUTE LONG1/A1024 = ‘A’;
COMPUTE LONG2/A1024 = ‘B’;
COMPUTE LONG3/A1024 = ‘C’;
SET DIRECTHOLD=ON
COMPUTE LONG4/A1024 = ‘D’;
New load algorithm
is 2-3 times faster
ON TABLE HOLD FORMAT FOCUS
END
FILE=HOLD, SUFFIX=FOCUS
Adds Unique
segments if over
~4K of data in
parent segment -No more FOC
error message
SEGMENT=SEG01, SEGTYPE=S1
FIELD=COUNTRY,,A16
FIELD=LONG1,,A1024
3088 bytes +
pointers < 3986
FIELD=LONG2,,A1024
FIELD=LONG3,,A1024
SEGMENT=, PARENT=SEG01, SEGTYPE=U
Overflow into
FIELD=LONG4,,A1024
new segment
FOCUS 7.6 Product Update
HOLD into PowerPoint and Visual Discovery
TABLE FILE CAR
SUM DCOST BY COUNTRY
ON TABLE HOLD FORMAT VISDIS
END
Download data to pc and
analyze in Visual Discovery
GRAPH FILE CAR
SUM DCOST BY COUNTRY
ON TABLE HOLD FORMAT PPT
END
Download as slide to pc and add
to any PowerPoint presentation
FOCUS 7.6 Product Update
Multi-Lingual MFD Attributes
 TITLEs and DESCRIPTIONs and REMARKs in alternate languages,
built right into the master
 Selected for use automatically, based on current code page (SET LANG=)
 Select two-character code from documented language list
FILE=LANGUAGE ,SUFFIX=FOC
SEGNAME=ACCOUNTS, SEGTYPE=S1
FIELDNAME=PERSON, PERS, FORMAT=A12
TITLE = ‘Name',
TITLE_SP = ‘Nombre',
TITLE_FR = ‘Nom',
DESCRIPTION = ‘Name of Person’,
DESCRIPTION_IT = ‘Nome di persona’ ,$
FIELDNAME= …
FOCUS 7.6 Product Update
The New SETs – SET ACROSSLINE=OFF
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET ACROSSLINE=SKIP *
TABLE FILE CAR
SUM SEATS BY CAR
ACROSS MODEL
END
PAGE 1
MODEL
MOD1 MOD2 MOD3
CAR
------------------------
BMW
530I
3.0SI
2002 2DR
The underline
goes away
*ACROSSLINE=OFF shows blank line
Skip SETs
FOCUS 7.6 Product Update
The New SETs – SET BYPANEL = ON/n
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
ON TABLE SET BYPANEL ON
TABLE FILE CAR
SUM COMPUTE
WIDE1/A40 = ‘ABC’;
WIDE2/A40 = ‘DEF’;
BY CAR
ON TABLE SET BYPANEL ON
END
PAGE 1.1
CAR WIDE1
----
------
ABC
PAGE 1.2
CAR WIDE2
---PF11
Sort field remains
on screen
------
DEF
FOCUS 7.6 Product Update
The New SETs – SET CDN = QUOTEP
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET CDN = QUOTEP
CDN =
OFF
ON
SPACE
QUOTE
QUOTEP
RESULT
16,000.99
16.000,99
16 000.99
16’000,99
16’999.99
Quote and
period
.
FOCUS 7.6 Product Update
The New SETs – SET CNOTATION=EXPLICIT /
PRINTONLY
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY Sort column is
never counted
SET CNOTATION = EXPLICIT
Redefine
builds 2nd
column in
matrix
TABLE FILE CAR
PRINT MPG NOPRINT
SEATS/D4.1 RCOST
COMPUTE WEIRD = C1 * C2;
BY CAR
Which columns
are chosen?
END
CNOTATION = ALL (default)
CAR MPG SEATS SEATS RCOST WEIRD
CNOTATION = EXPLICIT
CAR MPG SEATS SEATS RCOST WEIRD
CNOTATION = PRINTONLY
CAR MPG SEATS SEATS RCOST WEIRD
FOCUS 7.6 Product Update
The New SETs – SET DEFECHO = ON / ALL
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET DEFECHO = ON/ALL
Default setting for ECHO
Spans focexecs thruout session
SET DEFECHO=ON
-RUN
Force ECHO
to ON unless
overridden
locally
-DEFAULT &ECHO=ALL
-SET &ECHO=ALL;
EX PROC123 ECHO=ALL
TABLE FILE CAR …
Local
overrides
FOCUS 7.6 Product Update
The New SETs- SET EMPTYREPORT = ANSI
SET EMPTYREPORT = ANSI
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
TABLE FILE CAR
SET CDN = QUOTEP
HEADING
SET CNOTATION = EXPLICIT
“HEADING”
SET CNOTATION = PRINTONLY
FOOTING
SET DEFECHO = ON/ALL
“FOOTING”
SET EMPTYREPORT = ANSI
PRINT CAR MPG IF CAR EQ ‘XYZ’
SET KEEPFILTERS = ON
END
SET MAXLRECL = 64K
NUMBER OF RECORDS IN TABLE=
0 LINES=
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
PAGE 1
PAGE 1
SET PERMPASS = password
HEADING
HEADING
CAR
MPG
SET PDFLINETERM = SPACE
CAR
MPG
-----------------------SET SHOWBLANKS = ON
.
SET SUMMARYLINES=EXPLICIT
FOOTING
Note the dot
? SET BY CATEGORY
and Footing
0
FOCUS 7.6 Product Update
The New SETs – SET KEEPFILTERS = ON
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET KEEPFILTERS = ON
Preserves activated FILTERS
through a non-persistent
session.
Applicable to WebFOCUS only
FILTER FILE file
FILT1 = boolean expression ;
END
SET FILTER FILT1 IN file ON
TABLE FILE file
….
FOCUS 7.6 Product Update
The New SETs – SET MAXLRECL = 64
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET MAXLRECL = 64K
Maximum input file width
was 32K including OCCURS
clauses.
New maximum record
length is 64K. But still can
read up to 32K bytes of data
from any one record.
FOCUS 7.6 Product Update
The New SETs – SET MODCOMPUTE = NATV
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET MODCOMPUTE = NATV
 Activates the native
MODIFY expression
compiler
 No need to recompile
MODIFYs
 Some expression
components are not
compiled
FOCUS 7.6 Product Update
The New SETs – SET OPTIMIZATION = NOAGGR
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET OPTIMIZATION = NOAGGR
Optimize JOIN’s and DEFINE’s
NOW, do not optimize SUM’s
Only for SQL engines
SQL DB2 SET OPT... = NOAGGR
FOCUS 7.6 Product Update
The New SETs – SET PERMPASS = password
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET PERMPASS = password
Only .PRF ?
FOCUS 7.6 Product Update
The New SETs – SET PDFLINETERM = SPACE
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET PDFLINETERM = SPACE
To transport a PDF via the
specific ftp configuration of
mainframe-thru-unix-to-pc.
Adds a space before delimiter.
“STANDARD” is default.
Not for OCOPY users.
MAINFRAME
Unix
server
PC
FOCUS 7.6 Product Update
The New SETs – SET SHOWBLANKS = ON
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET SHOWBLANKS = ON
 Preserves leading blanks in
HTML and EXL2K outputs by
inserting multiple &nbsp’s (no
backspace)
 Preserves trailing blanks in
headings/footings that use
default alignment
FOCUS 7.6 Product Update
The New SETs – SET SUMMARYLINES = EXPLICIT
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
SET SUMMARYLINES = EXPLICIT
FOCUS 7.3 added
SET SUMMARYLINES=ON
to allow prefixes in summary
lines like:
SUBTOTAL field1 MAX. field2
field3
space
Now, EXPLICIT allows
• SUBTOTAL at one level, and
• SUMMARIZE at another level
FOCUS 7.6 Product Update
The New SETs - ? SET BY CATEGORY
SET ACROSSLINE = OFF
SET BYPANEL ON/n (new: ON TABLE)
SET CDN = QUOTEP
SET CNOTATION = EXPLICIT
SET CNOTATION = PRINTONLY
SET DEFECHO = ON/ALL
SET EMPTYREPORT = ANSI
SET KEEPFILTERS = ON
SET MAXLRECL = 64K
SET MODCOMPUTE = NATV
SET OPTIMIZATION = NOAGGR
SET PERMPASS = password
SET PDFLINETERM = SPACE
SET SHOWBLANKS = ON
SET SUMMARYLINES=EXPLICIT
? SET BY CATEGORY
? SET BY CATEGORY
? SET BY CATEGORY
? SET CATEGORY categoryname
Category
MEMORY
DATES
SECURITY
POOLTABLE
SINK
SEND
COMPUTATION
MDI
EXTERNALSORT
FOCCALC
Names
ENVIRONMENT
WEBFOCUS
REPORT
GRAPH
STYLESHEET
RETRIEVAL
HOLD
PLATFORM
MAINFRAME
MSWINDOWS
FOCUS 7.6 Product Update
XFOCUS Database for Legacy FOCUS Applications
FOCUS DATABASE
4K page size
512 pages per partition
Up to 2-gig per physical file
Up to 255 2-gig partitions
Up to ½ terabyte total size
Segment names up to 8 chars
Index field names up to 12 chars
Page 1 contains FDT and data
More records per page
More pages per file
Larger partition
XFOCUS DATABASE
-16k page size
-1024 pages per partition
-Up to 16-gig per physical file
-Up to 255 16-gig partitions
-Up to 4 terabytes total size
-Segment names up to 64 chars
-Index field names up to 66 chars
-Page 1 dedicated to FDT
Reduced I/O
Fewer partitions
MODIFY keeps working!
Copyright 2007, Information Builders. Slide 36
FOCUS 7.6 Product Update
XFOCUS Database Now Available on SU/FDS
The larger-than-2GB XFOCUS partition
gives extended life to FOCUS applications
All FOCUS syntax and capabilities work with XFOCUS files
 XFOCUS and FOCUS files can be COMBINED –
 Use the page size appropriate for application
 XFOCUS can be MAINTAINed and MODIFY’ed
 XFOCUS files can be used on the Sink Machine –
 Now also known as FOCUS Data Server (FDS)
Copyright 2007, Information Builders. Slide 37
FOCUS 7.6 Product Update
High Performance Multi-Dimensional Index – MDI
MDI provides
virtually instant
access to the
data you need
CAR
MDI has been
in use for years
in our FUSION
product
MODEL
COUNTRY
MDIs can have more than 3 dimensions
FOCUS 7.6 Product Update
MDI - Syntax
ACCESS FILE
MASTERNAME CAR
DATANAME 'CARACX1 FOCUS M'
DATANAME 'CARACX2 FOCUS M'
DATANAME 'CARACX3 FOCUS M'
TABLE FILE CAR
MDILOCATION CARMDI
PRINT BODYTYPE SEATS
TARGET_OF ORIGIN
WHERE CAR EQ 'JAGUAR'
DIMENSION CAR
WHERE COUNTRY EQ 'ENGLAND'
DIMENSION COUNTRY
END
MAXVALUES 100
DIMENSION MODEL
Unlike RDBMS Clustered index,
DATANAME 'CARMDI MDI M'
MDI fields can be in any order
REBUILD MDINDEX
creates new MDIs, or adds new partitions
to an existing MDI
and all fields need not be used
Copyright 2007, Information Builders. Slide 39
FOCUS 7.6 Product Update
Raised FOCUS Limits
MATCH FILE
16 files
JOIN
63 files
FOCSORT
Unlimited size, dynamic growth
MASTER FILE
256 segments
RECORD LENGTH
Read up to 64K
TABLE
32K bytes of displayed fields
ALPHA FIELDS
32K long each, TXT translation
AMPER (&) VARS
32K long each
FIELDNAMES
2K long each
FOCEXEC WIDTH
Unlimited width (use IEDIT to edit)
Copyright 2007, Information Builders. Slide 40
FOCUS 7.6 Product Update
Unlimited FOCSORT
SYSTEM SORT
FOCSORT
Required for
Multi-verb requests
ACROSS or DST.
MATCH FILE
Table MORE
Maximum 2GB
No size limitation
FOC920: FOCSORT HAS
REACHED ITS PAGE LIMIT
FOCSORT limited by
available disk space
Copyright 2007, Information Builders. Slide 41
FOCUS 7.6 Product Update
DATETRAN Subroutine
For the ultimate control in Date formatting
 Month and day translation
 Language
Alpha value  Zero suppression and ordinal suffixes
Maximum data size
 Date delimiters and punctuation
Smart Date format
DEFINE FILE DTFILE
Language
DTDATE/YYMD = LEGACYDATE;
FMTDATE/A15=DATETRAN (DTDATE,’(YYMD)’, ‘Btdo’,’EN’, 15, ‘A15’);
END
DATETRAN
Smart Date
TABLE FILE DTFILE
format
field only
PRINT FMTDATE AMOUNT
BY INVOICE
INVOICE FMTDATE
AMOUNT
END
------11111
11112
11113
------Jan 4th 2005
Feb 2nd 2005
Mar 1st 2005
----$2,333.00
$3,444.00
$4,555.00
Copyright 2007, Information Builders. Slide 42
FOCUS 7.6 Product Update
BY HIERARCHY
For data that can be described as a parent/child hierarchy-CHART OF ACCOUNTS
Intrinsic
10000 ASSETSBILL OF MATERIALS
11000 xxxxxxx
11100
xxxxxxx
1A1A
PART ASSEMBLY
TIME
11200
xxxxxxx
22222
PART1 SUBASSEMBLY 1
Logical
PART1 DIV
YEAR 2005
8786
PART1 SEP
QUARTER 1 SALES ORGANIZATION
33333
PART1 SUBASSEMBLY 2
MONTH January
98765 PART 1 ABS
DAY 1 Eastern Sales Region
PART 1 AMB
District 1
DAY 2
Office New York
...
Natural
Office Boston
MONTH February
District 2
Office Washington
Office Charlotte
Artificial
Copyright 2007, Information Builders. Slide 43
FOCUS 7.6 Product Update
BY HIERARCHY
The parent/child hierarchy can be described in the Master
FILE=CENTGL ,SUFFIX=FOC
SEGNAME=ACCOUNTS, SEGTYPE=S1
FIELDNAME=GL_ACCOUNT, ALIAS=GLACCT, FORMAT=A7,
TITLE='Ledger,Account', FIELDTYPE=I, $
FIELDNAME=GL_ACCOUNT_PARENT, ALIAS=GLPAR, FORMAT=A7,
TITLE=Parent, PROPERTY=PARENT_OF, REFERENCE=GL_ACCOUNT, $
FIELDNAME=GL_ACCOUNT_TYPE, ALIAS=GLTYPE, FORMAT=A1,
TITLE=Type,$
FIELDNAME=GL_ROLLUP_OP, ALIAS=GLROLL, FORMAT=A1,
TITLE=Op, $
FIELDNAME=GL_ACCOUNT_LVLNO, ALIAS=GLLEVEL, FORMAT=I3,
TITLE=Lev,
PROPERTY=LEVEL_NUMBER, REFERENCE=GL_ACCOUNT, $
FIELDNAME=GL_ACCOUNT_CAPTION, ALIAS=GLCAP, FORMAT=A30,
TITLE=Caption, PROPERTY=CAPTION, REFERENCE=GL_ACCOUNT, $
Copyright 2007, Information Builders. Slide 44
FOCUS 7.6 Product Update
BY HIERARCHY
Use the simple BY HIERARCHY syntax -JOIN SYS_ACCOUNT IN CENTGL TO ALL SYS_ACCOUNT IN CENTSYSF AS J1
TABLE FILE CENTGL
SUM NAT_AMOUNT NAT_BUDGET
BY GL_ACCOUNT_CAPTION HIERARCHY
SHOW TO DOWN 3
PAGE SET1STYLE *
ON TABLE
GRID=OFF,$
Caption
TOP
END ------DOWN
Profit Before Tax
3 LEVELS
Gross Margin
Sales Revenue
Retail Sales
INDENT
Mail Order Sales
ON
Internet Sales
LEVEL
Cost Of Goods Sold
Variable Material Costs
Direct Labor
Month
Actual
------320,109.
-4,146,295.
-10,722,253.
-8,448,964.
-1,167,419.
-1,105,870.
6,575,958.
4,957,331.
1,072,250.
Month
Budget
------498,750.
-4,305,000.
-10,605,000.
-8,400,000.
-1,155,000.
-1,050,000.
6,300,000.
4,725,000.
1,050,000.
Copyright 2007, Information Builders. Slide 45
FOCUS 7.6 Product Update
BY HIERARCHY
TABLE SYNTAX
SUM [FROLL.] measure_field
...
BY hierarchy_field [ HIERARCHY
[ WHEN expression_using_hierarchy_fields ; ]
[ SHOW [ TOP | UP n ] [ TO { BOTTOM | DOWN m } ]
[ byoption [ WHEN condition ] ...] ]
…
[ WHERE expression_using_dimension_data ]
…
[ ON hierarchy_field HIERARCHY
[ WHEN expression_using_hierarchy_fields ; ]
[ SHOW [ TOP | UP n ] [ TO BOTTOM | DOWN m ]
[ byoption [ WHEN condition ] ... ] ]
Copyright 2007, Information Builders. Slide 46
FOCUS 7.6 Product Update
ACROSS Summarization
You know the effect of SUB-TOTAL on a BY field…
TABLE FILE GGSALES
SUM UNITS DOLLARS
BY PRODUCT BY REGION
BY DATE
WHERE DATE FROM 19970801 TO 19971231;
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';
ON DATE SUB-TOTAL AS 'REGION TOTAL'
ON TABLE SUB-TOTAL
END
PRODUCT
------Capuccino
REGION
-----Northeast
REGION TOTAL 1997/08/01
.
.
REGION TOTAL 1997/12/01
*TOTAL REGION Northeast
.
DATE
---1997/08/01
UNITS
----1473
DOLLARS
------19486
1473
19486
1188
11551
13668
144742
Copyright 2007, Information Builders. Slide 47
FOCUS 7.6 Product Update
ACROSS Summarization
Now see the effect of SUB-TOTAL on an ACROSS field…
TABLE FILE GGSALES
SUM UNITS OVER DOLLARS
BY PRODUCT
ACROSS REGION ACROSS DATE
ON DATE SUB-TOTAL AS 'REGION'
ON TABLE SUB-TOTAL
WHERE DATE FROM 19970801 TO 19971231;
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';
END
REGION
PRODUCT
------Capuccino
Espresso
TOTAL
UNITS
DOLLARS
UNITS
DOLLARS
UNITS
DOLLARS
Midwest
MNTH
NOV
DEC
----.
.
.
.
2186
1752
27526 22281
2186
1752
27526 22281
West
REGION
-----.
.
3938
49807
3938
49807
TOTAL
NOV
DEC
REGION
---------- ----2535
4051
6586
6586
31153 57421 88574 88574
3088
3732
6820 10758
36123 51400 87523 137330
5623
7783 13406 17344
67276 108821 176097 225904
Copyright 2007, Information Builders. Slide 48
FOCUS 7.6 Product Update
ACROSS Summarization
You know the effect of SUMMARIZE on a BY field…
TABLE FILE GGSALES
SUM UNITS DOLLARS OVER
COMPUTE DOLLPER/I6 = DOLLARS/UNITS; AS 'UNIT PRICE'
BY PRODUCT BY REGION
BY DATE
WHERE DATE FROM 19970801 TO 19971231;
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';
ON DATE SUMMARIZE AS 'REGION TOTAL'
ON TABLE SUMMARIZE
END
PRODUCT
------Capuccino
REGION
-----Northeast
REGION TOTAL 1997/08/01
.
.
REGION TOTAL 1997/12/01
*TOTAL REGION Northeast
DATE
---1997/08/01
UNITS
----1473
DOLLARS
------19486
UNIT PRICE
---------13
1473
19486
13
1188
11551
13668
144742
11
12
Copyright 2007, Information Builders. Slide 49
FOCUS 7.6 Product Update
ACROSS Summarization
Now see the effect of SUMMARIZE on an ACROSS field…
TABLE FILE GGSALES
SUM UNITS DOLLARS OVER
COMPUTE DOLLPER/I6 = DOLLARS/UNITS; AS 'UNIT PRICE'
BY PRODUCT
ACROSS REGION ACROSS DATE
ON DATE SUMMARIZE AS 'REGION'
ON TABLE SUMMARIZE
REGION
WHERE DATE FROM 19970801 TO
19971231;
Midwest
West
WHERE PRODUCT EQ 'Capuccino'
OR
'Espresso';
MNTH
NOV
DEC
REGION NOV
DEC
END PRODUCT
------Capuccino
Espresso
TOTAL
UNITS
DOLLARS
UNIT PRICE
UNITS
DOLLARS
UNIT PRICE
UNITS
DOLLARS
UNIT PRICE
--.
.
.
2186
27526
12
2186
27526
12
--.
.
.
1752
22281
12
1752
22281
12
-----.
.
.
3938
49807
12
3938
49807
12
----2535
4051
31153 57421
12
14
3088
3732
36123 51400
11
13
5623
7783
67276 108821
11
13
TOTAL
REGION
------ ----6586
6586
88574 88574
13
13
6820 10758
87523 137330
12
12
13406 17344
176097 225904
13
13
Copyright 2007, Information Builders. Slide 50
FOCUS 7.6 Product Update
Cluster JOINS
Static JOINS among databases – relational / nonrelational
FROM:
JOIN fld1 IN DB21 TO fld1 IN DB22 AS J1
JOIN fld2 IN DB21 TO fld2 IN DB23 AS J2
JOIN fld3 IN DB21 TO fld3 IN CA-IDMS1 AS J3
JOIN fld4 IN DB21 TO fld4 IN CA-IDMS2 AS J4
JOIN fld5 IN DB21 TO fld5 INIMS AS J5
DB21
TABLE FILE DB21
CA-IDMS1
DB22
IMS
CA-IDMS2
DB23
Copyright 2007, Information Builders. Slide 51
FOCUS 7.6 Product Update
Cluster JOINS
Static JOINS among databases – relational / nonrelational
TABLE FILE DB21
Cross Reference Segments in MASTER
SEGNAME=table2, [SEGSUF=branchsuffix,]
SEGTYPE=relationship,
PARENT=table1 [,CRFILE=crfile2][,$]
FIELD=name,...,$
TO:
DB21
JOIN fields in ACCESS file
CA-IDMS1
DB22
SEGNAME=DB21,TABLENAME=DB21,KEYS=1,$
SEGNAME=DB22,TABLENAME=DB22,KEYS=1,
KEYFLD=fld2, IXFLD=fld2,$
IMS
CA-IDMS2
DB23
Copyright 2007, Information Builders. Slide 52
FOCUS 7.6 Product Update
FOCREPLAY
Test Your Interactive
FOCUS Applications
IN BATCH !!
Quickly test a new FOCUS release or service packTest your critical interactive applications before your users get it.
Test end-of-year reporting NOW against a new FOCUS release, or
a new operating system release, or a new database release.
Build scripts interactively*once, then run them in batch; Repeatable! Verifiable!
Replays scripts at batch speed – no physical screen, no human interaction.
Designed for simplicity - Just Allocate or Filedef, and run FOCUS – that’s it!
Run it online up to any point, then step manually to vary testing scenarios.
*Records session activity only when FOCUS controls the dialog; does not capture operating system interactivity.
Copyright 2007, Information Builders. Slide 53
FOCUS 7.6 Product Update
HOLD FORMAT SAME_DB
Do your HOLD files use different DBMS’s for
Temporary Tables, depending on the source?
ORACLE
DB2
TERADATA
ORACLE
DB2
TERADATA
Copyright 2007, Information Builders. Slide 54
FOCUS 7.6 Product Update
HOLD FORMAT SAME_DB




Verb objects, sorts and COMPUTES included in Master File
Keys created on BY fields
Better than &variable substitution for dbms type
PERSISTENCE determines type of file created • Permanent
TABLE FILE DB2FILE
• Global Temporary PRINT CURR_SAL AS ORIG_SAL
• Volatile
COMPUTE NEWSAL/D12.2=CURR_SAL * 1.10;
BY LAST_NAME
ON TABLE HOLD AS NEWSALS FORMAT SAME_DB
PERSISTENCE VOLATILE
END
Master
FILENAME=NEWSALS, SUFFIX=SQLDB2,$
SEGNAME=NEWSALS, SEGTYPE=S0,$
FIELDNAME=LAST_NAME,ALIAS=LAST_NAME, USAGE=A10, ACTUAL=A10,$
FIELDNAME=ORIG_SAL,ALIAS=ORIG_SAL,USAGE=D12.2, ACTUAL=D8,$
FIELDNAME=NEWSAL,ALIAS=NEWSAL,USAGE=D12.2,ACTUAL=D8,$
Access
SEGNAME=NEWSALS, TABLENAME=NEWSALS,KEYS=1,$
Copyright 2007, Information Builders. Slide 55
FOCUS 7.6 Product Update
SET ERROROUT=OVERRIDE
SET ERROROUT=OFF / ON / OVERRIDE/filename
 OFF (default) continues processing at the next control point
 ON stops processing – RC8 in &FOCERRNUM
 OVERRIDE/filename - alternative error handling for specific messages
1) Create override file as an ERRORS file (read at FOCUS startup)
2) Change selected errors to warnings, or warnings to errors
Syntax in file: error# E(rror) / W(arning)
Example: 205 E
makes a FOC205 warning react like an error
 SET may be placed in FOCPARM for site-wide applicability
(Not permitted with ON TABLE SET)
FOCUS 7.6 Product Update
GROUP Elements
FILE=
SEGMENT=
FIELD=
GROUP=groupname, ALIAS=, FORMAT=A9, ACTUAL=A9
FIELD=field1,, A3,$
FIELD=field2,, A4,$
9 characters
FIELD=field3,, A2,$
ELEMENTS=3
GROUP=groupname, ALIAS=, FORMAT=A??, ACTUAL=A??
FIELD=field1,, A1,$
FIELD=field2,, D12.2,$
how many characters??
FIELD=field3,, I9,$
FOCUS 7.6 Product Update
Even Dialog Manager Gets Better!!
&var.QUOTEDSTRING
-SET &VAR1= ABCD;
-SET &VAR2= 'AB‘ 'CD';
-TYPE &VAR1 &VAR2
-TYPE &VAR1.QUOTEDSTRING
&VAR2.QUOTEDSTRING
ABCD AB'CD
‘ABCD‘ 'AB‘ 'CD’
-INCLUDE fully qualified filename
-INCLUDE ‘XYZ FOCEXEC B’
-INCLUDE MYID.FOCEXEC.DATA(XYZ)
-INCLUDE ‘c:\dir1\subdir2\xyz.fex’
Need quotes?
System &’s in Master DEFINEs
FILE=
SEGMENT=
Which vars?
FIELD=
DEFINE XYZ=&??? * 2 ;
….
&FOCFEXNAME
-INCLUDE TEST2
-TYPE &|FOCFOCEXEC = &FOCFOCEXEC
-TYPE &|FOCFEXNAME = &FOCFEXNAME
&FOCFOCEXEC = TEST2
&FOCFEXNAME = TEST
FOCUS 7.6 Product Update
FILTER in the Master File Description
Put your global filters into the
metadata for general availability *
FILE = filename
MASTER
SEGMENT =
Filter format is I1
FIELD=FIELD1,, A3,$
FIELD=FIELD2,, A4,$
DEFINE DEFFIELD/A7 = FIELD1 | FIELD2 ; ,$
FILTER FILTFIELD = FIELD1 NE FIELD2; ,$
TABLE FILE filename
PRINT FIELD1 FIELD2 DEFFIELD
WHERE FILTFIELD
END
Expression must
resolve to 0 or 1
(false or true)
FOCEXEC
No operator, no
expression
* Works independently of FILTER FILE syntax
FOCUS 7.6 Product Update
DB_LOOKUP in another data source
SFLD1
SFLD1 SFLD2
RETURN
SFLD2
Lookup file
B141
SCONE
FOOD
SFLD3
LOOK1
F103
COFFEE POT
GIFT
LOOK2
LOOK3
RETURN
Retrieve a value from a data source without JOIN or COMBINE
Use in DEFINE or COMPUTE
Match on as many field pairs as needed (up to 63)
If multiple matches, only first is returned
DB_LOOKUP ( infile, sourcefield1, lookupfield1,
sourcefield2, lookupfield2, returnfield )
More efficient than using JOIN ????
FOCUS 7.6 Product Update
Load Fixed Format Sequential Files
MODIFY FILE fixfile
FIXFORM …
MATCH …
ON NOMATCH INCLUDE
DATA …
…
FILE=fixfile, SUFFIX=FIX, …
SEGMENT=…
 Records are added to the end of the file
 INCLUDE only; UPDATE/DELETE not supported
 Enables data types shy to HOLD FORMAT INTERNAL
FOCUS 7.6 Product Update
“RNK.” Prefix for Ranking
TABLE FILE
PRINT FIRSTNAME RNK.SALARY
BY YRS_SERVICE UNDER-LINE BY HIGHEST SALARY
WHERE TOTAL RNK.SALARY LE 2
END
YRS_SERVICE
15
16
17
SALARY
$115,000
$92,000
$99,000
$87,000
$83,000
FIRSTNAME
MOE
LARRY
CURLY
SHEMP
JOE
RANK
SALARY
1
2
1
1
2
 RNK.field must be a BY field in the request
 Not usable in IF/WHERE; only IF/WHERE TOTAL
FOCUS 7.6 Product Update
STRREP Function for Replacing Character Strings
Replace ‘$’
with ‘USD’
TABLE FILE
BY LASTNAME SUM CURR_SAL COMPUTE
CS_ALPHA/A15=FTOA(CURR_SAL,’(D12.2M)’,CS_ALPHA); NOPRINT
CS_USD/A18=STRREP(15,CS_ALPHA,1,’$’,4,’USD ’,18,CS_USD);
END
LASTNAME
CROSS
IRVING
SMITH
STEVENS
CURR_SAL
$29,700
$45,000
$51,200
$99,000
CS_USD
USD 29,700
USD 45,000
USD 51,200
USD 99,000
Note
blank
 Supports replacing null strings too (…,0,’’,3,’ABC’,…)
FOCUS 7.6 Product Update
Left Outer JOIN
Venn
Venn Diagram
Diagram
1 1 Inner
Inner
JOIN
JOIN
Same
Same as
as
SET
ALL=ON
SET ALL=ON
2 2
JOIN
INNER ]] field
IN hostfile
hostfile TO
TO crfield
crfield IN
IN crfile
crfile
JOIN [[ LEFT_OUTER
LEFT_OUTER || INNER
field IN
1
1
Left
Left Outer
Outer JOIN
JOIN
Same
Same as
as
SET
SET ALL=OFF
ALL=OFF
2 2
John Venn
1834 - 1923
Supports
Supports DEFINE-based
DEFINE-based JOINs
JOINs
Supports
Supports Conditional
Conditional JOINs
JOINs
FOCUS 7.6 New Features
Copyright 2007, Information Builders. Slide 65