# histdb

LLM-oriented reference for **AVEVA Historian** SQL retrieval — the Microsoft SQL Server linked-server (`INSQL`), the extension tables (`History`, `Live`, `WideHistory`, `AnalogSummaryHistory`, `StateSummaryHistory`, `Events`), the `wwXxx` time-domain extensions, every retrieval mode, and the Historian Data REST API. Distilled from the official *AVEVA Historian Retrieval Guide* (December 2020 edition, 243 pages).

## What this folder is for

Read-only reference. There's no code here — these files exist so an LLM can answer "how do I get data X out of Historian" without paging through the official PDF. For Galaxy / object configuration the right tool is [`graccesscli`](../graccesscli/README.md); for the System Platform Galaxy Repository (a different SQL database) it's [`grdb`](../grdb/README.md). This folder is specifically the **time-series / event store**.

## Hard constraints / things to know up front

- **Historian SQL access goes through a linked server named `INSQL`** that wraps the AVEVA OLE DB provider over the binary history blocks. The OLE DB provider is **stateless** — every query must repeat its `wwXxx` extension parameters.
- **A `WHERE` clause is mandatory** on every extension table except `HistoryBlock`. Date ranges (`DateTime >= ... AND DateTime <= ...`) plus tag selection (`TagName = ...` / `TagName IN (...)` / `TagName LIKE ...`) is the minimum.
- **All times are UTC internally; the wire format is local time** (or whatever `wwTimeZone` says). Time math uses Win32 `FILETIME` (100 ns resolution).
- **Wide tables (`WideHistory`) require `OPENQUERY`** — the schema is built per query. Up to 1024 columns. Query bodies inside `OPENQUERY` are capped at 8000 characters.
- **`IN` and `OR` cannot multiplex a `wwXxx` extension.** `wwVersion IN ('original','latest')` and `wwRetrievalMode = 'Delta' OR wwVersion = 'latest'` both fail. One value per extension per query.
- **The full PDF is canonical** — these summaries reduce 243 pages to the essentials. When in doubt, check the source: <https://cdn.logic-control.com/docs/aveva/historian/HistorianRetrieval.pdf>.

## Layout

```text
histdb/
  README.md                this file
  connectioninfo.md        Verified Runtime DB connection on this machine (server, sqlcmd recipe, sanity probes)
  01-overview.md           OLE DB provider, linked server, extension tables, four-part / OPENQUERY / OPENROWSET
  02-syntax-limits.md      Supported / unsupported SQL features, joins, sub-SELECTs, time-domain extensions overview
  03-retrieval-modes.md    Every wwRetrievalMode value (Cyclic, Delta, Full, Interpolated, BestFit, Average, Min, Max, Integral, Slope, Counter, ValueState, RoundTrip, Predictive, BoundingValue)
  04-retrieval-options.md  Every wwXxx parameter (CycleCount, Resolution, deadbands, Version, InterpolationType, TimeStampRule, TimeZone, QualityRule, StateCalc, Filter, ValueSelector)
  05-query-recipes.md      Practical SQL recipes (wide-table joins, INNER REMOTE JOIN, aggregate patterns, time-between-changes, gaps)
  06-alarms-events.md      Events table queries — listing events, alarm rates, time-to-clear, response-time analysis
  07-rest-api.md           Historian Data REST API — endpoints, $filter / OData syntax, auth, examples
```

## Resource index — by task

| Task | Go to |
| --- | --- |
| Connect from this machine — server, instance, `sqlcmd` recipe, sanity probes | [`connectioninfo.md`](connectioninfo.md) |
| Connect to Historian via SQL Server, pick a query style (four-part / view / OPENQUERY / OPENROWSET) | [`01-overview.md`](01-overview.md) |
| What SQL syntax works and what doesn't (LIKE, IN, OR, joins, CONVERT, sub-SELECTs) | [`02-syntax-limits.md`](02-syntax-limits.md) |
| Pick the right retrieval mode for the question being asked | [`03-retrieval-modes.md`](03-retrieval-modes.md) |
| Tune what the mode returns (cycle count, resolution, deadbands, quality rule, value selector, time zone) | [`04-retrieval-options.md`](04-retrieval-options.md) |
| Worked SQL recipes for common questions (wide tables, aggregates, gaps, GROUP BY) | [`05-query-recipes.md`](05-query-recipes.md) |
| List events / count alarms / measure time-to-acknowledge | [`06-alarms-events.md`](06-alarms-events.md) |
| Pull data from a browser, Excel, Power BI, or any HTTP client (no SQL needed) | [`07-rest-api.md`](07-rest-api.md) |
| Original PDF — definitive source for any uncovered detail | <https://cdn.logic-control.com/docs/aveva/historian/HistorianRetrieval.pdf> |

## Source

Source PDF: *AVEVA Historian Retrieval Guide* (formerly Wonderware), publication date 3 December 2020, downloaded from `cdn.logic-control.com/docs/aveva/historian/HistorianRetrieval.pdf`. The guide describes Historian retrieval; companion volumes (administration, database reference, glossary) are *not* covered here — see references inside each deep doc.

## Maintenance

This folder follows the doctrine in [`../DOCS-GUIDE.md`](../DOCS-GUIDE.md). When AVEVA ships a new Retrieval Guide that adds modes, options, or REST endpoints, update the affected deep doc(s) and bump any new entries into the Resource index above. The root [`../CLAUDE.md`](../CLAUDE.md) holds one row pointing here — don't fan out per-file links from the root.