Getting data from User Spaces made easy with SQL

The only way I have known how to write data to a User Space and read it has been by using APIs. With the last round of Technology Refreshes for IBM i 7.4 and 7.3 came an alternative to using an API to read.

There are two additions to SQL to help with User Space:

• View to list all User Space
• Table Function to view the data within an User Space

 

View to list all User Space

To be honest there is not much I feel I can say about the View, USER_SPACE_INFO which is found in the library QSYS2.

Being a View it will list all of the User Space in my partition if I do not use the WHERE clause in my SQL Select statement. In this example I am only interested by the one User Space that is found in my library, MYLIB.

SELECT * FROM QSYS2.USER_SPACE_INFO WHERE USER_SPACE_LIBRARY = 'MYLIB' ;

The View only contains seven columns, most of them are obvious for what information they contain.

USER_SPACE USER_ EXTEN- INITIAL OBJECT _LIBRARY SPACE SIZE DABLE _VALUE _DOMAIN TEXT_DESCRIPTION ---------- ----- ---- ------ ------- ------- ------------------------ MYLIB TEST 4096 NO 00 *USER User Space used for test

EXTENDABLE:  This User Space will not automatically extend itself.

INITIAL_VALUE:  The value that will be used in the new space when the User Space is extended

OBJECT_DOMAIN:  This User Space is in the user domain. The other value for this column is *SYSTEM to flag a User Space that is in the system domain.

 

Table Function to view the data within an User Space

This Table Function got me excited. Now I have an easy way to get the data from a User Space without using APIs!

I used the APIs from the post I first wrote about creating and writing data to a user space.

Before I can read the User Space I need to write data to it. This program does that:

01 **free 02 /copy qsysinc/qrpglesrc,qusec //Error DS for APIs 03 dcl-pr CrtUserSpace extpgm('QUSCRTUS') ; 04 *n char(20) const ; // Name 05 *n char(10) const ; // Attribute 06 *n int(10) const ; // Initial size 07 *n char(1) const ; // Initial value 08 *n char(10) const ; // Authority 09 *n char(50) const ; // Text 10 *n char(10) const ; // Replace existing 11 *n char(32767) options(*varsize:*nopass) ; // Error feedback 12 end-pr ; 13 dcl-pr ChgUserSpace extpgm('QUSCHGUS') ; 14 *n char(20) const ; // Name 15 *n int(10) const ; // Starting position 16 *n int(10) const ; // Length of data 17 *n char(100) const ; // Input data 18 *n char(1) const ; // Force changes to auxillary storage 19 *n char(32767) options(*varsize:*nopass) ; // Error feedback 20 end-pr ; 21 dcl-ds TestDs qualified ; 22 Str char(5) inz('<str>') ; 23 Data char(15) ; 24 End char(5) inz('<end>') ; 25 end-ds ; 26 dcl-s UserSpaceName char(20) inz('TEST MYLIB') ; 27 CrtUserSpace(UserSpaceName:'':1:x'00':'*ALL': 28 'User Space used for test': 29 '*YES':QUSEC) ; 30 TestDs.Data = 'First attempt' ; 31 ChgUserSpace(UserSpaceName:1:%size(TestDs):TestDs:'0':QUSEC) ; 32 TestDs.Data = 'Second attempt' ; 33 ChgUserSpace(UserSpaceName:(%size(TestDs) + 1):%size(TestDs): 34 TestDs:'0':QUSEC) ; 35 *inlr = *on ;

I don't want to go into too much detail about this program as I described how to call the APIs, etc. here.

Lines 3 – 12: This is the definition for the API used to create a User Space. The regular readers know that I do not give the API's parameters names, which is why they are all *N.

Lines 13 – 20: This is the API that writes to the user space.

Lines 21 – 25: I am using this data structure to contain the data I will be writing to the User Space.

Lines 27 - 29: Calling the API to create the User Space.

Lines 30 and 31: Call the API to write to the User Space. Notice that in the third parameter rather than hard code a number I am using the %SIZE built in function. This way I can change the data structure and not have to change the call to the API.

Lines 32 – 34: I am calling the API to write a second time. This time the starting position of where to write, second parameter, is calculating using the size of the data structure.

Now I have data in the User Space what is the SQL statement I can use to see it?

SELECT * FROM TABLE(QSYS2.USER_SPACE( USER_SPACE => 'TEST', USER_SPACE_LIBRARY => 'MYLIB'));

The Table Function USER_SPACE has two parameters. In my opinion it is obvious what information they need to contain. When I run this statement I get the results shown below:

USER_SPACE_LIBRARY USER_SPACE ------------------ ---------- MYLIB TEST DATA ------------------------------------------------------ <str>First attempt <end><str>Second attempt <end> ... DATA_BINARY ------------------------------------------------------ 4CA2A3996EC68999A2A34081A3A3859497A340404C8595846 ...

The column DATA is CLOB with a length of 16M bytes, and the DATA_BINARY is a BLOB of 16M.

If I was to use this in a real world situation I would not have the User Space name and library in the results as I know what those are as I used them to call this Table Function. I know that my elements/pieces/chunks (you choose which word to use) of data are 25 character so I can break them out just by using a substring statement:

SELECT SUBSTR(DATA,1,25) AS "1st data", SUBSTR(DATA,26,25) AS "2nd data" FROM TABLE(QSYS2.USER_SPACE('TEST'));

I am sure you have noticed that I did not pass the library. As I did not *LIBL is the default.

The results of my substrings are:

1st data 2nd data ------------------------- ------------------------- <str>First attempt <end> <str>Second attempt <end>

In a real world I would want to just use this SQL statement on its own to get the data out of the User Space. I would want a RPG program to do it:

01 **free 02 dcl-ds *n ; 03 FromUSRSPC char(1000) ; 04 Array char(25) dim(33) pos(1) ; 05 end-ds ; 06 exec sql SELECT DATA INTO :FromUSRSPC 07 FROM TABLE(QSYS2.USER_SPACE('TEST')) ; 08 *inlr = *on ;

Yes, only eight lines of code!

Lines 2 – 5: I am using this data structure to define a 1,000 long character subfield, FromUSRSPC, and on the line below it an array that overlays it. Note that the data structure does not have a name, that is why it has *N where the name is usually found.

Lines 6 and 7: I am using a Select statement to place the results into the program variable FromUSRSPC.

I start debug and place a breakpoint on line 8. When I do I display the contents of the array:

EVAL array ARRAY(1) = '<str>First attempt <end>' ARRAY(2) = '<str>Second attempt <end>' ARRAY(3) = ' ' ARRAY(4) = '

Wow! That was easy.

 

You can learn more about this from the IBM website:

• USER_SPACE_INFO View
• USER_SPACE Table Function

 

This article was written for IBM i 7.4 TR4, and will work for 7.3 TR10 too.