InterSystems IRIS SQL Professional

SECTION/SKILLS

ABILITIES

T1

Designs IRIS SQL applications

T1.1

Designs a SQL schema

1. Distinguishes use cases for row vs columnar table layout
2. Distinguishes use cases for different index types

T1.2

Designs advanced schemas

1. Recalls anatomy of Globals (subscript and value)
2. Interprets relationship between table structure and Globals
3. Distinguishes the (Globals) level at which mirroring/journaling operates from the SQL layer
4. Distinguishes the differences between date/time data types
5. Interprets the overhead associated with stream data
6. Identifies use cases for text search

T1.3

Writes business logic

1. Identifies use cases for UDFs, UDAFs and SPs
   

T1.4

Develops Object/Relational applications

1. Recalls SQL best practices when defining classes
2. Uses Object access to interact with individual rows
3. Identifies SQL limitations with class inheritance
4. Uses serial and object properties
5. Identifies use cases for collection properties
6. Distinguishes class relationships from Foreign Keys

T1.5

Deploys SQL applications

1. Determines what needs to be part of a deployment

T2

Uses IRIS SQL

T2.1

Manages IRIS query processing

1. Identifies benefits of the universal query cache
2. Lists considerations made by the optimizer
3. Differentiates client and server-side problems
4. Uses Statement Index to find statement metadata
5. Distinguishes between the use of parameters and constants in a query
6. Distinguishes between transaction and isolation levels

T2.2

Interprets query plans

1. Identifies the use of indices in a query plan
   
2. Identifies vectorized (columnar) query plans
   
3. Uses hints to troubleshoot query planning
   
4. Identifies opportunities for indices, based on a query plan

T2.3

Uses IRIS SQL in applications

1. Distinguishes use cases for Dynamic SQL and Embedded SQL

T2.4

Uses IRIS-specific SQL capabilities

1. Uses arrow syntax for implicit joining
2. Determines use cases for explicit use of collation functions

T3

Manages IRIS SQL operations

T3.1

Manages SQL operations

1. Identifies use cases for purging queries and rebuilding indices
   
2. Recalls impact of purging queries and rebuilding indices
   
3. Identifies use cases for un/freezing query plans, including automation
   
4. Identifies use cases for (bitmap) index compaction
   
5. Uses the runtime stats in the Statement Index to find statements with optimization opportunities

T3.2

Configures InterSystems SQL options

1. Recalls relevant system configuration options (e.g. lock threshold)
2. Differentiates scale-out options, ECP, and sharding

T3.3

Manages SQL security

1. Recalls to apply SQL privilege checking when using Embedded SQL

T3.4

Uses PTools for advanced performance analysis

1. Identifies use cases for using PTools