Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



Posted on 2004-04-14
Medium Priority
Last Modified: 2008-02-26
hi all

i have a AS-400 (which become very slow:) and i need to have 5 tables (in the same LIB) to be replicated every night in a MS SQL server ... Which is the best practice to follow so that every morning the data of these 5 tables will be identical to the as400. Of course i dont want all the tables to be reimported in the SQLSRV but only the new or changed data.

Any info will be much much appreciated

Thanx George
Question by:georgethanos
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 13

Accepted Solution

danblake earned 252 total points
ID: 10823007
Of course i dont want all the tables to be reimported in the SQLSRV but only the new or changed data.

Unless you have a replication engine driver suitable for SQL Server (you'll better off importing the whole dataset with dup check ignore set on a covering index or pk index and import in this way or use a trigger for the insert.  Any import into SQL Server via a linked server will be slower)


Trigger mechanism:
create trigger Ins_NonDups on tableA for insert

insert table_B (Primary_Key_B, field_B1, field_B2)
/*Excessive bracket removed..*/
select Primary_KeyA, field_A1,field_A2
from inserted
where primary_key_a not in (select Primary_Key_B from table_B)

Index Mechanism Demo:
You would need to to check against the key-columns in the second table as an exclusion criteria to your input query which takes a lot longer than the following demo using ignore dups (my preferred mechanism for this particular type of problem/solution):
The fastest way will be to change your index to ignore dups, as demonstrated in the below example (you don't have to run this in Northwind any test/scratch DB will do-- it only creates one table + one index, and does not take up a lot of space this demo)

/*Check our tables do not exist*/
USE Northwind
      WHERE TABLE_NAME = 'Cust_Payments')
   DROP TABLE Cust_payments*/ /*Uncomment if requring to delete the tables after the demo*/
USE Northwind
/*IF EXISTS (SELECT name FROM sysindexes
      WHERE name = 'cust_indpayments')
   DROP INDEX Cust_payments.cust_indpayments */ /*Uncomment if requring to delete the tables after the demo*/

USE Northwind
CREATE TABLE Cust_payments
 cust_ind int NOT NULL, /*=CustomerID in customer table*/
 cust_payment money NOT NULL,
 commissionmade decimal(2, 2) NOT NULL
) /*Create our test table, and some batch data*/
INSERT Cust_payments
   VALUES (1, 310, .1)
INSERT Cust_payments
   VALUES (2, 1500, .5)
INSERT Cust_payments
   VALUES (3, 980, .7)
INSERT Cust_payments
   VALUES (5, 250, .3)
INSERT Cust_payments
   VALUES (8, 75, .6)
GO /*End of population of batch data*/
GO /*Start DEMO*/ /*Add a ignore_dup_key -- you would only need to change your existing key to this type on your destination table*/
   ON Cust_payments(cust_ind)
/*Try insert of ALL dup values -- should not add any*/
insert into Cust_pay
select * from Cust_payments
/*Error returned:
Server: Msg 3604, Level 16, State 1, Line 1
Duplicate key was ignored.*/
/*This does not cause end-of execution of the batch*/

/*Otherwise use the following:*/
insert into Cust_payments
select * from Cust_payments
select 55 as cust_ind, 8036 as  cust_payment, .06 as commissionmade

/*Lets have a look at some of the results now..*/
select * from Cust_payments
/*Notice that 55 has been added all other updates, ignored*/

/*This is the fastest way-sql server rejects duplicates, but procceds to update/add
valid rows*/

The error msg 3604 can be safely ignored from the execution and will not cause the SP to END/fail -- you can ignore this particular error number in certain cases.  Ensure to use SET NO COUNT OFF when using this particular functionality (I've seen some oddities with SET NO COUNT ON and using duplicate indexes for ignoring row updates), You can use @@ROWCOUNT to determine the number of added rows.

Assisted Solution

vingom earned 248 total points
ID: 10847539
Although you don't want to update all the data from the AS400, it may be the easiest route.  We use Microsoft Host Integration Server to allow us to replicate our AS400 data to tables on the SQL Server.  We also use this interface to allow the SQLServer to upload data to the AS400.
LVL 13

Expert Comment

ID: 10848963
If you run the process from the SQL Server box (which you can do to/from the AS400) you would need to implement merge replication (inbuilt part of sql-server, do you have drivers to create a linked server directly from SQL Server -> AS400 (latest odbc/oledb drivers installed on sql-server?)) this way it would only retrieve the latest rows required/update rows as rqd.

(Push subscription from AS400-> SQL Server on demand)

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question