[Steps] use SQL DTS function from a DB / 2 to the SQL Server data transmission.: Operating Systems

[Steps] use SQL DTS function from a DB / 2 to the SQL Server data transmission.

[Introduction]
Thanks everyone's attention so that the "customs systems and enterprise ERP system from several networking" with the issue of solving the problem coming to an end, after a self-test function SQL2000 DTS, DTS tools through feeling from DB / 2 to take several very convenient and easy to achieve . Now paste the implementation steps out for reference.

[Environmental]
IBM AS/400e, OS/400, DB / 2, ERP
IBM PC SERVER, WIN2000 SERVER, SQL2000

[Title]
"How to use SQL2000 DTS function from a DB / 2 to the SQL Server data transmission? "
Below the LIU / EXECTL (EXECTL) PF example, elaborate illustrated:

1. First installed in Win2000 Server Platform Client Access/400 and confirmed ODBC in the "Client Access ODBC Driver (32-bit)," drive:

Qingzhou back in :2004-06-15 16:44:12 2. Win2000 Server platform installed in the Chinese SQL2000 Standard Edition, SQL Server Manager guarantees related services after the commencement of the implementation the "import and export data": Qingzhou back in :2004-06-15 16:45:39 3. A "DTS Import / Export wizard" interface, the next step: Qingzhou back in :2004-06-15 16:47:10 4. In the data source, select "Client Access ODBC Driver (32-bit)," Driver, the new "user / System DSN": Qingzhou back in :2004-06-15 16:49:15 5. A system data source: Qingzhou back in :2004-06-15 16:51:13 6. Choose "Client Access ODBC Driver (32-bit)" as the data source drivers: Qingzhou back in :2004-06-15 16:55:02 7. Click complete: Qingzhou back in :2004-06-15 16:56:02 8. In the allocation of the pop-up window, enter "data source name" and select System IP address (ie: AS/400 ip address), proposed that "the default SQL" designated for the operation to determine from the current window: Qingzhou back in :2004-06-15 17:03:59 9. Then directly in the "user / System DSN" column just to create the importation of the name, such as: EXECTL, and the importation of landing in the fields below the AS/400 user profile and password (with sufficient authority), the next step: Qingzhou back in :2004-06-15 17:07:19 10. "Purpose" Column Selection "for SQL Server Microsoft OLE DB providers," following the "database" of a new self, such as: AS400_Date: Qingzhou back in :2004-06-15 17:09:09 11. Table designated treatment type, here "from the source database replication table and view": Qingzhou back in :2004-06-15 17:11:09 12. Then choose to be dealt with in the OS/400> Qingzhou back in :2004-06-15 17:14:04 13. DTS packet scheduling set good time, the implementation of frequency and the preservation matters: Qingzhou back in :2004-06-15 17:15:37 14. And named to the DTS packages, such as: test Qingzhou back in :2004-06-15 17:16:56 15. A configuration summary, accurate confirmed after the completion point: Qingzhou back in :2004-06-15 17:18:31 16. If the above 13-step "time" - run, run screen will appear immediately after completion results are as follows: Qingzhou back in :2004-06-15 17:20:22 17. SQL launched "enterprise management," and "data conversion services - local package" will have just successfully created DTS package - test: Qingzhou back in :2004-06-15 17:21:49 18. Double-click test DTS package can occur under the map editing screen: Qingzhou back in :2004-06-15 17:23:17 19. At the same time in AS400_Date EXECTL the table in a table, query operation Show: Qingzhou back in :2004-06-15 17:25:51 20. In the following table, we can clearly see EXECTL various fields and records, from this point the entire DB / 2 → SQL2000 data transmission work complete. Xuguopeng back in :2004-06-15 18:02:39 You first top so quickly completed Mamei back in :2004-06-15 18:15:21 Oh! This is the admission data, we need to do some corresponding handling! Qingzhou back in :2004-06-15 18:23:22 Yes, it is now as far as possible require the background of the client software to use this database export data, this workload will be smaller. Or that the ERP enterprise production systems, PF document to satisfy the background of the client software interface requirements. Solve the question of admission, but realized "similar real-time" from several functions, solve other problems relatively simple. Mamei back in :2004-06-15 18:27:58 I am now used to take data ACCESS! From real-time processing of data is not very good! Mamei back in :2004-06-15 18:32:13 ORACLE would also like to use the past to achieve this! Unfortunately understand ORACLE! Qingzhou back in :2004-06-15 18:33:44 [Quote: 1613602c0d = "mamei"] I am now used to take data ACCESS! From real-time processing of data is not very good! [/ Quote: 1613602c0d] "Real-time" I think the issue is> ACCESS database itself is limited and does not support more than two G data, you may want to consider using SQL or ORACLE. Wildfish back in :2004-06-16 11:42:22 Big brother, if the volume of data is very large terrorist. This machine will be a corresponding increase in load. Http://www.microsoft.com/china/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx Look at the proposal boss dts programming to see whether the incremental transmission data, Oh, I know they can, but no time to read, or for the boss to solve the problem. Ibmas400 back in :2004-06-16 15:42:06 Qingzhou really好心人ah. . . Rare. Kimdai back in :2004-06-17 11:35:47 Ah hard to see how the automatic incremental import more valuable Qingzhou back in :2004-06-17 15:04:59 [Quote: b0065e219b = "wildfish"] boss, if the volume of data is very large terrorist. This machine will be a corresponding increase in load. Http://www.microsoft.com/china/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx Look at the proposal boss dts programming to see whether the incremental transmission data, Oh, I know ?.........[/ quote: b0065e219b] OS/400 through SQL2000 to visit> I carefully read DTS package Design window, But that "large capacity insert task" feature with the ability to access large amounts of data improve efficiency. Mamei back in :2004-06-17 18:22:31 [Quote: 6742c842d3 = "qingzhou"] 12. Then choose to be dealt with in the OS/400> I do this step> ! ! Mamei back in :2004-06-17 18:25:13 If the second election here, Not> ! ! Qingzhou back in :2004-06-17 18:27:30 [Quote: 45bdd6fdf2 = "mamei"] I do this step> ! ! [/ Quote: 45bdd6fdf2] You really unfortunate,: D, I repeated this a few days do not question the times. . . Inspection SQL2000 configuration correct? I am afraid sometimes saying do not understand SQL normal installation, configuration in accordance with the steps it should take no problem. Mamei back in :2004-06-17 18:30:44 Yes, Now> The second option election! ! Engage understand why! Qingzhou back in :2004-06-17 18:32:40 [Quote: 824bfa92ac = "mamei"] Yes, Now> The second option election! ! Engage understand why! [/ Quote: 824bfa92ac] The second option is to provide for the function, and then the window will let you enter SQL statements. . . The aim is not to achieve the same. Mamei back in :2004-06-17 18:38:03 I am here is a few tables with SQL using the same link! Wildfish back in :2004-06-18 09:29:54 Mamei, if you specify sql, then you have to add the name, but also your competence for users. You can put your sql 400> Qingzhou back in :2004-06-21 09:55:07 [Quote: f65cea2af3 = "mamei"] before ORACLE would also like to use to achieve this! Unfortunately understand ORACLE! [/ Quote: f65cea2af3] Installed CA/400, at the same time there will be "OLEDB For db2/400" data interface. NT then installed> Choi back in the black :2004-07-08 14:43:36 Two machines to the two sql database synchronized, in accordance with a master of the above methods, is the beginning of the first proper introduction, the rest of the time the data is quite different, oh, look at the data transormation services, not data packets dts run in the jobs sqlserver agent also revealed that the mission was running, I would like to ask you master is how matter Oh, and try a lot of ways it will not work, headache, oh, that Bangding ah? ? ? ? Choi back in the black :2004-07-08 14:45:21 Two machines to the two sql database synchronized, in accordance with a master of the above methods, is the beginning of the first proper introduction, the rest of the time the data is quite different, oh, look at the data transormation services, not data packets dts run in the jobs sqlserver agent also revealed that the mission was running, I would like to ask you master is how matter Oh, and try a lot of ways it will not work, headache, oh, that Bangding ah? ? ? ? Qingzhou back in :2004-07-08 16:16:27 [Quote: 83098e22f6 = "color black"] two machines to the two sql database synchronized, in accordance with a master of the above methods, is the beginning of the first proper introduction, the rest of the time the data is quite different Oh, look at the data transormation services, data packets dts not run in the jobs sqlserver agent also revealed that the mission was ..........[/ quote: 83098e22f6] This article is from the IBM AS/400 data to push SQL2000, rather than two-SQL SERVER between data synchronization, it is not confused! To achieve SQL SERVER 2 sets of data synchronization, try the following approaches: [B: 83098e22f6] "synchronization SQL Server data" [/ b: 83098e22f6] -- Test environment: SQL Server2000, and the remote server: xz, user name: sa, no password, database tests: test -- Server> Create table [user] (id int primary key, number varchar (4), name varchar (10)) Go -- Following in the LAN (local operation) -- The machine table, state Description: null said that the new record, a record that changes have been made, that no change in the record 0 If exists (select * from dbo.sysobjects where id = object_id (N '[user]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) Drop table [user] GO Create table [user] (id int identity (1,1), the number varchar (4), name varchar (10), state bit) Go -- Trigger create and safeguard the value of state field Create trigger t_state> After update As Update set state [user] = 1 From [user] a join inserted b> Where a.state is not null Go -- To facilitate the synchronization, and the creation of links to the server to server synchronization -- Here called the remote server: xz, user name: sa, no password If exists (select> Exec sp_dropserver 'srv_lnk', 'droplogins' Go Exec sp_addlinkedserver 'srv_lnk','',' SQLOLEDB', 'xz' Exec sp_addlinkedsrvlogin 'srv_lnk', 'false', null, 'sa' Go -- Create sync with the storage process If exists (select * from dbo.sysobjects where id = object_id (N '[dbo] [p_synchro]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) Drop procedure [dbo] [p_synchro] GO Create proc p_synchro As -- Set XACT_ABORT> -- Launch of the remote server MSDTC service -- Exec master .. xp_cmdshell 'isql / S "xz" / U "sa" / P "," / q "exec master .. xp_cmdshell''net start msdtc''no_output"' no_output -- The start of the MSDTC service -- Exec master .. xp_cmdshell 'net start msdtc' no_output -- A distributed transaction processing, if table with logo are taken care of keys, use the following method -- BEGIN DISTRIBUTED TRANSACTION -- Delete data synchronization Delete from srv_lnk.test.dbo. [User] Where id not in (select id from [user]) -- New data synchronization Insert into srv_lnk.test.dbo. [User] Select id, number, name from [user] where state is null -- Modified data synchronization Update srv_lnk.test.dbo. Set [user] = B.number number, name = b.name From srv_lnk.test.dbo. A [user] Join [user] = b> Where b.state = 1 -- Synchronization after updating this machine signs Update set state [user] = 0 where isnull (state, 1) = 1 -- COMMIT TRAN Go -- Create operations, timing of the implementation of storage data synchronization process If exists (SELECT 1 from msdb .. sysjobs where name = 'data processing') EXECUTE msdb.dbo.sp_delete_job @ job_name = 'data processing' Exec msdb .. sp_add_job @ job_name = 'data processing' -- Create operating steps Declare @ sql varchar (800), @ dbname varchar (250) Select @ sql = 'exec p_synchro' - data processing orders , @ Dbname = db_name () - the implementation of a data-processing database Exec msdb .. sp_add_jobstep @ job_name = 'data processing' @ Step_name = 'data synchronization' @ Subsystem = 'TSQL' @ @ Dbname = database_name, Sql = @ @ command, @ Retry_attempts = 5, - retry @ Retry_interval = 5 - retry interval -- Create scheduling EXEC msdb .. sp_add_jobschedule @ job_name = 'data processing' @ Name = 'timing' @ Freq_type = 4, - every day @ Freq_interval = 1, - a daily basis, @ Active_start_time = 00000 - 0点implementation Go Choi back in the black :2004-07-08 17:58:35 If it is a large number of data tables? According to this way is not to use analyzer written statement to implement data synchronization ah, because I am not well aware of the database may not understand all of a sudden you say, I am still learning, so if misunderstood local please understanding. : D Comment Sqlrpgle four in the use of quotation marks Through a cursor fetch cardstr / free Cardstr = select cdno from dtalib / kwj where + Substr (cdno, 9,1) = ; Cardstr =% trimr (cardstr) ++ v + @ type; Cardstr =% trimr (cardstr) + and; Cardstr =% tri.. AS/400 ODBC how monitoring data? ） QIBM_QTF_TRANSFER RPG IV how to embed SQL statements? SQL is normative, maintainability, readability, and so> If efficiency can be tolerated, in order to improve the maintainability of the procedures proposed to RPGIV embedded SQL data processing. Wo.. Common AIX Forum address, data download address, FTP Address Redbook Chapter 5 - storage management 6. Storage management, logical volume management, file system, a. Logical volume management concept Five concepts: the physical volumes (physical volumes) - Volume Group (VG) - Physics Division (PPar.. Emergency calls, AIX, CICS, recycling LINK transactions, CICS APP SERVER will abnormal ter.. Familiar with AIX, CICS friends please help: An implementation cycle needs of the thousands of transactions transactions, the total in section 481 cycles LINK transactions suggest that abnormal termi.. Home|Site Map www.obicn.com