PacFIN Brief for 2003

[Complete 2003 PSMFC Annual Report (800 KB pdf)]

During 2003, development efforts in the PacFIN office focused upon: enhancement of the logbook and qsm systems, development of BDS system retrievals for stock assessment authors, changes related to the reconfiguration to the OFIS hardware system structure, participation in a coastwide biological data meeting, NMFS NWR retrievals were developed in support of the west coast vessel buyback program, modification of the limited-entry permit subsystem, standard reports on the PacFIN website were enhanced, W-O-C permit system development was finalized, the fusport table was created for the NMFS/ST1 office, participation in a HMS-PacFIN meeting, the NPFMC reports were enhanced and posted to the webpage, analysis and design for the fish-ticket (FT) and fish-ticket-line (FTL) catch categories was completed, data files were added to the suite of W-O-C All Species Reports, revisions were made to the PacFIN coast guard (CG) table, and numerous data requests were processed.

The PacFIN Office processed 191 data feeds from seven data sources and responded to 130 requests-for-information. There were 171,401 visits to the PacFIN website for an average of 14,283 visits per month. This compares to 9,719 per month in 2002 and 8,939 per month in 2001.

The NMFS/AFSC OFIS computer systems group made significant changes to the configuration of the Unix - Oracle system utilized by the PacFIN project. This reconfiguration included the installation of a separate Unix server (pacfinsun) and installation of an Oracle 9.2 database system running on pacfinsun. The new configuration requires PacFIN users to continue to connect to the existing AKC1 database system running on the ofis450a server. This configuration requires PacFIN users to access pacfin Oracle tables using SQL*NET. All members of the PacFIN office staff participated in extensive testing and resolution of bugs in conjunction with the OFIS database staff.

An enhancement for the WDFW portion of the LBK subsystem was started in February and completed in April. As a result of discussions before and during the November 2002 PCFDC meeting and requests made by stock assessment authors after November, WDFW agreed to enhance their LBK contribution to the PacFIN central database. These enhancements included adding tow ending positions starting with 2003, adding tow_date to each tow record, and providing more exact set position data.

Two enhancements to the QSM subsystem were incorporated during February through June. These included splitting the non-trawl sablefish catch into primary and daily trip limit (DTL) fisheries and reporting the WDFW tribal catches in addition to reporting them as part of the WDFW totals. These two expansions of the QSM system required a major effort involving all aspects of the subsystem.

The QSM file entry module was modified in order to conform to the recently installed dual Oracle server configuration. As an extension of this effort, this file entry module has been redesigned and reprogrammed such that all Oracle processing is consolidated on the PACFIN Oracle database server, eliminating any processing by this module on the AKC1 server.

Oracle SQL*Plus retrieval routines were developed at the request of stock assessment authors. One of these retrieval routines is slct_bds_sp_csv, which retrieves essentially all BDS data for a particular sampled species (i.e. market category) into a comma-delimited (csv) data file. Routine slct_bds_sp_csv is intended to be used by stock assessment authors and other investigators directly.

The NWR made a request for information about each west-coast vessel holding a limited-entry permit. This information was to be used as part of the federal buyback project. For each vessel, the average annual total ex-vessel revenue for groundfish, dungeness crab, and pink shrimp for the three highest revenue years between 1998 and 2001 was requested. The necessary routines were developed and made available to the NWR.

Reports #307-#310 were enhanced with new management group sub-groupings. Report #316 was also improved. A new report, #311, was developed that displays catch, revenue, and price data by gear group, management group, and complex.

The W-O-C permit subsystem development was finalized during March and the wocprmt_update program was added to the production system.

The WOC-OBS transaction project was also completed during this period and added to the production system. This project was proposed by the NMFS/NWFSC during an extensive meeting and called for the NWFSC to upload fish-ticket identifiers that correspond to observed fishing trips.

The NMFS/ST1 office requested the PacFIN office to provide another piece of the "Fisheries of the U.S." data previously provided by the NWR. This request was for a summary table containing total catch for all species and all fisheries combined, for each major port of landing. Some ports-of-landing are combined with adjacent ports in order to maintain confidentiality. This annual aggregate data is refreshed as part of the regular production processing and is stored in table fusport.

In addition to participating in the HMS-PacFIN meeting, some progress was made on tasks the PacFIN office agreed to accomplish so that the PacFIN central database will be able to provide greater utility to the HMS FMP process and future HMS management. This included adding a number of new PacFIN spid codes and some reorganization within and between management groups. Additionally, some progress has been made on developing the most efficient structure(s) for collecting, or organizing, species that will be managed or monitored by the HMS FMP.

The PacFIN office was notified that the new NWR limited-entry permit (LEP) Oracle table was now the official LEP data source. A new design was employed consisting of copying the NWR LEP data table, in its entirety, to a PacFIN Oracle table, replacing existing data, and archiving to a backup LEP table. All PacFIN programs were modified to reference PacFIN Oracle object NWR_LEP_SRC. NWR_LEP_SRC is an Oracle view, which was created so that system modifications could be minimized.

A number of requests were received for NWR support. Catch summations for the B-platoon for bi-weekly periods for the trawl gear-group were created. A SQL*Plus program to compute catch summations of open-access (OA) groundfish by vessels participating in non-groundfish fisheries was created, and another program was developed to determine the port-of-landing for each vessel in the list of 92 buy-back vessels.

A table of confidential indicators (CONFIND) was created and consists of columns: year, spid, arid, grid, pcid, agglvl, period, ft_cnt, ftl_cnt, drvid_cnt, processor_cnt, and trip_cnt. This table parallels the summary-catch table and consists of approximately 430,000 rows per year for ADFG.

A password-protected webpage was established on the PSMFC/PacFIN website and the standard suite of NPFMC reports for 1991-2003 were posted to this page. AKFIN, ADFG, AKR, and PSMFC staff were notified of the availability of these standard NPFMC reports via this method. During November and December the SQL*Plus programs that produce the standard NPFMC reports were enhanced such that non-confidential versions of these reports are produced in addition to the confidential versions. The reports are posted on the public webpage.

Table prin_cmplx was added to the system at the request of the NMFS/ST1 office. Routine insert_prin_cmplx was developed and added to the monthly production set of tasks. This non-confidential table includes the principal species-complex, species, and gear-type for each W-O-C vessel for 1981-2003.

The Merchant Vessels of the U.S. data files received from the USCG via NMFS/ST1 for the six months ending December 2002 and June 2003 required revisions to the PacFIN Coast Guard (CG)table before they could be processed. These revisions were completed in November and both data files were processed.

A set of comma delimited data files was added to the suite of W-O-C All Species Reports that are posted on the website and refreshed monthly. This additional posting is the result of numerous requests for catch information by county. Each annual data file includes non-confidential summations of catch and revenue for each county for all species combined, and for each species within each county. The data files for the 1981-2003 period are available on the PSMFC/PacFIN website.