+-----------------------+----------------------------------------------+
| ![](medi              | **Software Release Notes for SDWIS/STATE     |
| a/image1.wmf){width=" | Interim**                                    |
| 2.2083333333333335in" |                                              |
| height="              | **Release 8.0.4 for Windows NT and Oracle**  |
| 1.229861111111111in"} |                                              |
|                       |                                              |
| United States         |                                              |
| Environmental         |                                              |
| Protection Agency     |                                              |
|                       |                                              |
| Office of Ground      |                                              |
| Water and Drinking    |                                              |
| Water                 |                                              |
|                       |                                              |
| Contract No.          |                                              |
| 68-W-99-002           |                                              |
|                       |                                              |
| Task Order No. 017    |                                              |
|                       |                                              |
| Product Control No.   |                                              |
|                       |                                              |
| SDC-0002--017-JB-5038 |                                              |
|                       |                                              |
| March 14, 2003        |                                              |
+-----------------------+----------------------------------------------+

**SOFTWARE RELEASE NOTES FOR SDWIS/STATE INTERIM RELEASE 8.0.4 FOR
WINDOWS NT AND ORACLE**

**CONTRACT NO. 68-W-99-002**

**TASK ORDER NO. 017**

**Prepared for:**

**United States Environmental Protection Agency**

**Office of Ground Water and Drinking Water**

**Drinking Water Protection Division**

**1200 Pennsylvania Avenue NW.**

**Washington, DC 20460**

**Task Order Project Officer:**

**Edward Cottrill**

**Prepared by:**

**Systems Development Center**

**Science Applications International Corporation**

**6565 Arlington Boulevard**

**Falls Church, VA 22042**

**CONTENTS**

**1.0 INTRODUCTION [1](#introduction)**

2.0 DESCRIPTION OF SDWIS/STATE INTERIM RELEASE 8.0.4 FOR WINDOWS NT AND
ORACLE
[1](#description-of-sdwisstate-interim-release-8.0.4-for-windows-nt-and-oracle)

2.1 SDWIS/STATE Interim Release 8.0.4 for Windows NT and Oracle
[1](#sdwisstate-interim-release-8.0.4-for-windows-nt-and-oracle)

2.1.1 Online SDWIS/STATE Components [2](#online-sdwisstate-components)

2.1.2 Migration to SDWIS/STATE: Monitoring
[4](#migration-to-sdwisstate-monitoring)

2.1.3 Migration to SDWIS/FED [5](#migration-to-sdwisfed)

2.2 Scripts [5](#scripts)

2.2.1 Reset_TCDSSLOG_Processes.SQL [5](#reset_tcdsslog_processes.sql)

2.2.2 Add_New_Indexes.SQL [6](#add_new_indexes.sql)

2.2.3 RESET_WSF_STOR_LOC.SQL [6](#reset_wsf_stor_loc.sql)

2.2.4 Insert_TMNVTAA.SQL [6](#insert_tmnvtaa.sql)

2.2.5 Resolve_Dup_TMNMNR.SQL [7](#resolve_dup_tmnmnr.sql)

2.2.6 Reset_RG_TCR_VIOL_DOC.SQL [7](#reset_rg_tcr_viol_doc.sql)

3.0 INSTALLATION INSTRUCTIONS AND FILE LIST FOR SDWIS/STATE INTERIM
RELEASE 8.0.4 FOR WINDOWS NT AND ORACLE
[7](#installation-instructions-and-file-list-for-sdwisstate-interim-release-8.0.4-for-windows-nt-and-oracle)

3.1 Scripts [8](#scripts-1)

3.2 SDWIS/STATE Replacement Application Files
[8](#sdwisstate-replacement-application-files)

3.3 File List [9](#file-list)

**APPENDIX A** Nebraska and Connecticut Performance Testing Results for
SDWIS/STATE Interim Release 8.0.4 for Windows NT and Oracle

**EXHIBIT**

**Exhibit 1.** Database Administrator Scripts for Release 8.0.4 for
Windows NT and

Oracle [9](#__RefHeading___Toc33862982)

+-----------------------------------------------------------------------+
| ![](media/image3.wmf){width="0.5111111111111111in"                    |
| height="0.2611111111111111in"}**NOTE:**                               |
|                                                                       |
| **The page numbers on the Contents page work as hyperlinks. Once you  |
| have used a hyperlink, a toolbar with back and front arrows will      |
| appear, enabling you to navigate through the document.**              |
+-----------------------------------------------------------------------+

# 1.0 INTRODUCTION

The *Safe Drinking Water Information System (SDWIS)/STATE Interim
Release 8.0.4 for Windows NT and Oracle* is being issued to improve the
performance of *Compliance Decision Support* (*CDS) Setup*. Where
modules are being reissued in support of this improvement, and where
software problems have been identified for those modules since
*SDWIS/STATE Release 8.0.3 Cumulative*, the fixes are also included, as
described in these Release Notes. Four software fixes are also included
to modules that are not related to *CDS Setup*.

You should install SDWIS/STATE Interim Release 8.0.4 only after your
primacy agency has installed *SDWIS/STATE Release 8.0.3 Cumulative for
Windows NT and Oracle.*

Please review Appendix A for detailed information about the performance
testing for this release, so that you can compare your own database and
database server with this information. Performance is a factor that
relies on many different variablesonly some of which can be controlled
by the SDWIS/STATE software. See Appendix A for an outline of the
environmental conditions under which specified performance results were
achieved for

*CDS Setup*. Even though the changes made with this release will reduce
the time it takes to run *CDS Setup*, you should initially run it over
the weekend.

For information and current advisories and discrepancies, see recently
delivered *Frequently Asked Questions, Advisories, and Known
Discrepancies for SDWIS/STATE Release 8.0*

(SDC-0002-017-CW-5031), dated February 21, 2003.

This release notes document includes a description of SDWIS/STATE
Interim Release 8.0.4 contents and instructions for file installation.
For answers to questions not covered by these release notes, please call
the SDWIS User Support Hotline at 703‑292‑6298 or send an e-mail to

# 2.0 DESCRIPTION OF SDWIS/STATE INTERIM RELEASE 8.0.4 FOR WINDOWS NT AND ORACLE 

This section contains a brief description of the new and improved
functionality included in SDWIS/STATE Interim Release 8.0.4.

## 2.1 SDWIS/STATE Interim Release 8.0.4 for Windows NT and Oracle

The following modules have been modified to improve the overall
performance of *CDS Setup.* Some software problems have also been
addressed in those modules that had to be reissued for performance
reasons. These are noted below.

### 2.1.1 Online SDWIS/STATE Components 

This document describes *CDS Setup* modules as well as online functions
either sharing *CDS Setup* software or requiring changes needed by *CDS
Setup* to improve performance. Additional software problems addressed in
these modules are also noted.

**2.1.1.1 CDS Setup**

The primary reason for issuing SDWIS/STATE Interim Release 8.0.4 is to
improve performance with *CDS Setup*. Appendix A provides detailed
information about the performance testing for this release, so that you
can compare your own database with this information. Appendix A includes
database and parametric data, size of database tables that *CDS Setup*
particularly targets, and performance times for a set of specified
tests. During the assessment of how to increase performance, the
following software problems were also addressed:

-   **Rounding Total Trihalomethane (TTHM) Samples**The *CDS Setup*
    Calculate Maximum Contaminant Level (MCL) Values process was not
    rounding to the significant digits stored in table TMNFANL, field
    MAX, for analytes TTHM and Haloacetic Acid 5 (HAA5). Instead, it was
    rounding to the significant digits stored for these analytes in the
    MCL field of table TMNALRA. This discrepancy has been fixed.

```{=html}
<!-- -->
```
-   **Adjusted Gross Alpha Calculation Bug** *CDS Setup* was adding
    total gross alpha (analyte code 4109) and combined uranium (analyte
    code 4006) to get gross alpha minus uranium and radon. *CDS Setup*
    should have been subtracting instead of adding. This discrepancy has
    been fixed. The gross alpha results (analyte code 4000) previously
    calculated by *CDS Setup* will be recalculated the first time you
    run *CDS Setup* for SDWIS/STATE Interim Release 8.0.4. This will
    cause all resultant monitoring period and running annual averages to
    be recalculated.

```{=html}
<!-- -->
```
-   **Schedule Type Not Displayed in Post CDS Setup Report**The
    Schedule Type was not displaying in the Post CDS Setup Report that
    was created locally after *CDS Setup* processing completed. Since
    the Schedule Type would be RT, it was determined that this did not
    add any value to the report. The Type field has therefore been
    removed.

    2.  **Online Sampling---Result Maintenance**

The Chemical, Radionuclide, and Water Quality Parameter result
maintenance windows use *CDS Setup* logic and, therefore, had to be
reissued. As such, the following software problems were also addressed
at the same time:

-   **Transferring Monitoring Periods**The **Change Monitoring Period**
    button that was used to transfer a result to a different monitoring
    period on the Chemical, Radionuclide, and Water Quality Parameter
    result maintenance windows was not working correctly. When the
    sample schedule corresponding to the result included a sampling
    point, the result was not being properly associated to the newly
    selected Sample Schedule Monitoring Period Assignment (SSMPA) and/or
    sub-Sample Schedule Monitoring Period Sampling Point Assignment. As
    a result, the Chem/RAD Monitoring and Reporting (M&R) Compliance
    Report did not return a candidate violation. (The report did,
    however, show that a sample was collected for the period, so you
    could distinguish when this situation occurred.) This discrepancy
    has been fixed. Note that with SDWIS/STATE Release 8.0, the **Change
    Monitoring Period** button on the General Microbiological Sample
    Analytical Result Maintenance window was not designed to work as it
    does on the Chemical, Radionuclide, and Water Quality Parameter
    result maintenance windows. You can use the non-TCR sample schedule
    function and *CDS Setup* to determine monitoring compliance for
    microbiological samples for analytes other than 3100, 3013,
    and 3014. However, if one or more of the schedules has a sampling
    point subschedule, and the result is associated to a new monitoring
    period, the CDS report will list a monitoring violation. As
    previously noted, the report will still show that a sample was
    collected for the period.

```{=html}
<!-- -->
```
-   **Radionuclide Result**On the individual Radionuclide Result
    Maintenance window, if you checked \"Less Than\"/\"Lab Reporting
    Level,\" most of the results information was not saved. This problem
    has been fixed.

-   **Water Quality Results**The Water Quality Result window did not
    save the last result of a user-defined analyte group. This problem
    has been fixed.

2.1.1.3 Online Monitoring and NoncomplianceMonitoring
Requirements/Monitoring Periods

Previously, when you changed a monitoring requirement, SDWIS/STATE
updated the Last_Update_Timestamp of all monitoring periods associated
to the monitoring requirement. Monitoring periods are associated to a
monitoring requirement through sample schedules. While this update was
considered a safety measure at the time *CDS Setup* 8.0 was released, it
has subsequently been determined not to be necessary, and removing it
reduces the amount of time needed to run *CDS Setup*. The online
monitoring requirements maintenance function has been changed so that it
no longer updates the Last_Update_Timestamp of any monitoring period.

Additionally, the monitoring period maintenance function has been
changed so that it only changes the Last_Update_Timestamp for a
monitoring period record if you change either the monitoring period
Begin Date or End Date. The benefits are the same as described above.

4.  **Online Monitoring and NoncomplianceNon-Total Coliform Rule
    (TCR)**

Schedule/Schedule Group

Previously, when you changed a non-TCR sample schedule, SDWIS/STATE
updated the Last_Update_Timestamp of all monitoring periods associated
to the sample schedule. The online non-TCR sample schedule maintenance
function has been changed so that it no longer updates the
Last_Update_Timestamp of any monitoring periods. While this update was
considered a safety measure at the time, it has subsequently been
determined not to be necessary, and removing it reduces the amount of
time needed to run *CDS Setup*. In addition, the problem of creating a
duplicate monitoring requirement when you add a schedule group under the
particular scenario where the schedule group's referenced monitoring
requirement does not reference a violation type, has been corrected.

2.1.1.5 Online Monitoring and NoncomplianceValidate Positive TCR
Results and TCR Noncompliance Determination (NCD)

A SDWIS/STATE regional user recently found that the Validate Positive
TCR Results and TCR NCD functions were creating preliminary TCR
violations where the value in the Data Origin Code field was set to S
(State), regardless of the value in the database's primacy agency
government type code. As a result, for a regionally owned database,
these preliminary TCR violations were incorrectly getting a Data Origin
Code of \"S.\" This problem has been corrected.

**2.1.1.6 Online Inventory****Storage Facility**

This fix addresses a problem that had gone undetected since SDWIS/STATE
Release 6.0. When you modified a Storage Facility (and only a Storage
Facility) that had locational data, the record was being incorrectly
reset to indicate it had no locational record. This problem has been
corrected.

### 2.1.2 Migration to SDWIS/STATE: Monitoring 

Because the *Migration to SDWIS/STATE: Monitoring* component shares
components that were modified for *CDS Setup*, it had to be re-issued;
therefore, several minor, previously documented software problems have
been addressed at the same time.

-   **PN Activity and Compliance Schedule modules***Migration to
    SDWIS/STATE: Monitoring* Public Notification (PN) Activity and
    Compliance Schedule modules could both previously migrate only 100
    PN activity or Compliance Schedule records at a time. This problem
    has been fixed, and you can now migrate as many of each type of
    record in your text file as you wish.

-   **Duplicate Monitoring Requirement**The *Migration to SDWIS/STATE:
    Monitoring* Schedule module created a duplicate monitoring
    requirement when adding a schedule group under the particular
    scenario where the schedule group's referenced monitoring
    requirement *did not* reference a violation type. This problem
    (addressed in the online software as described in Subsection
    2.1.1.4) has been fixed.

-   **Exception Schedules**A problem where the *Migration to
    SDWIS/STATE: Monitoring* Schedule module was not migrating the
    Exception Schedule's WSF State Asgn ID, and therefore, the software
    was unable to find the exception schedule\'s corresponding schedule
    group, has also been fixed.

```{=html}
<!-- -->
```
-   **Milestone Reason Code***Migration to SDWIS/STATE: Monitoring*
    Milestones module failed to migrate in the Milestone Reason Code.
    This has been fixed. You will still need to follow the instructions
    pertaining to the Milestone table in *Import Text to Staging Table*
    (Subsection 3.5.1.1) in *Frequently Asked Questions, Advisories, and
    Known Discrepancies for SDWIS/STATE Release 8.0*.

### 2.1.3 Migration to SDWIS/FED

The following two recently reported problems have been addressed in
*Migration to SDWIS/FED.*

2.1.3.1 Migration to SDWIS/FED: Inventory

Recently, a user who had already used *Migration to SDWIS/FED:
Inventory* to migrate several thousand site visit records, uncovered a
problem with migrating site visits. The problem was traced to the
inability of the software to handle a scenario where one or more of the
water systems has over 33 site visits. This problem has been fixed in
order to enable primacy agencies who have migrated in historical site
visit information to report them to SDWIS/FED.

2.  **Migration to SDWIS/FED: Actions**

Recently, a user used *Migration to SDWIS/FED: Actions* to send a
quarterly report to SDWIS/FED. The resulting error report showed that
all the type 75 violations sent were rejected because they were
\"missing data elements C1144 or C1145 and had the incorrect component
number.\" All the violations were linked to an original violation in
SDWIS/STATE and a P5000 link was included in the DTF file. The problem
was traced to the software using the wrong element number (P5000) to
specify the Violation ID for which the PN violation is issued, rather
than data element C1144. The software has been corrected to use data
element C1144 for this purpose.

## 2.2 Scripts

For specific information on the name, description, execution
instructions, as well as instructions on where to copy the script, see
Exhibit 1.

### 2.2.1 Reset_TCDSSLOG_Processes.SQL 

Run this script only once prior to running *CDS Setup* for the first
time after installing SDWIS/STATE Interim Release 8.0.4. This script
resets values in the CDS Setup Log (table TCDSSLOG). Specifically, it
resets the Timestamp for the Process 2B record (Associate Sample
Schedules to Added/Changed Monitoring Periods) to the latest
Last_Update_Timestamp in the monitoring period table (TMNMPRD) that
references a TMNSSMPA indicating that it has already been processed in a
previous *CDS Setup* run. The script also establishes the timestamp and
IS Number entries for the following *CDS Setup* processes:

-   5ACalculate Total Trihalomethane and Total Haloacetic Acid
    Results - Changed Sample.

-   5BCalculate Total Trihalomethane and Total Haloacetic Acid
    Results - Changed SAR.

-   5CCalculate Total Nitrate/Nitrite Results - Changed Sample.

-   5D Calculate Total Nitrate/Nitrite Results - Changed SAR.

-   5ECalculate Gross Alpha, Combined Radium Results - Changed Sample.

-   5F Calculate Gross Alpha, Combined Radium Results - Changed SAR.

For processes 5E and 5F, the IS Number will be reset to 0 and the
Reference timestamp reset to null. This will cause all gross alpha data
to be correctly recalculated during the first run of *CDS Setup* 8.0.4.

### 2.2.2 Add_New_Indexes.SQL 

Run this script only once prior to running *CDS Setup* for the first
time after installing SDWIS/STATE Interim Release 8.0.4. It adds
additional indexes to improve performance of *CDS Setup* for tables
TCDSVIOL, TMNCMCLV, TMNSASCH, TMNMPRD, TMNMNR, TSAANLYT, TMNMPAVG, and
TMNSSMPA.

### 2.2.3 RESET_WSF_STOR_LOC.SQL

Run this script only once. This script addresses a problem that has gone
undetected since SDWIS/STATE 6.0. It resets the Locational Data
Indicator to \"Y\" for Storage Facilities that have a locational data
record where that indicator may have been incorrectly set to \"N.\"

### 2.2.4 Insert_TMNVTAA.SQL

Run this script only once. This script inserts a new record into table
TMNVTAA to support recent reporting guidance for Microbial Disinfection
By-Products (MDBP) Rule and Interim Enhanced Surface Water Treatment
Rule (IESWTR) to help you meet federal reporting requirements for
violations.

### 2.2.5 Resolve_Dup_TMNMNR.SQL

Run this script only once. The script resolves a problem where duplicate
monitoring requirements can be added. The software and this script work
in conjunction to enable the software fix described in Subsection
2.1.1.4, where a duplicate monitoring requirement may be added when
adding a schedule group whose referenced monitoring requirement *does
not* reference a violation type. While this scenario is known to have
occurred in only two primacy agencies, this script will resolve any
duplicate monitoring requirements as well as correct any potential
referential usage of those duplicates by schedules, schedule groups,
etc., wherever the situation may exist.

### 2.2.6 Reset_RG_TCR_VIOL_DOC.SQL

**If your primacy agency is a Region, you must run this script once. If
your primacy agency is a State, do not run this script.**

Regions must run this script once; states must not run it. This script
resets the Data Origin Code to \"R\" for TCR violations where the
Validate Positive Sample Results and/or TCR NCD incorrectly set the Data
Origin Code to \"S.\"

# 3.0 INSTALLATION INSTRUCTIONS AND FILE LIST FOR SDWIS/STATE INTERIM RELEASE 8.0.4 FOR WINDOWS NT AND ORACLE

*SDWIS/STATE Interim Release 8.0.4 for Windows NT and Oracle* is a
combination of automatically installed files (application files) and
database scripts that you will need to copy and run from the
installation package. For automatically installed files, see
instructions in Subsection 3.2.

From EPA's SDWIS/STATE web site, download file 8.0.4_nt.zip. Using
WinZip, extract to a folder you name/create, such as EXTRACT_804_NT.
When you open this folder, you will see the following folders and files:

##### Folder DBA_Scripts_Oracle

**Folder Sdwis80**

File Appl804_Files.LST

File Appl804_NT.INF

File Install_Appl804NT.EXE

## 3.1 Scripts 

Exhibit 1 lists all the database scripts delivered with *SDWIS/STATE
Interim Release 8.0.4* for *Windows NT and Oracle.*

You will need to manually copy the scripts listed in this matrix. Use
the decision columns in the matrix to help you decide if you need to
copy a script and when it should be run. The last column of the matrix
specifies the folder into which you should copy a given script.

To run each script, log in as the DBA user (e.g., SYSTEM) and run it in
SQL\*Plus.

## 3.2 SDWIS/STATE Replacement Application Files

All SDWIS/STATE 8.0 Windows NT/Oracle users need to install the
application files. The files listed in Subsection 3.3 replace existing
files in the folder where you keep your SDWIS/STATE 8.0 application
software.

***Note: Do not access the application while the files are being
updated.***

Click on file INSTALL_Appl804NT.EXE. Press the **Install System**
button. You will be asked to confirm where the application files should
be installed. Select the same folder where your SDWIS/STATE Release 8.0
application files reside. (The files included in SDWIS/STATE Interim
Release 8.0.4 will overwrite existing files that were installed during a
previous SDWIS/STATE 8.0 release.) Press **O**K. Once the installation
is complete, you will receive a confirmation dialog box telling you that
the installation was successful. Press **OK**. Press **OK** in the
Installation Complete dialog box. Register.exe will automatically run
and register your files. When completed, press any key to exit.

+-------------------+-------+--------------------+--------------------+
| ***Name of the    | *     | #### *When to run* | ***Copy to         |
| script***         | **Who |                    | folder***          |
|                   | must  |                    |                    |
|                   | r     |                    |                    |
|                   | un*** |                    |                    |
+-------------------+-------+--------------------+--------------------+
| Reset_TCDSSLOG\_  | All   | This script should | C:\\SDWIS_MIGRATE  |
| Processes.SQL     |       | be run once prior  |                    |
|                   |       | to starting *CDS   | 70to80             |
|                   |       | Setup* 8.0.4 for   |                    |
|                   |       | the first time.    | \\DBA_SCRIPTS      |
+-------------------+-------+--------------------+--------------------+
| Ad                | All   | This script should | C:\\SDWIS_MIGRATE  |
| d_New_Indexes.SQL |       | be run once prior  |                    |
|                   |       | to starting *CDS   | 70to80             |
|                   |       | Setup* 8.0.4*.*    |                    |
|                   |       | for the first      | \\DBA_SCRIPTS      |
|                   |       | time.              |                    |
+-------------------+-------+--------------------+--------------------+
| RESET_WSF_STOR\_  | All   | This script should | C:\\SDWIS_MIGRATE  |
|                   |       | be run once prior  |                    |
| LOC.SQL           |       | to using           | 70to80             |
|                   |       | SDWIS/STATE        |                    |
|                   |       | Interim Release    | \\DBA_SCRIPTS      |
|                   |       | 8.0.4*.*           |                    |
+-------------------+-------+--------------------+--------------------+
| Insert_TMNVTAA.   | All   | This script should | C:\\SDWIS_MIGRATE  |
|                   |       | be run once prior  |                    |
| SQL               |       | to using           | 70to80             |
|                   |       | SDWIS/STATE        |                    |
|                   |       | Interim Release    | \\DBA_SCRIPTS      |
|                   |       | 8.0.4*.*           |                    |
+-------------------+-------+--------------------+--------------------+
| Resolve_Dup\_     | All   | This script should | C:\\SDWIS_MIGRATE  |
|                   |       | be run once prior  |                    |
| TMNMNR.SQL        |       | to using           | 70to80             |
|                   |       | SDWIS/STATE        |                    |
|                   |       | Interim Release    | \\DBA_SCRIPTS      |
|                   |       | 8.0.4*.*           |                    |
+-------------------+-------+--------------------+--------------------+
| Reset_RG          | **Re  | This script should | C:\\SDWIS_MIGRATE  |
| _TCR_VIOL_DOC.SQL | gions | be run once prior  |                    |
|                   | O     | to using           | 70to80             |
|                   | nly** | SDWIS/STATE        |                    |
|                   |       | Interim Release    | \\DBA_SCRIPTS      |
|                   |       | 8.0.4*.*           |                    |
+-------------------+-------+--------------------+--------------------+

[]{#__RefHeading___Toc33862982 .anchor}**Exhibit 1.** Database
Administrator Scripts for Release 8.0.4 for Windows NT and Oracle

## 3.3 File List

The following files are included with *SDWIS/STATE Interim Release 8.0.4
for Windows NT and Oracle*:

02/06/2003 06:34p 5,882 CDS0001S.dde

02/06/2003 06:50p 536,576 CDS0001S.DLL

09/01/2000 06:12p 40,960 CDS0001S.EXE

02/06/2003 06:50p 1,300 CDS0001S.reg

02/06/2003 06:38p 568 CDS0002S.dde

02/06/2003 06:50p 348,160 CDS0002S.DLL

09/01/2000 06:12p 40,960 CDS0002S.EXE

02/06/2003 06:50p 1,300 CDS0002S.reg

02/06/2003 06:33p 400 CDS0003S.dde

02/06/2003 06:49p 143,360 CDS0003S.DLL

09/01/2000 06:12p 40,960 CDS0003S.EXE

02/06/2003 06:49p 1,300 CDS0003S.reg

02/22/2003 03:39p 729 CDS0005S.dde

02/22/2003 03:40p 1,265,664 CDS0005S.DLL

09/01/2000 06:12p 40,960 CDS0005S.EXE

02/22/2003 03:39p 1,300 CDS0005S.reg

02/06/2003 06:34p 568 CDS0008S.dde

02/06/2003 06:49p 315,392 CDS0008S.DLL

09/01/2000 06:12p 40,960 CDS0008S.EXE

02/06/2003 06:49p 1,300 CDS0008S.reg

02/06/2003 06:33p 333 CDS0009S.dde

02/06/2003 06:49p 159,744 CDS0009S.DLL

09/01/2000 06:12p 40,960 CDS0009S.EXE

02/06/2003 06:49p 1,300 CDS0009S.reg

02/06/2003 06:35p 554 CDS0010S.dde

02/06/2003 06:50p 380,928 CDS0010S.DLL

09/01/2000 06:12p 40,960 CDS0010S.EXE

02/06/2003 06:50p 1,300 CDS0010S.reg

02/06/2003 06:34p 563 CDS0011S.dde

02/06/2003 06:49p 307,200 CDS0011S.DLL

09/01/2000 06:12p 40,960 CDS0011S.EXE

02/06/2003 06:49p 1,300 CDS0011S.reg

02/13/2003 10:46a 334 CDS0012S.dde

02/13/2003 10:47a 487,424 CDS0012S.DLL

09/01/2000 06:12p 40,960 CDS0012S.EXE

02/13/2003 10:47a 1,300 CDS0012S.reg

02/13/2003 10:46a 331 CDS0013S.dde

02/13/2003 10:47a 1,036,288 CDS0013S.DLL

09/01/2000 06:12p 40,960 CDS0013S.EXE

02/13/2003 10:47a 1,300 CDS0013S.reg

02/13/2003 10:54a 2,560 CDSSTAT_01_Primary_Progress_Bar.ole

02/19/2003 02:05p 50,227 GEN0200E.dde

02/19/2003 02:08p 659,456 GEN0200E.DLL

02/20/2003 01:42p 21,428 GEN0200E.HLP

02/19/2003 02:07p 1,300 GEN0200E.reg

02/23/2003 12:53p 248,068 GEN0300E.dde

02/23/2003 01:04p 2,121,728 GEN0300E.DLL

09/01/2000 06:12p 40,960 GEN0300E.EXE

02/20/2003 01:45p 22,286 GEN0300E.HLP

02/23/2003 01:04p 1,300 GEN0300E.reg

02/19/2003 05:47p 148,537 IBS0400E.dde

02/19/2003 06:01p 1,785,856 IBS0400E.DLL

02/20/2003 01:47p 37,678 IBS0400E.HLP

02/19/2003 06:01p 1,300 IBS0400E.reg

03/13/2003 02:11p 335,705 IBS0500E.dde

03/13/2003 02:11p 3,293,184 IBS0500E.DLL

09/01/2000 06:12p 40,960 IBS0500E.EXE

03/13/2003 02:39p 191,561 IBS0500E.HLP

03/13/2003 02:11p 1,300 IBS0500E.reg

02/19/2003 05:43p 308,268 IBSMAIN.dde

02/19/2003 05:52p 3,678,208 IBSMAIN.DLL

02/20/2003 02:00p 148,669 IBSMAIN.HLP

02/19/2003 05:50p 1,289 IBSMAIN.reg

02/06/2003 06:34p 116,859 MBS0200E.dde

02/06/2003 06:48p 2,461,696 MBS0200E.DLL

09/01/2000 06:12p 40,960 MBS0200E.EXE

02/27/2003 03:28p 70,291 MBS0200E.HLP

02/06/2003 06:48p 1,300 MBS0200E.reg

02/06/2003 06:36p 108,517 MBS0500E.dde

02/06/2003 06:49p 1,433,600 MBS0500E.DLL

09/01/2000 06:12p 40,960 MBS0500E.EXE

02/27/2003 03:29p 48,907 MBS0500E.HLP

02/06/2003 06:49p 1,300 MBS0500E.reg

02/06/2003 06:35p 395,994 MBS0600E.dde

02/06/2003 06:48p 5,230,592 MBS0600E.DLL

09/01/2000 06:12p 40,960 MBS0600E.EXE

02/27/2003 03:33p 169,694 MBS0600E.HLP

02/06/2003 06:48p 1,300 MBS0600E.reg

02/06/2003 06:35p 64,093 MBS0900E.dde

02/06/2003 06:48p 851,968 MBS0900E.DLL

09/01/2000 06:12p 40,960 MBS0900E.EXE

02/27/2003 03:38p 47,782 MBS0900E.HLP

02/06/2003 06:48p 1,300 MBS0900E.reg

02/06/2003 06:37p 20,749 MBS1200E.dde

02/06/2003 06:49p 741,376 MBS1200E.DLL

09/01/2000 06:12p 40,960 MBS1200E.EXE

02/27/2003 03:39p 31,785 MBS1200E.HLP

02/06/2003 06:49p 1,300 MBS1200E.reg

02/06/2003 06:37p 35,641 MIG0200E.dde

02/06/2003 06:50p 3,616,768 MIG0200E.DLL

09/01/2000 06:12p 40,960 MIG0200E.EXE

02/27/2003 03:49p 29,539 MIG0200E.HLP

02/06/2003 06:50p 1,300 MIG0200E.reg

04/24/2002 03:45p 49,152 REGISTER.EXE

02/06/2003 04:25p 223,707 SBS2000E.dde

02/06/2003 04:27p 2,060,288 SBS2000E.DLL

09/01/2000 06:12p 40,960 SBS2000E.EXE

02/27/2003 03:51p 57,356 SBS2000E.HLP

02/06/2003 04:26p 1,300 SBS2000E.reg

02/25/2003 05:42p 91,410 SBS2300E.dde

02/25/2003 05:43p 933,888 SBS2300E.DLL

09/01/2000 06:12p 40,960 SBS2300E.EXE

02/20/2003 02:02p 39,577 SBS2300E.HLP

02/25/2003 05:43p 1,300 SBS2300E.reg

02/06/2003 04:25p 71,313 SBS2500E.dde

02/06/2003 04:27p 421,888 SBS2500E.DLL

09/01/2000 06:12p 40,960 SBS2500E.EXE

02/27/2003 04:42p 38,989 SBS2500E.HLP

02/06/2003 04:27p 1,300 SBS2500E.reg

02/06/2003 04:25p 58,839 SBS2700E.dde

02/06/2003 04:27p 290,816 SBS2700E.DLL

09/01/2000 06:12p 40,960 SBS2700E.EXE

02/27/2003 04:43p 30,758 SBS2700E.HLP

02/06/2003 04:27p 1,300 SBS2700E.reg

03/13/2003 02:43p 3,195,933 SDWIS.HLP

115 File(s) 42,017,791 bytes

Script files:

02/12/2003 07:14p 1,671 Insert_TMNVTAA.SQL

02/21/2003 12:30p 3,375 Reset_RG_TCR_VIOL_DOC.SQL

02/24/2003 03:39p 13,899 Reset_TCDSSLOG_Processes.SQL

02/07/2003 01:09p 2,073 RESET_WSF_STOR_LOC.SQL

02/07/2003 01:05p 3,460 Resolve_Dup_TMNMNR.SQL

02/12/2003 07:11p 4,719 Add_New_Indexes.SQL

6 File(s) 29,197 bytes

Total Files Listed:

121 File(s) 42,047,988 bytes

**APPENDIX A**

Nebraska and Connecticut Performance Testing Results for SDWIS/STATE

Interim Release 8.0.4 for Windows NT and Oracle

This page is intentionally left blank.

The SDWIS/STATE Project Team measured performance of *CDS Setup* for
SDWIS/STATE Interim Release 8.0.4 and determined that the changes made
to some software modules as well as the addition of some indexes
improved performance. Because performance is a factor that relies on
many different variables, only some of which are controlled by the
SDWIS/STATE software, the information in this appendix documents the
environmental conditions under which *CDS Setup* achieved the specified
performance results.

1.0 DATABASE DESCRIPTION:

*CDS Setup* performance for SDWIS/STATE Interim Release 8.0.4 was
benchmarked on two databases, one supplied by Nebraska and the other
supplied by Connecticut.

**1.1 Nebraska Database Description**

The size of the export file was 600 GB with the following number of
records in tables heavily processed by *CDS Setup*:

**Table Name No. of records No. of records**

**before after**

> **running running**
>
> ***CDS Setup* *CDS Setup***

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TMNSASCH 190,165 189,552

TMNMPRD 169 170

TSASAMPL 237,498 237,502

TSASAR 1,251,313 1,251,346

TMNSSMPA 480,069 479,877

TMNCMCLV 440,684 441,561

TMNMPAVG 441,723 441,561

2.  **Connecticut Database Description**

The size of the export file was 351 GB with the following number of
records in tables heavily processed by *CDS Setup*:

**Table Name No. of records No. of records**

**before after**

> **running running**
>
> ***CDS Setup* *CDS Setup***

\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\--

TMNSASCH 165,611 165,611

TMNMPRD 143 143

TSASAMPL 51,976 51,976

TSASAR 232,842 233,198

TMNSSMPA 451,208 530,674

TMNCMCLV 444,903 447,156

TMNMPAVG 450,684 531,611

2.0 DATABASE AND SERVER PARAMETRIC INFORMATION

*CDS Setup* testing took place with the database and database server set
up according to the following parametric conditions:

-   Two workstations were used, one as a client and the other as a
    server.

```{=html}
<!-- -->
```
-   *CDS Setup* was run from the client Windows NT 4.0 workstation (Dell
    Optiplex GX400 with Pentium IV 1396 MHz, 0.5 GB RAM, and 37 GB disk
    space).

```{=html}
<!-- -->
```
-   An Oracle net service name, DBPWSS01, was created with a connection
    type of \"Dedicated Server\" on the client workstation.

```{=html}
<!-- -->
```
-   All rollback segments were brought offline except BIGRBS (and
    SYSTEM) before running *CDS Setup* such that *CDS* processes would
    use it.

```{=html}
<!-- -->
```
-   The databases, NEV80 and CTV80 were analyzed prior to running *CDS
    Setup*.

The following database server configuration parameters were used:

-   Oracle Server Version: 8.1.6 Enterprise Edition.

```{=html}
<!-- -->
```
-   Processor Type/clock speed: Pentium IV 1396 Mhz.

```{=html}
<!-- -->
```
-   Number of processors: 1.

```{=html}
<!-- -->
```
-   Operating system: Windows NT 4.0 workstation.

```{=html}
<!-- -->
```
-   Server Class/family: Dell Workstation Optiplex GX400.

```{=html}
<!-- -->
```
-   Total RAM: 0.5 GB.

```{=html}
<!-- -->
```
-   Number of instances running: 1 Oracle instance.

```{=html}
<!-- -->
```
-   Archive log enabled: No.

```{=html}
<!-- -->
```
-   Server mode: Shared server (also known as Multi-Thread Server
    (MTS)).

```{=html}
<!-- -->
```
-   Period during which *CDS Setup* ran: Overnight.

```{=html}
<!-- -->
```
-   Redo log file size: 5 MB.

```{=html}
<!-- -->
```
-   Rollback segment used: BIGRBS with extent size 400K maxextents
    UNLIMITED. Datafile assigned to BIGRBS was 400 MB with autoextend on
    and a maxsize of 1 GB.

```{=html}
<!-- -->
```
-   Size of Temp tablespace: 200 MB.

```{=html}
<!-- -->
```
-   Hard disk drives: One 37GB IDE.

```{=html}
<!-- -->
```
-   Database block size: 8K.

```{=html}
<!-- -->
```
-   db_file_multiblock_read_count: 8.

```{=html}
<!-- -->
```
-   Java_pool_size: 1048576.

```{=html}
<!-- -->
```
-   large_pool_size =32768000.

```{=html}
<!-- -->
```
-   optimizer_mode = CHOOSE.

> (In Oracle 8i (8.1.6) if optimizer_mode was not explicitly configured,
> database used the default value \"CHOOSE\").

-   Shared_Pool_Size: 104857600.

```{=html}
<!-- -->
```
-   DB_Block_Buffers: 19200.

```{=html}
<!-- -->
```
-   Sort_Area_Size: 104857600.

```{=html}
<!-- -->
```
-   Sort_Area_Retained_Size: 1048576.

```{=html}
<!-- -->
```
-   Timed_Statistics: False.

-   Log-Buffer: 2,097,152.

**3.0 PERFORMANCE MEASUREMENTS**

Exhibit A-1 displays the performance benchmark measurements recorded
while testing *CDS Setup* on the Nebraska and Connecticut databases.

+----------------------------------------------------+-----------------+
| **Test Description**                               | **Time          |
|                                                    | Required**      |
+----------------------------------------------------+-----------------+
| Ran *CDS Setup* for the first time on NEV80        | 11 hrs and 17   |
| (Nebraska SDWIS/STATE database) using *CDS Setup*  | min             |
| for SDWIS/STATE Interim Release 8.0.4 (with no     |                 |
| analyte code 4000 or 4010 sample analytical        |                 |
| results).                                          |                 |
+----------------------------------------------------+-----------------+
| Ran *CDS Setup* on Nebraska database subsequently  | 13 min          |
| after:                                             |                 |
+----------------------------------------------------+-----------------+
| -   Adding a monitoring requirement for a single   |                 |
|     analyte.                                       |                 |
+----------------------------------------------------+-----------------+
| -   Adding multiple monitoring requirements (6)    | 11 ½ min        |
|     for a single analyte.                          |                 |
+----------------------------------------------------+-----------------+
| -   Changing a monitoring requirement.             | 5 min           |
+----------------------------------------------------+-----------------+
| -   Adding a monitoring requirement for an analyte | 10 min          |
|     group.                                         |                 |
+----------------------------------------------------+-----------------+
| -   Adding multiple monitoring requirements (6)    | 10 min          |
|     for analyte groups.                            |                 |
+----------------------------------------------------+-----------------+
| -   Adding a monitoring period.                    | 7 min           |
+----------------------------------------------------+-----------------+
| -   Adding multiple monitoring periods (6).        | 14 min          |
+----------------------------------------------------+-----------------+
| -   Changing a monitoring period.                  | 10 ½ min        |
+----------------------------------------------------+-----------------+
| -   Deleting a monitoring period (Quarterly).      | 19 ½ min        |
+----------------------------------------------------+-----------------+
| -   Deleting multiple (6) monitoring periods       | 11 min          |
|     (Weekly).                                      |                 |
+----------------------------------------------------+-----------------+
| -   Adding a schedule/schedule group.              | 10 min−11 ½ min |
+----------------------------------------------------+-----------------+
| -   Changing a schedule/schedule group.            | 12 min−13 min   |
+----------------------------------------------------+-----------------+
| -   *Inventory* Changes (deleting Water Systems,   | 12 min          |
|     Water System Facilities and Sampling Points    |                 |
|     associated to Non-TCR records.                 |                 |
+----------------------------------------------------+-----------------+
| Ran *CDS Setup* for the first time on CTV80        | 12 hrs and 10   |
| (Connecticut SDWIS/STATE database) using *CDS      | min             |
| Setup* for SDWIS/STATE Interim Release 8.0.4 (with |                 |
| analyte code 4000 or 4010 sample analytical        |                 |
| results).                                          |                 |
+----------------------------------------------------+-----------------+

**Exhibit A-1.** Nebraska and Connecticut Database Performance
Benchmarks