Find

Article

Robert Cemper · Jan 23, 2023 2m read

Open Exchange

In general Global Streams are data objects embedded in Classes / Tables.
Using and viewing them with SQL is normally a part of the access to the containing tables.

SO WHAT?

During debugging or searching for strange or unexpected behavior there could be the need to
get closer to the stored stream. No big problem with direct access to Globals with SMP or Terminal.
But with SQL you are lost.
So my tool provides dynamic access to Global Streams wherever you may need this
Special thanks to @Oliver Wilms for the inspiration for this tool.

Mapping of globals to SQL is a rather traditional art from past.
Though Global Streams are somehow specialized.
But even as their Object classes have changed their representation in Global is the same.

The tricky point is that we see 4 different types of Global Streams in a common structure
Only the embedding Class /Table knows the meaning of the content.

%Stream.GlobalCharacter - raw text
%Stream.GblChrCompress - zipped text
%Stream.GlobalBinary - raw binary sequence
%Stream.GblBinCompress - zipped binary sequence

The Global itself has no indication, of what format it holds.
Dumping the Global just helps for raw text, the rest needs special treatment.
In combination with SQL you meet the problem of maximum field lengths.

I cover this issue by mapping all 4 types over the same stream and the user decides.
In addition, the total size and number of subnodes is also available.
For string manipulation, the first subscript level is also available as "body" VARCHAR
The Global Stream to examine is provided by a static where clause like this:

select * from rcc.gstream where rcc.use('^txtS')=1

WinSQL is friendly enough to let you see the full content. eg. for id=1 chr

and czip

with SQL the compressed data can be viewed unzipped.

Video

GitHub

1 Comment

Discussion (1)1

Question

Freddy Baier · Jan 10, 2023

#Globals #Terminal #Caché

Existe algum comando que retorna a quantidade de linhas de uma global?

Exemplo:

^test(1)="aa"
^test(2)="aa"
^test(3)="aa"
^test(4)="aa"

Total de linhas = 4

2 Comments

Discussion (2)2

Please note that this post is obsolete.

Question

Vishnu G · Dec 14, 2022

#HL7 #InterSystems IRIS for Health

Does InterSystems has CDS Hook implementations?
if yes, where I could get the details.

7 Comments

Discussion (7)3

Article

Megumi Kakechi · Nov 28, 2022 2m read

#SQL #Tips & Tricks #InterSystems IRIS #InterSystems IRIS for Health

Question:

IRISでは、PostgreSQLやMySQLで使うことができる、開始位置や取得件数を指定する LIMIT句やOFFSET句をサポートしているでしょうか？

Answer:

※2025/4/17更新：IRIS2025.1 以降のバージョンでは、LIMIT/OFFSET句をサポートするようになりました。ご参考

残念ながらサポートしていません。
ただ、代わりに使える同様の方法がありますのでご紹介します。

以下のようなSQLクエリをIRIS SQLで行うとします。

SELECT *
  FROM Sample.Person
ORDER BY Name
 LIMIT 3 OFFSET 5

---------------------------------------------------------------------------------
1. サブクエリとビュー ID (%VID)を使用する方法
---------------------------------------------------------------------------------

IRISでは、ビューまたは FROM 節のサブクエリで返される各行に整数のビュー ID (%VID) を割り当てることができます。
%VIDを使用すると、以下のサンプルのようにして同様のことが実現できます。
※%vidについて

SELECT *, %vid FROM (SELECT top all ID, Name
                     FROM Sample.Person
                     ORDER BY Name) v
WHERE %vid BETWEEN 6 AND 8
// 6番目から3つ分 --> 8番目まで

---------------------------------------------------------------------------------
2. OFFSET目までのデータを除いて TOP する方法
---------------------------------------------------------------------------------

SELECT TOP 3 ID, Name FROM Sample.Person WHERE ID NOT IN (SELECT TOP 5 ID
                 FROM Sample.Person
                 ORDER BY Name)
ORDER BY Name

---------------------------------------------------------------------------------
3.row_number() 関数を使用する方法
---------------------------------------------------------------------------------

IRIS 2021.1以降でサポートされるようになったウィンドウ関数の ROW_NUMBER() を使用して実現することも可能です。

SELECT * FROM (
  SELECT row_number() OVER (ORDER BY Name) AS rn, ID, Name
  FROM Sample.Person 
) AS e 
WHERE e.rn BETWEEN 6 AND 8 ORDER BY Name

是非お試しください。

Discussion (0)0

Question

Oliver Wilms · Nov 23, 2022

#Containerization #Enterprise Message Bank #InterSystems IRIS #InterSystems Kubernetes Operator (IKO)

My team works on implementing an Interoperability solution utilizing InterSystems Kubernetes Operator on Red Hat OpenShift container platform.

We are trying to determine how many messages we can process in any given time. We have a Feeder app running in 10 containers sending 50k messages each to a load balancer all beginning at the same time.

Messages are received via HTTPS protocol by webgateway containers.

Interoperability production runs in compute pods with persistent data, journals, and WIJ volumes.

We implemented Horizontal Pod Autoscaler to scale compute pods when CPU utilization is high.

We utilize Enterprise Message Bank to have one place to find any message processed by any compute.

We observe the queue for Message Bank operation grows quite large in compute pods.

Sometimes the Autoscaler scales computes down while they are still processing.

How likely is it Messages do not show up in Message Bank if they have been partially or completed processed in compute pods?

Any messages in queues on compute pods which are shutdown will not be processed until the compute pods is started again.

1 Comment

Discussion (1)2

Global-Streams-to-SQL

Como saber o tamanho (quantidade de linhas) de uma global com comando direto

Does InterSystems has CDS Hook implementations?

IRIS SQLでは LIMIT/OFFSET句のような機能をサポートしていますか？

What is the likelihood of encountering "missing messages" in message bank?