+----------------------+-----------------------------------------------+
| ![](media/           | ![](me                                        |
| image1.wmf){width="2 | dia/image3.jpeg){width="1.4583333333333333in" |
| .2083333333333335in" | height="0.7916666666666666in"}**SDWIS/eDV     |
| height="1            | 1.0**                                         |
| .229861111111111in"} |                                               |
|                      | **Installation Guide**                        |
| United States        |                                               |
| Environmental        |                                               |
| Protection Agency    |                                               |
|                      |                                               |
| Office of Ground     |                                               |
| Water and Drinking   |                                               |
| Water                |                                               |
|                      |                                               |
| CONTRACT NO. EPA BPA |                                               |
| OARMSC-SYS-04        |                                               |
|                      |                                               |
| SDWIS Project        |                                               |
|                      |                                               |
| Product Control No.  |                                               |
|                      |                                               |
| Sys                  |                                               |
| talex-SDWIS-01-5-d1e |                                               |
|                      |                                               |
| July 31, 2007        |                                               |
+----------------------+-----------------------------------------------+

**SDWIS/eDV 1.0 INSTALLATION GUIDE**

**CONTRACT NO. EPA BPA OARMSC-SYS-04**

**SDWIS PROJECT**

**Prepared for:**

**United States Environmental Protection Agency**

**Office of Ground Water and Drinking Water**

**Drinking Water Protection Division**

**1200 Pennsylvania Ave., NW**

**Washington, DC 20460**

**Contracting Officer Representative**

**Edward Cottrill**

**Prepared by:**

**SAIC Solutions Delivery Center**

**Science Applications International Corporation**

**6565 Arlington Boulevard**

**Falls Church, VA 22042**

**CONTENTS**

[1.0 Introduction 1](#introduction)

[1.1 Document Overview 1](#document-overview)

[1.2 Software and Hardware Test Environment
1](#software-and-hardware-test-environment)

[1.3 User Support 2](#user-support)

[2.0 Installation Procedures 3](#installation-procedures)

[2.1 Download SDWIS/eDV Installation Package
3](#download-sdwisedv-installation-package)

[2.2 Pre-installation Check 3](#pre-installation-check)

[2.3 Execute the SDWIS/eDV Installation Package
3](#execute-the-sdwisedv-installation-package)

[3.0 Enabling external access to the sdwis/edv derby database
8](#enabling-external-access-to-the-sdwisedv-derby-database)

[3.1 SQUIRREL-SQL and a JDBC Connection
8](#squirrel-sql-and-a-jdbc-connection)

[3.2 MS Access and an ODBC Connection
11](#ms-access-and-an-odbc-connection)

**EXHIBITS**

[**Exhibit 1.** SDWIS/eDV Hardware and Software Test Environment
2](#__RefHeading___Toc173657734)

[**Exhibit 2.** Default Software Port Assignments
2](#__RefHeading___Toc173657735)

[**Exhibit 3.** SDWIS/eDV Installation Welcome
4](#__RefHeading___Toc173657736)

[**Exhibit 4.** SDWIS/eDV Installation Activity
5](#__RefHeading___Toc173657737)

[**Exhibit 5.** SDWIS/eDV Installation Complete
5](#__RefHeading___Toc173657738)

[**Exhibit 6.** SDWIS/eDV Home Page 6](#__RefHeading___Toc173657739)

[**Exhibit 7.** JasperServer Login Page 6](#__RefHeading___Toc173657740)

[**Exhibit 8.** SDWIS/eDV Report 7](#__RefHeading___Toc173657741)

[**Exhibit 9.** SQuirreL-SQL Download Site
9](#__RefHeading___Toc173657742)

[**Exhibit 10.** SQuirreL-SQL Download 10](#__RefHeading___Toc173657743)

[**Exhibit 11.** Port Assignments A-2](#__RefHeading___Toc173657744)

**Appendix A -- Identifying Port Assignments**

# Introduction

The *SDWIS/eDV 1.0 Installation Guide* contains instructions for
installing SDWIS/eDV 1.0. SDWIS/eDV 1.0 supports the EPA Data
Verification process by automating the identification of violations and
comparing those violations to the Primacy Agency's database and the
SDWIS/ODS Quality Assurance (QA) Extract to identify discrepancies where
the mismatches occur. SDWIS/eDV 1.0 targets a SDWIS/STATE Web Release
1.0 database architecture.

## Document Overview

This document contains instructions for installing and administering
SDWIS/eDV 1.0. It is comprised of the following sections:

-   Section 1.0, Introduction, describes the content of the document,
    the software and hardware test environment, and user support
    procedures.

-   Section 2.0, Installation Procedures, describes the steps required
    to install SDWIS/eDV 1.0 as a web based application.

-   Section 3.0, Enabling External Access to the SDWIS/eDV Derby
    Database, describes tools and methods for using either a JDBC or
    ODBC database connection.

## Software and Hardware Test Environment

SDWIS/eDV 1.0 was tested using the hardware and supporting software
versions listed in Exhibit 1. More recent versions of the supporting
software may be available; the application, however, has not been tested
with these newer versions.

SDWIS/eDV software can reside on a server, or on a network‑connected
workstation configured as a stand-alone server.

SDWIS/eDV can interface with the site's SDWIS/State database to perform
the database extraction. To enable this, the product will need to have a
network connection to the database server containing the SDWIS/State
database. This is configured in the System Property file. Refer to the
SDWIS/eDV User Guide for instructions on how to use the software to
enable this connection.

+-----------+------------------+--------------------+-----------------+
| Machine   | Hardware         | System Software    | Application     |
| Type      |                  |                    | Software        |
+-----------+------------------+--------------------+-----------------+
| Client    | -   Dell         | MicrosoftWindows   | Internet        |
| Wo        |     OptiPlex     | XP Professional    | Explorer 6      |
| rkstation |     GX260        | Version 2002       |                 |
|           |                  | Service Pack 2     | Apache Derby    |
|           | -   Intel        |                    | 10.2.2.0        |
|           |     Pentium 4    |                    |                 |
|           |     CPU 3.06 GHz |                    | Kettle ETL 2.4  |
|           |                  |                    |                 |
|           | -   1 GB RAM     |                    | Jas             |
|           |                  |                    | perIntelligence |
|           |                  |                    | 1.2.1           |
|           |                  |                    |                 |
|           |                  |                    | Adobe 7.0       |
|           |                  |                    | and/or 8.1      |
|           |                  |                    | (Optional for   |
|           |                  |                    | r               |
|           |                  |                    | eading/printing |
|           |                  |                    | report pdf)     |
|           |                  |                    |                 |
|           |                  |                    | MS Excel 2003   |
|           |                  |                    | SP2 (Optional   |
|           |                  |                    | for use with    |
|           |                  |                    | SDWIS/eDV       |
|           |                  |                    | Report)         |
+-----------+------------------+--------------------+-----------------+

[]{#__RefHeading___Toc173657734 .anchor}**Exhibit 1.** SDWIS/eDV
Hardware and Software Test Environment

It is assumed that SDWIS/eDV will be run on a client workstation with at
least 1GB RAM.

All of the Application Software products are automatically installed by
the SDWIS/eDV Installation Package. Several of these require port
assignments on the workstation. The installation package will use the
following default port assignments:

  ----------------------------------- -----------------------------------
  **Port Number**                     **Product Assignment**

  1527                                Apache Derby Database

  1528                                JasperIntelligence Tomcat Startup

  1529                                JasperIntelligence Tomcat Shutdown

  1530                                MySQL
  ----------------------------------- -----------------------------------

[]{#__RefHeading___Toc173657735 .anchor}**Exhibit 2.** Default Software
Port Assignments

## User Support

You may call the SDWIS User Support Hotline at (703) 292‑6298 or e‑mail
SDWIS User Support at <sdwis@saic.com>. The hotline/e-mail account is
intended as a technical support tool for the operation and functionality
of SDWIS products such as SDWIS/eDV. A SDWIS team member answers calls
to the hotline between 8 a.m. and 5 p.m., on weekdays (except federal
holidays) and offers immediate user support when possible. During
evenings, weekends, or those times when the support personnel are
temporarily not available, callers may leave a detailed message.
Questions requiring the expertise of other team members, such as
developers or subject matter experts, are documented in the Events
Tracking System, forwarded to those individuals, and answered as soon as
possible.

# Installation Procedures

SDWIS/eDV is a web-based application accessed through web browsers. All
the components required for SDWIS/eDV are distributed within the
SDWIS/eDV installation package.

## Download SDWIS/eDV Installation Package

The first step is to download the SDWIS/eDV 1.0 Installation Package
(SDWIS_eDV-1.0.EXE) from the SDWIS web site
(<http://www.epa.gov/safewater/sdwis_st/current.html> ). You should also
download the *SDWIS/eDV 1.0 User Guide* and the *SDWIS/eDV 1.0 Release
Notes* from the documentation section.

## Pre-installation Check

If SDWIS/eDV is running on the workstation, shut it down before
re-install. Since SDWIS/eDV uses ports 1527, 1528, 1529, and 1530,
please ensure these ports are not in use. To identify which ports are in
use see Appendix A.

## Execute the SDWIS/eDV Installation Package

Double click on SDWIS_eDV-1.0.EXE to install SDWIS/eDV. On the Welcome
window, click Next.

![](media/image4.png){width="5.207638888888889in"
height="3.9680555555555554in"}

[]{#__RefHeading___Toc173657736 .anchor}**Exhibit 3.** SDWIS/eDV
Installation Welcome

During installation, an Installation window will display the
installation activity (Exhibit 4).

![](media/image5.png){width="5.208333333333333in"
height="3.9479166666666665in"}

[]{#__RefHeading___Toc173657737 .anchor}**Exhibit 4.** SDWIS/eDV
Installation Activity

When installation is complete, click Finish (Exhibit 5).

![](media/image6.png){width="5.208333333333333in"
height="3.9583333333333335in"}

[]{#__RefHeading___Toc173657738 .anchor}**Exhibit 5.** SDWIS/eDV
Installation Complete

The installation package will create a folder called C:\\Program
Files\\eDV, and a program group called SDWIS_eDV.

To ensure that installation was successful:

1.  Go to Start All Programs SDWIS_eDV Start eDV.

2.  Go to Start All Programs SDWIS_eDV Open eDV to be presented with the
    SDWIS/eDV Home Page (Exhibit 6).

![](media/image7.png){width="6.493055555555555in" height="3.74375in"}

[]{#__RefHeading___Toc173657739 .anchor}**Exhibit 6.** SDWIS/eDV Home
Page

3.  Click on the "eDV Reports" link, which presents the
    JasperIntelligence login page.

> ![](media/image8.png){width="6.492361111111111in"
> height="3.2527777777777778in"}

[]{#__RefHeading___Toc173657740 .anchor}**Exhibit 7.** JasperServer
Login Page

4.  Login to the JasperIntelligence tool using "edv" as the userid, and
    "edv" as the password, to bring up the SDWIS/eDV Report (Exhibit 7).

5.  Your installation is now complete.

![](media/image9.png){width="6.492361111111111in"
height="5.020833333333333in"}

[]{#__RefHeading___Toc173657741 .anchor}**Exhibit 8.** SDWIS/eDV Report

If you want to remove the SDWIS/eDV application:

1.  Go to Start All Programs SDWIS_eDV Shutdown eDV.

2.  Delete the C:\\Program Files\\eDV folder.

All software will be deleted and the port assignments released.

# Enabling external access to the sdwis/edv derby database

The SDWIS/eDV 1.0 data is stored in an Apache Derby Database. This is a
fully web-enabled database that is embedded in the SDWIS/eDV product.
There are two options outlined below to enable ad-hoc query capability
to the Derby Database.

Section 3.1 describes the use of a free product called SQuirreL-SQL that
uses a JDBC database connection. There are other products available
similar to SQuirreL-SQL which can be used. This product was chosen for
example purposes and is not a recommendation over any other product of
similar feature. Since Derby is a JDBC-compliant database, the JDBC
connection is more straightforward than the ODBC connection, described
in Section 3.2.

Since many users are very comfortable with MS Access, Section 3.2
describes how to enable an ODBC connection to Derby via MS Access. It
does require the additional installation of the IBM DB2 Client software
and is a bit more complicated that the JDBC connection option.

## SQUIRREL-SQL and a JDBC Connection

Download the "Install jars (and source) of SQuirreL x.x for
Windows/Linux/Others" from <http://www.squirrelsql.org/#installation>.

![](media/image10.png){width="6.492361111111111in"
height="5.020833333333333in"}

[]{.mark}

[]{#__RefHeading___Toc173657742 .anchor}**Exhibit 9.** SQuirreL-SQL
Download Site

On the Download and Installation page, download the
squirrel-sql-x.x-install.jar.

![](media/image11.png){width="6.495138888888889in" height="4.7125in"}

[]{#__RefHeading___Toc173657743 .anchor}**Exhibit 10.** SQuirreL-SQL
Download

Right click on the downloaded file and open it using the Java option.
Follow a series of installation screens and install SQuirreL-SQL client
using the default options.

For more instructions on how to install or use the SQuirreL-SQL utility
go to <http://www.squirrelsql.org/index.php?page=screenshots>.

To connect SQuirreL-SQL to the SDWIS/eDV Derby Database you must
configure the Apache Derby Client driver and alias as follows:

  ------------------- ------------------------------------------------------------------------
  Apache Derby Client C:\\Program
  Jar file            Files\\eDV\\apache-tomcat\\webapps\\eDV\\WEB-INF\\lib\\derbyclient.jar

  Alias name          SDWIS-EDV

  Driver              Apache Derby Client

  URL                 jdbc:derby://localhost:1527/EDVDB

  User Name           APP

  Password            APP
  ------------------- ------------------------------------------------------------------------

## MS Access and an ODBC Connection

In order to access SDWIS/eDV Derby Database using MS Access, you have to
first install IBM DB2 Client from:

<https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-dm-db2client>

You will need to register with IBM to get a userid and password, and use
that userid and password to download the IBM DB2 client. After
downloading the client, install the client using the default options.
After that, go to Start All Programs IBM DB2 Instance Name Command Line
Tools Command Line Processor, and run the following two commands:

1\. catalog tcpip node EDVNODE remote localhost server 1527

(Replace localhost with the machine name or IP Address of the server if
SDWIS/eDV is running on a server, and you are trying to connect to the
database from another machine)

2\. catalog db EDVDB at node EDVNODE authentication server

Open DB2 configuration assistant (Start All Programs IBM DB2 Instance
Name Set-up ToolsConfiguration Assistant), right click on EDVDB, click
on Bind.., click on Select All, and click Bind.

After you configure the DB2 client by following the above steps, you can
create an ODBC data source using the IBM DB2 ODBC Driver. To create an
ODBC data source for the SDWIS/eDV database:

1.  Go to StartControl Panel Administrative Tools Data Sources (ODBC).

2.  Click on the System DSN tab.

3.  Click on Add.

4.  Select the IBM DB2 ODBC Driver from the list of drivers.

5.  Click Finish.

6.  Type EDVDB in the data source name field.

7.  Select EDVDB from the Database Alias drop down list.

8.  Click OK.

After creating the ODBC data source, proceed to create an MS Access
database and create link tables from it to the SDWIS/eDV Derby Database
using the EDVDB ODBC data source. When connecting to the SDWIS/eDV
database using the ODBC data source, use the userid APP and password
APP.

This page is intentionally left blank

**Appendix A**

**Identifying Current Port Assignments**

Port Assignments

Run the command *netstat -ao \|find /i \"listening\"* in the command
window

 

Typical output is:

 

TCP    nairg-2T2WDC1:80       nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2064\
TCP    nairg-2T2WDC1:135      nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       992\
TCP    nairg-2T2WDC1:443      nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2064\
TCP    nairg-2T2WDC1:445      nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       4\
TCP    nairg-2T2WDC1:1074     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2252\
TCP    nairg-2T2WDC1:1521     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2208\
TCP    nairg-2T2WDC1:1527     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       6464\
TCP    nairg-2T2WDC1:1528     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       7908\
TCP    nairg-2T2WDC1:1530     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       3680\
TCP    nairg-2T2WDC1:2030     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       1164\
TCP    nairg-2T2WDC1:2100     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2208\
TCP    nairg-2T2WDC1:sapgw06  nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       356\
TCP    nairg-2T2WDC1:sapgw39  nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2064\
TCP    nairg-2T2WDC1:sapgw89  nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       904\
TCP    nairg-2T2WDC1:5800     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2608\
TCP    nairg-2T2WDC1:5900     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2608\
TCP    nairg-2T2WDC1:8009     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       7908\
TCP    nairg-2T2WDC1:8080     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2208\
TCP    nairg-2T2WDC1:8228     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2636\
TCP    nairg-2T2WDC1:1089     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2832\
TCP    nairg-2T2WDC1:1119     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2820\
TCP    nairg-2T2WDC1:1136     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       496\
TCP    nairg-2T2WDC1:1529     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       7908\
TCP    nairg-2T2WDC1:4664     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2260\
TCP    nairg-2T2WDC1:139      nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       4\
TCP    nairg-2T2WDC1:1748     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2144\
TCP    nairg-2T2WDC1:1754     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2144\
TCP    nairg-2T2WDC1:1808     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2144\
TCP    nairg-2T2WDC1:1809     nairg-2T2WDC1.sdc-moses.com:0 
LISTENING       2144

 

 

Lines marked in red indicated that (on the workstation nairg-2T2WDC1)
ports 1527, 1528, and 1530 are in use by processes 6464, 7908, and 3680.

To identify the exact process that is using a port, such as Port 1528 in
use by process 7908, open Windows Task Manager. The \"Image Name\" is
the program name, and PID is the process id.

 

![](media/image12.jpeg){width="4.208333333333333in"
height="4.739583333333333in"}

[]{#__RefHeading___Toc173657744 .anchor}**Exhibit 11.** Port Assignments