New post

Find

Question
· Oct 2, 2024

BP/External SQL Calls are creating IRIS.WorkQueue but is not purging the global

I have been trying to track down an issue we are seeing in our TEST environment with Memory usage.

We have Several BP's for years now that take a HL7 message, parse it apart, and make calls to a Custom EnsLib.SQL.OutboundAdapter to have it execute Insert/Select/Update/Delete stored procedures against a MS SQL Database via JDBC connection. We are using Microsoft's JDBC 12.2 driver to do this.

What we are seeing is that IRIS.WorkQueue globals are being defined for these calls but then the IRIS.WorkQueue is not being cleaned up and taking up large amounts of Memory.

I have tried ensuring the context variables are set to "" when we are done with them in the BP, and that the parameter array in the Custom BO is killed once the stored procedure is executed and we have a result.

Why would this not clean up on its own once the HL7 message has been processed (completed) through the BP? Any way to tell why IRIS.Workqueue globals are being created?

5 Comments
Discussion (5)2
Log in or sign up to continue
Announcement
· Oct 2, 2024

开发者社区回顾, 九月 2024

您好,欢迎阅读 九月 2024 开发人员社区通讯。
统计信息
✓ 九月发布了 45 篇新帖子:
 39篇新文章
 4个新公告
 2个新问题
✓ 九月有 47 位新成员加入
✓ 所有时间发布了 2,208 篇帖子
✓ 所有时间有 2,024 位成员加入
最高职位
本月最佳作家
文章
#InterSystems IRIS
 
#InterSystems IRIS for Health
第十二章 WS-Policy 配置类详细信息 - 配置类基础知识
按姚 鑫
第十三章 WS-Policy 配置类详细信息 - 添加扩展属性
按姚 鑫
第十四章 WS-Policy 配置类详细信息 - 配置 XData 块的详细信息(一)
按姚 鑫
第十五章 WS-Policy 配置类详细信息 - 配置 XData 块的详细信息(二)
按姚 鑫
第十六章 WS-Policy 配置类详细信息 - 自定义配置示例
按姚 鑫
第十八章 添加时间戳和用户名令牌
按姚 鑫
第十九章 添加时间戳和用户名令牌 - 时间戳和用户名令牌示例
按姚 鑫
第二十章 加密 SOAP 主体
按姚 鑫
第十七章 手动添加安全元素
按姚 鑫
第二十一章 加密 SOAP 主体 - 变体:使用可识别证书的信息
按姚 鑫
第二十二章 加密 SOAP 主体 - 变体:使用签名的 SAML 断言
按姚 鑫
第二十三章 加密安全标头元素
按姚 鑫
第二十四章 加密安全标头元素 - 基本示例
按姚 鑫
第二十五章 添加数字签名
按姚 鑫
第二十六章 添加数字签名 - 示例
按姚 鑫
第二十七章 添加数字签名 - 变体:使用签名的 SAML 断言
按姚 鑫
第二十八章 添加数字签名 - 指定摘要方法
按姚 鑫
第三十章 使用派生密钥令牌进行加密和签名
按姚 鑫
基于向量搜索的患者相似度匹配示例程序
按Nicky Zhu
第三十一章 使用派生密钥令牌进行加密和签名 - 变体:创建隐式 DerivedKeyToken
按姚 鑫
第三十二章 使用派生密钥令牌进行加密和签名 - 使用 DerivedKeyToken 进行加密
按姚 鑫
第三十三章 使用派生密钥令牌进行加密和签名 - 使用 DerivedKeyToken _进行加密(一)
按姚 鑫
第三十四章 使用派生密钥令牌进行加密和签名 - 使用 DerivedKeyToken 进行签名
按姚 鑫
第三十五章 结合加密和签名
按姚 鑫
第三十六章 结合加密和签名 - 使用对称密钥签名并加密
按姚 鑫
 
#Caché
 
公告
问题
#InterSystems IRIS
活动量
按wei su
 
#Caché
 
九月, 2024Month at a GlanceInterSystems Developer Community
Discussion (0)0
Log in or sign up to continue
Announcement
· Oct 2, 2024

InterSystems Open Exchange Applications Digest, September 2024

Hello and welcome to the September 2024 Open Exchange Newsletter.
General Stats:
19 new apps in
598 downloads in
995 applications all time
36,919 downloads all time
2,865 developers joined
New Applications
iterm
By Dmitry Maslennikov
Code-Scanner
By Robert Cemper
iris-DataViz
By Muhammad Waseem
ks-iris-lib
By Robert Barbiaux
iris-ccd-devtools
By Chi Nguyen-Rettig
db-management-tool
By Andrii Mishchenko
iris-dev-codeinspector
By Rodolfo Moreira
IRIS-API-Template
By Samuel Thomas
IRIS-Log-Monitor
By sara aplin
sql-embeddings
By Henrique Dias
pxw-lib-sql
By Paul Waterman
IPM in VS Code
By John Murray
smart-day-hands-on
By Netanel Frankel
Irisheimer
By Zacchaeus Chok
IOP REST Client Framework
By Antoine Dhollande
thalamus-og
By Audrey Chen
IRIS-Test-Data-Generator
By Dylan Cole
iris-api-interface-generator
By Alex Alcivar
VectorSearchOnPatientSimilarity
By Lin Zhu
New Releases
errors-global-analytics by Evgeny Shvarov
v1.1.1
upgrade to dsw 4+
v1.1.2
ipm version bump
iris-embedded-python-template by Evgeny Shvarov
v3.0.1
fix byRef signature for Embedded Python method
Healthcare HL7 XML by Alberto Fuentes
v3.6.0
  • Fixed installation issues
  • Fixed pre/post 2024 ReadRawDataStream / GetFieldStreamRaw in ITB.HL7.Format.HL7XMLv2
zpm-registry by Evgeny Shvarov
v1.3.1
Added 2 query params includePrerelease and includeSnapshots when searching for compatible versions of a package.
v1.3.2
fix: include prerelease and snapshot by default for compat with older zpm
DeepSeeWeb by Anton Gnibeda
v4.0.9
  • internal build with dev feature for oAuth
v4.0.10
  • fixed issue with addons loading when DSW uses authentication (#331)
v4.0.11
  • internal build with dev feature for oAuth
v4.0.12
  • internal build with dev feature for oAuth
iris-datapipe by Alberto Fuentes
v2.0.1
  • Delayed processing option available
v2.0.2
  • Fixed installation error on IRIS 2022 version
Intersystems-Monitoring by Teunis Stolker
v1.0.17
Use excepton.Data instead of exception.data
v1.0.18
Fixed a mistake for the HTTP status code filter.
v1.0.19
Removed space from behind version number; this was causing install errors
Samples-Aviation by Evgeny Shvarov
v1.0.4
Docker environment simplified
DX Jetpack for VS Code by John Murray
v2024.0.0
2024 edition for Developer Tools Contest, containing three new extensions.
IRIS apiPub by Claudio Devecchi
v1.1.72
improvements & bug fixes
v1.1.73
bug fixes & improvements
apptools-admin by Sergey Mikhailenko
v1.2.6
Enabled support for Django projects via the native Python API library.
Git for Shared Development Environments by Timothy Leavitt
v2.5.0

[2.5.0] - 2024-09-24

Added

  • New UI for the basic mode Sync (#415)
  • Allow changing namespaces and IPM package context from web UI (#280)
  • Support for editing repo from filesystem perspective via web application (#464)
  • Support for downloading a VSCode workspace file from web UI
  • IncrementalLoad pull event handler will update the running production, if any (#473)

Fixed

  • Instance wide settings are placed in proper global (#444)
  • Avoid delay/errors in loading interop JS when there is a URL prefix (e.g., instance name in multi-instance webserver configuration)
  • Added proper JS escaping in sync output
  • Added support to switch branch in basic mode from menu (#451)
  • Pull event handler will not fail when change set includes unmapped files (#453)
  • Pull event handler will attempt compile even if there are failures to load (#457)
  • Improved logging in preview and when errors occur via WebSocket commands (#467)
  • Fixed pull event handler handling of extremely long class names from diff (#467)
  • Fixed Git web UI prompt to update file list when file selected/unselected (#478)
  • Fixed folder settings in mappings to be saved and persist (#483)
  • Preview on the pull.csp page now shows commits from the correct branch (#490)
Most downloaded
MDX2JSON
By Eduard Lebedyuk
DeepSeeWeb
By Anton Gnibeda
WebTerminal
By Nikita Savchenko
ssl-client
By Evgeny Shvarov
iris-bi-utils
By Evgeniy Potapov
interoperability-embedded-python
By Guillaume Rongier
iris-cron-task
By Evgeny Shvarov
isc-json
By Timothy Leavitt
iris-web-swagger-ui
By Maks Atygaev
, 2024Month at a GlanceInterSystems Open Exchange
Discussion (0)1
Log in or sign up to continue
Announcement
· Oct 2, 2024

In-person Developer Meetup on Security - October 15, Cambridge, MA

Hi Community,
We invite you to join our next Developer Meetup in Cambridge, MA on October 15th, 2024.
This time we’ll focus on Data & Security. Our security experts will be sharing their insights and knowledge.

>> RSVP here <<

Agenda:

1️⃣ Security trends, attacks and threats. Practical tools and frameworks
by Mark-David McLaughlin, Director, Corporate Security, InterSystems

2️⃣ Zero Trust Architecture
by Jonathan Sue-Ho, Senior Security Engineer, InterSystems

⏱ Day and Time: October 15, 5:30 p.m. to 7:30 p.m.
📍CIC Venture Café in Cambridge, Massachusetts

Save your seat now!

Food, beverages, and networking opportunities will be provided as always.
Join our Discord channel to connect with developers from the InterSystems developer ecosystem.

Discussion (0)1
Log in or sign up to continue
Article
· Oct 2, 2024 14m read

Control IRIS database schema changes with Liquibase

In the modern world, the most valuable asset for companies is their data. Everything from business processes and applications to transactions is based on data which defines the success of the organization's operations, analysis, and decisions. In this scenario, the data structures need to be ready for frequent changes, yet in a managed and governed way. Otherwise, we will inevitably lose money, time, and quality of corporate solutions.
For a long time, data management and governance were solely based on the data itself, with excellent backup, restoration, ACID (Atomicity, Consistency, Isolation, and Durability) resources, authentication/authorization controls, logging, and tracing. However, since the business has become more dynamic and agile, changes in data structures have generated many outages and system breakdowns. To resolve this, DDL (Data Definition Language) source code management solutions were created. Two of those great solutions have become market references: Liquibase and Flyway. None of them, however, supported InterSystems IRIS until 2023, when Dmitry Maslennikov released a Liquibase extension for IRIS, making it possible to add IRIS DDLs to DevOps pipelines. The IRIS Liquibase extension is an open-source product, and it is currently published on https://openexchange.intersystems.com/package/liquibase-iris.
This article will detail what Liquibase is and how to use it to manage data structures together with the extension created by Dmitry, liquibase-iris.

What is Liquibase?

Liquibase is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production. To start using Liquibase quickly and easily, you can write your migration scripts in SQL. To take advantage of database abstraction abilities that allow you to write changes and deploy them to different database platforms, you can specify database-agnostic changes in XML, JSON, or YAML(https://docs.liquibase.com/concepts/introduction-to-liquibase.html).

Liquibase Workflow


Changelogs

Liquibase uses SQL, XML, JSON, and YAML changelog files to list database changes in sequential order. Those database alterations have the format of changesets that contain Change Types, which are types of operations to apply to the database, e.g., adding a column or primary key. Context, label, and precondition changelog tags help us control precisely when a database modification was made and to which database environment it was deployed. (https://docs.liquibase.com/concepts/introduction-to-liquibase.html).

Liquibase properties file

To set the link between Liquibase and your database, you need to know the database connection information and parameters. Liquibase includes a properties file to store database connection information and parameters that rarely change. Setting those parameters as environment variables to handle sensitive database information or running them at the command prompt is an alternative option.

Liquibase commands

Liquibase runs six basic types of commands: update, rollback, snapshot, diff, status, and utility. When you use the update command to deploy your first alterations, Liquibase checks the database connection information, including credentials, database URL, and JDBC driver.

Database Changelog and Database Changelog Lock

When you deploy your changes, Liquibase creates two tables in your database: DATABASECHANGELOG and DATABASECHANGELOGLOCK.
The DATABASECHANGELOG table tracks deployed modifications for you to have a record. Liquibase compares the changesets in the changelog file with the DATABASECHANGELOG tracking table and deploys only new changesets.

DATABASECHANGELOGLOCK prevents multiple instances of Liquibase from updating the database simultaneously. It manages access to the DATABASECHANGELOG table during deployment and ensures that only one instance of Liquibase is updating the database.

Database management options

Liquibase offers many ways to manage your database changes:

  • Run the command-line client (CLI).
  • Use the Liquibase Java API and integrate Liquibase into your application to deploy database modifications on the application startup.
  • Integrate Liquibase into your build processes using Maven, Spring Boot, Ant, Jenkins, GitHub Actions, or other CI/CD tools.
  • Combine Liquibase with ephemeral environments in Docker.

Step-by-step instructions on Liquibase with IRIS

Install Liquibase

To employ Liquibase CLI, install Liquibase on your OS:
1. Install Java (if you do not have it yet).
2. Download Liquibase CLI (https://www.liquibase.com/download):

3. If necessary, you can download the Windows or Mac installer or simply get the zip file and extract it to any folder.
4. Add the Liquibase installation directory to your system PATH (when you use the Installer, the path is included automatically).
5. From a command line or terminal, type liquibase --version to verify that Liquibase has been installed successfully:



Set Liquibase for your IRIS project

We will use a sample to see Liquibase in action, so follow the next steps:
1. Do a git clone for the project intersystems-iris-dev-template (https://openexchange.intersystems.com/package/intersystems-iris-dev-temp...):

git clone https://github.com/intersystems-community/intersystems-iris-dev-template.git

2. Go to the folder intersystems-iris-dev-template and open the project on VSCode:

3. Open a new Terminal:

4. Run the project:

5. After the project has started, go to the Docker Desktop > Containers and expand intersystems-iris-dev-template; then get the host port for the superport 1972 (in my case it is 51418):

6. Create the file liquibase.properties in the root project folder with the following content (pay attention to the right port on the URL, in my case, it is 51418):

driver: com.intersystems.jdbc.IRISDriver
classpath: ./lib/intersystems-jdbc-3.8.4.jar
url: jdbc:IRIS://127.0.0.1:51418/USER
username: _SYSTEM
password: SYS
changeLogFile: db.changelog.xml

7. Create the file db.changelog.xml in the root project folder with the following content:
 

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
    <changeSet  id="1"  author="yuri">
        <createTable schemaName="dc_sample"  tableName="persons">
            <column  name="id"  type="int"  autoIncrement="true">
                <constraints  primaryKey="true"  nullable="false"/>
            </column>
            <column  name="firstname"  type="varchar(50)"/>
            <column  name="lastname"  type="varchar(50)">
                <constraints  nullable="false"/>
            </column>
            <column  name="state"  type="char(2)"/>
        </createTable>
    </changeSet>
    <changeSet  id="2"  author="yuri">
        <addColumn  schemaName="dc_sample" tableName="persons">
            <column  name="username"  type="varchar(8)"/>
        </addColumn>
    </changeSet>
    <changeSet  id="3"  author="yuri">
        <addLookupTable existingTableSchemaName="dc_sample"
            existingTableName="persons" newTableSchemaName="dc_sample"  existingColumnName="state"
            newTableName="state"  newColumnName="id"  newColumnDataType="char(2)"/>
    </changeSet>
</databaseChangeLog>

8. Using the terminal, execute the command liquibase update on the root folder:

9. Look at the results on the terminal:

All three defined changesets have run successfully.
 

10. Check out the results on Management Portal or on DBeaver:

11. Run the history command to review all the changes that were made:

Liquibase commands

In addition to the update command, the Liquibase is equipped with more than 40 different commands providing its users the ability to execute various database change management operations (source: https://docs.liquibase.com/commands/home.html).

Database update commands

Command Description
rollback It rolls the database back to the state it was in when the tag was applied.
rollback-sql This helper command produces the raw SQL Liquibase that will run when operating the rollbackByTag command.
rollback-to-date It rolls the database back to the state it was in at the given date/time.
rollback-to-date-sql This helper command allows you to inspect the SQL Liquibase will run while employing the rollback-to-date command.
rollback-count It rolls back the last <value> changesets.
rollback-count-sql It writes SQL to roll back the previous <value> changesets to STDOUT.
future-rollback-sql It writes SQL to roll back the database to the current state after the changes in the changelog have been applied.
future-rollback-count-sql It generates the SQL that Liquibase would use to sequentially revert the number of alterations associated with undeployed changesets that have been added to a changelog file.
future-rollback-from-tag-sql It produces the raw SQL Liquibase would need to roll back all undeployed modifications made up to the specified tag.

 

Database inspection commands

Command Description
diff It describes differences between two databases to standardize them.
diff JSON It allows you to compare two databases of the same or different types and use the output in a JSON format.
diff-changelog It adds any differences between the specified databases to a changelog. It can be appended in any supported changelog format.
generate-changelog It generates a changelog from a database when you add Liquibase to a new project. It is synonymous with snapshots except that it saves the output as XML in the changelog.
snapshot It gathers the current database schema and displays this information to STDOUT. There is also an option to save the schema in JSON format and later use that JSON snapshot as a comparison database.
snapshot-reference It captures the current state of the referenceURL database, which is the source database.

 

Change tracking commands

Command Description
history It lists all deployed changesets and their deploymentIDs.
status It outputs the count (or list, if --verbose) of changesets that have not been deployed.
unexpected-changesets It produces a list of changesets that were run in the database but do not exist in the current changelog.

All other commands are documented on the following webpage: https://docs.liquibase.com/commands/command-list.html.

Liquibase Change Types

A Change Type is a database-independent XML, YAML, or JSON formatted modification that you can specify to update your database with Liquibase. Change Types correspond to SQL statements applied to your database, e.g., CREATE TABLE.You determine the Change Type you wish to employ within a Changeset in your Changelog (source: https://docs.liquibase.com/change-types/home.html). This tutorial utilized XML change type. However, it is possible to work with YAML or JSON as well. Look at the samples below:

YAML change type

To use YAML-based changelogs, you must include snakeyaml-<version>.jar in your classpath. In the example below, the changelog contains changesets that do the following (source: https://docs.liquibase.com/concepts/changelogs/yaml-format.html):

  1. It creates a new person table with columns id, firstname, lastname, and state;
  2. It adds a new username column to the person table;
  3. It creates a lookup table state using data from a person.
databaseChangeLog:
- changeSet:
      id:  1
      author:  your.name
      labels: example-label
      context: example-context
      comment: example-comment
      changes:
       - createTable:
            tableName:  person
            columns:
              - column:
                  name:  id
                  type:  int
                  autoIncrement:  true
                  constraints:
                    primaryKey:  true
                    nullable:  false
              - column:
                  name:  name
                  type:  varchar(50)
                  constraints:
                    nullable:  false
              - column:
                  name:  address1
                  type:  varchar(50)
              - column:
                  name:  address2
                  type:  varchar(50)
              - column:
                  name:  city
                  type:  varchar(30)
- changeSet:
      id:  2
      author:  your.name
      labels: example-label
      context: example-context
      comment: example-comment
      changes:
       - createTable:
            tableName:  company
            columns:
              - column:
                  name:  id
                  type:  int
                  autoIncrement:  true
                  constraints:
                    primaryKey:  true
                    nullable:  false
              - column:
                  name:  name
                  type:  varchar(50)
                  constraints:
                    nullable:  false
              - column:
                  name:  address1
                  type:  varchar(50)
              - column:
                  name:  address2
                  type:  varchar(50)
              - column:
                  name:  city
                  type:  varchar(30)
- changeSet:
      id:  3
      author:  other.dev
      labels: example-label
      context: example-context
      comment: example-comment
      changes:
       - addColumn:
            tableName:  person
            columns:
              - column:
                  name:  country
                  type:  varchar(2)

JSON Change Type

When operating a JSON-based changelog file, you need to select one of the following ways to audit your database and execute alterations (source: https://docs.liquibase.com/concepts/changelogs/json-format.html):

  • Pass it as an argument in the command line during runtime:
liquibase update --changelog-file=example-changelog.json
  • Specify it in the Liquibase properties file:
changelog-file: ../example-changelog.json

You can also include other related properties in the properties file, e.g., searchPath, which defines the directories and .jar files to search for changelog files. If you have multiple files, they can be separated with commas. Check out the following sample:

{
    "databaseChangeLog": [
      {
        "changeSet": {
          "id": "1",
          "author": "yuri",
          "changes": [
            {
              "createTable": {
                "tableName": "person",
                "columns": [
                  {
                    "column": {
                      "name": "id",
                      "type": "int",
                      "autoIncrement": true,
                      "constraints": {
                        "primaryKey": true,
                        "nullable": false
                      },
                     
                    }
                  },
                  {
                    "column": {
                      "name": "firstname",
                      "type": "varchar(50)"
                    }
                  },
                  {
                    "column": {
                      "name": "lastname",
                      "type": "varchar(50)",
                      "constraints": {
                        "nullable": false
                      },
                     
                    }
                  },
                  {
                    "column": {
                      "name": "state",
                      "type": "char(2)"
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "changeSet": {
          "id": "2",
          "author": "robert",
          "changes": [
            {
              "addColumn": {
                "tableName": "person",
                "columns": [
                  {
                    "column": {
                      "name": "username",
                      "type": "varchar(8)"
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "changeSet": {
          "id": "3",
          "author": "carlos",
          "changes": [
            {
              "addLookupTable": {
                "existingTableName": "person",
                "existingColumnName": "state",
                "newTableName": "state",
                "newColumnName": "id",
                "newColumnDataType": "char(2)",
               
              }
            }
          ]
        }
      }
    ]
}

SQL Change Type

It is possible to operate SQL as a change type as well. However, database independence will not be guaranteed once product-specific SQL has been written (click the following link for more information: https://docs.liquibase.com/concepts/changelogs/sql-format.html). Take a look at the sample below:
 

--liquibase formatted sql
--changeset your.name:1 labels:example-label context:example-context
--comment: example comment
create table person (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE person;
--changeset your.name:2 labels:example-label context:example-context
--comment: example comment
create table company (
    id int primary key auto_increment not null,
    name varchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
)
--rollback DROP TABLE company;
--changeset other.dev:3 labels:example-label context:example-context
--comment: example comment
alter table person add column country varchar(2)
--rollback ALTER TABLE person DROP COLUMN country;


Using Liquibase on DevOps projects

You can also use Liquibase on DevOps projects. It means that whenever your database creation/modification source code is uploaded to GitHub or GitLab, you can run Liquibase to synchronize the new version with the target database. To do it (when you work with GitHub), you can use a Github action. To see a sample, examine the following link https://github.com/liquibase/liquibase-github-action-example. However, in a nutshell, you must create the folder /.github/workflows/yourfile.yml and write something similar to the code below:
 

on: [push]
jobs:
  test-liquibase-action:
    runs-on: ubuntu-latest
    name: Test Liquibase Action
    steps:
      - uses: actions/checkout@v2
      - uses: liquibase/liquibase-github-action@v2
        with:
          operation: 'update'
          changeLogFile: 'db.changelog.yml'
          username: ${{ secrets.USERNAME }}
          password: ${{ secrets.PASSWORD }}
          url: ${{ secrets.URL }}
Discussion (0)2
Log in or sign up to continue