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.

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?

Well, I thought the Objectscript classes were more akin to views than tables since the data is stored as a global, not as a table.

so having 3 'tables' on the same global is just having 3 views of the same house.

This confuses me. In MySQL, a "view" is just a saved SQL query - dropping a view does not drop data. I never imagined dropping the class  (which I thought was just a view) would empty the global. The global was still there, but all the data was gone. 

Because I have 3 copies of the same class / table, 2 were for testing and I am done with them.

Yes, and I used that once, which I believe is how I dropped the actual data in the global. :( 

Now, I am really confused. I only want to "drop" the SQL table created by the class, not the actual global (data). For example, in my testing, I created 3 tables for the same data (table, table2, table3), all pull data from the global (^PTG). I want to drop table 2 and table3. I did one by DDL and one by deleting the class. However, I seem to have lost data (global), but the timing was not when I did this, so I'm not sure when the data loss occurred.

I've looked at those articles, but given the old version of cache we are using (2013) and the horribly inconsistent ways these classes are written, plus my inexperience in this very "different" system than what I am used to, I have a hard time getting much out of them. I'll take another look. 

Right after I posted this, I started working on the theory of what you are describing and got one to work...sort of. I can see the index now, but I don't really understand how the storage for these is supposed to work (I barely understand SQL storage at all). 

Also, it seems like the inverse is not true. I have some that have the storage map created, but no matching Objectscript property and the index builds just fine...well, except the index does not match the storage map description.

Example:

I guess it would help to completely read your response. Also, I was very wrong, almost all of these generate this error:

But you need to generate their content separately by ClassMethod 

%BuildIndices

 in each class.

What does that mean? I'm a PHP / C# developer and some of the Objectscript / Cache processes still eludes me.

Ok, I take that back. Some of these generate no errors and no indices are built, but some of them generate this error:

What does that mean?