Type of SQL JOINS:

1. Inner Join
2. Outer Join

• Left Outer join
• Right Outer join
• FULL Outer join

3. Exception Join

• Left Exception join
• Right Exception join

4. Cross Join

Sample Data

To understand joins, we are going to use straightforward data in 3 tables.

Table 1: SQLTEST1

CREATE TABLE SQLTEST1 (file_key for keyf char(10),file_dob for dobf date)

INSERT INTO SQLTEST1 values('01','1999-10-10'),('02','2000-10-10'),('03','2001-11-11'),('06','2016-12-12')

Table 2 : SQLTEST2

CREATE TABLE SQLTEST2 ( file_key for keyf char(10), file_name for namef char(20))

INSERT INTO SQLTEST2 values('01','SUMIT'),('02','AMIT'),('04','AMAN'),('05','RAM')

Table 3 : SQLTEST3

CREATE TABLE SQLTEST3 (file_key for keyf char(10) ,  file_last for lastf char(20))

INSERT INTO SQLTEST3 values('01','Goyal'),('05','Kumar'),('06','Gupta')

INNER JOIN

INNER Join returns records that are common in all tables based on the given join condition. There are two ways to write INNER Join.

1. With JOIN keyword

SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf= b.keyf

You can JOIN more than two tables:

SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf = b.keyf JOIN SQLTEST3 c ON b.keyf =c.keyf

2. Without JOIN keyword

SELECT * FROM SQLTEST1 AS a , SQLTEST2 AS b WHERE a.keyf= b.keyf

This is the same as:

SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf= b.keyf

1. JOIN keyword is replaced with ","
2. ON keyword is replaced with WHERE clause.
3. ON must be used instead of “WHERE” to specify the join condition when the JOIN keyword is used.

OUTER JOIN

SQL OUTER Join gives

• All data from the PRIMARY table with matching date from the SECONDARY table(s) appended to it.
• If no matching data found in the SECONDARY table(s) for the join condition, a NULL value will be used to fill that space.

There are 3 types of OUTER JOIN.

1. LEFT OUTER JOIN or LEFT JOIN (Yes, we can omit word OUTER)

• The “LEFT OUTER JOIN” table on the leftmost side of the “JOIN keyword” is considered as the “PRIMARY table.”
• table(s) on the JOIN keyword's right side is considered the SECONDARY table(s).

SELECT * FROM SQLTEST1 a LEFT JOIN SQLTEST2 b ON a.keyf= b.keyf

1. ON keyword specify the join condition.
2. All data from SQLTEST1(LEFT Table) with matching date from SQLTEST2(RIGHT table) appended to it.
3. If no matching data found in the SQLTEST2 table for the join condition, a NULL value will be used to fill that space.

When there are more than two tables in OUTER JOIN, the ON keyword defines which table is primary to which table.

SELECT * FROM SQLTEST1 a    
	LEFT JOIN SQLTEST2 b ON a.keyf = b.keyf                   
        LEFT JOIN SQLTEST3 c ON a.keyf = c.keyf

There are 2 LEFT JOINS in this query.

1. Join 1 > Primary Table : SQLTEST1 Secondary Table : SQLTEST2 based on “ON a.keyf = b.keyf”
2. Join 2 > Primary Table : SQLTEST1 Secondary Table : SQLTEST3 based on “ON a.keyf = c.keyf”

So the output will be

Now change last ON condition from “ON a.keyf = c.keyf” to “ON b.keyf = c.keyf”

SELECT * FROM SQLTEST1 a    
	LEFT JOIN SQLTEST2 b ON a.keyf = b.keyf                   
        LEFT JOIN SQLTEST3 c ON b.keyf = c.keyf

Again there are 2 LEFT JOINS in this query.

1. Join 1 > Primary Table : SQLTEST1 Secondary Table : SQLTEST2 based on “ON a.keyf = b.keyf”
2. Join 2 > Primary Table : SQLTEST2 Secondary Table : SQLTEST3 based on “ON b.keyf = c.keyf”. Now SQL will try to match table SQLTEST2(not table SQLTEST1) and table SQLTEST3.

Now the output will be changed to

2. RIGHT OUTER JOIN or RIGHT JOIN

The concept of the RIGHT OUTER JOIN is the same as of LEFT OUTER JOIN. In the case of RIGHT OUTER JOIN right-most table of JOIN keyword, the only difference is PRIMARY TABLE.

SELECT * FROM SQLTEST1 a RIGHT JOIN SQLTEST2 b ON a.keyf= b.keyf

• ON keyword specify the join condition.
• All data from SQLTEST2 (RIGHT Table) with matching date from SQLTEST1 (LEFT table) appended to it.
• If no matching data found in the SQLTEST1 table for the join condition, a NULL value will be used to fill that space.

3. FULL OUTER JOIN or FULL JOIN

FULL JOIN is a combination of LEFT JOIN and RIGHT JOIN. It will get you all records from all tables based on the join conditions.

SELECT * FROM SQLTEST1  a FULL JOIN SQLTEST2 b on a.keyf= b.keyf

EXCEPTION JOIN

Here is how SQL EXCEPTION JOIN works.

• It returns rows from the PRIMARY table that don’t have a match in any of the SECONDARY tables.
• Columns that come from the SECONDARY table(s) will always have a null value.

DB2 SQL supports 2 types of EXCEPTION JOINs:

1. Left Exception Join

• The table on the left side of the JOIN keyword will be considered as the PRIMARY table.
• Table(s) on the JOIN keyword's right side will be considered the SECONDARY table.

SELECT* FROM SQLTEST1  a LEFT EXCEPTION JOIN SQLTEST2 b on a.keyf= b.keyf

In this case:

• SQLTEST1 is the PRIMARY table.
• SQLTEST2 is the SECONDARY table.

2. Right Exception Join

• The table on the right side of the JOIN keyword will be considered as the PRIMARY table.
• Table(s) on the JOIN keyword's left side will be considered as the SECONDARY table.

SELECT* FROM SQLTEST1  a RIGHT EXCEPTION JOIN SQLTEST2 b on a.keyf= b.keyf

In this case:

• SQLTEST2 is the PRIMARY table.
• SQLTEST1 is the SECONDARY table.

CROSS JOIN

SQL CROSS JOIN returns every possible combination of rows from every table being joined(This is also called Cartesian product).

SELECT * FROM SQLTEST1 a CROSS JOIN SQLTEST2 b

Point to note: There is no "ON" keyword that means no join condition.

DB2 SQL JOINS was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

This is a handy and powerful concept and can help you solve complex issues with straightforward queries.

There are 2 types of TABLE EXPRESSIONs in DB2 SQL

1. Nested table expressions(also called Derived tables)
2. Common table expressions

NESTED TABLE EXPRESSION

Nested table expressions(NTE) (also called derived tables)

The basic syntax of the SQL SELECT statement is like

SELECT * FROM table-name

1. Nested table expressions give you the option to replace “table-name”(after FROM clause) with “(SELECT-statement),” which acts as a temporary table.
2. To use this Temporary table in the query, we must give this Temporary table a name (correlation name).

SELECT * FROM (SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM EMPLOYEE E1 GROUP BY WORKDEPT) AS T2

In the above query, “(SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM CORPDATA.EMPLOYEE E1 GROUP BY WORKDEPT) AS T2” is NTE with correlation name T2.

You can use other tables with NTE in the same query.

SELECT MGRNO, T1.DEPTNO, MAXSAL FROM DEPARTMENT T1,
		(SELECT MAX(SALARY) AS MAXSAL, WORKDEPT FROM EMPLOYEE E1 GROUP BY WORKDEPT) T2
	WHERE T1.DEPTNO = T2.WORKDEPT ORDER BY DEPTNO

In the same way above, you can use multiple NTEs in a single query.

Someone can easy confuse NTE with SubQueries as both follow almost the same syntax. The point to remember here is NTE replaces “Table” in FROM clause where SubQueries give the option to use a query in WHERE and HAVING clauses.

COMMON TABLE EXPRESSION (CTE)

1. Unlike NESTED TABLE EXPRESSION, CTE is not specified in FROM clause of the query.
2. But CTE is specified before the full-select in a SELECT statement using WITH keyword.
3. A single query can contain multiple CTEs, but the keyword WITH is used only once.
4. Inside query, the CTE table is treated as any other normal table.
5. By default, CTE uses column names from the query, it is driven from(Or the system-generated name in case of some SQL Function like SUM() is used), But there is an option to rename CTE table columns.

WITH AVSAL AS
     (SELECT WORKDEPT, AVG(SALARY) as AVGSAL 
        FROM EMPLOYE  GROUP BY WORKDEPT)
SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL 
FROM AVSAL GROUP BY WORKDEPT;

The above code “WITH AVSAL AS (SELECT WORKDEPT, AVG(SALARY) as AVGSAL FROM EMPLOYEE GROUP BY WORKDEPT)” defines a CTE with the name AVSAL means now there is a temporary table with the name AVSAL available to this query.

In the next line, “SELECT WORKDEPT, MAX(AVGSAL) AS MAXAVGSAL FROM AVSAL GROUP BY DEPT,” a select statement is used to get data from CTE table AVSAL like any other table.

Rename columns for CTE

WITH AVSAL(DEPARTMENT,AVGSALARY) AS
(SELECT   WORKDEPT, AVG(SALARY) as AVGSAL  
FROM  EMPLOYE  GROUP BY WORKDEPT)
SELECT DEPARTMENT, MAX(AVGSALARY) AS MAXAVGSAL 
FROM AVSAL GROUP BY DEPARTMENT;

The above query “WITH AVSAL(DEPARTMENT, AVGSALARY) AS” renames the CTE table AVSAL’s columns to DEPARTMENT and AVGSALARY.

Using Multiple CTEs in a Single Query

This is a sample of a complex query where Multiple CTE tables are being used in combination with SQL JOINS. For multiple CTEs in a single query, there is only a single WITH clause.

WITH Ctable1 AS
(SELECT C1Field1, 
row_number() over(partition by C1Field2) as rowNbr
FROM SampleTable1  WHERE C1Field5 = "XYZ" ), 
		
Ctable2 AS
(SELECT  SampleTable1.* , 
row_number() over(order by C2Field3 desc) as rowNbr
FROM SampleTable2 
      INNER JOIN SampleTable3 ON C2Field = SampleTable3.C3Field
WHERE C2Field5 <= "ABC")

SELECT * FROM Ctable1 t1 
       FULL JOIN Ctable2 t2 ON t1.C1Field1 = t2.C2Field1 
WHERE t1.rowNbr = 1 OR t2.rowNbr = 1

In the Above two temporary tables Ctable1 and Ctable2. Ctable1 is created using a simple select statement with OLAP function row_number. Ctable2 is created by joining two tables SampleTable2 and SampleTable3.

And finally, “SELECT statement,” which joins Ctable1 and Ctable2 using FULL JOIN.

DB2 SQL TABLE EXPRESSION was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

Something like this

SELECT country,city,sum(population) from Census GROUP By COUNTRY, CITY

“GROUP By COUNTRY, CITY” defines a set with values COUNTRY and CITY. Only one list can be specified for a query.

If you need more than one set of values, you need to total population by COUNTRY/CITY and CITY/ZIP in the same query. The Standard “GROUP BY” clause does not give the option to use multiple sets of columns.

GROUPING SETS

1. This is another extension to the “GROUP BY” clause,
2. This allows using multiple column sets with the “GROUP BY” clause within a single SQL Query,

SELECT country,city,zip,sum(population) from Census 
GROUP By GROUPING SETS((COUNTRY,CITY),(CITY,ZIP))

1. This is running

SELECT country,city,zip,sum(population) from Census GROUP By  COUNTRY,CITY

-AND-

SELECT country,city,zip,sum(population) from Census GROUP By CITY,ZIP

and then union the result.

GROUPING SETS with ROLLUP and CUBE.

You can combine GROUPING SETS with ROLLUP and CUBE to create more complex combinations.

SELECT country,city,zip,sum(population) from Census 
GROUP By GROUPING SETS((COUNTRY,CITY), ROLLUP(CITY,ZIP))

DB2 SQL OLAP PART 5: GROUPING SETS was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

Let's take an example of a “Census” table that keeps a record of the population by COUNTRY, CITY, ZIP, and POPULATION (in millions) like this.

Task 1: Get a report for the total population by each COUNTRY and CITY.

SELECT COUNTRY,CITY,SUM(POPULATION) as POPULATION from Census 
GROUP BY  COUNTRY,CITY

A simple “GROUP BY” with SUM() will work perfectly.

Task 2: In the last report, add a new row for sub-total by country and then a new row for the total population.

1. This may be possible use some very complex SQL query by not by Simple “GROUP BY.”

ROLLUP

1. With the ROLLUP booster, “GROUP BY” will create sub-total and final total with a minor change in a previous SQL query.

select COUNTRY,CITY,SUM(POPULATION) as total from Census 
GROUP BY ROLLUP (COUNTRY,CITY)

These are the Steps this query will follow

1. Aggregate (in this case using SUM() function) by

• COUNTRY and CITY
• COUNTRY and NULL [ kind of SUB TOTAL]
• NULL and NULL [ kind of FINAL TOTAL]

If you want all sub-total and Final total at the bottom of the report, add an “ORDER BY” clause to the query.

select COUNTRY,CITY,SUM(POPULATION) as total from Census 
GROUP BY ROLLUP (COUNTRY,CITY) ORDER BY CITY

1. ORDER BY CITY will push the NULL CITY to the bottom of the result.

CUBE

1. Same as ROLLUP, CUBE is also an extension to the “GROUP BY” clause.
2. Consider CUBE as like: CUBE = ROLLUP + MORE LEVEL OF TOTAL

select COUNTRY,CITY,SUM(POPULATION) as total from Census 
GROUP BY CUBE (COUNTRY,CITY)

Aggregate (in this case using SUM() function) by [1,2,3 are same as ROLLUP]

1. COUNTRY and CITY
2. COUNTRY and NULL [ kind of SUB TOTAL]
3. NULL and NULL [ kind of FINAL TOTAL]
4. NULL and CITY [ New to CUBE]

DB2 SQL OLAP PART 4: ROLLUP and CUBE was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

1. Participant(s) with the highest marks got the 1st rank.
2. Participant(s) with the 2nd highest marks got the 2nd rank and so on.

Here is a sample table with participant names with marks

1. Both of these rank* functions work in the same manner as we define ranks in any exam.
2. There is a minor difference in DENSE_RANK() and RANK() functions.
3. Same as ROW_NUMBER() , OVER() function is required.
4. But ORDER BY parameter is required in the OVER() function.

— Blank OVER() will not work

— If you use“PARTITION BY,” it must be followed by “ORDER BY.”

5. “ORDER BY” defines how to want to give the ranks.

1. DENSE_RANK()

1. This works exactly like the exam ranking logic defined at the starting of this article.

— Based on “ORDER BY,” the topmost row and all other matching rows get rank — 2nd and all matching rows get rank 2, and so on.

2. There will be no gaps in the rank sequence. This means no sequence number will be missing between the 1 and the highest rank sequence.

OVER(ORDER BY…)

select a.*, DENSE_RANK() OVER(ORDER BY MARKS DESC) as DENSE_RANK from RESULT a

1. Both rows with 900 marks are ranked 1.
2. Row with 800 marks is ranked 2.
3. All 3 rows with 600 marks are ranked 4.

OVER(PARTITION BY… ORDER BY…)

1. “PARTITION BY” works the same as in ROW_NUMBER() function.

— Create a virtual partition in rows, and each partition has its own set of sequence numbers starting with 1.

select a.*, DENSE_RANK() OVER(PARTITION BY MARKS  ORDER BY NAME) as DENSE_RANK 

from RESULT a

1. MARKS creates partitions, so each set of equal marks get its own set of rank sequence.
2. After that, ranks are assigned based on NAME (ORDER BY NAME).

2. RANK()

Here is how RANK() is different from DENSE_RANK().

1. Suppose you ask all participants to make a queue by their marks only. (Highest mark in front and lowest in back).
2. The queue will look like some this.

Here is how RANK() works

1. Same marks always get the same rank. So
2. Top 900 occupy positions 1 and 2. But you can not tell which one will go for position 1 and which one for position 2.

— So both get RANK 1

1. Both 900 got RANK 1, but they occupied the first 2 positions
2. So 800 will get the RANK 3. This will create a gap as there is no one with RANK 2.
3. 700 will get the RANK 4.
4. Three 600 occupy positions 5,6 and 7; you can not tell which one gets which position.

— So assign all three the same RANK 5.

1. Now 500 will get RANK 8, creating gaps for RANK 6 and RANK 7.
2. and SO ON…

This example with RANK() and DENSE_RANK() will help you understand how RANK() works in comparison to DENSE_RANK()

select a.*, 
RANK() OVER(ORDER BY MARKS DESC) as RANK, 
DENSE_RANK() OVER(ORDER BY MARKS DESC) as DENSE_RANK  
from RESULT a

RANK() OVER(PARTITION BY… ORDER BY…) work in the same concept as in DENSE_RANK() OVER(PARTITION BY… ORDER BY…) as above

DB2 SQL OLAP PART 3: RANK and DENSE_RANK was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

The most basic usage of this function looks like following-

SELECT a.* , ROW_NUMBER() OVER() FROM address a

Each row selected from table ‘address’ is assigned a unique sequence number starting with ‘1’ and keeps increasing in the order rows are fetched by SQL statement.

The above SQL statement is similar to the SQL statement below-

SELECT  a.* , ROW_NUMBER() 
over(order by ADDID, ADDRESS, CITY, STATE, ZIP, COUNTRY ) 
FROM address a

As stated earlier, the ROW_NUMBER function assigns the number in the order rows are fetched by SQL statement; so, if the fetch sequence of a row changes due to any factor, the assigned number also changes for that row. (Refer to an example below.)

SELECT  a.* , ROW_NUMBER() over() 
FROM address a ORDER BY a.CITY

OVER()

It is important to know that ROW_NUMBER() function always needs a function OVER() to work. OVER() provides two options to define how ROW_NUMBER would assign the numbers to the rows in the result set. These options are-

1. ORDER BY
2. PARTITION BY

OVER (ORDER BY … )

1. ORDER BY allows to define the ordering sequence in which the sequence number should be assigned to the rows fetched by SQL.
2. Always assigns a unique number to each row starting from 1.
3. This works in the same way as the ORDER BY clause works in a simple select statement.
4. ASC (Ascending) and DESC (Descending) options are allowed.

SELECT a.* , ROW_NUMBER() over(ORDER BY CITY DESC, ZIP) as ROW_NUMBER
 FROM address a order by CITY

NOTE: due to the order by CITY clause, the display sequence is different from the ROW_NUMBER sequence. ROW_ NUMBER is assigned for Descending CITY column while records are being displayed in Ascending CITY column.

OVER (PARTITION BY … )

1. PARTITION BY divide the data into virtual partitions and each partition gets its own set of rows (and hence ROW_NUMBER sequence).
2. Every set of ROW_NUMBER sequence starts with 1 (unique within a respective partition), so there may be duplicate ROW_NUMBER values in the result set.

SELECT a.* , ROW_NUMBER() OVER(PARTITION BY COUNTRY) as ROW_NUMBER
 FROM address a

NOTE: PARTITION BY COUNTRY creates 2 virtual partitions in data; one for COUNTRY = INDIA and the other for COUNTRY = USA. Each partition has its own set of ROW_NUMBERs starting with 1.

OVER (PARTITION BY … ORDER BY… )

1. Within each virtual partition created by “PARTITION BY,” an “ORDER BY” clause can be used to define the order of sequence number generated by the ROW_NUMBER() function.
2. Please be sure that OVER(ORDER BY … PARTITION BY…) is NOT ALLOWED.

SELECT a.* , ROW_NUMBER() OVER(PARTITION BY COUNTRY ORDER BY ZIP DESC) as ROW_NUMBER
 FROM address a

Note: SQL statement first creates partitions by COUNTRY and then orders each partition by ZIP in descending order.

DB2 SQL OLAP PART 2: ROW_NUMBER was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

What is online here?
An online algorithm can serially process its input piece-by-piece. So an online algorithm is one that never has access to all data at the same time. (So it’s streaming data)

Advantages-

1. OLAP functions are beneficial to create complex reports using a single SQL statement.
2. OLAP functions provide multiple and very advanced data grouping and ordering options, which are not possible by simple “GROUP BY” and/or “ORDER BY” clauses.

Below is the list of available OLAP functions.

DB2 SQL OLAP: Online Analytical Processing Part 1 was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

Here how we can use Named Indicators in RPG:

Using Pointer and DS.

D pIndicators S * Inz(%Addr(*In))
D Indicators DS Based(pIndicators)
D ExitKey N Overlay(Indicators:3)
D PromptKey N Overlay(Indicators:4)
D RefershKey N Overlay(Indicators:5)
D ClearSubfile N Overlay(Indicators:41)
D DisplaySubfileControl...
D N Overlay(Indicators:42)

Now in your RPG code, you don’t need to use *IN03 = *ON; instead, you can use ExitKey = *On. For Example, in free format RPG, you can write code something like this :

DoW ExitKey = *Off;
  DisplaySubfileControl = *Off;
  ClearSubfile = *On;
  Write SUBFILE_CONTROL_FORMAT;
  ClearSubfile = *Off ;
EndDo

Using INDDS.

Wise words from IBM-

The INDDS keyword lets you associate a data structure name with the INDARA indicators for a workstation or printer file. This data structure contains the conditioning and response indicators passed to and from data management for the file and is called an indicator data structure.

In simple words-

1. Your Display or Printer file must contain INDARA keyword at File-Level.

Example how to use INDDS in RPG:

FExample1  CF   E             WORKSTN INDDS(dispInds) 

 
D dispInds        DS                  
D  ExitKey                        N   Overlay(dispInds:3)
D  PromptKey                      N   Overlay(dispInds:4)
D  RefershKey                     N   Overlay(dispInds:5)
D  ClearSubfile                   N   Overlay(dispInds:41)
D  DisplaySubfileControl...
D                                 N   Overlay(dispInds:42)

This method removes the connection between *INXX indicators and Display file(or printer file). If you need to track whether F3 key is pressed or not, you cannot use *In03. Instead, you have to use ExitKey = *ON.

INDDS will be different for each file. So if you are using multiple display files, you have to track F3 using their respective INDDS indicator names.

More Details on IBM Site

ILE RPG: Named Indicators was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

To track enter key, we need to use a simple trick involving File information data structure(INFDS) and a hexadecimal value.

FSFLXXXXXD CF   E             WORKSTN INFDS(FileInfo)

 *---------------------------------------------------------------
 *  File information Data structure
 *---------------------------------------------------------------
D FileInfo        DS
D KeyPressed            369    369

 *---------------------------------------------------------------
 *  Constant
 *---------------------------------------------------------------
D EnterKey        C                   CONST(x'F1')

1. We declared a display file SFLXXXXXD with INFDS points to the FileInfo data structure.

2. AS400 updates Position from 369 to 369 in INFDS with Hexadecimal value corresponding to the key pressed on the screen.

3. We declared a constant “EnterKey” with hexadecimal value x’F1′.( x for hexadecimal).

So every time user presses Enter key on the screen. AS400 system updates variable KeyPressed (in FileInfo DS) with a hexadecimal value of x’F1′. Now, we can compare EnterKey constant with Keypressed variable :

Select ;
    When KeyPressed = EnterKey;
         ExSr ValidateData;
EndSl;

Using this way, you can track almost all keys, including function keys. Here is a list of Hexadecimal values for different keys.

D F1              C                   CONST(X'31')
     D F2              C                   CONST(X'32')
     D F3              C                   CONST(X'33')
     D F4              C                   CONST(X'34')
     D F5              C                   CONST(X'35')
     D F6              C                   CONST(X'36')
     D F7              C                   CONST(X'37')
     D F8              C                   CONST(X'38')
     D F9              C                   CONST(X'39')
     D F10             C                   CONST(X'3A')
     D F11             C                   CONST(X'3B')
     D F12             C                   CONST(X'3C')
     D F13             C                   CONST(X'B1')
     D F14             C                   CONST(X'B2')
     D F15             C                   CONST(X'B3')
     D F16             C                   CONST(X'B4')
     D F17             C                   CONST(X'B5')
     D F18             C                   CONST(X'B6')
     D F19             C                   CONST(X'B7')
     D F20             C                   CONST(X'B8')
     D F21             C                   CONST(X'B9')
     D F22             C                   CONST(X'BA')
     D F23             C                   CONST(X'BB')
     D F24             C                   CONST(X'BC')
      * Page Down/Roll Up                              
     D RollUp          C                   CONST(X'F5')
     D PageDown        C                   CONST(X'F5')
      * Page Up/Roll Down                              
     D RollDown        C                   CONST(X'F4')
     D PageUp          C                   CONST(X'F4')
      * Enter                                          
     D Enter           C                   CONST(X'F1')
     D Help            C                   CONST(X'F3')
     D Print           C                   CONST(X'F6')

ILE RPG: Track ENTER key was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.

1. It’s IBM’s version of the Bitmap index.
2. EVI is relatively new compared to Radix Index.
3. Logical files do not support EVI.
4. Use “CREATE ENCODED VECTOR INDEX ….” Statement to create EVI index on the table
5. It’s very efficient for:

• Small set or say defined set of distinct keys like States in the USA.
• Read-only table

A quick look into Bitmap Index

Let say this table represents the keys and corresponding RRNs.

Here is the Bitmap index for these keys(with RRN)

1. Bitmap Index is suitable for small values.
2. As the number of keys increase (new RRNs) number of bits needs to store the index also increased. (Issue 1)
3. The System has to update the complete index every time a new key comes in. (Issue 2)

So, IBM created a modified version called Encoded Vector Index, aka EVI.

EVI: Encoded Vector Index

EVI has two parts

1. Symbol table:

• It contains a distinct list of keys.
• A reference (called code) to an element in the Vector list.
• And it may contain the values for some SQL functions like sum(), count(),avg().

2. Vector list:

• It’s just a list.
• Each position represents the RRN from the base table.
• This position is the only link between the index and the base table.
• The value at a position is the link between the Symbol table and this list.

Let say this is the data in the base table.

The Symbol table might look like this.

Vector List :

So, let say the System needs to find a match for the key-value “TX.”

1. Look into the Symbol table for the code for this key-value TX = 99
2. Now check the vector list for all the values equals 99.
3. RRN 1 and 4 in the base table contains the record for the key “TX.”

If you add a new Key in the symbol table, it will only impact a single entry in the vector list. So unlike the Bitmap Index system don’t need to update the complete index. The System will create a new entry in the vector list.

Memory allocation for the CODE (the reference between the Symbol table and the Vector list)

1. By default, DB2 allocates 1 byte for the code.

• This means a total of 256 distinct keys (28) can be added to the symbol table.
• If the number of distinct keys increased beyond 256

— DB2 will allocate 2 bytes (then 4 bytes .. 8 bytes, and so on) for the code

— And rebuild the index.

• So, every time the size increases, the System has to rebuild the complete index.

2. To limit these rebuilds, you can give some idea of how many distinct keys you expect in this index.

• So, based on this idea, the System will allocate an appropriate number of bytes for the code.

CREATE ENCODED VECTOR INDEX XYZ on ABC(state)

WITH 3650 DISTINCT VALUES

1. From this statement, System gets the idea that there might be 3650 distinct keys in this index.

• So, 1-byte won’t be enough for 3650 values
• But 2 bytes will be enough for now.

2. This is not a hard limit. If the number of distinct keys in the index goes beyond 2 bytes, the System will allocate extra space and rebuild the index.

More references

1. https://www.ibm.com/developerworks/ibmi/library/i-evi-only-access-in-ibm-db2-for-i/index.html
2. https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rzajq/howeviworks.htm

DB2 for I: Index internals. Part-3 Encoded Vector Index was originally published in Applied I on Medium, where people are continuing the conversation by highlighting and responding to this story.