Overview
The CWMS database is a zone provided for local Corps offices to store time series and relational data used in modeling CWMS watersheds. Oracle was chosen as the database facilitator because it is a widely used database and follows the corporate guidelines for USACE. The CWMS system provides a variety of ways to interact with the CWMS database, including PL/SQL, the CWMS API, and various application programs. The CWMS database schema also stores procedures used to write and receive data to the database. Updates to the procedures and database information to fix bugs and provide added functionality are accomplished through changes to the CWMS schema (CWMS_20). Schema updates are created and tested at HEC and then distributed to districts databases.
Accomplishments
Minor Improvements and Field Support
The data team responded to numerous database issues and distributed corrections to the field with two full database schema updates.
Performance improvements were made to the database for retrieving time series records. Improvements in CWMS-Vue tab loading were also implemented to make CWMS-Vue loading faster. Further improvements for the database were prioritized for future database work.
Goldengate processes and monitoring were removed from the CPC and migrated to CWBI. All national products were removed from the DMZ and CPC. Goldengate issues were mitigated as needed to keep data replicating to the national database in CWBI production for access to water as well as to CWBI test for cloud migration testing.
Data Acquisition and National Database Administration
Data acquisition processes were developed including grabbing and storing USGS ratings, grabbing and storing USGS measurements, grabbing and storing SHEF data all using CDA. Tools were developed to migrate existing configuration files for rating imports and SHEF file imports into the database objects for migrating to cloud processes. Airflow was setup in CWBI development, test, and production environment. Enterprise processes were configured and setup to grab data from the USGS, and through LDM into the national database orchestrated by Airflow. Airflow was integrated with LDM to process SHEF files directly into the CWBI databases. Assistance was given to district to migrate data acquisition scripts to python from jython.
Authorization for Data Access
A contract was developed and awarded to a contractor for the new authorization scheme. Workplans, scope of work, and budgets were developed. Work began with status meetings with the contractor that continues into FY26.
Migration of Historical Public Data to CWBI
Processes were developed to migrate historical data from district databased into the national CWBI databases. All historical data that was present in district databases for timeseries that were designated as public (DMZ include list) were migrated to the CWBI production database and are available to access to water.
Investigate Moving Current Build System to Flyway
Continued progress was made toward migrating our database schema update process away from single update scripts to a more CI/CD process using flyaway. This progression will continue in FY26.
Host Workshops and Webinars
Monthly data acquisition working sessions were conducted to assist districts in migrating data acquisition script to python using cwms-python. Numerous topics were discussed and presented during these working sessions to inform the field on new cloud based data acquisition processes. These include LDM, USGS rating and measurements scripts, SHEF processing among others. Preparation was also made for an in person 3 day data acquisition workshop that was supposed to take place in October, but was suspended and will likely be held virtually in FY26.