Restoring SQL materialized query tables

SQL materialized query tables (MQTs) are physical files, like logical files, that have dependencies on other files. You can defer the restore of logical files and MQTs whose based-on files or members are missing.

A deferred MQT is created, so that its data spaces can be restored from the media on the first pass. A deferred MQT is created, but until the restore operation is complete, you cannot perform functions on the MQT that require access to the based-on files.

In this example, you would use the Restore Deferred Object (RSTDFROBJ) command to complete the restore of a deferred MQT. The logical files and MQT, which have multiple based-on files, are created using database commands. Then libraries A and B are saved using the Save Library (SAVLIB) command, deleted, and restored using the Restore Library (RSTLIB) command. The logical files and MQT being restored are deferred, because their based-on files do not exist and because a defer ID is specified on the RSTLIB command. An implicit restore operation occurs only if you save and restore the same files using the SAVLIB and RSTLIB commands. Otherwise, you must use the RSTDFROBJ command to complete the restore operation for the deferred files.

CRTLIB A 
CRTLIB B
CREATE TABLE B/T1 (C1 INT)
CREATE TABLE B/T2 (C1 INT)
CREATE VIEW A/M1 
	AS (SELECT * FROM B/TI UNION SELECT * FROM B/T2)
	DATA INITIALLY DEFERRED
	REFRESH DEFERRED
	ENABLE QUERY OPTIMIZATION
	MAINTAINED BY USER
SAVLIB LIB(A B) DEV(TAP01)
DLTLIB LIB(A)
DLTLIB LIB(B)
RSTLIB SAVLIB(A B) RSTLIB(*SAVLIB) DFRID(FLOW4) DEV(TAP01)

If the completion message indicates that not all of the deferred files were restored, issue the RSTDFROBJ DFRID(FLOW4) command to complete the restore operation for the deferred files.

The Remove Defer ID (RMVDFRID) command removes information about objects that were deferred during the restore operation. Use this command to remove the deferral information for the MQT if, for example, the deferred MQT is deleted and recreated with new based-on files.

RMVDFRID DFRID(FLOW4)