DB2-Backup command

DB2 backup command is doing, piece by piece, in plain terms. 

Here’s what that DB2 backup command is doing, piece by piece, in plain terms.

Command

db2 backup database SALESDB \

to /db2backup/SALESDB/part1, \

   /db2backup/SALESDB/part2 \

compress \

buffer 1024 \

num_buffers 8 \

parallelism 4

1. backup database SALESDB

This tells DB2 to take a backup of the database named SALESDB.
By default, this is an online backup if the database is enabled for it; otherwise, it will be offline.

2. to /db2backup/SALESDB/part1, /db2backup/SALESDB/part2

These are the target directories where the backup image will be written.

• DB2 will stripe the backup across both locations.

• Striping improves performance and allows DB2 to use multiple disks in parallel.

• If one directory fills up, DB2 can continue writing to the other.

3. compress

Enables backup compression.

• Reduces backup size on disk.

• Uses extra CPU, but usually worth it for large databases.

• Very common for 1 TB–scale databases.

4. buffer 1024

Specifies the size of each backup buffer in 4 KB pages.

• 1024 pages × 4 KB = 4 MB per buffer

• Larger buffers generally improve backup performance, especially on fast storage.

5. num_buffers 8

Tells DB2 to allocate 8 backup buffers.

• Total memory used for buffers =
  8 × 4 MB = 32 MB

• More buffers allow DB2 to pipeline reads and writes more efficiently.

6. parallelism 4

Controls how many parallel I/O streams DB2 uses during the backup.

• DB2 will read and write data using 4 parallel threads.

• Works well with striped destinations and multiple disks.

• Helps reduce total backup time on multi-core systems.

In short

This command:

• Takes a backup of SALESDB

• Writes it in parallel to two directories

• Compresses the backup

• Uses tuned buffer settings for better performance

• Runs with multiple parallel threads to speed things up

If you want, I can also explain:

• How to size these values for a 1 TB database

• How this impacts restore performance

• Or how to convert this into an Azure-friendly backup strategy (Blob, NFS, or disk-based)

Explain for Export/Import Pipeline with compressed 

do
  echo $i
  mkfifo $i.fifo
  gzip -9 < $i.fifo > $i.gz &
  db2 "export to $i.fifo of ixf MESSAGES $i.EXPORT.msg select * from $i with ur"
  rm $i.fifo
  echo
  done

This shell snippet is exporting DB2 tables to compressed files using a FIFO, one table at a time. It streams the DB2 export directly into gzip instead of writing an uncompressed intermediate file.

Overall loop

The outer structure:

for i in SCHEMA.TABLE_NAME
do
  ...
done

for i in SCHEMA.TABLE_NAME is meant to loop over one or more fully-qualified tables (for example MYSCHEMA.TABLE1 MYSCHEMA.TABLE2). For each value, the body of the loop runs with i holding that table name.

Step‑by‑step inside the loop

1. Show the current table

echo $i

Prints the table name to the screen/log so you can see which table is being processed.

2. Create a FIFO (named pipe)

mkfifo $i.fifo

Creates a named pipe called SCHEMA.TABLE_NAME.fifo. A FIFO behaves like a file, but data is streamed through memory between a writer and a reader instead of being written to disk.

3. Start gzip in the background

gzip -9 < $i.fifo > $i.gz &

·       gzip -9 runs gzip with maximum compression.

·       It reads from the FIFO $i.fifo.

·       It writes compressed output to $i.gz (for example SCHEMA.TABLE_NAME.gz).

·       & puts this gzip process in the background so the script can continue.

At this point gzip is waiting to read data from the FIFO.

4. DB2 export writes into the FIFO

db2 "export to $i.fifo of ixf MESSAGES $i.EXPORT.msg select * from $i with ur"

·       Runs a DB2 command to export the table.

·       export to $i.fifo of ixf tells DB2 to write the export in IXF format into the FIFO, not a regular file.

·       MESSAGES $i.EXPORT.msg stores export messages and errors in a log file like SCHEMA.TABLE_NAME.EXPORT.msg.

·       select * from $i with ur selects all rows from that table using WITH UR (uncommitted read), which avoids locking conflicts but can see uncommitted data.

·       As DB2 writes rows into $i.fifo, gzip reads from the FIFO and compresses the data into $i.gz.

Because of the FIFO, there is never a large uncompressed .ixf file on disk; only the final .gz is stored.

5. Clean up the FIFO

rm $i.fifo
echo

Removes the named pipe after the export finishes and prints a blank line for readability.

Purpose in practical terms

·       Automate export of one or more DB2 tables to IXF format.

·       Stream the export directly into gzip to save disk space and I/O.

·       Use WITH UR to minimize locking impact on the source database.