New post
AllAccepted answers
Sylvain Guilbaud · Sep 8, 2023

The answer has been given in French here by @Lorenzo Scalese 

You can create a custom class query like this: 

Class dc.Frank
{

Query QueryAFO() As %Query(ROWSPEC = "key1:%String,key2:%String,key3:%String,key4:%String,key5:%String,key6:%String,datavalue:%String") [ SqlProc ]
{
}

ClassMethod QueryAFOExecute(ByRef qHandle As %Binary) As %Status
{
    Set qHandle("node") = $Name(^AFO)
    Quit $$$OK
}

ClassMethod QueryAFOFetch(
	ByRef qHandle As %Binary,
	ByRef Row As %List,
	ByRef AtEnd As %Boolean) As %Status [ PlaceAfter = QueryAFOExecute ]
{
    Set sc = $$$OK
    Set qHandle("node") = $Query(@qHandle("node"), 1, data)

    If qHandle("node") = "" Set Row = "", AtEnd = $$$YES Quit $$$OK
    ; feeds the key x fields based on the subscripts of the global
    For i=1:1:$QLength(qHandle("node")) Set $List(Row, i) = $QSubscript(qHandle("node"), i)

    If i < 6 {  ; if we do not have 6 subscripts, we feed the rest with an empty string
        For j = i+1:1:6 Set $List(Row, j) = ""
    }

    Set $List(Row, 7) = data, AtEnd = $$$NO
    Quit sc
}

ClassMethod QueryAFOClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QueryAFOExecute ]
{
	Kill qHandle Quit $$$OK
}

/// just for some test data
ClassMethod set()
{
    s ^AFO("Site","Ville")="66722,3743"
    s ^AFO("Site","Ville","111BB","OBT")=",MMM,XXX,"
    s ^AFO("Site","Ville","111OW","OBT")=",XXX,MMM,"
    s ^AFO("Site","Ville","AANVRBIBS","zzz")    =    "1^^1"
    s ^AFO("Site","Ville","AANVRBIBS","zzz","*","dut")    =    "*afhalen waar gevonden"
    s ^AFO("Site","Ville","AANVRBIBS","zzz","*","eng")    =    "*Pickup where found"
    s ^AFO("Site","Ville","AANVRBIBS","zzz","*","fre")    =    "*Lieu où trouvé"
}

}

You can then easily exploit it with the following query:

select *
from dc.Frank_QueryAFO()

In terminal mode, you can also simply use this line to display the results:

Do ##class(dc.Frank).QueryAFOFunc().%Display()

For my part, I carried out the test on IRIS 2023.2, there should be no incompatibility, but if you notice a problem do not hesitate to respond with the error message.

Sylvain Guilbaud · Sep 4, 2023

Hi @Yuri Marx,

and today, you can add to this list : 

  1. Embedded Python
  2. Adaptive Analytics (AtScale)
  3. System Alerting and Monitoring (SAM)
  4. InterSystems Kubernetes Operator (IKO)
  5. SQL improvements on performances and capabilities (Global Iterator,  Columnar Storage, Adaptive Parallel Execution, LOAD DATA, etc.)
  6. Kernel enhancements (Mirroring, etc.)
  7. Security enhancements 
  8. etc. (see release notes)
Sylvain Guilbaud · Aug 30, 2023

To activate all system AUDIT events, simply execute the following SQL query from the namespace %SYS :


update security.events set enabled=1 where flags = 1

example :


set tRes = ##class(%SQL.Statement).%ExecDirect(,"update security.events set enabled=1 where flags = 1")

if tRes.%SQLCODE=0 {

set ^["USER"]TRACE("%SYS Security.Events")=tRes.%ROWCOUNT_" successfully enabled"

} else {

set ^["USER"]TRACE("%SYS Security.Events")=tRes.%Message_" SQLCODE:"_tRes.%SQLCODE

}
Sylvain Guilbaud · Aug 29, 2023

NB : for column-oriented storage specified at the table level via the statement WITH STORAGETYPE = COLUMNAR , it should be noted that IRIS leaves itself free to choose for you the most commonly optimal storage (in row or in column), according to the types of data.

Example : 

the following statement :


CREATE TABLE a.addressV1 (
        city varchar(50),
        zip varchar(15),
        country varchar(15)
    )
    WITH STORAGETYPE = COLUMNAR

Will not create any column-oriented storage, due to the risk of too disparate data, due to the number of characters allowed in each column (15 or 50) : 


Class a.addressV1 Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {_SYSTEM}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = addressV1 ]

{

Property city As %Library.String(COLLATION = "EXACT", MAXLEN = 50, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 2 ];

Property zip As %Library.String(COLLATION = "EXACT", MAXLEN = 15, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 3 ];

Property country As %Library.String(COLLATION = "EXACT", MAXLEN = 15, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 4 ];

Parameter STORAGEDEFAULT = "columnar";

Parameter USEEXTENTSET = 1;

while the example given in the article, retains a column (and only one) in column-oriented storage, since having only 5 characters allowed for the zip column.




 CREATE TABLE a.addressV2 (
        city varchar(50),
        zip varchar(5),
        country varchar(15)
    )
    WITH STORAGETYPE = COLUMNAR

Class a.addressV2 Extends %Persistent [ ClassType = persistent, DdlAllowed, Final, Owner = {_SYSTEM}, ProcedureBlock, SqlRowIdPrivate, SqlTableName = addressV2 ]

{

Property city As %Library.String(COLLATION = "EXACT", MAXLEN = 50, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 2 ];

Property zip As %Library.String(COLLATION = "EXACT", MAXLEN = 5) [ SqlColumnNumber = 3 ];
Property country As %Library.String(COLLATION = "EXACT", MAXLEN = 15, STORAGEDEFAULT = "ROW") [ SqlColumnNumber = 4 ];

Parameter STORAGEDEFAULT = "columnar";
Parameter USEEXTENTSET = 1;