Hi Danny, I see! I vaguely remember now there was a security setting to prevent personal from one facility seeing data generated from a different facility. But that wasn't important to us since we were setup as a single facility. As a result, the FACILITY column in every table is set to 1. It's not clear to me what IRIS role would allow pass this row level security, but it seems easier than re-compiling all the classes.
Strange, I'm a member of the %All role. And I added specific permissions (via Edit Role) to DocM.DocumentImage any way, but I still can't get query results.
Yes a security parameter and method exists as such. That was the one dependent class I also had to import into the USER namespace. I executed SQL from the Manager Portal and all the techniques from the terminal. I have like 4 records now in the USER namespace DocM.DocumentImage. I'll attach the class file in XML format.
Hi Danny, Ok I went ahead and exported the DocumentImage class (and one other dependent class) and successfully imported them into my empty USER namespace. Then I inserted a row using SQL. But found myself in the same situation, no SQL query results, but I could see the data in the global DocM.DocumentImageD.
Yes, I got this to work. Viewing the class definition was another learning experience. So in the terminal opening and writing out the global data works.
Hi Danny, yes I see a list of 20 indexes and their hyperlinked globals. This is where I click on the DocM.DocumentImageD global I can clearly see the data. When I clicked on Open Table I get a separate window: http://localhost:8972/csp/sys/exp/UtilSqlOpen.csp?$NAMESPACE=AVCWS&$ID1=DocM&$ID2=DocumentImage but it remains a blank page. The table is huge, about 500K records, so wondering if Open Table will paginate.
Hi Danny, the recompile worked well (just a few errors logged). The Upgrade() command just said "No classes were modified". But after those commands I was able to rebuild the indexes with no errors! Still SQL queries come back empty. Will keep scratching my head! I increase the row limit of my view of the global values to 5K, and the data looks great, just can't get it via SQL yet.
When I use the console to rebuild an index, I get this: DO ##CLASS(DocM.DocumentImage).%BuildIndices() ^ <CLASS DOES NOT EXIST> *%Library.CacheStorage I did a little research and found that %Library.CacheStorage was replaced by %Library.Persistent in IRIS.
Hi Danny I did a head for the DAT files and they appear to be correct. I'm not sure how indexing works in IRIS, but I have a suspicion that data indexes are stored in the missing “TMP” databases. That might explain why I can see data by directly looking at the globals but not getting results with SQL queries. When I search the iris.cpf file for global mappings containing the word index, I can get the following:
Wow! Using the Management Portal, under System > Globals > View Global Data, I can see data by looking at my ^DocM.DocumentImageD global! But when I use SQL: select ID, ... from DocM.DocumentImage (no where clause) I get: Row count: 0 Performance: 0.0660 seconds 327 global references 5390 commands executed 0 disk read latency (ms) Cached Query: %sqlcq.AVCWS.cls4 Last update: 2023-09-20 11:34:26.644
Yes Danny good idea. When I look at the cpf file, I actually see that I'm missing 2 databases: AVCWSTMP and AVPMTMP, but since they appeared to be for the purpose of temporary storage, I just created blank databases to take their place. And since they never gave me the corresponding DAT files, I didn't think they were important. Here's a snippet of the cpf file. I commented out the original entries of the cache.cpf file and replaced them with what I'm using in the IRIS.cfg. There appears to be some mapping of globals into the AVCWSTMP and AVPMTMP databases, but most of the mappings are into AVCWS and AVPM.
#[Databases] #CACHESYS=/opt/npc/cachesys/mgr/ #CACHELIB=/opt/npc/cachesys/mgr/cachelib/ #CACHETEMP=/opt/npc/cachesys/mgr/cachetemp/ #CACHE=/opt/npc/cachesys/mgr/cache/ #CACHEAUDIT=/opt/npc/cachesys/mgr/cacheaudit/ ## Must mount at startup #AVCWS=/npc/cachedb/yes_live/avcws/,,1 #AVCWSTMP=/npc/cachedb/yes_live/avcwstmp/,,1 #AVPM=/npc/cachedb/yes_live/avpm/,,1 #AVPMTMP=/npc/cachedb/yes_live/avpmtmp/,,1 ## end must mount at startup #DOCBOOK=/opt/npc/cachesys/mgr/docbook/ #SAMPLES=/opt/npc/cachesys/mgr/samples/ #USER=/opt/npc/cachesys/mgr/user/
Well I got the CACHE.DAT files from our hosting company and renamed them to IRIS.DAT. I also got hold of the cache.cpf file which I used to merged into a iris.cpf file. So using IRIS 2023.1 I was able to mount the databases and I can see the schema and table definitions. I cannot yet see any data. So not sure what is wrong. The IRIS.DAT files are large, 30GB and 53.4GB for each database. The hosted environment was Linux, and now I'm on Windows, but I believe the endian is the same (little). Using the Management Portal I don't see any options to upgrade or even verify the database. Let me know if you have any ideas. Thanks!
I found a work around, using to_number function around the column query, e.g. where to_number(max_client_resp_service) < 110 yields correct results. So strange it's as if the numeric(15,2) column is being stored or queried as an varchar string. Perhaps there's a wrong typed index on this column, but I can't seem to determine the index type if any on this column from INFORMATION_SCHEMA.INDEXES.
Thanks. I used %IGNOREINDEX and %NOINDEX optimizer hints and got the same results. And looking at my Cache Monitor generated DDL it looks like there's not an index on this column. There appears to be one clustered index on the table and it doesn't include the max_client_resp_service field.
Excellent suggestions Sergei. Yes I believe the index does need to be rebuilt! But I'll have to ask my hosting company to do that. I wonder if there's a query hint to to suppress the index temporarily.
Thanks Georg, I like option 2. I have a maximum of 12 values possible so I was able to write a general query (with 12 unions) to handle all possible records. I'm surprised how good the performance of $piece in combination with the unions are. I could limit or even eliminate the "(condition of your select)" to make a general in-line view to suite broad queries.
go to post
OMG! That worked! You have been so helpful. I'm so grateful and it's been a really fun experience.
go to post
Hi Danny, I see! I vaguely remember now there was a security setting to prevent personal from one facility seeing data generated from a different facility. But that wasn't important to us since we were setup as a single facility. As a result, the FACILITY column in every table is set to 1. It's not clear to me what IRIS role would allow pass this row level security, but it seems easier than re-compiling all the classes.
go to post
Strange, I'm a member of the %All role. And I added specific permissions (via Edit Role) to DocM.DocumentImage any way, but I still can't get query results.
go to post
Yes, the security parameter is set to 1 inside the NTSTLIB.SecurityPolicy class.
<Method name="%SecurityPolicy">
<ClassMethod>1</ClassMethod>
<FormalSpec>FACILITY:%Integer</FormalSpec>
<ReturnType>%String</ReturnType>
<SqlName>SecurityPolicy</SqlName>
<SqlProc>1</SqlProc>
<Implementation><![CDATA[ q FACILITY
]]></Implementation>
</Method>
The account I've been using is my installation account. Maybe I need to assign addition roles. Thanks!
go to post
Yes a security parameter and method exists as such. That was the one dependent class I also had to import into the USER namespace. I executed SQL from the Manager Portal and all the techniques from the terminal. I have like 4 records now in the USER namespace DocM.DocumentImage. I'll attach the class file in XML format.
go to post
Hi Danny, Ok I went ahead and exported the DocumentImage class (and one other dependent class) and successfully imported them into my empty USER namespace. Then I inserted a row using SQL. But found myself in the same situation, no SQL query results, but I could see the data in the global DocM.DocumentImageD.
go to post
Yes, I got this to work. Viewing the class definition was another learning experience. So in the terminal opening and writing out the global data works.
AVCWS>Set obj = ##CLASS(DocM.DocumentImage).%OpenId("1||1")
AVCWS>write obj.DocumentDescription
Diagnostic Impressions (Non PHP)-Diagnostic Impressions
AVCWS>
go to post
The first part of the 1st record of the DocumentImageD global is
1: ^DocM.DocumentImageD(1,1) = $lb("",1,"26179","PATIENT.USER_DEFINED",1,0,...
So I assume an id of 1 will suffice.
AVCWS>Set obj = ##CLASS(DocM.DocumentImage).%OpenId(1)
I assume a property will be any column name? But any writes I try come up with <INVALID OREF>.
AVCWS>write obj.ID
WRITE obj.ID
^
<INVALID OREF>
go to post
Hi Danny, yes I see a list of 20 indexes and their hyperlinked globals. This is where I click on the DocM.DocumentImageD global I can clearly see the data. When I clicked on Open Table I get a separate window: http://localhost:8972/csp/sys/exp/UtilSqlOpen.csp?$NAMESPACE=AVCWS&$ID1=DocM&$ID2=DocumentImage but it remains a blank page. The table is huge, about 500K records, so wondering if Open Table will paginate.
go to post
Hi Danny, the recompile worked well (just a few errors logged). The Upgrade() command just said "No classes were modified". But after those commands I was able to rebuild the indexes with no errors! Still SQL queries come back empty. Will keep scratching my head! I increase the row limit of my view of the global values to 5K, and the data looks great, just can't get it via SQL yet.
go to post
When I use the console to rebuild an index, I get this:
DO ##CLASS(DocM.DocumentImage).%BuildIndices()
^
<CLASS DOES NOT EXIST> *%Library.CacheStorage
I did a little research and found that %Library.CacheStorage was replaced by %Library.Persistent in IRIS.
go to post
Hi Danny I did a head for the DAT files and they appear to be correct. I'm not sure how indexing works in IRIS, but I have a suspicion that data indexes are stored in the missing “TMP” databases. That might explain why I can see data by directly looking at the globals but not getting results with SQL queries. When I search the iris.cpf file for global mappings containing the word index, I can get the following:
Global_indexEp*=AVCWSTMP
Global_indexTX=AVPM
Global_indexCareFabricQuery=AVPMTMP
Global_indexEp*=AVPMTMP
Almost every instance is mapped to a TMP database. I’m asking our hosting company for these files now.
go to post
I don't seem to get any meaningful data from terminal queries on original unmounted DAT files.
%SYS>Write $$ROOT^LABEL("D:\Avatar\avatar\avcwsdat\CACHE.DAT")
-1
%SYS>Write $$ROOT^LABEL("D:\Avatar\avatar\avpmdat\CACHE.DAT")
-1
go to post
Wow! Using the Management Portal, under System > Globals > View Global Data, I can see data by looking at my ^DocM.DocumentImageD global! But when I use SQL: select ID, ... from DocM.DocumentImage (no where clause) I get: Row count: 0 Performance: 0.0660 seconds 327 global references 5390 commands executed 0 disk read latency (ms) Cached Query: %sqlcq.AVCWS.cls4 Last update: 2023-09-20 11:34:26.644
go to post
Yes Danny good idea. When I look at the cpf file, I actually see that I'm missing 2 databases: AVCWSTMP and AVPMTMP, but since they appeared to be for the purpose of temporary storage, I just created blank databases to take their place. And since they never gave me the corresponding DAT files, I didn't think they were important. Here's a snippet of the cpf file. I commented out the original entries of the cache.cpf file and replaced them with what I'm using in the IRIS.cfg. There appears to be some mapping of globals into the AVCWSTMP and AVPMTMP databases, but most of the mappings are into AVCWS and AVPM.
[ConfigFile]
#Version=2017.226
Product=IRIS
Version=2023.1
#[Databases]
#CACHESYS=/opt/npc/cachesys/mgr/
#CACHELIB=/opt/npc/cachesys/mgr/cachelib/
#CACHETEMP=/opt/npc/cachesys/mgr/cachetemp/
#CACHE=/opt/npc/cachesys/mgr/cache/
#CACHEAUDIT=/opt/npc/cachesys/mgr/cacheaudit/
## Must mount at startup
#AVCWS=/npc/cachedb/yes_live/avcws/,,1
#AVCWSTMP=/npc/cachedb/yes_live/avcwstmp/,,1
#AVPM=/npc/cachedb/yes_live/avpm/,,1
#AVPMTMP=/npc/cachedb/yes_live/avpmtmp/,,1
## end must mount at startup
#DOCBOOK=/opt/npc/cachesys/mgr/docbook/
#SAMPLES=/opt/npc/cachesys/mgr/samples/
#USER=/opt/npc/cachesys/mgr/user/
[Databases]
IRISSYS=D:\InterSystems\IRISHealth\Mgr\
IRISLIB=D:\InterSystems\IRISHealth\Mgr\irislib\
IRISTEMP=D:\InterSystems\IRISHealth\Mgr\iristemp\
IRISLOCALDATA=D:\InterSystems\IRISHealth\Mgr\irislocaldata\
IRISAUDIT=D:\InterSystems\IRISHealth\Mgr\irisaudit\
AVCWS=D:\InterSystems\IRISHealth\Mgr\cws\,,1
AVCWSTMP=D:\InterSystems\IRISHealth\Mgr\avcwstmp\,,1
AVPM=D:\InterSystems\IRISHealth\Mgr\pm\,,1
AVPMTMP=D:\InterSystems\IRISHealth\Mgr\avpmtmp\,,1
ENSLIB=D:\InterSystems\IRISHealth\Mgr\enslib\
HSCUSTOM=D:\InterSystems\IRISHealth\Mgr\HSCUSTOM\
HSLIB=D:\InterSystems\IRISHealth\Mgr\hslib\
HSSYS=D:\InterSystems\IRISHealth\Mgr\hssys\
USER=D:\InterSystems\IRISHealth\Mgr\user\
[Namespaces]
%SYS=IRISSYS
#%SYS=CACHESYS
AVCWS=AVCWS
AVCWSTMP=AVCWSTMP
AVPM=AVPM
AVPMTMP=AVPMTMP
#DOCBOOK=DOCBOOK
#SAMPLES=SAMPLES
HSCUSTOM=HSCUSTOM
HSLIB=HSLIB
HSSYS=HSSYS
USER=USER
[Map.AVCWS]
Global_A=AVPM
Global_ACAP=AVPM
Global_ADT*=AVCWSTMP
Global_AERROR*=AVCWSTMP
Global_AGE*=AVCWSTMP
Global_APAY*=AVCWSTMP
Global_AREF=AVPM
Global_AREFCAP=AVPM
…
Global_CacheSql=AVCWSTMP
…
Global_csmsql=AVCWSTMP
...
[Map.AVPM]
Global_A=AVPM
Global_ACAP=AVPM
Global_ADT*=AVCWSTMP
Global_AERROR*=AVCWSTMP
...
Global_CacheSql=AVPMTMP
…
Global_csmsql=AVPMTMP
<End of Message>
go to post
Well I got the CACHE.DAT files from our hosting company and renamed them to IRIS.DAT. I also got hold of the cache.cpf file which I used to merged into a iris.cpf file. So using IRIS 2023.1 I was able to mount the databases and I can see the schema and table definitions. I cannot yet see any data. So not sure what is wrong. The IRIS.DAT files are large, 30GB and 53.4GB for each database. The hosted environment was Linux, and now I'm on Windows, but I believe the endian is the same (little). Using the Management Portal I don't see any options to upgrade or even verify the database. Let me know if you have any ideas. Thanks!
go to post
I found a work around, using to_number function around the column query, e.g. where to_number(max_client_resp_service) < 110 yields correct results. So strange it's as if the numeric(15,2) column is being stored or queried as an varchar string. Perhaps there's a wrong typed index on this column, but I can't seem to determine the index type if any on this column from INFORMATION_SCHEMA.INDEXES.
go to post
Thanks. I used %IGNOREINDEX and %NOINDEX optimizer hints and got the same results. And looking at my Cache Monitor generated DDL it looks like there's not an index on this column. There appears to be one clustered index on the table and it doesn't include the max_client_resp_service field.
go to post
Excellent suggestions Sergei. Yes I believe the index does need to be rebuilt! But I'll have to ask my hosting company to do that. I wonder if there's a query hint to to suppress the index temporarily.
go to post
Thanks Georg, I like option 2. I have a maximum of 12 values possible so I was able to write a general query (with 12 unions) to handle all possible records. I'm surprised how good the performance of $piece in combination with the unions are. I could limit or even eliminate the "(condition of your select)" to make a general in-line view to suite broad queries.