ASP_VARY_INFO view

The ASP_VARY_INFO view returns one row for each step associated with a vary on or vary off operation for all independent ASP devices.

The values returned for the columns in the view are similar to the values returned by the Display ASP Status (DSPASPSTS) CL command.

Authorization: The privileges held by the authorization ID of the statement must have *USE authority to the independent ASP device description. If the user does not have *USE authority to all independent ASP device descriptions, a warning is returned to indicate that partial data is returned.

The following table describes the columns in the view. The system name is VARY_INFO. The schema is QSYS2.

Table 1. ASP_VARY_INFO view
Column Name	System Column Name	Data Type	Description
IASP_NAME	IASP_NAME	VARCHAR(10)	The name of the ASP device description.
OPERATION_NUMBER	OP_NUMBER	INTEGER	A value for an instance of a vary on or vary off operation, where the highest number is the most recent operation. The most recent 64 operations are returned.
OPERATION_TYPE	OP_TYPE	VARCHAR(8)	The type of vary operation. VARY OFF VARY ON
OPERATION_STATE	OP_STATE	VARCHAR(8)	The state of the entire operation. ACTIVE The operation is active. `STEP_STATE` shows the status of the steps that are part of the operation. COMPLETE The operation completed successfully. FAILED The operation failed to complete successfully.
STEP	STEP	VARGRAPHIC(50) CCSID 1200	The description of the operation step.
STEP_STATE	STEP_STATE	VARCHAR(8)	The state of the operation step. ACTIVE The step is active. COMPLETE The step completed successfully. FAILED The step failed to complete successfully.
START_TIMESTAMP	START	TIMESTAMP	The timestamp for the start of this operation step.
END_TIMESTAMP	END	TIMESTAMP Nullable	The timestamp for the end of this operation step. Contains the null value if the operation step has not completed or may never complete.
DURATION	DURATION	DECIMAL(12,6) Nullable	The time duration, in seconds, of this operation step. Contains the null value if the operation step has not completed or may never complete.
JOB_NAME	JOB_NAME	VARCHAR(28) Nullable	The qualified job name that initiated this vary operation. Contains the null value if the job name is not available.
IASP_NUMBER	IASPNUM	INTEGER	The number associated with the ASP device.

Example

Return the steps from available vary on operations, listed from most expensive to least expensive.

SELECT * FROM QSYS2.ASP_VARY_INFO 
  WHERE OPERATION_TYPE = 'VARY ON' 
  ORDER BY IASP_NAME, DURATION DESC;

Create a table to retain vary on historical data. Populate it with the current available values.
```
CREATE TABLE VARY_HISTORY AS 
      (SELECT * FROM QSYS2.ASP_VARY_INFO) WITH DATA;
```

Update the table that contains vary on historical data with any new rows.

MERGE INTO VARY_HISTORY H
      USING QSYS2.ASP_VARY_INFO N
      ON H.OPERATION_NUMBER = N.OPERATION_NUMBER
    WHEN NOT MATCHED THEN
      INSERT VALUES (N.IASP_NAME, N.OPERATION_NUMBER, N.OPERATION_TYPE,
                     N.OPERATION_STATE, N.STEP, N.STEP_STATE,
                     N.START_TIMESTAMP, N.END_TIMESTAMP, N.DURATION,
                     N.JOB_NAME, N.IASP_NUMBER);