Hi,

Below To_Char function is not working as expected

SELECT TO_CHAR('12/31/1840','mm/dd/yyyy') ---01/12/1841

I expect the result to be 12/31/1840. Let me know If there are any suggestion to get the correct result.

Thanks, Meena

Hi,

I am getting below error in date column in a table. I am unable to query data or find distinct or to_char on this column.

Select distinct column name from table Select column name from table Select To_Char(column name) from table

Table has 3923509 rows.

Could someone help me how can I identify bad data in this table?

Any idea how we can avoid this error?

[SQLCODE: <-400>:<Fatal error occurred>] [Cache Error: <<VALUE OUT OF RANGE>%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.1>] [Location: <ServerLoop - Query Fetch>] [%msg: <Unexpected error occurred: <VALUE OUT OF RANGE>%0AmBs1+1^%sqlcq.SMTKTUAT.cls498.

I would like to know if there is a way of having a callback or something similar, on persistent classes that is always called after the execution of the operation (failed or successfull).

%OnOpen is executed prior to the action, and there is no post callback

%OnAfterDelete and %OnAfterSave are executed after a successfull operation

I know that 1-1 relationships are not officialy supported by intersystems cache/iris so I want to know the best way to store data with this kind of data model.

Currently I have two classes that where implemented some time ago:

Table A with a relationship type one on table B

Table B with a relationship type one on table A

To compile I have a double compile with qualifyer U.

What is the best way to implement a data model with 1-1 relationships?

Thank you

The InterSystems IRIS has functions that allows create DIWK digital services. A few products have the ability to transform data into wisdom, according to the following pyramid.

With IRIS we build data in many formats and purposes (SQL, Document and Object). Using InterSystems IRIS Reports (JReport) or creating business classes exposed as REST or WS services to deliver to applications and people for consume. So knowleage can be also produced from data and information creating BI and AI projects (with IntegratedML or ML Toolkit).

I have a set of strings that contains special characters. Im trying to write a sql query to remove all the special characters in the string. Im using Intersystems cache sql.

Example

%Lis&ten  - Listen

What is the best free/open source option to generate the database diagram (MER) to the iris or cache persistent classes?

About regulations

Personal data privacy regulations have become an indispensable requirement for projects dealing with personal data. The compliance with these laws is based on 4 principles:

1. Compliance with the rights of the holder of personal data;
2. Governance of personal data assets;
3. Privacy by Design and by Default;
4. Data protection.

Sometimes you need quickly and easily import data into IRIS. For this, an IRIS import manager has been developed.

This application allows you to import JSON data and also provides a really simple interface for transferring data from MongoDB collections to IRIS globals. It has never been so easy.

Let's look at examples.

Import JSON

Suppose we have data in JSON format and we want to import it into IRIS. To do this, you just need to specify the global name and insert the data. After import, you will see the imported data. If global exists data will be overwritten.

In this article I'd like to share with you a phenomena that is best you avoid - something you should be aware of when designing your data model (or building your Business Processes) in Caché or in Ensemble (or older HealthShare Health Connect Ensemble-based versions).

A More Industrial-Looking Global Storage Scheme

In the first article in this series, we looked at the entity–attribute–value (EAV) model in relational databases, and took a look at the pros and cons of storing those entities, attributes and values in tables. We learned that, despite the benefits of this approach in terms of flexibility, there are some real disadvantages, in particular a basic mismatch between the logical structure of the data and its physical storage, which causes various difficulties.

Introduction

In the first article in this series, we’ll take a look at the entity–attribute–value (EAV) model in relational databases to see how it’s used and what it’s good for. Then we'll compare the EAV model concepts to globals.

Sometimes you have objects with an unknown number of fields, or perhaps hierarchically nested fields, for which, as a rule, you need to search.

Take, for example, an online store with diverse groups of products. Each product group has its own unique set of properties and has common properties as well.

Hi, we have mirrored databases, now we have three nodes. Primary, backup, async. They are sync by journals and with some TB each node (expensive storage).

I would like to have a scenario where the two nodes (Primary and backup) have the same amount of messages (purge task of x days) , both as failover, but the asynchronous node should have more messaging, as much as the storage allows us. We want to use this node so provider can give support(search old messages), but not have databases in production that are so large that they are hardly consulted.

Any ideas?

Thanks in advance!

I've been reading the documentation guide for 2018.1 over frozen query plans several times in the last days (link) and there is an answer I can't seem to find directly.

I hope you already know one of my projects Cache Blocks Explorer. I've recently renamed it to Blocks Explorer.

For the recent contest, I've added a new feature, the ability to generate a static picture of any Cache or IRIS database. Like below. Where unique globals have a unique color. This is how looks like inside 9.5GB database. Where 1 pixel represents one block. By link on image you will get even bigger image, with more detalization.

Is there a way to query the database structure?  In SSMS there are queries for finding tables with a column with a certain name (using LIKE).  And there is the redgate tool SQL Search.  But I'm not sure how to go about looking for columns that have say a value of 'PATID' and returning all tables that match.  Does anyone know?

InterSystems IRIS Deployment Guide for AWS using CloudFormation template 

Please note: following this guide, especially the prerequisites section requires Intermediate to Advanced level of knowledge of AWS. You'll need to create and manage S3 buckets, IAM roles for EC2 instances, VPCs and Subnets. You'll also need access to InterSystems binaries (usually downloaded via WRC site) as well as IRIS license key.
 

Aug 12, 2020
Anton Umnikov

Templates Source code is available here: https://github.com/antonum/AWSIRISDeployment

Table of Contents

InterSystems IRIS Deployment Guide – AWS Partner Network.

Hello Community,

Thank you all for your continued feedback and support of our ad hoc reporting platform, VDM.  There's been some questions around setting up a non-ODBC connection for InterSystems platforms.  We published a new YouTube video showing the steps necessary to connect to InterSystems Caché and InterSystems IRIS with BridgeWorks VDM. 

HI,

I am planning to build a WebApp that will have tons of data to display in tables, ability to add comments on that table rows and may be some few more small features as we move on.

1. It has to be Secure

2. It has to be fast

3. It has to be Non CSP / Non Zen

4. Will have Cache DB to pull records from but should be flexible to do that from any odbc resource

5.

Introduction

We used the InterSystems iKnow technology to create a review assessment system called iKnow Reviews Analyzer (iKRA). Some information about the prototype of the system can be found here. iKRA analyzes users’ text reviews and automatically rates the object being reviewed. This functionality may come in very handy on e-commerce sites, forums or collections of media content – in other words, everywhere where people discuss products, places or services, for example.

What does the solution do?

InterSystems IRIS supports a unique data structure, called globals, for information storage. Essentially, globals are persistent arrays with multi-level indices, having several extra capabilities—transactions, quick traversal of tree structures, and a programming language known as ObjectScript.

I'd note that for the remainder of the article, or at least the code samples, we'll assume you have familiarised yourself with the basics of globals:

Globals Are Magic Swords For Managing Data. Part 1.
Globals - Magic swords for storing data. Trees. Part 2.
Globals - Magic swords for storing data.

my cachedb version:
Cache Running version:
Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.1.1 (Build 111U_SU) Tue May 23 2017 13:39:47 EDT

The frequency with which the journal is synced depends on the operating circumstances of the InterSystems IRIS instance involved. A journal sync can be triggered:

• Once every two (2) seconds if the system is idle.
• In an ECP-based distributed cache cluster, by the data server when responding to specific requests (for example, $Increment) from the application servers to guarantee ECP semantics.

My project need to  restore the previous part of the log,  and I use the rountine "^JRNRESTO" and filter.  After recovery completed,  the table structure is restored, but the table data is not restored， also unable to insert data into the table.

I would like to Compact globals in a database to free up space.   

I would begin the process on Saturday morning, but am concerned, due to the size, that it would not complete by Sunday evening.  I understand that the process is setup so that it can run with users on the system, however, as the advice indicates, this would not be ideal.

Can the process be stopped if it does not complete by the time you want/need it to?

Do you know how to guestimate how long the process would take?

This is the Freespace for the database in question.  There are 1047 globals of which one of them takes up 50%.

Greetings community. I would like to know how to migrate a BD in production to a local environment. When I have a system in production (BD Sql Server) what we do is mount a local copy to do the analysis with the data and not occupy resources of the system in production. My question is: How do you do it with Intersystems technology? I already tested the PowerBi connector and it looks great, but that's where the question came up.
 

Hi,

     I am trying to update an year on a date field in a cache database table but it showing an error message. But the functions are working on select query. The query I used is 

update RB_ResEffDateSessPayorRestr SET RESTR_DATETo = DATEADD(YYYY,1,RESTR_DATETo) where YEAR(RESTR_DATETo)=2020

I tried to update only the year which are 2020.

Can anyone please help me, Is there any error in the query?

Thanks

Is there a way to estimate compaction / truncation sizes? For a DB when trying to reclaim disk space.

Basically, is there some utility or CLI command that would allow us to estimate if it is worth the effort of compacting a large database or not.
 

Thanks

Here is an ObjectScript snippet which lets to create database, namespace and a web application for InterSystems IRIS:

	set currentNS = $namespace
	
	zn "%SYS"
	
	write "Create DB ...",!
	set dbName="testDB"
 	set dbProperties("Directory") = "/InterSystems/IRIS/mgr/testDB"
 	set status=##Class(Config.Databases).Create(dbName,.dbProperties)
	write:'status $system.Status.DisplayError(status)
	write "DB """_dbName_""" was created!",!!
	
	
	write "Create namespace ...",!
	set nsName="testNS"
	//DB for globals
	set nsProperties("Globals") = dbName
	//DB for routines
	set nsProperties("Routines") = dbName
	set status=##Class(Config.Namespaces).Create(nsName,.nsProperties)
	write:'status $system.Status.DisplayError(status)
	write "Namespace """_nsName_""" was created!",!!
	
	
	write "Create web application ...",!
	set webName = "/csp/testApplication"
	set webProperties("NameSpace") = nsName
	set webProperties("Enabled") = $$$YES
	set webProperties("IsNameSpaceDefault") = $$$YES
	set webProperties("CSPZENEnabled") = $$$YES
	set webProperties("DeepSeeEnabled") = $$$YES
	set webProperties("AutheEnabled") = $$$AutheCache
	set status = ##class(Security.Applications).Create(webName, .webProperties)
	write:'status $system.Status.DisplayError(status)
	write "Web application """webName""" was created!",!
	
	zn currentNS

Hi Community!

Enjoy watching  the new video on InterSystems Developers YouTube, recorded by @Joel Solon, InterSystems Senior Technical Trainer:

Globals Quickstart