Pages

Tuesday, December 13, 2022

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.

1 comment:

  1. Thank you Simon for sharing this. This is much easier than the API.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.