What options exist to query RMS files in OpenVMS? The context for the query/access would be for BI & reporting. Currently, a very old FOCUS (Infomation Builders, v. 6.9.8) is in use, and that only from within the native OpenVMS command line shell.
My challenge working within the VMS environment is that output is intended for off-platform consumption & analysis in Excel, R, and Business Objects/Crystal Reports, and Splunk/Hunk. On-platform, I'm limited in what I can use by whatever I can compile &/or run from within my own user space, and the CONNX & similar tools all look to require a server process in the VMS environment.
Edit: I have accepted a comprehensive answer which, given organizational constraints, may not be feasible. My likely path will be to write additional data extractions jobs in FOCUS, and incur the latency & maintenance overhead that goes along with that.
Do you want to the reporting to be on-platform, or off-platform (for example with Excel)?
On-platform, after 30+ years, I still really really like Datatrieve, as mentions in a comment. This tool was created before SQL became all the rage, so its query language takes a little getting use. It knows show to used just about every RMS option (keys, RFA's for collections, joins, locks and sharing,...)
I'm sure there are multiple commercial tools like Focus you mention, and perhaps the IGH tool Vselect for data extraction, column shuffling, sorting. Some would even recommend OpenVMS native SORT but now you are still in Command Line space.
For a (green screen) windows approach, and command line, perhaps check out the freeware tool DIX: http://www.oooovms.dyndns.org/dix/
Off-platform google for "openVMS odbc" (jdbc). You'll find tools from Connx, Easysoft and "Connect" from the company I work for : Attunity. Those will allow you to use (windows, linux) tools like DBvizualizer or Excell to get to the OpenVMS sourced data.
Perhaps an interesting hybrid could be Attunity's Connect ( "AIS" ) solution which allows for SQL language RMS file access, but on platform (NAV_UTIL) and off-platform, ("Studio, Nav_util, Oracle db-link, ODBC, JDBC, XML, ... )
For better help, please clearify the query still better. Notably the remark " only from within the native OpenVMS command line shell". What's wrong with that? :-). What alternative access did you envision? fake-gui, DECwindows? Native API? Remote API? ...
Hope this helps some already, Hein