You could do the same thing by creating a View with a function in the WHERE clause that called the method to figure out if you should see the row or not.

I think this is a cleaner solution as you do not need to worry about the %RLI becoming available again.


This is a bug that ISC Development needs to look at.  I spoke to the developer about it, he understands what is going wrong and will look at options to fix this.

The problem is the format of the data in the index does not match the format of %TimeStamp.  This Property is defined as a %TimeStamp but we are only storing the date part in the index.   If you modify you query to something like this then it should return the correct answer:

SELECT MAX(IndexedDate)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE DATEPART(sqltimestamp,IndexedDate) < '2016-11-28'

If you would like to track the Prodlog you can open an WRC issue and tell the advisor to come and talk to me about this.



Running TuneTable is safe to do at any time.  It will be CPU intensive so you might not want to run it at peak workload times, other than that it is fine to run at any time.


It is important that all your tables have this info so it is great that you are running it on all your tables.

The first question for any SQL performance issue is:  Have you run TuneTable?

It is very important to have correct values for ExtentSize, Selectivity and Block Count, whether the info comes from a developer manually entering it or by running TuneTable.  With out these values the Query Optimizer can only guess what the right plan might be.

The next step is to provide proper indices to support the queries you are going to write.  Without have a lot more info it is impossible to tell you what those might be or what type of index would be best (bitmap or standard).

I like Tim's method better.  While I understand the desire to hide complexity in the SQL you are also hiding information about the work that is needed to resolve the query.  What if it would be faster if the query first looked at the AppName.BO.DatabaseFunctions table and then looked at the ApplicationName.DB.MedicalCoding.ICDAutoCodeDefn table?  When you are hiding the join in the compute code that is not an option.  Using the -> syntax keeps the query pretty simple but still leaves all options open to the Query Optimizer.

can you please start a new question for this error.  Include the SQL and the full error message.  -400 should be reported for an COS error like <UNDEFINED>,  <SUBSCRIPT>, <FILEFULL>....


You have a couple of options:  I created an index in Sample.Person that is the same as yours and then look at this query

SELECT Name,FavoriteColors FROM Sample.Person
    WHERE FOR SOME %ELEMENT(FavoriteColors) (%Value = 'Red')

It uses the index and should give very good performance.

  • Read index map Sample.Person.FavColors, using the given %SQLUPPER(Subvalue(FavoriteColors)), and looping on ID.

  • For each row:
  •  Read master map Sample.Person.IDKEY, using the given idkey value.
     Output the row.

Have a look at the docs for info on Indexing Collections

The above is a little different as it is a List instead of an Array, but it should still work.

If you want better performance out of the Child Table created for the array you need to define the index to be on both the Element and the Key.  I think if you look in the Management Portal at the maps /  indices for your table you will see the one you defined is not projected in the child table.

I added the following Property and index:

Property Kids As array Of %String;

Index KidIndex On (Kids(ELEMENTS), Kids(KEYS));

and for this query:

SELECT id FROM Sample.Person_Kids WHERE Kids = 'Kieran'

I get a plan that uses the index.

  • Read index map Sample.Person_Kids.KidIndex, using the given %SQLUPPER(Kids), and looping on element_key and Person.

  • For each row:
  •  Output the row.

Either way should give you good performance.

hope this helps.


In 2012.2 and up you can use SQL Runtime Stats to gather the above info plus Global Refs and Lines of Code for every query run.  This has more overhead and take up more disk space so please remember to turn this off after you gather the needed info.

If you have questions after reviewing the docs I would be happy to answer them:

lots of options!

If you are going to change namespaces make sure you move to the new namespace, create and use your Object and then move back.  Cache does not support using an object opened in one namespace in a different namespace.

How are you doing this now in COS?  If you are using extended global syntax you could still do that in the class definition.   If you are using Global Mapping in the Namespace you could include Package Mapping to expose the classes as well.

If you have data in 2 databases that you want to expose in one class you can create a class using Cache SQL Storage and "map" the two globals into one class def.  I can provide an example of this if needed.

As for add SQL to your application, using Embedded SQL with cursors is the fastest way to go.

If you want to use a resultset, ISC encourages you to use %SQL.Statement.  The sample code above is a great example for %SQL.Statement, it is a dynamic query that is customized based on user import. 

If the query never changes use Embedded SQL.  If you feel the need to stick with a resultset then put the SQL in a class query and execute that.  Why waste the time preparing an SQL statement over and over again if it never changes?