Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

577 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