Daten kopieren mit dem DBDataPumper
Übersicht
Der DBDataPumper ist ein Objekt vom Typ com.nextlevel.tools.b2b.db.DBDataPumper der B2B Skript Konsole, das Methoden zum kopieren von Tabellen anbietet. Ein Beispiel zu seinem Einsatz ist in einem nachstehenden Skript enthalten.
Command-Methods
DBConnection newConnection(String jdbcUrl, String jdbcUrl)
Die Methode newConnection konfiguriert eine neue Datenbank-Verbindung,
die als Quelle oder Senke für einen Kopiervorgang genutzt werden kann.
Jeder Kopiervorgang des DBDataPumper benötigt sowohl Quelle als auch
Senke.
var to = DBDataPumper.newConnection(
"jdbc:sqlserver://x;databaseName=b2bbp",
"com.microsoft.sqlserver.jdbc.SQLServerDriver");
to.setUser("x");
to.setPassword("x");
to.setTargetDataBase("SQLServer");
to.setSchema("dbo");
DBTaskDefinition newTask(DBConnection from, DBConnection from, String tableName)
Diese Methode definiert einen neuen Kopiertask, der eine Tabelle von einem System in ein anderes System kopiert.
var task = DBDataPumper.newTask(from, to, "FIRST_TABLE");
task.setTimeout(3600);
task.setBatchSize(10000);
task.setCommitSize(10000);
task.setIsolationLevel("READ_UNCOMMITTED");
task.setName(filename);
task.join("INNER JOIN OTHER_TABLE ON FIRST_TABLE.key = OTHER_TABLE.key");
task.setOrderBy("OTHER_TABLE.started");
task.setWhere("OTHER_TABLE.state > 5 AND FIRST_TABLE.name like '%someName%'");
void execute(DBTaskDefinition definition)
Die Methode reiht einen definierten Task zur Abarbeitung in eine Warteschlange ein. Der Task wird dabei kopiert, Änderungen an der Taskdefinition haben keinen Einfluss mehr auf den eingereihten Task.
DBDataPumper.execute(attributeTask);
boolean awaitFinish(int secondsToWait)
Die Methode wartet eine Anzahl von Sekunden, dass alle Tasks abgearbeitet sind und gibt true zurück, falls die Tasks abgearbeitet wurden.
DBDataPumper.execute(attributeTask);
Beispiel-Skript
var oneDay = 86400 * 1000 * 1;
var calendar = java.util.Calendar.getInstance();
calendar.set(2014, 2, 2, 0, 0, 0);
var startTime = calendar.getTimeInMillis();
calendar.set(2015, 0, 1);
var stopTime = calendar.getTimeInMillis();
var to = DBDataPumper.newConnection(
"jdbc:sqlserver://x;databaseName=b2bbp",
"com.microsoft.sqlserver.jdbc.SQLServerDriver");
to.setUser("x");
to.setPassword("x");
to.setTargetDataBase("SQLServer");
to.setSchema("dbo");
function getTask(start, stop, tableName) {
var dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
var h2Format = new java.text.SimpleDateFormat("yyyyMMdd");
var filename = java.lang.String.format(
"Z:\\b2b\\MigrationsDaten_Prod\\stored%s", h2Format
.format(new java.util.Date(start)));
var h2Url = "jdbc:h2:" + filename + ";COMPRESS=TRUE",
var from = DBDataPumper.newConnection(h2Url, "org.h2.Driver");
var task = DBDataPumper.newTask(from, to, tableName);
task.setTimeout(3600);
task.setBatchSize(10000);
task.setCommitSize(10000);
task.setIsolationLevel("READ_UNCOMMITTED");
task.setName(filename);
return task;
}
while (startTime + oneDay < stopTime) {
var nextStartTime = startTime + oneDay;
var messageTask = getTask(startTime, nextStartTime, "B2BBP_DATA_MESSAGE");
var dataFile = new java.io.File(messageTask.getName());
if (dataFile.exists()) {
messageTask.addColumn("B2BBP_DATA_MESSAGE.MESSAGEID AS MESSAGEID");
messageTask.addColumn("B2BBP_DATA_MESSAGE.REFERENCEID AS REFERENCEID");
messageTask.addColumn("B2BBP_DATA_MESSAGE.DIRECTION AS DIRECTION");
messageTask.addColumn("B2BBP_DATA_MESSAGE.STARTED AS STARTED");
messageTask.addColumn("B2BBP_DATA_MESSAGE.FINISHED AS FINISHED");
messageTask.addColumn("B2BBP_DATA_MESSAGE.FORMATIN AS FORMATIN");
messageTask.addColumn("B2BBP_DATA_MESSAGE.FORMATOUT AS FORMATOUT");
messageTask.addColumn("B2BBP_DATA_MESSAGE.VDEWTYPE AS VDEWTYPE");
messageTask.addColumn("B2BBP_DATA_MESSAGE.VDEWVERSION AS VDEWVERSION");
messageTask.addColumn("B2BBP_DATA_MESSAGE.STATE AS STATE");
messageTask
.addColumn("B2BBP_DATA_MESSAGE.ACKNOWLEDGEMENT AS ACKNOWLEDGEMENT");
messageTask.addColumn("B2BBP_DATA_MESSAGE.PARTNER AS PARTNER");
messageTask.addColumn("B2BBP_DATA_MESSAGE.SENDER AS SENDER");
messageTask
.addColumn("B2BBP_DATA_MESSAGE.CORRELATIONID AS CORRELATIONID");
messageTask
.addColumn("B2BBP_DATA_MESSAGE.ALTERNATIVEID AS ALTERNATIVEID");
messageTask.addColumn("B2BBP_DATA_MESSAGE.CHANNELID AS CHANNELID");
messageTask
.addColumn("B2BBP_DATA_MESSAGE.CLEARINGCODE AS CLEARINGCODE");
DBDataPumper.execute(messageTask);
var actionTask = getTask(startTime, nextStartTime, "B2BBP_DATA_ACTION");
actionTask.addColumn("B2BBP_DATA_ACTION.ACTIONID AS ACTIONID");
actionTask.addColumn("B2BBP_DATA_ACTION.MESSAGEID AS MESSAGEID");
actionTask.addColumn("B2BBP_DATA_ACTION.STARTED AS STARTED");
actionTask.addColumn("B2BBP_DATA_ACTION.FINISHED AS FINISHED");
actionTask.addColumn("B2BBP_DATA_ACTION.TYPE AS TYPE");
actionTask.addColumn("B2BBP_DATA_ACTION.NAME AS NAME");
actionTask.addColumn("B2BBP_DATA_ACTION.CLASSNAME AS CLASSNAME");
actionTask.addColumn("B2BBP_DATA_ACTION.STATE AS STATE");
DBDataPumper.execute(actionTask);
var errorTask = getTask(startTime, nextStartTime, "B2BBP_DATA_ERROR");
errorTask.addColumn("B2BBP_DATA_ERROR.ACTIONID AS ACTIONID");
errorTask.addColumn("B2BBP_DATA_ERROR.MESSAGEID AS MESSAGEID");
errorTask.addColumn("B2BBP_DATA_ERROR.ERRORID AS ERRORID");
errorTask.addColumn("B2BBP_DATA_ERROR.NAME AS NAME");
errorTask.addColumn("B2BBP_DATA_ERROR.STACKTRACE AS STACKTRACE");
errorTask.addColumn("B2BBP_DATA_ERROR.MESSAGE AS MESSAGE");
DBDataPumper.execute(errorTask);
var clearingTask = getTask(startTime, nextStartTime,
"B2BBP_DATA_CLEARING");
clearingTask.addColumn("B2BBP_DATA_CLEARING.CREATED AS CREATED");
clearingTask.addColumn("B2BBP_DATA_CLEARING.MESSAGEID AS MESSAGEID");
clearingTask.addColumn("B2BBP_DATA_CLEARING.USERNAME AS USERNAME");
clearingTask
.addColumn("B2BBP_DATA_CLEARING.CLEARINGCODE AS CLEARINGCODE");
clearingTask.addColumn("B2BBP_DATA_CLEARING.SHORTTEXT AS SHORTTEXT");
clearingTask.addColumn("B2BBP_DATA_CLEARING.LONGTEXT AS LONGTEXT");
DBDataPumper.execute(clearingTask);
var clearing2Task = getTask(startTime, nextStartTime,
"B2BBP_DATA_CLEARING2");
clearing2Task
.addColumn("B2BBP_DATA_CLEARING2.MESSAGEPARTID AS MESSAGEPARTID");
clearing2Task.addColumn("B2BBP_DATA_CLEARING2.MESSAGEID AS MESSAGEID");
clearing2Task.addColumn("B2BBP_DATA_CLEARING2.USERNAME AS USERNAME");
clearing2Task.addColumn("B2BBP_DATA_CLEARING2.CREATED AS CREATED");
clearing2Task
.addColumn("B2BBP_DATA_CLEARING2.CLEARINGCODE AS CLEARINGCODE");
clearing2Task.addColumn("B2BBP_DATA_CLEARING2.SHORTTEXT AS SHORTTEXT");
clearing2Task.addColumn("B2BBP_DATA_CLEARING2.LONGTEXT AS LONGTEXT");
DBDataPumper.execute(clearing2Task);
var attributeArchiveTask = getTask(startTime, nextStartTime,
"B2BBP_DATA_ATTRIBUTE_ARCHIVE");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.ATTRIBUTEID AS ATTRIBUTEID");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.ACTIONID AS ACTIONID");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.MESSAGEID AS MESSAGEID");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.NAME AS NAME");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.VAL AS VAL");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.LEN AS LEN");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.TYPE AS TYPE");
attributeArchiveTask
.addColumn("B2BBP_DATA_ATTRIBUTE_ARCHIVE.PREVIEW AS PREVIEW");
DBDataPumper.execute(attributeArchiveTask);
var attributeTask = getTask(startTime, nextStartTime,
"B2BBP_DATA_ATTRIBUTE");
attributeTask
.addColumn("B2BBP_DATA_ATTRIBUTE.ATTRIBUTEID AS ATTRIBUTEID");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.ACTIONID AS ACTIONID");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.MESSAGEID AS MESSAGEID");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.NAME AS NAME");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.VAL AS VAL");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.LEN AS LEN");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.TYPE AS TYPE");
attributeTask.addColumn("B2BBP_DATA_ATTRIBUTE.PREVIEW AS PREVIEW");
DBDataPumper.execute(attributeTask);
} else {
log("File: " + messageTask.getName() + " does not exist!");
}
startTime = nextStartTime;
}