Posted on 2004-04-14
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 63 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 62 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now