IBM DB2 (Universal Database)

The UPSTREAM UDB agent is a full featured DB2 backup facility:

• 120_List1_Bullet_1055999

  • Supports full backups as a “vendor DLL” or shared library.
  

• 120_List1_Bullet_1056000

  • Supports 31-bit and 64-bit on Windows, HPUX, AIX, Solaris, and Linux OS on IBMZ.
  

• 120_List1_Bullet_1056001

  • Both online and offline backups
  

• 120_List1_Bullet_1056002

  • Tablespace level backups and restores.
  

• 120_List1_Bullet_1056003

  • The database can be recreated directly from UPSTREAM backups. You can also rename the database or restore it to a new instance.
  

• 120_List1_Bullet_1056004

  • Multiple, simultaneous backups and restores of separate databases.
  

• 120_List1_Bullet_1056005

  • Multi-session backups and restores of a given database.
  

• 120_List1_Bullet_1056710

  • Includes a user exit (pre DB2 9.5) or a log backup method (9.5 or above) for system generated incremental backups if you are running in archive log mode.
  

• 120_List1_Bullet_1056711

  • The user exit or log backup method supports roll-forwards from user exit or log backup method backups.
  

• 120_List1_Bullet_1056712

  • Partitioned database support.
  

System requirements are IBM Universal Database v7 or later.

UPSTREAM DB2 Interface

DB2 Administration Quick Start#

This section is intended to give a quick set of instructions for getting the DB2 agent working for the database administrator. The following section gives a set of steps that are complete and thorough. If at any time you are confused, we strongly recommend that you consult that section.

o Consult with your Storage Server administrator (UPSTREAM Storage Server or UPSTREAM Reservoir admin.

• 120_List2_Bullet_1056024

  – Should backups go to disk or tape?
  

• 120_List2_Bullet_1056025

  – You need to assign an 8 character name for the database (what we call a backup profile). You can use the database name or any other name that matches z⁄OS naming conventions. We assume that you use the database name as the backup profile. You need to give this name to the Storage Server person so that they can create a backup profile. If the name needs to be different, you need to create a profile mapping using usudb.prf. See “Setting Up a Backup Profile Mapping File” in Section for details on how to do this.
  

• 120_List2_Bullet_1056026

  – Since the Storage Server usually is scheduling the backups, you need to write a script to do the backups; so you need to test the script and give the fully qualified path name of the script to the mainframe person. Something like this for UNIX:
  

/bin/su - <db2 instance owner name> -c <fully qualified path to script>

• 120_List2_Bullet_1117764

  – Or for a Windows PC:
  

c:\<path to sqllib>\db2cmd.exe -c -w -i -t <fully qualified name to the .bat script file>

This needs to be tested running as the UPSTREAM user (see “Installing UPSTREAM” in Section for a description of the UPSTREAM user). Operating system commands with spaces in them need to be surrounded in double quotes. However, DB2 commands with spaces in them need to be surrounded in single quotes. For example (as one line):

“c:\program files\IBM\sqllib\bin\db2cmd” -c -w -i -t db2 backup database sample load 'c:\Program Files\Innovation Data Processing\UPSTREAM\usudb.dll' options 'c:\Program Files\Innovation Data Processing\UPSTREAM\' without prompting

If the job is to be run as an UPSTREAM “Run Job” (ACTION=5), in the FILES statement (or the “Run Command if run from the UPSTREAM ZDIRECTOR) you need to precede the first double quote with a single back quote (`). This is used as an indicator to UPSTREAM to not strip the double quotes from the command. See Chapter 17 “Advanced UPSTREAM” for more details on Running Jobs. For example:

`”c:\program files\IBM\sqllib\bin\db2cmd” -c -w -i -t db2 backup database sample load 'c:\Program Files\Innovation Data Processing\UPSTREAM\usudb.dll' options 'c:\Program Files\Innovation Data Processing\UPSTREAM\' without prompting

o Work with the SA (the one with system root or Administrative permissions):

• 120_List2_Bullet_1063446

  – Set security on the UPSTREAM directory so that both the UPSTREAM user and the DB2 user can write to the directory (in UNIX make the DB2 instance owner’s group the directory’s group and set the permissions to the directory to 775).
  

• 120_List2_Bullet_1137050

  – Set security on upstream.cfg so that the DB2 database owner has read/write access (in UNIX 664 is nice)
  

• 120_List2_Bullet_1063449

  – It is convenient to work with the database SA in creating the template parameter file (usudb.dat). This is a required file. You may want to contact BMC Support as well. Typically, you use the Director or “us” program to do this. The program has to be accessible to the database instance owner. You want to set the parameters:
  

  • 120_List3_Bullet_1056048

    • Tape or Disk (storage type)
    

  • 120_List3_Bullet_1056049

    • Compression (run with at least fast compression)
    

• 120_List2_Bullet_1117804

  – UPSTREAM SOS or SAN Express options (if any)
  

  • 120_List3_Bullet_1063509

    • Modify security (USERID and PASSWORD if enabled). Specify a USERID and PASSWORD of “&JOB” in the template parameter file and in your Storage Server batch job; this cause’s UPSTREAM to use the security prevalidated in the backup job.
    

If you do need to do a locally initiated function or the user exit is used you must modify the template parameter file to specify a Storage Server USERID and PASSWORD.

• 120_List2_Bullet_1056054

  – Create your script. We strongly recommend that you consult with the DB2 documentation in setting up the BACKUP DATABASE command. The script is simply that command. See Section “Automation” for help in creating your script.
  

• 120_List2_Bullet_1056055

  – If you wish to backup transaction logs, see the description below.
  

Installation and Configuration#

There are two programs in the UPSTREAM UDB agent: usudb.dll (for Windows, and usudb64.dll for 64-bit Windows; usudb for 31-bit UNIX systems and usudb64 for 64-bit UNIX systems) for full backups and restores, and for pre-DB2 v9.5 systems, a user exit for archiving and recovering logs (db2uext2.exe for Windows and db2uext2 for UNIX). usudb can be used as a log backup method processing in DB2 v9.5 and above systems. usudb can reside in the UPSTREAM directory, however, the user exit program (db2uext2) must exist in a specific directory.

Note that once the user exit is copied in, and ANY database is configured for a user exit, it is called and expected to generate backups and restores (archives and retrieves). Thus it should not be copied in until database is configured and you have performed a test backup and restore with usudb.

There are several aspects to configuration:

• 120_List1_Bullet_1057090

  • Installing, configuring, and testing UPSTREAM. (See Installing UPSTREAM.)
  

• 120_List1_Bullet_1057091

  • Setting up the environment. (See Setting Up the Environment.)
  

• 120_List1_Bullet_1056074

  • Setting up a template UPSTREAM parameter file. (See Setting up a Template Parameter File.)
  

• 120_List1_Bullet_1056075

  • (Optional) Setting up a backup profile mapping file. (See Setting Up a Backup Profile Mapping File.)
  

• 120_List1_Bullet_1056076

  • Testing backups. (See Testing Backups.)
  

• 120_List1_Bullet_1059533

  • (Optional) Configuring DB2 for the user exit or log backup method. (See Archive Log Maintenance.)
  

Installing UPSTREAM

You should install, configure, and test UPSTREAM for locally initiated, standard file backups. If you intend to initiate UPSTREAM backups and/or restores from the Storage Server you should also test Storage Server initiates.

For ease of configuration, we strongly recommend that you install UPSTREAM in the default directory.

UPSTREAM must be running as a service (Windows) or daemon (UNIX) for the DB2 agent to complete its backups. You do not need a separate installation of UPSTREAM for the DB2 agent.

We strongly recommend that you specify the directories for the UPSTREAM message file and the UPSTREAM log file in the advanced options of the UPSTREAM Configurator.

In UNIX, the DB2 agent runs as the DB2 instance owner and needs to access the UPSTREAM configuration file, upstream.cfg. We recommend that you either allow access to the default configuration file, or copy the configuration file to a DB2 agent configuration file, usudb.cfg, and allow it access by the instance owner. Note that whenever you save to the default configuration file using the Configurator, the file is rewritten, so we recommend that you copy the configuration to usudb.cfg and then allow access for the DB2 instance owner. In UNIX, the easiest way to do this is type:

Setting Up the Environment#

The UPSTREAM DB2 library needs to find the UPSTREAM directory. UPSTREAM does this by looking for the usudb.dat file (see Setting up a Template Parameter File) in a number of places. If the DB2 OPTIONS parameter is set, it looks in that directory first. Then it assumes that UPSTREAM was installed in the default directory for that operating system. Finally it looks in the USPATH file if it exists (see below).

Since DB2 starts when the system starts it can be somewhat difficult to set environment variables, particularly in UNIX. In Windows, environment variables must be set from the System applet in the Control Panel. For UNIX we recommend that you set DB2 agent values with files in the DB2 instance owner home directory.

The following is a list of the environment variables for the UPSTREAM DB2 agent. Note that most users will not need to specify any of these variables.

USUDBDIR

The directory where the agent finds the UPSTREAM programs. If this variable is not set, it looks for the UPSTREAMPATH environment variable, the UPSTREAM default directory for the given operating system type, the HOME environment variable, and then the DB2 current directory. If a USPATH file is found throughout this search it is used. Starting in DB2 9.5, in Windows, the USPATH file must be placed in the directory:

{{id name="120_JCL_1119493"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1119493"/}}{{id name="1119493"/}}

{{id name="IBMDB2(UniversalDatabase)-1119493"/}}

c:\Documents and Settings\All Users\Application Data\IBM\DB2\<database>\DB2\\Default:

• 120_List2_Bullet_1117879

  – c:\Program Files\Innovation Data Processing\UPSTREAM (Windows)
  

• 120_List2_Bullet_1117880

  – /usr/lpp/fdrupstream (AIX)
  

• 120_List2_Bullet_1063871

  – /opt/fdrupstream (all other UNIX systems)
  

USUDBLOG

The fully qualified file name where the DB2 agent log is written. By default the DB2 agent log is written to the UPSTREAM directory. You can create a USUDBLOG file as well as the environment variable.

Default: usudb.log in the UPSTREAM directory.

USUDBTRACE

Should only be defined on the request of BMC Support. Trace information is written to the USUDB.LOG file and can also be activated by the existence of a usudbtrc file in the UPSTREAM directory. If there is a ustrc file in the UPSTREAM directory, UPSTREAM tracing is enabled for this request.

USWAITDELAY

The number of seconds for a number of agent functions where it must wait. Generally only needs to be set if tape mounts may exceed 2 hours.

Default: 7200.

Since the UPSTREAM DB2 agent runs as a shared library or DLL, it runs under the account of DB2 itself. If you wish to test the UPSTREAM DB2 and cannot reboot your machine, you can create a file, USPATH (all upper case, no extension), which simply holds the fully qualified path to the UPSTREAM directory. For UNIX, this file must be in the $HOME directory of the user that DB2 logs into; for PCs it is the default system directory (for Windows, it is usually:

{{id name="120_JCL_1119586"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1119586"/}}{{id name="1119586"/}}

{{id name="IBMDB2(UniversalDatabase)-1119586"/}}

c:\Documents and Settings\All Users\Application Data\IBM\DB2\<database>\DB2)\\(UNIX) For example, if you installed UPSTREAM in the default directory for AIX: /usr/lpp/fdrupstream and the home directory for DB2 is /db2/home, then your USPATH file would be a single line in the /db2/home directory:

{{id name="120_JCL_1056151"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056151"/}}{{id name="1056151"/}}

{{id name="IBMDB2(UniversalDatabase)-1056151"/}}

/usr/lpp/fdrupstream\\(Windows) For example, if you installed UPSTREAM in the d:\upstream directory, USPATH would be a single line in the directory:

{{id name="120_JCL_1119735"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1119735"/}}{{id name="1119735"/}}

{{id name="IBMDB2(UniversalDatabase)-1119735"/}}

c:\Documents and Settings\All Users\Application Data\IBM\DB2\<database>\DB2\\{{id name="120_JCL_1117924"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1117924"/}}{{id name="1117924"/}}

{{id name="IBMDB2(UniversalDatabase)-1117924"/}}

D:\upstream\\

(UNIX) To set environment variables that can be seen by UPSTREAM when loaded into DB2, you must:

• 120_List1_Bullet_1056166

  • Set the environment variable. We recommend that you add it to the $INSTHOME/sqllib/userprofile. For example, if you wanted to set USWAITDELAY to 20 minutes, add to userprofile:
  

• 120_List1_Bullet_1056170

  • Login as the database instance owner. Verify that no one is using the database, then stop it:
  

• 120_List1_Bullet_1056173

  • Set the environment variable using the db2set program:
  

• 120_List1_Bullet_1056176

  • Restart DB2
  

{{id name="120_JCL_1056178"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056178"/}}{{id name="1056178"/}}

{{id name="IBMDB2(UniversalDatabase)-1056178"/}}

db2start\\To make the change permanent, add the db2set line above into your script that starts DB2.

Setting up a Template Parameter File#

Required. Run UPSTREAM (./us or UPSTREAM Local) or the Director, go into the backup screen and set up the parameters that you wish to specify for your DB2 backups. These can include virtually every UPSTREAM function, but we highly recommend that you carefully consider your choices for the following:

• 120_List1_Bullet_1060617

  • Tape or Disk (storage type)
  

• 120_List1_Bullet_1060618

  • Compression
  

  • 120_List2_Bullet_1117948

    – FDRSOS Local Backups
    

• 120_List1_Bullet_1056188

  • Security (USERID and PASSWORD if enabled). If you are running UPSTREAM v3.3.0 or higher on both sides, and you are starting the backup with CONV=WAIT batch jobs, you can specify a USERID and PASSWORD of “&JOB” in the template parameter file and in your Storage Server batch job. This causes UPSTREAM to use the security pre-validated in the backup job. This is true for the UPSTREAM Reservoir as well as UPSTREAM Storage Server.
  

If you do need to do a locally initiated function or the user exit is used you must modify the template parameter file to specify a Storage Server USERID and PASSWORD.

Parameters that are not used include:

• 120_List1_Bullet_1056193

  • Backup Profile (configured below).
  

• 120_List1_Bullet_1056194

  • Full or incremental (backup type).
  

When you have completed your configuration save it to the parameter file usudb.dat in the UPSTREAM directory.

By default, transaction log backups use the same parameters as specified in usudb.dat. However, if you wish to have transaction log backups use different UPSTREAM parameters (storage type, compression, etc.), create a separate parameter file for incremental backups and name it usudbi.dat.

If you wish, you can copy the usorasbt.dat.sample to usudb.dat or usudbi.dat and manually modify the parameters rather than run a user interface.

Setting Up a Backup Profile Mapping File#

(Optional) By default, UPSTREAM uses the name of your database as the backup profile name. This may work for many users. However, if you wish to have the same database on more than one machine you need to use a backup profile mapping file or use the backup profile OPTIONS override (see The OPTIONS Clause below)

This file is a simple text file usudb.prf (in the UPSTREAM directory) that is of the format:

{{id name="120_JCL_1093457"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1093457"/}}{{id name="1093457"/}}

{{id name="IBMDB2(UniversalDatabase)-1093457"/}}

[(<instance name>|*).]<database name> <profile or profile prefix>\\You must use a text editor that adds no formatting such as the Windows notepad. For example, to map the database SAMPLE in the instance DB2 to backup profile PC1SMPL and IMAGES to PC1IMGS, you would have a usudb.prf that looks like:

{{id name="120_JCL_1056212"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056212"/}}{{id name="1056212"/}}

{{id name="IBMDB2(UniversalDatabase)-1056212"/}}

DB2.SAMPLE PC1SMPL
DB2.IMAGES PC1IMGS\\Note that the instance name is optional if you have a single instance.

If you wish to have a separate backup profile for transaction logs, you can create a transaction log alias file usudbi.prf, created in the same form as above. If it does not exist, it uses usudb.dat and if that does not exist, it uses the database name.

If you are going to run multiple sessions, you can create an template parameter file for each session. See “Multi-Session Backups and Restores” in Section for details.

Testing Backups

For PC operating systems, you can test backups from the DB2 Control Center. For UNIX operating systems you must use the DB2 command line (see below).

With the Control Center running, expand so that you can see the database you wish to test with, highlight it, pull down the Selected menu, select Backup.

Backup Wizard - Confirm the Details of Your Database

For a simple test, select Backup entire database and press the Next> button.

Backup Wizard - Specify Where to Store Your Backup Image

Media Type

Select Vendor DLL

Sessions

For your first test select 1. Once you have a single session working, you can increase this value if you wish. See section 27.1.2 below for details.

File

Enter the fully qualified file and path name to the UPSTREAM DB2 backup agent. If there are spaces in the name, you must surround the name in single quotes. For example, for Windows users running 32-bit DB2, most users type:

{{id name="120_JCL_1117998"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1117998"/}}{{id name="1117998"/}}

{{id name="IBMDB2(UniversalDatabase)-1117998"/}}

'C:\Program Files\Innovation Data Processing\UPSTREAM\USUDB.DLL'\\or for 64-bit DB2 most users type:

{{id name="120_JCL_1118000"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118000"/}}{{id name="1118000"/}}

{{id name="IBMDB2(UniversalDatabase)-1118000"/}}

'C:\Program Files\Innovation Data Processing\UPSTREAM\USUDB64.DLL'\\Press the Next button:

Backup Wizard - Choose Your Backup Options

Backup Type

Most users will leave the default of Full Backup.

Availability

The ability to do an Online backup depends on whether transaction logging is enabled. If transaction logging is not enabled you will only have the choice of Offline backups.

Quiesce Database

The value of this is not important to UPSTREAM, though a quiesced database has more integrity and is more likely to restore.

Throttle

This should only be checked if you find UPSTREAM's functions keeping the database from functioning well during a backup.

Compression

We recommend that you use UPSTREAM compression so that this option not be checked.

The remaining screens are for performance and scheduling options. To begin your backup, press the Finish> button. This will display the small busy dialog with the spinning wheels. This dialog should clear with an error dialog or a completed dialog.

If you wish you can perform the test backup from the DB2 command line. To start the DB2 command line from PC operating systems, you may need to run the db2cmd program (installed in the \sqllib\bin directory) and then run db2. To start the command line from UNIX systems, youYou may be able to run db2 directly, or may have to run CmdLine that sets up environment variables and then executes db2. See Section “Automation” for operating system specific details on running DB2 scripts. The format is (all on one line):

{{id name="120_JCL_1056257"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056257"/}}{{id name="1056257"/}}

{{id name="IBMDB2(UniversalDatabase)-1056257"/}}

DB2 BACKUP DATABASE <database name> LOAD <UPSTREAM library> OPTIONS <UPSTREAM Directory>[;BKPROFILE=<profile override>]\\Note that the <UPSTREAM library> must be a fully qualified name to usudb or usudb64. If the library name has spaces in it, it must be surrounded by single quotes. Note that you specify the UPSTREAM library named usudb.dll for 31-bit DB2 (even if it is on a 64-bit machine), and usudb64.dll for 64-bit DB2.

For example, to backup the database SAMPLE from a Windows machine with UPSTREAM in the C:\Program Files\Innovation Data Processing\UPSTREAM /opt/fdrupstream directory on a 64-bit version of DB2, type (on a single line):

{{id name="120_JCL_1056267"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056267"/}}{{id name="1056267"/}}

{{id name="IBMDB2(UniversalDatabase)-1056267"/}}

DB2 BACKUP DATABASE SAMPLE LOAD ’’ 'c:\Program Files\Innovation Data Processing\UPSTREAM\usudb64.dll' OPTIONS 'c:\Program Files\Innovation Data Processing\UPSTREAM'\\The usudb library is executed by DB2 and UPSTREAM started. Since these programs run as services, no screen will be displayed. The success or failure of the backup is logged in the DB2 Journal by DB2 and details can be found in the usudb.log and the upstream.log files. If you have problems, you need to examine these two logs and any messages logged in the DB2 Journals or displayed on the screen.

The OPTIONS Clause#

If you wish, you can use the OPTIONS clause in the DB2 commands (or LOGARCHOPT1 for archive logs described below) to do several things at backup time:

• 120_List1_Bullet_1099689

  • Specify the directory for the UPSTREAM binaries. This is necessary if you do not use the default UPSTREAM directory or wish to specify a backup profile override.
  

• 120_List1_Bullet_1099907

  • Specify a backup profile override if you do not wish to use the Backup Profile mapping file (see above).
  

OPTIONS values are separated by semicolons. The format for the OPTIONS clause for backups is (as described above):

{{id name="120_JCL_1100089"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1100089"/}}{{id name="1100089"/}}

{{id name="IBMDB2(UniversalDatabase)-1100089"/}}

DB2 BACKUP DATABASE <database name> LOAD <UPSTREAM library> OPTIONS <UPSTREAM Directory>[;BKPROFILE=<profile override>]\\So if you wished to back up the database SAMPLE and use the default Linux directory but a profile override of SMPL you could specify (all on one line):

{{id name="120_JCL_1100245"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1100245"/}}{{id name="1100245"/}}

{{id name="IBMDB2(UniversalDatabase)-1100245"/}}

DB2 BACKUP DATABASE SAMPLE LOAD /opt/fdrupstream/usudb64 OPTIONS /opt/fdrupstream;BKPROFILE=SMPL\\There are also OPTIONS which are very helpful in restores. When UPSTREAM does a backup, it uses a 3 part identifier in the file name made up of the database instance id, the instance name and the database name. For example, the file name would contain:

{{id name="120_JCL_1100530"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1100530"/}}{{id name="1100530"/}}

{{id name="IBMDB2(UniversalDatabase)-1100530"/}}

SQL9050.db2inst1.MYDB.\\When the restore is performed to a host with a different instance id, or another instance on the same host, db2 may error with:

{{id name="120_JCL_1100841"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1100841"/}}{{id name="1100841"/}}

{{id name="IBMDB2(UniversalDatabase)-1100841"/}}

SQL2542N No match for a database image file was found based on the source database alias "PAYROLL" and timestamp "20120811011201" provided.\\In order to perform the redirected restore correctly, specify the three-part identifier of the backup as it was originally taken in the OPTIONS to the agent using the following keywords (separated with semicolons):

• 120_List1_Bullet_1100999

  • DBID=<the first component, the DBID>
  

• 120_List1_Bullet_1101088

  • DBINSTANCE=<the second component, the DBINSTANCE>
  

• 120_List1_Bullet_1101194

  • DBNAME=<the third component the DBNAME>
  

For instance, if we were trying to restore the PAYROLL database to a second instance on the same host to the database PAYROLL, we would specify:

{{id name="120_JCL_1100450"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1100450"/}}{{id name="1100450"/}}

{{id name="IBMDB2(UniversalDatabase)-1100450"/}}

db2 restore db PAYROLL load /opt/fdrupstream/usudb64 options “/opt/fdrupstream;DBID=SQL9050;DBINSTANCE=db2inst1;DBNAME=PAYROLL” taken at 20120811011201 redirect\\Also for database restore there is the OPTIONS parameter:

• 120_List1_Bullet_1107635

  • FORCEOK - Specify this OPTIONS parameter with no value; it takes affect by simply being specified. This should only be used when restoring a small amount from a large database, such as restoring a single tablespace, or generating a script; but you use it to dramatically reduce processing time. Note that the use of this option will cause an error in both the client and storage server log as the restore completes before all of the data is transmitted.
  

For instance, if we were trying to generate a script from the database sample, and did not want to wait for the entire database to be transmitted, we might specify:

Archive Log Maintenance#

DB2 has the capability of using archive logs. Archive logs are general recommended as they allow online database backups to be taken, reduce the number of full database backups that need to be taken (as archive logs are effectively incremental backups of the full database) and allow UPSTREAM to be the central repository for all database data.

UPSTREAM supports the pre-9.5 method of a User Exit program and the 9.5 and above method of using the shared library to perform archive log backup and retrieval. If you use the shared library, DB2 controls when to delete an archive log, so you must use DB2 commands to control archive log retention; if you use the user exit program, your backup profile controls archive log retention.

We strongly recommend to customers who have moved to DB2 v9.5 or above to migrate to the shared library method for log file management and use DB2 to control retention.

Pre 9.5 User Exit Installation and Configuration.

The user exit is a separate program that must be installed in a specific directory. For PCs copy the db2uext2.exe to the \sqllib\bin directory (usually c:\Program Files\IBM\BIN). For UNIX create a symbolic link name db2uext2 in the $INSTHOME/sqllib/bin directory to $UPSTREAMPATH/db2uext2; for example (as root, and if the environment variables are not set in the shell you are in, you must fully qualify the names):

{{id name="120_JCL_1056284"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056284"/}}{{id name="1056284"/}}

{{id name="IBMDB2(UniversalDatabase)-1056284"/}}

ln -s $UPSTREAMPATH/db2uext2 $INSTHOME/sqllib/bin/db2uext2\\You should test the user exit by logging in as the DB2 instance owner, cd to the $INSTHOME/sqllib/bin directory and run the ./db2uext2 program. The exit should complain about an “invalid number of parameters” and log the same message to the $UPSTREAMPATH/usudb.log file. If it does not run, recreate your symbolic link. If it runs but does not add to the log file, verify that the DB2 instance owner has rights to write to the UPSTREAM directory and the usudb.log file.

To have the system enabled for user exits, you can modify the database configuration in the Control Center (for Windows) or on the DB2 command line (all systems).

If you wish to use the Control Center to modify the configuration, highlight the database you wish to enable user exits for, pull down the Selected menu and select Configure. Press the Logs tab, scroll down the list and highlight Invoke user exit for log file archiving and press the OK button. You are notified that all applications must disconnect before the changes will be in affect.

If you wish to use the DB2 command line (see above for instructions on starting the DB2 command line) type (as the DB2 administrator):

{{id name="120_JCL_1058074"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1058074"/}}{{id name="1058074"/}}

{{id name="IBMDB2(UniversalDatabase)-1058074"/}}

db2 update database configuration for <database> using userexit on\\Once all users have disconnected, the user exit is enabled.

DB2 9.5 and Above Shared Library Log File Management.

Since DB2 uses the same shared library for archiving and retrieving logs that it uses for database backup and restore, by testing backup and restore you have already done the majority of the work. All that is left is to activate the facility. This is done with a single command:

{{id name="120_JCL_1118068"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118068"/}}{{id name="1118068"/}}

{{id name="IBMDB2(UniversalDatabase)-1118068"/}}

db2 update db cfg for sample using LOGARCHMETH1 “VENDOR:c:\program files\innovation data processing\upstream\usudb64.dll” LOGARCHOPT1 “c:\program files\innovation data processing\upstream”\\This sets your transaction log type to archived. If you had been using circular logging previously, as soon as this command is active, you are using archived logging.

Typically you need to disconnect all applications from the database and restart it before the command is activated. However, as soon as it is active, backups can happen at any time. Thus we recommend that you setup all log file backups to disk. To have your log file backups be different than your regular backups (disk vs. tape, or a different compression level), create a template parameter file usudbi.dat, which is used only for log file backups and restores. Note that these backups are always tagged by UPSTREAM as incremental backups.

The profile name is either specified in the database configuration (as above with the optional parameter BKPROFILE), in usudb.prf to use the same backup profile as the full. Using usudbi.prf or BKPROFILE above when you don’t do it in the full to specify a different profile for the incremental than the full is generally not recommended.

Troubleshooting

When there is a problem with the UPSTREAM agent, DB2 simply reports a non-zero return code (usually 11) and there is a SQL message written to the output (STDOUT). If the message text provides no guidance to solving the problem, you must look to the UPSTREAM logs. There may be multiple copies of these logs, so you should look for the one with the modification date/time closest to your run.

For UNIX you should look in the UPSTREAM directory, the DB2 user’s home directory, and the root. For Windows you should look in the UPSTREAM directory, \SQLLIB\BIN directory or the directory:

• 120_List1_Bullet_1056322

  • usudb.log. This is the log for the UPSTREAM DB2 agent messages. It logs messages related to data access and UPSTREAM setup.
  

• 120_List1_Bullet_1056323

  • usudberr.log. When UPSTREAM cannot write to the usudb.log file, it writes to this file. It tends to contain messages related to access to the UPSTREAM control files. It is more likely to be in a directory other than the UPSTREAM directory.
  

• 120_List1_Bullet_1056324

  • upstream.log. Any messages related to communications with the Storage Server.
  

During setup, the most common problem is having the usudb library find UPSTREAM. In UNIX, you almost always need to have a USPATH file. Note that the usudb library fails to find UPSTREAM if there is no usudb.dat file.

If you still cannot find a usudb.log file and are getting errors in the backup request, we recommend that you create a USUDBLOG file with the fully qualified name of a file that is accessible by all users. Place this file in the root (UNIX) or system directory (C:\WINNT\system32 for Windows), the DB2 instance home directory, and the UPSTREAM directory. The usudb.log file should then be created with sufficient information to help you resolve the problem.

The DB2 agent needs to contact the UPSTREAM service or daemon. If the UPSTREAM’s currently not running you see “Connection refused” messages in the usudb.log.

(AIX.) After upgrading the DB2 agent (usudb), you may need to run the system utility slibclean to have the operating system refresh its cache of shared libraries.

(UPSTREAM SOS Restores) UPSTREAM allocates a data area on the local backup disk using the smaller of the mainframe estimate of the size of the restore or the MAXBACKUPSIZE defined for the backup profile on the local backup disk in the same way as for backups. If this estimate is too small (and it will be for a number of UPSTREAM database agent restores including DB2 and Oracle), there will be excessive wraps through this area and thus a reduction in performance. You can use the DASDOVERRIDE in usudb.dat to increase the size of the transfer area and thus reduce the number of wraps. As for backups, the upstream.log file logs the space allocated for the local backup and the number of wraps that were used. Note that changing DASDOVERRIDE causes the DB2 size estimate for backups to be ignored; thus you may choose to have a separate usudb.dat file for restores only.

Multi-Session Backups and Restores#

To use the multi-session support for your backups or restores, UPSTREAM uses a backup profile of the database name or alias name (if specified in usudb.prf) for the first session. For subsequent sessions, the numbers from 1-9 are appended, and then A-Z. Thus up to 36 sessions are supported. To use this facility, you must not specify more than 7 characters for your backup profile name to allow this extra character to be added and you may need to configure each of the backup profiles in the Storage Server. Single session backups or restores can use the full 8 characters for the backup profile name.

Within your jobs, you specify the backup with the extra qualifier open xx sessions. For example, to perform a backup with three sessions, type (on a single line):

By default, UPSTREAM uses usudb.dat (usudbi.dat if you have it defined for your incremental backups) as template parameter files. For multi-sessions you can optionally have a template parameter file for each sequence number that UPSTREAM generates. These parameter files use a single character as part of the file name in the same way that backup profiles work (described previously). Thus, use usudb.dat for the first session, usudb.1.dat for the second session, usudb.2.dat for the third session,…,usudb.a.dat for the eleventh session, usudb.b.dat for the twelfth session, etc.

User exits or log archive methods use a single session and always backup using the backup profile without the additional character.

DB2 Partitioned Database Support

To support partitioned databases, UPSTREAM stores the node number in the backup profile in the same way that it stores the sequence number for multiple session backups. By default, this facility is disabled so as not to affect non-partitioned database backups.

To enable the facility, you must create a file usudb.addnodes in the UPSTREAM directory. In this file place on a line by itself, all the databases you wish to add node number to the backup profiles. You can use a single asterisk (*) to denote ALL databases. For example, to enable the facility for all databases, create usudb.addnodes to hold a single line:

{{id name="120_JCL_1118130"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118130"/}}{{id name="1118130"/}}

{{id name="IBMDB2(UniversalDatabase)-1118130"/}}

*\\If you have a usudb.prf alias file, aliases are used to create the backup profile name. Then if node names are supported, the node number is added. Finally, if this is a multi-session backup, the session number is added. Node numbers, like session numbers, use numbers for the values 0-9 and then use letters for values 10-35. For example, if this is node 2, session 3 for database TEMP (no alias), the resulting backup profile would be TEMP23.

Note that this means that if you are using both node and session support, the maximum backup profile name is 6 characters.

To use the support use the db2_all command prefixing the db2 backup database command. See the DB2 documentation for a description of the use of the db2_all command in a partitioned database environment.

Automation#

To automate UPSTREAM backup and restore functions use the DB2 command line facility.

For Windows environments, use the DB2CMD program (usually in c:\program files\ibm\sqllib\bin) which will handle environment settings. For recent versions of DB2, you will need to run DB2CMD with some optional switches: -c (to close the window when complete), -w (to wait for it to complete), -i (run within the same console) and -t (run with the parent title). For example, command line (FILES specification in an ACTION 5) to backup the SAMPLE database might contain the single line (note the leading back-quote):

{{id name="120_JCL_1108691"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1108691"/}}{{id name="1108691"/}}

{{id name="IBMDB2(UniversalDatabase)-1108691"/}}

‘”C:\Program Files\IBM\SQLLIB\BIN\DB2CMD.EXE -c -w -i -t DB2 BACKUP DATABASE SAMPLE ONLINE LOAD 'C:\Program Files\Innovation Data Processing\UPSTREAM\USUDB64.DLL' WITHOUT PROMPTING”\\To do the same function in the Director (or the Dispatcher), you need to change the command line slightly (again note the leading back-quote).

{{id name="120_JCL_1118141"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118141"/}}{{id name="1118141"/}}

{{id name="IBMDB2(UniversalDatabase)-1118141"/}}

‘C:\Program Files\IBM\SQLLIB\BIN\DB2CMD.EXE -c -w -i -t DB2 BACKUP DATABASE SAMPLE ONLINE LOAD 'C:\Program Files\Innovation Data Processing\UPSTREAM\USUDB64.DLL' WITHOUT PROMPTING\\If you wished to backup only the SALES tablespace, your command line might be (all on one line, again note the leading back-quote):

{{id name="120_JCL_1118143"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118143"/}}{{id name="1118143"/}}

{{id name="IBMDB2(UniversalDatabase)-1118143"/}}

‘”C:\Program Files\IBM\SQLLIB\BIN\DB2CMD.EXE -c -w -i -t DB2 BACKUP DATABASE SAMPLE TABLESPACE SALES ONLINE LOAD 'C:\Program Files\Innovation Data Processing\UPSTREAM\USUDB64.DLL' WITHOUT PROMPTING”\\For UNIX environments, a number of environment variables must be set. Usually, the instance owner has them set correctly and has permission to run the DB2 functions. So we strongly recommend that you run your script as the instance owner.

Thus, for a simple backup script like this (named db2back):

{{id name="120_JCL_1056402"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056402"/}}{{id name="1056402"/}}

{{id name="IBMDB2(UniversalDatabase)-1056402"/}}

#!/bin/ksh
db2 BACKUP DATABASE SAMPLE LOAD /usr/lpp/fdrupstream/usudb64 WITHOUT PROMPTING\\For automation purposes, if you Storage Server initiate these backups, you need to use the Run a PC Job function of USTBATCH. We recommend that you specify on the command line of the job to execute, the su command rather than running it directly. For example, if your script is /usr/lpp/fdrupstream/db2back, and your DB2 instance user is db2inst1 you could setup your Storage Server job FILES parameter:

{{id name="120_JCL_1056407"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056407"/}}{{id name="1056407"/}}

{{id name="IBMDB2(UniversalDatabase)-1056407"/}}

FILES /bin/su - db2inst1 -c /usr/lpp/fdrupstream/db2back\\Note the “-” after the su command that causes the environment variables to be set.

If you were to run this command z⁄OS initiated, you might surround it with sample JCL like this:

{{id name="120_JCL_1056413"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056413"/}}{{id name="1056413"/}}

{{id name="IBMDB2(UniversalDatabase)-1056413"/}}

~/~/AGKTST2 JOB (ACCOUNT),’NAME’,CLASS=N,NOTIFY=AGK
~/~/TSTOS201 EXEC PGM=USTBATCH,PARM=’NOTIMER’
~/~/STEPLIB DD DISP=SHR,DSN=USTSYS.TESTLIB
~/~/SYSUDUMP DD SYSOUT=*
~/~/USTLOG DD SYSOUT=*
~/~/USTPARM DD *
APPLPREF=UPSTR
USAPPL=UPSTREAM
LOGMODE=USTMODE
MAXRETRY=0
CONV=WAIT
TCPTARG=192.168.150.104,1973
ACTION 5 * RUN A JOB
JOBOPTIONS 35 * WAIT FOR THE JOB TO TERMINATE
TERMINATE
FILES /bin/su - db2inst1 -c /usr/lpp/fdrupstream/db2back
ATTENDED N
ENDPARM *
~/~/\\We also recommend that you check Run job from UPSTREAM Wait for job completion, and Log STDOUT and STDERR (JOBOPTIONS 35).

See the DB2 Administration guide for other information including security restrictions and requirements.

Recovery#

The entire process of database restore and roll-forward recovery is fully described in the DB2 Administration Guide and we recommend that you follow the instructions outlined there.

When you restore a database using the control panel, you can select the backup to restore from by the DB2 backup timestamp.

If you are not using the Control Center or are creating the database on a new system from the backup, you need the DB2 backup timestamp. The DB2 backup timestamp is generated by DB2; you cannot use the UPSTREAM version date. When the backup was performed, the timestamp was written to the output of the job as the a component of the single file name used to hold the backup.

You can obtain the backup timestamp by looking at the original file specification of the backup. You can do this by:

• 120_List1_Bullet_1098406

  • Examining the file specification available in the Director Profile tab. Highlight the backup and highlight the version; the specification is in the File Specifications window.
  

• 120_List1_Bullet_1098502

  • In the ISPF interface, use option 1: USTBATCH, option 2: Restore and Inquiry, enter your backup profile, select Inquiry Backups, select a version, select Details and select File Specs.
  

• 120_List1_Bullet_1098699

  • Run a Backup Host Report, selecting the Profile and File Name Mask filters.
  

Once you have the file specification, you can extract the DB2 backup timestamp by knowing the format of the file name. The file name format (on PCs) for UNIX/Linux systems is:

{{id name="120_JCL_1118174"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118174"/}}{{id name="1118174"/}}

{{id name="IBMDB2(UniversalDatabase)-1118174"/}}

~\~\.\PIPE\SQL<version>\<instance>\<database>\<timestamp>\\For example, with DB2 v5.20 and the database SAMPLE, a file name may be:

{{id name="120_JCL_1118176"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1118176"/}}{{id name="1118176"/}}

{{id name="IBMDB2(UniversalDatabase)-1118176"/}}

~\~\.\PIPE\SQL05020\DB2\SAMPLE\19981211151720\\Which gives us a backup timestamp of 19981211151720 (December 11, 1998 3:17:20 PM). Note that DB2 requires a 4 digit year.

For UNIX the database name is:

{{id name="120_JCL_1056469"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056469"/}}{{id name="1056469"/}}

{{id name="IBMDB2(UniversalDatabase)-1056469"/}}

<UPSTREAM directory>/SQL<version>.<instance>.<database>.<timestamp>\\The file name format for Windows systems is:

{{id name="120_JCL_1098152"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1098152"/}}{{id name="1098152"/}}

{{id name="IBMDB2(UniversalDatabase)-1098152"/}}

~\~\.\PIPE\SQL<version>\<instance>\<database>\<timestamp>\\For example, on a Windows system with DB2 v5.20 and the database SAMPLE, a file name may be:

{{id name="120_JCL_1056471"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056471"/}}{{id name="1056471"/}}

{{id name="IBMDB2(UniversalDatabase)-1056471"/}}

~\~\.\PIPE\SQL05020\DB2\SAMPLE\19981211151720\\Resulting in a backup timestamp of 19981211151720.

If you are running most UNIX/Linux systems for example, if UPSTREAM is in the /opt/fdrupstream directory, the version 5.0, the database SAMPLE, and the instance db2inst1, the following line has a backup timestamp of 19981215163725:

{{id name="120_JCL_1056473"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1056473"/}}{{id name="1056473"/}}

{{id name="IBMDB2(UniversalDatabase)-1056473"/}}

/usr/lpp/fdrupstream/SQL05000.db2inst1.SAMPLE.19981215163725\\In UNIX, restores must be performed using the db2 command. Thus to restore the above database, type:

{{id name="120_JCL_1057675"/}}

{{id name="IBMDB2(UniversalDatabase)-120_JCL_1057675"/}}{{id name="1057675"/}}

{{id name="IBMDB2(UniversalDatabase)-1057675"/}}

db2 RESTORE DATABASE SAMPLE LOAD /usr/lpp/fdrupstream/usudb TAKEN AT 19981215163725\\For both PCs and UNIX, (unless otherwise specified) onceOnce the restore has completed the database in roll-forward pending mode. To complete the restore, use the ROLLFORWARD DATABASE command. For example, for the SAMPLE database:

If you are restoring to a new database instance, you need to have UPSTREAM account for this. See Section “Errata” for instructions on changing the instance so UPSTREAM searches using the original instance name or see The OPTIONS Clause above for an example of using the OPTIONS clause to override the original DBID, INSTANCE and DBNAME.

Errata#

Some additional notes for the DB2 agent:

• 120_List1_Bullet_1118203

  • The DB2 agent does not have to be run root or Administrator to use UPSTREAM SOS as it uses TCP/IP and pipes to communicate with an UPSTREAM daemon or service which runs as root or Administrator. In most cases the script must be run as the database SA to have permission to perform the requested function.
  

• 120_List1_Bullet_1056503

  • The UPSTREAM DB2 agent uses the DB2 database ID as the first part of the file name stored on the Storage Server. This ID is DB2 version specific and you may have problems performing restores after a DB2 upgrade. You can specify the ID by creating a single line text file usudb.id in the UPSTREAM directory that contains the database ID that you wish to use. This parameter affects both backups and restores. Most users will use the technique described in The OPTIONS Clause above.
  

• 120_List1_Bullet_1056504

  • The UPSTREAM DB2 agent uses the DB2 instance as the second part of the file name stored on the Storage Server. If you are restoring a database to a different instance, you need to specify the new instance to the agent. You do this by creating a single line text file <DATABASE NAME>.instance in the UPSTREAM directory that contains the instance you wish to use. For UNIX, the database name is often in UPPER case and the instance name is typically in lower case.
  

• 120_List1_Bullet_1056506

  • For example, for database SAMPLE, if you wish to change the instance from DB2 to SECOND, create a file: SAMPLE.instance with the single line: SECOND. This change affects both backups and restores.
  

• 120_List1_Bullet_1101564

  • Most users will use the technique described in The OPTIONS Clause above.
  

• 120_List1_Bullet_1118219

  • If you are using the UPSTREAM Reservoir and restoring from a vault, you will need to specify the VAULTNUMBER=<vaultnumber> in the usudb.dat file. Don't forget to put it back when you have completed your restore.
  

• 120_List1_Bullet_1056508

  • You can restore a backup to a flat file for recovery using DB2 tools or verification with the DB2 tool db2ckbkp: You can then use regular DB2 tools to recover the database. You may need assistance to select the database for restore. Contact tech support for assistance in restoring a database to a flat file.