InterSystems IRIS SQL Specialist

T1

Designs InterSystems IRIS SQL applications

T1.1

Designs a SQL schema 

1. Identifies role of bitmap extent index
2. Determines use cases for indexes
3. Distinguishes use cases for different index types​
4. Distinguishes primary, unique, and ID keys
5. Identifies properties for foreign key relationships
6. Identifies behavior of referential actions for update and delete
7. Identifies tradeoffs for adding even more indexes
8. Identifies use cases for composite indexes

T1.2

Designs advanced schemas

1. Distinguishes the differences between date/time data types

T1.3

Loads data

1. Uses LOAD DATA command
2. Uses SQL diagnostics tables (%SQL_Diag)
3. Identifies options for exporting data (physical vs logical)

T1.4

Writes business logic

1. Defines stored procedures
2. Defines triggers
3. Identifies language options for writing business logic

T1.5

Develops object/relational applications

1. Recalls default object/relational mapping
2. Recalls SQL best practices when defining classes

T1.6

Deploys SQL applications

1. Lists mechanisms for deploying SQL applications
2. Determines what needs to be part of a deployment

T2

Uses InterSystems IRIS SQL

T2.1

Manages InterSystems IRIS query processing

1. List considerations made by the optimizer
2. Differentiates syntax and runtime errors
3. Uses Statement Index to find statement metadata

T2.2

Interprets query plans

1. Identifies ways to view query plans for a statement
2. Identifies Full Table Scans in a query plan
3. Identifies the use of indexes in a query plan
4. Distinguishes between loops and lookups in a query plan
5. Distinguishes modules called once vs repeatedly in a query plan
6. Recalls role of table statistics in query planning
7. Uses hints to troubleshoot query planning
8. Identifies opportunities for indexes, based on a query plan
9. Recalls the meaning of relative cost

T2.3

Uses InterSystems IRIS SQL in applications

1. Defines InterSystems IRIS connections
2. Identifies prepare/execute steps
3. Recalls how proper use of parameters can guard against SQL injection
4. Leverages explicit transaction controls effectively

T2.4

Uses InterSystems IRIS-specific SQL capabilities

1. Uses SelectMode appropriately
2. Uses arrow syntax for implicit joining

T2.5

Uses transactions effectively 

1. Determines appropriate transaction boundaries
2. Uses CommitMode appropriately
3. Recalls impact of large or long-running transactions

T3

Manages InterSystems IRIS SQL operations

T3.1

Manages SQL operations

1. Uses SQL Process view to monitor SQL activity (2022.2+)
2. Gathers table statistics
3. Uses the runtime stats in the Statement Index to find statements with optimization opportunities

T3.2

Manages SQL Security

1. Assigns SQL privileges to users and roles
2. Differentiates SQL privilege checking and application security
3. Identifies impact of enabling SQL auditing

T3.3

Uses PTools for advanced performance analysis

1. Distinguishes between performance metrics "time spent", "global references" and "commands executed"