DECUS U. S. Chapter SIGs Newsletter, Volume 5, Number 1 September 1989
Wombat Examiner, Volume 11, Number 1
----------------------------------------------------------------
DTR/DECREPORTER: User's Friend
Jim Copenhaver, Simons-Eastern Consultants, Inc., Decatur, GA
----------------------------------------------------------------
The Computer Systems and Support department at Simons (Atlanta
Region) was presented with a challenging problem. We had a
rapidly growing user population comprised of managers, department
heads, and staff personnel that had little exposure to a VAX and
the flexibility and versatility available to them. This group,
almost overnight, required access to data that had previously
only been available to them via weekly reports. Each individual
had specific needs for extracting data that varied drastically
from user to user. Because of the large number of users, it was
not possible for us to tailor the reports for each specific user.
Since many of our weekly reports were produced with DATATRIEVE,
it seemed like DATATRIEVE would be the logical solution for
reporting problems. Unfortunately, several new users were
confused by DATATRIEVE and cluttered up the CDD with aborted
procedures. Our solution was to use DECREPORTER in conjunction
with DATATRIEVE to provide the users with a platform for
producing reports. We called this platform "Simons_Reporter".
I took a "cookbook" approach that will allow anyone (with a
familiarity with the CDD, DATATRIEVE, and DECREPORTER) to set up
a similar platform.
I reduced the process down to seven phases. Each phase, if
followed, will step you through the creation of the platform.
PHASE 1:
For those unfamiliar with the CDD, the first phase is the most
important and the most difficult to implement. It involves using
the CDD utility DMU and how the CDD protections differ from those
of VMS. If anyone needs to look up what, exactly the privileges
shown here are, please refer to page A-48 in the VAX/CDD/Plus
User's Guide (VAX/CDD/Plus V4.0).
The following DMU commands will create either a CDD subdictionary
or a CDD directory. Most applications will not need to create a
separate subdictionary, and the *.dic file associated with it.
Your specific needs may require this for security, space
considerations, or some other reason, so it is listed here.
DMU> CREATE CDD$TOP.SYSTEMS.SIMONS_REPORTER
DMU> CREATE/SUBDICTIONARY=CDD$DSK:SIMONS_REPORTER.DIC SIMONS_REPORTER
Here I have entered DMU and created the directory to house my
platform. Notice that I specified the entire CDD path name for
this dictionary. The New CDD is more selective and will not
allow you to be as "careless" when you create a dictionary using
DMU. Be sure that if you create a subdictionary that the *.dic
file created has the VMS protections set to allow proper user
access.
Protection on the subdictionary and some of its objects might
look like:
_CDD$TOP.SYSTEMS.SIMONS_REPORTER
1. [*,*], Username: "DBADMIN"
Grant - CDHPSX, Deny - none, Banish - none
2. [ENG_APPL]
Grant - DEMPRSUWX, Deny - none, Banish - none
3. [*,*], Username: "JCOPENHAVER"
Grant - DEMPRSUWX, Deny - none, Banish - none
4. [*,*]
Grant - EPRS, Deny - none, Banish - none
SIMONS_REPORTER.TIME_REPORTING_SYSTEM;2
Access Control List is empty
SIMONS_REPORTER.TIME_REPORTING_SYSTEM_REC;1
Access Control List is empty
Here, I have several commands to set the protections on the new
directory. These protections allow specific users or groups of
users limited access to the directory. The EPRS protection for
the general users is most important. This allows users to access
all of the existing reports or to create new reports. This will
become more important later. For the first position, we have a
user called "DBADMIN". This is a user we created to manage
everything in the CDD. We make sure that whenever we create a
new dictionary, DBADMIN is the user that occupies position 1.
For position 2, we have a group called "ENG_APPL" that actually
maintains this directory. The protections have been opened up
for them (and for me on position 3). For position 4, the general
users have only EPRS. These protections are important at this
point because using DMU to create the directory and set the
protections allows the users to access the domains, record
definitions, and procedures using INHERITED privileges. Using
the CDD's CDO utility will not allow this flexibility.
When you decide to implement CDD Security features, you should
also keep in mind that VMS file protections and file ownership
have not changed! Just because you grant someone write access in
the CDD doesn't necessarily mean that the person has write access
on the RMS level.
PHASE 2:
The second phase of setting up the platform is performed at the
DCL level. While this phase is not "critical", it adds
functionality and ease of operation to the users.
These commands (modified to suit your environment) should be
added to your system startup:
$ DEFINE/SYSTEM/NOLOG SIMONS_REPORTER "_CDD$TOP.SYSTEMS.SIMONS_REPORTER"
$ SIMONS_REP*ORTER:==REPORT/PATH=SIMONS_REPORTER
This shows how we defined a logical for the CDD directory we used
as the platform. By creating a logical for the CDD path, this
keeps users from wandering around the CDD and causing problems
for other users. The symbol we created uses the same name as the
logical. Few users would check to see if a symbol was also a
logical. This symbol and logical were created at system startup.
A command procedure executed at login time would look like:
$ CDD_DICT := "DOESNT_EXIST"
$ ACCOUNT = "''F$GETJPI("","ACCOUNT")'"
$ ACCOUNT = F$EDIT(ACCOUNT,"TRIM")'
$ USERNAME = "''F$GETJPI("","USERNAME")'"
$ USERNAME = F$EDIT(USERNAME,"TRIM")
.
.
.
$ IF ACCOUNT .EQS. "CMPT_SER" THEN CDD_DICT := ISD$LIBRARY
.
.
.
$ DEFINE/NOLOG CDD$DEFAULT "_CDD$TOP.''CDD_DICT'.''USERNAME'"
$ DEFINE/NOLOG MY_CDD "''F$TRNLNM("CDD$DEFAULT")'"
Here is a key element in setting up this phase. The command
procedure shown is accessed when a user first logs in. Most
users don't know that this even exists. When we add a user to
the system, we also add them a personal CDD subdirectory. This
we have keyed to the "group" they belong to in the UAF. Members
of the same UIC group will probably need to share procedures more
than users from another UIC group. They will also be able to
share common databases without opening the protections to world
read.
With that in mind, we define each individual user's CDD$DEFAULT
and define the logical MY_CDD. This logical allows users to save
a new DTR/DECREPORTER report to their personal CDD$DEFAULT
without keying in the full CDD pathname.
PHASE 3:
Now that we have the CDD directory created, the protections set
in the CDD, and the necessary symbols/logicals defined, it is
time to populate the directory with domains, record definitions,
and procedures.
The first thing you do is look around the CDD for any existing
DATATRIEVE procedures you want to use. These procedures are the
ones that most users are comfortable with, have seen output from
in the past, and probably cannot be reproduced through
DECREPORTER.
Here is how I extracted a sample DECREPORTER procedure and a
sample DATATRIEVE procedure to disk.
DMU> EXTRACT CDD$TOP.ISD$LIBRARY.JCOPENHAVER.USER_IMAGE_REPORT DECUS_DTR.BAK
DMU> EXTRACT CDD$TOP.ISD$LIBRARY.JCOPENHAVER.USER_SEARCH DECUS_DECREPORTER.BAK
DMU is the preferred way to extract a procedure. While
DATATRIEVE can be used here, it is a good practice to use DMU
while moving DECREPORTER and DATATRIEVE procedures.
One of the nice things about DECREPORTER is that it produces
DATATRIEVE code. While the two are not combined as a unified
product now, they should be. If you look at code generated by
DECREPORTER, you will notice several things that are not common
to DATATRIEVE but are permitted by it. The SET SEMICOLON, the
SET SEARCH, and the SET LOCK_WAIT are good examples of this.
A typical report command file originally generated for DATATRIEVE
would look like:
! DATATRIEVE PROCEDURE TO GENERATE USER IMAGE REPORTS
!
READY DTR_CIS_DATA
! READY OBJECTS SHARED READ
READY DTR_DATE READ
!
! ESTABLISH CURRENT COLLECTION
!
PRINT "ESTABLISHING CURRENT COLLECTION"
!
!
PRINT "BEGINNING COLLECTION, ETC...."
!
!
FIND REPDAT IN DTR_CIS_DATA WITH DEPT_NO GT "099"
!
SORT REPDAT BY DEPT_NO, USERNAME, OBJ_NAME
!
!
DECLARE REIMB_CHARGE PIC 9(9)V99 COMPUTED BY
CHOICE
(PROJECT BETWEEN "1000" AND "9999") THEN SALES
ELSE 0.0
END_CHOICE.
DECLARE NONREIMB_CHARGE PIC 9(9)V99 COMPUTED BY
CHOICE
(PROJECT LT "1000") THEN SALES
ELSE 0.0
END_CHOICE.
DECLARE REIMB PIC 999V99.
!
PRINT "BEGINNING USER IMAGE REPORT GENERATION"
!
REPORT REPDAT ON SAC_QRSO:USER_IMAGE.RPT
SET COLUMNS_PAGE = 132
SET REPORT_NAME =
"COMPUTER RESOURCE ACCOUNTING"/"VAX COMPUTING SYSTEM"/" "/
"USER IMAGE REPORT"
AT TOP OF PAGE
PRINT REPORT_HEADER, SKIP, DATES FROM DTR_DATE, SKIP 3, COLUMN_HEADER
AT BOTTOM OF REPORT
PRINT SKIP 1, COL 21, ("REPORT TOTAL"),
COL 50,
TOTAL REIMB_CHARGE USING $,$$$,ZZZ.99,
COL 70,
TOTAL NONREIMB_CHARGE USING $,$$$,ZZZ.99,
COL 90,
TOTAL CONN_SEC/3600 USING ZZZ,ZZZ.99,
COL 110,
TOTAL CPU_SEC/10 USING ZZZ,ZZZ,ZZ9
END_REPORT
!
PRINT "USER IMAGE REPORT GENERATION COMPLETED"
!
FINISH ALL
This sample DATATRIEVE procedure looks nothing like the
DECREPORTER procedure in many ways. However, VAX/TPU will allow
us to edit this procedure at the DCL level and disguise it as a
DECREPORTER procedure. The procedure must be changed by adding
at the beginning
SET SEMICOLON;
SET SEARCH;
SET LOCK_WAIT;
Each statement must be terminated with a semicolon. And
SET NO SEMICOLON;
SET NO SEARCH;
FINISH ALL;
must be appended to the end of the procedure. It is important to
change anything in the procedure at this time. Once you have
gotten DECREPORTER to accept a report procedure, you will not be
able to edit the procedure after it is in the CDD and still have
it accessible via DECREPORTER.
With these changes, the procedure LOOKS like the DECREPORTER
procedure now. All of the syntax for the two procedures is the
same. Using DECREPORTER`s RDMU utility, we can now add this
procedure into our new dictionary.
<FIGURE TITLE: Main RDMU Menu>
VAX DECreporter V2.1
Dictionary Management Utility
MAINTENANCE SELECTIONS
DOMAIN
RECORD
REPORT
EXIT Utility
ENTER SELECTION:
<FIGURE TITLE: Specification of Report Name in RDMU>
VAX DECreporter V2.1
Dictionary Management Utility
SIMONS_REPORTER
EXIT Utility ARNED;1 ARNED;2
PHS_PROJ_STD_2;1 PHS_PROJ_STD_3;1 PHS_STD_1;1
PROJECT_3709C;1 PROJECT_LISTING;1 TRS_BUS_DEV;1
TRS_DEPT_STD_1;1 TRS_DEPT_STD_2;1 TRS_DEPT_STD_3;1
TRS_DEPT_STD_4;1 TRS_IND_ALL_PROJ;1 TRS_IND_AREA_PROJ;2
TRS_IND_PROJ_AREA;1 TRS_PROJ_STD_1;1 TRS_PROJ_STD_2;1
Enter new REPORT name: decus_demo
<FIGURE TITLE: Specification of Report Command File Name to RDMU>
VAX DECreporter V2.1
Dictionary Management Utility
SIMONS_REPORTER
EXIT Utility ARNED;1 ARNED;2
PHS_PROJ_STD_2;1 PHS_PROJ_STD_3;1 PHS_STD_1;1
PROJECT_3709C;1 PROJECT_LISTING;1 TRS_BUS_DEV;1
TRS_DEPT_STD_1;1 TRS_DEPT_STD_2;1 TRS_DEPT_STD_3;1
TRS_DEPT_STD_4;1 TRS_IND_ALL_PROJ;1 TRS_IND_AREA_PROJ;2
TRS_IND_PROJ_AREA;1 TRS_PROJ_STD_1;1 TRS_PROJ_STD_2;1
Enter REPORT command file: decus.tmp
<FIGURE TITLE: RDMU Screen Loading the Report Selection
VAX DECreporter V2.1
Dictionary Management Utility
SIMONS_REPORTER.DECUS_DEMO
SET SEMICOLON;
SET SEARCH;
! DATATRIEVE PROCEDURE TO GENERATE USER IMAGE REPORTS
!
READY DTR_CIS_DATA;
! READY OBJECTS SHARED READ
READY DTR_DATE READ;
!
More...
Storing Dictionary Object
At this point, if you enter SIMONS_REPORTER from the DCL level,
you will see the "new" report by accessing the "Recall an
Existing Report" option. If selected, the report should run
providing that all necessary changes have been made.
<FIGURE TITLE: User Recalling an Existing Report>
VAX DECreporter V2.1
SIMONS_REPORTER
EXIT Utility ARNED;1 ARNED;2
PHS_PROJ_STD_2;1 PHS_PROJ_STD_3;1 PHS_STD_1;1
PROJECT_3709C;1 PROJECT_LISTING;1 TRS_BUS_DEV;1
TRS_DEPT_STD_1;1 TRS_DEPT_STD_2;1 TRS_DEPT_STD_3;1
TRS_DEPT_STD_4;1 TRS_IND_ALL_PROJ;1 TRS_IND_AREA_PROJ;2
TRS_IND_PROJ_AREA;1 TRS_PROJ_STD_1;1 TRS_PROJ_STD_2;1
Recall report PROJECT_LISTING;1 (Y/N)? Y
If you want to mix DATATRIEVE and DECREPORTER procedures in the
same CDD directory, they would all show up when someone used the
"Recall an Existing Report" option. However, if someone tried to
access a DATATRIEVE procedure from DECREPORTER, they would get an
error message.
<FIGURE TITLE: Error Message Generated when Trying to Access a DATATRIEVE Report using DECREPORTER
VAX DECreporter
SIMONS_REPORTER
EXIT DECreporter ARNED;1 ARNED;2
DECUS_DEMO;1 PHS_PROJ_STD_2;1 PHS_PROJ_STD_3;1
PHS_STD_1;1 PROJECT_3709C;1 PROJECT_LISTING;1
TRS_BUS_DEV;1 TRS_DEPT_STD_1;1 TRS_DEPT_STD_2;1
TRS_DEPT_STD_3;1 TRS_DEPT_STD_4;1 TRS_IND_ALL_PROJ;1
TRS_IND_AREA_PROJ;2 TRS_IND_PROJ_AREA;1 TRS_PROJ_STD_1;1
TRS_PROJ_STD_2;1
Select the report to be recalled:
DECUS_DEMO;1 has been created by Datatrieve. Please use Datatrieve to Process this Report
Unless you have complicated CDD domain definitions, it may be
easier to simply recreate them in the new directory using RDMU.
If you want to move a domain definition, the easiest method in
this case, is to use DATATRIEVE to:
1. extract the domain to a DCL file
2. edit the file if necessary
3. reload using DATATRIEVE
DTR>@FILENAME.
PHASE 4:
The best feature of this platform is it's ability to have online
help built into the record definition. DECREPORTER is quite nice
in this aspect, where DATATRIEVE is not. If you have simple
record definitions that exist in the CDD, I suggest you use RDMU
to recreate them and incorporate the helps at that point.
However, it almost never works out that way.
Here is an abbreviated record definition that allows users to see
the "helps" added in the record. All the user does is press
<PF4>.
DEFINE RECORD TIME_REPORTING_SYSTEM_REC
DESCRIPTION IS
/* This is the record for the Time Reporting System */.
TRS_DOMAIN_REC STRUCTURE.
/* week ending dd/mm/yy */
WEEK_ENDING DATATYPE IS TEXT
SIZE IS 8 CHARACTERS
PICTURE FOR DATATRIEVE IS "X(8)"
QUERY NAME FOR DATATRIEVE IS "WEND".
/* Project number */
PROJECT DATATYPE IS TEXT
SIZE IS 5 CHARACTERS
PICTURE FOR DATATRIEVE IS "X(5)".
/* Area number */
AREA DATATYPE IS TEXT
SIZE IS 3 CHARACTERS
PICTURE FOR DATATRIEVE IS "X(3)".
/* Hours project to date */
HOURS_PROJECT_TO_DATA DATATYPE IS RIGHT SEPARATE NUMERIC
SIZE IS 7 DIGITS
SCALE -1
PICTURE FOR DATATRIEVE IS "S9(6)V9(1)"
QUERY NAME FOR DATATRIEVE IS "HPYTD"
EDIT STRING FOR DATATRIEVE IS "-Z(5)9.9".
END TRS_DOMAIN_REC_STRUCTURE.
END TIME_REPORTING_SYSTEM_REC RECORD.
If you look at this record definition, you will see how "helps"
look in the actual record definition itself. When a user
accesses this record definition from DECREPORTER, all he needs to
do is press <PF4> and the help for that particular field will
appear on the screen and help him determine if he wants to select
it.
If you have an existing DECREPORTER record definition that you
want to move to the platform, follow these steps exactly:
1. Enter DMU and set your default over to the dictionary that
holds the record definition (if it is different than your
CDD$DEFAULT).
2. Type "LIST" to make sure that the record definition is out
there. Type DMU> EXTRACT/RECORD record_name out_file_name.
If the record definition is a DATATRIEVE definition, you can
edit it at this point and add any help messages you want.
3. The ONLY way to get this record back into the CDD with the
help messages intact is to follow these step EXACTLY:
a. $define cdd$default simons_reporter
b. $CDDL out_file_name
4. Re-enter DMU and type
DMU> LIST/FULL record_name
on the record and check the record for accuracy.
5. Reset your CDD$DEFAULT to its correct dictionary path.
PHASE 5:
Earlier we covered the way you can set protections using the CDD.
There are a few things that should be added here. For practical
purposes, you can consider that the CDD protections sit on top of
the DCL protections. That is, even if a file has a DCL world
read protection on it, they cannot access it via the CDD unless
you set it up that way. On the other hand, just because you
allow them to access something via the CDD doesn't necessarily
mean that DCL will permit it. This is a strong security feature
and should not be overlooked.
An example of this is a personnel database containing salary
information. A manager may need the information there, but not
want the clerks to have access to it. Even if the clerks had
other valid procedures they needed to run from the platform, the
manager could eliminate anyone from snooping around by
restricting the access to the file at DCL level.
PHASE 6:
Earlier, we covered how to move the altered DATATRIEVE
procedures, DECREPORTER reports, domains, and record definitions.
We ended up with a platform that was populated with some
procedures, but not everything that some users may need. As the
person creating this platform, you need to have a flexibility
that will allow users to create their own reports from the
platform but not clutter it up with them. One of the first
things we did was create a logical called "MY_CDD". This logical
will allow users to enter the platform, create a new report to
their specifications and save it to their personal CDD directory.
<FIGURE TITLE: Illustration of Person Saving a Report to MY_CDD.Whatever
--------------------------------------------------------Selection Criteria---------------------------------------------------------
SIMONS_REPORTER.PROJECT_HISTORY_SYSTEM;2
PROJECT DEPT AREA
ACT_CODE HOURS_PROJECT_TO_DATE
Name of this report: my_cdd.decus_demo
This report can be accessed directly from the user's CDD
directory by using either DATATRIEVE or DECREPORTER. It will
retain all pointers to the platform, its domains and record
definitions but will not clutter the platform up with a report
only one person will use.
DECREPORTER has some limits that DATATRIEVE doesn't. For
example, DATATRIEVE will allow you to create a report over 132
characters wide. DECREPORTER alone will not. However, you can
create a "SHELL" from DECREPORTER and modify it to your specific
needs.
A typical report procedure created by DECREPORTER looks like:
SET SEMICOLON;
SET SEARCH;
SET LOCK_WAIT;
SET DICTIONARY CDD$TOP.PROD$LIBRARY.QUANTUM;
READY DTR_CIS_DATA SHARED READ;
REPORT DTR_CIS_DATA
ON * ."the report output device and filespec ";
SET COLUMNS_PAGE = 132;
SET LINES_PAGE = 55;
SET REPORT NAME = "DECUS DEMO";
PRINT
COL 2, NODE ("NODE") USING XXXXXX,
COL 9, PROJ_NO ("PROJECT") USING XXXX,
COL 17, PROJ_SUF ("PROJECT"/"SUFFIX") USING X,
COL 25, DEPT_NO ("DEPARTMENT"/"NUMBER") USING XXX,
COL 36, USERNAME ("USERNAME") USING XXXXXXXXXXXXXXXX.
COL 51, OBJ_NAME ("OBJECT"/"NAME") USING XXXXXXXXXXXXXXXX,
COL 68, CONN_SEC ("CONNECT"/"TIME") USING XXXXXXXX,
COL 77, CPU_SEC ("CPU"/"TIME"/"SECONDS") USING XXXXXXXX,
COL 90, PAGE_FAULTS ("PAGE"/"FAULTS") USING XXXXXXXX,
COL 99, DIRECT_IO ("DIRECT"/"IO") USING XXXXXXXX,
COL 108, BUFF_IO ("BUFFERED"/"IO") USING XXXXXXXX,
COL 117, PRINT_PAGES ("PRINTED"/"PAGES") USING XXXXX,
COL 125, SALES ("SALES") USING XXXXXXXX ;
AT BOTTOM OF REPORT
PRINT
SKIP 2 ;
END_REPORT;
SET NO SEMICOLON;
SET NO SEARCH;
FINISH ALL;
The limit on this report is 132 columns wide, but my specific
needs require a report that goes beyond that. I know which field
I want to add and the size that the report should be.
So it can be "fixed" by changing from
SET COLUMNS_PAGE = 132;
to
SET COLUMNS_PAGE = 150;
and replacing
COL 125, SALES ("SALES") USING XXXXXXXX;
with
COL 125, SALES ("SALES") USING XXXXXXXX,
COL 136, AUTHORIZATION_CODE ("AUTHORIZATION"/"CODE") USING XXXXXXXXXX;
This procedure has been modified to allow a report limit of 150.
We defined a form that will allow our LN03 to print it. I also
added a field to the procedure. One way to do this is to create
a field at the end of the report and replace it with the "real"
field. After the procedure has been edited to your satisfaction,
enter RDMU and add the report from there. Remember to use
DECREPORTER syntax for any changes.
PHASE 7:
The final phase is to let the users know how to use the platform.
It seems like it would be easy for anyone to use, but users can
be quite hesitant to try something new. The easiest way for
everyone to learn about it is send a memo around with detailed
instructions. There are several drawbacks to this method. The
first is that many people never read their memos. They will
glance at it and throw it in the trash. Another problem with
memos is that it is hard to target the memo to the people who
will actually use the platform.
Another approach is to hold a "brown bag seminar" to show them
how the platform really works. After the demonstration, you can
pass out the instructions to those who attend. You might hold
several of them to accommodate those who may have missed the
first "brown bag".
If you have the personnel that can do it, have someone go around
to each of the users and show them individually how to use the
platform and customize their own reports. This insures that you
get the necessary feedback from the users on how to improve the
procedures and the user documentation.
----------------------------------------------------------------
Dear Wombat Wizard
----------------------------------------------------------------
Dear Wombat Wizard:
I have an indexed data file approaching 15,000 records. The key
is 6 characters long. The first character is alphanumeric; the
remaining 5 are numeric only. In other words, some of the keys
have an alphabetic prefix.
What I need to do is change the prefix to a suffix. The
objective is a 6-character key with the first 5 characters
numeric and the sixth alphanumeric.
What I need is some ideas as to how to convert the existing
records (probably several hundred of them) from the A99999 form
to the 99999A form without doing each record manually.
Signed,
Need Some Keys Changed
Dear Changed:
Assuming that the records are located in an RMS file (even if it
is in Rdb), it still shouldn't be too hard. I would use
DATATRIEVE, naturally! I am also assuming that the key is the
primary key, which means that it cannot be changed. In order to
change the primary key, you must delete the record and reenter it
with the new key value. If MOST of the records need to be
altered, I would consider just setting up a new data file and
transfer over each record, making the conversion in the key field
as necessary. If less than 30% (a fairly arbitrary number) of
the record need to be changed, then I would just do it in the
same file. Take each record, look at the key to see if it begins
with an alphanumeric, if it does, then do a store to a new record
with the appropriate change to the key field (put the alpha
portion on the end instead of the front). If storing in the same
file then delete the original record.
The record definition for the data file could be
DEFINE RECORD FOO-RECORD USING
01 FOO-REC.
03 KEY-FIELD PIC X(6).
03 REST-OF-RECORD PIC X(n).
;
Then the statements and commands to restructure those particular
records which begin with an alpha could look like:
READY FOO AS OLD SHARED WRITE
READY FOO AS NEW SHARED WRITE
FOR OLD WITH KEY-FIELD NOT BETWEEN "0" AND "999999" BEGIN
STORE NEW USING BEGIN
KEY-FIELD = FN$STR_EXTRACT(KEY-FIELD,2,5)|FN$STR_EXTRACT(KEY-FIELD,1,1)
REST-OF-RECORD = REST-OF-RECORD
END
ERASE
END
There are several other ways you could do this. One of the more
interesting is by using a group field in two domains. The old
record would look like
01 FOO-REC.
03 KEY-FIELD.
05 ALPHA-CHARACTER PIC X.
05 NUMERAL-CHARACTERS PIC X(5).
03 REST-OF-RECORD PIC X(n).
;
and the new record would look like
01 FOO-REC.
03 KEY-FIELD.
05 NUMERAL-CHARACTERS PIC X(5).
05 ALPHA-CHARACTER PIC X.
03 REST-OF-RECORD PIC X(n).
;
Note the change in order of the fields.
After you have restructured your data file, you will most
certainly want to recover the space in the deleted records with a
CONVERT/RECLAIM and re-optimize the file by the well known steps
ANALYZE/FDL, EDIT/FDL, and CONVERT/FDL which are described in
your RMS tuning guide.
One of DATATRIEVE's most useful features is its ability to easily
restructure data.
Signed,
The Wombat Wizard (LJ&SC&JG)
----------------------------------------------------------------
The 4GL Problem for New Orleans
Winston Tellis, Problem Session Coordinator
----------------------------------------------------------------
At a meeting of DTR/4GL SIG sponsored Working Group Chairs and
Counterparts at the 1989 Spring Symposia in Atlanta, Working
Groups expressed an interest in cooperating in one or more
symposia presentations. One suggested activity was that each 4GL
Working Group present a solution to the same problem. Since the
logistics for such a symposia presentation would require some
significant commitments by members of the working groups, it was
decided that such a presentation could not be made in Anaheim in
the Fall of 1989 but could be accomplished in New Orleans in the
Spring of 1990.
Each of the DTR/4GL SIG Working Groups and DATATRIEVE is invited
to select a representative to present a solution to the problem
which follows at the Spring 1990 DECUS Symposium in New Orleans.
There will be one and only one presentation from each
participating 4GL. The representative must be chosen and the
DTR/4GL SIG Steering Committee notified by the end of the Anaheim
Symposia so that the appropriate amount of time may be scheduled
for New Orleans. The order of presentations in New Orleans will
be determined by a random drawing; each presenter will have
exactly the same amount of time to present their solutions.
The problem is a "People Database" browse problem.
The record layout is as follows:
id number 7 digit numeric, primary key, no dups
last name 15 characters secondary key (dups allowed)
first name 10 characters
middle initial 1 character
title of address (Mr., Ms., Mrs., Dr., etc)
seniority (Jr., Sr., II, III etc)
address including city, state, and zip
soundex of last name secondary key (dups allowed)
You are to write and describe an application in which a user may
enter the id number, or last name, or part of last name, or the
soundex of the last name.
If the entry selects a unique record, the user gets to look at
the record to confirm that it is the desired record.
If the entry is not unique, the user gets records to browse
through in sort order of last name, first name, and middle
initial (either by paging or scrolling through a list both
forward and backwards) to select the desired record.
The browse display will include the id number, the full name with
title and suffix, and the city.
The data base is large; there are more than 10,000 records. The
application must be prepared to deal with at least 400 potential
records in the browse (approximately 20 screens of 20 records
each).
There is no restriction on the programming technique and whether
or not you have to link to another language (3GL) to execute a
particular routine. For those 4GLs not having a built-in Soundex
algorithm, you may use the MACRO routine described in Bart
Lederman's article in the March 1989 issue of the Newsletter,
Volume 4, Number 7, pages DTR-5 to DTR-12.
Comparing how each product approaches the problem should be most
interesting.
----------------------------------------------------------------
Wombat Magic, Spring 1989 - Part 2
Session Co-Chairs: Dana Schwartz, DOD and Bert Roseberry, U. S. Coast Guard, Washington, DC
Session Editor: Joe H. Gallagher, Ph. D., 4GL Solutions, Overland Park, KS
----------------------------------------------------------------
Editor's note: The following is Part 2 of a highly edited
transcription of the Wombat Magic Session at the 1989 Spring
DECUS Symposium in Atlanta, Georgia, which occurred on May 11,
1989. Part 1 appeared in the July 1989 newsletter. Material
which was presented on transparencies has been merged into the
oral presentation. An attempt has been made to convey both the
technical content of the Magic Session as well as the humor,
covert intellectual swaggering, and the spirited interchange of
the presentations. Material which appears in the text within
square brackets [] has been added by the editor in an attempt to
improve the understandability of this very exciting Magic
Session. The material presented here is not presented in the
same order as it occurred in the session.
John Putnam, Harris Trust and Savings Bank, Chicago, IL
John described a problem in which he was asked to produce
approximately 2400 pages of text and horizontal bar charts in 2
week window. The report had to effectively match the report
format and presentation quality of prototype reports produced by
outside consultants. And the report had to be done in-house and
in a timely fashion. The problem is that mixed test and bar
graphs using Tellagraph and a LN03 laser printer to 12 minutes
per page. [A quick calculations will tell that it would take
more than 19 days to print all the reports.]
So John's obvious answer to the problem was to use the DATATRIEVE
Report Writer (and his Talaris Printer).
The report had to made horizontal bar graphs which represented
the percent of favorable, neutral, and unfavorable responses to
an in-house questionnaire. For each record, two or three graphs
needed to be produced -- for group A and B and optionally group
C.
The "trick" to the problem was fully understanding and using the
escape sequences of the Talaris 1590 printer.
The Talaris native function to plot rulers has the command
format:
<ESC>[P1;P2;P3;P4:P5;P6|
where P1 is 0 for the absolute position or 1 for the relative
position, P2 is the x starting position in thousands of an inch,
P3 is the y starting position in thousands of an inch, P4 is the
horizontal length, P5 is the vertical height, and P6 is the
pattern. The default pattern for
black is 65535(10) or 1111111111111111(2)
grey is 43690(10) or 1010101010101010(2)
white is 00000(10) or 0000000000000000(2).
The Talaris escape sequence to save the current horizontal
position in save area 1 is
<ESC>[1;1 x
and the sequence to restore the current horizontal position from
save area 1 is
<ESC>[0;1 x
The complete procedure to drive the Talaris printer is:
DEFINE PROCEDURE PRINT_SURVEY_BAR_REPORT
READY CURCC_DOM SHARED
DECLARE CURCC1 PIC 999.
DECLARE CURCC2 PIC 999.
DECLARE NEMPCC1 PIC 9999.
DECLARE NEMPCC2 PIC 9999.
DECLARE NEMPCC3 PIC 9999.
DECLARE CURCCTITLEX PIC X(60).
DECLARE RPTYPEX PIC X.
FOR FIRST 1 CURCC_DOM
BEGIN
CURCC1 = CUR_CC1
CURCC2 = CUR_CC2
RPTYPEX = RPT_TYPE
NEMPCC1 = NUMCC1
NEMPCC2 = NUMCC2
NEMPCC3 = NUMCC3
CURCCTITLEX = CURCCTITLE
END
READY SURVEY_REP_ABC SHARED
DECLARE THEME_A_FAV COMPUTED BY THEME VIA THEME_AFAV_TABLE.M
DECLARE THEME_ORDER COMPUTED BY IF ORDER = 0 THEN 999 ELSE AFAV .
DECLARE TWIDE PIC 9999.
TWIDE = 4000
FIND ALL SURVEY_REP_ABC WITH THEME LT 14
SORT BY DESC THEME_A_FAV,ASC THEME,DESC THEME_ORDER
REPORT ALL CURRENT ON SURBARRPT
SET COLUMNS_PAGE=250
!
! Each record prints three bars (the third bar does not print if CFAV missing)
! of comparative survey data (AFAV,BFAV, AND CFAV are favorable responses)
! (AUN ... unfavorable), the middle segment is wishy washy responses.
! The three bars are comparing a parent group with a specific group and
! with different year
!
! Each bar has three bar segments that have total width in inches of TWIDE
! with the first segment being AFAV (value 0-100) percent of total on left
! right hand segment AUNF ( " ) " of total on right
! middle segment (100 - AFAV - AUNF) with different shading
!
! Pad each bar with enough blanks so that the t(145) folds each bar and
! left justifies in the proper column.
!
! The <ESC>[0;1 x recalls beginning (left) position of bar requiring
! a string of spaces to position text that is to appear at right of bar
!
PRINT SKIP 3,COL 1,FN$STR_EXTRACT(ITEM VIA ITEM_TABLE,1,6)(-) USING X(6),
COL 8,FORMAT FN$STR_EXTRACT(ITEM VIA ITEM_BIG_TABLE,7,239) USING
X(239)||""(-) USING T(45),
COL 60,("A " | AFAV |
" <ESC>[1;1 x[1;0;0;"| FN$NINT(TWIDE*(AFAV/100.00)) ||
";65436;|<ESC>[1;" | FN$NINT(TWIDE*(AFAV/100.00)) || ";0;" |
FN$NINT(TWIDE*((100.0 - AFAV - AUNF)/100.00)) ||
";65436;2048|<ESC>[1;" |
FN$NINT(TWIDE*((100.0 - AFAV - AUNF)/100.00))||
";0;" | FN$NINT(TWIDE*(AUNF/100.00)) ||
";65436;43690|<ESC>[0;1 x {MANY SPACES HERE} "| AUNF |
" B " | BFAV |
" <ESC>[1;1 x<ESC>[1;0;0;"| FN$NINT(TWIDE*(BFAV/100.00)) ||
";65436;|<ESC>[1;" | FN$NINT(TWIDE*(BFAV/100.00)) || ";0;" |
FN$NINT(TWIDE*((100.0 - BFAV - BUNF)/100.00)) ||
";65436;2048|<ESC>[1;" |
FN$NINT(TWIDE*((100.0 - BFAV - BUNF)/100.00))||
";0;" | FN$NINT(TWIDE*(BUNF/100.00)) ||
";65436;43690|<ESC>[0;1 x {MANY SPACES HERE} "| BUNF |
IF CFAV = " " AND CUNF = " " THEN "" ELSE " C " | CFAV |
" <ESC>[1;1 x<ESC>[1;0;0;"| FN$NINT(TWIDE*(CFAV/100.00)) ||
";65436;|<ESC>[1;" | FN$NINT(TWIDE*(CFAV/100.00)) || ";0;" |
FN$NINT(TWIDE*((100.0 - CFAV - CUNF)/100.00)) ||
";65436;2048|<ESC>[1;" |
FN$NINT(TWIDE*((100.0 - CFAV - CUNF)/100.00))||
";0;" | FN$NINT(TWIDE*(CUNF/100.00)) ||
";65436;43690|<ESC>[0;1 x"|
" "|
CUNF || "" ) USING T(164)
AT TOP OF PAGE PRINT SKIP 7,COL 42,
'SURVEY: HARRIS BANK EMPLOYEE OPINION SURVEY', SKIP 3, COL 3,
'ITEM REPORT: '|CURCCTITLEX(-) ,SKIP,COL 45,
' SUBGROUP',COL 76,'SYMBOL N', COL 45,
' --------',COL 76,'------ -',SKIP,
COL 45,CHOICE RPTYPEX = "D" THEN "GROUP "
RPTYPEX = "G" THEN "DEPARTMENT"
RPTYPEX = "S" THEN "DIVISION"
ELSE "???"
END_CHOICE | " " | FORMAT CURCC2 USING ZZ9 |
" (1988)" ,
COL 79,"A " | FORMAT NEMPCC2 USING ZZZ9 ,SKIP,
COL 45,CHOICE RPTYPEX = "D" THEN "DIVISION"
RPTYPEX = "G" THEN "GROUP "
RPTYPEX = "S" THEN "SECTION "
ELSE "???"
END_CHOICE | " " | FORMAT CURCC1 USING ZZ9 |
" (1988)",
COL 79,"B " | FORMAT NEMPCC1 USING ZZZ9 ,SKIP,
COL 45,CHOICE RPTYPEX = "D" THEN "DIVISION"
RPTYPEX = "G" THEN "GROUP "
RPTYPEX = "S" THEN "SECTION "
ELSE "???"
END_CHOICE | " " | FORMAT CURCC1 USING ZZ9 |
" (1986)",
COL 79,"C " | FORMAT NEMPCC3 USING ZZZ9 ,SKIP 4,
COL 6, THEME VIA THEME_TABLE(-) USING T(73),
COL 75,"% FAVORABLE % NEUTRAL %UNFAVORABLE",SKIP,
COL 70, "<ESC>[1;0;0;"| FN$NINT(TWIDE*.333) ||
";65436;|<ESC>[1;" | FN$NINT(TWIDE*.333) || ";0;" |
FN$NINT(TWIDE*.333) ||
";65436;2048|<ESC>[1;" |
FN$NINT(TWIDE*.333)||
";0;" | FN$NINT(TWIDE*.333) ||
";65436;43690|"
AT BOTTOM OF THEME PRINT NEW_PAGE
END_REPORT
END_PROCEDURE
The report looks like:
[Editor's note: Since the hardcopy of the newsletter is produced
on a Postscript printer, it is not possible to exactly reproduce
the Talaris output. Also the report below has been narrow to 80
columns rather than the original 132 column report.]]
SURVEY: HARRIS BANK EMPLOYEE OPINION SURVEY
ITEM REPORT: ELECTRONIC PROCESSING DIVISION
SUBGROUP SYMBOL N
------- ------ -
GROUP 401 (1988) A 448
DIVISION 450 (1988) B 98
DIVISION 450 (1986) C 59
%FAVORABLE %NEUTRAL %UNFAVORABLE
Q41: USEFULNESS ... aaaaaaaaaaaaaaaxxxxxxxxxxxxxxxaaaaaaaaaaaaaaa
41D. Immediate ... A 74 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaxxxxxxxxxaaa
B 77 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaxxxxxxxxxaa
. . .
41E. Co-workers ... A 53 aaaaaaaaaaaaaaaaaaaaaaaxxxxxxxxxxxxxaaaaaaaaa
B 49 aaaaaaaaaaaaaaaaaaaaaxxxxxxxxxxxxxxxxaaaaaaaa
. . .
With this procedure, John was able to give better output than the
outside consulting firm and run the Talaris printer at full speed
of 16 pages per minute.
Part 3 of Wombat Magic
will continue next month