Data Interaction between two projects based on rich experience in blog posts-Import

I originally wanted to write the background of this software in this log so that readers can have a clearer understanding of this demand,

I thought about it carefully to avoid unnecessary troubles, so I should write this log from a functional point of view.

(Note: all the content in this article will be blurred)

 

Two different databases have different data structures.

Now let me simply call them

System A and system B

Without changing systemCodeTo achieve data interaction between the two projects.

 

To get data from A to B and write data from B to the system flow of A, you only need to have a good understanding of the data structures of these two systems.

The data structure of system A is unknown. It is developed by other software companies.

System A: DB2 database, with more than a thousand data tables, with a data volume of about millions

System B is developed by us for sqlserver Database

More than 200 data tables with a data volume of about 100,000 rows

(Here I will briefly describe the data concepts of the two systems)

 

 

Except that a provides the. PDM file, there is no other instruction document about system.

The following figure shows the PDM: powerdesigner file of the conceptual data model.

Multiple people use powerdesigner to draw ergraphs and design database concepts.

So with this document, I began to analyze the data structure of database A and the relationship between tables.

Entity, attribute, identifier, and constraint.

This model is also written in detail and can be clearly described in the field description.

 

After repeated tests on system

(For example, if a purchase command is published in system A, which tables have additional data in the background database, and what is the meaning of each word read)

I have compiled every functional module and Process status into a document description.

Therefore, we have a clear concept in our mind.

 

 

CompletedAAfter a preliminary understanding of the system, we will consider howBSystem connection.

The following figure shows the connection process.

Step 1

1. Create a database identical to the DB2 data structure of system A in sqlserver. I name it system A (SQL)

2. Add the unchanged data (such as basic data) to the database of system A (SQL ).

Step 2

1. Create a DB2 connection and configure the ODBC Data Source

2. Since the data structure of system A (SQL) is the same as that of system A (DB2), it is easy to passProgramming LanguageSend the latest data of the expected table to system A (SQL)

For example, if the original DB2 has thousands of tables, only 20 or more tables are actually needed. In this step, you only need to export the data in more than 20 tables.

Step 3

1. At this time, the data in system A (SQL) is basically what we want, but the data structure is different from that in system B (SQL) the relevant fields in the table are extracted and integrated into the same table structure in system B (SQL, this step may cause many details. In this case, you can carefully refer to the field meanings of each data table in the PDM file and test the system to make the obtained data accurate.

For example, system B's procurement module consists of a single table, and system A's procurement module consists of multiple tables. In this case, we should filter out useless fields and synthesize a table, on the contrary, if system B has multiple tables while system A has a single table, the obtained data is split into multiple tables and inserted into system B separately.

In fact, these processes are not difficult to deal with in the stored procedure. They only need to be careful, rigorous, and repeated query insertion tests.

2. When the obtained data is sorted out, a conversion process is required to convert the data number of system A (SQL) to the one that can be identified by system B (SQL ).

For example, the name of "syy0000001" in system A Is badminton.

 The badminton serial number of system B is 1101 and the name is double star badminton.

 In this case, you must convert syy0000001 to 1101.

Convert badminton to double star Badminton

Create a data conversion table in system B (SQL) to convert all fields of system A (SQL) to system B (SQL) through this table ).

Product Type Code

Check type Encoding

Customer type code

 

Note: virtual data is used to make it easier for readers to read blog posts.

According to this conversion table, I have implemented a data interface maintenance function in the project, so there is a benefit that even if a system has a new code, use the interface maintenance function to add a new piece of data to the system conversion table and fill in the encoding in system B corresponding to the new encoding in system A to ensure that the new encoding imported later can be converted to the identifiable encoding.

 

At this time, the processed data, regardless of the data structure or even the data content, is completely and completely converted into data in system B (SQL ).

Finally, the data is directly inserted into system B (SQL) to implement data import from system A (DB2 ).

In order to make it easy for others to read and understand, I wrote a stored procedure specification.

 

 

 

The system administrator clicks the data import button to perform the second step.

Import the latest data to the intermediate buffer database.ASystem(SQL)

Distribute the third step to each department user according to the system process

For example, the Sourcing Department It only triggers the import of procurement data into the stored procedure and imports procurement-related data.

Assembly department It only triggers the import of Assembly data into the stored procedure and imports assembly-related data.

In this way, no irrelevant code is repeatedly executed, and data import between departments does not conflict.

 

Now, the Data Interaction import module between two projects is basically written here.

The entire blog does not need to be fully understood by the reader. As long as you have a clear idea, the key lies in that flowchart.

Some places are detailed, some places are included in the document, hoping to help people who have read it.

Next, the entire process is reversed to implement the data export function. ( To be continued... )