What is the Best Tool to Edit Data in InterSystems IRIS?

Hi,

   we are updating the data using an update query via the management portal. Globals need to change then we are using the terminal to change it. Recently, started to Visual studio code extension of SQL Tools. but frequently check and update the above would not work out. each time change query parameters. so we created a zen page to do the CRUD operations to the particular table. 

I don't have a real favorite.

- for similar changes in multiple records, I use SQL in SMP, rarely also $system.SQL.Shell() from terminal
- for single changes, I use Global Edit in SMP and direct access in terminal equally
- for special objects I often write my class related customized edit method

I believe the best answer is: It depends.

Depends on the data architecture of the original project, whether the data structure is GLOBAL, relative SQL or Objects.

For example, in my current project, I have data in objects originating from Ensemble and metrics and monitoring data in relational tables. The native part of Ensemble is never subject to change, but the part concerning relational tables sometimes needs editing. When it happens I prefer to use third party SQL manager like DBeaver.

In another project, fully specified in Globals, the preferred way of editing large amounts of data was to write scripts in ObjectScript.

Edit globals directly, only in very specific situations. And keep in mind that, if the global is referring to an SQL table, there is always the risk of corrupting an index.

Hi. The team I work in has been supporting old MUMPS and then Cache systems for many years, and have two rules for fixing data on live systems:

1. Never write a kill statement directly at the command line.

It's quick and convenient, but we've seen many occasions  where big chunks of, or, before the database parameter to stop it, whole globals were removed by accident. Cue major panics, hours of restore and de-journal. etc.

2. Don't write SQL updates directly (SMP, command line, etc).

Because if you get the WHERE wrong then all rows in a table suddenly vanish or have the same value. :-)

So we use a local command line tool for global amendments that uses the old %G style selection search to display one node at a time, so that you can then edit the data, or kill (with a warning first if there are sub nodes you may have forgotten about).

We also have a local command line tool for SQL/objects. This takes in a table name and WHERE clause, and only allows editing and deleting when the query finds only one row.

Anything more than a few nodes/rows and we usually write a proper bit of script on our dev server, and get it checked and tested before running in live. Better safe than sorry.

Why such a complicated question, I wonder. Can anyone answer immediately the "Best Tool" for any given job (other than a salesman, of course)?

I'm a fan of writing my own SQL commands on the SQL Browser (it gives me a sense of power that comes from using the raw tools some deity provided for me) but hey, if it's as simple as editing a bunch of records, well my choice is Dbeaver Community Edition (through JDBC) but it's not the only one based on java (that's why JDBC driver is needed). The one I downloaded came with its own OpenJDK (but it's not full java as some .dll, .dylib or .so libraries are included). It seems you can even install it as some Eclipse plugin if you want! I haven't tested this one yet though.

Well it's simple and you can use the jar that's in the <instance-dir>/dev/java/lib/JDK11/intersystems-jdbc-<version>.jar. It offers to download one as you establish your first connection to an "InterSystems IRIS" or "Cache" database, check this Dbeaver officially supports ....

It has a nice "view data" for a table and allows to edit in a grid (you have to "save" in order to trigger all the UPDATE commands necessary or see them prior to submitting/saving). It's filterable so you see just a range of data you want to edit as in "ID between 40 and 50". If you have some primary key (other than the ID column which is hidden to the beaver but you still can use it to filter data) then the updates will use it no questions asked, otherwise you can use all the columns for the updates but you know how that can have unwanted repercussions. 

It also has the usual import and export (using csv files with mapped columns, just don't use any identity columns!) and the community edition is supposed to be under an apache license. If you really like of course you can buy some license that gives you a full load of functionalities but if it's just to edit some records,  I'd stick to the community edition.

RESTForms, duh.