Note: Descriptions are shown in the official language in which they were submitted.
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
1
1 A System and Method for Coming Data
2
3 Introduction
4
The present invention relates to a system and method for copying data
6 between environments in an information management system and in
7 particular to the copying of business data between production and test
8 environments in a database system.
9
Background to the Invention
11
12 Database systems are used in a wide range of enterprises for the storage
and
13 manipulation of data. The data could, for example, relate to a customer's
14 personal information and information on the products and services that they
have purchased. Relational database systems such as IBM DB2 UDB are
16 used to store this type of data.
17
18 Database systems depend on stored metadata for the normal operation of a
19 database instance. A database system uses this information when serving
user requests, in the form of either DML (data manipulation language)
21 statements or calls from database utilities. In DB2 UDB, this metadata
22 information is stored as database tables and views called system catalog
23 views. Application developers can use metadata to manipulate a database
24 object when developing applications. The DB2 database manager uses
system catalog views and database instances while serving requests from
26 users. Data management is provided using a structured query language SQL;
27 this provides data query and update, schema creation and modification, and
28 data access control.
29
Initially, relational database systems operated with embedded or static SQL.
31 Static SQL statements are written within an application program and are pre-
32 processed by a database dependent SQL preprocessor before the application
33 is compiled. In the preprocessing stage, a DB2 package is created which is
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
2
1 bound outside the compile process. The bind process determined the access
2 path.
3
4 Open Database Connectivity ODBC facilitated the creation of dynamic SQL
because it allowed SQL to be created that did not have to be embedded into
6 the application programming language, and pre-compilation was no longer
7 required.
8
9 Therefore static SQL statements in an application do not change at runtime
and can be hard-coded into the application. Dynamic SQL statements are
11 constructed at runtime to allow users to enter their own queries. Thus, the
12 dynamic SQL statements cannot be hard-coded into the application. Static
13 SQL provides performance advantages over dynamic SQL because static
14 SQL is pre-processed, which means the statements are parsed, validated,
and optimized only once.
16
17 In many applications, database systems contain millions of data tables and
18 there is an ongoing need to be able to create queries which copy data from
19 one environment to another. This can be as a result of the need to test an
improvement to the database or a new product before live use.
21
22 Testing requires data created within a test environment. This is usually
data
23 that is extracted from an alternative environment and loaded into the test
24 environment which is often a production environment. In many cases,
because of the complex inter-relationships between data in the database, the
26 tester may not know exactly which data is required. The data chosen by the
27 tester may have underlying data requirements that are not apparent Where a
28 test is performed on incomplete data an error will occur. The problem with
29 errors is that it is not immediately apparent to the tester whether the
error has
arisen from a failure to properly specify the required data or whether there
is a
31 more fundamental problem with the test itself.
32
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
3
1 Another issue is that of CPU capacity. In many cases, organisations will buy
2 database services from a supplier; this will include the purchase of
processing
3 capacity. The ability to copy data from one environment to another requires
a
4 user to create their own database queries which have a large draw on the
available processing capacity. In practice, this problem is controlled by
6 carefully managing the queries that are created by users and running the
7 queries at times when the system processing capacity is underused such as
8 in the evening or at weekends. This is inconvenient for the users and slows
9 down the development process considerably. Product service
11 Summary of the Invention
12
13 It is an object of the present invention to provide an improved system and
14 method for copying data.
16 In accordance with a first aspect of the invention there is provided a
system
17 for copying data from a first to a second environment in an information
18 management system, the system comprising:
19 a relations database comprising a set of relations derived from a first
database, said relations providing a link between database tables in the first
21 database, wherein the relations database is created outside the runtime
22 environment;
23 a service generator which extracts and copies database tables at runtime to
24 form a predetermined service or product, wherein
the service generator, upon receiving a request to copy a product or service,
26 does so only if the data in the database tables comprising the product or
27 service is the subject of one or more relation as contained within the
relations
28 database.
29
Preferably. the data in the database tables comprising the product or service
31 is the subject of one or more relation as contained within the relations
SQL
32 commands.
33
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
4
1 Preferably, one or more type of relation is present in the relations
database.
2
3 Preferably, a first type of relation is the presence of an index for
preselected
4 data in the database table.
6 Preferably, the relation is a referential integrity relation.
7
8 Preferably, where database tables are related as referential integrity
parent
9 and child, data from both tables will be extracted.
11 Preferably, the relation is an application relation.
12
13 Preferably, the system further comprises input means to allow a user to
create
14 application relations between data tables in the first database and to
allow the
data tables to be included in the relations database.
16
17 By using static SQL instead of dynamic SQL less CPU capacity is required to
18 create copies. This means that copies can be created at any time for
testing.
19
Preferably, the service generator comprises encryption means for encrypting
21 and decrypting data in database tables.
22
23 Preferably, the encryption means comprises one or more SQL statement.
24
Preferably, the service generator further comprises a tracking mechanism so
26 that if the data has already been retrieved from a table then it is not re-
read
27 and subsequently reprocessed.
28
29 By generating the possible static SQL statements and including a mechanism
to read data once, the service generator provides a highly re-usable
31 component that can meet any extract requirement as efficiently as possible.
32 By including the calls to programs to retrieve parent rows the service
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
5
1 generator also ensures that the data extracted will be a complete
Referential
2 Integrity set and able to be successfully loaded.
3
4 Preferably, the service generator further comprises a delete program which
can delete data access paths by accessing the relations database to analyse
6 the table definition and all available indexes upon the table.
7
8 Preferably, the service generator comprises a database service generator
9 which identifies the one or more tables that comprise a database service,
extracts them from the first database and loads them into a database service.
11
12 Preferably, the service generator comprises a product service generator
which
13 identifies the one or more database services that comprise a product
service,
14 extracts them from the first database and loads them into a database
service.
16 Preferably, the service generator is adapted to create products from the
17 database services.
18
19 Preferably, the second environment is a test environment.
21 In accordance with a second aspect of the invention there is provided a
22 method for copying data from a first to a second environment in an
information
23 management system, the method comprising:
24 creating a relations database comprising a set of relations derived from a
first
database, said relations providing a link between database tables in the first
26 database, wherein the relations database is created outside the runtime
27 environment;
28 generating services by extracting and copying database tables at runtime to
29 form a predetermined service or product, wherein
upon receiving a request to copy a product or service, the service is
31 generated only if the data in the database tables comprising the product or
32 service is the subject of one or more relation as contained within the
relations
33 database.
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
6
1
2 Preferably, one or more type of relation is present in the relations
database.
3
4 Preferably, a first type of relation is the presence of an index for
preselected
data in the database table.
6
7 Preferably, the relation is a referential integrity relation.
8
9 Preferably, where database tables are related as referential integrity
parent
and child, data from both tables will be extracted.
11
12 Preferably, the relation is an application relation.
13
14 Preferably, the system further comprises input means to allow a user to
create
application relations between data tables in the first database and to allow
the
16 data tables to be included in the relations database.
17
18 By using static SQL instead of dynamic SQL less CPU capacity is required to
19 create copies. This means that copies can be created at any time for
testing.
21 Preferably, the service generator comprises encryption means for encrypting
22 and decrypting data in database tables.
23
24 Preferably, the encryption means comprises one or more SQL statement.
26 Preferably, the service generator further comprises a tracking mechanism so
27 that if the data has already been retrieved from a table then it is not re-
read
28 and subsequently reprocessed.
29
By generating the possible static SQL statements and including a mechanism
31 to read data once, the service generator provides a highly re-usable
32 component that can meet any extract requirement as efficiently as possible.
33 By including the calls to programs to retrieve parent rows the service
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
7
1 generator also ensures that the data extracted will be a complete
Referential
2 Integrity set and able to be successfully loaded.
3
4 Preferably, the service generator further comprises a delete program which
can delete data access paths by accessing the relations database to analyse
6 the table definition and all available indexes upon the table.
7
8 Preferably, the service generator comprises a database service generator
9 which identifies the one or more tables that comprise a database service,
extracts them from the first database and loads them into a database service.
11
12 Preferably, the service generator comprises a product service generator
which
13 identifies the one or more database services that comprise a product
service,
14 extracts them from the first database and loads them into a database
service.
16 Preferably, the service generator is adapted to create products from the
17 database services.
18
19 Preferably, the second environment is a test environment.
21 Brief Description of the Drawings
22
23 The present invention will now be described by way of example only with
24 reference to the accompanying drawings in which:
26 Figure 1 is an illustration of the structure of a system in accordance with
the
27 present invention;
28
29 figure 2 is a schematic diagram which illustrates an advantage of the
present
invention when compared to the prior art; and
31
32 figure 3 is a flow diagram which shows the process of copying data in
33 accordance with the example of the present invention; and
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
8
1
2 figure 4 shows a schematic overview of an embodiment of a system in
3 accordance with the present invention.
4
Detailed Description of the Drawings
6
7 Figure 1 is an illustration of the structure of a system in accordance with
the
8 present invention with a database. In the figure, shown generally at
reference
9 numeral 1, an IBM DB2 database 3 is shown which comprises a plurality of
data tables denoted as X1 to X13..., each of these databases comprises fields
11 for holding information on, for example, a person's name, address,
telephone
12 number and other matters such as insurance policy numbers, financial
13 services products, intermediary details and so on. Within the data tables,
at
14 least some of the fields may be indexed. It is well known that indexing
makes
data in large databases easier to search.
16
17 The relations database 5 comprises a set of information on data tables
which
18 have been derived from database 3 where the data tables contain a relation
19 which is valid for the purpose of defining links and relationships between
data
tables. In this example of the present invention the valid relations are those
21 which define or describe data indexing, an application relation or
referential
22 integrity between parent and child database tables.
23
24 The database service 7 comprises a number of tables which are to be found
via the relations database and which make up database services such as
26 customer details 9, payments 11, invoices 13 or the like. These database
27 services, in turn are used to create product services as shown generally by
28 reference numeral 15 and by reference numerals 17 and 19.
29
The present invention provides a system and method for copying data that is
31 fast and efficient. By creating a relation database and prescribing the use
of
32 data tables in a database which are referred to in the relation database,
the
33 present invention ensures that well defined pathways created outside the
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
9
1 runtime environment are the ones that are used to create database services
2 and product services. In known copying tools, the determination of pathways
3 between data tables, the calculation and identification of which databases
are
4 required and which pathways are to be used is made at run time using
dynamic SQL. This requires significant processor power and in some
6 examples has been calculated as slower than using the present invention by a
7 factor of 100.
8
9 Figure 2 is a schematic diagram which illustrates an advantage of the
present
invention when compared to the prior art.
11
12 The figure 21 shows a time line 23 which illustrates the time involved in
13 making a copy of data between environments using the prior art 24 and the
14 present invention 25. The time line is divided by a vertical line 27 which
marks the notional change between the pre-runtime environment and the
16 runtime environment for a database system.
17
18 As described above, the use of dynamic SQL requires much more processing,
19 and also requires a repeat of many of the processing steps. In contrast,
the
present invention uses the relations database to provide predefined data
21 pathways and definitions which can be used with static SQL and requires
little
22 in the way of runtime processing.
23
24 Figure 3 is a flow diagram 31 which describes an overview of an example of
the process in accordance with the present invention. In this example, a user
26 defines 33 a set of data, such as a new product which is to be copied for
test
27 purposes. In this example of the invention, the overall set of data tables
is
28 contained in a DB2 Catalogue. A subset of these tables is defined in the
29 Relations Database which describes only data tables which have been
indexed or linked to other data tables.
31
32 The flow diagram 31 shows a user initiating a copy command 33. This
33 defines a set of data which may form a new or modified product or service
= CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
10
1 which the user wishes to copy and test. A check 35 is made to determine
2 whether all of the data tables within the product or service are indexed
i.e.
3 whether all are represented in the relations database. If all are present,
the
4 copy is made by first extracting the data 37 then loading the data 39. This
process is described in more detail below with reference to the example of
6 figure 4. If one or more of the data tables is absent from the relations
7 database, the user is informed 45 and asked to create a relation to allow
the
8 data table to be added to the relations database. Once all of the non-
indexed
9 data tables for the product or service have been defined in the relations
database, the process of copying the product or service may proceed.
11
12 Figure 4 shows an embodiment of a system in accordance with the present
13 invention. The system 51 is designed to allow quick and easy copying of
user
14 defined products and services and comprises a DB2 catalogue 53 which
contains all of the data tables which may be useable to create a product or
16 service, a relations database 55 which, in this example comprises
information
17 concerning a subset of the databases contained in the DB2 catalogue 53.
18 This subset is of indexed or linked data tables.
19
The system further comprises a product service generator 57, a database
21 service generator 59 and a data access generator 61 which implement
22 product services 63, data services 65 and data access services 67. Each
23 product service comprises one or more data access service and/or data
24 service. In each case data can be extracted and loaded and in the case of
data access services, encryption 67b and data deletion 67d may be
26 implemented.
27
28 As previously described, the relations database 55 contains information on
29 data tables which allows the data tables to be used in the creation/copying
of
products and services. In this example, the relations database 55 contains the
31 following information:
32
33 = The name of the Data Access program for a table;
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
11
1 = Table to table relationships;
2 = Available indexes;
3 = which tables form a Database Service;
4 = which Database Services form a Product Service; and
= encryption requirements.
6
7 The Data Access Generator 61 creates database services from data tables.
8 In order to achieve this it generates up to 5 COBOL programs which are
9 required for data extraction and loading as well as supporting copybook
definitions and bind statements.
11
12 The programs generated are:
13 = Extract Program;
19 = RI Parent program;
= Encryption program;
16 = Delete Program; and
17 = Load program.
18
19 For data extraction, in this example, there are up to 3 programs:
= Data Extract Program;
21 = RI Parent program; and
22 = Encryption program.
23
24 The table extract program contains a separate piece of SQL to select all
columns for each possible access path to the selected data. The Generator
26 does this by accessing both the IBM DB2 catalog 53 and the relations
27 database 55 to analyse the table definition and its available indexes.
28
29 Where data encryption is required, the Data Access Generator 61 will
implement the code to do the encryption. This encryption will be done within
31 the SQL statement where possible but if this is not possible the Data
Access
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
12
1 Generator 61 will put the encryption in a separate encryption program and
call
2 that from the table extract program.
3
4 By accessing the relations database 55 and the DB2 database 53, the Data
Access Generator 61 is aware of other tables that are related to the one being
6 generated. This will either be a relationship defined within the relations
7 database but derived from information on the DB2 database or one which is
8 user defined. For each relationship the Data Access Generator 61 will
identify
9 the program to be called and insert the code to make the call.
11 Where the data is being extracted from a table that is an RI child of
another
12 table the Generator will put in a program call to retrieve the parent data.
This
13 data will be required to successfully load the data from the table being
14 generated. Code will be created to determine whether to call the table
extract
program for the parent table or the RI parent program. Irrespective of which
16 program is called the Generator will set the input parameters to retrieve
the
17 parent data as efficiently as possible.
18
19 The present invention also has a tracking mechanism so that if the data has
already been retrieved from a table then it is not re-read and subsequently
21 reprocessed. The Data Access Generator 61 will insert the appropriate code
22 to make available the means to check to see if the data has already been
read
23 before it executes the SQL.
24
The Data Access Generator 61 is provided with information to determine
26 when an RI parent program is required by interrogating the relationships on
27 the table within the relations database. The RI parent program will only be
28 called with the full primary key and the Data Access Generator 61 will
29 generate SQL to meet that requirement. Before accessing the data the Data
Access Generator 61will put in any calls required to retrieve the parent data
31 for this table.
32
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
13
1 Where encryption is required, the encryption program does the encryption
that
2 cannot be done as part of the SQL in the table extract program. The Data
3 Access Generator 61 produces this program when encryption exists on the
4 table and will know whether the desired encryption needs to be done in this
program or can be done as part of the extract SQL. The results of the
6 encryption are written to a log at runtime.
7
8 Once data which is fully indexed has been extracted from the DB2 database,
9 the data which forms a database service is loaded. For data loading there
are
2 programs generated:
11 = DB2 delete Program 67d
12 = DB2 load program 67c
13
14 The DB2 delete 67d program contains the SQL to delete all rows for all
possible access paths to the data. The Data Access Generator 61 does this
16 by accessing the IBM DB2 catalog to analyse the table definition and all
17 available indexes upon the table. A separate piece of static SQL is
generated
18 for all possible access paths to the data,
19
The DB2 load program 67c generates the SQL to insert a row into a table.
21 Where there is a primary key on the table the Data Access Generator 61 will
22 identify this and add UPDATE logic so that existing rows are replaced
rather
23 than inserted.
24
In this example of the present invention, the Database Service Generator 59
26 generates 2 COBOL programs. The first controls the data extraction process
27 whilst the other will control the data deletion process, if it has been
defined.
28 The database service programs 65a, 65b control what data is logged so that
29 at the end of the job there is detailed information about the data
extracted,
deleted and loaded. Where data has been encrypted the new versions are
31 written to the log file so that the customer can identify their cases.
32
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
14
1 As with the Data access Generator 61, the Database Service Generator 59
2 identifies the tables that have been defined to the copy system as being
part
3 of the database service and generates a top level routine to control access
to
4 the table extract programs. The database service generator 59 will take the
table relationships that form the database service from the relations database
6 55 and set the inputs to all the table extract programs so that the most
7 efficient access path is utilised to retrieve the data.
8
9 The Database Service Generator 59 will set parameters in the database
service extract program 65a that all the table extract programs will use to
11 determine what processing is required. The database service generator
12 understands the RI relationships and will order the calls to the data
access
13 routines so that the extracted data appears in the order that it is
required to be
14 loaded.
16 It will also determine the level of tracking required based on data stored
within
17 the copy system.
18
19 The database service generator 59 identifies the tables that have been
defined to the relations database 55 as requiring the data to be deleted prior
21 to loading. The database service generator 59 will work out the best access
22 path to delete the data based on the way in which the delete has been
defined
23 to the copy system and the available indexes. It will then set the inputs
to the
24 DB2 delete program to delete the data using the best access path available.
The database service generator 59 will delete the data in the order that has
26 been defined to the relations database 55.
27
28 The product service generator 57 generates skeleton JCL (job control
29 language) and control files to run the database services that form a
product
service. The product service JCL will take data from a single source
31 environment and load it to potentially multiple target environments.
32
CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
15
1 For each product service defined to the copy system, there is information
2 stored as to all the possible source environments and all possible target
3 environments. The product service generator uses this information to
4 generate control cards to drive the generation of the appropriate jobs to
extract and load the data based on user input.
6
7 It also generates skeleton JCL to carry out the extract and load process.
8 There are three types of jobs generated:
9 = Driver Job;
= Extract Phase; and
11 = Load Phase.
12
13 There are occasions when it is not possible to generate a piece of JCL to
14 meet a requirement. The Generator allows exit points to be defined where a
manually written procedure can be included in the job flow.
16
17 There are 2 main types of special requirements that can be included:
18 = Conversion Exit ¨ This will change the data that has been extracted
19 based on information in the test environment. The most common reason for
this is to change key identifiers so they don't clash with existing data in
test.
21 = Supplementary Exit: This will extend the data being extracted based on
22 the data already retrieved. The most common reason for this exit is where
23 data is required but the key data to access the data is dependant on the
data
24 already extracted.
26 The product service generator 57 will generate a driver job that will use
the
27 generated skeleton JCL to dynamically build specific jobs to extract and
load
28 data based on user input.
29
The product service generator 57 understands what database services form
31 the product service. For each database service the generator knows which
32 DB2 and IMS subsystems are required to Extract and load the data. It will
= CA 02810459 2013-03-05
WO 2012/032299 PCT/GB2011/001320
16
1 build all the extract jobs required and submit them on the appropriate
2 subsystems to execute.
3
4 The Driver job will set up the starting point for job tracking to enable the
copy
system to follow the complete progress of the extract/load process. As part of
6 this it will indicate what jobs will run so that subsequent jobs will know
when
7 the entire process is complete.
8
9 The product service generator 57 will generate both DB2 and IMS skeleton
JCL to extract all the database services that form the product service.
11 Database services will be grouped together by subsystem within which they
12 are to run.
13
14 There are times where a database service has an associated service that
needs to be included in addition the standard database services that form the
16 product service. The generator knows about these services from the
relations
17 database 55 and will include the appropriate JCL to run the supplementary
18 service. It will also set up the Job to load the data, if required, and
also set up
19 the necessary JCL so that the job tracking is informed when the job starts
and
when it finishes.
21
22 The product service generator 57 will generate both DB2 and IMS skeleton
23 JCL to load all the data that has been extracted in the extract phase.
24
The product service generator 57 will know whether there is data to be
26 deleted before it is loaded and will include a call to the appropriate
Database
27 delete service. Using information stored in the relations database 55 the
28 product service generator 57 knows when there is data in the files that
needs
29 conversion either based on predefined rules or on values stored on the
target
environment. The product service generator 57 will put in the necessary calls
31 to carry out this conversion. It will then invoke a generic program to load
the
32 data from the extract files. It will set up the necessary JCL so that the
job
CA 02810459 2013-03-05
WO 2012/032299 17 PCT/GB2011/001320
1 tracking is informed when the job starts and when it finishes. When all jobs
2 are complete the originating user will be informed.
3
4 The present invention provides a tool for copying data between environments
which greatly improves the speed and efficiency of the copying process. The
6 overall effect of this is to increase the amount of product testing that can
be
7 done which improves product reliability and reduces errors.
8
9 Improvements and modifications may be incorporated herein without deviating
from the scope of the invention.
11
12
