PacFIN Brief for 2009

[Complete 2009 PSMFC Annual Report (8.52 MB pdf)]

The Pacific Fisheries Information Network (PacFIN) is the nation's first regional fisheries data network. Funded by a grant from the National Marine Fisheries Service (NMFS), PacFIN is a joint federal and state project focused on fisheries data collection and information management. PacFIN provides timely and accurate data to aid effective management of fisheries and fishery resources.

In 2009 the PacFIN Office processed 556 datafeeds from eight data sources and responded to at least 126 requests-for-information. Various selections of standard PacFIN reports were generated weekly or monthly and uploaded to the PSMFC and PacFIN website.

Following are highlights of PacFIN central office activities in 2009:

Quota Species Monitoring (QSM) Subsystem

A change to the QSM program adding arid "UI" (unknown area) to the QSM area group (VNCLEKMT) was implemented to accommodate lags in logbook area reporting. This enhancement assures inclusion of all landings where reporting is exclusive to this area group and individual area detail is not required. The QSM sub-system was also updated with Harvest Guidelines data for 2009 during this period.

Internal System Upgrades

Internal system upgrades related to maintaining the success of current production applications were performed. These activities are supportive in nature and included code list change monitoring, database performance improvements, new production automation and retrieval scripts, new documentation and metadata.

Oracle Database Support

Jointly contracted services were established during this period to provide database administration (DBA) support for the AKFIN/PacFIN projects, including: disaster recovery; Oracle software maintenance services to apply critical patches and database upgrades when necessary; database monitoring to include regularly scheduled checks of logs; backup procedures; and statistics and consultation regarding performance, upgrades and database tools and functionality.

System Server Upgrades

The PSMFC "prowfish" server in Portland was upgraded to the latest 10.2.0.4.0. release of Oracle.

PacFIN User Support

PacFIN staff continued to work with clients to establish accounts, grant table access and assist with data retrievals from the PacFIN prowfish server environment.

LE Trawl Permit Data Requests

PacFIN staff continued to work with representatives from WDFW, ODFW and CDFG using retrieval programs developed to allow them to respond to requests from individual Limited Entry Trawl permit holders for historical catch by permit. These requests relate to the ongoing PFMC effort to develop an Individual Trawl Quota (ITQ) system for the Limited Entry Trawl fleet.

Fisherman License data addition

Development work to obtain fisherman license data from Washington and California and include it in the PacFIN database was completed during this period. A new fisherman_license field was added to the Fish Ticket (FT) table. This task required modifications to various production programs and database tables and the source data specification. The FT table has been populated with historical data from WDFW and CDFG and both agencies are now providing this data item in their monthly datafeeds. The state of Oregon does not collect this data type and will therefore not participate.

At-Sea Whiting data acquisition

An additional field to match USCG vessel codes to ADFG vessel codes was added to a materialized view from the source NMFS/AFSC NORPAC database. Modifications to accommodate this change were made to PacFIN export/import data acquisition software as well as the PacFIN NPAC4900 destination table on the prowfish server. This data system allows PacFIN to report on the Pacific Coast At-Sea Pacific Whiting fishery.

Pacific Coast At-Sea Whiting Fishery data added to Federal Buy Back reporting

New software was developed during this period to include Pacific Coast At-Sea Whiting fishery landings data in the OMIBBFEE table in support of the Federal Buy Back program. Software development to estimate the value of the landings delivered to mother ships was initiated.

Federal Buy Back Program price reconciliation project

Work continued on some long standing price reconciliation issues between PacFIN dollar value estimates and buyer reports from the Federal Buy Back program. At issue is the difference in methodologies between price estimates to best value fisheries and price estimates to best estimate the actual cost to buyers involved in the Buy Back program. Work is continuing with PacFIN data providers to improve catch disposition coding in order to produce the best estimate possible of actual buyer costs in the targeted fisheries.

A secondary goal of this project is to identify discrepancies between PacFIN calculated revenues and Buyer calculated revenues that occurred which were not due to pricing. During this process, data errors were found and sent to the PacFIN data providers to be corrected. Following corrections by the source data providers, new datafeeds were received and processed. Also discovered were inclusions of catch where fees were not required and the exclusion of catch where fees were required. The script that populates the Buy Back program (OMIBBFEE) table was modified to account for these special circumstances. Other data issues, specific to state agency reporting protocols have also been addressed in an effort to provide the best available data in support of the Buy Back program.

PacFIN Web Site re-development

The project to re-develop the PacFIN website was completed during this period. All HTML code was brought up to W3C standards, follows the more stringent XHTML Transitional coding requirements and was verified using W3C online code checkers. The PHP programming language was utilized in conjunction with XHTML in order to increase efficiency and decrease the number of web pages that need to be maintained. Security has been increased and individual, password protected accounts have been created to allow for more directed access. The website has been launched for public use with good reviews. A website procedures document has been written to assist with its upkeep. Additional features are under development.

Web Access retrieval system upgrades

The Web Access system that allows approved users to extract data from the database using a web application was re-developed using SQL*Net. This task was driven by changes to the host web server and included the conversion of all extract html query forms and modifications to web access code lists and stored procedures. Development and testing of web-based retrievals using the Oracle Discoverer tool continued during this period.

Internal address/phone book database

A new phone contact list was developed using data from a decommissioned database, printed lists and current Outlook client contacts. Once the new list has been fully vetted, the phone list will be uploaded into Outlook and the mailing label database will be edited.

NPAFC annual reporting modifications

The program used to produce annual catch summaries for North Pacific Anadromous Fish Commission (NPAFC) reporting was enhanced to include a new reporting category for Sharks, Skates and Dogfish.

Internal procedures documentation

The PacFIN production "Data Procedures" document was re-written to update procedural changes and additions.

Cataloguing SQL scripts

A project to catalog and describe over 4000 Oracle SQL and PL/SQL scripts was initiated during this period. This effort was undertaken at the request of the Pacific Coast Fisheries Data Committee (PCFDC) from the November 2008 PCDFC meeting.

Training

The Data Management Specialist completed an HTML level two course through a local community college and continued independent study of the PHP programming language in support of PacFIN web design and maintenance. The Data Management Specialist has also started learning and working with the Oracle Discoverer tool.

Meetings and Seminars

The Program Manager met with the Senior Program Manager, the AKFIN Program Manager and other PSFMC staff, April 6-7 in Portland, OR, to coordinate server and database management activities and development. The Annual Pacific Coast Fisheries Data Committee (PCFDC) meeting was held November 9th and 10th in Portland, OR. PacFIN staff attended and presented.