Creating a User Index with SQL

When I wrote about a SQL procedure that allowed me to add data to a User Index, ADD_USER_INDEX_ENTRY, I created the User Index with the QUSCRTUI API. I received an email from an IBM employee telling me that there was a SQL procedure that would allow me to create the User Index.

I am going to do here is to create the same user index as I did with QUSCRTUI in that other post.

In this first example I am going to create the User Index with a fixed length entries of 100 bytes.

01 CALL QSYS2.CREATE_USER_INDEX(USER_INDEX => 'MYUSRIDX', 02 USER_INDEX_LIBRARY => 'MYLIB', 03 ENTRY_TYPE => 'FIXED', 04 MAXIMUM_ENTRY_LENGTH => 100, 05 KEY_LENGTH => 4, 06 REPLACE => 'YES', 07 IMMEDIATE_UPDATE => 'YES', 08 TEXT_DESCRIPTION => 'My user index', 09 PUBLIC_AUTHORITY => '*USE') ;

Line 1: The CREATE_USER_INDEX SQL procedure is found in the library QSYS2. Here is also its first parameter, which is the name I want to call the User Index.

Line 2: The library within which I want the User Index to be created.

Line 3: The entries made to the User Index will be fixed in length, in this case 100 bytes.

Line 4: As the user Index has fixed length entries I use this parameter to assign the length of the entry.

Line 5: The key will be four bytes long.

Line 6: Passing the value of "YES" will be delete any existing User Index with the same given name and in the given library.

Line 7: The value "YES" indicates that the User Index will be updated immediately.

Line 8: The description I am giving the object.

Line 9: The public authority for this object will be "*USE".

There are other parameters that I have not used:

• INDEX_SIZE:  Maximum size of the User Index, default is 4GB
• INDEX_ATTRIBUTE:  I don't see the point of giving a User Index an object attribute.
• OPTIMIZATION:  How I want the access to the User Index. As I had not used this, the default is used, which is the User Index will be optimized for random access.
• TRACK_USAGE:  Improves the way the system determines if the object becomes damaged. The default is "NO".
• OBJECT_DOMAIN:  Whether the object should be in the system or user domains. The default is "*DEFAULT" which will let the system decide which domain to put the object in.

After the User Index is created I can use USER_INDEX_INFO View to check the User Index's attributes. I used the following:

-- List all user indexes in my library 01 SELECT USER_INDEX,ENTRY_TYPE, 02 ENTRY_LENGTH AS "Min", 03 MAXIMUM_ENTRY_LENGTH AS "Max" 04 FROM QSYS2.USER_INDEX_INFO 05 WHERE USER_INDEX_LIBRARY = 'MYLIB' ;

The result is:

USER_INDEX ENTRY_TYPE Min Max KEY_LENGTH ---------- ---------- ---- ---- ---------- MYUSRIDX FIXED 100 100 4

This shows that the entry type is fixed, and that the minimum and maximum length can only be 100 bytes.

Next I wanted to create a variable entry type User Index. The statement I used was:

01 CALL QSYS2.CREATE_USER_INDEX(USER_INDEX => 'MYUSRIDX', 02 USER_INDEX_LIBRARY => 'MYLIB', 03 ENTRY_TYPE => 'VARIABLE', 04 KEY_LENGTH => 4, 05 REPLACE => 'YES', 06 IMMEDIATE_UPDATE => 'YES', 07 TEXT_DESCRIPTION => 'My user index', 08 PUBLIC_AUTHORITY => '*USE') ;

The differences from my previous statement, for the fixed entry lengths, was that I changed the entry type to variable, line 3, and I deleted the maximum entry length parameter as the documentation says that this should not be used for a variable entry type User Index.

Alas, when I ran the statement I received the following error, displayed in the bottom section of ACS's Run SQL Scripts window:

SQL State: 22023 Vendor Code: -443 Message: [SQL0443] KEY_LENGTH MUST BE BETWEEN 1 AND MAXIMUM_ENTRY_LENGTH Cause . . . . . : Either a trigger program, external procedure, or external function detected and returned an error to SQL. If the error occurred in a trigger program, the trigger was on table QDBSSUDF2 in schema QSYS. If the error occurred in an external procedure or function, the external name is QDBSSUDF2 in schema QSYS. The associated text is KEY_LENGTH MUST BE BETWEEN 1 AND MAXIMUM_ENTRY_LENGTH. If the error occurred in a trigger program, the associated text is the type of trigger program. If the error occurred in an external function, the associated text is the text of the error message returned from the external function. Recovery . . . : Refer to the joblog for more information regarding the detected error. Correct the error and try the request again.

After trying various things I discovered that I had to give a maximum entry length, which I did in the statement below:

01 CALL QSYS2.CREATE_USER_INDEX(USER_INDEX => 'MYUSRIDX', 02 USER_INDEX_LIBRARY => 'MYLIB', 03 ENTRY_TYPE => 'VARIABLE', 04 MAXIMUM_ENTRY_LENGTH => 4, 05 KEY_LENGTH => 4, 06 REPLACE => 'YES', 08 IMMEDIATE_UPDATE => 'YES', 09 TEXT_DESCRIPTION => 'My user index', 10 PUBLIC_AUTHORITY => '*USE') ;

I have added the maximum entry length parameter, line 4, and I found I have to provide a value that is greater or equal to the length of the key length, line 5. With that change the the User Index is created.

When I run the same USER_INDEX_INFO statement as I did before, I get:

USER_INDEX ENTRY_TYPE Min Max KEY_LENGTH ---------- ---------- ---- ---- ---------- MYUSRIDX VARIABLE 0 2000 4

This show that the entry type is variable, thus the minimum entry length is zero and the maximum entry length is 2,000 bytes. Just as it was when I created a User Index using the QUSCRTUI API.

IMHO, apart from the issue with the maximum entry length this is a lot easier than using the QUSCRTUI API to create a User Index.

 

You can learn more about the SQL procedure CREATE_USER_INDEX from the IBM website here.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.