intersystems - Cache Database - DB model

Question

I have a requirement to read data from Cache database, before that i would like to see the DB model(List of tables or classes etc).I have used Management tool but i could'nt find out the tables list.How we find out the DB model using management tool?

Help is highly appreciable.

Thanks,

Answer 1

A Cache instance will have several namespaces. When you are connected to the System Management portal you will want to use the menu item System Explorer->SQL and be sure you are connected to the namespace that has the tables you are looking for. If you still find no tables it may be that for the system you are connecting to there are no tables defined. Application developers can, and did in the past, develop entire applications without actually using any class definitions/sql tables. If this is the case it is possible to create the class definitions/tables after the fact but does require understanding of the data structures that exist.

Answer 2

In Windows, you could also use any SQL client of your preference and use the Cache ODBC already set up in your PC when you installed Cache.

I've used (and actually prefer) SQuirrel SQL Client (free open source) and WinSQL apps with no issues whatsoever.

To setup Cache connections, just go to your Microsoft ODBC administrator and use them anywhere, even Excel for Pivot tables, etc.

If you're in MacOS or Linux/Unix, then the Cache Management Portal webapp or the SQL Shell are your best options though.

Answer 3

You can use normal SQL syntax:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

In Management Portal click here:

Also you can do this in Terminal: (samples is my namespace):

1. zn "samples"
2. do $system.SQL.Shell()
3. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'