If you are defining a subscription that replicates to a multi-member physical file on an IBM i system (AS/400), there are special implications for the Destination Options of the subscription properties. The information provided in this topic assumes that the source table type always matches the destination table type you select. If you select a source table that is a multi-member file, but select <SQL File Type> in the Member field for the destination table, only the data for the default member of the source table will be replicated. If the source is enabled for Incremental Replication you must replicate only to regular SQL files.
New file: If you select Create Member When Subscription Is Saved and neither the multi-member destination file nor the specific member exists, the Data Replicator creates both the file and the member.
New member in existing file: If you select Create Member When Subscription Is Saved and the multi-member destination file exists but the specific member does not exist, the Data Replicator creates the member and adds it to the existing file. Previously existing data for other members is not affected.
Existing file, member, and table structure: If you select Create Member When Subscription Is Saved and both the multi-member file and the specific member exist, the Data Replicator asks you to confirm the operation when you attempt to save the subscription. If you confirm the operation, the Data Replicator drops and recreates the specified member for the existing file. Previously existing data for that member is discarded at that time, but previously existing data for other members is not affected. Between changes to the subscription, you can preserve replicated data for the selected member by selecting Append Replicated Rows To Existing Data. If you do not want to preserve existing data for the selected member, select Delete Existing Data Before Replication.
Changing from multi-member to SQL file type: If you select Create Member When Subscription Is Saved and enter the name of an existing multi-member physical table file in the Table Name field, but select <SQL File Type> in the Member field, the Data Replicator prompts you to confirm the operation and then drops the previously existing multi-member file and creates a regular SQL file.
Changing from SQL to multi-member file type: If you select Create Member When Subscription Is Saved and enter the name of an existing regular SQL table in the Table Name field, but do not select <SQL File Type> in the Member field, the Data Replicator reports an error when you attempt to save the subscription. Either select <SQL File Type> in the Member field or enter a different name in the Table Name field.
New file: If you select Re-create Member On Each Replication, and if neither the destination table file nor the specific member exists, the Data Replicator creates both the file and the member when the subscription is first run.
New member in existing file: If you select Re-create Member On Each Replication, and if the multi-member destination table file exists but the specific member does not exist, the Data Replicator creates the member and adds it to the existing file when the subscription is first run. Previously existing data for other members is not affected.
Existing file, member, and table structure: If you select Re-create Member On Each Replication, and if both the multi-member table file and the specific member exist, the Data Replicator asks you to confirm the operation and then drops and recreates the specified member for the existing file when the subscription is first run. Previously existing data for that member is discarded, but previously existing data for other members is unaffected.
Changing table structure: To use the Data Replicator to change the table structure of an existing multi-member destination table file, you must select Re-Create Member On Each Replication and enter a SQL command (on the Processing tab or corresponding wizard screen) that drops the entire destination table before each replication.
Changing from multi-member to SQL file type: If you select Re-create Member On Each Replication, and if you enter the name of an existing multi-member physical table file in the Table Name field but select <SQL File Type> in the Member field, the Data Replicator prompts you to confirm the operation when you attempt to save the subscription. If you confirm the operation, the Data Replicator drops the previously existing multi-member file and creates a regular SQL file.
Changing from SQL to multi-member file type: If you select Re-create Member On Each Replication and enter the name of an existing regular SQL table in the Table Name field, but do not select <SQL File Type> in the Member field, the Data Replicator reports an error when you attempt to save the subscription. Either select <SQL File Type> in the Member field or enter a different name in the Table Name field.
New file: If you select Use Existing Member and the destination table file and/or the specific member do not exist, the Data Replicator displays an error message when you try to save the subscription. Either use an AS/400 utility to create the destination table file and member or choose another Destination Option for your subscription.
Existing file and member: If you select Use Existing Member and both the destination table file and the specific member exist, the Data Replicator uses the existing file and member at replication time. To delete previously existing data for the specified member, select Delete Existing Data Before Replication. To preserve previously existing data for that member, select Append Replicated Rows to Existing Data. Previously existing data for other members is not affected by your choice between these two options.
Changing from multi-member to SQL file type: If you select Use Existing Table and enter the name of an existing multi-member physical table file in the Table Name field, and you select <SQL File Type> in the Member field, the Data Replicator displays an error message when you attempt to save the subscription. The error message appears because the SQL data types specified in the modified subscription do not match the data types in the existing non-SQL multi-member table. Either select a different listing in the Member field or choose Create Member When Subscription Is Saved or Re-create Member On Each Replication.