# Pharmacy StockHistory Archival Archiving old StockHistory records keeps the active `STOCKHISTORY` table lean and fast while preserving full history in a separate `STOCKHISTORYARCHIVE` table. Archived rows are still queryable through reports that have the **Include Archived** option. --- ## Who Can Run Archival | Privilege | Grants Access To | |---|---| | `ArchiveOldStockHistory` | StockHistory archive page | | `ArchiveOldItemBatch` | ItemBatch archive page | Assign these privileges in **User Management → Privileges** before the first run. --- ## How to Navigate to the Archive Page 1. Log in as a system administrator. 2. Go to **Administration → Manage Metadata**. 3. Open the **Data** tab and click **Archive StockHistory**. The archive settings page opens. --- ## Archive Settings | Field | Description | Default | |---|---|---| | **Retention Period (days)** | Rows created more than this many days ago are eligible for archival. | 730 (2 years) | | **Cutoff Date** | Calculated automatically from retention days. Rows created *before* this date will be archived. You can also set it directly. | 2 years ago | | **Batch Size** | Number of rows moved per database transaction. Smaller batches reduce lock contention on busy systems. | 2 000 | | **Max Batches Per Run** | Safety limit on how many batches a single run will process. Prevents runaway jobs. | 50 | | **Dry Run** | When checked, only counts eligible rows — no data is moved. Always run a dry run first. | Checked | ### Changing the Retention Period 1. Enter the number of days in **Retention Period (days)**. 2. Click **Apply** — the **Cutoff Date** field updates automatically. 3. Adjust the cutoff date further if needed. --- ## Step-by-Step: Running an Archive ### 1. Preview (Dry Run) 1. Set **Retention Period** and click **Apply** to confirm the cutoff date. 2. Ensure **Dry Run** is checked. 3. Click **Preview Count** — the page shows how many rows are eligible. 4. Review the count. If it looks reasonable, proceed. ### 2. Live Run 1. Uncheck **Dry Run**. 2. Set **Batch Size** and **Max Batches Per Run** appropriate for your load: - For off-peak maintenance: Batch Size 5 000, Max Batches 200. - For live-hours: Batch Size 1 000, Max Batches 20 (run repeatedly). 3. Click **Run**. 4. Confirm the prompt ("Run archive now? …"). 5. The **Last Result** panel shows rows archived, batches run, duration, and whether the batch limit was reached. ### 3. If the Batch Limit Is Reached The result panel shows **Reached Batch Limit: Yes**. The job stopped before all eligible rows were moved. Simply click **Run** again — it will continue from where it left off until all eligible rows are moved or the limit is reached again. --- ## Understanding the Result Panel | Field | Meaning | |---|---| | Mode | Dry run or Live run | | Candidates | Total rows eligible for archival (based on the cutoff date) | | Archived | Rows actually moved in this run | | Batches Run | Number of batches executed | | Reached Batch Limit | Yes = more rows remain; run again | | Started / Ended | Wall-clock timestamps | | Duration (ms) | Total time for the run | | Message | Summary text | --- ## Viewing Archived Records in Reports Reports that support archived data have an **Include Archived** checkbox. When ticked, the report queries both the live table and the archive table and merges the results. Reports with Include Archived support: - **Stock Ledger (DTO)** — `reports/inventoryReports/stock_ledger_dto.xhtml` - **Closing Stock Report** — `reports/inventoryReports/closing_stock_report.xhtml` - **Department Stock History** — `pharmacy/pharmacy_department_stock_history.xhtml` - **Bin Card (DTO)** — `pharmacy/bin_card_dto.xhtml` Leave the checkbox **unticked** for day-to-day reporting; tick it only when you need historical data beyond the retention window. --- ## Database Migration (v2.7.0) The v2.7.0 migration script adds three composite indexes to `STOCKHISTORYARCHIVE` that match the query patterns used by archive-inclusive reports: | Index | Columns | |---|---| | `idx_stockhistoryarchive_batch_date_retired` | `ITEMBATCH_ID, CREATEDAT, RETIRED` | | `idx_stockhistoryarchive_date_dept_retired` | `CREATEDAT, DEPARTMENT_ID, RETIRED` | | `idx_stockhistoryarchive_historytype_dept_createdat` | `HISTORYTYPE, DEPARTMENT_ID, CREATEDAT` | The script is idempotent — safe to run multiple times. Run it through the normal database migration process. --- ## Recommended Maintenance Schedule | Frequency | Action | |---|---| | Monthly | Run with Dry Run to review candidate count | | Quarterly | Run live archive during off-peak hours | | After each run | Confirm Reached Batch Limit = No (or re-run if Yes) | --- ## Troubleshooting **"Archive run failed" error** Check the Payara server log (`server.log`) for the full stack trace. Common causes: - Database connection timeout on a very large batch — reduce Batch Size. - Insufficient database privileges for the JDBC pool user. **Cutoff date did not change after editing Retention Period** Click the **Apply** button next to the Retention Period field. Simply typing a new value and pressing Tab does not recalculate the cutoff — you must click Apply. **Preview count is 0 but you expect archived rows** Archived rows are already in `STOCKHISTORYARCHIVE`. The preview counts rows still in `STOCKHISTORY` that are eligible to be moved. If count is 0, all eligible rows have already been archived. --- [Back to Pharmacy Administration](https://github.com/hmislk/hmis/wiki/Pharmacy-Administration)