Solved

AS400 - SQL SERVER MIRROR

Posted on 2004-04-14
5
587 Views
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
0
Comment
Question by:georgethanos
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
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)

Demos:

Trigger mechanism:
create trigger Ins_NonDups on tableA for insert
as

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*/
/*SET NOCOUNT ON*/
USE Northwind
/*IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'Cust_Payments')
   DROP TABLE Cust_payments*/ /*Uncomment if requring to delete the tables after the demo*/
GO
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*/

GO
USE Northwind
GO
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*/
SET NOCOUNT OFF
GO /*Start DEMO*/ /*Add a ignore_dup_key -- you would only need to change your existing key to this type on your destination table*/
CREATE UNIQUE CLUSTERED INDEX cust_indpayments
   ON Cust_payments(cust_ind)
   WITH IGNORE_DUP_KEY
GO
/*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
union
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.
0
 

Assisted Solution

by:vingom
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.
0
 
LVL 13

Expert Comment

by:danblake
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)
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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

18 Experts available now in Live!

Get 1:1 Help Now