High Plains Water-Level Monitoring Study
Documentation: Maintaining the High Plains alternate database on the Nebraska Server

Information on maintaining the High Plains alternate database using the NWIS wat_get, wat_put, and wat_purge programs.

High Plains alternate database

The alternate database was set up by the NWIS database administrator and others within the Nebraska NWIS database. Peolple involved in the process included Dwain Curtis, Kurt Schultz, Jim Cornwall, and Dale Hollway. They set up two alternate databases: DB42, for data through 2006, data through 2008, etc. and DB43, for data through 2007, data through 2009 data, etc. The populated database from the previous year is available while the current year database is being created; the previous year's database will be initialized after the current datebase is validated.

Programs used to populated the High Plains alternate database

NWIS's wat_get and wat_put programs will be used to populate the alternate database.

• In brief, the process is to ssh over to the source server (in UNIX), run wat_get on the source nwis database, and sftp the results to Nebraska's server--so this is running wat_get at least 8 times--once for each state. Then, in UNIX on the Nebraska server, to run wat_put to load the data from the source nwis database into the alternate database (db42 or db43).
• wat_get retrieves the data specified in the file_list file from gwsi db01 for the sites in the site file (for example: CoSiteList) on the respective State's source server.
• wat_put loads the wat_get data into the specified gwsi database (for example db42 or db43, whichever is appropriate) on Nebraska's server
• wat_purge can be used to delete data in the alternate database; however, because wat_purge takes a very long time to run, we initailized the appropriate database (db42 or db43) prior to starting the refresh process.

Prior to running wat_get and wat_put

Before starting wat_get/wat_put jobs, set up the following directories on the Nebraska server for each state

• /luey/9N000/altdb42/Colorado/co_watmove/COWL and a data subdirectory
• /luey/9N000/altdb42/Kansas/ks_watmove/KSWL and a data subdirectory
• /luey/9N000/altdb42/Nebraska/ne_watmove/NEWL and a data subdirectory
• /luey/9N000/altdb42/NewMexico/nm_watmove/NMWL and a data subdirectory
• /luey/9N000/altdb42/Oklahoma/ok_watmove/OKWL and a data subdirectory
• /luey/9N000/altdb42/SouthDakota/sd_watmove/SDWL and a data subdirectory
• /luey/9N000/altdb42/Texas/tx_watmove/TXWL and a data subdirectory
• /luey/9N000/altdb42/Wyoming/wy_watmove/WYWL and a data subdirectory

Work on the appropriate State server with the source nwis database

The source nwis database are nwis DB01 on the following servers:

• Colorado -- nwisdcolka.cr.usgs.gov
• Kansas -- nwisdkslwr.cr.usgs.gov
• Nebraska -- readdnelnc.cr.usgs.gov
• New Mexico -- pyrite.cr.usgs.gov
• Oklahoma -- kendokokl.cr.usgs.gov
• South Dakota -- v08dsdhrn.cr.usgs.gov
• Texas -- sun1ast.cr.usgs.gov
• Wyoming -- srv1dwychy.cr.usgs.gov

The alternate database servers are db42 and db43 on the Nebraska server

On each of the source servers, set up a data directory under your home directory in your home directory on the source server

UNIX: ssh to the source server in your home directory
For example: ssh nwisdcolka.cr.usgs.gov

UNIX: make a data directory under the main directory

mkdir data

UNIX: make a site file(s) for the retrievals The following have been recommended:

1. to limit the site file to less than 500 sites for wat_get and wat_put,
2. to run wat_get and wat_put after hours to lessen performance effects on others using gwsi,
3. to not run wat_get and wat_put during the nightly database sync (scema editor) process.
4. Track the available space in the jrnl area for the Nebraska server--to make sure the jrnl are does not fill up.
   When the jrnl area is full, database transactions can be lost, meaning if the database crashes, the database since the last backup cannot be recovered.
5. To check the size of the jrnl are on the Nebraska database, enter df -k /usr/opt/ingres/jnl
6. If the jrnl area fill up, ask the nwis db administrator to run backup the database

Note: In practice, have had up to 1500 sites in site file and because the nightly database sync is not run each night on the Nebraska server. If the database sync had run nightly, a large job updating the database coul ???????????????

UNIX: Used scripts to split up the Site file into manageable chunks and make/run wat_ger/wat_put jobs for each site file

To run wat_get

Files to run wat_get

wat_get File01: The file_list file are the tables to be retrieved for the alternate database--these files are set-up when the data base in initialized

  Y SITE Site File
  Y ADDC ADAPS datum corrections file
  Y ADDD ADAPS data descriptor file
  Y ADDV ADAPS daily values file
  Y ADIN ADAPS instruments file
  Y ADMS ADAPS measurements file
  Y ADPK ADAPS peak flow file
  Y ADPR ADAPS processor file
  Y ADRT ADAPS ratings file
  Y ADSV ADAPS shifts-by-stage file
  N ADUV ADAPS unit values file
  Y ADSS ADAPS statistics file
  Y GWCO GWSI construction file
  Y GWDI GWSI discharge file
  Y GWGE GWSI geologic logs file
  Y GWHY GWSI hydraulics file
  Y GWLE GWSI water levels file
  Y GWMI GWSI miscellaneous file
  Y GWOB GWSI observation hdgs file
  N GWST GWSI state water use file
  N QW QW File

wat_get File02: site file A list of sites as C004 (agency) C001 (site id).

wat_get File03: edit_list file This file was not used and is not needed.

wat_get Configuration file -- gives run instructions to wat_get
An example wat_get.cfg file for Nebraska gwsi

  #! /bin/sh
  #
  #  wat_get.cfg
  #
  #  Configuration file for wat_get program
  #
  #  Notes:  The variable names must be in upper-case.
  #          No space is allowed on either side of the equals sign.
  #
  #  The variables are defined as follows:
  #    DIS_NODE        :  DIS node ident (e.g., qvarsa)
  #    DBNUM           :  Data base number for retrieval
  #    20050101      :  Start date (yyyymmdd) for ADAPS UV retrieval,
  #                       NULL ('') => earliest date available
  #    END_DATE        :  End date for UV retrieval, NULL => current date
  #    SITE_LIST_PATH  :  Path name of site-list file
  #    FILE_LIST_PATH  :  Path name of file-list file
  #    EDIT_LIST_PATH  :  Path name of edit-list file, NULL if not used
  #    XFR_DIR         :  Path name of transfer directory
  #    MODE            :  Processing mode, W to write tran files, C to count only
  #    FTP_DEST        :  IP address of destination machine for ftp transfer
  #    FTP_USER        :  User ID on destination machine
  #    FTP_PW          :  User password on destination machine
  #    FTP_PATH        :  Pathname of directory on destination machine
  #
  #  Modify the following lines as necessary:
  #----------------------------------------------------------------------------
  DIS_NODE=dnelnc
  DBNUM=01
  START_DATE=''
  END_DATE=''
                              SITE_LIST_PATH=/luey/9N000/altdb42/Nebraska/ne_watmove/NEWL/4watmove_missing_wells_nebraska.txt
  FILE_LIST_PATH=/luey/9N000/altdb42/Nebraska/ne_watmove/NEWL/file_list
  EDIT_LIST_PATH=''
  XFR_DIR=/luey/hp05/9N000/altdb42/Nebraska/ne_watmove/NEWL/data
  MODE=W
  FTP_DEST=XXXXXX
  FTP_USER=anonymous
  FTP_PW=wat_move
  FTP_PATH=incoming/wat_move/data

An example of a wat_get.cfg file for Colorado gwsi

  #!/bin/sh
  #
  #  wat_get.cfg
  #
  #  Configuration file for wat_get program
  #
  #  Notes:  The variable names must be in upper-case.
  #          No space is allowed on either side of the equals sign.
  #
  #  The variables are defined as follows:
  #    DIS_NODE        :  DIS node ident (e.g., qvarsa)
  #    DBNUM           :  Data base number for retrieval
  #    20050101      :  Start date (yyyymmdd) for ADAPS UV retrieval,
  #                       NULL ('') => earliest date available
  #    END_DATE        :  End date for UV retrieval, NULL => current date
  #    SITE_LIST_PATH  :  Path name of site-list file
  #    FILE_LIST_PATH  :  Path name of file-list file
  #    EDIT_LIST_PATH  :  Path name of edit-list file, NULL if not used
  #    XFR_DIR         :  Path name of transfer directory
  #    MODE            :  Processing mode, W to write tran files, C to count only
  #    FTP_DEST        :  IP address of destination machine for ftp transfer
  #    FTP_USER        :  User ID on destination machine
  #    FTP_PW          :  User password on destination machine
  #    FTP_PATH        :  Pathname of directory on destination machine
  #
  #  Modify the following lines as necessary:
  #----------------------------------------------------------------------------
  DIS_NODE=dcolka
  DBNUM=01
  START_DATE=''
  END_DATE=''
  SITE_LIST_PATH=./CoSiteList
  FILE_LIST_PATH=./file_list
  EDIT_LIST_PATH=''
  XFR_DIR=./data
  MODE=W
  FTP_DEST=XXXXXX
  FTP_USER=anonymous
  FTP_PW=wat_move
  FTP_PATH=incoming/wat_move/data	

UNIX: to run wat_get

On the source server, in the same directory with the wat_get.cfg and related files (site_list, file_list), and in the ./data parent directiory

wat_get > log_yyyymmdd_wat_get_State.txt

UNIX: after the wat_get job is completed:
sftp the data directory to appropriate state directory on the Nebraska server.

The appropriate state directory on the Nebraska server for the wat_get results are:

  /luey/9N000/altdb42/Kansas/ks_watmove/KSWL
  /luey/9N000/altdb42/Nebraska/ne_watmove/NEWL
  /luey/9N000/altdb42/NewMexico/nm_watmove/NMWL
  /luey/9N000/altdb42/Oklahoma/ok_watmove/OKWL
  /luey/9N000/altdb42/SouthDakota/sd_watmove/SDWL
  /luey/9N000/altdb42/Texas/tx_watmove/TXWL
  /luey/9N000/altdb42/Wyoming/wy_watmove/WYWL   

To run wat_put

files to run wat_put

wat_put File01: The file_list file are the tables to be loaded into the alternate database.

  Y  SITE  Site File
  Y  ADDC  ADAPS datum corrections file
  Y  ADDD  ADAPS data descriptor file
  Y  ADDV  ADAPS daily values file
  Y  ADIN  ADAPS instruments file
  Y  ADMS  ADAPS measurements file
  Y  ADPK  ADAPS peak flow file
  Y  ADPR  ADAPS processor file
  Y  ADRT  ADAPS ratings file
  Y  ADSV  ADAPS shifts-by-stage file
  N  ADUV  ADAPS unit values file
  Y  ADSS  ADAPS statistics file
  Y  GWCO  GWSI construction file
  Y  GWDI  GWSI discharge file
  Y  GWGE  GWSI geologic logs file
  Y  GWHY  GWSI hydraulics file
  Y  GWLE  GWSI water levels file
  Y  GWMI  GWSI miscellaneous file
  Y  GWOB  GWSI observation hdgs file
  N  GWST  GWSI state water use file
  N  QW    QW File

wat_put File02: the node_list File, with the server name:
dnelnc

wat_put configuration File (wat_put.cfg)

  #! /bin/sh
  #
  #  wat_put.cfg
  #
  #  Configuration file for wat_put program
  #
  #  Notes:  The variable names must be in upper-case.
  #          No space is allowed on either side of the equals sign.
  #
  #  The variables are defined as follows:
  #    DBNUM           :  Data base number for retrieval
  #    NODE_LIST_PATH  :  Path name of node-list file
  #    FILE_LIST_PATH  :  Path name of file-list file
  #    EDIT_LIST_PATH  :  Path name of edit-list file, NULL ('') if not used
  #    RCV_DIR         :  Path name of transfer directory
  #
  #  Modify the following lines as necessary:
  #----------------------------------------------------------------------------
  DBNUM=43
  NODE_LIST_PATH=/luey/9N000/altdb42/Nebraska/ne_watmove/NEWL/node_list
  FILE_LIST_PATH=/luey/9N000/altdb42/Nebraska/ne_watmove/NEWL/file_list
  EDIT_LIST_PATH=''
  RCV_DIR=/luey/9N000/altdb42/Nebraska/ne_watmove/NEWL/data

To run wat_purge

Note: only run wat_purget if wells were loaded that were not intended to be loaded

To run wat_purge:
Go to the directory with the wat_purge.cfg, file_list, and site file
Enter only wat_purge (don't enter: wat_purge > log_file because wat_purge asks a question during the run and will hang waiting for you to answer the question, can use a script that answers the question)

files to run wat_purge
wat_purge File01: The file_list file are the tables to be erased in the alternate database.

  Y  SITE  Site File
  Y  ADDC  ADAPS datum corrections file
  Y  ADDD  ADAPS data descriptor file
  Y  ADDV  ADAPS daily values file
  Y  ADIN  ADAPS instruments file
  Y  ADMS  ADAPS measurements file
  Y  ADPK  ADAPS peak flow file
  Y  ADPR  ADAPS processor file
  Y  ADRT  ADAPS ratings file
  Y  ADSV  ADAPS shifts-by-stage file
  N  ADUV  ADAPS unit values file
  Y  ADSS  ADAPS statistics file
  Y  GWCO  GWSI construction file
  Y  GWDI  GWSI discharge file
  Y  GWGE  GWSI geologic logs file
  Y  GWHY  GWSI hydraulics file
  Y  GWLE  GWSI water levels file
  Y  GWMI  GWSI miscellaneous file
  Y  GWOB  GWSI observation hdgs file
  N  GWST  GWSI state water use file
  N  QW    QW File

wat_put configuration File (wat_purge.cfg)

  #! /bin/sh
  #
  #  wat_purge.cfg
  #
  #  Configuration file for wat_purge program
  #
  #  Notes:  The variable names must be in upper-case.
  #          No space is allowed on either side of the equals sign.
  #
  #  The variables are defined as follows:
  #    DBNUM           :  Data base number for retrieval
  #    START_DATE      :  Start date (yyyymmdd) for ADAPS UV deletions,
  #                       NULL ('') => earliest date available
  #    END_DATE        :  End date, NULL => current
  #    SITE_LIST_PATH  :  Path name of site-list file
  #    FILE_LIST_PATH  :  Path name of file-list file
  #    EDIT_LIST_PATH  :  Path name of edit-list file, NULL if not used
  #
  #  Modify the following lines as necessary:
  #----------------------------------------------------------------------------
  DBNUM=43
  START_DATE=''
  END_DATE=''
  SITE_LIST_PATH=/luey/9N000/altdb42/Texas/tx_watmove/TXWL/txsites.dat
  FILE_LIST_PATH=/luey/9N000/altdb42/Texas/tx_watmove/TXWL/file_list
  EDIT_LIST_PATH=''