A number of situations occur in enterprise operations that could require moving data from one iSeries server to another. For example, a new dealership might open in a region, and some clients from one or two other dealerships might be transferred to the new dealership as determined by client address. Perhaps a dealership closed or no longer represents Spiffy Corporation sales and service. That dealer’s inventories and required service information must be allocated to either the regional office or other area dealerships. Perhaps a dealership has grown to the extent that it needs to upgrade its server, and the entire database must be moved to the new server.

Some alternatives for moving data from one iSeries server to another are:

• User-written application programs
• Interactive SQL (ISQL)
• DB2® UDB for iSeries Query Management functions
• Copy to and from tape or diskette devices
• Copy file commands with DDM
• The network file commands
• iSeries server save and restore commands

Creating a User-Written Application Program

A program compiled with DUW connection management can connect to a remote database and a local database and FETCH from one to INSERT into the other to move the data. By using multi-row FETCH and multi-row INSERT, blocks of records can be processed at one time. Commitment control can be used to allow checkpoints to be performed at points during the movement of the data to avoid having to start the copy over in case of a failure.

Querying a database using Interactive SQL

Using the SQL SELECT statement and interactive SQL, you can query a database on another iSeries server for data you need to create or update a table on the local server. The SELECT statement allows you to specify the table name and columns containing the desired data, and selection criteria or filters that determine which rows of data are retrieved. If the SELECT statement is successful, the result is one or more rows of the specified table.

In addition to getting data from one table, SQL allows you to get information from columns contained in two or more tables in the same database by using a join operation. If the SELECT statement is successful, the result is one or more rows of the specified tables. The data values in the columns of the rows returned represent a composite of the data values contained in specified tables.

Using an interactive SQL query, the results of a query can be placed in a database file on the local server. If a commitment control level is specified for the interactive SQL process, it applies to the application server (AS); the database file on the local server is under a commitment control level of *NONE.

Interactive SQL allows you to do the following:

• Create a new file for the results of a select.
• Replace and existing file.
• Create a new member in a file.
• Replace a member.
• Append the results to an existing member.

Consider the situation in which the KC105 dealership is transferring its entire stock of part number ‘1234567’ to KC110. KC110 queries the KC105 database for the part they acquire from KC105. The result of this inventory query is returned to a database file that already exists on the KC110 server. This is the process you can use to complete this task:

Use the Start SQL (STRSQL) command to get the interactive SQL display. Before you enter any SQL statement (other than a CONNECT) for the new database, specify that the results of this operation are sent to a database file on the local server by doing the following steps:

1. Select the Services option from the Enter SQL Statements display.
2. Select the Change Session Attributes option from the Services display.
3. Enter the Select Output Device option from the Session Attributes Display.
4. Type a 3 for a database file in the Output device field and press Enter. The following display is shown:

   Change File
   
   Type choices, press Enter.
   
   File . . . . . . . . .   QSQLSELECT   Name
   Library  . . . . . .   QGPL         Name
   Member . . . . . . . .   *FILE        Name, *FILE, *FIRST
   
   Option . . . . . . . .   1            1=Create new file
   2=Replace file
   3=Create new member
   4=Replace member
   5=Add to member
   
   For a new file:
   Authority  . . . . .   *LIBCRTAUT   *LIBCRTAUT, *CHANGE, *ALL
   *EXCLUDE, *USE
   authorization list name
   
   Text . . . . . . . .
   
   F3=Exit     F5=Refresh     F12=Cancel

5. Specify the name of the database file that is to receive the results.

When the database name is specified, you can begin your interactive SQL processing as shown in the example below.

Enter SQL Statements

Type SQL statement, press Enter.
Current connection is to relational database KC000.
CONNECT TO KC105__________________________________________________________
Current connection is to relational database KC105.
====> SELECT * FROM INVENTORY_____________________________________________
WHERE PART = '1234567'____________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
__________________________________________________________________________
Bottom
F3=Exit   F4=Prompt   F6=Insert line   F9=Retrieve   F10=Copy line
F12=Cancel            F13=Services     F24=More keys

For more information on the SQL programming language and interactive SQL, see the SQL Programming Concepts and the SQL Reference topics in the iSeries Information Center.

Querying remote servers using DB2 UDB for iSeries Query Management function

The DB2 UDB for iSeries Query Management function provides almost the same support as interactive SQL for querying a remote server and returning the results in an output file to the local server.

Both interactive SQL and the query management function can perform data manipulation operations (INSERT, DELETE, SELECT, and so on) for files or tables without the requirement that the table (or file) already exist in a collection (it can exist in a library). Also, query management uses SQL CREATE TABLE statements to provide data definition when a new table is created on the server as a result of the query. Tables created from a query management function follow the same guidelines and restrictions that apply to a table created using SQL.

However, the query management function does not allow you to specify a member when you want to add the results to a file or table. The results of a query function are placed in the first file member unless you use the Override with Database File (OVRDBF) command to specify a different member before starting the query management function.

For more information on the query management function, see the Query Management Programming book.

Copying files to and from tape or diskette

You can copy a table or file to tape or diskette using the Copy to Tape (CPYTOTAP) andCopy to Diskette (CPYTODKT) commands on the iSeries server.

Data on tape or diskette can be loaded on another server using the Copy from Tape (CPYFRMTAP) and Copy from Diskette (CPYFRMDKT) commands. For more information about using these commands, see the Storage solutions topic.

You can also use the Copy File (CPYF) command to load data on tape into DB2 UDB for iSeries. This is especially useful when loading data that was unloaded from DB2 UDB for z/OS®, or DB2 UDB Server for VM (SQL/DS™). Nullable data can be unloaded from these servers in such a way that a single-byte flag can be associated with each nullable field. CPYF with the *NULLFLAGS option specified for the FMTOPT parameter can recognize the null flags and ignore the data in the adjacent field on the tape and make the field null in DB2 UDB for iSeries. Another useful FMTOPT parameter value for importing data from IBM® mainframes is the *CVTFLOAT value. It allows floating point data stored on tape in System/390® format to be converted to the IEEE format used by DB2 UDB for iSeries.

Moving data between iSeries servers using Copy File Commands

Another way to move data from one iSeries server to another is to copy the data using the copy file commands with DDM. You can use the Copy File (CPYF), Copy Source File (CPYSRCF), and Copy From Query File (CPYFRMQRYF) commands to copy data between files on source and application source (AS)s. You can copy local relational database or device files from (or to) remote database files, and remote files can also be copied to remote files.

For example, if a dealership closes, the distributed relational database administrator can copy the client and inventory tables from the remote server to the local regional server. The administrator needs a properly authorized user profile on the application source (AS) to access and copy the tables and must create a DDM file on the application requester (AR) for each table or file that is copied. The following example shows the command the database administrator would use to copy a table called INVENT in a collection called SPIFFY from a server with a remote location name of KC105 to a regional center server called KC000. A DDM file called INCOPY in a library called TEST on the application requester (AR) KC000 is used for the file access. These commands are run on the KC000 server:

CRTDDMF   FILE(TEST/INCOPY) RMTFILE(SPIFFY/INVENT)
   RMTLOCNAME(KC105)
CPYF      FROMFILE(TEST/INCOPY)  TOFILE(TEST/INVENTDDM)
   MBROPT(*ADD)

In this example, the administrator runs the commands on the KC000 server. If the administrator is not on the KC000 server, then pass-through must be used to run these commands on the KC000 server. The Submit Remote Command (SBMRMTCMD) command cannot be used to run the above commands because the iSeries server cannot be a application requester (AR) and a application source (AS) for the same job.

Consider the following items when using this command with DDM:

• A DDM file can be specified on the FROMFILE and the TOFILE parameters for theCopy File (CPYF) command and Copy Source File (CPYSRCF) commands.
  Note:
  For the Copy From Query File (CPYFRMQRYF), Copy from Diskette (CPYFRMDKT), and Copy from Tape (CPYFRMTAP) commands, a DDM file name can be specified only on the TOFILE parameter; for the Copy to Diskette (CPYTODKT) and Copy to Tape (CPYTOTAP) commands, a DDM file name can be specified only on the FROMFILE parameter.

• When a delete-capable file is copied to a non-delete capable file, you must specify COMPRESS(*YES), or an error message is sent and the job ends.

• If the remote file name on a DDM file specifies a member name, the member name specified for that file on the Copy File (CPYF) command must be the same as the member name on the remote file name on the DDM file. In addition, the Override with Database File (OVRDBF) command cannot specify a member name that is different from the member name on the remote file name on the DDM file.

• If a DDM file does not specify a member name and if the Override with Database File (OVRDBF) command specifies a member name for the file, the Copy File (CPYF) command uses the member name specified on the OVRDBF command.

• If the TOFILE parameter is a DDM file that refers to a file that does not exist, CPYF creates the file. Following are special considerations for remote files created with theCopy File (CPYF) command:
  • The user profile for the target DDM job must be authorized to the Create Physical File (CRTPF) command on the application source (AS).
  • For an iSeries target, the TOFILE parameter has all the attributes of the FROMFILE parameter except those described in the File Management topic in the iSeries Information Center.

• When using TCP/IP, the second element of the RMTLOCNAME parameter of theCreate Distributed Data Management File (CRTDDMF) command must be *IP.

For more information about using the Copy File commands to copy between servers, see theDistributed Data Management topic in the iSeries Information Center.

Transferring data over networks using Network File Commands

Data can be transferred over networks protocols that support SNA distribution services (SNADS). In addition to APPC and APPN protocols used with distributed relational database processing, SNADS can be used with binary synchronous equivalence link (BSCEL) and SNA Upline Facility (SNUF) protocols. An iSeries server supported by SNADS can send data to another server with the Send Network File (SNDNETF) command and receive a network file from another server with the Receive Network File (RCVNETF) and Work with Network Files (WRKNETF) commands.

Moving a table using server save and restore commands

You can move a table from another iSeries server using the Save Object (SAVOBJ) andRestore Object (RSTOBJ) commands. The save commands save database files on tape, diskette, or a save file. The save file can be distributed to another server through communications.

The save and restore commands used to save and restore tables or files include:

• Save Library (SAVLIB) command saves one or more collections or libraries
• Save Object (SAVOBJ) command saves one or more objects (including database tables and views)
• Save Changed Object (SAVCHGOBJ) command saves any objects that have changed since either the last time the collection or library was saved or from a specified date
• Restore Library (RSTLIB) command restores a collection or library
• Restore Object (RSTOBJ) command restores one or more objects (including database tables and views)

For example, if two dealerships were merging, the save and restore commands could be used to save collections and tables for one relational database, which are then restored on the remaining server’s relational database. To accomplish this an administrator would:

1. Use the Save Library (SAVLIB) command on server A to save a collection or use theSave Object (SAVOBJ) command on server A to save a table.
2. Specify whether the data is saved to a save file, which can be distributed using SNADS, or saved on tape or diskette.
3. Distribute the save file to server B or send the tape or diskette to server B.
4. Use the Restore Library (RSTLIB) command on server B to restore a collection or use the Restore Object (RSTOBJ) command on server B to restore a table.

A consideration when using the save and restore commands is the ownership and authorizations to the restored object. A valid user profile for the current object owner should exist on the server where the object is restored. If the current owner’s profile does not exist on this server, the object is restored under the QDFTOWN default user profile. User authorizations to the object are limited by the default user profile parameters. A user with QSECOFR authority must either create the original owner’s profile on this server and make changes to the restored object ownership, or specify new authorizations to this object for both local and remote users.

For more information about the save and restore commands, see the Backup and Recoverytopic in the iSeries Information Center.