New post
AllAccepted answers
Dan Pasco · Feb 22, 2022

When not using EXTENTSET mapping, all indexes are stored by default in a single global. To keep the index structures separate the first subscript of the index global, by default, is the index name. This creates two conditions that impact performance negatively. Firstly, the index name subscript creates a longer reference for each index key. That leaves less room for index key subscripts and longer references consume resources. Secondly, the size of the index global is increased and the number of index key values per block is reduced. Fewer key values per block means more blocks read during query execution and a less efficient global cache. The index name subscript is essentially "noise".

Using EXTENTSET mapping removes the need for an index identifying subscript (the global is the index).

Dan Pasco · Feb 22, 2022

First of all, the global names are not random but are based on a hash algorithm to reduce the length of the global name and to reduce the probability of global name collisions. This is the default global name assignment when USEEXTENTSET is true. The benefits of using EXTENTSET mapping are many but primarily, the size of indexes is reduced substantially making index filing is faster. Queries using indexes are also likely faster with USEEXTENTSET mapping.

With EXTENTSET, the storage default global is used as a base value for the set of globals used by the extent. Each index, including the master map/master data index (MDI) - also known as the "DATALOCATION", is the base value (the EXTENTLOCATION) plus ".n" where "n" is a number computed when mapping globals to indexes. The master map/MDI always is ".1". In the original post, the DEFAULTGLOBAL setting overrides the hash computation of the EXTENTLOCATION.

Compare the index reference for a simple name index on Sample.Person between USEEXTENTSET = 1 and USEEXTENTSET = 0. ^Sample.PersonI("PersonNameIndex","DOE, JOHN Q", 100) vs ^Sample.Person.2("DOE, JOHN Q", 100).

The developer has to choose whether to use conveniently named globals or better performance.

Note that not all class definitions/tables will benefit from USEEXTENTSET mapping. But many will.

Dan Pasco · Jan 12, 2022

Yes, of course "inverse" - sorry.

Persistent vs RegisteredObject - not a problem but you are calling a simple class method so we don't need any super class. I used this implementation for the IRIS Class:

Class Utils.CSW1JavaFunctions
{
    ClassMethod IrisReturn(user = "user", pass = "pass") As %Stream.GlobalBinary
    {
         try {
             set cswStream=##class(%Stream.GlobalBinary).%New()
             set cswReturn = {"user":(user), "pass":(pass) }
             do cswReturn.%ToJSON(cswStream)
             return cswStream
         } catch exc {
             write !,"Caught Exception on server: ", exc.AsSQLMessage()
         }
    }
}

 

 

And this is a crude hack at the Java code - the anonymous InputStream class could use more work but it does run for this simple example. I'll leave the rest of the InputStream coding to you.

package utils;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.intersystems.jdbc.*;

import java.io.*;
import java.sql.SQLException;


public class Reader {
    public static final String CACHE_CLASS_NAME = "Utils.CSW1JavaFunctions";

    public IRISConnection connection;
    public IRIS iris;

    public Reader(IRISConnection connection) throws SQLException {
        this.connection = connection;
        this.iris = IRIS.createIRIS(connection);
    }
    public static void main(String[] args) throws SQLException {
        IRISDataSource dataSource = new IRISDataSource();
        dataSource.setServerName("localhost");
        dataSource.setPortNumber(51776);
        dataSource.setDatabaseName("USER");
        dataSource.setUser("_SYSTEM");
        dataSource.setPassword("SYS");
        IRISConnection connection = (IRISConnection) dataSource.getConnection();
        Reader reader = new Reader(connection);
        try {
            JsonNode jsonNode = reader.execute("IrisReturn", "java", "jpass");
            System.out.println(jsonNode.toString());
        } catch (Exception exc) {
            exc.printStackTrace();
        }
    }

    public JsonNode execute(String method, Object... args) throws Exception {
        ObjectMapper mapper = new ObjectMapper();
        JsonNode jsonNode = null;
        try {
            IRISObject data  = (IRISObject) iris.classMethodObject(CACHE_CLASS_NAME, method, args[0], args[1]);
            InputStream is = new InputStream() {
                byte[] buffer;
                int pos = 0;
                int len = -1;

                @Override
                public int read() throws IOException {
                    if (pos >= len) {
                        getBuffer();
                    }
                    if (len == -1) {
                      return -1;
                    }
                    return buffer[pos++];
                }

                void getBuffer() {
                    pos = 0;
                    IRISReference readLen = new IRISReference(3200);
                    String string = (String) data.invoke("Read", readLen);
                    if (readLen.getLong() == -1) {
                        buffer = null;
                        len = -1;
                    } else {
                        buffer = string.getBytes();
                        len = buffer.length;
                    }
                }
            };

            jsonNode = (JsonNode) mapper.readTree(is);
            return jsonNode;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }
}

Running this produces this output:

/usr/lib/jvm/adoptopenjdk-11-hotspot-amd64/bin/java -javaagent:/home/...
{"user":"java","pass":"jpass"}

Process finished with exit code 0

Dan Pasco · Jan 12, 2022

Interesting! I didn't see which version of InterSystems IRIS you are using but you might try a couple of things:

First, don't return a status value from your method. Instead return a %Stream.GlobalBinary instance. If you still want a status, get it some other way. I recommend just throwing an exception - IRIS Native should handle that okay. The object value you get back will be a proxy object that should allow you to read the stream. In our documentation you might search for "reverse proxy objects".

If get some time, I will try to build a sample of doing this.

Dan Pasco · Jan 4, 2022

I like :sql as a way to launch the shell - so that means this alias should be renamed to something else!!

USER>:sql "select top 2 * from person"
do $system.SQL.Execute("select top 2 * from person").%Display()
id    name    ssn    dob    home_street    home_city    home_state    home_zip
1    Willeke,Thelma K.    934-46-2099    18948    308 Madison Blvd    Bensonhurst    HI    71501
2    Nathanson,Will P.    771-91-1008    37359    9664 Second Place    Jackson    VA    15837

2 Rows(s) Affected
Dan Pasco · Jul 8, 2021

I didn't count the number of question marks but I assume you've verified that it matches the number of columns.

Also, there are limitations on the number of command line arguments you can pass but that number is rather high. I don't recall exactly what it is but it is certainly limited. At one time we established 16 as the maximum number of arguments. As I recall, we did nothing to impose an arbitrary limit but ObjectScript itself does have a limit.

My next questions are meant to establish where the error is reported. You can prepare a dynamic SQL statement and then execute it separately and multiple times. Is the error reported during prepare or during execute?

PS: (edited):

Sorry, I see now your code. You are using a HS Adapter to execute this statement. That leads me to a different question - can you prepare and execute this statement as a Dynamic SQL Statement?

set statement = ##class(%SQL.Statement).%New()

set status = statement.%Prepare(.tSQL)

If status is okay (1) then execute it - set result = statement.%Execute( <your parameter values go here>)

Then check result.%SQLCODE.

In the meantime, I will take a look at the Adapter (not my area but I can still look) to see if there is some limitation.

Dan Pasco · Jul 6, 2021

I agree with Herman - to a point. Leave the IDKEY index alone and just accept the system assigned idkey. In most cases this is the best policy. But - there are potentially several "candidate keys", all can be defined - or not, but one can be chosen to be the "primary key". In that case, define the key not as unique but as "primarykey". Some tooling works better with a defined primary key. It does not interfere with the IDKEY unless it is also defined as the "idkey".

Dan Pasco · Jul 6, 2021

Hi Jonathan,

I am very interested in this problem (I wrote the early version of dynamic statement parameter binding). My first question is simple - can you execute this statement using embedded SQL? If you can then we need to dig further to discover the correct error (some errors get masked as syntax when there is a different problem).

Thanks,

Dan

PS:

Of course, you will have to supply values for ? to test compile this as an embedded statement (:hv1, :hv2... can work) - or - can you try this using an SQL utility or as a ODBC/JDBC statement?

Dan Pasco · Apr 28, 2021 
    set java = $system.external.getJavaGateway()
    do java.addToPath("/home/myhome/mariadb-java-client-2.7.2.jar")
    set mariads = java.new("org.mariadb.jdbc.MariaDbDataSource","jdbc:mariadb://myhost:3306/SAMPLES")
    set mariaconn = mariads.getConnection("myuser","secret")
    set pstmt = mariaconn.prepareStatement("select * from person limit 5")
    set jdbcresult = pstmt.executeQuery()
    set jdbcmetadata = jdbcresult.getMetaData()
    for i=1:1:jdbcmetadata.getColumnCount() {
        write jdbcmetadata.getColumnName(i),$char(9)
    }
    while jdbcresult.next() {
        write !,$increment(rowcnt),$char(9)
        for i=1:1:jdbcmetadata.getColumnCount() {
            write jdbcresult.getString(i),$c(9)
        }
    }
    write !!!,"OR I COULD JUST USE MY ALREADY IMPLEMENTED RESULT SET RENDERER FROM ANOTHER JAVA PROJECT",!!!
    do java.addToPath(..#EXTERNALLIBPATH)
    set ers = java.new("external.test.ExternalResult",pstmt.executeQuery())
    do ers.renderTable()
    do mariaconn.close()
    do java.disconnect()

And the results:

USER>do ##class(external.test.MariaJDBC).communityDemo()
name    ssn    dob    home_street    home_city    home_state    home_zip    
1    Basile,Molly M.    452-57-8033    1994-06-02    1153 First Street    Xavier    SD    98033    
2    Cooke,Howard F.    131-62-3894    2017-12-09    5172 Washington Place    Zanesville    NE    44980    
3    Donaldson,Phil R.    480-79-5019    1990-01-23    4429 Elm Street    Miami    WA    67638    
4    Eisenstien,Michael D.    655-11-6334    1948-08-14    4676 Elm Avenue    Reston    KY    52729    
5    Faust,Mo E.    772-42-3921    2018-01-10    826 Maple Avenue    Youngstown    OH    37180    

OR I COULD JUST USE MY ALREADY IMPLEMENTED RESULT SET RENDERER FROM ANOTHER JAVA PROJECT

┌─────────────────────┬───────────┬──────────┬─────────────────────┬──────────┬──────────┬────────┐
│name                 │ssn        │dob       │home_street          │home_city │home_state│home_zip│
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Basile,Molly M.      │452-57-8033│1994-06-02│1153 First Street    │Xavier    │SD        │98033   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Cooke,Howard F.      │131-62-3894│2017-12-09│5172 Washington Place│Zanesville│NE        │44980   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Donaldson,Phil R.    │480-79-5019│1990-01-23│4429 Elm Street      │Miami     │WA        │67638   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Eisenstien,Michael D.│655-11-6334│1948-08-14│4676 Elm Avenue      │Reston    │KY        │52729   │
├─────────────────────┼───────────┼──────────┼─────────────────────┼──────────┼──────────┼────────┤
│Faust,Mo E.          │772-42-3921│2018-01-10│826 Maple Avenue     │Youngstown│OH        │37180   │
└─────────────────────┴───────────┴──────────┴─────────────────────┴──────────┴──────────┴────────┘
Dan Pasco · Apr 28, 2021

Exactly - you haven't used the Gateways before. Why? IMO, the Gateways (mostly Java and DotNet) were a bit cumbersome to deal with. With ELS, we have default servers that can be easily managed and discovered, they require little or no configuration to get started (some ELS's do require some configuration if the language platform support is not discoverable by IRIS), and the interface is simple and direct.

One quick example, using Python, is to use the os module to get the current working directory:

USER>set python = $system.external.getPythonGateway()

USER>write python.invoke("os","getcwd")
/opt/intersystems/iris/xdbc/mgr

All that is required is a gateway connection to an external server, your code needs to be visible to that external server, either by direct placement into the default path for that language platform or by explicitly adding it by calling addToPath(), and public interfaces in your external code. By "external", I mean code that isn't written inside of the IRIS Server - ObjectScript.

When your external code writes to the "system output" device, that output is redirected to the IRIS current device. In my above example, the renderTable() function simply constructs a formatted string using the AsciiTable library (got it from GitHub) and writes it using System.out.println(formattedstring). I simply copied the output that was displayed in my IRIS session terminal window and pasted it in the original post. No extra work involved.

If your external code returns an object then you can indeed make use of that object as if it were a local IRIS Object - because it is. It is actually a network proxy object that communicates with the original external language object. That communication can actually be full duplex - your external code can talk to IRIS and IRIS can talk to your external code.

One really simple example that I think is quite profound is using JDBC in IRIS. This isn't JDBC Gateway - that still exists and it is what it is. I am talking about using a Java JAR file that contains a JDBC driver and you want to use it. I'll try a simple demo here. I have MariaDB installed on my system so I'll just use it to query a table I have defined there.

First - using mariaDB from the command line interface:

 ~ % mysql -u myusername -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.8-MariaDB Homebrew

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| SAMPLES            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.010 sec)

MariaDB [(none)]> use SAMPLES
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [SAMPLES]> show tables;
+-------------------+
| Tables_in_SAMPLES |
+-------------------+
| person            |
+-------------------+
1 row in set (0.000 sec)

MariaDB [SAMPLES]> select * from person limit 5
    -> ;
+-----------------------+-------------+------------+-----------------------+------------+------------+----------+
| name                  | ssn         | dob        | home_street           | home_city  | home_state | home_zip |
+-----------------------+-------------+------------+-----------------------+------------+------------+----------+
| Basile,Molly M.       | 452-57-8033 | 1994-06-02 | 1153 First Street     | Xavier     | SD         | 98033    |
| Cooke,Howard F.       | 131-62-3894 | 2017-12-09 | 5172 Washington Place | Zanesville | NE         | 44980    |
| Donaldson,Phil R.     | 480-79-5019 | 1990-01-23 | 4429 Elm Street       | Miami      | WA         | 67638    |
| Eisenstien,Michael D. | 655-11-6334 | 1948-08-14 | 4676 Elm Avenue       | Reston     | KY         | 52729    |
| Faust,Mo E.           | 772-42-3921 | 2018-01-10 | 826 Maple Avenue      | Youngstown | OH         | 37180    |
+-----------------------+-------------+------------+-----------------------+------------+------------+----------+
5 rows in set (0.016 sec)

MariaDB [SAMPLES]>

Next, I'll follow up with an example of doing this from within IRIS.

Dan Pasco · Apr 28, 2021

You are right, of course. I was puzzled by the lack of documentation myself. I did some research and I've been told that there will be documentation available for the External Language Servers and $system.external.

The $system.external.Help() feature is something that has been part of the product for many years. It is not a substitute for proper documentation but serves to aid the command line user when questions regarding function names and arguments need a quick answer.

The ELS features are based on what is known as "Dynamic Object Gateways". There is extensive documentation available for them. That documentation is a bit dated in that it doesn't incorporate the new $system.external API's but still mostly accurate. Obtaining a gateway connection to an External Language Server by simply invoking $system.external.getGateway(<gatewayname>) is just one way we've simplified that interface.

There are default ELS's defined for Java, Python, DotNet, and R. Each is supported not only by the getGateway() that accepts the name of the ELS but also by get<Language>Gateway() functions for Java, DotNet, Python and R that return gateway connections to the default language servers.