0

On several occasions, I noticed that it takes a long time before anything "happens" during a redirected restore. There are no entries in db2diag.log and list utilities show detailshows:

Completed Work             = 67129344 bytes

I assume this is because the filesystems differ between source and target machine, and that something has to be checked/prepared, but it is just my guess. Does anyone know what is happening during the delay?

Example:

db2 "values current_timestamp"

1                         
--------------------------
2018-02-20-11.08.39.881924

  1 record(s) selected.

db2 list utilities show detail

ID                               = 19820
Type                             = RESTORE
Database Name                    = ****
Member Number                    = 0
Description                      = db 
Start Time                       = 2018-02-20 10.25.07.240025
State                            = Executing
Invocation Type                  = User
Progress Monitoring:
      Completed Work             = 67129344 bytes
      Start Time                 = 2018-02-20 10.25.07.240029
1

Between the RESTORE DATABASE ... REDIRECT and the RESTORE DATABASE ... CONTINUE command, you will execute all of the SET TABLESPACE CONTAINER commands.

If your database resides on a filesystem that does not support fast file allocation (for example: AIX JFS2), then these SET TABLESPACE CONTAINER operations can take quite a long time if you have large containers – DB2 is formatting the tablespace containers.

When this is happening you'll see lots of I/O on your system, but no apparent activity on the database restore.

  • Thanx, makes sense. I'll check I/O activity next time. – Lennart Feb 21 '18 at 0:55
  • The fs is XFS, haven't checked whether that supports the property you describe. FWIW the question is mostly out of curiosity since performance is not critical, the system of concern is used for exploratory testing with production-like data. – Lennart Feb 21 '18 at 9:33
  • I can confirm that there is indeed a lot of disk activity during the wait time. vmstats shows roughly 300000 blocks/s. The blocksize is 512 bytes meaning that about 150Mb / s is written to disk. – Lennart Feb 22 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.