quote:

Is it more efficient to load the DB with one Master that has no indexes and then use REBUILD to create the indexes?

FILE=SALES1, SUFFIX=XFOC $
SEGMENT = TOP     , SEGTYPE=S1, $
  FIELD = TOP     , ALIAS=TOP     , FORMAT=A6   , FIELDTYPE=I, $
SEGMENT = DIVISION, SEGTYPE=S1, $
  FIELD = DIVISION, ALIAS=DIVISION, FORMAT=A6   , FIELDTYPE=I, $
SEGMENT = AREA    , SEGTYPE=S1, $
  FIELD = AREA    , ALIAS=AREA    , FORMAT=A6   , FIELDTYPE=I, $
SEGMENT = DISTRICT, SEGTYPE=S1, $
  FIELD = DISTRICT, ALIAS=DISTRICT, FORMAT=A6   , FIELDTYPE=I, $
SEGMENT = GROUP   , SEGTYPE=S1, $
  FIELD = GROUP   , ALIAS=GROUP   , FORMAT=A6   , FIELDTYPE=I, $
SEGMENT = UNIT    , SEGTYPE=S3, $
  FIELD = UNIT    , ALIAS=UNIT    , FORMAT=A6   , FIELDTYPE=I, $
  FIELD = ROW_NAME, ALIAS=ROW_NAME, FORMAT=A6   , FIELDTYPE=I, $
  FIELD = COL_NBR , ALIAS=COL_NBR , FORMAT=I2   , FIELDTYPE=I, $
  FIELD = SALES , ALIAS=SALES , FORMAT=P18.2BC, $
  FIELD = RETAIL , ALIAS=RETAIL , FORMAT=P18.2BC, $

-SET &ECHO = 'ALL';

SET LINES      = 999999
SET ASNAMES    = ON
SET HOLDLIST   = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN

SET EXTSORT    = OFF
SET MASTER     = NEW
SET XFOCUS     = ON
SET JOINOPT    = NEW
-*SET XFCBINS = 256
-RUN

TABLE FILE FMDETH1
SUM
SALES/P18.2BC
RETAIL/P18.2BC

BY TOP
BY DIVISION
BY AREA
BY DISTRICT
BY GROUP
BY UNIT

BY ROW_NAME
BY COL_NBR

-*WHERE RECORDLIMIT EQ 100

ON TABLE HOLD AS FMDETH2
END
-RUN

CREATE FILE SALES1
-RUN

MODIFY FILE SALES1
CHECK 100000
FIXFORM FROM FMDETH2

MATCH TOP
  ON MATCH   CONTINUE
  ON NOMATCH INCLUDE

MATCH DIVISION
  ON MATCH   CONTINUE
  ON NOMATCH INCLUDE

MATCH AREA
  ON MATCH   CONTINUE
  ON NOMATCH INCLUDE

MATCH DISTRICT
  ON MATCH   CONTINUE
  ON NOMATCH INCLUDE

MATCH GROUP
  ON MATCH   CONTINUE
  ON NOMATCH INCLUDE

MATCH UNIT ROW_NAME COL_NBR
  ON MATCH   REJECT
  ON NOMATCH INCLUDE

DATA ON FMDETH2
END
-RUN

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

quote:

FOCUS Techniques/Code Examples
Tips for loading very large databases.
Many FOCUS applications require loading very large FOCUS databases. The following considerations help to reduce load time and CPU consumption:
• Have input records sorted (use system sort utility if necessary).
• Limit the number of fields in the Master File Description by using an alternate description with one field spanning over many fields.
• Establish indices after the data is loaded via the INDEX option of the REBUILD Utility. The following steps should be taken:
o Using a Master File Description with FIELDTYPE=I added to all fields that will be indexed, issue the CREATE FILE filename command. This will format the file for data and indices.
o Using a Master File Description with FIELDTYPE=I removed from all fields that will be indexed, issue the MODIFY FILE filename command. This will load the file with data without indices.
o Using a Master File Description with FIELDTYPE=I added to all fields that will be indexed, issue the REBUILD/INDEX command. This will add the indices to the file.
• Remove all COMPUTES and TYPES wherever possible.
• Set the number of bins higher to lessen the number of I/Os. (SET BINS=63).
• Suppress checkpointing or SET CHECK very high. (CHECK OFF or CHECK 10000).
• Avoid large numbers of rejected transactions.
• Avoid updating parent segments before including child segments. A separate routine for updates is preferable.
• Read segments into the MODIFY in the order which they appear in the FOCUS database and use full fieldnames to facilitate the search sequence.
These considerations can be applied together, separately, or in any way that your application dictates. The important point to remember is that there is no database size requirement for these considerations to apply to your application. For example, when loading only 15,000 unsorted transactions into a FOCUS database with 140 fields and 7 indices, the addition of many of the above techniques can reduce load time.
Note: The key to this technique is that it applies to the initial loading of a FOCUS database. However, many of the considerations can also apply to subsequent modifies to that database.