Row-based archiving in database accelerators
An indication of data of a first table in a first database is received. The data includes one or more rows of data. The indicated data is transferred from the first table in the first database component to a backup table in the same database component. The data is transferred from the backup table to a second table in a second database component.
1. A method comprising:
receiving a request to transfer a row of data from a source table of a source database system, to a target table of a target database system, wherein the row of data is a subset of a first partition of the source table and the target database system is an online analytical processing (OLAP) database;
responsive to receiving the request:
creating a second partition in an intermediate table of the source database system,
transferring the row of data to the second partition,
copying the row of data to a backup partition on a backup database system,
storing an image copy of the backup partition,
copying the second partition to the target table, to create an archive partition in the target table,
deleting the row of data from the source table, and
purging the second partition from the intermediate table of the source database system.
2. The method of claim 1 , further comprising:
receiving, by one or more computer processors, a query directed to the source table, wherein the query is received at the source database system, and wherein the query is a request to retrieve the row of data;
querying, by one or more computer processors, the source table for the row of data; and
responsive to not identifying the row of data in the source table, querying, by one or more computer processors, the target table for the row of data.
3. The method of claim 2 , further comprising:
responsive to not identifying the row of data in the target table, querying, by one or more computer processors, the image copy for the requested data.
4. A computer program product comprising:
one or more computer readable storage media; and
program instructions stored on the one or more computer readable storage media, the program instructions comprising:
program instructions to receive a request to transfer a row of data from a source table of a source database system, to a target table of a target database system wherein the row of data is a subset of a first partition of the source table and the target database system is an online analytical processing (OLAP) database;
responsive to receiving the request, program instructions to:
create a second partition in an intermediate table of the source database system,
transfer the row of data to the second partition,
copy the row of data to a backup partition on a backup database system,
store an image copy of the backup partition,
copy the second partition to the target table, to create an archive partition in the target table,
delete the row of data from the source table, and
purge the second partition from the intermediate table of the source database system.
5. The computer program product of claim 4 , further comprising program instructions, stored on the one or more computer readable storage media, to:
receive a query directed to the source table, wherein the query is received at the source database system, and wherein the query is a request to retrieve the row of data;
query the source table for the row of data; and
responsive to not identifying the row of data in the source table, query the target table for the row of data.
6. The computer program product of claim 5 , further comprising program instructions, stored on the one or more computer readable storage media, to:
responsive to not identifying the row of data in the target table, query the image copy for the requested data.
7. A computer system comprising:
one or more computer processors;
one or more computer readable storage media; and
program instructions, stored on the one or more computer readable storage media, for execution by at least one of the one or more computer processors, the program instructions comprising:
program instructions to receive a request to transfer a row of data from a source table of a source database system, to a target table of a target database system wherein the row of data is a subset of a first partition of the source table and the target database system is an online analytical processing (OLAP) database;
responsive to receiving the request, program instructions to:
create a second partition in an intermediate table of the source database system,
transfer the row of data to the second partition,
copy the row of data to a backup partition on a backup database system,
store an image copy of the backup partition,
copy the second partition to the target table, to create an archive partition in the target table,
delete the row of data from the source table, and
purge the second partition from the intermediate table of the source database system.
8. The computer system of claim 7 , further comprising program instructions, stored on the one or more computer readable storage media, for execution by the at least one of the one or more computer processors, to:
receive a query directed to the source table, wherein the query is received at the source database system, and wherein the query is a request to retrieve the row of data;
query the source table for the row of data; and
responsive to not identifying the row of data in the source table, query the target table for the row of data.
9. The computer system of claim 8 , further comprising program instructions, stored on the one or more computer readable storage media, for execution by the at least one of the one or more computer processors, to:
responsive to not identifying the row of data in the target table, query the image copy for the requested data.