Changing data within User Space with SQL

As part of the IBM i 7.5 and 7.4 TR6 release and refresh we have been given two new SQL Procedures that allow us to change data within an User Space. The first allows me to change the data within the User Space in character format, the other as binary.

Before I start showing these new Procedures I need a User Space. I can create this with SQL using the CREATE_USER_SPACE Procedure:

CALL QSYS2.CREATE_USER_SPACE('MYUSRSPC','MYLIB',131072,'YES', '','*USE','YES')

This is also a new Procedure, you can learn more about it by reading this post.

There already is a SQL Table Function that allows me to retrieve data from an User Space, USER_SPACE:

SELECT DATA FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC'))

The returned result shows that the User Space contains blanks:

DATA --------------------------------

Both CHANGE_USER_SPACE and CHANGE_USER_SPACE_BINARY have the same parameters:

01 CALL QSYS2.CHANGE_USER_SPACE(USER_SPACE => 'MYUSRSPC', 02 USER_SPACE_LIBRARY => '*LIBL', 03 DATA => 'Some text', 04 START_POSITION = 1, 05 FORCE = 'NO')

Line 1: Name of the User Space to change data within.

Line 2: Library the User Space is in. This parameter will accept *LIBL.

Line 3: The text to change the contents of the User Space.

Line 4: Start position of where the text should start. The default is 1.

Line 5: Should the changes made to the User Space be forced to auxiliary storage. The default is 'NO'.

In my first example statement I want to insert some text into the User Space starting in the first position:

CALL QSYS2.CHANGE_USER_SPACE(USER_SPACE => 'MYUSRSPC', USER_SPACE_LIBRARY => '*LIBL', DATA => '<-Simon was here--->')

As I did not give a starting position then the text will start in the first position. I can show this using the USER_SPACE Table Function:

SELECT DATA FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) DATA -------------------------------- <-Simon was here--->

I want to add more text to the User Space that follows what I had above. Here I need to start in the 21st position so I do not overlay what is already in the User Space.

CALL QSYS2.CHANGE_USER_SPACE(USER_SPACE => 'MYUSRSPC', USER_SPACE_LIBRARY => '*LIBL', DATA => '<-He was here too-->', START_POSITION => 21)

Below shows that both of the strings of text are in the User Space.

SELECT DATA FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) DATA ---------------------------------------- <-Simon was here---><-He was here too-->

I can overlay what is already in the User Space. In this example my string starts at position 13 will overlay both strings. The statement does not include the parameter names, which are optional:

CALL QSYS2.CHANGE_USER_SPACE('MYUSRSPC','*LIBL', '<-In the middle---->', 13)

The results from USER_SPACE show that I have overlayed the previous strings:

SELECT DATA FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) DATA ---------------------------------------- <-Simon was <-In the middle---->e too-->

What happens when I change text "further out" in the User Space, let's say starting in the 100th position:

CALL QSYS2.CHANGE_USER_SPACE('MYUSRSPC','*LIBL', '<-All the way out here->', START_POSITION => 100)

The results returned by USER_SPACE are misleading. With the following statement is looks like the text from above was added following the text I added before:

SELECT DATA FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) ; DATA ---------------------------------------------------------------- <-Simon was <-In the middle---->e too--><-All the way out here->

If I use a substring in my SQL statement, starting at the 100th position of the DATA column, I find what I added is really out there:

SELECT SUBSTR(DATA,100,30) FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) 00001 ------------------------- <-All the way out here->

Before I start using the CHANGE_USER_SPACE_BINARY Procedure I am going to delete and re-create the User Space:

CL: DLTUSRSPC MYLIB/MYUSRSPC ; CALL QSYS2.CREATE_USER_SPACE('MYUSRSPC',' MYLIB',131072,'YES', '','*USE','YES') ; SELECT DATA,DATA_BINARY FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) ; DATA DATA_BINARY ---- ---------------------------- 0000000000000000000000000...

I selected the DATA and DATA_BINARY columns so I can see the contents of the User Space in both character and binary formats. As I have not changed the User Space it is blank.

I could manually translate character to binary for use in this examples. But I find it easier to use the BINARY scalar function to perform the translation for me:

VALUES BINARY('<-Simon used binary->') 00001 ------------------------------------------ 4C60E28994969540A4A28584408289958199A8606E

I can copy the binary result into the CHANGE_USER_SPACE_BINARY statement below:

CALL QSYS2.CHANGE_USER_SPACE_BINARY( USER_SPACE => 'MYUSRSPC', USER_SPACE_LIBRARY => '*LIBL', DATA => '4C60E28994969540A4A28584408289958199A8606E')

The results show that the User Profile contains the text I changed in it:

SELECT DATA,SUBSTR(DATA_BINARY,1,30) FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) DATA DATA_BINARY --------------------- -------------------------------------------- <-Simon used binary-> 4C60E28994969540A4A28584408289958199A8606E00

Or I could just use the BINARY scalar function within the CHANGE_USER_SPACE_BINARY statement:

CALL QSYS2.CHANGE_USER_SPACE_BINARY( 'MYUSRSPC','*LIBL', BINARY('<-Start again->'), 1)

I know that translating a string to binary and then using CHANGE_USER_SPACE_BINARY Procedure is redundant, I just wanted to show it was possible.

The string starts a position 1 which overlays the previous text.

SELECT DATA,SUBSTR(DATA_BINARY,1,30) FROM TABLE(QSYS2.USER_SPACE('MYUSRSPC')) DATA DATA_BINARY --------------------- -------------------------------------------- <-Start again->nary-> 4C60E2A38199A3408187818995606E958199A8606E00

I can see myself using the CHANGE_USER_SPACE Procedure, but I cannot think of a reason I would need CHANGE_USER_SPACE_BINARY. There must be a reason for having it otherwise it would not have been created.

 

You can learn more about the CHANGE_USER_SPACE and CHANGE_USER_SPACE_BINARY SQL Procedures from the IBM website here.

 

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