Christmas has just finished – how do I slim down my IBM i System?
I like to look in all the nooks and crannies for data that has been left behind, or simply accumulating and never being consumed.
Data Queues are small and fast. The downside is that loading data queues over long periods of time, and they will grow and grow and grow.
We can easily see the sizes of all data queues on the machine using an IBM supplied view. This DATA_QUEUE_INFO view returns a row for every data queue.
The information returned is similar to the information available through the Retrieve Data Queue Description (QMHQRDQD) API.Authorization: The caller must have:
- *EXECUTE authority to the library containing the data queue, and
- *OBJOPR and *READ authority to the data queue.
The following table describes the columns in the view. The system name is DTAQ_INFO. The schema is QSYS2.
Finding large objects is easy with this IBM supplied SQL magic:
select data_queue_name,
data_queue_library,
data_queue_type,
current_messages,
text_description
from qsys2.data_queue_info
where current_messages > 100
and substr(data_queue_name, 1, 1) <> 'Q'
order by current_messages descHere I only selecting a few columns but the results are very clear:
The column types are extensive:
| Column name | System column name | Data type | Description |
|---|---|---|---|
| DATA_QUEUE_LIBRARY | DTAQ_LIB | VARCHAR(10) | Library containing the data queue. |
| DATA_QUEUE_NAME | DTAQ | VARCHAR(10) | Name of the data queue. |
| DATA_QUEUE_TYPE | DTAQ_TYPE | VARCHAR(8) | This will be set to one of the following values:DDMThe data queue is a DDM data queue.STANDARDThe data queue is a standard data queue. |
| MAXIMUM_MESSAGE_LENGTH | MSG_LENGTH | INTEGERNullable | The maximum length allowed for messages. This is the value that was specified with the MAXLEN keyword on the CRTDTAQ command.Contains the null value for a DDM data queue. |
| SEQUENCE | SEQUENCE | VARCHAR(5)Nullable | The sequence in which messages can be removed from the queue.FIFOFirst-in first-outKEYEDKeyedLIFOLast-in first-outContains the null value for a DDM data queue. |
| KEY_LENGTH | KEY_LENGTH | INTEGERNullable | The length of the message reference key for a keyed data queue, in bytes. Values are from 1 to 256.Contains the null value if this is not a keyed queue or is a DDM data queue. |
| INCLUDE_SENDER_ID | SENDER_ID | VARCHAR(3)Nullable | Indicates if the queue was created to include the sender ID with sent messages.NOThe sender ID is not included when data is sent to the data queue.YESThe sender ID is included when data is sent to the data queue.Contains the null value for a DDM data queue. |
| CURRENT_MESSAGES | CUR_MSGS | INTEGERNullable | The number of messages currently on the data queue.Contains the null value for a DDM data queue. |
| MAXIMUM_MESSAGES | MAX_MSGS | INTEGERNullable | The maximum number of messages that will fit into the data queue.Contains the null value for a DDM data queue. |
| SPECIFIED_MAXIMUM_MESSAGES | SPEC_MAX | INTEGERNullable | The maximum number of messages that was specified on the SIZE keyword of the CRTDTAQ command. Can contain the following special values:-1*MAX16MB was specified for the data queue size.-2*MAX2GB was specified for the data queue size.Contains the null value for a DDM data queue. |
| INITIAL_MESSAGE_ALLOCATION | INIT_ALOC | INTEGERNullable | The number of messages that will fit into the storage allocated for the data queue when it is created or when it is automatically reclaimed.Contains the null value for a DDM data queue. |
| CURRENT_MESSAGE_ALLOCATION | CUR_ALOC | INTEGERNullable | The number of entries that will fit into the data queue before it is extended. When the queue is extended, additional storage is allocated for the queue. The data queue can be extended until it reaches the value for the maximum number of entries allowed.Contains the null value for a DDM data queue. |
| FORCE | FORCE | VARCHAR(3)Nullable | Whether the data queue is forced to auxiliary storage when entries are sent or received.NOThe data queue is not forced to auxiliary storage after entries are sent or received.YESThe data queue is forced to auxiliary storage after entries are sent or received.Contains the null value for a DDM data queue. |
| AUTOMATIC_RECLAIM | RECLAIM | VARCHAR(3)Nullable | Whether or not the data queue has the amount of storage allocated for the queue reclaimed when the queue is empty.NOStorage is not reclaimed.YESStorage is reclaimed when the queue is empty. The amount of storage allocated will be set to the initial number of entries.Contains the null value for a DDM data queue. |
| LAST_RECLAIM_TIMESTAMP | RECLAIM_TS | TIMESTAMPNullable | The date and time that the last automatic reclaim was done.Contains the null value for a DDM data queue or when no reclaim has occurred for a standard data queue. |
| ENFORCE_DATA_QUEUE_LOCKS | DTAQ_LOCKS | VARCHAR(3)Nullable | Identifies whether IBM®-supplied data queue operations will enforce a lock on the data queue. This attribute cannot be specified on the Create Data Queue (CRTDTAQ) CL Command. The default when a data queue is created is for locks to be ignored. A data queue can be locked with the Allocate Object (ALCOBJ) CL Command. When locks are enforced, performance can be degraded due to the additional locking performed by all data queue operations.NOLocks on the data queue are ignored by IBM-supplied data queue operations.YESLocks on the data queue are enforced by IBM-supplied data queue operations.Contains the null value for a DDM data queue. |
| TEXT_DESCRIPTION | TEXT | VARCHAR(50)Nullable | The text description of the data queue.Contains the null value if there is no text description. |
| REMOTE_DATA_QUEUE_LIBRARY | RMT_DTAQL | VARCHAR(10)Nullable | The name of the library for the remote data queue on the target system. This is the data queue name that was specified on the RMTDTAQ parameter of the CRTDTAQ command. Can contain the following special values:*CURLIBThe current library will be used to find the data queue.*LIBLThe library list will be used to find the data queue.Contains the null value for a standard data queue. |
| REMOTE_DATA_QUEUE | RMT_DTAQ | VARCHAR(10)Nullable | The name of the remote data queue on the target system. This is the data queue name that was specified on the RMTDTAQ parameter of the CRTDTAQ command.Contains the null value for a standard data queue. |
| REMOTE_LOCATION | REMOTE_LOC | VARCHAR(8)Nullable | The name of the remote location that is used with this object. This is the name that was specified on the RMTLOCNAME parameter of the CRTDTAQ command. Can contain the following special value:*RDBThe remote location information from the relational database entry returned in the relational database entry name field is used to determine the remote system.Contains the null value for a standard data queue. |
| RELATIONAL_DATABASE_NAME | RDBNAME | VARCHAR(18)Nullable | The name of the relational database entry that identifies the target system or target ASP group. This is the name that was specified on the RDB parameter of the CRTDTAQ command.Contains the null value for a standard data queue or a data queue that is not an RDB type DDM data queue. |
| APPC_DEVICE_DESCRIPTION | APPC_DEVD | VARCHAR(10)Nullable | The name of the APPC device description on the source system that is used with this DDM data queue. This is the name that was specified on the DEV parameter of the CRTDTAQ command. Can contain the following special value:*LOCThe device associated with the remote location is used.Contains the null value for a standard data queue and for RDB type DDM data queues. |
| LOCAL_LOCATION | LOCAL_LOC | VARCHAR(8)Nullable | The name of the local location. This is the name that was specified on the LCLLOCNAME parameter of the CRTDTAQ command. Can contain the following special values:*LOCThe device associated with the remote location is used.*NETATRThe LCLLOCNAME value specified in the system network attributes is used.Contains the null value for a standard data queue and for RDB type DDM data queues. |
| MODE | MODE | VARCHAR(8)Nullable | The mode name used with the remote location name to communicate with the target system. This is the name that was specified on the MODE parameter of the CRTDTAQ command. Can contain the following special value:*NETATRThe mode name specified in the system network attributes is used.Contains the null value for a standard data queue and for RDB type DDM data queues. |
| REMOTE_NETWORK_ID | REMOTE_NET | VARCHAR(8)Nullable | The remote network identifier in which the remote location used to communicate with the target system. This is the name that was specified on the RMTNETID parameter of the CRTDTAQ command. Can contain the following special values:*LOCThe remote network ID associated with the remote location is used.*NETATRThe remote network ID value specified in the system network attributes is used.*NONENo remote network ID is used.Contains the null value for a standard data queue and for RDB type DDM data queues. |
I found some fat data queues – what now?
If you have researched those data queues and confirmed that they can be cleared this nice little command will do the business. Using my Projex4i toolkit command CLRDTAQ
CLRDTAQ SDSM0045
Preparing to clear DTAQ(SMSLIVF/SDSM0045)
Clearing Data Queue -SMSLIVF/SDSM0045
826840 entries discarded from SMSLIVF/SDSM0045
Data Queue SMSLIVF/SDSM0045 cleared.