IBM DB2 for i testing indexes

IndexTest.sql

/* Creating schema/library MYLIB if you don't already have it */
BEGIN
  IF (NOT EXISTS(SELECT * FROM TABLE(SYSIBM.SCHEMAS()) AS SCHEMAS WHERE ODOBNM='MYLIB')) THEN
    CREATE SCHEMA MYLIB;
  END IF;
END;
/* Creating table MYLIB.TEST */
CREATE TABLE MYLIB.TEST 
  ( NAME VARCHAR (20) NOT HIDDEN  ) NOT VOLATILE ;
-- Grant Permissions
GRANT ALTER , DELETE , INDEX , INSERT , REFERENCES , SELECT , UPDATE  
  ON MYLIB.TEST TO MYUSER WITH GRANT OPTION ;
-- Create 3 Records
INSERT INTO MYLIB.TEST VALUES('Bob'),
  ('Bill'),
  ('Charlie');
--Create an EV index on UPPER(Name) column
CREATE ENCODED VECTOR INDEX MYLIB.TESTINDEX ON MYLIB.TEST (UPPER(NAME) ASC);
--Use the EV index on UPPER(Name)
SELECT * FROM MYLIB.TEST WHERE UPPER(NAME)='BOB';

----OTHER Index options (Non unique,unique, and unique where not null)
--CREATE INDEX MYLIB.TESTINDEX ON MYLIB.TEST (UPPER(NAME) ASC);
--CREATE UNIQUE INDEX MYLIB.TESTINDEX ON MYLIB.TEST (UPPER(NAME) ASC);
--CREATE UNIQUE WHERE NOT NULL INDEX MYLIB.TESTINDEX ON MYLIB.TEST (UPPER(NAME) ASC);