Tuesday, October 4, 2022

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.

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:

 

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

No comments:

Post a Comment

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.