Solution to the Problem of failure to import DB2 interface files to Oracle

When an interface file is sent from a DB2 host to an Oracle host, the data in the interface file is always missing. As a result, the CRM system cannot see the corresponding marketing activities. So you said I didn't pass the interface text.

When an interface file is sent from a DB2 host to an Oracle host, the data in the interface file is always missing. As a result, the CRM system cannot see the corresponding marketing activities. So you said I didn't pass the interface text.

A problem occurred on the contact marketing platform a few days ago. Ask a colleague to help solve the problem. The result is two days later and the problem is still not located.
We still need to solve the problem near the Spring Festival. We have been busy this morning and finally solved this problem.
When an interface file is sent from a DB2 host to an Oracle host, the data in the interface file is always missing. As a result, the CRM system cannot see the corresponding marketing activities.
So you said that I didn't upload the interface file. I said that you couldn't process the interface file. In fact, it is very easy to process. The key is to pull words, check whether the interface file is on the server and consistent with the databases on both ends.
It is hard to clarify the interface file problem, but it is also related to the interface file content. Some of the file content on the Interface machine is garbled and there is a line break problem with carriage return.
A new round of tests has been started. In fact, both ends should test whether the interface files can be normally stored in the database. The Export and Import of test data on DB2 are normal, what are the garbled characters on the Interface host? It is estimated that several lines of bytes are too long to be fully displayed. Therefore, the system splits some Chinese characters, leading to garbled content.
The test on DB2 is very simple. Just export data, load data, and display data.
Export to u:/IW3001test0001. AVL of del modified by coldel0x01 nochardel striplzeros decplusblank
Select * from hnwangbq. yingxiaoan;
Create table hnwangbq. test
(
Sale_act_id varchar (20 ),
Sale_act_name varchar (50 ),
Act_begin_date varchar (20 ),
Act_end_date varchar (20 ),
Data_time varchar (20 ),
Sales_act_script varchar (500 ),
Sms_script varchar (500 ),
Sale_act_type varchar (20)
);
Import from u:/IW3001test0001. AVL of del modified by coldel0x01 nochardel decplusblank
Insert into hnwangbq. test;
Select * from hnwangbq. test;
Everything processed on DB2 is normal. Next we will test the import on Oracle.
You have to reinstall Oracle and PL/SQL Developer to create a new table.
Create table hnwangbq. test
(
Sale_act_id varchar2 (20 ),
Sale_act_name varchar2 (50 ),
Act_begin_date varchar2 (20 ),
Act_end_date varchar2 (20 ),
Data_time varchar2 (20 ),
Sale_act_script varchar2 (500 ),
Sms_script varchar2 (500 ),
Sale_act_type varchar2 (20)
);
The Text importer of PL/SQL Developer is used for the import test. The error is that the field row is too short. Next, check whether Oracle has merged several records into one record.
Why is this happening? We found that "Double quotation marks exist in the row. Oracle regards" Double quotation marks as the Quote character of the column, so the content of the two "Double quotation marks is used as a field.
So the replace function is used to change "Double quotation marks to" single quotation marks "and test again.
However, the test in the production environment encountered a problem or an error. After reading the shell script, we found that Oracle was loading interface files through SQLLDR. I also tested it through SQLLDR, the control file is as follows:
LOAD DATA
INFILE 'd: \ IW3001test0001. AVL'
Replace into table test
Fields terminated by '01' optionally enclosed '"'
Trailing nullcols
(SALE_ACT_ID,
SALE_ACT_NAME,
ACT_BEGIN_DATE,
ACT_END_DATE,
DATA_TIME,
SALE_ACT_SCRIPT,
SMS_SCRIPT,
SALE_ACT_TYPE
)
An error is also reported. The cause of the error is in the loading log. The error message is as follows:
Record 1: rejected-The SALE_ACT_SCRIPT column in the TEST table has an error.
The maximum length of the field in the data file is exceeded.
Record 3: rejected-an error occurs in the SMS_SCRIPT column of the TEST table.
The maximum length of the field in the data file is exceeded.
Record 8: rejected-the table TEST column SMS_SCRIPT has an error.
The maximum length of the field in the data file is exceeded.
Record 11: rejected-an error occurs in the SMS_SCRIPT column of the TEST table.
...
Record 42: rejected-an error occurs in the SMS_SCRIPT column of the TEST table.
The maximum length of the field in the data file is exceeded.
Table TEST:
27 rows are loaded successfully.
15 rows are not loaded due to data errors.
Because all the WHEN clauses fail, the 0 rows are not loaded.
Because all fields are empty, 0 rows are not loaded.
Baidu searched and found that other people had the same problem. The cause of the problem was that Oracle's SQLLDR processed the string by default as CHAR (255 ), the columns of some fields obviously exceed 255 bytes, So I modified the control file.
LOAD DATA
INFILE 'd: \ IW3001test0001. AVL'
Replace into table test
Fields terminated by '01' optionally enclosed '"'
Trailing nullcols
(SALE_ACT_ID,
...
SALE_ACT_SCRIPT char (2000 ),
SMS_SCRIPT char (2000 ),
In fact, there are two solutions: one is to control the data source side, and the other is to continuously test and improve the load side.

There is no way to solve the problem. It is nothing more than testing and testing. Sometimes it seems that one problem has been solved, and another problem has come out again. The root cause of the problem lies in constant exploration.
In fact, exploration is also a pleasure.