The Database Services Utility is an application program that supplies a user interface to the IBM DB2 Server for VSE & VM product and that, with some limitations, also works with other relational databases that use DRDA flow. Consider using it to load or reload data into, or unload data from, a database. If the amount of data to be processed is large, or if exact sequences of database commands are to be used on a periodic basis, consider using the utility.
|You usually employ the Database Services Utility for DB2 Server for |VSE & VM for large-scale processing of relational databases in a batch |environment. Input to the utility, as well as its reported output, is |in the form of sequential files. In VM, you have another way of using |the Database Services Utility. Although DB2 Server for VM batch processing is the utility's usual operating mode, you can also use it interactively by specifying a terminal as its input file. You can also direct its output to a terminal instead of storing the output as a physical file.
In addition to loading data into and unloading it from a database, you can use the utility to process SQL statements and to transfer packages into or out of databases. You can do these operations in either single or multiple user mode.
The four primary Database Services Utility control commands are DATALOAD, DATAUNLOAD, UNLOAD, and RELOAD. The UNLOAD and RELOAD commands are qualified by the object they manipulate:
UNLOAD RELOAD ____________________________________________________ UNLOAD DBSPACE RELOAD DBSPACE UNLOAD TABLE RELOAD TABLE UNLOAD PACKAGE RELOAD PACKAGE
The DATALOAD command inserts data from a sequential file into a DB2 Server for VSE & VM table. You specify the format of the sequential file.
The DATAUNLOAD command selects data from tables and copies it to a sequential file. You specify the format of the sequential file.
The UNLOAD commands provide a backup function for existing tables, dbspaces, and packages. These control commands are also useful for distributing copies of data to other sites that use the database manager. The output of each UNLOAD command is a sequential file formatted for the use of its corresponding RELOAD command.
The RELOAD commands restore information previously backed up with UNLOAD commands. The RELOAD commands are also useful for reorganizing database tables or dbspaces and for receiving tables and packages from other sites. With the RELOAD TABLE command, you can create new tables from logical views previously unloaded from existing tables. You can then build an index for each newly created table. The RELOAD package can be used to distribute packages to other sites that use the DB2 Server for VSE & VM application server or other application servers that support |DRDA flow.
The Database Services Utility provides other commands for your convenience:
You can use the Database Services Utility DATALOAD command to load or add rows from a user-defined sequential file. The input to DATALOAD processing consists of a set of Database Services Utility commands and input data records. The utility commands identify:
You can specify how often DATALOAD processing commits insertions to the database. Specify a number of input data records, and the insertions are committed each time the DATALOAD command processes the specified number of records. If a subsequent error occurs, the database manager only has to undo the database changes made since the last commit point. The committing and restarting capabilities of the database manager are useful when you are loading large amounts of data with the utility.
Referential constraints (rules that require all values in dependent tables to match corresponding values in parent tables) are enforced during DATALOAD processing. This means that primary key rows must be loaded before their foreign key rows. You can improve the utility's performance by deactivating the constraints before loading the data and activating them again afterwards. For descriptions and instructions on DATALOAD processing, see Chapter 2, Loading Data with the Database Services Utility.
The DATAUNLOAD command allows you to selectively unload data from a database to a sequential access method (SAM) output file. You can:
You can use the other Database Services Utility unload-data commands (UNLOAD DBSPACE and UNLOAD TABLE) to:
You can also use these UNLOAD commands, immediately followed by their RELOAD counterparts, to:
The main difference between the DATAUNLOAD and UNLOAD commands is that DATAUNLOAD allows you to specify more about the data you unload than the UNLOAD commands allow. Consequently, the UNLOAD commands are simpler, but it is easier to work with output data from a DATAUNLOAD command. For descriptions and instructions on DATAUNLOAD and UNLOAD processing, see Chapter 3, Unloading Data with the Database Services Utility.
DATALOAD and RELOAD are essentially the same kind of operation: they both insert data into databases; however, RELOAD inserts data that was previously unloaded using the UNLOAD command while DATALOAD uses a user-defined file of data, or the output file of a DATAUNLOAD command.
RELOAD processing can purge existing tables before reloading them (from previously unloaded information). Similarly, you can unload a view as if it were a table and reload it as a new table. When RELOAD creates a new table, it does not automatically re-create all the entities associated with the old table; you must specify views, indexes, keys, and access privileges. For descriptions and instructions on RELOAD processing, see Chapter 4, Reloading Data with the Database Services Utility.
You can use the Database Services Utility to unload a package from a DB2 Server for VSE & VM database to a portable file. A package consists of the internally optimized application SQL statements stored in (bound to) the database at preprocessing time and used by the database with the application at execution time. A portable file is one that contains an unloaded DB2 Server for VSE & VM package that is ready for distribution to another application server. You can unload a package to a file to:
The UNLOAD PACKAGE command unloads the package, along with information about the way it was created, to a portable file. You can then send the file to the application server that requires it. It is unnecessary to distribute source programs or to preprocess and compile source code at the receiving location. For descriptions and instructions on unloading packages, see Chapter 5, "Unloading and Reloading Packages with the Database Services Utility".
You can use the Database Services Utility to load a package from a file into a DB2 Server for VSE & VM database. You can do this to achieve the following:
The database manager preprocesses reloaded packages to ensure that all dependencies are satisfied on the installing system.
When a RELOAD PACKAGE command loads a package into an application server, the module can replace another package with the same name. The new package can carry over the run-privileges previously granted to users of the replaced version. You can reload a package created and unloaded on a VM system, and use it on on a VSE system; or you can reload a package created and unloaded on a VSE system, and use it on a VM system. For descriptions and instructions on reloading packages, see Chapter 5, Unloading and Reloading Packages with the Database Services Utility.
The Database Services Utility executes SQL statements against the database. You can use most SQL statements in a VM utility control file or a VSE utility input control card file. SQL statements not supported by the Database Services Utility are those used only in application programs (SELECT statements with INTO clauses, cursor management commands, DESCRIBE, EXECUTE, INCLUDE, PREPARE, and WHENEVER).
A basic job has five components that control the input and output of data. All five are discussed in more detail later in this chapter:
A basic job has three components that control the input and output of data. All three are discussed in more detail later in this chapter: