Replies

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).

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.