Adding and updating data in a User Index with SQL

I have only dabbled with User Indexes. My recollections of using them were of having to do everything with APIs, mapping system data types to RPG data types, etc. Well, with IBM i 7.5 and 7.4 TR6 comes a SQL procedure that makes the insertion and updating of data in the User Space very easy.

Alas, I still need to use an API, QUSCRTUI, to create the User Index I am going to using in this example.

I have created a RPG to call the API:

01 **free 02 /copy qsysinc/qrpglesrc,qusec 03 dcl-pr CrtUserIndex extpgm('QUSCRTUI') ; 04 *n char(20) const ; // User index name 05 *n char(10) const ; // Extended attribute 06 *n char(1) const ; // Entry length attribute 'V'=Variable 07 *n int(10) const ; // Entry length 08 *n char(1) const ; // Key insertion '1'=By key 09 *n int(10) const ; // Key length 10 *n char(1) const ; // Immediate update '1'=Yes 11 *n char(1) const ; // Optimization '1'=Optimize for sequential 12 *n char(10) const ; // Public authority 13 *n char(50) const ; // Text description 14 *n char(10) const ; // Replace 15 *n likeds(QUSEC) options(*varsize : *nopass) ; // Error DS 16 end-pr ; 17 CrtUserIndex('MYUSRIDX MYLIB' : 18 '' : 19 'V' : 20 0 : 21 '1' : 22 4 : 23 '1' : 24 '1' : 25 '*USE' : 26 'My user index' : 27 '*YES' : 28 QUSEC) ; 29 *inlr = *on ;

Line 1: I only write totally free RPG.

Line 2: Rather than defining the standard error data structure myself I am copying its definition from the QUSEC source member in QRPGSRC in the QSYSINC library.

Lines 3 – 16: This is the prototype definition for calling the QUSCRTUI API.

Line 3: On the first line of the Protype definition, DCL-PR, I have given the API what I think is a more descriptive name CrtUserIndex.

Lines 4 – 15: I never bother to give names to the various parameters. I define all of their names as null with *N. Lines 4 – 14 parameters are defined as CONST, constant, as I want to pass strings rather than variables, therefore, they are constants as values cannot be returned into these parameters.

Line 15: The error parameter returned from QUSCRTUI is the same as the standard error data structure. Therefore, I use LIKEDS to define it to be the same as QUSEC data structure. As this parameter can vary in size and does not have to be passed to the API I have defined it with OPTIONS(*VARSIZE : *NOPASS).

Lines 17 – 28: The call to QUSCRTUI. I have placed each of the parameters on their own lines so I can explain each one.

Line 17: The User Index name, positions 1 – 10, and Library, 11 – 20, I want it created in.

Line 18: I don't want to bother giving the User Index an extended attribute, so I pass null to it. Null here is represented by two single quotes ( ' ) next to each with nothing between them to indicate no value (= null) is passed.

Line 19: I am defining the entries to be variable in length. By doing this I don't waste space if the string is less than length of the entry. Also when I add entries to User Index I need to pass a string a length of entry. Trust me it is easier just to define the entry length to be variable.

Line 20: As the User Index entry has a variable length if I create it with a length of zero the API creates the User Index to have an entry length of up to 2,000 characters.

Line 21: I want to insert entries by key.

Line 22: Length of the key.

Line 23: I want to update the User Index immediately when I insert or change entries.

Line 24: Optimize the User Index for accessing data in sequential order.

Line 25: Public authority.

Line 26: Text description of the object.

line 27: Replace an existing User Index if one already exists.

Line 28: The errors are returned into the QUSEC data structure.

I compiled the program with the name CRTUSRIDX.

The rest of this example plays out in ACS's Run SQL Scripts.

First I need to create the User Index, I do that by calling the RPG program I just created, which I can in Run SQL Scripts by prefixing the command with "CL:".

01 CL:CALL PGM(MYLIB/CRTUSRIDX) ;

I can now check my User Index's definition using the USER_INDEX_INFO View:

02 SELECT * FROM QSYS2.USER_INDEX_INFO 03 WHERE USER_INDEX_LIBRARY = 'MYLIB' ;

As I only have one User Index in my library, MYLIB, only the results for this User Index is returned:

USER_ MAXIMUM INDEX_ USER_ ENTRY_ ENTRY_ _ENTRY_ INDEX IMMEDIATE LIBRARY INDEX TYPE LENGTH LENGTH _SIZE _UPDATE ------- --------- -------- ------ ------- ----- --------- MYLIB MYUSRIDX VARIABLE 0 2000 4 GB YES KEY_ KEY_ ENTRY_ ENTRIES ENTRIES OPTIMZATION INSERTION LENGTH TOTAL _ADDED _REMOVED ----------- --------- ------ ------ ------- -------- SEQUENTIAL YES 4 0 0 0 OBJECT_ DOMAIN TEXT_DESCRIPTION ------- ---------------- *USER My user index

Those match what I used in my RPG program.

Onto the first insertion into the User Index using the new Procedure, ADD_USER_INDEX_ENTRY.

04 CALL QSYS2.ADD_USER_INDEX_ENTRY(USER_INDEX => 'MYUSRIDX', 05 USER_INDEX_LIBRARY => 'MYLIB', 06 REPLACE => 'NO', 07 ENTRY => 'First entry added', 08 KEY => '0010') ;

Like all other procedures ADD_USER_INDEX_ENTRY has a number of parameters. Here I have used their names to illustrate what they are:

Line 4: Name the User Index.

Line 5: The library the User Index is in.

Line 6: The examples in IBM's documentation omit this parameter. When I omitted it I received a message:

SQL State: 428HF Vendor Code: -20484 Message: [SQ20484] Parameter 3 required for routine ADD_USER_INDEX_ENTRY in QSYS2. Cause . . . . . : A call statement for routine ADD_USER_INDEX_ENTRY in schema QSYS2 specified with a named parameter is not valid. Parameter 3 must be specified on the call statement because it has no default value. Recovery . . . : Specify a value for parameter 3. Try the request again.

Adding the REPLACE parameter stopped the error.

Line 7: The text I wanted in my entry. As it is variable in length it can be up to 2,000 characters.

Line 8: The key. It must be entered as a character value and be the length of the string, in this case four. Entering a key value of less than four returns an error.

I don't have to use the parameter names, I can just list the values:

09 CALL QSYS2.ADD_USER_INDEX_ENTRY('MYUSRIDX','MYLIB','NO','Second entry added', '0020') ; 10 CALL QSYS2.ADD_USER_INDEX_ENTRY('MYUSRIDX','MYLIB','NO','Third entry added', '0015') ;

Let me see the data I have inserted in the User Space. For that I use the USER_INDEX_ENTRIES Table Function:

11 SELECT * 12 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 13 USER_INDEX_LIBRARY => 'MYLIB', 14 USER_INDEX => 'MYUSRIDX')) ;

Lines 13 and 14: I need to pass USER_INDEX_ENTRIES the User index's name and library.

The results are:

USER_ ORDINAL_ INDEX_ USER_ KEY_ ENTRY POSITION LIBRARY INDEX KEY BINARY ENTRY BINARY -------- ------- -------- ---- -------- ------------------ -------- 1 MYLIB MYUSRIDX 0010 F0F0F1F0 First entry added C6899... 2 MYLIB MYUSRIDX 0015 F0F0F1F5 Third entry added E3888... 3 MYLIB MYUSRIDX 0020 F0F0F2F0 Second entry added E2858...

I added the entries in First, Second, Third order, notice that the entries are sorted by key, not arrival, sequence.

Let me update/amend one of the existing entries:

15 CALL QSYS2.ADD_USER_INDEX_ENTRY('MYUSRIDX','MYLIB','YES','Second amended', '0020') ;

Line 15: The third parameter is 'YES' for update, and I have a new entry text.

I have reduced the columns returned by USER_INDEX_ENTRIES to only the ones I am interested in:

16 SELECT ORDINAL_POSITION,KEY,ENTRY 17 FROM TABLE(QSYS2.USER_INDEX_ENTRIES( 18 USER_INDEX_LIBRARY => 'MYLIB', 19 USER_INDEX => 'MYUSRIDX')) ;

And the entry with a key value of 20 has been updated.

ORDINAL_ POSITION KEY ENTRY -------- ---- ----------------- 1 0010 First entry added 2 0015 Third entry added 3 0020 Second amended

My last line deletes the User Index, as I am now finished with it.

20 CL:DLTUSRIDX USRIDX(MYLIB/MYUSRIDX) ;

I am very impressed with how easy it has become inserting and updating entries in User Indexes by using ADD_USER_INDEX_ENTRY. There is also a ADD_USER_INDEX_ENTRY_BINARY Procedure that does the same as the other Procedure but using binary values for the key and entry. IMHO why would I want to insert the data in binary when it is so much easier to insert regular characters.

 

You can learn more about the ADD_USER_INDEX_ENTRY and ADD_USER_INDEX_ENTRY_BINARY SQL Procedures from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.