|
Special Feature: Using CHECK FILE HOLD to Document Files
The
CHECK FILE filename command is a very useful tool for checking the accuracy and syntax of a Master File Description (MFD). In addition, in the case of multi-segment and multi-path file structures, the
PICTURE option provides a graphic representation of the file structure that illustrates the paths that FOCUS uses to retrieve data.
An additional option is the use of the
HOLD keyword:
CHECK FILE filename HOLD
This option produces a special
HOLD file with an associated MFD that offers a considerable amount of information about a FOCUS file and allows users to create reports that produce useful documentation. This is especially helpful when files are joined together, as information about the cross-referenced file(s) is included in the
HOLD file. The
HOLD file may be named by specifying the
AS 'filename' option. An extension to the
HOLD option,
ALL, provides additional information about dates and special indices.
In this article, we use the
EMPLOYEE file (Figure 1) to produce some sample reports. A good starting point is to look at the MFD resulting from the following command:
CHECK FILE EMPLOYEE HOLD
(See Figure 2, which contains the
FOCMAST MFD.)
Asterisks indicate the fields in this MFD used to produce reports, as well as other fields of interest. As you see, the available information includes the fieldname, alias, format, and segtype and indicates whether a field is a key and if it is indexed. All of this information can be used to create production control documentation, end-user computing documentation, internal helpdesk information, and documentation for extract or non-FOCUS files. (You will notice that some fields are not marked and parts of the MFD are missing. This is because many of the fields and information are for the internal use of Information Builders technical staff and are not relevant here.)
The following reports illustrate the use of the information in the
HOLD file produced by
CHECK FILE.
1. The first report produces an alphabetic listing of the fieldname, alias, format, the positional number of the field in the MFD, the segment in which the field resides, and whether the field is in a cross-reference file and indexed. The FOCEXEC and output appear in Figure 3.
Figure 1: EMPLOYEE MFD
FILENAME=EMPLOYEE, SUFFIX=FOC
SEGNAME=EMPINFO, SEGTYPE=S1
FIELDNAME=EMP_ID, ALIAS=EID, FORMAT=A9, $
FIELDNAME=LAST_NAME, ALIAS=LN, FORMAT=A15, $
FIELDNAME=FIRST_NAME, ALIAS=FN, FORMAT=A10, $
FIELDNAME=HIRE_DATE, ALIAS=HDT, FORMAT=I6YMD, $
FIELDNAME=DEPARTMENT, ALIAS=DPT, FORMAT=A10, $
FIELDNAME=CURR_SAL, ALIAS=CSAL, FORMAT=D12.2M, $
FIELDNAME=CURR_JOBCODE, ALIAS=CJC, FORMAT=A3, $
FIELDNAME=ED_HRS, ALIAS=OJT, FORMAT=F6.2, $
SEGNAME=FUNDTRAN, SEGTYPE=U, PARENT=EMPINFO
FIELDNAME=BANK_NAME, ALIAS=BN, FORMAT=A20, $
FIELDNAME=BANK_CODE, ALIAS=BC, FORMAT=I6S, $
FIELDNAME=BANK_ACCT, ALIAS=BA, FORMAT=I9S, $
FIELDNAME=EFFECT_DATE, ALIAS=EDATE, FORMAT=I6YMD, $
SEGNAME=PAYINFO, SEGTYPE=SH1, PARENT=EMPINFO
FIELDNAME=DAT_INC, ALIAS=DI, FORMAT=I6YMD, $
FIELDNAME=PCT_INC, ALIAS=PI, FORMAT=F6.2, $
FIELDNAME=SALARY, ALIAS=SAL, FORMAT=D12.2M, $
FIELDNAME=JOBCODE, ALIAS=JBC, FORMAT=A3, $
SEGNAME=ADDRESS, SEGTYPE=S1, PARENT=EMPINFO
FIELDNAME=TYPE, ALIAS=AT, FORMAT=A4, $
FIELDNAME=ADDRESS_LN1, ALIAS=LN1, FORMAT=A20, $
FIELDNAME=ADDRESS_LN2, ALIAS=LN2, FORMAT=A20, $
FIELDNAME=ADDRESS_LN3, ALIAS=LN3, FORMAT=A20, $
FIELDNAME=ACCTNUMBER, ALIAS=ANO, FORMAT=I9L, $
SEGNAME=SALINFO, SEGTYPE=SH1, PARENT=EMPINFO
FIELDNAME=PAY_DATE, ALIAS=PD, FORMAT=I6YMD, $
FIELDNAME=GROSS, ALIAS=MO_PAY, FORMAT=D12.2M, $
SEGNAME=DEDUCT, SEGTYPE=S1, PARENT=SALINFO
FIELDNAME=DED_CODE, ALIAS=DC, FORMAT=A4, $
FIELDNAME=DED_AMT, ALIAS=DA, FORMAT=D12.2M, $
SEGNAME=JOBSEG, SEGTYPE=KU ,PARENT=PAYINFO, CRFILE=JOBFILE, CRKEY=JOBCODE,$
SEGNAME=SECSEG, SEGTYPE=KLU,PARENT=JOBSEG, CRFILE=JOBFILE,$
SEGNAME=SKILLSEG,SEGTYPE=KL, PARENT=JOBSEG, CRFILE=JOBFILE,$
SEGNAME=ATTNDSEG,SEGTYPE=KM, PARENT=EMPINFO, CRFILE=EDUCFILE,CRKEY=EMP_ID,$
SEGNAME=COURSEG, SEGTYPE=KLU,PARENT=ATTNDSEG,CRFILE=EDUCFILE,$
|
Figure 2:
FOCMAST MFD – from
CHECK FILE
FILE=FOCMAST, SUFFIX=FIX,$
SEGMENT=FILEATTR,$
FIELD=RECTYPE,FLE,A4,A4,$
FIELD=FILENAME,FILN12,A8,A8,$ * FILENAME
FIELD-SUFFIX,FILN34,A8,A8,$ * SUFFIX
FIELD=VIEW FIELD,VWFIELD,A12,A12,$ * ROOT VIEW FIELD
FIELD=VIEW INDEX,VWINDEX,A12,A12,$ * ROOT VIEW INDEX
FIELD=REAL ROOT,REALROOT,A8,A8,$ * REAL ROOT SEGMENT
SEGMENT=SEGATTR,PARENT=FILEATTR,$
FIELD=RECTYPE,SEG,A4,A4,$
FIELD=SEGNO,SEGNUM,I2S,I4,$ * SEGMENT NUMBER
FIELD=LEVEL,LLEVELI,I2S,I2,$
FIELO=PARNO,LLEVEL2,I2S,I2,$ * NUMBER OF PARENT SEGMENT
FIELD=LEVTY,LLEVEL3,I2S,I2,$
FIELD=OCCURS,LLEVEL4,I4S,I2,$
FIELD=SEGSIZE,LLEVEL5,15S,I2,$ * FIELD SIZE OF SEGMENT IN BYTES
FIELD=FSTFLD,LLEVEL6,I5S,I2,$ * FIELD NO. OF 1ST FIELD IN SEGMENT
FIELD=LSTFLD,LLEVEL7,I5S,I2,$ * FIELD NO. OF LAST FIELD IN SEGMENT
FIELD=NUMDEC,LLEVELB,I2S,I2,$ * NO. OF DESCENDANT SEGMENT
FIELD=FDEC,LLEVEL9,I2S,I2,$ * SEGMENT NO. OF 1ST DESCENDANT
FIELD=FTWIN,LLEVEL1O,I2S,I2,$
FIELD=SKEYS,LLEVELll,I2S,I2,$ * NO. OF KEY FIELDS
FIELD=VKEYRC,LLEVELI2,I4S,I2,$
FIELD=SEGNAME,SEGMENT,A8,A8,$ * SEGMENT NAME
FIELD=PATH,LLEVEL17,I2S,I2,$ * NO. DESIGNATION FOR PATH
.
.
.
FIELD=PARENT,PARSEG,A8,A8,$ * PARENT OF SEGMENT
FIELD=SEGTYPE,SEGT,A4,A4,$ * SEGMENT TYPE
FIELD=CR INDEX,CRINDEX,A12,A12,$ * X-REF INDEX
FIELD=CRKEY,CRKEY,A12,A12,$ * X-REF KEY
FIELD=CRFILE,CRFILE,A8,A8,$ * X-REF FILE
FIELD=TAGNAME,TAG,A8,A8,$
SEGMENT=FLDATTR,PARENT=SEGATTR,$
FIELD=RECTYPE,FLD,A4,A4,$
FIELD=FLDNO,FLDNUM,I4S,I4,$ * FIELD NO.
FIELD=FIELDNAME,FIELD,A66,A66,$ * FIELD NAME
FIELD=ALIAS,SYNONYM,A66,A66,$ * ALIAS
FIELD=FORMAT,USAGE,A8,A8,$ * FORMAT/USAGE
FIELD=AUTHORITY,AUTHCODE,A8,A8,$ * DBA INFORMATION
FIELD=FLDSEG,RECDIS21,I2S,I2,$ * SEGMENT NO.
FIELD=FTYPE,RECDIS22,I2S,I2,$ * FIELD FORMAT CODE
FIELD=DEC,RECDIS23,I2S,I2,$ * NO. OF DECIMAL PLACES
FIELD=FLEN,RECDIS24,I3S,I2,$ * FIELD STORAGE IN BYTES
FIELD=WLEN,RECDIS25,I3S,I2,$
FIELD=EXLEN,RECDIS26,I3S,I2,$ * DISPLAY LENGTH
FIELD=OFFSET,RECDIS27,I4,I2,$
.
.
.
FIELD=INDX,RECDIS34,I2S,I2,$ * FIELD NO. OF INDEXED FIELD
FIELD=KIND,RECDIS3536,U4,A4,$
FIELD=ACTUAL,ACTUAL,A8,A8,$ * ACTUAL
FIELD=IXGRDUPELM,KINDB1I,A1,Al,$
FIELD=GRDUPELM,KINDBI2,A1,Al,$
FIELD=KINDBI3,KMISSING,A1,Al,$ * MISSING ATTRIBUTE
FIELD=HASIXELM,KINDBI4,Al,Al,$
FIELD=MIXFDRM,KINDB15,Al,Al,$
FIELD=GRDUP,KINDB16,Al,Al,$
FIELD=HELPMESSAGE,MESSAGE,A80,A80,$ * HELPMESSAGE ATTRIBUTE
FIELD=TITLE,TITLE,A64,A64,$ * TITLE ATTRIBUTE
|
Figure 3: Alphabetic Listing of Fieldname, Alias, Format, etc.
FOCEXEC
-* THIS PRODUCES AN ALPHA LISTING OF FIELDNAME,
_* ALIASES AND FORMAT FOR A FILE
-* FOR THIS AND ALL REPORTS, FIELDNAME HAS BEEN SHORTENED FROM
_* 66 TO 12 CHARS, BY CREATING THE DEFINED FIELD, XFIELDNAME.
SET PAGE=OFF
DEFINE FILE HOLD
SWI/A8=IF INDX NE 0 THEN 'INDEXED' ELSE ' ';
XFIELDNAME/A12=EDIT(FIELDNAME,'999999999999');
END
TABLE FILE HOLD
HEADING
"<12 AN ALPHA LISTING DF FIELD NAMES FDR THE EMPLDYEE FILE"
"<24 SUFFIX FDR FILE IS -- <SUFFIX"
"<12 *****************************************************"
" "
PRINT ALIAS IN 20 USAGE IN 30 FLDNO IN 40
SEGMENT CRFILE SWI AS 'INDEX,FIELD'
BY XFIELDNAME AS 'FIELDNAME'
END
|
Figure 3 Output
AN ALPHA LISTING DF FIELD NAMES FDR THE EMPLDYEE FILE
SUFFIX FOR FILE IS -- FOC
*****************************************************
INDEX
FIELDNAME ALIAS FORMAT FLDNO SEGNAME CRFILE FIELD
--------- ----- ------ ----- ------- ------ -----
ACCTNUMBER ANO I9L 21 ADDRESS
ADDRESS_LN1 LN1 A20 18 ADDRESS
ADDRESS_LN2 LN2 A20 19 ADDRESS
ADDRESS_LN3 LN3 A20 20 ADDRESS
BANK_ACCT BA I9S 11 FUNDTRAN
BANK_CODE BC I6S 10 FUNDTRAN
BANK_NAME BN A20 9 FUNDTRAN
COURSE_CODE CC A6 33 COURSEG EDUCFILE
COURSE_NAME CD A30 34 COURSEG EDUCFILE
CURR_JOBCODE CJC A3 7 EMPINFO
CURR_SAL CSAL D12.2M 6 EMPINFO
DAT_INC DI I6YMD 13 PAYINFO
DATE_ATTEND DA I6YMD 31 ATTNDSEG EDUCFILE
DED_AMT DA D12.2M 25 DEDUCT
DED_CODE DC A4 24 DEDUCT
DEPARTMENT DPT A10 5 EMPINFO
ED_HRS OJT F6.2 8 EMPINFO
EFFECT_DATE EDATE I6YMD 12 FUNDTRAN
EMP_ID EID A9 1 EMPINFO
EID A9 32 ATTNDSEG EDUCFILE INDEXED
FIRST_NAME FN A10 3 EMPINFO
GROSS MO_PAY D12.2M 23 SALINFO
HIRE_DATE HDT I6YMD 4 EMPINFO
JOB_DESC JD A25 27 JOBSEG JOBFILE
JOBCODE JBC A3 16 PAYINFO
JC A3 26 JOBSEG JOBFILE INDEXED
LAST_NAME LN A15 2 EMPINFO
PAY_DATE PD I6YMD 22 SALINFO
PCT_INC PI F6.2 14 PAYINFO
SALARY SAL D12.2M 15 PAYINFO
SEC_CLEAR SC A6 28 SECSEG JOBFILE
SKILL_DESC SD A30 30 SKILLSEG JOBFILE
SKILLS A4 29 SKILLSEG JOBFILE
TYPE AT A4 17 ADDRESS
|
2. The second report (Figure 4)
is a simple report that shows the fieldname, alias, and format. What makes this report
different is that it is sorted by the type of format.
Figure 4: Fieldname, Alias, and Format Sorted by
Type of Format
FOCEXEC
-* THIS PR0CEDURE PR0DUCES A SIMPLE LISTING 0F THE FIELD NAME,
-* ALIAS, AND FORMAT, BY FORMAT TYPE.
DEFINE FILE HOLD
NAME/A20=DECODE FTYPE( 1 'INTEGER, - I' 2 'SINGLE PRECISION - F'
3 'DOUBLE PRECISION - D' 4 'PACKED' - P' 6 'ALPHANUMERIC' A');
ORDER/I2= IF FTYPE EQ 6 THEN 0 ELSE FTYPE;
XFIELDNAME/A12=EDIT(FIELDNAME,'999999999999');
END
TABLE FILE HOLD
HEADING
"<12 A FIELD NAME LIST BY TYPE OF FORMAT"
"<12 ***********************************"
" "
PRINT ALIAS AS '' IN 30 USAGE AS '' IN 40
BY ORDER NOPRINT
BY XFIELDNAME AS '' IN 10
ON ORDER SUBHEAD
"FORMAT OF FIELDS IS: <NAME"
" "
" FIELDNAME ALIAS FORMAT"
" --------- ----- ------"
" "
ON ORDER NOSPLIT
END
|
Figure 4 Output
A FIELD NAME LIST BY TYPE OF FORMAT
***********************************
FORMAT OF FIELDS IS: ALPHANUMERIC - A
FIELDNAME ALIAS FORMAT
--------- ----- ------
ADDRESS_LN1 LN1 A20
ADDRESS_LN2 LN2 A20
ADDRESS_LN3 LN3 A20
BANK_NAME BN A20
COURSE_CODE CC A6
COURSE_NAME CD A30
CURR_JOBCODE CJC A3
DED_CODE DC A4
DEPARTMENT DPT A10
EMP_ID EID A9
EID A9
FIRST_NAME FN A10
JOB_DESC JD A25
JOBCODE JBC A3
JC A3
LAST_NAME LN A15
SEC_CLEAR SC A6
SKILL_DESC SD A30
SKILLS A4
TYPE AT A4
FORMAT OF FIELDS IS: INTEGER - I
FIELDNAME ALIAS FORMAT
--------- ----- ------
ACCTNUMBER ANO I9L
BANK_ACCT BA I9S
BANK_CODE BC I6S
DAT_INC DI I6YMD
DATE_ATTEND DA I6YMD
EFFECT_DATE EDATE I6YMD
HIRE_DATE HDT I6YMD
PAY_DATE PD I6YMD
FORMAT OF FIELDS IS: SINGLE PRECISION - F
FIELDNAME ALIAS FORMAT
--------- ----- ------
ED_HRS OJT F6.2
PCT_INC PI F6.2
FORMAT OF FIELDS IS: DOUBLE PRECISION - D
FIELDNAME ALIAS FORMAT
--------- ----- ------
CURR_SAL CSAL D12.2M
DED_AMT DA D12.2M
GROSS MO_PAY D12.2M
SALARY SAL D12.2M
|
3. The next report (Figure 5) provides
information that does not appear in the first two reports. It shows the space
requirements – internal bytes of storage – for each field. This is very handy
when calculating total storage requirements or deciding if certain field
formats should be changed. In addition, we see the display length of each
field, which is helpful in designing report layouts.
Figure 5: Space Required by Each Field
-* THIS PROCEDURE PRODUCES AN ALPHA LIST OF FIELDNAMES IN
-* A FILE. IT PROVIDES INFORMATION ON THE INTERNAL STORAGE
-* LENGTH AND THE DISPLAY LENGTH. THIS CAN BE USEFUL IN
-* DETERMINING A FILE'S STORAGE REOUIREMENTS AND REPORT LAYOUTS.
-* THE REPORT ALSO INDICATES WHICH FIELDS ARE ACTUALLY IN
-* CROSS-REFERENCE FILE
DEFINE FILE HOLD
XFIELDNAME/A12=EDIT(FIELDNAME,'999999999999');
END
TABLE FILE HOLD
HEADING
"<12 A LISTING OF FIELD INTERNAL STORAGE AND DISPLAY LENGTH"
"<21 FOR THE EMPLOYEE FILE -- SUFFIX = <SUFFIX"
"<12 ******************************************************"
" "
" "
PRINT ALIAS IN 20 USAGE IN 30
FLEN AS 'BYTES OF,STORAGE' IN 40
EXLEN AS 'DISPLAY,LENGTH' IN 50
CRFILE AS 'X-REF,FILE' IN 60
BY XFIELDNAME AS 'FIELD NAME'
END
|
Figure 5 Output
A LISTING OF FIELD INTERNAL STORAGE AND DISPLAY LENGTH
FOR THE EMPLOYEE FILE -- SUFFIX = FOC
******************************************************
BYTES Of DISPLAY X-REF
FIELD NAME ALIAS FORMAT STORAGE LENGTH FILE
---------- ----- ------ -------- ------- -----
ACCTNUMBER ANO I9L 4 9
ADDRESS_LN1 LN1 A20 20 20
ADDRESS_LN2 LN2 A20 20 20
ADDRESS_LN3 LN3 A20 20 20
BANK_ACCT BA I9S 4 9
BANK_CODE BC I6S 4 6
BANK_NAME BN A20 20 20
COURSE_CODE CC A6 6 6 EDUCFILE
COURSE_NAME CD A30 30 30 EDUCFILE
CURR_JOBCODE CJC A3 3 3
CURR_SAL CSAL D12.2M 8 15
DAT_INC DI I6YMD 4 8
DATE_ATTEND DA I6YMD 4 8 EDUCFILE
DED_AMT DA D12.2M 8 15
DED_CODE DC A4 4 4
DEPARTMENT DPT A10 10 10
ED_HRS OJT F6.2 4 6
EFFECT_DATE EDATE I6YMD 4 8
EMP_ID EID A9 9 9
EID A9 9 9 EDUCFILE
FIRST_NAME FN A10 10 10
GROSS MO_PAY D12.2M 8 15
HIRE_DATE HDT I6YMD 4 8
JOB_DESC JD A25 25 25 JOBFILE
JOBCODE JBC A3 3 3
JC A3 3 3 JOBFILE
LAST_NAME LN A15 15 15
PAY_DATE PD I6YMD 4 8
PCT_INC PI F6.2 4 6
SALARY SAL D12.2M 8 15
SEC_CLEAR SC A6 6 6 JOBFILE
SKILL_DESC SD A30 30 30 JOBFILE
SKILLS A4 4 4 JOBFILE
TYPE AT A4 4 4
|
4. The fourth report (Figure 6)
gives much of the information seen in the previous reports, but broken
down on a segment-by-segment basis. In addition, we are given the segment number,
the segtype, the number of keys in that segment, and the fields that are key
fields (indicated by the word KEY).
Figure 6: Segment Information
FOCEXEC
-* THIS PROCEDURE WILL PRODUCE A LIST OF FIELD NAMES BY SEGMENT,
-* ALONG WITH THE ALIAS, FIELD FORMAT, AND INDICATE KEY FIELDS
-* IN EACH SEGMENT.
-* THIS REPORT ALSO INCLUDES CROSS-REFERENCE SEGMENT INFORMATION
DEFINE FILE HOLD
FIRST/I3 = FSTFLD+SKEYS-1;
KEY1/A3 = IF SEGTYPE CONTAINS 'U' THEN ' ' ELSE
IF FLDNO GE FSTFLD AND FLDNO LE FIRST THEN 'KEY' ELSE ' ';
KEY2/I3 = IF SEGTYPE CONTAINS 'U' THEN 0 ELSE SKEYS;
XFIELDNAME/A12=EDIT(FIELDNAME,'999999999999');
END
TABLE FILE HOLD
HEADING
"A LISTING OF FIELD NAMES ARRANGED BY SEGMENT"
"FOR THE EMPLOYEE FILE -- SUFFIX = <SUFFIX"
"********************************************"
" "
PRINT ALIAS AS '' IN 20 USAGE AS '' IN 30
KEY1 AS '' IN 40
BY SEGNO NOPRINT BY SEGMENT NOPRINT BY XFIELDNAME AS ''
ON SEGMENT SUBHEAD
" "
"SEGMENT: <SEGNO -- <SEGMENT SEGTYPE: <SEGTYPE NO. OF KEYS: <KEY2"
"------------------------------------------------------"
" "
" <1 FIELD NAME <20 ALIAS <30 FORMAT <40 KEY FIELD"
" <1 ---------- <20 ----- <30 ------ <40 ---------"
ON SEGMENT SKIP-LINE
ON SEGMENT NOSPLIT
END |
Figure 6 Output
A LISTING OF FIELD NAMES ARRANGED BY SEGMENT
FOR THE EMPLOYEE FILE -- SUFFIX = FOC
********************************************
SEGMENT: 1 -- EMPINFO SEGTYPE: S NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
CURR_JOBCODE CJC A3
CURR_SAL CSAL D12.2M
DEPARTMENT DPT A10
ED_HRS OJT F6.2
EMP_ID EID A9 KEY
FIRST_NAME FN A10
HIRE_DATE HDT I6YMD
LAST_NAME LN A15
SEGMENT: 2 -- FUNDTRAN SEGTYPE: U NO. OF KEYS: 0
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
BANK_ACCT BA I9S
BANK_CODE BC I6S
BANK_NAME BN A20
EFFECT_DATE EDATE I6YMD
SEGMENT: 3 -- PAYINFO SEGTYPE: SH NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
DAT_INC DI I6YMD KEY
JOBCODE JBC A3
PCT_INC PI F6.2
SALARY SAL D12.2M
SEGMENT: 4 -- JOBSEG SEGTYPE: KU NO. OF KEYS: 0
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
JOB_DESC JD A25
JOBCODE JC A3
SEGMENT: 5 -- SECSEG SEGTYPE: KLU NO. OF KEYS: 0
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
SEC_CLEAR SC A6
SEGMENT: 6 -- SKILLSEG SEGTYPE: KL NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
SKILL_DESC SD A30
SKILLS A4 KEY
SEGMENT: 7 -- ADDRESS SEGTYPE: S NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
ACCTNUMBER ANO I9L
ADDRESS_LN1 LN1 A20
ADDRESS_LN2 LN2 A20
ADDRESS_LN3 LN3 A20
TYPE AT A4 KEY
SEGMENT: 8 -- SALINFO SEGTYPE: SH NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
GROSS MO_PAY D12.2M
PAY_DATE PD I6YMD KEY
SEGMENT: 9 -- DEDUCT SEGTYPE: S NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
DED_AMT DA D12.2M
DED_CODE DC A4 KEY
SEGMENT: 10 -- ATTNDSEG SEGTYPE: KM NO. OF KEYS: 1
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
DATE_ATTEND DA I6YMD KEY
EMP_ID EID A9
SEGMENT: 11 -- COURSEG SEGTYPE: KLU NO. OF KEYS: 0
------------------------------------------------------
FIELD NAME ALIAS FORMAT KEY FIELD
---------- ----- ------ ---------
COURSE_CODE CC A6
COURSE_NAME CD A30
|
5. The next report (Figure 7)
allows us to check for the presence of duplicate fieldnames in files. In this
report we see like-named fields in cross-reference files.
Figure 7: Duplicate Fieldnames
FOCEXEC
-* THIS PROCEDURE WILL PRODUCE A LISTING OF DUPLICATE FIELDNAMES.
-* THIS CAN BE USEFUL BOTH FOR FILES WITHOUT ANY CROSS-REFERENCES.
-* AS WELL AS IDENTIFY LIKE FIELDNAMES THAT ACTUALLY ARE PART OF
-* A CROSS-REFERENCE STRUCTURE.
DEFINE FILE HOLD
XFIELDNAME/A12=EDIT(FIELDNAME,'999999999999');
END
TABLE FILE HOLD
HEADING
" <20 A LISTING OF DUPLICATE FIELD NAMES"
" <20 **********************************"
" "
COUNT FIELDNAME IN 15
BY XFIELDNAME AS 'FIELDNAME'
PRINT ALIAS IN 30 USAGE IN 40
SEGMENT AS 'SEGMENT,NAME' CRFILE AS 'X-REF,FILE'
BY XFIELDNAME AS 'FIELDNAME'
IF TOTAL COUNT NE 1
END |
Figure 7 Output
A LISTING OF DUPLICATE FIELD NAMES
**********************************
FIELDNAME SEGMENT X-REF
FIELDNAME COUNT ALIAS FORMAT NAME FILE
--------- --------- ----- ------ ------- -----
EMP_ID 2 EID A9 EMPINFO
EID A9 ATTNDSEG EDUCFILE
JOBCODE 2 JBC A3 PAYINFO
JC A3 JOBSEG JOBFILE
|
6. The sixth report (Figure 8)
produces a segment analysis showing each segment, the segtype, the length of all the
fields in the segment, the length of the internal pointers, the total segment length
and the number of instances per page.
Figure 8: Segment Analysis
FOCEXEC
-* THIS PROCEDURE USES THE HOLD FILE PRODUCED BY
-* CHECK FILE HOLD, AND PRODUCES INFORMATION REGARDING
-* THE LENGTH OF FIELDS PER SEGMENT, THE LENGTH OF POINTERS,
-* THE TOTAL SEGMENT LENGTH, AND INSTANCES PER PAGE
DEFINE FILE HOLD
KEY1/I2=IF SEGTYPE CONTAINS 'U' THEN 0 ELSE SKEYS;
KEY2/A2=EDIT(KEYl);
SEGTYP/A6=IF SEGTYPE EQ 'S' OR 'SH' THEN SEGTYPE||KEY2 ELSE SEGTYPE;
PNTRS/I4S=IF SEGNO EQ 1 THEN NUMDEC + 1 ELSE NUMDEC + 2;
SEG1/I4S=INT((SEGSIZE+3)/4);
SEG2/I4S=SEG1 * 4;
PNTR_LEN/I4S=PNTRS * 4;
TOT_LEN/I4S=SEG2 + PNTR_LEN;
END
TABLE FILE HOLD
HEADING
"AN ANALYSIS OF SEGMENTS IN THE EMPLOYEE FILE"
" "
PRINT SEGTYP AS 'SEGMENT,TYPE'
SEG2 AS 'LENGTH OF,FIELDS'
PNTR_LEN AS 'POINTER,LENGTH'
TOT_LEN AS 'TOTAL,SEGMENT,LENGTH'
AND COMPUTE
PAGE_INST/I4S=3968/TOT_LEN; AS 'INSTANCES,PER PAGE'
BY SEGNO AS 'SEGMENT,NUMBER'
BY SEGNAME AS 'SEGMENT,NAME'
END |
Figure 8 Output
AN ANALYSIS OF SEGMENTS IN THE EMPLOYEE FILE
TOTAL
SEGMENT SEGMENT SEGMENT LENGTH OF POINTER SEGMENT INSTANCES
NUMBER NAME TYPE FIELDS LENGTH LENGTH PER PAGE
------- ------- ------- --------- ------- ------- ---------
1 EMPINFO S01 64 24 88 45
2 FUNDTRAN U 32 8 40 99
3 PAYINFO SH01 20 12 32 124
4 JOBSEG KU 28 16 44 90
5 SECSEG KLU 8 8 16 248
6 SKILLSEG KL 36 8 44 90
7 ADDRESS S01 68 8 76 52
8 SALINFO SH01 12 12 24 165
9 DEDUCT S01 12 8 20 198
10 ATTNDSEG KM 16 12 28 141
11 COURSEG KLU 36 8 44 90
|
These same reports, with little modification, can be used to document
non-FOCUS files and extract files produced by the ON
TABLE HOLD syntax. To illustrate this, I have chosen the
ECOURSE and
COURSE files used in the current Relational Data Adapter manual
(see Figure 9 for the MFDs).
This final report (Figure 10) is based on a
CHECK FILE filename
HOLD of a DB2 table. As you can see, the report shows the fieldnames, the actual, the usage,
the presence of remote segments for read-only access, and whether the
MISSING attribute is used for certain fields.
Figure 9: ECOURSE and COURSE MFDs
ECOURSE MASTER FILE
FILENAME=ECOURSE, SUFFIX=SQLDS, $
SEGNAME=EMPINFO, SEGTYPE=S0, $
FIELD=EMP_ID ,ALIAS=EID ,USAGE=A9 ,ACTUAL=A9,$
FIELD=LAST_NAME ,ALIAS=LN ,USAGE=A15 ,ACTUAL=A15,$
FIELD=FIRST_NAME ,ALIAS=FN ,USAGE=A10 ,ACTUAL=A10,$
FIELD=HIRE_DATE ,ALIAS=HDT ,USAGE=YMD ,ACTUAL=DATE,$
FIELD=DEPARTMENT ,ALIAS=DPT ,USAGE=A10 ,ACTUAL=A10,
MISSING=ON,$
FIELD=CURRENT_SALARY,ALIAS=CSAL ,USAGE=P9.2 ,ACTUAL=P4,$
FIELD=CURR_JOBCODE ,ALIAS=CJC ,USAGE=A3 ,ACTUAL=A3,$
FIELD=ED_HRS ,ALIAS=OJT ,USAGE=F6.2 ,ACTUAL=F4,
MISSING=ON,$
FIELD=BONUS_PLAN ,ALIAS=BONUS_PLAN ,USAGE=I4 ,ACTUAL=I4,$
SEGNAME=COURSE ,SEGTYPE=KL ,PARENT=EMPINFO, CRFILE=COURSE,$
|
COURSE MASTER FILE
$ THIS IS THE CROSS-REFERENCE FILE USED FOR PRODUCING A
$ REPORT FOR A NON-FOCUS FILE FILENAME=COURSE, SUFFIX=SQLDS,$
FILENAME=COURSE ,SUFFIX=SQLDS,$
SEGNAME=COURSE ,SEGTYPE=S0 ,$
FIELD=CNAME ,ALIAS=COURSE_NAME ,USAGE=A15, ACTUAL=A15,$
FIELD=WHO ,ALIAS=EMP_NO ,USAGE=A9, ACTUAL=A9,$
FIELD=GRADE ,ALIAS=GRADE ,USAGE=A1, ACTUAL=A1, MISSING=ON,$
FIELD=YR_TAKEN,ALIAS=YR_TAKEN ,USAGE=A2, ACTUAL=A2,$
FIELD=QTR ,ALIAS=QUARTER ,USAGE=A1, ACTUAL=A1,$ |
Figure 10: Report for Non-FOCUS File
FOCEXEC
CHECK FILE ECOURSE HOLD
-* THIS PRODUCES AN ALPHA LISTING OF FIELDNAME, ALIASES,
-* AND FORMAT FOR A NON-FOCUS FILE.
-* NOTICE, THAT IN THIS PROCEDURE WE ARE INDICATING WHETHER A
-* FIELD HAS THE 'MISSING' ATTRIBUTE IN THE MASTER.
-* THIS FEATURE IS HANDY FOR FOCUS FILES.
DEFINE FILE HOLD
ACT/A8=IF USAGE EQ 'YMD' THEN 'DATE' ELSE ACTUAL;
XFIELDNAME/A12=EDIT(FIELDNAME,'999999999999');
END
TABLE FILE HOLD
HEADING
"<12 AN ALPHA LISTING OF FIELD NAMES FOR THE EMPLOYEE FILE"
"<21 SUFFIX FOR FILE IS -- <SUFFIX"
"<12 ******************************************************"
" "
PRINT ALIAS IN 20 ACT AS 'ACTUAL' IN 30 USAGE AS 'USAGE' IN 40
SEGMENT CRFILE AS 'REMOTE,SEGMENT' KMISSING AS 'MISSING,SET ON'
BY XFIELDNAME AS 'FIELDNAME'
END |
Figure 10 Output
AN ALPHA LISTING OF FIELD NAMES FOR THE EMPLOYEE FILE
SUFFIX FOR FILE IS -- SQLDS
******************************************************
REMOTE MISSING
FIELDNAME ALIAS ACTUAL USAGE SEGNAME SEGMENT SET ON
--------- ----- ------ ----- ------- ------- -------
BONUS_PLAN BONUS I004 I4 EMPINFO
CNAME COURSE A015 A15 COURSE COURSE
CURR_JOBCODE CJC A003 A3 EMPINFO
CURRENT_SALARY CSAL P004 P9.2 EMPINFO
DEPARTMENT DPT A010 A10 EMPINFO Y
ED_HRS OJT F004 F6.2 EMPINFO Y
EMP_ID EID A009 A9 EMPINFO
FIRST_NAME FN A010 A10 EMPINFO
GRADE GRADE A001 A1 COURSE COURSE Y
HIRE_DATE HDT DATE YMD EMPINFO
LAST_NAME LN A015 A15 EMPINFO
QTR QUARTER A001 A1 COURSE COURSE
WHO EMP_NO A009 A9 COURSE COURSE
YR_TAKEN YR_TAKEN A002 A2 COURSE COURSE |
|
|
