This looks like it is just a bug.  I reproduced this in InterSystems IRIS latest.  Looking at the generated code we are able to resolve the the index global in other places, but not in this one case.

I will report the bug to development to get it fixed.

My question is what do you plan to use this index for?

So the issue with Inheritance and Cache SQL Storage is you can't modify the storage to add new properties in the sub class.  For you that does not matter, you just want to change the global name.  So if there was some way we could write the code so that it would generate a different global name for the different classes you would be all set.  I tried using $parameter($classname,"MyParameter") but could not get it to work.  One issue, I think, is when we are compiling this code we are in a Cached Query class, not the class I want to be referencing.  

The best I could do is hard code it to the current class.  So if you have 2 classes with the info below the only change you need to make is to change the parameter value from A to B.

Class Mapping.SuperClass Extends %Persistent [ Language = objectscript, StorageStrategy = NewStorage1 ]
{ Parameter GloParameter = {"A"}; Property GlobalName As %String; Property Sub1 As %Integer; Property Data1 As %String; Index Master On (GlobalName, Sub1) [ IdKey ]; Storage NewStorage1
{
<SQLMap name="Map1">
<Data name="Data1">
<Delimiter>"^"</Delimiter>
<Piece>1</Piece>
</Data>
<Global>^*</Global>
<Subscript name="1">
<AccessType>Global</AccessType>
<Expression>{GlobalName}</Expression>
<NextCode> i {L1}="" s {L1}=##class(Mapping.SuperClass).#GloParameter QUIT
 s {L1}=""
</NextCode>
</Subscript>
<Subscript name="2">
<AccessType>Sub</AccessType>
<Expression>{Sub1}</Expression>
</Subscript>
<Type>data</Type>
</SQLMap>
<StreamLocation>^Mapping.SuperClassS</StreamLocation>
<Type>%Storage.SQL</Type>
} }

Not sure if you contacted the WRC or not, but if not here is what I would try.

1)  Can you use a different ODBC tool?  Our fav in support is WinSQL.  If this works then we know we are dealing with an ADO issue, if it fails then most likely we have an ODBC bug.

2)  Turn on the ODBC client logging. If you are using the ODBC Data Source Manager then there is a checkbox ODBC logging.  You will need to stop and start your ADO client to pick up the flag, reproduce the problem and then look for the irisODBC.log file in C:\Users\Public\Logs

Hello

I was able to get this to work.  I think the only chagne I made was to add the SQLName to the query

Here is my class with your code

Class DC.ProcClass [ Abstract ]
{ Query ClosedFutures(FromDate As %Date, ToDate As %Date) As %Query(ROWSPEC = "FundCode:%String, Asset:%String, TradeDate:%Date,BuySell:%String, Price:%Numeric, Quantity:%Numeric, CumulativePosition:%Numeric, PreviousCumulativePosition:%Numeric, ClosingTrades:%Integer, ClosingTradesInPeriod:%Integer, ClosedTradeValueInPeriod:%Numeric, NetQuantityInPeriod:%Integer, WeightedClosedAverageTradePriceInPeriod:%Numeric, InitialTradeDate:%Date, InitialTrade:%Integer, LastInPeriod:%Integer") [ SqlName = ClosedFuturesProc, SqlProc ]
{
} ClassMethod ClosedFuturesExecute(ByRef qHandle As %Binary, FromDate As %Date, ToDate As %Date) As %Status
{
// Contains complex implementation including setting ^||justClosedTrades
} ClassMethod ClosedFuturesClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
kill ^||justClosedTrades
Quit $$$OK
} ClassMethod ClosedFuturesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ClosedFuturesExecute ]
{
set nextid=$order(^||justClosedTrades(qHandle),1,rec)
if $length(nextid) { set Row=$listbuild($piece(rec,"~",1),$piece(rec,"~",2),$piece(rec,"~",3),$piece(rec,"~",4),$piece(rec,"~",5),$piece(rec,"~",6),$piece(rec,"~",7),$piece(rec,"~",8),$piece(rec,"~",9),$piece(rec,"~",10),$piece(rec,"~",11),$piece(rec,"~",12),$piece(rec,"~",13),$piece(rec,"~",14), $piece(rec,"~",15), $piece(rec,"~",16)),qHandle=nextid }
else { set AtEnd=1,Row="" } Quit $$$OK
} }

and here is the view using my schema and SQLName

Class DC.NewClass1 [ ClassType = view, ViewQuery = {
SELECT * FROM DC.ClosedFuturesProc(65257,65286) --'2019-09-01','2019-09-30'
} ]
{ Parameter READONLY = 1; }

Giray

Your code does not match your class descriptions.  In your code you are using a property, Parent to do the child lookup, but there is no parent property defined in Example.Child.

If you truly want a Many to Many then you can't define a Parent property in the child, unless it is a list, and I am not recommending that!

I am not a fan of Lists of Objects, but this might not be too bad.  I think I would still rather go with an array of objects as that would give you a third table and line up with the classic Many to Many format using 3 tables.  If you want to stay with a List then I would suggest that you pass the List of Child IDs into the QueryChildren method.  You can then use the list of Object IDs to get each child via SQL or Objects.

Sorry for not answering this sooner.  In Cache / InterSystems IRIS when you define a Parent Child relationship between 2 classes the Child class has an IDKey made up of 2 values:  a reference back to the Parent and a unique identifier.  Because the IDKey is a compound key you can not define a Bitmap index in the Child Ccass.  That is drawback #1.   Bitmap indices are not the be all / end all, but they can give you GREAT performance so it is nice to keep this option on the table.

The Parent Child relationship Storage is the second drawback, while it is not required, and it might not always be a drawback, the classic storage for a Parent Child relationship is to store all the data in the Parent Global. 

Now to explain this I want to make sure everyone knows what I am talking about when I say Global.  InterSystems stores persistent data in a sparse tree structure that we call Globals.   Subscripts are always stored in ASCII Collated order, so if I did it right the way I show the globals here is the same way they would be stored.  A simple persistent class will store the data in a global like this:  ^Package.ClassD(id), where id in a unique integer.  So 4 rows in this table could look like:

      ^Package.ClassD(1)=$LISTBUILD("data 1")

      ^Package.ClassD(2)=$LISTBUILD("data 2)

      ^Package.ClassD(3)=$LISTBUILD("data 3")

      ^Package.ClassD(4)=$LISTBUILD("data 4")

If we define a Child table in this class then we will store that child data in a lower level subscript

    ^Package.ClassD(1)=$LISTBUILD("data 1")

          ^Package.ClassD(1,"Child",1)=$LISTBUILD("Child 1")

        ^Package.ClassD(1,"Child",2)=$LISTBUILD("Child 2")

      ^Package.ClassD(2)=$LISTBUILD("data 2)

        ^Package.ClassD(12"Child",4)=$LISTBUILD("Child 4")

      ^Package.ClassD(3)=$LISTBUILD("data 3")

      ^Package.ClassD(4)=$LISTBUILD("data 4")

        ^Package.ClassD(4,"Child",3)=$LISTBUILD("Child 3")

Now add a second Parent Child relations where the child class is the parent so we have 3 subscript levels

    ^Package.ClassD(1)=$LISTBUILD("data 1")

         ^Package.ClassD(1,"Child",1)=$LISTBUILD("Child 1")

              ^Package.ClassD(1,"Child",1, "Grand",3)=$LISTBUILD("Grand 3")

        ^Package.ClassD(1,"Child",2)=$LISTBUILD("Child 2")

              ^Package.ClassD(1,"Child",2, "Grand",1)=$LISTBUILD("Grand 1")

              ^Package.ClassD(1,"Child",2, "Grand",2)=$LISTBUILD("Grand 2")

      ^Package.ClassD(2)=$LISTBUILD("data 2)

        ^Package.ClassD(12"Child",4)=$LISTBUILD("Child 4")

      ^Package.ClassD(3)=$LISTBUILD("data 3")

      ^Package.ClassD(4)=$LISTBUILD("data 4")

        ^Package.ClassD(4,"Child",3)=$LISTBUILD("Child 3")

It should be easy to imagine this global becoming very large storing all the different children at all the different levels.  This increases the cost of getting the data for 1 class into memory because we need to load all the above data into memory just to get the 4 rows from the top level parent table.  This increased IO is the second issue with Parent Child Relationships.

When you define a Foreign Key between 2 classes the storage structure is not changed.  Both classes by default will have a positive Integer as the IDKey so they will both support Bitmaps.  They will each have their own D global so you will only need to load the data for the "Child" table when it is needed.  

If you go with the Foreign Key instead of the Parent Child make sure you define the FKey to use Cascade on Delete and you need to define an index in the "Child" class on the reference to the "Parent" class.

OK so that was longer than I thought it would be.  Hope it makes sense.  If you have more questions i will try to answer them faster this time.

Hi Sean

2 reasons:  

    1:  In general you get better performance if the data is stored in 2 different global.  The only time having the data in 1 global is better is when you query both the parent and the child together.  If you write a query against just one you still end up loading the other table as well.  So over all 2 globals means less disk IO.

    2:  You can't use bitmaps in the child table because the ID of the child is a String and Bitmaps only work when the IDKey is a positive Integer.

The downside of using a Foreign Key is with Objects you don't have the methods to get at children like you do with a Relationship.

Brendan

Hi Jimmy

Just to make sure we are talking about the same thing:  in Cache the Primary Key is not always the ID Key.  I think you are really talking about the ID Key, used in the subscripts of the D global.  If I am correct then you have an index define on Year , Month, OrderNo with IDKey True.

From a Parent Child point of view having a compound IDKey is fine.  The IDKey of the parent will be something like "2018||11||1" and then the IDKey of the children will just add one more subscript:  "2018||11||1||child 1"

Generally speaking we recommend that you leave the IDKey as a system generated integer and instead of using a Parent Child relationship you define a Foreign Key with Cascade on Delete  to get the same behavior.  You can still define a Primary Key index on Year, Month, and OrderNo so deleted will be fast.  By keeping the data in different globals and using Integers as IDKeys you will be able to define a Bitmap index in all of your classes and will keep disc IO to a minimum when queries the different tables.

Now maybe you say you will not be using Bitmaps and you will never query the children without also referencing the Parent data, so having the data in the same global makes sense.  You need to look at everything  case by case.

Will it work, Yes.

Will it perform the way you want it to, Maybe.

Sorry I can't give you a simple yes or no, but hopefully I have provided the needed info so you can make an educated decision.  As always InterSystems Support is happy to go over your specific details and help you come up with what will work best.

Brendan Bannon

InterSystems Support Manager

Hello I am trying to get a good example, but for now I will just try to give you a general description. As the Docs say this hint is used in a subquery. At compile time the optimizer has 2 options for dealing with a subquery: 1) Treat it as a black box, executing the subquery independently from the other query and then applying the results to the outer query 2) rewrite the query incorporating the subquery into the outer query as a JOIN and combining conditions from the subquery to the WHERE clause of the outer query. If you look at the show plan of a query you can see which option the optimizer is taking. If you see a section of the plan with a heading of SubQuery then you know it is option 1. If the query does not have a Subquery section we are in option 2. You can see that with the following query: select home_state, avg(age) from sample.employee e where home_state = ANY (select MAX(v.address_state) from sample.vendor v where balance > 0) group by home_state will be rewritten while: select home_state, avg(age) from sample.employee e where home_state = ANY (select MAX(v.address_state) from %NOMERGE sample.vendor v where balance > 0) group by home_state will keep the subquery.

Scott

Not that I have seen a lot of outbound adaptor code, but I have never see TSQL and a query sent over the wire like this.

If you just send the SELECT does it work?

are you getting any info back from the execute you are logging?

$$$LOGINFO("tSC = "_tSC)

THe next set would be to look at Java Gateway log or any logging SQL Server might have to see what we are sending over to SQL Server.

If none of this point to the issue I would suggest opening a WRC issue and getting one of our Ensemble  to have a look.

Thanks for sending the class to me Yaniv.  

Life is always easier when you don't have to guess what is happening. 

The 2 options list above are not really options, but rather the steps you should take when defining any type of index for Cache SQL Storage.  Defining the Index def helps us correctly report info to external databases, but it is the map in the storage that the query optimizer is looking for, so #1 is required for all types of indices.  

Here is the property and index

Property StatusCode As %String(COLLATION = "EXACT");

Index StatusCodeIdx On StatusCode [ Type = bitmap ];

When you want to define a bitmap you do not add the IDKey as a subscript, it will be generated in the data.  For Yaniv's class this is what the bitmap storage def will look like:

<SQLMap name="StatusCodeMap">
<Global>^SPMORDP</Global>
<Subscript name="1">
<Expression>2</Expression>
</Subscript>
<Subscript name="2">
<Expression>{StatusCode}</Expression>
</Subscript>
<Type>bitmap</Type>
</SQLMap>

If you do not want to use EXACT collation then the Collation that is defined in the Property needs to match the collation defined in the map.  If no collation is listed for a property the default is SQLUPPER, so when the Property looks like this:

Property StatusCode As %String;

the Map needs to have a Subscript that looks like this:

<Subscript name="2">
<Expression>$$SQLUPPER({StatusCode})</Expression>
</Subscript>

Also when ever you define a bitmap in a class you should also define a Bitmap Extent.  The class compiler does this automatically for Default Storage, but for Cache SQL Storage you need to define the extent map.  The <Type> is bitmapextent and no fields are listed as subscripts:

<SQLMap name="BitmapExtent">
<Global>^SPMORDP</Global>
<Subscript name="1">
<Expression>3</Expression>
</Subscript>
<Type>bitmapextent</Type>
</SQLMap>

If you are using Objects or SQL to modify the data then these indices will be maintained by the generated class code.  If you application is still doing Global sets and kills then you will need to write code to maintain the indices.  You can look at the class Mapping.BitMapExample that is in The Art of Mapping Globals to Classes (5 of 3) to see what that code would look like.

If you have any questions please let me know.

Here is my version of Yaniv's class

Brendan

This looks like a bug in InterSystems code.  Can you please email this problem  to  the WRC:  Support@intersystems.com

Please include this table, the parent table and the Version you are using.

Can you give a little more info about the query format and the details of the <STORE> error.  In simplest terms:  

SELECT * FROM VeryLargeTable

will not result in a STORE error.  So you either have a GROUP BY or you are doing other things with the results and that is leading to a <STORE> error.

Brendan

If you really are doing just a SELECT COUNT(*) FROM Table does your class have a Bitmap extent?  That is the fastest way to get the answer for a COUNT(*).

If you have a BitMap Extent and the query is still slow then you might need to clean up your bitmap.  For tables that have a large number of rows deleted, Link most Ensemble table, over time a bitmap will slow down.

You can clean them up by using a system utility:  %SYS.Maint.Bitmap

There is not much in the docs about this Util but you can have a look at the Class Reference to see examples of how to use it.

Nicki

An Array of Objects is basically the same as a Parent Child relationship storing the child data in the same global as the parent.  

When people create the third class CinemaFilms they are do that to create a Many to many relationship between Cinema and Films.  One Cinema shows many Films and one Film will be shown in many Cinemas.

For performance reasons we suggest using Foreign Keys instead of relationships.  You can still setup the Parent Child behavior with Cascading Delete.

I don't see why using a Foreign Key would break Referential Integrity.

brendan

Alexander

I think you need to use 2 parameters in your COS code

s sp1="child"

s sp2="health"

&SQL(

DECLARE c1 CURSOR FOR

SELECT ID INTO :id FROM ICD WHERE Name %CONTAINS (:sp1, :sp2)

This would be equivalent to what you are doing in the Portal.

Brendan

Just to make it as simple as possible as I am guessing you are new to Caché

ClassMethod Export(pGlobal, pFile)
{
    set file=##class(%File).%New(pFile)
    do file.Open("WN")
    set key=$order(^Global1(""))
    while key'="" {
        do file.WriteLine(^Global1key))
        set key=$order(^Global1(key))
    }
    do file.%Save()
}

$ORDER() is a command that returns the next defined subscript value.  When you pass in "" it returns the first defined subscript value, 1 in your case.   After writing the value of ^Global1(n) to the file the final $ORDER() will return "" indicating there are no more defined subscript values.

for more details see our docs for $ORDER()

Susobhan

The example you have above should have no problem handling the number of rows assuming the Business Logic is not building some huge thing in memory.

%Library.ResultSet has been replaced by %SQL.Statement.  We would expect %SQL.Statement to give you better performance then %Library.ResultSet.

To increase performance a little more Embedded SQL gives the best performance of all three options.  In older version of Cache the difference was pretty large.  In current version of Cache the difference is not that great.

Brendan

Hello

Are you storing 64365 or 64365,23587?

If you are storing the first then the above answers are correct.  If you are storing the second then you will need to create your own Data Type class based on %Timestamp and write your own LogicalToStorage and StorageToLogical methods.

Cache has system functions that will convert $H to a readable format and back

USER>W $zdatetime($h)
03/23/2017 06:36:38

USER>W $ZDATETIMEH("03/23/2017 06:36:38")
64365,23798

Brendan

Susobhan

Yes you can use any DML statements over ODBC to create new users and GRANT and REVOKE privs.

Create User:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Grant :  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Revoke:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

We do not recommend making general use of the _SYSTEM username.  For a secure system you should either disable this account, or change the password and limit it's use.  Then create a new Cache User for each person connecting to the application.  You can first create a Role that has all the privs needed for a general user and then just GRANT that one Role to each User.

If you need more detailed examples on this you can contact InterSysetms Support and we will help you out.

Brendan