# AI for Epicor 9 and Vantage on Progress OpenEdge: A Private Architecture > Source: https://netray.co/erp-help/private-ai-layer-epicor-9-vantage-progress-architecture > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer Epicor 9 and Vantage store data in a Progress OpenEdge database, which can be read through the OpenEdge SQL-92 engine over ODBC or JDBC. The practical design replicates the data to a SQL Server or similar reporting copy, indexes documents locally, serves an open-weight model on your own GPUs, and keeps the legacy system read-only. ## Kind advanced ## System epicor ## Erp Epicor 9 and Vantage (Progress OpenEdge) ## Module Integration / OpenEdge SQL / Service Connect ## Query Epicor 9 AI integration Progress database ## Alternate Queries - how to query Epicor 9 database with AI - Epicor Vantage OpenEdge ODBC JDBC read only - Epicor 9.05 chat with data on-prem - private AI for legacy Epicor Progress ## Applies To Epicor 9.05 and earlier Vantage releases on Progress OpenEdge, Windows application servers; exact driver and table names depend on your release and data dictionary ## Steps ### How to add a private AI layer to Epicor 9 or Vantage **Items:** - Confirm the Progress OpenEdge version and whether the SQL-92 broker is running. SQL access is a separate service from the 4GL application broker and must be enabled on the database server. - Create a read-only database user for SQL access and set the SQL width for string columns, because OpenEdge SQL truncates data to the declared SQL width and the default can cut descriptions and comments. - Install the OpenEdge ODBC or JDBC driver on a dedicated integration server and test with a single query against PUB.JobHead and PUB.PartTran before building anything. - Decide the read model. For light use, query the OpenEdge SQL engine directly off-hours. For anything heavier, replicate tables to SQL Server or PostgreSQL (OpenEdge replication tools, Progress Pro2, or a scheduled extract). - Extract documents and attachments from the file shares your install uses, embed them locally and tag each chunk with company and plant. - Expose a small set of parameterised read actions through a tool server. The model never holds database credentials. - Serve a local open-weight model with vLLM or Ollama and log every call to an append-only audit store. - Keep the system read-only. If a write is truly needed, route it through Epicor's supported service layer after human approval, not through SQL. ## Sections ### Data access on a Progress OpenEdge database **Body:** Epicor 9 and Vantage tables such as JobHead, JobMtl, JobOper, PartTran, Part, PartBin, OrderHed, OrderDtl, POHeader and PODetail live in the PUB schema of the OpenEdge database. The OpenEdge SQL-92 engine lets standard SQL tools read them through ODBC or JDBC, which is the only way to reach the data without going through the application. Two OpenEdge-specific details bite integrations. First, SQL width: if the database was not prepared with adequate widths, long text fields return truncated. Second, the SQL engine and the 4GL runtime take their own locks and statistics, so a heavy query on the live database can degrade the application. Keep analytics off the live database in business hours. Epicor 9 also has its own application services and Service Connect for message-style integration. Native REST arrived only in later products, so on 9 expect SQL for reads and Service Connect or the service layer for any sanctioned writes. **Code:** - -- OpenEdge SQL-92, read only SELECT TOP 50 jh.JobNum, jh.PartNum, jh.ProdQty, jh.ReqDueDate FROM PUB.JobHead jh WHERE jh.Company = 'EPIC03' AND jh.JobClosed = 0 ORDER BY jh.ReqDueDate - jdbc:datadirect:openedge://dbhost:20931;databaseName=mfgsys;user=ai_readonly ### Replication, retrieval and model serving **Body:** For a legacy Progress system, a replica does more than protect performance: it lets you add indexes, flatten tables into reporting views and keep a history that the live database prunes. Progress documents replication options for OpenEdge, and the Pro2 product replicates OpenEdge tables into SQL Server or Oracle. A nightly or hourly extract is often sufficient for planning, quoting and inventory questions. Build the retrieval index from that copy and from documents on file shares: travelers, drawings, certificates and SOPs. Store source, company, plant and access tags with every chunk. Serve the model with vLLM or Ollama on local GPUs behind a gateway that writes an audit record per call. An air-gapped option is straightforward because the stack never needs outbound connectivity: stage weights and containers once, pin versions, and run embeddings locally. Many sites with 9 are on isolated networks already, and this design leaves that posture intact. ### Permissions and write-back **Body:** Epicor 9 security is held in the application (security groups, menu access, company and plant rules), not in the database. A SQL read user sees everything it is granted, so the permission logic has to live in your tool server. Maintain a mapping from Epicor users or groups to allowed companies, plants and actions, and have the tool server inject those filters into every query. Authenticate users to the assistant against Active Directory, map them to that table, and refuse any request that does not resolve. Apply the same tags as retrieval filters on documents. Treat financial and costing data as a separate permission class and default it to denied. Write-back is the area to be most cautious on a legacy release. Use only supported service layer or Service Connect paths, put a person in the loop, and consider leaving writes out until after any planned migration. ### Sizing and honest limits **Body:** Sizing mirrors other ERPs: most 9 and Vantage sites with 30 to 200 named users fit on a single 24 GB GPU running a 7B to 14B parameter model, with a modest vector store and an hourly or nightly replica. The load driver is peak concurrent requests, so measure them in a pilot. The limits are real. Epicor 9 is out of mainstream support, so the database and OS around it may be old, and SQL width or schema oddities will cost time. Business logic hidden in customisations or BPM may not be visible to SQL, so a number the assistant computes can differ from the one the screen shows. Always show the query and let users compare. If the real goal is leaving Epicor 9, a migration project with data profiling may deliver more than an assistant on top of it. A good compromise is an assistant that also documents the data model you will need to map. ## Pitfalls - Running heavy model-driven queries on the live OpenEdge database during shifts. - Ignoring SQL width, so descriptions and comments come back truncated and the model reasons over partial text. - Granting a SQL user broad read access with no per-user filtering in the tool layer. - Trusting totals that depend on customisation or BPM logic that SQL cannot see. - Attempting writes by SQL against a Progress database, bypassing application rules. ## Ai Angle ERPray can be pointed at a replicated copy of an Epicor 9 or Vantage database and used on-prem to answer inventory, job and order questions, with the query shown so users can check it against the screen. It also helps map legacy tables ahead of a migration, which is where many 9 sites end up anyway. ## Faq ### Can I query an Epicor 9 database directly with SQL? **Answer:** Yes, through the Progress OpenEdge SQL-92 engine over ODBC or JDBC, using a read-only user. Check SQL width settings, avoid heavy queries on the live database, and prefer a replicated copy for analytics and AI workloads. ### Does Epicor 9 have a REST API? **Answer:** Not in the way Kinetic does. Epicor 9 relies on its application services and Service Connect for integration. For an AI assistant, plan on SQL for reads and supported service paths for any approved writes. ### Is it worth adding AI to Epicor 9 instead of upgrading? **Answer:** Sometimes. A read-only assistant can remove reporting backlog quickly and the replica and document index carry over to a new system. If an upgrade is already funded, align the two projects so the data mapping serves both. ### How do I keep data on site? **Answer:** Run the replica, the vector store and the model server on your own hardware, with no outbound calls from the stack. Open-weight models served by vLLM or Ollama make this possible, including on air-gapped networks. ## Search terms Epicor 9 AI integration, Epicor Vantage database query, Epicor 9 OpenEdge ODBC, Epicor 9.05 reporting, Progress OpenEdge SQL read only, private LLM legacy ERP, Epicor 9 chat with data, Pro2 replication Epicor --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json