Take a look at these two other posts with very similar questions, maybe you can find something that works for you. Unfortunately, there doesn't seem to be a built-in random function on the SQL side (though on the Objectscript side there is $RANDOM.)
These errors are one part of the picture. It would probably be beneficial to open a ticket with the WRC so they can look into these errors and your performance issues to see if they are related/separate.
Do you know what queries are triggering these errors? w3wp.exe is an IIS process but I'm not familiar with EXTRService.exe or KIORAS. Perhaps the IP addresses can also help you narrow down the source.
The <READ> errors indicate something like a problem reading from the socket, but that doesn't explain the cause. There may be other logs you can review in your environment to help explain this.
To add-on to my initial post, I do think that if you use the same type of outbound adapter and inbound adapter (ie counted), it would be expected for the message to be accepted. Since you're sending XML I wonder why the operation isn't using the counted XML adapter. Hopefully, somebody with expertise in this area can help you.
You might need to give more details on what your operation/service code looks like to determine what's going on. The WRC or your InterSystems rep could probably help with this.
The TCP adapter (including counted) docs can be found here. There are similar docs for the outbound, but it's pretty much the same but swapped for outbound:
"EnsLib.TCP.CountedInboundAdapter supports incoming TCP connections over which a TCP client and TCP listener exchange blocks of data, with the block length specified in the first 4 bytes of the block. The adapter uses the block length to acquire the meaningful portion of the data from the client application."
The above doc page also includes some guidance for setting up a TCP service.
Is there a particular reason you are using a Counted operation/service rather than generic TCP? I'm unfamiliar with the use case for the Counted adapters.
The above suggests that the counted adapters are meant for handling "counted data blocks".
The error you are getting seems to indicate a mismatch between the block size being sent, and the expected size. Looking at the traffic in a Wireshark could be helpful to understand what is happening.
"This method is called to remove data from the %SYS.PTools.SQLStats table. It does not remove data from %SYS_PTools.SQLQuery, those rows are cleaned up when a query is compiled"
You can take a look at what's actually filling up your CACHE database to determine if this will work for you. For a complete purging, I'd go with purging cached queries, as that's the "full" solution.
On your version, the equivalent tool only offers the option to purge cached queries, so I think that's what you'll need to do. %SYS.PTools.SQLStats.Purge will purge some of the SQL stats data, though not all.
"Purging a cached query purges any related SQL Stats data. Dropping a table or view purges any related SQL Stats data."
edit: corrected, CE 2018.1.5 does not have the purge stats option that IRIS does.
I'm not very familiar with 3rd party tools that could be good for this (I'm sure they exist), but you should be able to design a Caché-role that has the restrictions you are looking for. I think you should be able to use SQL and portal permissions to lock down a role to your specifications.
It sounds like you may be able to leverage mirroring, which is built-in and is an IRIS functionality where separate instances of IRIS can automatically transfer journal files to synchronize their copies of a database. Docs here:
go to post
Hello Andre,
Take a look at these two other posts with very similar questions, maybe you can find something that works for you. Unfortunately, there doesn't seem to be a built-in random function on the SQL side (though on the Objectscript side there is $RANDOM.)
https://community.intersystems.com/post/how-select-random-row-table
https://community.intersystems.com/post/custom-query-returns-random-number-columns
go to post
Hello again Fábio,
These errors are one part of the picture. It would probably be beneficial to open a ticket with the WRC so they can look into these errors and your performance issues to see if they are related/separate.
Do you know what queries are triggering these errors? w3wp.exe is an IIS process but I'm not familiar with EXTRService.exe or KIORAS. Perhaps the IP addresses can also help you narrow down the source.
The <READ> errors indicate something like a problem reading from the socket, but that doesn't explain the cause. There may be other logs you can review in your environment to help explain this.
go to post
To add-on to my initial post, I do think that if you use the same type of outbound adapter and inbound adapter (ie counted), it would be expected for the message to be accepted. Since you're sending XML I wonder why the operation isn't using the counted XML adapter. Hopefully, somebody with expertise in this area can help you.
go to post
Hello Sai,
You might need to give more details on what your operation/service code looks like to determine what's going on. The WRC or your InterSystems rep could probably help with this.
The TCP adapter (including counted) docs can be found here. There are similar docs for the outbound, but it's pretty much the same but swapped for outbound:
Overview of Inbound TCP Adapter
"EnsLib.TCP.CountedInboundAdapter supports incoming TCP connections over which a TCP client and TCP listener exchange blocks of data, with the block length specified in the first 4 bytes of the block. The adapter uses the block length to acquire the meaningful portion of the data from the client application."
The above doc page also includes some guidance for setting up a TCP service.
Is there a particular reason you are using a Counted operation/service rather than generic TCP? I'm unfamiliar with the use case for the Counted adapters.
Connecting Systems with Interoperability Productions > Introducing Interoperability Productions > Connectivity Options
The above suggests that the counted adapters are meant for handling "counted data blocks".
The error you are getting seems to indicate a mismatch between the block size being sent, and the expected size. Looking at the traffic in a Wireshark could be helpful to understand what is happening.
go to post
Hello Fábio,
I'm just quoting the class reference I linked:
"This method is called to remove data from the %SYS.PTools.SQLStats table. It does not remove data from %SYS_PTools.SQLQuery, those rows are cleaned up when a query is compiled"
You can take a look at what's actually filling up your CACHE database to determine if this will work for you. For a complete purging, I'd go with purging cached queries, as that's the "full" solution.
go to post
Hello Fábio,
In IRIS there is a convenient SQL Runtime Statistics page where you can purge those stats.
Using the SQL Runtime Statistics Tool
On your version, the equivalent tool only offers the option to purge cached queries, so I think that's what you'll need to do. %SYS.PTools.SQLStats.Purge will purge some of the SQL stats data, though not all.
"Purging a cached query purges any related SQL Stats data. Dropping a table or view purges any related SQL Stats data."
edit: corrected, CE 2018.1.5 does not have the purge stats option that IRIS does.
go to post
Hello Alex,
Have you confirmed that the original transaction process goes away when the VPN disconnects? How are you opening the terminal in the first place?
If disconnecting the VPN leaves the original process around, it won't have a chance to rollback the transaction.
edit: perhaps this is related to your Journal Freeze on error setting?
go to post
Hello Kevin,
I'm not very familiar with 3rd party tools that could be good for this (I'm sure they exist), but you should be able to design a Caché-role that has the restrictions you are looking for. I think you should be able to use SQL and portal permissions to lock down a role to your specifications.
Using Custom Resources with the Management Portal
SQL Privileges and System Privileges
go to post
Hello Eudoro,
It sounds like you may be able to leverage mirroring, which is built-in and is an IRIS functionality where separate instances of IRIS can automatically transfer journal files to synchronize their copies of a database. Docs here:
Mirroring Architecture and Planning
go to post
Rico,
WRC stands for Worldwide Response Center and is short for InterSystems Support.
https://www.intersystems.com/support-learning/support/