All
Accepted answers
Ali Nasser · May 22 go to post

Hi Ethan, you should check out the SQL Specialist exam page, it contains a list of resources to help you prepare for the exam (for example, this great presentation on optimizing queries). You can also browse SQL courses on our online learning platform. To practice using SQL in IRIS, you can enroll in this course that that allows you to lunch a learning lab where you can run queries in IRIS in virtual environment as you follow along with its content.

If you have any questions about the exam itself, feel free to post here or send an email to certification@intersystems.com

Ali Nasser · Apr 23 go to post

This is not a trick but a feature that I don't see used very often, but defining a unique index provides you with auto generated methods to get an oref to a row in a table by column value rather than by knowing the %ID of the row.

For example, here's a simple table with two columns:

class DC.Example Extends %Persistent {
    Property Name As %String;
    Property DOB As %Date;
    
    Index NameIDX On Name [Unique];
}

Let's insert a value into this table:

INSERT INTO DC.Example(Name, DOB) VALUES('John',TO_DATE('01 Jan 1986'))

Suppose you know that one of the rows in a table contains the name "John" but you want to get their DOB, you can get the oref by using an auto-generated method called "NameIDXOpen()" like so:

set oref = ##class(DC.Example).NameIDXOpen("John")
write $ZDATE(oref.DOB) //prints out "01/01/86"

For the curious: https://docs.intersystems.com/iris20261/csp/docbook/DocBook.UI.Page.cls…

Ali Nasser · Mar 31 go to post

The open error location feature is specially helpful. I no longer have to count lines while trying to remember which type of comment counts as a line for offset purposes.

Ali Nasser · Mar 18 go to post

I'd argue that Python generators should be in this list too. They are like a genie that can generate a rock sample on the fly without having to store the rocks in a big box.

Ali Nasser · Mar 17 go to post

An attacker might supply a malicious value such as 0; DROP TABLE Patients, with disastrous results.

Though I agree that you shouldn't concatenate user input into dynamic SQL, this classic SQL attack wouldn't work in IRIS as it doesn't allow you to run more than one command in a single execution.

You can try it yourself, you will see that when you %Prepare your query that has "0; DROP TABLE XYZ" then IRIS will throw an error that says:

ERROR #5540: SQLCODE: -25 Message:  Input (;) encountered after end of query^ SELECT ...

Ali Nasser · Mar 12 go to post

I added a line to clarify that either Sample.Person or a subclass are valid arguments. Appreciate your feedback.

Ali Nasser · Mar 9 go to post

I didn't know that iFind indexes can be used to search through streams. This is very useful information

Ali Nasser · Mar 6 go to post

I updated the end date of the beta period to April 30th to coincide with the end of READY 2026

Ali Nasser · Feb 17 go to post

Sure, let's create a class like this:

Class Sample.DC
{
ClassMethod RunMe() As %Status
{
        Set x = 1
        do ..NestedOne()
}
ClassMethod NestedOne() As %Status
{
        Set y = 2
        do ..NestedTwo()
}
ClassMethod NestedTwo() As %Status
{
        set z = 3
        set err = z / 0
}
}

If you go to your terminal and do ##class(Sample.DC).RunMe() then you will get this error:

<DIVIDE>NestedTwo+2^Sample.DC.1

Your terminal will also show this prompt:

USER 4d1>

You can examine the variables in the NestedTwo classmethod to determine what went wrong by printing variables in that stack frame, for example: write z. However, you can't print out the values of y and x because they are in a parent stack frame. Instead, you use quit <number> to go back up to the NestedOne and RunMe frames where you can print out the values of y and x. For example, quit 1 will go to NestedOne where the var y is defined.

Ali Nasser · Feb 14 go to post

Whether I use Quit or Return depends on what I'm trying to do. If the aim is to just terminate a method and/or return a value then I go for Return. But if I'm within a code block that I'm trying to escape out of, then I use Quit (as long as that block isn't an if/else statement then I need to resort to trickery to get out of it without ending the routine)

Quit also has a neat feature when used to debug from the terminal in that it allows you to go back up the call stack by giving it a numeric argument.

Ali Nasser · Oct 24, 2025 go to post

If you annotate your Rust function with the #[rzf::rzf] attribute, how can you call it from your ObjectScript code? The presentation slides were a little cut off in the video so I can't see that part.

Ali Nasser · Oct 15, 2025 go to post

I do have the source, and I ended deleting and recreating the class from it.

Ali Nasser · Aug 8, 2025 go to post

You only need to install this in one place, in the last method or routine call in your call stack. If your stack is like this AAA -> BBB -> CCC^ABC -> XXX^XYZ, then you need to put Stuart's code in a method called by XXX^XYZ

For example, assume you have these classmethods

ClassMethod GrandParent()
{
    do ..Parent()
}
ClassMethod Parent()
{
    do ..Child()
}
ClassMethod Child()
{
    do ..Inspector()
}
ClassMethod Inspector()
{
    FOR i=0:1:$STACK(-1)-1 {
      SET s=$STACK(i,"PLACE")
      SET retloc=$PIECE(s," ")
      TRY { SET code=$TEXT(@retloc) } CATCH { SET code="n/a" }
      WRITE !,retloc," (",$PIECE(s," ",2),") -> ",code
    }
}

Then if you call GrandParent(), then it will print this:

@ (+1) -> n/a
GrandParent+1^Sample.Test.1 (+1) ->     do ..Parent() }
Parent+1^Sample.Test.1 (+1) ->     do ..Child() }
Child+1^Sample.Test.1 (+1) ->     do ..Inspector() }
Ali Nasser · Jul 21, 2025 go to post

Thank you for making this post Ben.

I highly encourage anyone who uses CCR with TrakCare to fill out the survey and provide their opinion on what the exam should cover.

Ali Nasser · Jul 11, 2025 go to post

This was an interesting read. It's always educational to me to see how people use Angular with IRIS as someone who started using Angular recently too.

If you want to refine your code, then I would recommend migrating away from BehaviourSubjects and decorators to using Signals (in fact, I would say to minimize using RxJs if you can get away with it while we wait for more widespread javascript native observables support). Similarly, Zoneless with OnPush change detection seems to be the way of the future.

If you are taking suggestions on future directions for this series then I'm interested to see how Auth Guards can work with IRIS to secure REST endpoints.

Ali Nasser · Jun 3, 2025 go to post

It's weird that your extent size is showing up as 3521840 but your SELECT COUNT(*) says 49468

Maybe you need to rebuild pxfactidIndex?

Ali Nasser · Jun 3, 2025 go to post

I think the difference in relative costs makes sense. In the "WHERE pxfactid IS NULL" query, you are only looping over a specific subsection of the global. This is similar if you have a query that says SELECT COUNT(*) FROM SomeTable WHERE pxfactid  = 'hello'

In the second query, you are not looping over subrange of nodes that have a specific value, instead you are looping over the entirety of the index and testing whether its content do not match null. This is equivalent to SELECT COUNT(*) FROM SomeTable WHERE pxfactid  != 'hello'

Ali Nasser · May 22, 2025 go to post

Hi Anthony,

Preparation material are listed in the exam page of each exam. You can find the list of exam pages here.

Let's say for example that you are taking the InterSystems HL7 Interface Specialist, then a list of applicable preparation material are found in this page, under the heading "Recommended Preparation".

Ali Nasser · May 14, 2025 go to post

Can you share your working query and the query that is getting the privilege violation? 

Ali Nasser · May 7, 2025 go to post

It looks like the exam browser tried to access a Windows wifi api and was prevented from doing so. The reason could be that you didn't start the exam browser with admin privileges or you are using a work computer and your user account doesn't have sufficient privileges to access this wifi api.

If using a work computer, then one solution is to contact your IT department and ask to elevate your account privileges so that exam browser can start correctly, or to use your own personal device to take the exam.

Feel free to reach out to our exam vendor, for support with starting the Safe Exam Browser.

Ali Nasser · May 5, 2025 go to post

Hi Yuhong,

The InterSystems HL7 Interface Specialist certification exam is not an open book exam. It consists of a series of 68 multiple choice/multiple response questions. You can read more about the exam here.

If you already have the InterSystems HL7 Interface Specialist certification, and that certification is within 6 months of its expiration date, then you have the option of extending your certification by 5 more years by taking the performance-based recertification project. In this project, you are given a vm with an IRIS instance and your task is to create an HL7 production according to the provided specification. You can read more about the recertification project here.

Please feel free to ask any more questions here or you can email your questions to certification@intersystems.com

Ali Nasser · Apr 25, 2025 go to post

Dynamic SQL offers flexibility, as queries can be constructed and executed at runtime, making it ideal for scenarios with unpredictable or highly variable query needs. Conversely, Embedded SQL emphasizes stability and efficiency by integrating SQL code directly into application logic, offering optimized response times for predefined query patterns.

Just a quick note about Embedded vs Dynamic: in recent versions of IRIS, both types are compiled at runtime and both make use of Universal Query Cache, the differences in efficiency and performance should be minor, if any.

Ali Nasser · Apr 14, 2025 go to post

I think the benefit of SQLProcs is that your stored procedure is executed as your data is retrieved. This means that data retrieval and processing happen in one fell swoop rather than being broken into two stages where you loop over your table to retrieve data then loop again over the results to process them. This, I hope, can save you a lot of compute time. Of course, this is all conjecture, the best way to know is to benchmark both approaches.

Wrapping your application logic in a query is a great idea. You are basically delegating the task of retrieving your data in the most optimal way to the SQL Engine, so that the engine has to figure out whether to use indexes, parallelize data retrieval, create temp file to store intermediary results, clean up etc rather than you having to do it.

Ali Nasser · Apr 10, 2025 go to post

Hi Michael,

I'm not an expert nor a programmer so take the below with your most skeptical grains of salt:

When it comes to SQL pagination of results, do you know about the new SQL server-side pagination in 2025.1? This can help you limit what you are sending to the client. If you are on older versions then you can use the TOP clause to control the number of returned results

"I would admit our tables aren't best optimized for SQL in the sense that we don't utilize parent-child relationship in tables rather we point to other tables as property object references and often index on those pointers. I don't think this is uncommon, yeah?  I think it becomes an issue when we are accessing a property by the pointer pointing to another pointer and so on until we make the object reference we want."

I can't speak to how common this is but what you are describing here is impacted by swizzling. It can become an issue by taking up a lot of memory as you load deeply nested objects. How big of an issue depends on the size of your data, how long are objects kept in memory, how capable is your system etc.

Two more issues, you need to watch out for, are concurrency and privilege checking, but these aren't specific to accessing nested objects

"- I assume this next concept is common: depending on the table, some of the data reported to the client is calculated rather than specifically derived from the table or pointers. Or the data itself may be a link so there's an <a>Data Name</a> around it. This means if we need to filter data based on the calculated data we can't rely on just the SQL.  We call a base query and then load the resulting object IDs into an array and then loop through the array and process, transform and filter the data in ObjectScript. "

You can have computed properties so that instead of querying on the field that has "<a>Data</a>", you instead do a SQL query on a computed field that does the processing you need on "<a>Data</a>"

You can also do that processing before your data is saved so that your INSERTS or UPDATES only save the processed data, and you save yourself some compute time when querying your data. If you want something elaborate then you can setup triggers to execute that processing step on UPDATEs or INSERTs

"- Maybe that previous point isn't so common?  Do I need to buckle down and learn how to get a lot fancier in SQL? We have methods that transform data in ObjectScript, but I don't think I can call those within ISC SQL.  But really, at the end of the day, the SQL translates to a bunch of $ORDER-ing through indexes and such to get the data we need, right?  So by calling a base query and then further processing in ObjectScript, I'm just doing what the SQL would have been doing in the guts.  I know I'm oversimplifying, but does that make sense?"

You can call ObjectScript methods from SQL (and vice versa). If the methods you are using are class methods, then they can be exposed as stored procedures by adding the SqlProc keyword to the method signature. You can also create a stored procedure (that uses either SQL or ObjectScript in its code body).

What you are saying makes sense but I don't think is true. If you call a base query then use ObjectScript to further process the results then aren't you looping over the data twice? The first time is when you get it and the second when you are processing it in ObjectScript. I think that by using computed fields and sql procs, then your SQL query will be more efficient than using a base query + objectscript processing.

"A use case for example..."

I'm a little confused by this. Does the user have to specify which field does they want to look for "Sally" in or do they have the freedom to just say "Return all rows with all of their fields if any of their fields have the word Sally in it". If we are talking about the former then you can use Dynamic SQL to construct SQL queries on the fly that only target the columns that need to be filtered. If it is the latter then I think we're getting into the territory of full-text search or even vectors/embeddings and that's out of my league 

Ali Nasser · Nov 14, 2024 go to post

Maybe the query optimizer doesn't see any performance improvements from running the query in parallael? You can use the "Alternate Show Plans" tool to see what other query plans were considered.

Ali Nasser · Jun 4, 2024 go to post

Hi Fei,

No, Optimizing Your SQL Queries is a 41-minute video that covers important concepts relevant to both designing and using InterSystems IRIS SQL. Its inclusion in both sections is intentional.

Ali Nasser · May 20, 2024 go to post

Hey Enrico, thanks for catching this. The SQL Best Practices Doc link is redundant and will be removed from the post.