Generate scripts and import policies for large data volumes

During the performance test of generating scripts for large data volumes and importing policies, if you need to construct a table with large data volumes, note the following: how to generate an unl file (this file is suffixed. unl data file, in the format of data records separated by table columns, for example: 10001 | 33333 |). Can the generated unl file be imported to the corresponding database table at one time. For ease of description, assume that the database has a table test (id integer, name integer, primary key (id, name )). If you want to import million records to this table, manual insert is unrealistic. Generally, the database system provides specific commands to import and export data. We can create unl files in the corresponding format in advance, then, call the Database Import command to quickly import data. Taking the Informix database as an example, the data import command is: load from testdata. unl insert into test; now, the problem is how to generate the unl file of 10 million rows. I learned how to use shell scripts quickly and thought about awk implementation. Here we also want to use awk implementation: [plain] <span style = ""> #! /Bin/sh awk 'in in {dbfile = sprintf ("testdata. unl "); for (I = 0; I <1000; I ++) {for (j = 0; j <10000; j ++) {printf "100% 05d | 9% 08d | \ n", I, j> dbfile} printf "% d completed. \ n ", I} '/dev/null </span>, an unl file with records is generated, so we are eager to use" load from testdata. unl insert into test; "command to import data. An error is returned. Because too much data is imported at one time, the cache of the database system and the space of the log system are insufficient, too many records cannot be recorded at one time (or because the operation time is too long, "Long transaction aborted "). There are two solutions: Increase the cache and log system size, and reduce the data volume of a single unl. Obviously, the latter is more reasonable. If you divide 10 million of unl into 100 small portions, each of which contains 10 million pieces, you can process more. The script is slightly modified as follows: [plain] <span style = ""> #! /Bin/sh awk 'in in {for (I = 0; I <100; I ++) {dbfile = sprintf ("mytable % 02d. unl ", I); for (j = 0; j <100000; j ++) {printf" 100% 05d | 9% 08d | \ n ", I, j> dbfile} printf "% d completed. \ n ", I }}'/dev/null </span> in this case, 100 operations are required for one hundred files, which is a little big. In line with the principle of laziness, we naturally want to use it automatically. As a result, the shell script automatically imported to the test table is also released: [plain] <span style = ""> #! /Bin/sh # generate 100 SQL scripts awk 'in in {dbfile = sprintf ("loadData. SQL "); for (I = 0; I <100; I ++) {printf" load from mytable % 02d. unl insert into test; ", I> dbfile} printf" completed. "} '/dev/null # Run the script dbaccess mydatabank loadData. SQL </span> of course, if you want to be more "lazy", you can put these shells together, but pay attention to readability. We recommend that you create a new shell to call these two shells in sequence, convenience for subsequent maintenance.