Not opening an instance by index key after cache migration to iris.

Question

Question

Edmara Francisco · Nov 11, 2022

Hello!

Not opening an instance by index key after CACHE migration to IRIS. In composite indices, when one of the values is null, when using idx open it does not return the reference.

Has anyone had this problem?

Product version: IRIS 2022.1

Discussion (8)3

Log in or sign up to continue

Edmara Francisco · Nov 28, 2022

Hello!

For some reason, the query used by the idxExits method was frozen and was not returning the desired record.

After unfreezing the query and recompiling the class, the idxOpen method started returning the record as expected.

Thank you

0 0

score 0 · Answer 1 · 2022-11-11T12:26:24-05:00

Hi there,

We've actually faced this problem in the past. Executing %BuildIndices() wouldn't solve the problem either for us...

In our particular case, we found that killing the indexing globals and then calling the %BuildIndices() solved the issue. Have you tried that?

score 0 · Answer 2 · 2022-11-17T18:37:00-05:00

Hello,

It didn't solve for me....

Analyzing the global, the values are the same in CACHE as well as in IRIS, if there wasn't any chance in global value.

Anyone what changed from CACHE to IRIS that idxOpen can't found the data?

Thaks

score 0 · Answer 3 · 2022-11-17T20:26:19-05:00

First, the suggestion of Victor Gordillo is correct.

do ##class(Some.Class).%BuildIndices()

solves most index related problems.

Second, there are a couple of things, we do not know, but you
- did you made an in-place-upgrade or a clean install + data transfer
- the class in question is the same on IRIS and Cache (i.e. no change was made)
- is the class re-compiled (on IRIS)
- what do you exactly mean when you say "Not opening an instance...": (1) you get an NULL-OREF or (2) you get an error
- what do you provide as a NULL value: (1) "", (2) $c(0), (3) " " or something else as open parameter

A shortened class definition (we don't need the whole definition) would be also helpfull, something like this

Class Problem.Class extends %Persistent
{
Property Field1 as %String;
Property Filed2 As %String;

Index composite on (Field1, Field2) [ Unique];
}

score 0 · Answer 4 · 2022-11-22T19:14:27-05:00

Hello!

Follow the requested information

- did you made an in-place-upgrade or a clean install + data transfer
I made in-place-upgrade
- the class in question is the same on IRIS and Cache (i.e. no change was made)
Yes, is this same on IRIS and Cache, no change was made
- is the class re-compiled (on IRIS)
Yes, re-compilde on IRIS
- what do you exactly mean when you say "Not opening an instance...": (1) you get an NULL-OREF or (2) you get an error
I get an NULL-OREF
- what do you provide as a NULL value: (1) "", (2) $c(0), (3) " " or something else as open parameter
""A shortened

"" String Empty

Shortened class:

Class dado.TblTeste Extends (%Persistent, dado.Persistente)
{

Index idxFieldOneFieldTwoFieldThree On (FieldOneId, FieldTwoId, FieldThreeId) [ Unique ];

ForeignKey fkFieldOneId(FieldOneId) References dado.TblFieldOne();

ForeignKey fkFieldTwoId(FieldTwoId) References dado.TblFieldTwo();

ForeignKey fkFieldThreeId(FieldThreeId) References dado.TblFieldThree();

Property FieldOneId As dado.TblFieldOne [ Required ];

Property FieldTwoId As dado.TblFieldTwo [ Required ];

Property FieldThreeId As dado.TblFieldThree;

Property Descricao As %String [ Required ];

Property VersaoCheck As %String [ InitialExpression = 0 ];

}

Use:

Set object = ##class(dado.TblTeste).idxFieldOneFieldTwoFieldThreeOpen(1, 2, "")

Thanks

score 0 · Answer 5 · 2022-11-24T10:29:23-05:00

Add this line to your test:

Set object = ##class(dado.TblTeste).idxFieldOneFieldTwoFieldThreeOpen(1, 2, "")
If 'object Do $system.OBJ.DisplayError()

So you see the reason for failing

Open by unique index reqires an EXACT match

score 0 · Answer 6 · 2022-11-25T11:21:24-05:00

Hi Edmara!

Which IRIS version are you trying?

I did a test using the version "IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2022.2 (Build 368U) Fri Oct 21 2022 17:18:04 EDT" and all worked fine.

Please, checkout the example below on your IRIS version. It shows the index global value (^dado.TblTesteI) in order to get more visibility of what is going on.

dado.TblTeste:

Spoiler

Class dado.TblTeste Extends (%Persistent, %Populate)
{

Index idxFieldOneFieldTwoFieldThree On (FieldOneId, FieldTwoId, FieldThreeId) [ Unique ];
ForeignKey fkFieldOneId(FieldOneId) References dado.TblFieldOne();
ForeignKey fkFieldTwoId(FieldTwoId) References dado.TblFieldTwo();
ForeignKey fkFieldThreeId(FieldThreeId) References dado.TblFieldThree();
Property FieldOneId As dado.TblFieldOne [ Required ];
Property FieldTwoId As dado.TblFieldTwo [ Required ];
Property FieldThreeId As dado.TblFieldThree;
Property Descricao As %String [ Required ];
Property VersaoCheck As %String [ InitialExpression = 0 ];
ClassMethod OpenCompositeIndexTest()
{
    // IRIS version
    Write "IRIS version: ", $ZV,!

    // clean up tables
    Write !,"Cleaning up tables..."
    $$$TOE(st, ##class(dado.TblTeste).%KillExtent())
    $$$TOE(st, ##class(dado.TblFieldOne).%KillExtent())
    $$$TOE(st, ##class(dado.TblFieldTwo).%KillExtent())
    $$$TOE(st, ##class(dado.TblFieldThree).%KillExtent())

    // populate dependencies
    Write !,"Populating tables..."
    $$$TOE(st, ##class(dado.TblFieldOne).Populate())
    $$$TOE(st, ##class(dado.TblFieldTwo).Populate())
    $$$TOE(st, ##class(dado.TblFieldThree).Populate())

    Write !

    // test with value for all index fields
    Set testDesc = "Test with FieldThreeId using SQL DML"
    Write !,"---",!,testDesc_":"
    &SQL(insert into dado.TblTeste (FieldOneId, FieldTwoId, FieldThreeId, Descricao) values (1, 2, 3, :testDesc))
    If SQLCODE < 0 {Write !,"SQLCODE error ", SQLCODE, " ", %msg}
    Set object = ##class(dado.TblTeste).idxFieldOneFieldTwoFieldThreeOpen(1, 2, 3)
    If 'object {Do $system.OBJ.DisplayError()} Else {Write !,"Ok"}
    Write !,"Index global: ",!
    ZWrite ^dado.TblTesteI

    // test with no value for field FieldThreeId and insertion via SQL DML
    Set testDesc = "Test with no FieldThreeId using SQL DML"
    Write !,"---",!,testDesc_":"
    &SQL(insert into dado.TblTeste (FieldOneId, FieldTwoId, FieldThreeId, Descricao) values (1, 2, null, :testDesc))
    If SQLCODE < 0 {Write !,"SQLCODE error ", SQLCODE, " ", %msg}
    Set object = ##class(dado.TblTeste).idxFieldOneFieldTwoFieldThreeOpen(1, 2, "")
    If 'object {Do $system.OBJ.DisplayError()} Else {Write !,"Ok"}
    Write !,"Index global: ",!
    ZWrite ^dado.TblTesteI

    // test with no value for field FieldThreeId and insertion via object interface
    Set testDesc = "Test with no FieldThreeId using object"
    Write !,"---",!,testDesc_":"
    Set o = ##class(dado.TblTeste).%New()
    Do o.FieldOneIdSetObjectId(2)
    Do o.FieldTwoIdSetObjectId(1)
    Set o.Descricao = testDesc
    $$$TOE(st, o.%Save())
    Set object = ##class(dado.TblTeste).idxFieldOneFieldTwoFieldThreeOpen(2, 1, "")
    If 'object {Do $system.OBJ.DisplayError()} Else {Write !,"Ok"}
    Write !,"Index global: ",!
    ZWrite ^dado.TblTesteI
}

Storage Default
{
<Data name="TblTesteDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>FieldOneId</Value>
</Value>
<Value name="3">
<Value>FieldTwoId</Value>
</Value>
<Value name="4">
<Value>FieldThreeId</Value>
</Value>
<Value name="5">
<Value>Descricao</Value>
</Value>
<Value name="6">
<Value>VersaoCheck</Value>
</Value>
</Data>
<DataLocation>^dado.TblTesteD</DataLocation>
<DefaultData>TblTesteDefaultData</DefaultData>
<IdLocation>^dado.TblTesteD</IdLocation>
<IndexLocation>^dado.TblTesteI</IndexLocation>
<StreamLocation>^dado.TblTesteS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

dado.TblFieldOne:

Spoiler

Class dado.TblFieldOne Extends (%Persistent, %Populate)
{

Property FieldOneId As %Integer [ Required ];
Property FieldOneText As %String [ Required ];
Storage Default
{
<Data name="TblFieldOneDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>FieldOneId</Value>
</Value>
<Value name="3">
<Value>FieldOneText</Value>
</Value>
</Data>
<DataLocation>^dado.TblFieldOneD</DataLocation>
<DefaultData>TblFieldOneDefaultData</DefaultData>
<IdLocation>^dado.TblFieldOneD</IdLocation>
<IndexLocation>^dado.TblFieldOneI</IndexLocation>
<StreamLocation>^dado.TblFieldOneS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

dado.TblFieldTwo:

Spoiler

Class dado.TblFieldTwo Extends (%Persistent, %Populate)
{

Property FieldTwoId As %Integer [ Required ];
Property FieldTwoText As %String [ Required ];
Storage Default
{
<Data name="TblFieldTwoDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>FieldTwoId</Value>
</Value>
<Value name="3">
<Value>FieldTwoText</Value>
</Value>
</Data>
<DataLocation>^dado.TblFieldTwoD</DataLocation>
<DefaultData>TblFieldTwoDefaultData</DefaultData>
<IdLocation>^dado.TblFieldTwoD</IdLocation>
<IndexLocation>^dado.TblFieldTwoI</IndexLocation>
<StreamLocation>^dado.TblFieldTwoS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

dado.TblFieldThree:

Spoiler

Class dado.TblFieldThree Extends (%Persistent, %Populate)
{

Property FieldThreeId As %Integer [ Required ];
Property FieldThreeText As %String [ Required ];
Storage Default
{
<Data name="TblFieldThreeDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>FieldThreeId</Value>
</Value>
<Value name="3">
<Value>FieldThreeText</Value>
</Value>
</Data>
<DataLocation>^dado.TblFieldThreeD</DataLocation>
<DefaultData>TblFieldThreeDefaultData</DefaultData>
<IdLocation>^dado.TblFieldThreeD</IdLocation>
<IndexLocation>^dado.TblFieldThreeI</IndexLocation>
<StreamLocation>^dado.TblFieldThreeS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

}

Output of OpenCompositeIndexTest() method:

IRISAPP>d ##class(dado.TblTeste).OpenCompositeIndexTest() IRIS version: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2022.2 (Build 368U) Fri Oct 21 2022 17:18:04 EDT

Cleaning up tables... Populating tables...

--- Test with FieldThreeId using SQL DML: Ok Index global: ^dado.TblTesteI("idxFieldOneFieldTwoFieldThree",1,2,3,1)=""

--- Test with no FieldThreeId using SQL DML: Ok Index global: ^dado.TblTesteI("idxFieldOneFieldTwoFieldThree",1,2,-100000000000000,2)="" ^dado.TblTesteI("idxFieldOneFieldTwoFieldThree",1,2,3,1)=""

--- Test with no FieldThreeId using object: Ok Index global: ^dado.TblTesteI("idxFieldOneFieldTwoFieldThree",1,2,-100000000000000,2)="" ^dado.TblTesteI("idxFieldOneFieldTwoFieldThree",1,2,3,1)="" ^dado.TblTesteI("idxFieldOneFieldTwoFieldThree",2,1,-100000000000000,3)=""

HTH,

José

score 0 · Answer 7 · 2022-12-08T09:42:01-05:00

Developer Commu... · Dec 8, 2022

💡 This question is considered a Key Question. More details here.

0 0