Joseph Doherty
5a78ec5a76
Captures the working SQL Server connection state on this machine: server (localhost / MSSQLSERVER), database (Runtime), Windows-auth account (DESKTOP-6JL3KKO\dohertj2, sysadmin), the sqlcmd recipe, four paste-and-run sanity probes including an end-to-end INSQL retrieval, linked-server roles (INSQL, INSQLD), and alternate client paths (SSMS / PowerShell / ODBC / pyodbc). Linked from histdb/README.md Layout and Resource index per DOCS-GUIDE.md. Mirrors the pattern grdb/connectioninfo.md uses for its own DB. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
54 lines
5.1 KiB
Markdown
54 lines
5.1 KiB
Markdown
# 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.
|