Bffi
f/o
ieval Training Overview
Current as of 09/15/1998
-------
Table of Contents
1.0 SDWTS/FED Basic Retrieval Training Overview .' 1.3
1.1 Welcome U
1.2 Course Prerequisites 1.3
1.3 Course Objectives 1.3
1.4 Training Materials Overview 1.3
2.0 Safe Drinking Water Information System/Federal fSDWIS/FED) 2.2
2.1 SDWIS/FED Overview 2.2
2.2 SDWIS/FED Content 2.8
3.0 Accessing EPA's IBM Mainframe 2.1
3.1 TSONETMAIN Panel 2.1
3.2 EPA2 TSO LOGON Panel r. 5.2
3.3 READY PROMPT .' 2.3
3.4 Online Help 2.3
3.5 Exercises , 2.3
4.0 Interactive System Productivity Facility (ISPFl 3.4
4.1 Introduction to ISPF 3.4
4.2 Accessing ISPF 3.4
4.3 Data Sets 4.4
4.4 Exercises 4.6
4.5 Review 4.6
5.0 Accessing SDWIS /FED 4.7
5.1 Accessing SDWIS/FED ; 4.7
5.2 List Screens Option ;.... 4.7
5.3 Exercises ..; 4.7
5.4 Standard Reports Option 5.1
5.5 Running and Viewing the Report Data Set 5.2
5.6 Printing A Report - Interactively 5.2
5.7 Exercises 5.3
6.0 Spool Display and Search Facility CSDSF) 5.4
6.1 Overview 5.4
6.2 Viewing Job Status 6.2
6.3 View, Print, or Purge (Delete) A Report From The Hold Queue 6.2
6.4 Exercises 6.3
7.0 Platinum Report Facility (PRF) 7.1
7.1 Introduction to PRF 7.1
7.2 Copying and Running A Query and/or Form 7.5
7.3 Constructing A Single Table Query 7.9
7.5 FORMS : 7.14
1.2
-------
1.0 SDWIS/FED Basic Retrieval Training Overview
1.1 Welcome
Welcome to SDWIS/FED Basic Retrieval Training. Over the next few days, you will learn the
basic skills to: access SDWIS/FED through the EPA mainframe; create, view and print standard
reports; access the Platinum Report Facility to create, view and print a simple ad hoc query; and,
locate, view and copy someone else's query to your personal directory for your use.
You will learn about these topics, and then will have the opportunity to practice what you have
learned in hands-on exercises. The instructors will be available to assist you during these
practice sessions.
1.2 Course Prerequisites
You should have some familiarity with the Drinking Water Program in order to fully utilize the
SDWIS/FED data effectively and its tools more efficiently. You should also be familiar with a
keyboard and the use of a mouse to get the greatest benefit from the course.
1.3 Course Objectives
This course is constructed to provide you with the basic knowledge and skills necessary to
perform the following tasks:
Identify the basic structure and content of SDWIS/FED
Access the SDWIS/FED database
Understand the purpose and content of the standard reports
Produce, view and print standard reports
Construct a simple query to display specific data
View and print the results tables produced by your query
Identify available documentation and the tools provided by the EPA Mainframe,
SDWIS/FED and PRF for learning on your own.
1.4 Training Materials Overview
This training manual provides you with the following tools:
1. SDWIS/FED Information Summary - provides a listing and brief description of the
types of data available in SDWIS/FED. The Summary also identifies minimum reporting
requirements, data elements which are generated by EPA, and data elements which can
not currently be reported to SDWIS/FED via the Data Transfer Format.
1.3
-------
2. Data Management Time-Line Charts for the following:
- state quarterly reporting to the data base
- determining the eligibility of data for PWSS grant allocations
- state data cleanup and creation of state Annual Compliance Report
- EPA's periodic archiving of the data base for various purposes (e.g., release to
Envirofacts, creation of the PWSS Annual Compliance Report, etc.)
- identification and posting of Significant Non-Compliance Records to the database
- identification and posting of Exceptions Records to the database
3. SDWIS/FED System and User Documentation List
4. SDWIS/FED Technical Assistance Source Lists - provides the names and phone
numbers for user assistance regarding access, data submission, error correction, reporting
requirements, retrieval assistance and data interpretation at both national and regional
levels.
5. Primary and Line Commands for ISPF - provides some of the commands to
manipulate the user's files and the data within the file (e.g., COPY, BROWSE,
RENAME, DELETE, etc.).
6. Navigation Keys and Key Function Mapping Information - for mainframe ISPF,
SDWIS/FED and PRF.
7. Permitted Values for Standard Reports Selection Screens - screen by screen display of
selection criteria and the permitted values. The definitions are found in Appendix G -
Definition of Terms.
8. SDSF Primary Commands List - provides some of the commands used within, the
Spool Display and Search Facility (e.g., checking the status of a report, browsing, re-
routing the report to print, purging a report, etc.)
9. SDWIS/FED User's Guide - provides much of the information contained in this course.
10. Consolidated Summary of State Reporting Requirements for the Safe Drinking
Water Information System (SDWIS) - provides a consolidated summary of all the
current reporting guidance documents on the "what" that is to be reported to
SDWIS/FED.
11. SDWIS/FED's Entity Relationship Diagram - a pictorial display of the Drinking Water
Program's data base. Major business and subject areas are displayed and their
relationships are described. (Handout)
1.4
-------
12. Table of Contents from the SDWIS/FED Compendium of Reporting Requirements
Documents
13. DTF Data Capture Forms
14. Code Table Values (a - f below are Appendices in the User's Guide and g is an
appendix to this training manual)
a. Service Area
b. Owner Type
c. Treatment Objective and Process Combinations
d. Contaminant Codes (Alpha and Numeric Listings)
e. Violation Type
f. Enforcement Actions ("Appropriate Actions" to address SNCs are highlighted)
g. Lead and Copper Milestones, Violations, SNC Definitions and RTC Definitions
15. SNC/SETS Information
a. Jennifer Morris's SNC/Exceptions schedule "Secrets Revealed"
b. SNC type codes
c. SETS System Capabilities
16. Definition of Terms
17. PRF "Extras"
a. Script File from an earlier PRF training class - examples
b. Tricks and Treats on:
- block mode forms
- extract forms
- expressions
c. Several more difficult queries for use and example
2.1
-------
2.0 Safe Drinking Water Information System/Federal
(SDWIS/FED)
2.1 SDWIS/FED Overview
SDWIS/FED, formerly called the Federal Reporting Data System (FRDS), became operational in
1977 and was designed to satisfy the needs for Federal oversight of the Public Water Supply
Supervision Program (PWSS). SDWIS/FED is an inventory of public water systems regulated
under the Safe Drinking Water Act. In addition to being an oversight tool, it is used to determine
PWSS grant allocations and portray water systems' compliance with the Safe Drinking Water
Act's (SDWA) regulations. Both the data system and the PWSS program have undergone many
changes during thejast 20 years. SDWIS/FED is currently undergoing a major development
effort to meet the program's'need to manage its enforcement targeting and tracking activities, and
the collection and maintenance of analytical results of unregulated chemical monitoring
requirements.
A. Data Management Time-lines
Knowing the status of the data at any point in time may be important depending on
your intended use of the data. The following charts provide a variety of time-lines
which users of the SDWIS/FED data should be aware.
Chart 2.1 provides a flow chart for the SDWIS/FED data collection, reporting, and
error resolution. It also describes the collection of SNC/Exceptions tracking process
in Region 8. (Handout)
Chart-2.2 provides the following time-lines:
1. State Quarterly Reporting Schedule (SDWIS Update Cycle)
2. PWSS Grant Allocation Determination Schedule
3. State Annual Compliance Report
4. EPA's Periodic Data Base Snapshot Archiving Schedule
5. Significant Non-Compliance and Exceptions Tracking
B. Documentation-List of Related Documentation
The following provides a list of current user and system documentation. A
recent Joint Requirements Planning (JRP) meeting was held to review existing
documentation that will also be revised to incorporate the SDWIS/FED
structure and naming conventions where appropriate.
2.1
-------
Document Name
FRDS Data Element Dictionary
FRDS Data Entry Instructions
FRDS-DEII Data Entry Application (Software)
FRDS to SDWIS/FED Conversion Mapping
SDWIS/FED to FRDS Conversion Mapping
SDWIS/FED Conversion Training Manual
SDWIS/FED User's Guide
SDWIS/FED Trouble Shooter's Guide
SDWIS/FED On-Line Data Dictionary (Software Application)
Note: See Chapter 2 of the User's Guide for additional documentation.
2.2
-------
C. Sources of Technical Assistance-(National and Local Contacts)
The following provides a listing and brief description of the types of technical
assistance available. Names, phone numbers and .addresses are provided where
appropriate.
National
SDWIS/FED User Support, Documentation and Technical Assistance
Michelle Stoner Voice: (202) 260-2798 Fax: (202) 401-3041
SDWIS/FED User Support HotLine - hotline support provided by EPA -
contractors regafding the following: data interpretation, limited query creation
and/or debugging, documents reported system problems, documents requests
for new or modified standard reports, controls submittals of update files, etc.
Help is available from 8:00am to 6:00pm EST, Monday through Friday. Voice
mail is available for messages:
Earl Lerette and Peggie Robinson, SAIC Contractors, (703) 908*2460
EPA DataBase Administrator may be contacted regarding significant database
issues or for special testing or processing requests:
AbeSiegel Voice: (202)260-2804 Fax:(202)260-2804
National FOIA (Freedom Of Information Act) requests:
TowanaDorsey Voice: (202)260-2803 Fax:(202)260-4656
To request copies of the "Information Management Branch Update":
Tracy Ward Voice (202) 260-0986 Fax: (202) 260-4656
To request information about Envirofacts related to SDWIS/EED_data:
Christine O'Brien Voice: (202) 260-4275 Fax:(202)260-4656
Revised: (4/98)
2.3
-------
Sources of Technical Assistance - (Local/Regional)
REGIONAL SDWIS DATA MANAGEMENT CONTACTS Regional Data Management
Coordinator - provides technical assistance regarding: reporting requirements, data entry, error
resolution, submission of state quarterly reports, SDWIS/FED retrievals and general data base
information, etc.
Region 1:
Office: Office of Ecosystem Protection
Office Director: Linda Murphy
Unit Name: Strategic Planning Unit
Unit Leader: Bob Goetzl
Section Name: NA
Section Chief: * NA
SDWIS Coordinator: Kathy Lynch
Phone: 617-565-4153
Fax: 617-565-4940
Mailing Address:
US EPA Region 1
1 Congress Street
Suite 1100-CMU
Boston, MA 02114-2023
Region 2:
Division Office: Environmental Planning and Protection
Division Director: Kathaleen Callaghan
Branch Name: Water Programs
Branch Chief: Walter Andrews
Section Name: Drinking Water Section
Section Chief: Bruce Kiselica
SDWIS Coordinator: MarkRasso
Phone: 212-637-3839
Fax: 212-637-3887
Mailing Address:
US EPA Region 2
290 Broadway
New York, NY 10007
Region 3:
Division: Water Protection Division
Division Director: Alvin R. Morris
Branch Name: Safe Drinking Water Act Branch
2.4
-------
Branch Chief: Karen D. Johnson
Section Name: NA
Section Chief: NA
SDWIS Coordinator: Jackie Pine
Phone: 215-566-5782
Fax: 215-566-2302
Mailing Address:
USEPARegion3(3WP32)
841 Chestnut St.
Philadelphia, PA 19107
Region 4:
Division: . Water Management Division
Division Director: Mike McGhee
Branch Name: Ground Water/Drinking Water Branch
Branch Chief: Stallings Howell
Section Name: Drinking Water Section
Section Chief: Napoleon Kotey
SDWIS Coordinator: Robert Bums
Phone: 404-562-9762
Fax: 404-562-9439
Mailing Address:
US EPA Region 4
61 Forsyth Street
Atlanta, GA 30303
Region 5:
Division: Water Division
Division Director: Jo Lynn Traub
Branch Name: Safe Drinking Water Branch
Branch Chief: Charlene Denys
Section Name: . N/A
Section Chief: N/A
SDWIS Coordinators:!. Kris Werbach
2. Tom Poleck
Phones: 1.312-886-6527
2.312-886-2407
Fax: 312-886-6171
Mailing Address:
US EPA Region 5
77 W Jackson Boulevard
Chicago, IL 60604
Region 6:
Division: Water Quality Protection
Division Director: William B. Hathaway
2.5
-------
Branch Name:
Branch Chief: .
Section Name:
Section Chief:
Source Water Protection
Larry Wright, Acting
Drinking Water Section
James Brown
SDWIS Coordinators: 1. Andy -Waite
2. Shirley Mlachak
Phones: 1.214-665-7332
2.214-665-2267
Fax: 214-665-2191
Mailing Address:
US EPA Region 6 (6WQ-SD)
1445 Ross Avenue
Dallas, TX 75202
Region 7:
Division:
Division Director:
Branch Name:
Branch Chief:
Section Name:
Section Chief:
Water Wetlands & Pesticides
U. Gale Hutton
Resource Management
Ann Keener
NA
NA
SDWIS Coordinator: Darlene Schowengerdt
Phone: 913-551-7474
Fax: 913-551-7765
Mailing Address:
US EPA Region 7 (WWPD/MBR)
726 Minnesota Ave
Kansas City, KS 66101
Region 8:
Division:
Division Director:
Branch Name:
Branch Chief:
Section Name:
Section Chief:
Office of Enf. Compliance & Environmental Justice
Carol Rushin
Planning & Targeting
Andrew M. Gaydosh
NA
NA .
SDWIS Coordinator: Rich Gomez
Phone: 303-312-7073
Fax: 3 03-312-1234
Mailing Address:
US EPA Region 8 (8-ENF-PT)
One Denver Place
999 18th Street, Suite 500
Denver, CO 80202-2405
2.6
-------
Region 9:
Division: Water Division
Division Director: Alexis Strauss, Acting
Office Name: Drinking Water Office
Office Chief: Bill Thurston
Section Name: NA
SDWA Compliance Advisor: Marvin Young
SDWIS Coordinator: Mark Rathbun
Phone: 415-744-1840.
Fax: 415-744-1235
Mailing^Address:
US EPA Region 9
75 Hawthorne Street
' San Francisco, C A 94105
Region 10:
Office: Office of Water
Office Director: Philip G. Millam
Unit Name: Drinking Water Unit
Unit Manager: John Larry Worley
Section Name: NA
Section Chief: NA
SDWIS Coordinator: Jane Schuster
Phone: 206-553-1096
Fax: 206-553-0165
Mailing Address:
US EPA Region 10 (OW-136)
1200 6th Avenue
Seattle, WA 98101
2.7
-------
2.2 SDWIS/FED Content
SDWIS/FED is an EXCEPTIONS based database. This means data, other than basic inventory,
is reported only if it occurs or exists.
Example: 1. Generally, sample results are not reported unless they are MCL
violations or unregulated monitoring results. MCL analytical results
may be a single result or an average.
2. If a violation of the regulation is determined to exist by the state, a
violation record is to be reported.
3. If an enforcement or follow-up action is taken by the state or EPA, it
is to be reported.
HOWEVER -
4. AJack of violations does not mean there are no violations, only that
"No violations have been reported."
This is an important concept to understand when using the data in SDWIS/FED. It is important
to know the reporting quality and habits of the state who's data you are evaluating. Many data
elements are optional and lack of data may not indicate that a certain condition exists or not.
States are required to report Inventory data at least annually and all other data on a quarterly
basis. There is a one-quarter lag for updates (e.g., first quarter, FY 1998 data are submitted for
processing during the second quarter of FY 1998 and would not be available until after
3/1/1998).
A specific Data Transfer Format (DTF) is required for all data submissions. All submissions are
processed in batch mode. No on-line edits or updates to the data base are allowed. (Exception:
SNC/Exceptions Tracking System)
The public can access a snapshot of specific SDWIS/FED data through the Agency's
ENVIROFACTS internet application at the following web site:
Http://WWW.EPA.GOV/Enviro
(We will not provide Envirofacts training in this course.)
2.8
-------
The "SDWIS/FED Information Summary" (3/98 draft version) below, provides a listing and
brief description of the types of data available in SDWIS/FED. The summary also identifies the
minimum reporting requirements, data elements which are generated by EPA, and data elements
which can not currently be reported to SDWIS/FED via the Data Transfer Format (as of March
FY 1998). More detailed information can be found in system and user documentation for
SDWIS/FED as well as the individual reporting guidance documents for the various regulations.
(See this section, 2.1 .B for system and user documentation, Appendix B for a consolidated
summary of reporting requirements, and Appendix C for a reporting guidance documentation
listing).
SDWIS/FED Information Summary
The Safe Drinking Water Information Sy stem/Federal-Database (SDWIS/FED) is an EPA
national database which stores information about the nations drinking water supply. Suites and
EPA Regional Offices having been granted the primary enforcement responsibility (known as
Primacy) for the National Primary Drinking Water Regulations, are required to report drinking
water data periodically to EPA. This summary is designed to provide the following information
about the data maintained in SDWIS/FED:
a general description of the categories of data
a more detailed listing and description of the more significant attributes for all the
categories and an indication of whether the attribute is required or optional
current DTF Reporting/Registration Requirements listing of required attributes
Note: Additional reporting, user and system documentation is available in the following
documents: SDWIS/FED User's Guide, FRDS Data Entry Instructions, SDWIS/FED On-
Line Data Dictionary, Consolidated Summary of Current Reporting Requirements for the
Public Water Supply Supervision Program, SDWIS/FED Compendium of Reporting
Requirements Documents. To obtain additional information you may contact Towana
Dorsey of the Office of Ground Water and Drinking Water, Information Management
Branch, at (202) 260-2803.
2.9
-------
GENERAL CATEGORIES OF SDWIS/FED DATA Descriptions
1. Inventory data represents the most recent data submitted by the primacy agencies for each
public water system. The data describes the entity including its type and size, address
information, address information for other system contacts, service area characteristics,
geographic locational information, source(s), including type, the treatment objectives and
processes associated with a source. Historical data is also maintained for some key data
elements.
Historical Inventory Tracking - The historical tracking function stores the old and new value
and the date it changed for the following "key" inventory data elements: PWS, Name, C103,
(C105, C107, C109), Inactive Cl 13, Pop Cat. Cl 15, Treatment Class C129, Service
Connections C147.
* .
EPA maintains inventory data on all public water systems, both active and inactive. When a
water system goes inactive, specific data is reported to EPA. Primacy agencies may delete an
inactive water system from their inventory after reporting its inactivation to EPA. Sometimes
inactivations fail to be reported and result in PWSs being listed as "active current"
incorrectly, or as "active historical". "Historical inventory" is a term used to represent those
PWSs that existed on the SDWIS/FED Inventory at one time, but were not included in
subsequent inventory updates by the primacy agency when providing a total replacement of
their inventory. These frequently are systems that have been inactivated by the primacy
agency and removed from their inventory. Historical PWSs are kept hi their entirety,
including all associated violation and enforcement records.
2. Sample data exists for unregulated contaminants for which some types of water systems are
required to be sampled. This occurrence data is used for analysis and future regulation
development. The specific chemicals have and will continue to change over time. Effective
July 1991, the Lead and Copper Rule required water systems to sample and report the 90th
percentile values for lead and copper. This data, while posted as SAMPLE data, is not a true
sample result. It is important to note that the existence of the data for a particular period of
time, varies greatly and is dependant on the type and size of the water system and the desired
contaminate.
Note: SDWIS/FED is an exception data base system. It does not contain parametric or
occurrence data other than as described above. A system is considered "in
compliance" with its monitoring requirements unless a violation record is reported by
the state. This includes meeting the maximum concentration level for each regulated
analyte, meeting all treatment techniques, and meeting all monitoring requirements.
3. Milestone data describing requirements or completion events for systems exceeding the lead
or copper action level based on the 90th percentile value.
2.10
-------
4. Violation data is reported for each exceedance of the Maximum Contaminant Level (MCL),
failure to meet a required monitoring or treatment technique requirement, and failure to
properly notify the public of the violation.
5. Action data describes the Federal or State follow-up actions taken to assist or require the
water system achieve compliance. The actions may be informal in nature, such as a site visit
offering technical assistance, or formal, such as a Criminal enforcement action. States and
EPA Regions are only required to report specific formal actions and the fact that the non-
compliant systems have returned to compliance for certain contaminants.
6. On-site visit data.
7. Variance and Exemption data describe events and effective time periods for the temporary
exceptions to a'PWS's normal standards. The issuance of a variance or exemptioiTis at the
discretion of the state and historically not been employed until recently.
In summary, to effectively utilize the SDWIS/FED data, the user must have a basic knowledge of
the various reporting requirements based on the Safe Drinking Water Act (SDWA) and its
implementing regulations.
ATTRIBUTE LISTING BY CATEGORY
1. Water System (Basic information) (M = Mandatory, 0 = Optional, G = Generated)
Name Description Required
PWS ID unique identification number M
assigned by the state
Activity Flag active or inactive M
Deactivation Date if inactive M
Type of PWS Community (C), Non-Community (NC), t M
or Non-Transient, Non-Community (NTNC)
Seasonal Operation begin and end dates for NC and NTNC M
Dates
Retail population served . M
Number of Service Connections M
2.11
-------
Average annual percent of water furnished by surface
sources, ground sources, purchased surface sources, &
purchased ground sources
Primary Source
Type
Owner Type
Generated from reported source data
(i.e., federal, private, state or
local govt. agency, mixed private
& public, native American & Alaskan
remote village.)
O
0
System Name and""
Address.
Telephone Number
State and Zip are Mandatory,
Address is optional.)
2. Other names and addresses associated with the PWS
3. Service Area Characteristics
Type of Service (e.g., residential, transient;
Area mobile home park, rest area)
O
O
O
Note: More than one can be supplied if appropriate. NTNCs and Community water systems
MUST indicate which service area characteristic is the primary service area characteristic.
Wholesalers with a population < 25, must report the type code for wholesalers.
4. Source - For each source and plant.
Note: At least one source must be reported for ALL PWSs. In addition, ALL SURFACE
SOURCES for all PWSs must be reported.
a. Type of source record (i.e., source, treatment plant)
b. Type of source of water (i.e., surface, ground,
purchased surface or ground, ground under the
influence)
M
M
2.12
-------
c. Seller Id PWS identifier of the principal
Seller (e.g., if PWS 'A' purchases
from PWS 'B' and PWS 'C1 purchases
from PWS 'A1, PWS 'A1 would show
having purchased water from PWS 'B'
and PWS 'C' would show purchasing
water from PWS 'A')
d. Source name (e.g., name of river, well or plant)
e. Availability of source (e.g., permanent or emergency)
Assumed permanent if not provided.
f. Latitude/Longitude
Note: Township/section/range is optional
g. Hydrologic Unit Code representing the source
location, and, if appropriate, the
River Reach Segment ID and Reach Miles.
h. River Reach information - data that represents the
hydrologic unit code, source of for the defined
river reach and a mile designation for the location
on the river reach.
i. Treatments) applied at each plant (all treatments
are deemed to be applied at a plant)
5. Geographic Areas Served
M
Congressional
Districts)
State Admin.
Region/District
District(s) served by the PWS.
(Note: this information will be
generated if not supplied by the
primacy agent.)
Geographic representation of the
Primacy Agency's administrative
Organization. Not all agencies
organize in this fashion and/or
report this information if they
do.
0
0
M
O
O
O
O
2.13
-------
State County
or FIPS
County Code
City Served
Indian Reservation"
Served
6. On-Site Visit
Date of Visit
Reason
County served by the PWS (where
the distribution system is located).
(Note: Primacy agents are encouraged
to use the FIPS (Federal Information
Processing Standards) county codes.
However, FRDS will accept an alter-
nate State county coding scheme as
long as the primacy agent provides
the necessary information to convert
these codes to the FIPS codes.)
City served in whole or in part by PWS
Code representing the Indian reservation
or Alaskan Remote village served by
the PWS in whole or in part.
O
Main Reason for visit
(e.g., sanitary survey, other)
O
O
O
O
7. Violation Information
Note: All applicable violation data is required to be reported. A PWS is considered out of
compliance for the entire compliance period for that contaminant or reporting requirement.
General:
Contaminant
Type Code
Contaminant in violation
Code for the specific type of violation
(e.g., monitoring, MCL, public notice,
or, treatment technique). More recent
regulations provide a specific code for
each violation of the rule.
Note: Compliance period represents a period of time the state requires the water system to do
something (e.g., monitor, dig a well, meet a MCL or treatment requirement, etc.). The
Data Element Dictionary defines compliance period as follows:
2.14
-------
Compliance Period
Begin Date
Compliance Period
End Date
OR
Violation Duration
COMPLIANCE PERIOD - Compliance period can have two distinct
definitions. One definition when we refer to the Standardized Monitoring
Framework (SMF), which applies to the Phase I,- II, IIB, and V contaminant
monitoring requirements, and a completely separate definition when we
refer to FRDS.
Under the SMF, a compliance period means a three-year period of time
(calendar year based) within a nine-year compliance cycle. The first
compliance period of the first compliance cycle begins 01/01/93 and ends
12/31/95; the second compliance period of the first compliance cycle
begins 01/01/96 and ends 12/31/98; and the third compliance period of the
first compliance cycle begins 01/01/99 and ends 12/31/2001.
In SDWIS/FED, a compliance period means the period of time during
which monitoring was to have been performed, such as a quarter, a year,
etc. For example, assume a public water system is required to monitor for
contaminant X each calendar quarter. If this PWS fails to conduct the
required monitoring for contaminant X for the first calendar quarter of
1993, a regular sampling M&R violation is incurred. When this M&R
violation is reported to FRDS, the State must supply the beginning date of
the compliance period, and either the ending date of the compliance period
or the duration of the compliance period. The beginning date of the
compliance period hi this example would be 01/01/93, the ending date of
the compliance period would be 03/31/93, and the duration of the
compliance period would be three months.
First day of the compliance period (e.g.,
for a quarterly compliance period for
April-June 1985 would be 4/1/1985).
Last day of the compliance period
(e.g., 6/30/1985 from the previous example).
Number of months in the compliance period
to which the violation applies (e.g., 3 from
the previous example).
Note: Although states may report either the compliance period end date OR the duration,
SDWIS/FED does not store duration. It calculates and stores the compliance period end
date.
2.15
-------
MCL Violations Only:
MCL violated
Note: This information must be supplied only if the primacy agent has a
different MCL than in the Federal regulations.
Analytical method Optional
Analytical result
Awareness Date
Note: This information must be supplied on all contaminants other than
microbiological and turbidity. It is not required for treatment technique
violations.
Date Primacy Agent became aware of the violation (Note: This information
must be supplied on all MCL violations for Chem/RAD contaminants).
Monitoring Violations Only:
The number of samples required and number of samples taken.
Major/Minor M/R Indicator - (major = no samples taken, minor = some samples taken.)
8. State Follow-up Action Information
Note: All formal actions are required to be reported. In addition, the "return to
compliance" status code is required to be reported for most violation types, the
major exception being bacteriological violations. Currently, follow-up actions are
not required to be linked to the violation records. Without this link it is difficult to
determine why the action was taken.
Action Code Represents the specific follow-up or
enforcement action taken. Actions can be
formal, informal, or "other" in nature
(e.g., respectively: administrative penalty
order, technical assistance, returned to
compliance).
Action Date(s)
Violation(s)
Link
Date or date range to which the action applies.
This information varies depending on the action.
There are 4 methods of linking actions to
the violations: Date range, specific violations,
groups of contaminants and violation types, and
a combination with an end date into the future to
2.16
-------
address long term compliance situations
(e.g., requiring construction).
9. Variance/Exemption or Other Related Information (V&E)
Note: Most of the following information is required to be reported if a V&E is granted. The
SWTR (Surface Water Treatment Rule) requires the reason that an "unfiltered" PWS is
required to filter. This filtration reason code and associated schedule of activities is
reported with the V&E data.
Contaminant or Standard to which this V&E applies (e.g., identifies which contaminant or
standard the PWS can not meet the treatment requirements. Best Available Technology"
(BAT) is to be applied and must have failed before the V&E would be granted).
Dates
Type Code
Revised MCL Level
Alternative
Treatment
Schedule
Information
Reason Code
Other Information
Effective/Expiration Date's
Denotes which type of V&E is hi effect
If MCL related
If treatment related
Dates and events for implementation of
V&E schedule, if applicable
Reason for V&E having been granted
Status Code, Vulnerability Flag, Alternate
Monitoring Frequency, Source ID for
source associated with this V&E
2.17
-------
DATA ELEMENTS REQUIRED FOR THE PWS TO BE GRANT ELIGIBLE
Note: "Grant Eligibility" is a term used to identity data that may or may not be required to be
reported, but is required to be reported if the PWS is to be counted for Grant determination
purposes. Only "active, current" PWSs are considered for grant eligibility, therefore this
information is available for most PWSs. This information is used to determine if a water
system meets the definition of a "Public Water System" and therefore is subject to
regulation.
PWS ID PWS Name
Activity Flag PWS City
PWS Type PWS State
Retail Population Served PWS Zip Code
Service Connections -°
Season BeguTand End Dates for other than Community (year round) PWSs
CONDITIONALLY REQUIRED DATA ON AN OCCURRENCE BASIS
Public Water Systems must report the following as they occur:
Violation Information (for each violation)
Formal Enforcement/Follow-up Action and "Compliance Achieved" data for
specific violations
Variances and Exemptions
Milestones for Lead and Copper and Surface Water Treatment Rules
Sample Data including Unregulated Contaminant Monitoring and 90* Percentile
values for Lead (90th Percentile values are required for all Large PWSs and only
exceedances of the action level are required for medium and small PWSs. Copper
90th percentile values are reported only as milestone data).
REVISED REPORTING REQUIREMENTS
The following changes to the inventory reporting requirements were made following the
protocol for making data sharing decisions as carried out by the Data Sharing Committee and
were issued from the Office of Ground Water and Drinking Water in April, 1997. The
implementation of these new requirements will begin once the technical documentation is
written and distributed (scheduled for early summer of 1998):
a. Change of address data to that of the owner/operator/primary contact.
b. Re-addition of owner type code as a requirement.
c. Re-addition of service area characteristics as reporting requirements.
d. Addition of principal city and principal county served as requirements.
e. Addition of latitude/longitude data along with 7 meta data fields for all sources of
drinking water for community and non-transient, non-community water systems.
2.18
-------
f. Treatment data are still being considered for inclusion, but additional information is being
collected so that a more informed decision can be made on this requirement.
CURRENT DTF REPORTING/REGISTRATION REQUIREMENTS
The following table presents the minimum data requirements as they existed in 1997. Recent
changes have increased the "required" data elements but those changes have not been
implemented by the states or the SDWIS/FED Edit Program. Therefore, this information
reflects the requirements for the data that currently exist on the database. Also note that: the
following are FRDS data element names. This table is currently being updated to reflect the
new minimum data requirements and the SDWIS/FED structure and naming convention. The
'FRDS To SDWIS/FED Conversion Mapping Document' provides the new attribute names for
reference. Your instructor may provide this document as a handout.
2.19
-------
FRDS-IIDTF Requirements
III D. Data Element Requirements
Element
Number
C101
C105
C107
CII3
C117
C131
C137
C139
C141
C147
C159
C161
C30!
C40!
C405
C407
C415
C417
C418
C419
C421
C423
C425
C481
C483
C485
C501
C601
C603
C605
C701
C801
C803
C805
C815
Element
Name
PWS-ID
PWS-TYPE
PWS-ACTIVITY-FLAG
PWS-DEACT-YYMM
PWS-RETAIL-POP-SERVED
PWS-SYSTEM-NAME
PWS-SYSTEM-CITY
PWS-SYSTEM-STATE
PWS-SYSTEM-ZIP
PWS-SERVICE-CONNECTIONS
PWS-SEASON-BEGIN-MMDD
PWS-SEASON-END-MMDD
PWS-AD-ID
PWS-SE-ID
PWS-SE-RECORD-TYPE
PWS-SE-CODE
PWS-SE-LATITUDE
PWS-SE-LONGITUDE
PWS-SE-MERIDIAN-NAME
PWS-SE-TOWNSfflP
PWS-SE-RANGE
PWS-SE-SECTION
PWS-SE-QTR-SECTION
PWS-SE-TREATMENT-rD
PWS-SE-TREATMENT-OBJECTTVE
PWS-SE-TREATMENT-PROCESS
PWS-GA-ID
PWS-SERV-ID
PWS-SERV-CATEGORY
PWS-SERV-PRIMARY-FLAG
PWS-VISIT-ID
PWS-MILESTONE-ID
PWS-MILESTONE-DATE
PWS-MILESTONE-CODE
PWS-MILESTONE-VALUE
Registration Grant
Requirement Requirement
1
2
2
4
2
2
3
1
1
2
4
4
4
4
4
4
4
3
3
3
3
3
3
4
3
3
2
2
4
YES
YES
YES
YES
YES
YES
YES
YES
YES
COND
COND
2.20
-------
FRDS-IIDTF Requirements
III D. Data Element Requirements
Element
Number
CHOI
C1I03
CU05
C1107
C1109
Cllll
C1115
C1123
C1125
C1129
C1131
C1201
C1203
C1205
C2101
C2103
C2105
C2107
C2109
C2111
C2112
C2113
C2115
C2119
C2123
C2127
C3001
C3003
C3005
C3007
C3009
C3011
C3013
C3017
C3019
C3101
C4001
Element
Name
VIO-ID
VIO-CONTAMINANT
VIO-TYPE
VIO-COMP-PERIOD-BEGIN-DATE
yiO-COMP-PERIOD-END-DATE
VIO-COMP-PERIOD-MONTHS
VIO-AWARE-DATE
VIO-ANALYSIS-RESULT
VIO-MtL-VIOLATED
VIO-SAMPLES-TAKEN
VIO-MAJOR-VIOLATION-FLAG
ENF-ID
ENF-ACTION-DATE
ENF-FOLLOW-UP-ACTION
SAMPLE-ID
SAMPLE-BEGIN-DATE
SAMPLE-END-DATE
SAMPLE-CONTAMINANT
SAMPLE-RESULT-SIGN
SAMPLE-ANALYSIS-RESULT
SAMPLE-RESULT-UM
SAMPLE-ANALYSIS-METHOD
SAMPLE-SOURCE-TYPE
SAMPLE-SE-ID
SAMPLE-ANALYSIS-DATE
SAMPLE-PWS-NOTIF1ED-DATE
VE-ID
VE-CONTAMINANT
VE-RECORD-TYPE
VE-EFFECTIVE-DATE
VE-EXPIRATION-DATE
VE-STATUS-CODE
VE-MOD-MCL
VE-ALT-PROCESS
VE-REASON-CODE
VE-SCHED-ID
SDD-ID
Registration
Requirement
3
4
2
2
4
4
4
4
4
4
4
3
2
2
3
4
2
2
4
2
4 .
4
4
4
4
4
3
4
2
4
4
4
4
4 -
4
3
3
Grant
Requirement
*Insert the definitions page for the registration requirement codes
2.21
-------
3.0 Accessing EPA's IBM Mainframe
This section provides a detailed description of the LOGON process to the EPA Mainframe. A
copy of each LOGON panel is provided with the proper response highlighted to ensure a
successful LOGON. First time users must contact their local SDWIS/FED RSA (Regional
Security Administrator) to obtain a mainframe user ID and to be set up as an authorized user.
Additional information is provided in Chapter 2 of the SDWIS/FED User's Guide.
To access the EPA IBM mainframe from an EPA LAN environment:
b.
Find the Agency LAN Services Window or icon, double click on the IBM
mainframe Icon. This will bring you to the main menu.
If there is no icon hi the Agency LAN services window, then create it by^ double
clicking on Update ALS. Once created - double click on it to reach the main menu.
3.1 TSONETMAIN Panel
NETMAJN U.S. Environmental Protection Agency
Menu System
Please enter selection or command and then press ENTER.
Date: 05/20/98
Time: 19:36:51
Terminal: TI2AN038
Logmode: M2SDLCQ
I. INFORMATION
2. SYSTEM MENU
3. MAIL
4. APPLICATIONS
5. INTER-AGENC
6. PUBLIC
News Alerts and User Menus
System Selection Menu
EPA Electronic Mail for 3270
EPA Applications Menu
Inter-Agency Applications Menu
Public Access Applications Menu
WARNING NOTICE!
This is a United States Environmental Protection Agency
(EPA) computer system, which may be accessed and used
More:
Command =
>TSO
Optional Quick Logon - USERJD
PASSWORD =
=>MEU
=> XXXXXXX
Fl/F13=Help F5/FI7=Refi«sh F8/F20=Forward FI2/F24K:ancel
Note: First time users must also reset the initial password to log o*n to the mainframe.
Therefore, only enter the "TSO" (Time Share Option) command and then press the
ENTER key. From then on, the above quick logon method can be used.
Also, the ENTER key can vary from machine to machine, however in the PC
environment, it is usually the right CTRL key or the ENTER key on the number
pad.
3.1
-------
3.2 EPA2 TSO/E LOGON Panel - (Filled in EXAMPLE)
-EPA2 TSO/E LOGON-
PFl/PF13=>Help PF3/PFI5 => Logoff PA 1 => Attention PA2=>Reshow
-T270392
RACF LOGON PARAMETERS:
NEW PASSWORD
GROUP IDENT =>
ENTER LOGON PARAMETERS BELOW:
USERJD ==> MED
PASSWORD =>
PROCEDURE => SEPAPWSS
ACCTNMBR=> PAAA
FIMAS => FRDSP
SIZE => 65536
COMMAND ==>
ENTER AN "S' BEFORE EACH OPTION DESIRED BELOW
-NOMAIL -NONOTICE -NOSUMMARY -CANCEL
BIN NUMBER
SYSOUTDEST
>D170
HOLD
(Below is a blank screen to fill in for this training set-up or as you wish)
-EPA2 TSO/E LOGON-
-T270392
PFl/PF13=>Help PF3/PF15=>Logoff PA1 =>Attention PA2=>Reshow
ENTER LOGON PARAMETERS BELOW: RACF LOGON PARAMETERS:
USERJD =>
PASSWORD => NEW PASSWORD =>
PROCEDURE => GROUP IDENT ==>
ACCTNMBR=>
FIMAS => BIN NUMBER >
SIZE => SYSOUTDEST =>
COMMAND =>
ENTER AN "S" BEFORE EACH OPTION DESIRED BELOW
-NOMAIL -NONOTICE -NOSUMMARY -CANCEL
3.2
-------
3.3 READY PROMPT
The TSO READY prompt is equivalent to the DOS prompt from which you can enter many
basic commands such as: COPY, ALLOCATE, DELETE, PRINTOFF, etc. Interactive System
Productivity Facility (ISPF) is a file management utility application that also utilizes these basic
commands. More information will be provided on the ISPF utility later in this class as well as
the Intermediate and Advanced classes.
3.4 Online Help = F1
3.5 Exercises .
The instructor wilLprovide the User-IDs and Passwords to be used during this class. _
.
Logon to the EPA IBM Mainframe using the example above and the
assigned parameters.
At the READY Prompt, try using HELP by pressing the F1 KEY.
When finished using HELP, at the READY Prompt, type
"LOGOFF" to exit.
3.3
-------
4,0 Interactive System Productivity Facility (ISPF^
4.1 Introduction to ISPF
ISPF is a menu driven multi-function file utility application for the mainframe. Files or "data
sets" can be browsed, copied, edited, listed, printed, and managed through this utility. There are
many other capabilities included in this utility. The focus of this class will be to show the user
how to list, browse, copy, rename, and delete data sets. The online "Help" or "Tutorial" is quite
robust which will allow the user to explore and learn more about the utility at his own pace
outside this class.
4.2 Accessing ISPF
To access the ISPF Main Menu, at the READY Prompt, type "ISPF". From the main menu,
enter the number of the option you wish to access (e.g., enter "3" for Utilities, etc.). Enter the
number of the option from the second menu you wish to access and so on.
A simplified method of jumping to the specific screen you desire is also available. Typing an
"=" sign followed by the menu/screen numbers (separated by periods) at the command/options
line on any screen will jump the user directly to that screen (e.g., "=3.4"). The user may also
jump directly to a submenu or screen from the READY Prompt by typing "ISPF followed by the
menu/option numbers (also separated by periods) (e.g., 'ISPF 3.4')- This option will jump to the
screen which allows the user to identify the directory of data sets to be displayed. At the
command line type "=X" from any menu screen to exit ISPF. The following navigation keys
allow the user to move around within ISPF, PRF (Platinum Report Facility) and the SDWIS/FED
application.
A. Navigation Keys (Program Function Keys) UG-P 2-3
Many operations performed while using the SDWIS/FED Reporting
System are accomplished in one of two ways:
By typing a specific command while the cursor is positioned on the
"Command =>" line, that appears at the top of user dialogue
screens.
By pressing a Program Function key (PF key) that equates to the
specific command.
4.1
-------
ISPF provides a capability whereby a user may redefine the command issued when a given PF
key is pressed. Thus, the specific key pressed to perform a given function may vary from user to
user. Default ISPF command assignments, however, for the PF keys are as follows:
PFKev
Command
PFKev
Command
PF1/PF13
PF2/PF14
PF3/PF15
PF4/PF16
PF5/PF17
PF6/PF18
Help
Split
End
Return
RFind
RChange
PF7 /PF19
PF8 /PF20
PF9 /PF21
PF10/PF22
PF11/PF23
PF12/PF24
Up
Down
Swap
Left
Right
Cursor
Throughout the remainder qf this user's guide, references made to a specific PF key assume the
default ISPF command assignments listed above.
Please note that the PRF (Platinum Report Facility) often modifies (re-maps) the command
associated with a given PF key. In fact, PRF may associate one command with a PF key on one
user dialogue screen and associate another command with that same PF key on another user
dialogue screen. PF key command re-mapping is under the exclusive control of PRF and not
modifiable by SDWIS/FED.
B. Main Menu - the instructor will briefly review the main menu options
provided.
Menu Utilities Compilers Options Status Help
Option *
ISPF Primary Option Menu
0 Settings Terminal and user parameters
1 View Display source data or listings
2 Edit Create or change source data
3 Utilities Perform utility functions
4 Foreground Interactive language processing
5 Batch Submit job for language processing
6 Command Enter ISO or Workstation commands
7 Dialog Test Perform dialog testing
8 LM Facility Library administrator functions
9 IBM Products IBM program development products
| Licensed Materials - Property of IBM
| 5645-001. 5655-042 (C) Copyright IBM Corp. 1980, 1996.
I All rights reserved.
| US Government Users Restricted Rights -
| Use, duplication or disclosure restricted
I by GSA ADP Schedule Contract with IBM Corp.
Fl=Help F3=E.xit F10=Actions F12=Cancel
User ID..: MEU
Time....: 19:46
Terminal.: 3278
Screen...: 1
Language.: ENGLISH
Appl ID...: ISR
TSO logon: SEPAPWSS
TSO prefix:
MEUPAAA
System ID : EPA2
MVS acct.: PAAAFRDS
Release...: ISPF 4.4
I
I
4.2
-------
-------
C. Tutorial - The ISPF Tutorial is very robust and easy to use. The user may
access the Tutorial from the main menu or by typing "HELP" on the
command line. Help may also be accessed by pressing the FI key. You
then need to type in the partial or full name of the file.
TUTORIAL
OPTION =>
-TABLE OF CONTENTS.
-TUTORIAL
ISPF PROGRAM DEVELOPMENT FACILITY TUTORIAL |
The following topics are presented in sequeuence, or may be selected
- General information about ISPF
- Specify terminal and user parameters
- Display source data or output listings
- Create or change source data
Perform utility functions
- Invoke language processors in foreground
- Submit job for language processing
- Enter TSO command. CLIST, or REXX exec
Perform dialog testing
- Perform library administrator utility functions
- Use additional IBM program development products
- Software Configuration and Library Manager
- Librarian ISPF functions
Terminate ISPF using log and list defaults
The following topics will be presented only if selected by number
A APPENDICES - Dynamic allocation errors and ISPF listing formats
I [NDEX - Alphabetical index of tutorial topics
PFt=HELP 2=SPLIT 3=END 4-RETURN S=RPIND 6=RCHANGE
PF7-UP 8=DOWN *=SWAP 10=LEFT M=RJGHT 12=RETRIEVE
G GENERAL
0 ISPF PARMS
1 BROWSE
2 EDIT
3 UTILITIES
4 FOREGROUND
5 BATCH
6 COMMAND
7 DIALOG TEST
8 LM UTILITY
9 IBM PRODUCTS
10 SCLM
L LIBRARIAN
X EXIT
D. Utilities - As stated earlier, this class will focus on the list data
set/directories, browse, copy and rename options and will be detailed in
the remaining sections of this chapter.
Menu Help
Utility Selection Panel
Option
I Library
2 Data Set
3 Move/Copy
4 Dslist
5 Reset
6 Hardcopy
7 ISPF OS
8 Outlist
9 Commands
* Reserved
11 Format.
12 SuperC
13 SuperCE
14 Search-For
ISSearch-ForE
Compress or print data set Print index listing. Print,
rename, delete, browse, edit or view members
Allocate, rename, delete, catalog, uncatalog, or display
information of an entire data set
Move, copy, or promote members or data sets
Print or display (to process) list of data set names.
Print or display VTOC information
Reset statistics for members of ISPF library
Initiate hardcopy output
Install ISPF C/S workstation code from MVS to your workstation.
Display, delete, or print held job output
Create/change an application command table
This option reserved for future expansion.
Format definition for formatted data Edit/Browse
Compare data sets (Standard Dialog)
Compare data sets Extended (Extended Dialog)
Search data sets for strings of data (Standard Dialog)
Search data sets for strings of data Extended (Extended Dialog)
Fl=Help F3=Exit F10=Actions F12=*Cance!
4.3
-------
4.3 Data Sets
The term data set refers to data files, reports, programs and any number of other types of files on
the mainframe. The mainframe utility applications refer to "outputs" as data sets. The
mainframe environment has a standard naming convention which users must adhere to.
SDW1S/FED generates report outputs as data sets which conform to this convention for format
and basic content characteristics. The standard naming convention for SDWIS/FED is described
in the User's Guide, page 5-9.
A. Naming Convention -
1. Maximum of 44 characters for entire data set name (DSN)
2. Maximum of 8 characters for each qualifier
3. Each qualifier must begin with a letter (A-Z) _
4. Qualifiers are separated with.a period
5. Avoid using special characters
B. Browsing - From within the user's data set file listing (ISPF 3.4), tab to
the line of the file you wish to browse. Type "B" (for browse) and press
enter. The file will open and allow the user to scroll left and right and up
and down. The user may also "filter" select data sets by typing the file
name (including the three character userid and account number, plus any
other parts of the file name you remember) alongside the "Dsname
Level. . . " prompt. This will only display those data sets that
match the filter criteria ("AISPARS.D980328.*"). The "*" is a wildcard
character which tells the system we don't care what characters follow
those specified in the filter. A data set listing, similar to the following,
would result. Editing the file is not allowed in the browse mode.
Menu Utilities Compilers Options Status Help
DSL1ST Data Sets matching AISPARS.D980323 Row 1 of 10
Command => ; Scroll ==> Page
Command Enter"/" to select action Tracks %Used XT Device
AISPARS.D980323.TIO3158.RPTAC.EOUTPUT
AISPARS.D980323.T1O3158.RPTAC.REPORT
AISPARS.D980323.T1O3158.RPTAC.SCF
AISPARS.D980323.T103226.RPTAC.EOUTPUT
AISPARS.D980323.TIO3226.RPTAC.REPORT
15
15
1
15
15
20
6
100
. 60
6
! 3390
1 3390
1 3390
1 3390
1 3390
Fl=Help F3=Exit F5=Rfmd Fl2=Canccl
C. Renaming-
1. From the TSO READY prompt, type the following:
RENAME ('filename') (new filename)
4.4
-------
OR
2.
From within the user's data set listing (SPF 3.4), tab to the line of
the file you wish to rename, type an "R" and press enter. A screen
will appear displaying the FROM data set name and prompt you
for the TO data set name. Type the name and press enter.
D. Printing a Saved Report -
1. To print a saved report, you need to have the name of the report
available.
2. At the READY prompt, type the following:
PRINTOFF ('filename') D170 DES(N3)
* t
Filename = the full name of the file, including the single quotes on either
end
D170 = the box at the Washington Information Center (WIC) - EPA HQ
where the report will be printed
DES(N3) = printing destination is the WIC
Remember: Your printer location or box will be different, so please substitute
those values in the command.
E. Copying - copying someone else's file/report from the READY prompt
1. Write down the full file name.
2. At the READY prompt, type the following:
Copy ('filename') (newfilename)
Filename = the EXACT filename of the file to be copied, including the single
quotes on both ends.
Newfilename = whatever you want to name it using the standard naming
convention. The system knows your user ID and Account and
will copy the file to your directory.
*
F. ISPF Copy - The ISPF utility also includes a copy command. From the data set
directory you wish to copy from, select the data set you wish to copy, and access
the ISPF options menu by pressing the "V key. Select the copy command. The
data set filename you selected will be displayed in the "FROM" field. Enter the
new data set filename in the "TO" field. The data set will be copied into the
user's directory. Note: It is not necessary to include the user's ID and Account in
the new name since the system knows your user ID and Account information.
4.5
-------
4.6
-------
4.4 Exercises
1. Access ISPF, Option 3.4, to select a data set (file) to be copied. Use the "Data set
name prefix of AISPAAA", or use the data set name provided by the instructor.
2. Copy the data set to your directory.
3. Print the data set using the PRINTOFF command.
4. Copy a data set using the ISPF COPY command.
4.5 Review
_i
1. Who can help you with information about Envirofacts?
2. Which SDWIS/FED document describes the basic reporting requirements by rule?
3. What do you type at the TSO READY Prompt to display a listing of your data
sets?
4. How do you exit from TSO?
5. How do you exit from ISPF?
6. How do you access HELP in ISPF?
7. Describe the two ways to perform functions or operations in TSO.
*
8. What is the command to print a saved report?
9. What is the command to view a data set?
Note: The remaining chapters will use the SDWIS/FED User's Guide as the primary training
material.
4.7
-------
5.0 Accessing SDWIS /FED
5.1 Accessing SDWIS/FED
A. Main Menu Overview - To access the SDWIS/FED Main Menu, follow the normal
LOGON procedures and type "SDWIS" at the ISO READY prompt.
B. Navigation Keys (Function Keys)
C. On-LineHelp
5.2 List Screens Option
The List Screens Options are described in Chapter 6 of the User's Guide. Turn to page one of
Chapter 6. The instructor will briefly describe these options using the following demonstration..
i
Demonstration: Select Large PWS for a small state and display each of the list screen
options, reviewing the following:
the inventory, source, violation, enforcement and variance/exemptions display
screens
the common data to each screen
the indicator for more information
the broken exit
the limitation of being able to select by PWS-ID
other (orphan limitation on violation screen, etc.)
53 Exercises
A. Select active water systems in a small state, sort on PWS TYPE.
B. Display Water System Facility (WSF) information for a PWS and then display its
violations.
5.4 Standard Reports Option (UG-P 5-1)
There are two kinds of standard reports: Detailed and Summary. When appropriate, the.
selection criteria screens have been standardized. Some reports have additional selection criteria
and sort selection screens. Summary reports have totaling options. Chapter 5 of the User's
Guide provides specific examples of the various options and report layouts for each report. The
instructor will briefly describe the purpose and content of each of the standard reports. Selection
criteria and available options will be discussed using the following demonstrations:
5.1
-------
Demonstration: Use the 07 Report - to review the following:
Standard Selection Criteria
(Permitted Values, consistency of selection criteria screens
New File Name Option
(Standard naming convention vs user supplied name) .
Report File Disposition
Sort Options
Review of Reports (Examples)
Note: EPA has implemented a new policy regarding enforcement sensitive data which
restricts certain detailed information from appearing in associated reports and
query results tables. Only authorized users may access this information. Your
instructor may provide more information upon request.
_.,
5.5 Running and Viewing the Report Data Set
The following hands on session will allow the user to walk-through the following example with
the instructor to further demontrate specifying the criteria to run a standard report.
Demonstration and walk-through with class: Using 07 Report for the example:
Find the number of community PWSs in Delaware, having TCR (Total Coliform
Rule) violations hi FY 95 and FY 96.
Then produce a 24 report to show the details (Browse, etc).
5.6 Printing A Report - Interactively
The following screen displays the disposition options for a report file.
Report: nn
Vl.O
Command =
- Specify Report File Disposition
Please specify the disposition of your report file...
Uuuaaa.Dyymmdd.Thhmmss. RPTxx.Report
Disposition => XX
PK - Print and keep K - Keep (without printing)
PD - Print and delete D - Delete (without printing)
If your report file is to be printed, you MUST provide the fallowing information:
Bin/Box ==> bbb (e.g., Dl 70)
Destination => dddddd (e.g., WIC, or Hold, or N3)
(e.g., 1)
Copies => nnn
Press ENTER to proceed
5.2
-------
5.7 Exercises
1. Select the report which will show how many systems and how many violations
occurred for community, surface water systems, for chemicals in a small state.
Review the report on-line, then print it interactively.
2. Run Annual Compliance (AC) reports to do a comparison between the 1996 Frozen
database and the production database for a small state. Run them in batch, then view
the reports, and print them.
5.3
-------
6.0 Spool Display and Search Facility (SDSF)
6.1 Overview
Accessed through ISPF, this utility allows a user to:
- view the status of a submitted report
- change the print destination of a report
- delete an unneeded report
To access SDSF - Type "ISPF E.S" at the READY prompt. The following primary option menu
screen will be displayed:
Display Filter View Print Options Help _
V1R6MO-
SDSF PRIMARY OPTION MENU -
COMMAND INPUT =
SCROLL PAGE
Type an option or command and press Enter.
LOG - Display the system tog
DA - Display active users in the sysplex
I - Display jobs in the JES2 input queue
0 - Display jobs in the JES2 output queue
H - Display jobs in the JES2 held output queue
ST - Display status of jobs in the JES2 queues
PR - Display JES2 printers on this system
INIT - Display JES2 initiators on this system
LINE - Display JES2 Lines on this system
NODE - Display JES2 nodes on this system
Licensed Materials - Property of IBM
5665-488 (C) copyright IBM Corp. 1981, 1995. ALL rights reserved. US Government Users Restricted Rights - Use,
duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
We will focus on options."!" (INPUT) and "O" (OUTPUT) only. "I" is to look at the reports
submitted in batch; "0" is to look at and manage reports that have been completed.
6.1
-------
Viewing Job Status - Option "I"
Type the letter "I" on the command line to display all the reports you've submitted, but that have
not completed processing yet. The following screen will be displayed:
Display Filter View Print Options Help
SDSF INPUT QUEUE DISPLAY ALL CLASSES LINE 1-1 (1)
COMMAND INPUT ==> SCROLL => PAGE
NP JOBNAME Job-id P C Pos Prt-Dest ASYS Status Causer Programcr-Name
AIS07RPT JOB23943 2H R255 EPA2 %%SDWIS 07 REPO
At this point, the only feature you can implement is to modify the printer destination in the
column identified at 'Prt-Dest'. If you have opted to HOLD the report, (that is, print it to the
HOLD queue) you can change it to print to the remote printer. (Note: A remote printer is one
that is an official printer recognized by the mainframe computer. At HQ in Waterside~Mall, the
destination printer is N3 or WIC). This is accomplished by using the tab key to move the cursor
over to the column 'Prt-Dest1, and typing over the existing destination with the desired
destination.
6.3 View, Print, or Purge (Delete) A Report From The Hold Queue -
Option "O"
This option allows you to view a report sent to the HOLD queue, print it out by changing the
destination, or delete it. Type "O" at the command line. The completed reports you specified to
be routed to the HOLD queue will be displayed and will look similar to the following screen:
Display Filter View Print Options Help
SDSF OUTPUT ALL CLASSES ALL FORMS
LINES 2,938 LINE 1-5 (5)
COMMAND INPUT =>
NP JOBNAME jobid
AISACRPT
AISACRPT
AISACRPT
A1S07RPT
AIS19RPT
Dest
SCROLL ==> PAGE
C Form FCB UCS Writer Tot-Reds Status Devi
JOB 11951 R255
JOB 11956 R255
JOB12107R255
JOB23943 R255
JOB 12909 R255
ASTD
ASTD
ASTD
ASTD
ASTD
517
517
518
528
858
To view a report, type the letter "S" in the NP column alongside the report you wish to view. It
will then be displayed. The first group of pages will likely contain information useless to you,
such as the following:
6.2
-------
Display Filter View Print Options Help
SDSF OUTPUT DISPLAY ATS07RPT JOB23943 DSID . 2 LINED
COLUMNS 02-81
COMMAND INPUT SCROLL ==> PAGE
. TOP OF DATA
JES2JOBLOG-SYSTEMEPA2-NODE
18:12:44 JOB23943 THURSDAY, 26 MAR 1998
18.12.44 JOB23943 ICH70001IAIS
LAST ACCESS AT 18:06:35 ON THURSDAY, MARCH
t8.12.44 JOB23943 JHASP373 AIS07RPT STARTED - INIT 20 - CLASS H - SYS EPA2
18.13.53JOB23943 NCC801I AIS07RPTSORT 0 38 :02'
18.13.56 JOB23943 NCC8011 A1S07RPTCOPY 0 4 :00
JES2 JOB STATISTICS
. 26 MAR 1998 JOB EXECUTION DATE
187 CARDS READ
528 SYSOUT PRINT RECORDS
OSYSOUT PUNCH RECORDS _.
.
Scroll past this information to the actual report. This is accomplished with a number of strokes
to the scroll down key (F8 key). Then scroll through the report using the left and right, up and
down scroll keys to view it. When done, press the F3 key to exit back to the previous menu.
Printing or Changing the Print Destination - If you have opted to HOLD the report (that is,
print it to the HOLD queue), you can change it to print to the remote printer. (Note: A remote
printer is one that is an official printer recognized by the mainframe computer. At HQ in
Waterside Mall, the destination printer is N3 or WIC).
Purge the Report - Enter a "P" alongside the report file name to be deleted or Purged.
6.4 Exercises
The instructor has placed a report hi the HOLD queue for each user and will provide the data set
name for the following exercise:
From the READY Prompt, access the SDSF facility and view the specified data set.
Next, change the printer destination to print at your current location.
6.3
-------
7.0 Platinum Report Facility (PRF)
7.1 Introduction to PRF
Platinum Report Facility (PRF) is used in SDWIS/FED as the Ad Hoc Query and report writer
tool. It is very powerful and therefore will take time, effort and patience on the user's part to
learn and efficiently utilize it's capabilities. The relational database environment utilizes the
Structured Query Language (SQL) to create the format and general syntax of the query. PRF
generates the SQL within a set of display options which are selected by the user.
A. The primary options and functions of the SQL SELECT statement follows:
1. Identifies the data to be displayed and the order in which it is to be displayed..
2. Provides sort instructions for the results table.
3. Provides math function instructions on specific or groups of data (count, average, sum,
etc.).
4. Identifies the data selection criteria.
5. Identifies which tables are joined.
6. Provides an interface with a variety of user-defined report formats.
B. The following "Stages for Building a Query" provides a listing of steps one might
consider before building a query:
Stages for Building a Query
1. Fully understand the question you're asking of the database.
2. Identify the tables in the database that contain the attributes you're interested in.
3. You WILL go through several iterations of 1 and 2 above - that's OK, particularly
when you first start writing queries.
4. Identify the specific table(s), attribute(s), and permitted values that you want to use,
using the Table Structure Chart Boxes, the Data Dictionary and Code Value Tables.
5. Invoke PRF. See if you've already built a similar query - ask others if they may have
already built one. This may save you TONS of work. Examine the query and form
libraries of others that have significant PRF experience.
6. If you must build it yourself, give yourself plenty of time, allow yourself the luxury of
doing it completely wrong - that is the only way to fully understand how it works.
Also, use the Fl key for help.
7. After you feel you've succeeded and get results, test it to ensure it is providing correct
results. Just because you're getting results doesn't necessarily mean that they are the
correct results! Build an independent, simple query that can test and confirm numbers
or other data in your query. If you have an opportunity, especially when you're
inexperienced, have a colleague confirm it too.
7.1
-------
8. Make use of user support. Just remember that they can assist with solving problems,
and can answer questions. THEY WILL NOT TEACH YOU HOW TO USE PRF.
C. Accessing PRF - PRF is accessed by selecting AD HOC Reports from the SDWIS Main
Menu.
D. PRF Main Menu and Help Function - The instructor will discuss how to access PRF,
the Main Menu's primary options and functions, and the Help function in the following
demonstration.
Demonstration:
1. Logon to mainframe and enter SDWIS at the READY Prompt following the
procedures described earlier.
^s
2. To access PRF, type 'A' for AD HOC Reports from the SDWIS main menu.
3. The user may perform any of the commands as listed options (add, batch, copy, delete,
etc.) on QUERIES, FORMS OR PROCEDURES (known as objects) from the Main
Menu as displayed below. To EXIT PRF, the user may enter an "X" in the OPT
column.
PRFM.OI R04.00 PLATINUM REPORT FACILITY 06/0113:46
COMMAND==>
PRF0053W You must specify a valid option (or "X" to exit)
OPT NAME
QUERY
FORM
PROCEDURE
New object name for C/N options...
DB2 subsystem: #D2D
Enter one of the folio wing options:
A-Add B-Batch C-Copy D-Delete
E-Edit F-Freefoim L-Library N-reName
R-Run S-Select X-eXil
TAPE VERSION: P512AA
developed by the Janina Ratkowska Group
- Copyright 1988-1996 PLATINUM technology, inc. All Rights Reserved -
4. To Add a new QUERY, enter 'A' under the OPT column and enter a name for it under
the NAME column and press ENTER.
5. Naming queries is just as important on the mainframe as naming files in the PC
environment. The user will want to uniquely identify the query with a name which
has meaning and follows PRF's naming convention. The.primary difference between
PRF and ISPF is that PRF uses underscores instead of periods between levels. The
user also has the ability to provide a description of the query, form or procedure for
7.2
-------
additional reference.
6. A Library of SDWIS/FED Data Tables will be displayed to'select from. The Table
naming convention is as follows:
a. The first character identifies the table as T = Table and V = View (a view is an
artificial table created by the database administrator which may consist of specific
attributes from multiple tables joined to act as a real table via the "view").
b. The 2nd and 3rd character denote the business system being developed when the
table was created. IN = Inventory, FR = FRDS Conversion, etc.
c. The remaining characters are a cryptic version of the actual name of the data
contained in the table (e.g., TINWS YS = Water System Table). This class will
focus on the water system table. The Structure Chart Boxes provided in the
Appendices displays the table names and attributes contained within each table.
Note: "PWSSDOWN" (in Capital letters) must be displayed in the ID field on the
table selection screen in order to obtain the SDWIS/FED data tables. Tab to
the ID field and enter PWSSDOWN if it is different on your screen.
SLTB.03 R04.00
COMMAND>
SELECT TABLES-
PWSSDOWN LOC=> LOCAL
TYPE
1 (V) PWSSDOWN.ALIAS VI
2 (V) PWSSDOWN.DESCRIPTION V01
3(V)PWSSDOWN.PETER SNC1 ~
4 (T) PWSSDOWN.PLAN TABLE
5 (T) PWSSDOWN.PLAN TABLE SAVE
6 (T) PWSSDOWN.RPROF
7 (T) PWSSDOWN.SDWIS REGION STATE
8 (V) PWSSDOWN.SNC PETER1
9 (V) PWSSDOWN.SNC V2
10 (V) PWSSDOWN.SNC V3
11 (T) PWSSDOWN.STATE_CODE
12 (V) PWSSDOWN.TEMP_VIEW
13 (V) PWSSDOWN.TEXT_V01
14 (T) PWSSDOWN.TFRCNTAS
15 (T) PWSSDOWN.TFRCNTAS FY1997Q3
16 (T) PWSSDOWN.TFRCNTAS_FYI997Q4
17 (T)PWSSDOWN.TFRCNTAS.FYI998QI
18 (T) PWSSDOWN.TFRCNTAS_FY1998Q2
07/16 11:14
MEU.TEST
SEL=>N
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL:
LABEL: CONTAMINANT ASGMT
LABEL:
LABEL:
LABEL:
LABEL:
7. Tab to the TINWS YS table and enter an S in the SEL column and press enter. Note
that a literal description of the table is displayed to the right of the table name. The
user may scroll through the table listing by using the function keys as described
earlier. The "Build Query Option" screen will then display, the attribute names of the
TINWS YS table. There are many other options on this screen that will be covered in
a later section.
7.3
-------
E. Press F3 to exit this screen and repeat until you are back to the PKF Main Menu.
F. Exercise: Access PRF, Add a new QUERY and name it PRFEXJ. Then select
the TINWSYS table and explore the HELP function. Then return to
the PRF Main Menu.
7.4
-------
7.2 Copying and Running A Query and/or Form
A. USING (RUNNING) OTHER PEOPLE'S QUERIES - Anyone may access, read,
run and copy anyone else's query, unless the creator specified otherwise. The default
designation is shared. To access someone else's query, form or procedure (known as
objects), the user must know the creator's three character mainframe user ID (e.g.,
AIS).
Note: The first time a new user accesses SDWIS/FED, he has no queries of his own.
1. Access the PLATINUM REPORT FACILITY main menu by selecting the Ad
Hoc Option from the SDWIS main menu.
*
2. Type the letter "L" (to view the Library) under the OPT column alongside
Query.
3. The next screen is the 'QUERY LIBRARY1 screen, listing the queries (same for
forms or procedures). It allows the user to list objects that were created, among
other characteristics, by someone else (put their 3 character ID in the CREATOR
field, this example has AIS - Abe Siegei). The NAME field provides a search
option on the query name. The "*" is a wildcard which yields ALL queries. To
search on a portion of the name, use the % and the common characters (e.g.,
%train%).
QYLB.02 R04.00
COMMAND=>
PRFOI43I Query not updated
NAME=> * CREATOR=
- QUERY LIBRARY -
USER ID: GJL
AIS TYPE=>* SEL=
06/01 13:33
options-> S = select
QUERY NAME
A1S.TRAIN4 EX3
AIS.TRAIN4_EX4
AIS.TRAIN4_EX5
AIS.TRAIN5_EX1
AIS.TRAIN5_EXI 1
AIS.TRAIN5_EX2
AIS.TRAINS EX3
AIS.TRAINS_EX4
AIS.TRAINS OUTERJ
AIS.TRAIN6~EX1
AIS.TRAIN6~EX2
AIS.TRAIN6IEX3
= mn D = delete U = UN-delete B = batch
DESCR
STUDENT PROBLEM NO JOIN CONDITIONS .
STUDENT PROBLEM NO JOIN CONDITIONS .
ROUTINE REPORT
EXAMPLE IN REGION 5 FOR NON-NERRDS...
TCR VIOLATIONS IS Wl
EXAMPLE 3 IN REGION 5
VIOLATION TO ENF ASSIGNMENT
TRAINING EXAMPLE »I
TRAINING EXAMPLE 2 - SIMPLE QUERY-
EXAMPLE OF GROUPING COUNTS
4.
Find the desired Query. (Hopefully, the creator has provided some meaningful
information in the DESCRIPTION, or has provided you with the query name.
You also may view the full description (up to 40 characters wide) by pressing the
Fllkey).
7.5
-------
5. Select the query by typing the letter'S' to the left of the name. Use the tab key to
move down to the desired query. The selected SDWIS/FED tables, attributes and
their specific selection criteria is displayed.
6. To RUN the query, enter RUN at the command line and press enter. The
following screen will appear:
RUNQ.04R04.00 RUN QUERY 06/0217:43
COMMAND=> AIS.PRFEXJ
ROW LIMIT: NONE
USING FORM: DEFAULT FORM
Forms that can be used by this Query:
Note: A query can also be run by entering a "R" in the SEL column "By the
desired QUERY name on the Query Library Screen. See above display
7. The results table for the selected query will be displayed. Use the function keys
to scroll through the report. Note the report is formatted for 132 character
display (standard computer paper width).
8. Exit the report by pressing the F3 key as before.
B. EXERCISE: From the PRF Main Menu, select the Query Library and change the
CREATOR to AIS or a CREATOR designated by the instructor.
Select a query, run it, browse the results table and exit. If time
permits, try running a query from the Query Library, etc.
C. TO COPY SOMEONE ELSE'S QUERY OR FORM to your directory, enter a "C"
(COPY) alongside the object to be copied and enter the name of the object to be
copied. Then TAB to the center of the screen to "New object name for C/N options
and enter the new file name and press enter.
7.6
-------
PRFM.OL R04.00 PLATINUM REPORT FACILITY 06/0218:11
COMMANT>=>
OPT NAME
C QUERY AIS.TRAIN6_EX3
FORM
PROCEDURE
New object name for C/N options... GJl~APPLE6
DB2 subsystem: #02D
Enter one of the following options:
A-Add B-Batch C-Copy D-Delete
E Edit F - Freeform L Library N - reName
R-Run S-Select X-eXit
TAPE VERSION: PS 12AA
developed by the Janina Ratkowska Group
.
-Copyright 1988-1996 PLATINUM technology, inc. All Rights Reserved-
The next screen advises the user that the query was successfully copied and places
the new query name at the QUERY option line for selection as follows:.,
PRFM.01 R04.00 PLATINUM REPORT FACILITY 06/0218:09
COMMAND=>
PRFOS921 QUERY AIS.TRAIN6_EX3 copied to GJLAPPLE6
OPT NAME
QUERY GJLAPPLE6
FORM
PROCEDURE
New object name for C/N options...
DB2 subsystem: #D2D
Enter one of the following options:
A - Add B - Batch C - Copy D - Delete
E-Edit F-Freeform L-Library N-reName
R-Run S-Select X-eXit
TAPE VERSION: P512AA
developed by the Janina Ratkowska Group
-Copyright 1988-1996 PLATINUM technology, inc. All Rights Reserved -
*
Note: Due to a current 'bug' in PRF, the user must exit by pressing the F3 key, and
then select the new version of the query before any modifications may be
made to it.
D. FORMS - Anyone may access, read, run and copy anyone else's form, unless the
creator specified otherwise. The default designation for forms is also shared. The
instructions are the same as for queries above with the exception that you select the
FORMs library by typing the "L" in front of "FORM" on the PRF main menu).
7.7
-------
E. EXERCISE: Copy the following query, select your library, select and run the query
using the RUN command from the Query Library screen, quickly view
the results and exit: AIS.TRAIN6 EX1
7.8
-------
7.3 Constructing A Single Table Query
Select the Add a new Query option and name it PRFEX_2. Select the TINWSYS table from the
SDWIS/FED data table listing. Walk through the following demonstration with the Instructor as
(s)he describes the following options from the Build Query Screen:
Demonstration:
A. SELECT COLUMN - allows the selection of data for inclusion in the results table.
Selection is made by entering an "S" hi front of the desired attribute. The user can
identify the order the attribute (column) is to be displayed by placing a number in the
SEL column instead of an "S". (Note: As the user attempts more complex queries,
he may want to use blocks of numbers when ordering the columns to allow for
insertion of additional columns without having to renumber everything (e.g., 5,10,
15,20, etc., instead of 1,2,3,4))
BLDQ.02R04.00 BUILD QUERY 07/1612:02
COMMANT>=> MEU.TEST
Distinct: N Shrink: N Location: LOCAL
SEL PWSSDOWN.TINWSYS FY1998Q ORD GRP FUNC WHERE using: AND
1 1. NUMBER C
2. TINWSYS IS NUMBER.... N
2 3. TINWSYS Sf CODE C ='AR*
4. ALTERNATE ST MUM C
3 5. NAME 1..."C
6. LOCAL_NAME C
5 7. ACTIVITY_STATUS_CD.... C
8. ACTIVITY_DATE D
10 9. OWNER TYPE CODE C
10. D PRIN CITY SVD NM.... C
II. D_PRIN CNTY_SVD NM.... C
12. SURF WTR RATIO N
SELECT NUMBER. TINWSYS_ST_CODE, NAME, ACTIVITY_STATUS_CDT
OWNER TYPE CODE
FROM PWSSDOWN.TINWSYS_FYi998Q2 A
B. WHERE CLAUSE PROCESSING - provides the user the ability to specify
selection criteria based on individual attribute values or conditions. This is
accomplished by the use of operators and functions which include, but are not limited
to, the following:
«
1. BOOLEAN OPERATORS - symbols used to denote equal to, less than, greater
than, etc., ( <, >, = <=, >=,).
TINWSYS_IS_STATE_CODE = 'AR'
In this example, only systems having the state code of AR would be selected.
7.9
-------
2. IN LIST - a sub-set of permitted values (e.g., some, but not all of the states)
TINWSYS_IS_STATE_CODE IN ('AR(, 'LA1, TX')
In this example, only systems having the state code of AR, LA, and TX would be
selected.
3. NUMERIC AND ALPHANUMERIC RANGES - searches restricted to values
that fall BETWEEN a low end number or value AND a high end number or value.
Remember to enclose alphanumeric values in quotes.
TINWSYS_IS_STATE_CODE BETWEEN 'LA1 and TN'
In this example, neither AR nor WY would be selected. -
* *
4. REPLACEABLE PARAMETERS - The need to periodically check the current
status of a particular set of water systems, or certain other conditions is met by
using Replaceable Parameters. This option allows the user to create a "generic"
condition in the WHERE CLAUSE that can be changed quickly and easily
without having to recreate or permanently modify the query.
TINWSYS_ST_CODE IN ('&Statel', '&State2', '&State3')
In this example, the user would be prompted to enter up to three state codes. The
same thing can be done with dates using BETWEEN as well as other operators
(e.g., Replaceable Parameters and Ranges)
Distinct; N Shrink: Y Location: LOCAL
SEL PWSSDOWN.TINWSYS ORD GRP FUNC WHERE using: AND
S 1. NUMBER C
3. TINWSYS_ST CODE C ='&STATE'
S 5. NAME ~ C
S 24. D POPULATION COUNT.... N
SELECT NUMBER, NAME, D POPULATION COUNT
FROM PWSSDOWN.TINWSYS A WHERE TINWSYS_ST_CODE ESTATE1
When replaceable parameters are used in a query, PRF prompts the user to allow
those parameters to be updated whenever the query is run * The user should tab to
the prompt and enter "YES." (Note: The entire word must be entered.) All
replaceable parameters are displayed on the subsequent screen with the last value
used for that parameter. Type over the value(s) and press the F3 Key to continue
the RUN command.
7.10
-------
RUNQ.04 R04.00 RUN QUERY 06/02 17:43
COMMAN1>=> AIS.TRAIN6_EX4
DO YOU WISH TO VIEW OR UPDATE THE REPLACEABLE PARMS? yes
ROW LIMIT: NONE
USING FORM: DEFAULT FORM
Forms that can be used by this Query:
5. SUBSTRING SEARCH - allows the user to search on a character string match by
using the operator LIKE and the following wild cards or combinations of wild
cards: "%" placed before the string search or after the string search is a wild card
for any number of character(s); a "_" placed before - between - or after the string
search is a wild card for any single character.
* f
NAME LIKE '%ville%' would select any system having the characters
'ville' appear anywhere in its name.
NAME LIKE 'akron%' would select only those systems having 'akron' in
the first 5 positions of its name.
AIS.TRAIN2_EX2
Distinct N Shrink: Y Location: LOCAL
SEL PWSSDOWN.TINWSYS ORDGRPFUNC WHERE using-AND
2 1. NUMBER C
3. TINWSYS_ST_CODE C IN f &STATE IV&STATE2' .'&STATE31)
1 5. NAME C LIKE -%NEW YORK'/.1
S 24. D_POPULAT10N_COUNT N BETWEEN &LOWPOP AND & HIGHPOP
SELECT NAME, NUMBER, D_POPULATION_COUNT
FROM PWSSDOWN.TINWSYS A WHERE TINWSYS ST CODE IN('&STATEI
','& STATE2 ','& STATES V&STATE4'} AND ( D POPULATION COUNT BETWEEN & LOWPOP AND &
HIGHPOP) AND NAME LIKE TcNEW YORK%' ORDER BY NUMBER
C. TO SAVE THE QUERY - enter "SAVE" at the command line. (Note: The F3 Key
also saves the query.) Because the RUN command can fail and the user be kicked out
of PRF, the user should get into the habit of saving the query before issuing the RUN
command.
D. TO VIEW ONLY SELECTED CRITERIA - tab to the SHRINK field and enter
"Y" (yes). This will exclude all attributes from the Build Query screen except those
selected for display in the results table or those having selection criteria specified.
Should the user need to add additional attributes or specify other conditions, enter a
"N" and the full display will reappear.
E. OTHER FUNCTIONS - the instructor will describe the function of the ORD(ER),
GRP(GROUP), and FUNC(TION) Options on the Build Query screen by using the
7.11
-------
HELP function (Fl Key)
F. ROW LIMIT - The user may restrict the display of a limited number of rows from
the results table. This option provides the user an opportunity to "test" the query
results on a small set of data and saves processing time. If the results are appropriate,
the query can be re-run without the limit.
RUNQ.04 R04.00 RUN QUERY 06/02 17:47
COMMAND=> AIS.TRAIN6_EXl
ROW LIMIT: 50
USING EORM: DEFAULT FORM
<
Forms that can be used by this Query: NO FORMS BASED ON THIS QUERY
Note: There were no existing FORMS associated with this query. If there
had been, this prompt would provide the name of the FORM or
FORMS.
The following results table advises the user that there was a row limit requested.
OENR.OS R04.00 GENERATE REPORT 06/0217:48
COMMAND=> QUERY -AIS.TRAIN6_EXr
PRF00161 Row limit reached. 50 rows used LINE 1
NUMBER NAME D PWS FED TYPE CD
OK1010201 VALLIANT C
OK1010202 WRIGHT CITY C
OK1010203 IDABEL PUBLIC WORKS AUTHORITY C
OK1010207 MCCURTAIN COUNTY RWD »8 C
OK1010214 BROKEN BOW PWA C
OK1010301 CLAYTON PUBLIC WORKS AUTHORITY C
OK1010302 ANTLERS PUBLIC WORKS AUTHORITY C
OK1010304 TALIHINA C
Note: The on-line HELP function (F 1) is very good in PRF - use it while practicing.
EXERCISES FOR PRF:
Student problem #1
a. display data in following sequence
- system name
- system address
- system type
- population served
- primary source of water
b. save the query
c. run report
7.12
-------
Student problem # 2 - Using ORD - sort results from previous query
a. by state
b. decreasing population
Student Problem # 3 -
1. Create a new query under the name of PRFEX_3 to meet the following conditions:
a. Limit selection to two small states
b. Population between 1,000 and 100,000
c. Community and non-community transient Systems only
d. Display the following data:
- System name
- System number
- Federal system type
- Population served '-"
2. Save the report
3. Run the report
4. Modify the report to include replaceable parameters and Limit the selection criteria for up
to three states
5. Run the report
7.13
-------
7.5 FORMS
This section presents a brief overview of the wide range of formatting capabilities available
under the FORMS option. The on-line help in the FORMS option is very robust and clear. Help
can be accessed by pressing the Fl KEY at any field location on the BUILD FORMS screen.
The instructor will conduct an introductory demonstration of the FORMS options using the
following demonstration.
Demonstration
A. To Access FORMS from the PRF Main Menu - A new FORM is created by entering
an "A" alongside the FORMS OPTion and giving it a name to be saved under. It is
recommended that the FORM name be the same or similar to the query it will be
associated with to identify and manage its use. Libraries of FORMS can be selected
by entering an "L". Other user's FORMS can be viewed and copied in the~same
manner as QUERIES.
PRFM.01 R04.00 PLATINUM REPORT FACILITY 06/01 13:46
COMMAND=>
PRF0053W You must specify a valid option (or "X" to exit)
OPT NAME
QUERY ;
A FORM TRAIN6.EX3
PROCEDURE
New object name for C/N options...
DB2 subsystem: #D2D
Enter one of the following options:
A-Add B-Batch C-Copy D-Delete
E-Edit F-Freefotm L-Library N-rcName
R - Run S - Select X - eXit
B. Available FORM types are:
- Columnar is straight columns of data, typically used for detailed reports.
- Tabular is used to do math on data values, and is typically used for summary
reports
- Extract creates a flat file with some formatting options such as comma delimited
for importation into applications such as LOTUS or dbase*.
To add or create a new FORM, select the form type and press enter. This class will
only cover the Column FORM. The others options are covered in the Intermediate
and Advanced Training Classes.
7.14
-------
CFRM.02 R04.00 CREATE FORM 06/02 17:50
COMMAND=> GJL.TRAIN1
Select Form Type => B - Block
C-Column
L Letter
M - Mailing Label
T - Tabulation
X-extract
Name of Query«
Blank to bring up Query Library.
C. Linking a FORM to a Query - If the user can not remember the name of the QUERY
he wishes Jo build a FORM for, he need only TAB to the "Name of Query => field,
and press enter, leaving it blank. The user's query library will be displayed. The user
may select the desired query by placing an "S" alongside the desired queryjand press
enter.
D. Build "COLUMN" Form - will display the query's selection criteria. The user can
specify which columns will be displayed on the report, the order in which they appear,
how many characters will be displayed, etc. Some formats can be selected (e.g.,
monetary, scientific, various date formats, etc.). Data can be grouped into blocks of
data and some math functions can be performed.
**Insert build col form
Headings can be changed from the actual attribute name as well as how the
heading is displayed can be specified (e.g., single word, stacked multiple words,
all caps, or combinations, etc.) Footers can also be specified in addition to other
options. As the options are selected, the bottom of the Build "Columns" Form
displays a template to assist the user in visualizing how the report will appear.
The FORM Profile Screen is displayed after a FORM is created to allow the user to
describe the form and designate whether it is shareable or restricted. Describing the
FORM is recommended to inform other potential users regarding the nature of the
FORM, remind the user, and provides a tool for data set management.
OPRO.03 R04.00 FORM PROFILE 06/02 18:04
COMMAND=> GJL.TRAIN3
PRF0102I Enter short description
DESCR=
SHARE >Y
"Y" - others can use - but not update - your FORM
"N" your FORM cannot be referenced or copied by others
"U" - others can use - and update - your FORM
7.15
------- |