A good writer is supposed to draw you in with the title and bury the answer somewhere in the article. I suppose this makes me a bad writer – don’t think less of me, my whole feeling of self-worth comes from the opinions of internet strangers!
SQL
See Part 1 here.
Part 2: Index Handling
Now you have a good idea of what kind of indices you need for your class and how to define them. Next, how do you handle them?
The Art of Mapping Globals to Classes 1 of 3
Looking to breathe new life into an old MUMPS application? Follow these steps to map your existing globals to classes and expose all that beautiful data to Objects and SQL.
By following the simple steps in this article and the next two you will be able to map all but the craziest globals to Caché classes. For the crazy ones I will put up a zip file of different mappings I have collected over the years. This is NOT for new data; if you don’t already have existing global please just use the default storage.
If you still can’t make heads or tails of your globals, send some example data to Support@InterSystems.com and we will be happy to help you figure it out.
Steps for mapping a global to a class
Last comment 1 February 2019
There are three things most important to any SQL performance conversation: Indices, TuneTable, and Show Plan. The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place. Our documentation provides more detail on these and other SQL Performance topics in the links below. The eLearning options reinforces several of these topics. In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.
There is a fair amount of repetition in the information listed below. The most important aspects of SQL performance to consider are:
- The types of indices available
- Using one index type over another
- The information TuneTable gathers for a table and what it means to the Optimizer
- How to read a Show Plan to better understand if a query is good or bad
Last comment 18 January 2019
Best Practices, Caché, Terminal, Business Service, Java, JDBC, ODBC, SQL, InterSystems Data Platform Blog
As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems
Last comment 28 December 2018
Mapping Examples
Clearly if you have a fourth article in the trilogy you need to go for the money grab and write the fifth, so here it is!
Note: Many years ago Dan Shusman told me that mapping globals is an art form. There is no right or wrong way of doing it. The way you interpret the data leads you to the type of mapping you do. As always there is more than one way to get to a final answer. As you look through my samples you will see there are some examples that map the same type of data in different ways
Last comment 11 October 2018
The Art of Mapping Globals to Classes (4 of 3)
The forth in the trilogy, anyone a Hitchhikers Guide to the Galaxy fan?
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
If the above does not sound familiar to you please start at the beginning with the following:
The Art of Mapping Globals to Classes (1 of 3)
Last comment 12 June 2018
Date range queries going too slow for you? SQL Performance got you down? I have one weird trick that might just help you out! (SQL Developers hate this!)*
If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp1 < TimeStamp2 if and only if ID1 < ID2 for all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges. Consider the following table
Last comment 4 June 2018
This is a sample Ensemble/Health Connect production which demonstrates how to receive an HL7 order (ORM) inbound from a file, extract fields (in this case, basic demographic information), and insert those into a table in an external SQL database via ODBC.
Included in the zip file:
- Exported code
- Sample ORM message
- 'How to configure' doc
Last comment 19 April 2018
It is often necessary to sort the results of a query on a string field containing a combination of alphabetic and numeric characters. In cases like this the default string collation may not always return the data in the expected sequence.
An example of this may be where a select from Samples.Person should order the results by the street address, but firstly ordered by the street number part as numeric, and then by the street name.
The default query will return the results as follows:
SELECT Name,Home_Street
FROM Sample.Person
Last comment 6 March 2018
No doubt bitmap indexing, if used with a suitable property, performs just impressive!
But there is a major limit: ID key has to be a positive integer.
For modern class definitions working with CacheStorage this is a default.
BUT: There are hundreds (thousands ?) old applications out in the field that
are still using composite ID keys.
Or - to name the origin - work on Globals with 2 subscript levels (or more).
They are by construction excluded from our "Bitmap Wonderland".
Of course, using the feature of multiple storage maps could possibly allow to escape - somehow.
But in those cases where you have many many GB of data stored in that way and
where you work with millions of lines of old - often poorly documented - code.
You wouldn't think seriously more than 5 min. if you change the storage structure or not.
So the bitmap is not with you
Last comment 17 February 2018
Last week, we announced the InterSystems IRIS Data Platform, our new and comprehensive platform for all your data endeavours, whether transactional, analytics or both. We've included many of the features our customers know and loved from Caché and Ensemble, but in this article we'll shed a little more light on one of the new capabilities of the platform: SQL Sharding, a powerful new feature in our scalability story.
Last comment 31 January 2018
As you will know the size of a Global reference is limited to 511 encoded characters. see docs:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GGBL_structure#GGBL_structure_maxsubscrlen
Last comment 9 November 2017
The Art of Mapping Globals to Classes (3 of 3)
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
If the above does not sound familiar to you please start at the beginning with the following:
This example is going to show you how to map a classic parent-child structure.
Last comment 2 November 2017
The newer dynamic SQL classes (%SQL.Statement and %StatementResult) perform better than %ResultSet, but I did not adopt them for some time because I had learned how to use %ResultSet. Finally, I made a cheat sheet, which I find useful when writing new code or rewriting old code. I thought other people might find it useful.
First, here is a somewhat more verbose adaptation of my cheat sheet
Last comment 18 October 2017
Quotes (1NF/2NF/3NF)ru:
Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
The same value can be atomic or non-atomic depending on the purpose of this value. For example, “4286” can be
- atomic, if its denotes “a credit card’s PIN code” (if it’s broken down or reshuffled, it is of no use any longer)
- non-atomic, if it’s just a “sequence of numbers” (the value still makes sense if broken down into several parts or reshuffled)
This article explores the standard methods of increasing the performance of SQL queries involving the following types of fields: string, date, simple list (in the $LB format), "list of <...>" and "array of <...>".
In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. By default access is determined at object modification Some time ago I became interested in determining row-level security at runtime. Here's how to implement it.
Last comment 16 June 2017
This is a translation of the following article. Thanks [@Evgeny Shvarov] for the help in translation.
Someone posted a question on DC asking whether it was possible to determine access rights for a particular table row always at runtime, and if it was, how could one do that?
Answer: it is possible and it’s not hard at all.
Last comment 14 June 2017
The Data Platforms department here at InterSystems is gearing up for this year's crop of interns, and I for one am very excited to meet them all next week!
We've got folks from top technical colleges with diverse specialties from hard core engineers to pure computer scientists to mathematicians to business professionals. They come from countries around the world like Vietnam, China, and Finland and they all come with impressive backgrounds. We're sure they will do very well this summer.
Rather than providing a set list of projects this year, Data Platforms interns will be developing their own projects which satisfy a short list of requirements. I can't wait to see what they come up with!
For an idea of what to expect, check out the white paper attached from my group last year who ported Deep Feature Synthesis research done at MIT for SQL platforms to Caché. Sarat Vysyaraju and Ryan St. Pierre did me and all the other mentors who provided guidance proud
The Art of Mapping Globals to Classes (2 of 3)
If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.
This example is going to cram in 4 or 5 different things beyond what was covered in Part 1
All that is left after this is the Parent Child mapping example and then you will be on your way.
Same disclaimer: If you can’t make heads or tails of your globals after reviewing these articles please contact the WRC and we will try to help you out: Support@InterSystems.com
Steps for Mapping a Global to a Class
Last comment 23 May 2017
It's almost time to get your customers upgraded to new versions - are you worried about showing off your SQL Performance after upgrades? If you want to upgrade without worrying, then I have just the program for you!!! Check out this video from Global Summit 2016 featuring yours truly explaining how to upgrade a system without worrying about pesky SQL queries showing on your waistline!
https://www.youtube.com/watch?v=GfFPYfIoR_g
Unfortunately the video started after the Frozen Musical Sing-a-long, but it's 30 minutes of the most fun you'll have while learning tools and tips for Caché SQL!
Questions? Comments? You know you can leave them here!
TL;DW: If you're on a version older than 2016.2, then you'll need to do a lot of testing. Once you hit 2016.2, upgrades are easy as "Hello World!"
Overview
Encryption of sensitive data becomes more and more important for applications. For example patient names, SSN, address-data or credit card-numbers etc..
Cache supports different flavors of encryption. Block-level database encryption and data-element encryption. The block-level database encryption protects an entire database. The decryption/encryption is done when a block is written/read to or from the database and has very little impact on the performance.
With data-element encryption only certain data-fields are encrypted. Fields that contain sensitive data like patient data or credit-card numbers. Data-element encryption is also useful if a re-encryption is required periodically. With data-element encryption it is the responsibility of the application to encrypt/decrypt the data.
Both encryption methods leverage the managed key encryption infrastructure of Caché.
The following article describes a sample use-case where data-element encryption is used to encrypt person data.
But what if you have hundreds of thousands of records with an encrypted datafield and you have the need to search that field? Decryption of the field-values prior to the search is not an option. What about indices?
This article describes a possible solution and develops step-by-step a small example how you can use SQL and indices to search encrypted fields.
Last comment 16 March 2017
Embedded SQL is a tool that allows us to execute SQL statements in Caché Object Script. For example, to select the name of a person with a particular SSN from the Sample.Person class we can do the following:
Last comment 25 February 2017
So I know it's been a while, and I hate to let my adoring fans down... just not enough to actually start writing again. But the wait is over and I'm back! Now bask in my beautiful ginger words!
For this series, I am going to look at some common problems we see in the WRC and discuss some common solutions. Of course, even if you find a solution here, you are always welcome to call in and expression you gratitude, or just hear my voice!
This week's common problem: "My query returns no data."
Now, supposedly you've checked that your query SHOULD return data. That is, if you do a "SELECT * FROM MyTable" and don't get back any data, I don't expect that your more complicated query with JOINs, a WHERE clause, and a GROUP BY will either. So, if you've determined that the data lives in your table what else could be going wrong?
1) You're in the wrong namespace.
Last comment 16 January 2017
If you have a lot of SQL queries to external systems (or even internal dynamic ones), you can encounter something like this
Last comment 19 December 2016
Class Queries in InterSystems Caché is a useful tool that separates SQL queries from Caché Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!
Last comment 9 December 2016
Have some free text fields in your application that you wish you could search efficiently? Tried using some methods before but found out that they just cannot match the performance needs of your customers? Do I have one weird trick that will solve all your problems? Don’t you already know!? All I do is bring great solutions to your performance pitfalls!
As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end. Just know you are hurting my feelings.
If you open up your version of Sample.Company in the SAMPLES namespace of a recent (2015.1 or later) Caché/Ensemble/HealthShare version you will see a Mission field that is pseudo-randomly generated text. Suppose we want to search this text field. For the purpose of this exercise, I generated about 256,246 companies – feel free to populate some on your own and follow along. Well you might run the following query
Last comment 5 December 2016
Beginning in Caché 2013.1, InterSystems introduced Outlier Selectivity to improve query plan selection involving fields with one atypical value.
In this article, I hope to use an example 'Projects' table to demonstrate what Outlier Selectivity is, how it helps SQL performance and a few considerations for writing queries.
Selectivity
First, let's take a quick look at Selectivity. Selectivity is meta information about the values in one column in a table. Assuming a typical distribution of data, it answers the question "If I ask for all the rows in this table that have a certain value in this column, what fraction of the table will I typically get back?
Last comment 8 November 2016