Create and change User Spaces' attributes with SQL

In IBM i 7.5 and 7.4 TR6 the Db2 for i team introduced two new SQL Procedures for creating and changing the attributes of User Spaces, rather than using APIs.

• Create User Space
• Change a User Spaces attributes

I am going to show examples of these below.

 

Create User space

I have written about using the API to create a User Space, I am going to copy some of the code from that post to illustrate the API method versus the SQL Procedure.

I would use the API within a RPG program. First I would have to define the prototype for the API, QUSCRTUS:

01 dcl-pr CrtUserSpace extpgm('QUSCRTUS') ; 02 *n char(20) const ; // Name 03 *n char(10) const ; // Attribute 04 *n int(10) const ; // Initial size 05 *n char(1) const ; // Initial value 06 *n char(10) const ; // Authority 07 *n char(50) const ; // Text 08 *n char(10) const options(*nopass) ; // Replace existing 09 *n char(32767) options(*varsize:*nopass) ; // Error feedback 10 end-pr ;

I am not going to describe the parameters in this post, go to the original and read about them there.

I can use the following statement to call the API and create the user space:

38 CrtUserSpace('MYUSRSPC MYLIB':'':131072:x'00': '*USE':'Created by API':'*YES':QUSEC) ;

The equivalent with the new Procedure CREATE_USER_SPACE, including the parameters names, would be:

01 CALL QSYS2.CREATE_USER_SPACE( 02 USER_SPACE => 'MYUSRSPC', 03 USER_SPACE_LIBRARY => 'MYLIB', 04 SIZE => 131072, 05 REPLACE => 'YES', 06 TEXT_DESCRIPTION => 'Created by CREATE_USER_SPACE', 07 PUBLIC_AUTHORITY => '*USE', 08 EXTENDABLE => 'YES', 09 -- INITIAL_VALUE => Use default, 10 OBJECT_ATTRIBUTE => 'SIMON', 11 -- TRANSFER_SIZE => Use default, 12 OBJECT_DOMAIN => '*USER')

Lines 2 and 3: The User Space name and library are in seperate parameters.

Line 4: The initial size of the User Space.

Line 5: If there is already a User Space with the same name in the library should it be replaced? In this case the answer is yes.

Line 6: If I want the User Space to have a description I would give it here.

Line 7: Authority that I give the User Space. I have chosen use authority, allowing others to use the User Space.

Line 8: Will the User Space be automatically extend?

Line 9: I have commented out this line. This is where I can give the User Space an initial value, as a binary string. If I do use the parameter the Procedure will initialize the User Space with the value of x'00'.

Line 10: If I want to give the User Space a object attribute I can do so here. An object attribute can be up to 10 characters. If this parameter is not used the object attribute will be blank.

Line 11: The number of pages to be transferred between main and auxiliary storage. By commenting out this parameter it will use the default of zero.

Line 12: The domain into which the User Space is created. I have chosen to create it in the user domain. Other options are: *SYSTEM, which will create the User Space in the system domain, and *DEFAULT, where the system will decide the domain the User Space is created in.

As with other SQL Procedures and Table Functions I do not have to give the parameter names. For example, the statement will create the User Space with a minimum of parameters used:

01 CALL QSYS2.CREATE_USER_SPACE('MYUSRSPC','MYLIB',131072,'YES', 02 'Created by CREATE_USER_SPACE','*USE','YES')

if I want to check that the User Space has been created the way I desire I can use the USER_SPACE_LIBRARY View:

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

The results show that everything is as I wanted it.

USER_ SPACE_ USER_ INITIAL OBJECT_ TEXT LIBRARY SPACE SIZE EXTENDABLE _VALUE DOMAIN DESCRIPTION ------- -------- ------ ---------- ------- ------ --------------- MYLIB MYUSRSPC 131072 YES 00 *USER Created by C...

 

Change a User Spaces attributes

If I had made a mistake and had not created the User Space the way I wanted, rather than delete it and re-create it I can use the other new SQL Procedures CHANGE_USER_SPACE_ATTRIBUTES.

CHANGE_USER_SPACE_ATTRIBUTES has a subset of the parameters that CREATE_USER_SPACE does. In the statement below I am changing the User Space's attributes for no other reason than just because I can:

01 CALL QSYS2.CHANGE_USER_SPACE_ATTRIBUTES ( 02 USER_SPACE => 'MYUSRSPC', 03 USER_SPACE_LIBRARY => 'MYLIB', 04 SIZE => 26000, 05 EXTENDABLE => 'NO', 06 INITIAL_VALUE => '01', 07 TRANSFER_SIZE => 0)

I can check if my changes happened using the USER_SPACE_LIBRARY View again:

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

The size column in the results is not the size I wanted, 26000. I can only assume that the additional size is needed within the User Space for header information.

USER_ SPACE_ USER_ INITIAL OBJECT_ TEXT LIBRARY SPACE SIZE EXTENDABLE _VALUE DOMAIN DESCRIPTION ------- -------- ------ ---------- ------- ------ --------------- MYLIB MYUSRSPC 28672 NO 01 *USER Created by C...

To change everything back to the way it was before I made the change above, I use the CHANGE_USER_SPACE_ATTRIBUTES again:

CALL QSYS2.CHANGE_USER_SPACE_ATTRIBUTES ('MYUSRSPC','MYLIB',131072,'YES','00',0)

When I use USER_SPACE_INFO...

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

I can see that everything is back to the way it was before I made the change:

USER_ SPACE_ USER_ INITIAL OBJECT_ TEXT LIBRARY SPACE SIZE EXTENDABLE _VALUE DOMAIN DESCRIPTION ------- -------- ------ ---------- ------- ------ --------------- MYLIB MYUSRSPC 131072 YES 00 *USER Created by C...

 

There is no need for a SQL Procedure to delete a User Space as I can do that with the Delete User Space command, DLTUSRSPC:

DLTUSRSPC USRSPC(MYLIB/MYUSRSPC)

 

You can learn more about this from the IBM website:

• CREATE_USER_SPACE Procedure
• CHANGE_USER_SPACE_ATTRIBUTE Procedure

 

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