Pages

Wednesday, June 30, 2021

Getting data from User Spaces made easy with SQL

sql to get user space data

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

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:

 

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

9 comments:

  1. We've been able to access/update User Spaces via a pointer since the beginning of RPG IV Simon. No need for APIs or the overhead of using SQL. Just access it like any other memory in your program.

    It is covered in the original RPG Sorcerers Guide.

    ReplyDelete
    Replies
    1. +1 (and getting tired of this Let's-do-it-all-in-SQL hype).

      In my personal opinion IBM is wasting a lot of money and developer resources for things that can already be done for +20 years while there are simple things that still can't be done in (embedded) SQL.

      Delete
    2. Jon and Paul,

      You are being short sighted. IBM is exposing the system ways others not used to the IBM i can better understand. Everything IBM exposes through SQL is worth while especially if the item exposed is for something that is already available so as to not take significant resources to build the SQL part.

      -Matt

      P.S., I don't know what I would want to do with this yet but its not wrong to have it.

      Delete
    3. SQL is a pretty effective appetizer. You can lure new Devs on the platform... that's the way they've got me and Simon trapped me here with great success.

      Delete
  2. I never through about this being possible. Never had to retrieve user space data. Only needed the space for system information I needed to verify . Which mean it’s being saved.. very interesting read with good examples. DB2 and the IBMi is a great marriage Thanks for sharing.

    ReplyDelete
  3. Thank you for sharing

    ReplyDelete
  4. As noted earlier, providing SQL interfaces allows non-IBM i programmers to access the system from apps using JDBC or ODBC or other similar contexts. In addition, stored procedures and user defined functions written in SQL can have access to this data without needing to call non-SQL UDFs and making things more complex.

    ReplyDelete
  5. I'm not able to see the data using above SQL, it is displayed as *Pointer instead of actual data.. Any idea? Please

    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.