New post
AllAccepted answers
Andreas Schneider · Apr 28

You can change the default schema (= what is used if no schema is specified in the query) here:


However, if you use the schema extensively in your data model to structure the model, you should always use the schema in the query. Working with fully qualified object names is simply best practice and avoids misunderstandings. It seems better not to rely on such things like a default schema because it can changed (or diff from server to server). This becomes even more important when the same object names exist in different schemas.

jm2c
Andreas

Andreas Schneider · Mar 12, 2023

I'm not sure what the reason is but with 2023.1 there are some errors in %SQL_Diag.Result when loading data from my datasets. The errors are different from 2022.3.
After changing some columns in my data model from NOT NULLABLE to NULLABLE the errors are gone and the performance with IRIS 2023.1 is the same as with 2022.3

Andreas Schneider · Mar 12, 2023

I've upgraded my small test project https://github.com/andreas5588/openflights_dataset from 2022.2.0.368.0 to IRIS 2023.1.0.207.0 and I've discovered that the SQL execution time was much slower than expected.
Within this project I load the data using "LOAD DATA FROM FILE" sql statement and it looks like that this is taking up the time.
A "docker-compose build" with IRIS 2022.2.0.368.0 took about about 17 seconds on my machine, the same with IRIS 2023.1.0.207.0 took 300 seconds!
The image with IRIS 2022.2.0.368.0 is 2.87Gb and the image file with IRIS 2023.1.0.207.0 image is 3.21Gb.

This is the only change I've made https://github.com/andreas5588/openflights_dataset

Any idea whats going on?

Andreas
 

Andreas Schneider · Dec 26, 2022

That looks strange. If you execute the query via JDBC the error ist the same. A CAST to the same datatype as provided from Config.ConfigFile works:

SELECT
       CAST(ID AS VARCHAR(512)),
       CAST(CPFName AS VARCHAR(255)),
       CAST(Comments AS VARCHAR(4096)),
       CAST(Name AS VARCHAR(64)),
       CAST(Product AS VARCHAR(64)),
       CAST(SectionHeader AS VARCHAR(255)),
       CAST(Version AS VARCHAR(7))
FROM Config.ConfigFile
Andreas Schneider · Aug 14, 2022

Hi! Unfortunately there is no way to UPDATE the %DESCRIPTION information on a table or column with SQL.
But you can add these information while CREATEing a table like this:

or this way for a column:

Please see CREATE TABLE | InterSystems SQL Reference | InterSystems IRIS Data Platform 2022.1 for more details.

By default the %DESCRIPTION information is projected to the Remarks column via JDBC:

As you can see the text comes from the %DESCRIPTION property:

This works also for the columns.
This is from JDBC:


This is from INFORMATION_SCHEMA.COLUMNS:



Andreas

Andreas Schneider · Jan 9, 2022

I think it is always a good idea to avoid reserved words in the data model. Even if the quick import of a CSV tempts to take it not so exactly. Especially because messages from IRIS don't always point exactly to the cause of the problem ... or it leads to problems later. This saves unnecessary troubleshooting.

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

happy loading! ;-)

Andreas

Andreas Schneider · Jan 3, 2022

Maybe you haven't send the complete statement?
This works for me via JDBC, tested with Caché2018.1.0.184  and IRIS 2021.2.0.617

LOCK TABLE dc_data_flights.airports IN EXCLUSIVE MODE

-- OR
LOCK dc_data_flights.airports IN EXCLUSIVE MODE

-- OR
LOCK TABLE dc_data_flights.airports IN SHARE MODE

-- OR
LOCK dc_data_flights.airports IN SHARE MODE

If try this I get the same error like you:

-- this create an error, the "IN EXCLUSIVE MODE" or "IN SHARE MODE" is missing
LOCK TABLE dc_data_flights.airports

Andreas