New post
Question
· Jan 12, 2023

Replicating values across processes when executing SQL queries with 1 or more UNION %PARALLEL's

#Key Question #SQL #InterSystems IRIS

Is it possible to retrieve the SQL-Executing process ID (PID) from a process spawned from a UNION %PARALLEL? I have a value set in the SQL-Executing process that I need to replicate in the UNION %PARALLEL spawned process(es). The SQL-Executing process writes the value to a global subscripted by its PID so determining the SQL-Executing PID from the spawned process is what I need. I expected $ZPARENT from the spawned process would hold the SQL-Executing PID but the PID returned is different.

With thanks,

Michael Hill

Product version: IRIS 2022.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2022.1 (Build 209_0_21931U) Wed Oct 5 2022 13:53:48 EDT
Discussion (11)2
Log in or sign up to continue
Eduard Lebedyuk · Jan 13, 2023

As process spawning is expensive, parallel queries are processed using Work Queue Manager. It works by having a queue managing process spawn worker processes at the system startup. When a new parallel query needs to be executed, it is distributed to workers.

That is why the $zparent value is not what you expected. It is a value of a work queue managing process.

You can use it instead, as it's relatively constant.

Before executing your main query, run this query to get Work Queue Manager JobId:

Class Test.Parallel
{

Query Test() As %SQLQuery
{
SELECT Test.Parallel_Parent() UNION %PARALLEL
SELECT Test.Parallel_Parent()
}

ClassMethod Parent() As %Integer [ CodeMode = expression, SqlProc ]
{
$zparent
}

/// do ##class(Test.Parallel).Try()
ClassMethod Try()
{
	set rs = ..TestFunc()
	do rs.%Next()
	write "Work Queue Manager Job: ", rs.%GetData(1)
}

}

Then, store your data subscribed by Work Queue Manager JobId, and all Work Queue Workers can pick it up using $zparent.

0 0
Benjamin De Boe · Jan 13, 2023

Can you provide a little more context on what your subqueries are trying to accomplish? Conceptually, it feels a little unnatural to force PID awareness into SQL (sub)queries, so maybe this is more an application scenario asking for more direct interaction with the Work Queue Manager. 

In 2022.2, we introduced an Application Metadata Stack that SQL (and in the future other subsystems or application code) uses to stash a little context in the process metadata in order to build things like the SQL Process View. Amongst other things, that metadata helps us tie subquery processes to their parent. Right now we have no public API to interpret this rather technical metadata directly from the stack, but maybe you can convince us there's a use case :-)

0 0
Michael Hill · Jan 16, 2023

Thanks for your replies.

@Eduard - Multiple Users could trigger these queries simultaneously with a different replicable value so storing it against the Work Queue Manager PID doesn't allow for the required flexibility.

@Benjamin - A % Variable holds the value that needs replicating across processes. The value is used in SQL procedure methods for lookups, among other things.

With thanks,

Michael

0 0
Michael Hill · Jan 17, 2023

The % variable is set from a GUI before executing an SQL query. It's used by one or more SQL procedure methods within an SQL query that can be called multiple times & with differing arguments within a single SQL query. The % variable value isn't passed in as an argument to the procedure methods as it is held in the % variable. The arguments to the methods are hard coded in the SQL query but the detail held in the % variable is dependant on what is selected in the GUI at the time of SQL query execution. The % variable holds the ID of an object that contains a number of properties that are used by the SQL procedure methods in the SQL query to lookup data specific to the query execution. This solution works well for queries executed within a single process.

There is already code in place to handle UNION %PARALLEL that relies on the ^%ISCWorkQueue global, which through my testing never appears to exist. The intention of the code is to take the parent PID of the worker (assumed to be the GUI triggered executing process) & use it to retrieve a value held in a global subscripted by the executing process PID & set the % variable equal to it.

e.g.
Executing Process PID = 12345
set ^MyGlobal(12345)=125
Child Process PID = 54321

From Child:
    set group = $g(^%ISCWorkQueue("Worker",$J))  /* $J = 54321 */
    set parentPID = $lg(^%ISCWorkQueue(group,"Settings"),1) /* Expecting parentPID = 12345 */
    %Variable = ^MyGlobal(parentPID) /* Expecting %Variable = 125 */

Therefore, if there is a way to retrieve the PID of the process that queued the unit of work (i.e. the Child) to the Work Queue then it could be job done.

With thanks,

Michael

0 0