A method for generating data loading operation

Abstract

The present application discloses a method for generating a data loading job. The method includes: obtaining job parameters; the job parameters include: indicative information of a source data set file, identification information of a target database, and a job output address; accessing a target database based on the identification information and scanning a list of source data set files based on the indicative information; analyzing the target database and the source data set file, and when it is determined that the target database contains a data table that matches the source data set file, generating a data loading job using a job template, and outputting the data loading job to the job output address. The method can quickly generate a large number of high-quality and stable data loading jobs in an automated manner, improve the generation efficiency of data loading jobs, reach a generation speed of minutes, and reduce the error rate.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method for generating a data loading operation. Background Art

[0002] The mainframe is the top product of IBM computers. Due to its high security and high availability, it has been widely used in the financial, securities and insurance industries. DB2 is a relational database management system developed by IBM in the United States. A database is a collection of large amounts of data that is stored in a computer for a long time, organized, shareable and uniformly managed. The mainframe DB2 database is the core database system of many banks, and a lot of data will be backed up during daily operation and maintenance. These backup data can be used to help restore production when the DB2 database of the production system has problems and needs to be restored. Production accidents are usually low-probability events, and more often, these data can be used to load data on the DB2 database of the development and testing and disaster recovery systems to meet various usage requirements.

[0003] A job is a unit of work that a computer is required to complete. Data loading jobs are generally used to load data into a host DB2. Data loading jobs for a host DB2 database can be executed on the host, and are generally used to restore data on the disk to the database. The generation of a data loading job is a prerequisite for its execution. The generation of the data loading job is generally done manually by checking the name of the data set file on the disk, and manually editing or modifying the parameters and commands into the job control language (JCL) text. A small number of data loading jobs are generally generated by manual editing, but when the number of data set files in the host DB2 database is relatively large (can reach tens of thousands or hundreds of thousands), manually editing so much file information into the data loading job requires a lot of manual editing. From the actual work effect, it takes a person several days, which is inefficient and prone to errors. Summary of the invention

[0004] Based on the above problems, the present application provides a method for generating a data loading job, which generates a data loading job in an automated manner, improves the generation efficiency of the data loading job, and reduces the error rate.

[0005] The embodiments of the present application disclose the following technical solutions:

[0006] In a first aspect, the present application provides a method for generating a data loading job, comprising:

[0007] Obtaining job parameters; the job parameters include: indicative information of the source data set file, identification information of the target database and the job output address;

[0008] Accessing the target database according to the identification information and scanning a source data set file list according to the indicative information;

[0009] The target database and the source data set file are analyzed, and when it is determined that the target database contains a data table matching the source data set file, a data loading job is generated using a job template, and the data loading job is output to the job output address.

[0010] Optionally, the indicative information includes: range indicative information and date indicative information.

[0011] Optionally, the above method further comprises: when it is determined that the target database does not contain a data table matching the source data set file, outputting risk warning information and outputting a list of the source data set files.

[0012] Optionally, when it is determined that the target database contains a data table matching the source data set file, the method further includes:

[0013] Generate or obtain the necessary parameters for running a job;

[0014] The step of generating a data loading job using a job template includes:

[0015] The necessary parameters are transmitted to the subprogram where the job template is located, and the necessary parameters are added as variables to the required positions in the job template through the subprogram.

[0016] Optionally, the operation parameters further include: decision information;

[0017] The generating the data loading job by using the job template includes: when it is determined that the target database contains a data table matching the source data set file and the decision information indicates that the data loading job needs to be generated, generating the data loading job by using the job template.

[0018] Optionally, the analyzing the target database and the source data set file includes:

[0019] Determine key testing indicators;

[0020] Compare the key detection indicators of the source data set file and the key detection indicators of the data table in the target database for consistency. When the key detection indicators of the source data set file and the key detection indicators of the data table in the target database match, determine that the target database contains a data table that matches the source data set file.

[0021] Optionally, the key detection indicator includes at least one of the following:

[0022] Table name, partition information, table structure, column information or column length; the partition information includes whether it is partitioned and the number of partitions.

[0023] Optionally, the above method further includes:

[0024] Add or modify job templates.

[0025] Optionally, the above method further includes:

[0026] The job template is selected from a set of job templates.

[0027] Optionally, the job template set includes a data installation template, an index creation template and a statistical template.

[0028] Compared with the prior art, this application has the following beneficial effects:

[0029] The method for generating a data loading job provided by the present application includes: obtaining job parameters; the job parameters include: indicative information of the source data set file, identification information of the target database and the job output address; accessing the target database according to the identification information and scanning out a list of source data set files according to the indicative information; analyzing the target database and the source data set file, and when it is determined that the target database contains a data table that matches the source data set file, generating a data loading job using a job template, and outputting the data loading job to the job output address. This method can quickly generate a large number of high-quality and stable data loading jobs in an automated manner, improve the generation efficiency of data loading jobs, reach a generation speed of minutes, and reduce the error rate. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0031] Figure 1 A flowchart of a method for generating a data loading job provided in an embodiment of the present application. DETAILED DESCRIPTION

[0032] As described above, the current operation of generating data loading jobs is usually implemented by manpower. The prior art is to view the data files on the disk through the host command list, and manually edit or modify the loading job text to achieve, such as keyword search, replacement, etc. Manual editing has similar problems such as high cost, low efficiency, poor stability, and easy errors. Taking 50,000 data files as an example, if they are manually consulted and edited, it takes about 3 seconds to search, copy and paste, and edit a single data file into the loading job. Repeating the operation of 50,000 files takes about 41 hours, about 5 working days, and 5 manpower. From the perspective of actual work, a large amount of repetitive and monotonous manual editing can easily make people tired, and human errors such as searching, copying and pasting will occur. In contrast, computer programs are more suitable for this kind of work.

[0033] Based on this problem, this application provides a method for generating a data loading job, which uses a programmed and standardized method to quickly generate a host DB2 database data loading job. The operation is generally performed in minutes and the time is short. Due to the existence of the job template, the output quality of the data loading job in this application is high and the stability is good. The problem of low efficiency and easy error in job generation is solved.

[0034] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0035] See also Figure 1 , which is a flow chart of a method for generating a data loading job provided by an embodiment of the present application. Figure 1 As shown, the method for generating a data loading job includes:

[0036] S101: Obtaining operation parameters.

[0037] In the embodiment of the present application, a job can be understood as a combination of a command and a parameter. The job parameters obtained in this step include: indicative information of the source data set file, identification information of the target database, and the job output address.

[0038] In an embodiment of the present application, in order to generate a data loading job, a key step is to compare and analyze the target database and the source data set file. Of the two parts of the comparison and analysis, one is the target database (i.e., the target DB2 database), and the other is the source data set file. Among them, the indicative information of the source data set file may include two parts, one of which is the range indicative information, and the other is the date indicative information. The range indicative information and the date indicative information are combined as double filtering conditions for the source data set file. The identification information of the target database is used to uniquely determine the host DB2 database targeted by the technical solution of the present application. According to the indicative information of the source data set file, a list of source data set files can be obtained, which contains at least one source data set file; and according to the identification information of the target database, the target database can be locked.

[0039] The job output address can also be called the job output directory, which can be the address of a folder. After generating a data loading job according to the data loading job generation method provided in the embodiment of the present application, the generated data loading job can be output to the job output directory provided in the job parameters for easy query, management and application.

[0040] S102: Access the target database according to the identification information and scan out a source data set file list according to the indicative information.

[0041] The identification information has a one-to-one correspondence with the database, so the target database can be uniquely determined according to the identification information in the S101 job parameter. This representation can be used as a basis to access the target database. For example, by querying a table in the target database (hereinafter referred to as the target table) using the Structured Query Language (SQL), all information of the target table can be obtained, such as the table name, column information, table partitions, etc.

[0042] Since the indicative information contained in the job parameters indicates the date and range of the source dataset files, a list of source dataset files of a specified date and a specified range can be obtained according to the indicative information. For example, the file size, file sequence, file partition information, and the name of the table in the target database to which the file refers can be obtained through a stable reading and writing method.

[0043] It should be noted that the operations of accessing the target database and scanning the source dataset file list can be performed simultaneously, or the target database can be accessed first and then the source dataset file list can be scanned, or the source dataset file list can be scanned first and then the target database can be accessed. There is no restriction on the execution order.

[0044] S103: Analyze the target database and the source data set file, and when it is determined that the target database contains a data table matching the source data set file, generate a data loading job using a job template, and output the data loading job to a job output address.

[0045] Here, the source data set files to be analyzed refer to the source data set files in the source data set file list scanned in S102 .

[0046] Here, data table matching does not mean that all information between data tables is completely consistent. For example, when some key detection indicators of the target table in the target database match those of the data table in the source data set file, the data tables can also be considered to match each other.

[0047] Determine that the key detection indicators include at least one of the following:

[0048] Table name, partition information, table structure, column information or column length; partition information includes whether it is partitioned and the number of partitions.

[0049] As a possible implementation method, a key detection indicator set can be pre-established according to actual detection requirements. In specific implementation, the key detection indicators of the source data set file in the key detection indicator set are compared with the key detection indicators of the data table in the target database in the key detection indicator set. When all the key detection indicators of the two parts of the table in the key detection indicator set match, it is determined that the target database contains a data table that matches the source data set file.

[0050] When it is determined that the target database contains a data table that matches the source data set file, the necessary parameters for the job operation can be generated or obtained according to the job parameters. In addition, some necessary parameters for the job operation can also be pre-set common parameters.

[0051] Common parameters only need to be specified once to be applicable to all templates in this scenario, so they can be used as manual input parameters of the solution to keep them stable. For example, the identification information of the target database. These parameters need to be specified manually by the user.

[0052] Generating or obtaining the necessary parameters for running a job based on job parameters is related to a specific job or a certain type of job. Its characteristics are that it can directly access and obtain the parameters in the system or give the evaluation value based on system information. For example, the host data set name list and related information (such as size, etc.), database table list, program running parameters in the job, etc., can be obtained by accessing the database, accessing the source data set information, and establishing a parameter evaluation allocation method.

[0053] In order to generate a job, you also need to obtain a job template and then fill in the necessary parameters for the job into the positions in the job template. In specific implementation, the necessary parameters can be transmitted to the subroutine where the job template is located, and the subroutine adds the necessary parameters as variables to the required positions in the job template.

[0054] In actual applications, a job template set can be pre-established, and the set includes multiple alternative job templates. For example, a data template, an index template, and a statistical template. The job template is selected from the job template set. Different job templates may require the same or different necessary parameters. Therefore, the necessary parameters can also be set, generated, or obtained in combination with the specific job template to be used. According to actual needs, the original templates in the job template set can also be modified and adjusted. In addition, new job templates can be added to the job template set according to the needs of actual applications.

[0055] It can be seen that in the technical solution of the present application, new requirements can be easily and flexibly adapted by adding or modifying job templates.

[0056] The above is the method for generating a data loading job provided by the present application, including: the indicative information of the source data set file, the identification information of the target database and the job output address; accessing the target database based on the identification information and scanning out the source data set file list based on the indicative information; analyzing the target database and the source data set file, and when it is determined that the target database contains a data table that matches the source data set file, using the job template to generate a data loading job, and outputting the data loading job to the job output address. This method can quickly generate a large number of high-quality and stable data loading jobs in an automated manner, improve the generation efficiency of data loading jobs, reach a generation speed of minutes, and reduce the error rate.

[0057] In the above embodiment, S103 only describes the operation performed when the target database contains a data table that matches the source data set file. In addition, the method for generating a data loading job provided in the embodiment of the present application also includes:

[0058] When it is determined that the target database does not contain a data table that matches the source data set file, a risk warning message is output and a list of source data set files is output, thereby prompting the user to generate a data loading job and to make additional definitions.

[0059] In the above embodiment, after analyzing the scanned source data set file list, the scanned source data set files may be classified according to the following standards:

[0060] Category 1: There is a matching data table in the target database, which is not partitioned. For this type of source data set file, fill the non-partitioned source data set and environment configuration information into the corresponding parameter position in the corresponding job template directly or after conversion (for example, according to the file size, mathematical calculation or set relationship mapping to obtain the necessary parameters), and a complete data loading job can be obtained. Repeat this step until all data loading jobs of this type are obtained.

[0061] The second category: There are matching data tables and partitions in the target database. For this type of source data set file, directly or through conversion (for example, according to the file size, mathematical calculations or set relationship mapping to obtain the necessary parameters) fill in the corresponding parameter positions in the corresponding job template to obtain a complete data loading job. Repeat this step until all data loading jobs of this type are obtained.

[0062] Category 3: There is no matching data table in the target database, whether it is partitioned or not. For this type of source data set file, in this case, considering that the generated loading job will report an error when it is running because it cannot find the table information, it is necessary to output risk information to prompt the user and list the relevant source data set files for the user's reference. If the user adds additional definitions, refer to the processing of the first and second categories.

[0063] Through the above method, a large number of high-quality and stable data loading operations can be generated. The above method can be implemented by programming (including but not limited to C language) and compiled into a module and placed on the required application system for rapid execution.

[0064] In the actual situation of this scenario, you can also consider integrity checking to reduce the risk of subsequent failure of the generated data loading job. The purpose of integrity checking is to reduce the risk of job failure, omission and duplication. Failure here refers to the failure of a single job itself. Omission means that at least one job is omitted, which leads to potential inconsistency in usage. Duplication means that two jobs perform the same function, resulting in potential job conflicts and waste. Specifically for the data loading job of the target database, the following issues can be addressed:

[0065] First, are the parameters correct? This problem can be verified by a specific method. For example, if the database ID "D1" cannot be accessed in the system, it is considered that there is a problem with the parameters, and the job is not allowed to be generated or run.

[0066] Second, whether the assignment is missing. Missing means whether the table corresponding to each data set exists in the database, and whether each table in the database has a corresponding data set. If one of the two conditions is not met, it is considered to be missing.

[0067] Third, the source data set is duplicated. If there are more than two data set files corresponding to the same database table, it is considered a problem and needs to be confirmed and corrected, and one of them must be removed.

[0068] Once these problems are solved, most of the potential risks in daily manual operations can be avoided. It should be noted that these tests are often carried out together with the acquisition process and are tested immediately after acquisition.

[0069] In summary, the method for generating a data loading job provided in the embodiment of the present application provides the following innovations compared to the prior art:

[0070] 1. The process and results of the analysis of the documents to form standardized parameters suitable for loading operations;

[0071] 2. The process and results of normalizing the parameters of the query information results in the database;

[0072] 3. Standardized classification of the collection of job templates;

[0073] 4. Standardized classification and conversion of job template parameters;

[0074] 5. Rules, methods and output tools for programming and outputting loading operations.

[0075] To facilitate understanding of the technical solution provided in the embodiments of the present application, an example is provided below.

[0076] 1. Read all job parameters.

[0077] 2. According to the range indicative information LEVEL = DPBBCATG.PQ2F, scan out the list (list) of source data set files, for example:

[0078] DPBBCATG.PQ2F.TESTDB1.TESTTS1.D2011031.T25

[0079] DPBBCATG.PQ2F.TESTDB1.TESTTS1.D2012014.T23

[0080] DPBBCATG.PQ2F.TESTDB1.TESTTS1.D2013073.T19

[0081] DPBBCATG.PQ2F.TESTDB2.TESTTS2.D2013073.T14

[0082] DPBBCATG.PQ2F.TESTDB3.TESTTS3.D2013076.T11

[0083] Among them, D2011031, D2012014, D2013073 and D2013076 are different date indicative information; TESTDB1 is the target database subsystem name; TESTTS1 is a table space in TESTDB1.

[0084] 3. According to the date indicative information DTL=D2013073, the result in the above step 2 is filtered by date to obtain the following source data set file and related information, such as file size.

[0085] DPBBCATG.PQ2F.TESTDB1.TESTTS1.D2013073.T19

[0086] DPBBCATG.PQ2F.TESTDB2.TESTTS2.D2013073.T14

[0087] 4. According to the indicative information SYSID=DC10 of the target database, access the target database DC10 and obtain the structural information of all tables in the database:

[0088] TESTDB1.TESTTS1,(COL1,COL2,COL3)

[0089] TESTDB2.TESTTS2, (COL1,COL2,COL3,COL4)

[0090] TESTDB3.TESTTS3, (COL1, COL2, COL3, COL4, COL5)

[0091] The information in the above table can be obtained by comparing and analyzing with that in 3:

[0092] TESTDB1.TESTTS1 matches:

[0093] DPBBCATG.PQ2F.TESTDB1.TESTTS1.D2013073.T19

[0094] TESTDB2.TESTTS2 matches:

[0095] DPBBCATG.PQ2F.TESTDB2.TESTTS2.D2013073.T14

[0096] 5. Take SYSID=DC10 and the analysis result of step 4 as the necessary parameters for the operation, and fill in the required position in the data loading template. The content in the job template is used to generate a data loading job (the data loading job generated by this template is referred to as the data loading job).

[0097] 6. According to SYSID = DC10, combined with the result information of 3 and 4, the sort concurrency number 24 is calculated and evaluated as the necessary parameter for the job operation. The position required by the index template can be filled. The content in the job template is used to generate a data loading job (the data loading job generated by this template is referred to as the indexing job).

[0098] 7. The job parameters may also include: decision information NEEDRUNS, which is used to decide whether to use the statistical template to generate a data loading job. Therefore, when it is determined that the target database contains a data table that matches the source data set file, and the decision information indicates that a data loading job needs to be generated, the job template is used to generate a data loading job.

[0099] NEEDRUNS=Y, indicating that the statistical template is needed to update the relevant information to the target database. According to the result information of 3 and 4, the statistical template content can be filled to obtain the data loading operation (the data loading operation generated by this template is referred to as the statistical operation).

[0100] 8. According to the job output address OUTPDS = CMABC.RIVAROLA.REDOVER.JCL, if the directory CMABC.RIVAROLA.REDOVER.JCL exists, each job generated in 5-7 will be output and saved in the directory. If it does not exist, create the directory and output the data loading job.

[0101] In the above example, the decision information is only associated with the use of the statistical template, and the use of the data loading template and the indexing template is not related to the decision information. The generation of the data loading job is completed through the data loading template, the indexing template and the statistical template (the three have a dependency relationship).

[0102] In combination with the above examples, in the technical solution of this application, by executing the above operations, the matching judgment operation between the target database table and the source data set file can be completed in batches, and then the data loading job can be generated in batches, replacing the manual operation, reducing the error rate and improving stability.

[0103] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed in the present application should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

Claims

1. A method for generating a data loading operation, characterized in that: include: Get job parameters; The job parameters include: indicative information of the source data set file, identification information of the target database and the job output address; the indicative information includes: range indicative information and date indicative information; Accessing the target database according to the identification information and scanning a source data set file list according to the indicative information; The target database and the source data set file are analyzed. When it is determined that the target database contains a data table that matches the source data set file, a data loading job is generated using a job template, and the data loading job is output to the job output address; when it is determined that the target database does not contain a data table that matches the source data set file, risk warning information is output and a list of the source data set files is output.

2. The method according to claim 1, characterized in that When it is determined that the target database contains a data table matching the source data set file, the method further includes: Generate or obtain the necessary parameters for running a job; The step of generating a data loading job using a job template includes: The necessary parameters are transmitted to the subprogram where the job template is located, and the necessary parameters are added as variables to the required positions in the job template through the subprogram.

3. The method according to claim 1, characterized in that: The operation parameters also include: decision information; The generating the data loading job by using the job template includes: when it is determined that the target database contains a data table matching the source data set file and the decision information indicates that the data loading job needs to be generated, generating the data loading job by using the job template.

4. The method according to claim 1, characterized in that: The analyzing the target database and the source data set file includes: Determine key testing indicators; Compare the key detection indicators of the source data set file and the key detection indicators of the data table in the target database for consistency. When the key detection indicators of the source data set file and the key detection indicators of the data table in the target database match, determine that the target database contains a data table that matches the source data set file.

5. The method according to claim 4, characterized in that The key detection indicators include at least one of the following: Table name, partition information, table structure, column information or column length; the partition information includes whether it is partitioned and the number of partitions.

6. The method according to claim 1, characterized in that Also includes: Add or modify job templates.

7. The method according to claim 1, characterized in that Also includes: The job template is selected from a set of job templates.

8. The method according to claim 7, characterized in that The job template set includes a data installation template, an index creation template and a statistical template.

Images