Start of change

ADMIN_INFO_SMS stored procedure

The ADMIN_INFO_SMS stored procedure returns space information about copy pools and their storage groups and volumes.

Environment

Begin general-use programming interface information.

The load module for this stored procedure, DSNADMIV, must reside in an APF-authorized library. ADMIN_INFO_SMS runs in a WLM-established stored procedures address space. All libraries in this WLM procedure STEPLIB DD concatenation must be APF-authorized.

Authorization

To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges on each package that the stored procedure uses:

  • The EXECUTE privilege on the package for DSNADMIV
  • Ownership of the package
  • PACKADM authority for the package collection
  • SYSADM authority

Syntax

The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:

>>-CALL--SYSPROC.ADMIN_INFO_SMS--(--return-code,--message--)---><

Option descriptions

return-code
Provides the return code from the stored procedure. The following values are possible:
0
The stored procedure completed successfully.
4
The stored procedure could not return the volume space statistics for all of the requested objects. The message output parameter contains messages that describe the warnings.

This return code is issued when one or more objects that are returned in the result set contains one of the following messages in the ERRMSG column:

  • OBJECT NOT FOUND: Indicates that the copy pool, storage group, or volume that is specified in the input table is not found, as reported by the SMS construct access services.
  • NO VOLUMES ASSOCIATED WITH STORAGE GROUP: Indicates that the storage group that is specified in the input table does not have any volumes, as reported by the SMS construct access services.
  • NO STORAGE GROUPS ASSOCIATED WITH COPY POOL: Indicates that the copy pool that is specified in the input table does not have any storage groups, as reported by the SMS construct access services.
  • DSNA661I DSNADMIV THE MACRO LSPACE FAILED WITH RETURN CODE=nn
12
The call did not complete successfully. The message output parameter contains messages that describe a parameter error, an SQL error, or an internal error that was encountered by the stored procedure.

This is an output parameter of type INTEGER.

message
Contains messages that describe a parameter error, an SQL error, or an internal error that was encountered by the stored procedure. If an error did not occur, a message is not returned.

This is an output parameter of type VARCHAR(1331).

Input

This stored procedure reads from the created global temporary table SYSIBM.SMS_OBJECTS to determine which objects' space statistics to return.

The following table shows the format of the SYSIBM.SMS_OBJECTS input table.

Table 1. Input row for the ADMIN_INFO_SMS stored procedure
Column name Data type Contents
OBJECTID
INTEGER
NOT NULL
A unique positive identifier 
for the object that a set of
volume space statistics is
associated with.
OBJECTTYPE
CHAR(1)
NOT NULL
 Valid values are:
  • 'C': Copy pool
  • 'S': Storage group
  • 'V': Volume
OBJECTNAME
VARCHAR(30)
NOT NULL
 Valid values are:
  • Copy pool name, if OBJECTTYPE is 'C'. Returns all of the volumes that are associated with each storage group in this copy pool, along with the corresponding volume space statistics.
  • Storage group name, if OBJECTTYPE is'S'. Returns all of the volumes that are associated with this storage group, along with the corresponding volume space statistics.
  • Volume name, if OBJECTTYPE is 'V'. Returns the volume space statistics for this volume.

Output

This stored procedure returns the following output parameters, which are described in Option descriptions:

  • return-code
  • message

In addition to the preceding output, the stored procedure returns one result set that contains the volume space statistics.

The following table shows the format of the result set that is returned in the created global temporary table SYSIBM.SMS_INFO. The result set rows are returned in ascending order by ROWNUM and OBJECTID.

Table 2. Result set row for ADMIN_INFO_SMS result set
Column name Data type Contents
ROWNUM
INTEGER
NOT NULL
 Sequence number of the table row, from 1 to n.
OBJECTID
INTEGER
NOT NULL
A unique positive identifier for the
object that a set of volume space
statistics is associated with
This ID is used to reference the
object in the input table
SYSIBM.SMS_OBJECTS.
COPYPOOL
VARCHAR(30)
 A copy pool. This value is NULL if a storage group or volume is specified. For example, if the corresponding OBJECTTYPE in the SYSIBM.SMS_OBJECTS table is 'S' or 'V'.
STORAGEGROUP VARCHAR(30) A copy pool storage group. This value is NULL if no storage groups are associated with the specified copy pool, or if a volume is specified. For example, if the corresponding OBJECTTYPE in the SYSIBM.SMS_OBJECTS table is ‘V'.
VOLUME VARCHAR(6) A volume in a copy pool storage group. This value is NULL in the following cases:
  • If no volumes are associated with the specified storage group
  • If no volumes are associated with a storage group that is associated with the specified copy pool
  • If no storage groups are associated with the specified copy pool
TOTALCAPACITY
INTEGER
NOT NULL
 Total capacity of a volume, in megabytes. The value is -1 if the VOLUME column is NULL.
FREESPACE
INTEGER
NOT NULL
 The total amount of free space in a volume, in megabytes. The value is -1 if the VOLUME column is NULL.

For a DB2® volume, this is the amount of space in the copy pool that has not been allocated by DB2 yet. Therefore, disk space that has been allocated by DB2, but is currently not used (like data records that were deleted) would not fall into this category.
LARGESTFREEEXT
INTEGER
NOT NULL
 Largest free extent in a volume, in megabytes. The value is -1 if the VOLUME column is NULL.
ERRMSG VARCHAR(256) Possible values are:
  • NULL
  • OBJECT NOT FOUND: Indicates that the copy pool, storage group, or volume that is specified in the input table is not found, as reported by the SMS construct access services.
  • NO VOLUMES ASSOCIATED WITH STORAGE GROUP: Indicates that the storage group that is specified in the input table does not have any volumes, as reported by the SMS construct access services.
  • NO STORAGE GROUPS ASSOCIATED WITH COPY POOL: Indicates that the copy pool that is specified in the input table does not have any storage groups, as reported by the SMS construct access services.
  • DSNA661I DSNADMIV THE MACRO LSPACE FAILED WITH RETURN CODE=nn

End general-use programming interface information.

End of change