I have and am right now in the process of asking again, but they have not been helpful. What do you mean by "a COMPLETE class definition"? What they provided was class definitions based on our requirements (which are pretty specific).

Bump.

Thanks for the info, yes, this is an old version of Cache, and given the fact that we are stuck on this due to our vendor not upgrading the system, we have no choice. 

I need the indices, that is the reason I am working on the system right now, as some of these tables are performing so poorly that we are getting timeouts.

A View in SQL (at least in MySQL) is basically just a saved query - deleting the view has no effect on the data.

We are not the customer of IS, our vendor is, so we have no way of getting support from IS, unfortunately  (and our vendor is not any help). That plus we are on a very old version of cache, which makes it more difficult to even get community support.

Thanks for your reply, however, these concepts are still mostly beyond me. Due to lack of resources, I have not made much progress as all, to be honest. Our configuration is currently broken (we have huge SQL tables that cannot be indexed, so they perform terrible, for example), so we are just limping along.

I realize today that I made a mistake in the topic heading - I did not want to "drop the table",  but just delete the SQL table definition, not delete data.

Just now having time to look at this. I ended up just pulling the 2 sets separately, and I see now that this would have been an issue:

The mapping for this class assumes that there are no parents without children.  If you need to support rows that only have parent data then we will need to look at this a different way.

I don't think I can use the WRC. I am not an Intersystems customer, the vendor that built and maintains the product that uses Cache is the customer. We were provided access so that we can integrate our own systems into theirs. 

%BuildIndices/%PurgeIndices are generated for any class that primarily extends %Library.Persistent. When such a class defines one or more indexes and that class uses SQL Storage then the class must also define an index map in the storage definition. Without that the index simply won't be maintained - it doesn't exist as far as SQL is concerned. Your display in the original posting is a view of the SQL table definition. The SQL table definition is a projection of the class definition to SQL.

I get that part, and I get how to add those methods to my class, what I don't get is how to build the actual index. I can't find an example simple enough to, nor in reading the documentation do I, understand how to "create" an index. Again, I come from a MySQL background (and some MongoDB), and indexes are just specified, not created.

I'm not sure what you need for a "simple" example.

I want to create this Index (I realize the IdKey covers this one, but that is not showing up in the SQL Manager, so I want to test this one):

Index Account On Account;

<Storage name="demoStor">
<ExtentSize>100000</ExtentSize>
<Property name="accountNo"/>
<Property name="citySt"/>
<Property name="dob"/>
<Property name="name"/>
<Property name="patientNo"/>
<Property name="rel2Guar"/>
<Property name="sex"/>
<Property name="ssn"/>
<Property name="street1"/>
<Property name="street2"/>
<Property name="telephone"/>
<Property name="zip"/>
<SequenceNumber>80</SequenceNumber>
<SQLMap name="PatDemoMap">
<Data name="Account">
<Delimiter>":"</Delimiter>
<Piece>1</Piece>
</Data>
<Data name="AdmissionDate">
<Delimiter>":"</Delimiter>
<Piece>14</Piece>
</Data>
<Data name="DateChanged">
<Delimiter>":","^"</Delimiter>
<Piece>25,1</Piece>
</Data>
<Data name="Diag1Code">
<Delimiter>":"</Delimiter>
<Piece>17</Piece>
</Data>
<Data name="DischargeDate">
<Delimiter>":"</Delimiter>
<Piece>15</Piece>
</Data>
<Data name="Ethnicity">
<Delimiter>":"</Delimiter>
<Piece>35</Piece>
</Data>
<Data name="MedicalRecord">
<Delimiter>":"</Delimiter>
<Piece>11</Piece>
</Data>
<Data name="Note">
<Delimiter>":"</Delimiter>
<Piece>18</Piece>
</Data>
<Data name="Notes1">
<Node>"1"</Node>
</Data>
<Data name="Notes2">
<Node>"2"</Node>
</Data>
<Data name="OperatorChanged">
<Delimiter>":","^"</Delimiter>
<Piece>25,2</Piece>
</Data>
<Data name="PatInsOrd">
<Node>"0"</Node>
</Data>
<Data name="PatPolicy">
<Node>"P"</Node>
</Data>
<Data name="Patient">
<Delimiter>":"</Delimiter>
<Piece>2</Piece>
</Data>
<Data name="PrefLanguage">
<Delimiter>":"</Delimiter>
<Piece>36</Piece>
</Data>
<Data name="ProviderCode">
<Delimiter>":"</Delimiter>
<Piece>13</Piece>
</Data>
<Data name="Race">
<Delimiter>":"</Delimiter>
<Piece>34</Piece>
</Data>
<Data name="RecallInfo">
<Node>"9"</Node>
</Data>
<Data name="ReferringDocCode">
<Delimiter>":"</Delimiter>
<Piece>16</Piece>
</Data>
<Data name="Relationship">
<Delimiter>":"</Delimiter>
<Piece>21</Piece>
</Data>
<Data name="ReminderCode">
<Delimiter>":"</Delimiter>
<Piece>19</Piece>
</Data>
<Global>^PTG</Global>
<Subscript name="1">
<Expression>{Account}</Expression>
<Invalidcondition name="1">
<Expression>{L1}="Z"</Expression>
</Invalidcondition>
<LoopInitValue>99</LoopInitValue>
<StopExpression>{L1}="Z"</StopExpression>
</Subscript>
<Subscript name="2">
<Expression>{Patient}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Adsc.MedicsPremier.PatientsS</StreamLocation>
<Type>%CacheSQLStorage</Type>
</Storage>

I added these to my class, which compiles, but of course does nothing:

ClassMethod %BuildIndices() As %Library.Status
{
}

ClassMethod %PurgeIndices() As %Library.Status
{
}

I'm already doing that as well every night.

I see I was not clear. The data that I want to backup includes production data, but the purpose is for testing (to be able to restore individual globals to a test database). 

2 things:

1. Why are you doing that to XML versus the GOF format? I don't even see that as an option when doing that from the UI. Is that as easy to import as the GOF format? If I wanted to do the GOF using this method, how do I do that?

2. How do I automate this method?

As far as the Online Backup, I'm using that as well. But from the documentation, it appears I have to restore the entire database, and I want to be able to restore only specific globals (and also use these for Import into a test database). 

Finally, I'm a backup freak. I tend to have 2 or 3 different methods of backup for every system, plus we lost data last week (see my previous post), so I want to have several options to restore from.

Are there any online tutorials and/or simple examples of how to write the %BuildIndices and %PurgeIndices methods? The examples I've found are so complex, I'm not even sure where to start.

Enable "Cached Query - Save Source" — This specifies whether to save the routine and INT code that Caché generates when you execute any Caché SQL except for embedded SQL.

Are there any performance concerns with enabling this?

Wow! Super helpful, thanks!

So how can it be both "read on the fly" and be "a class which stores data on disk"? 

That's the thing, I don't want views, I want tables, and I need indexing (which was how this all got started, the indexing is not working). I was not aware that the data could be written to (or dropped) without add how that was to happen in the class (still don't understand how that could possibly correlate).

DROP. I understand how it reads the global values using the class definition, I don't understand how it drops (or any other write operation, for that matter).

ANY data in Caché and IRIS is stored in global and it's not stored twice, everytime in a global, which is in the storage strategy of the persistent class.

So a new global is made that is in this storage strategy format? The format of the global I am pulling data does not change when I create a class with a storage strategy but are you saying a new global is created in this new format? Or I am still not understanding what you mean?

For example, we have a class that converts this:

To this:

When y'all keep saying this:

Every persistent class (a class which stores data on disk) has the map of how the data should be stored in a particular global (globals). 

I don't see how that is occurring. Where is that data stored? Is that not just a mapping that is read "on the fly"? If not, then by what mechanism is the data stored in this new format?

I should have responded to both @Evgeny Shvarov  and @Eduard Lebedyuk at the same time, so see above, but I still don't understand what this means:

Every persistent class (a class which stores data on disk) has the map of how the data should be stored in a particular global (globals). 

Stores data on disk? Meaning, that if we create these persistent classes, the data is stored twice? Once in the global node and in some other format as defined (or not defined by the class)?  

But! As @Robert Cemper mentioned if you delete it via SQL API with DROP statement it will delete data in a global too (because we suppose the data to be deleted if we DROP a table, right?).

But just to reiterate, how does the DDL interpret a read definition into a write definition? The data stored in the global node and the definition do line up exactly (in our case, almost not at all).

That was my initial understanding, but then how does DDL drop the "table" (the global node data), since it is just a key-value store? There is no 1 to 1 relationship between the class and the global node data. How does a read definition get interpreted in the inverse? Makes no sense to me.

Also, it did not drop the global, only truncated it (dropped the data only). 

BTW, thanks to everyone in this thread (and others) for the time you are taking helping me. I had no idea this community existed and have been learning this on my own for the last 4 years and only found this forum about a month ago. 

I've read tons of documentation and I've been a developer for 20 years, but the correlation between these classes and the globals still eludes me.

I don't understand. How do I remove a class then? I mean, I have 3 classes that are basically duplicates of each other, how do I remove the duplicates, without removing the data?