Высокопроизводительные операции вставки с использованием JDBC Type 4 в средах с ограниченными ресурсами

Используйте преимущества объявленных глобальных временных таблиц DB2

Узнайте, как выполнить большое количество операций вставки данных, используя только технологию Java™ и соединение IBM® DB2® Universal Driver Type 4, эффективную альтернативу для ситуаций, когда код приложения не имеет доступа к DB2-клиенту или не применимы утилиты import/load. В основе повышения производительности лежит использование объявленных глобальных временных таблиц DB2 (Declared Global Temporary Tables - DGTT).

Боб Калио, инженер-программист, IBM

Боб Калио (Bob Calio) работает инженером-программистом в IBM Thomas J. Watson Research Center, Yorktown, NY. Боб в IBM уже 23 года. Он имеет 18-летний опыт разработки DB2-приложений. В настоящее время интересуется использованием преимуществ DB2 и Websphere в области инструментальных средств проектирования VLSI.



Иззи Бендрихем, инженер по аппаратному обеспечению, IBM

Иззи Бендрихем (Izzy Bendrihem) работает старшим инженером по аппаратному обеспечению в отделе VLSI Design Department в IBM Thomas J. Watson Research Center, Yorktown, NY. Иззи в IBM 15 лет. Он занимается инструментальными средствами проектирования VLSI для разработки микропроцессоров. Участвовал в разработке высокопроизводительных микропроцессоров для игровых приставок, IBM-мэйнфреймов и UNIX-систем. В настоящее время исследует проблемы многопозиционной инфраструктуры проектирования для VLSI-разработки и основанными на базах данных программами VLSI-проектирования высокопроизводительных микропроцессоров.



27.06.2008

Проблема

Вставка строк данных в одну или несколько таблиц реляционных баз данных является обычной задачей практически во всех современных клиент-серверных или Web-приложениях.

В распределенных двухуровневых клиент-серверных средах, в которых осуществляется прямая вставка данных в реляционные таблицы, на первый план выдвигаются два ключевых момента:

  1. Производительность: продолжительность транзакции с точки зрения клиента. Если количество строк данных для вставки исчисляется тысячами, транзакции с удаленной базой данных могут занимать много времени. Дополнительное время тратится на обработку триггеров, индексов и т.д.
  2. Параллелизм: количество клиентов, которые одновременно могут обращаться к таблице в режиме чтения, записи или в обоих. Если приложение не может выполнять фиксацию транзакций достаточно часто или блокирует целевую таблицу на продолжительный период времени, пользователям может быть запрещен доступ к этой таблице (взаимоблокировка).

Если количество строк, вставляемых клиентом в таблицу на сервере относительно невелико (скажем, десятки строк), проблему можно легко решить методом прямой вставки практически без потерь в производительности или параллелизме.

Однако если вставляется много строк (скажем, тысячи или десятки тысяч), все известные на сегодняшний день решения имеют ограничения. Одним из решений является локальная установка кода DB2-клиента на каждом клиенте и выполнение утилит load/import для вставки данных в целевую таблицу.

В этом случае производительность не сильно снижается, однако параллелизм страдает существенно. Фактически, такое решение является проблематичным в нескольких аспектах, включая полномочия, параллелизм и обработку ошибок:

  • Полномочия - В DB2 выполнение утилиты загрузки (load) требует более высоких полномочий, чем обычно предоставляется пользователям. Это может сказаться на появлении нежелательных побочных явлений, включая снижение защищенности и другие.
  • Параллелизм - Утилита загрузки load может запросить исключительную блокировку целевой таблицы. Исключительная блокировка таблицы запрещает к ней доступ любого уровня (даже доступ только на чтение) на время выполнения загрузки, уменьшая параллелизм до единицы.
  • Обработка ошибок - Очень важным аспектом онлайновой транзакционной системы является способность восстанавливаться после сбоев. Если утилита загрузки load выполняется неудачно, данные в таблицу не вставляются, и в некоторых случаях табличная область (tablespace) может остаться недоступной, что потребует ручного вмешательства администратора базы данных для решения проблемы. Такая ситуация будет оказывать влияние не только на непосредственного клиента (и таблицу), но также и на других клиентов, пытающихся обратиться к целевой таблице (или таблицам в данной табличной области), уменьшая параллелизм до нуля.

Кроме добавления значительного объема работы по установке DB2-кода на каждой машине, решение с утилитой загрузки load отрицательно влияет на ключевые аспекты: параллелизм, защищенность и обработку ошибок.

Задачей является достижение максимального параллелизма для таблиц и производительности вставки с минимальными полномочиями клиентов в распределенных средах "тонких" клиентов (клиентский код базы данных отсутствует) с возможностью повторения работы после возникновения ошибок.


Решение

В основе предлагаемого решения лежит инновационный подход с использованием объявленных глобальных временных таблиц (DGTT) и Java Database Connectivity (JDBC) для вставки больших объемов данных в таблицы реляционных баз данных из удаленных, распределенных тонких клиентов. Этот подход имеет много преимуществ по сравнению с известными решениями, включая (но не ограничиваясь) следующие:

  • Минимальная блокировка (или отсутствие таковой) таблицы во время реальной вставки данных в целевую таблицу.
  • Транзакционная активность ограничена DGTT и, следовательно, не регистрируется в журнале до тех пор, пока все данные не будут проверены и вставлены в целевую таблицу.
  • Повторение неудачной транзакции вставки без необходимости перезагрузки данных.
  • Кадрирование транзакции (transaction framing) для разрешения предварительной или последующей обработки данных (pre/post processing) без влияния на целевую таблицу.
  • Требуются только полномочия на операцию вставки Insert.
  • Отсутствует влияние на других пользователей при передаче данных от клиента в базу данных.
  • Возможность использования SQL/DML, функций и хранимых процедур для очистки или подготовки данных в DGTT до вставки в целевую таблицу.
  • Возможность использования SQL/DML для соединения (join) данных в DGTT с другими таблицами в базе данных во время вставки в целевую таблицу.

Как это работает

Что такое объявленная глобальная временная таблица

Полное описание приведено в DB2 Information Center.

Из DB2 Information Center (IBM, июнь 2007):
"Выражение DECLARE GLOBAL TEMPORARY TABLE определяет временную таблицу для текущей сессии. Описание объявленной временной таблицы не появляется в системном каталоге. Она не является постоянной и не может использоваться в других сессиях. Каждая сессия, в которой определяется объявленная глобальная временная таблица с одним и тем же названием, использует свое собственное уникальное описание временной таблицы. После завершения сессии удаляются строки таблицы и описание временной таблицы".

Объявленная глобальная временная таблица создается во время выполнения как точная или измененная структурная копия целевой таблицы, выступая в роли индивидуального буфера и предоставляя каждому пользователю для работы свою собственную "песочницу". Данные из исходной таблицы не копируются, а копируется только структура.

В отличие от обычных таблиц, DGTT не регистрируются в системном каталоге базы данных, что экономит время на их создание, а также обеспечивает "невидимость" DGTT для остальных пользователей, поскольку к DGTT нельзя обратиться никакому другому пользователю или приложению, отличному от первоначального, которому DGTT принадлежит. Транзакции (вставки, обновления, удаления), выполняемые в DGTT, не регистрируются в журнале транзакций. Поскольку каждая DGTT изолирована от всех других пользователей, на данном этапе работы с данными отсутствуют какие-либо проблемы параллелизма. Все это обеспечивает быстрый и эффективный механизм вставки строк данных в таблицу базы данных.

После полного завершения клиентом загрузки DGTT (из однородного файла (flatfile) или из созданных программным путем данных), приложение готово переместить данные из DGTT в целевую таблицу. На данном этапе данные в DGTT можно легко "очистить" или подготовить каким-либо выбранным приложением способом, полностью изолировано (без появления взаимоблокировок) и без регистрации в журнале транзакций. Реальная вставка из DGTT в целевую таблицу осуществляется простым (или сложным) SQL/DML-выражением, и работа выполняется на сервере с небольшим потреблением сетевого трафика или вовсе без него. Поскольку эта работа выполняется локально на сервере, она намного быстрее удаленных транзакций, что минимизирует вероятность блокировки таблицы.

Если вставка из DGTT в целевую таблицу по каким-либо причинам проходит неудачно, можно выполнить откат всей транзакции (вставки) и повторить ее любое количество раз. До тех пор пока приложение не удалит DGTT, данные будут оставаться на сервере, и клиенту не нужно снова их передавать. Просто повторите SQL-выражение insert, проверяя ошибки.

После успешного перемещения данных из DGTT в целевую таблицу приложение может удалить DGTT, иначе она будет удалена автоматически после отсоединения приложения от базы данных.

На рисунке 1 показана блок-схема процесса.

Рисунок 1. Процесс вставки
Рисунок 1. Процесс вставки
  1. Процесс начинается с инициализации приложения для получения всей необходимой информации (имени целевой таблицы, файла и т.д.).
  2. Подключение к базе данных.
  3. Объявленная глобальная временная таблица (DGTT) создается как точная или измененная копия целевой таблицы.
  4. Данные считываются из файла или создаются программным путем и подготавливаются для вставки.
  5. Данные вставляются в DGTT.
  6. Выполнение предварительной обработки.
  7. (Очистка или соединение данных при необходимости) Вставка в целевую таблицу.
  8. Проверка ошибок и выполнение логики ошибка/повтор.
  9. Выполнение пост-обработки данных в целевой таблице (при необходимости).
  10. Выполнение фиксации изменений и выход.

На рисунке 2 показан поток данных.

Рисунок 2. Поток данных
Рисунок 2. Поток данных
  1. Получить информацию по подключению и подключиться к базе данных. Специфичные для конкретной транзакции параметры можно прочитать из ключей командной строки или создать их "на лету".
  2. Объявить глобальную временную таблицу (DGTT) как точную или измененную копию целевой таблицы.
  3. Данные, предназначенные для вставки, могут быть прочитаны из файла, канала (pipe)(стандартного потока ввода, stdin) или созданы программным путем "на лету". Вставить все строки из входного потока данных в DGTT, используя подготовленное выражение (регистрация TRX здесь не выполняется). Для еще большего ускорения обработки используйте возможности пакетной обработки JDBC для выполнения нескольких команд insert одновременно. При ошибке выполните обработку ошибок (целевая таблица все еще не затрагивалась). В случае успеха выполните фиксацию транзакции для сохранения строк в DGTT. Выполните всю необходимую обработку данных в DGTT (сортировки, вычисления и т.д.).
  4. Выполните SQL-команду insert для перемещения всех строк из DGTT в целевую таблицу (эта операция регистрируется в журнале). Вся работа осуществляется на сервере с минимальным потреблением сетевого трафика. Если возникает ошибка, можно повторить операцию вставки, поскольку DGTT все еще содержит данные. Выполните любую пост-обработку; другими словами, активизируйте событие приложения или DB2-триггер.
  5. Зафиксируйте транзакцию, удалите DGTT и завершите работу.

Листинг псевдокода

Листинг 1. Псевдокод
//Начать обработку, получить DB2-соединение

Connection connection = getConnection();
//изменить название таблицы
String targetTable = " Your Table";
//будет сгенерировано уникальное имя таблицы
String tempTable = "SESSION.T"+System.currentTimeMillis();

//Предположим, что имеется относительно стабильное выражение insert с 
//фиксированным числом столбцов. При необходимости в динамическом выражении 
//insert вы должны создать его "на лету".
//Вы должны настроить это выражение, чтобы оно соответствовало
//вашим входным данным и столбцам таблицы db2 

String insertSql = "insert into "+tempTable+" (column1,column2,column3)
                    values(?,?,?)";

String createDGTT = "DECLARE GLOBAL TEMPORARY TABLE "+tempTable +
      			" LIKE  " + targetTable +
                        " INCLUDING IDENTITY COLUMN ATTRIBUTES " +
                        " INCLUDING COLUMN DEFAULTS  NOT LOGGED  " +
                        " ON COMMIT PRESERVE ROWS in USERTEMP" ;
try {
	connection.setAutoCommit(false);//выполните фиксацию самостоятельно
	statement = connection.createStatement();
	//Создать таблицу DGTT как точную копию целевой таблицы
	statement.execute(createDGTT);
	connection.commit();
	//получить подготовленное выражение 
	ps = connection.prepareStatement(insertSql);


     	Do Insert Processing here
		int c = 1;
		ps.setObject(c++,value1);
		ps.setObject(c++,value2);
 		....
		ps.setObject(c++,valueN);
		ps.execute();
		connection.commit();//все строки вставлены в DGTT

		try {
                  boolean done = false;
                  for (int retries=0;!done " " retries < 10 ; retries++ )
                  {
                       //теперь скопировать все строки из временной в целевую таблицу
                       statement.executeUpdate(" insert into "+targetTable+
                                               " select * from "+tempTable);
                       connection.commit();
                  }
		} catch (SQLException e) {
               //может быть, выполнить здесь логику отката и повтора,
               //поскольку все строки все еще находятся в Temp Table
	         try {connection.rollback();} catch (Exception e1) {}
	         if (e.getErrorCode()==-911 ||
                     e.getErrorCode()==-964){// взаимоблокировка, повторить
	             done=false;
	             long s = random.nextInt(120);//0-120 seconds
	             Thread.sleep(s*1000);//подождать ? секунд и повторить
	         }else {
	             e.printStackTrace();
               }
		}

	//очистка
	statement.execute("DROP TABLE "+tempTable);//не обязательно, но это – хороший тон

} catch (Exception e) {
	e.printStackTrace();
      try{connection.rollback();} catch(Exception e){}
} finally {
	try{ps.close();} catch(Exception e){}
	try{statement.close();} catch(Exception e){}
	try{connection.commit();} catch(Exception e){}
	try{connection.close();} catch(Exception e){}
}

Системные требования

Для того чтобы использовать объявленные глобальные временные таблицы в DB2, необходимо выполнение следующих условий:

Требования к серверу DB2

Настройка размера кучи управления (control heap) приложения.

Из DB2 Information Center (IBM, октябрь 2006):
"Начните со значения по умолчанию. Возможно, придется установить большее значение для работы сложных приложений, если система содержит большое число разделов базы данных или используются объявленные временные таблицы. Объем требуемой памяти увеличивается с количеством одновременно работающих объявленных временных таблиц. Объявленная временная таблица с большим числом столбцов имеет больший размер дескриптора таблицы, чем таблица с небольшим числом столбцов, поэтому большое число столбцов в объявленных временных таблицах приложения также увеличивает требования к размеру кучи управления приложения".

  1. DB2 8.2 или выше, работающая на Windows®, Linux® или UNIX®. Предпочтительнее DB2 9.x.
  2. Существующая база данных. Если базы данных нет, создайте ее.
  3. Измените базу данных следующим образом:
    1. Создайте пользовательскую временную табличную область (полный синтаксис команды приведен в разделе "Команда Create Tablespace" в DB2 Information Center):
      • CREATE USER TEMPORARY TABLESPACE USERTEMP MANAGED BY SYSTE PAGESIZE 4096 USING ('/db2data/usertemp') ...
    2. Предоставьте полномочия на использование этой табличной области всем пользователям, нуждающимся в доступе:
      • GRANT USE OF TABLESPACE USERTEMP TO USER db2user1
    3. Увеличьте размер кучи управления приложения (APP_CTL_HEAP_SZ. Полный синтаксис команды приведен в разделе "Параметр конфигурации control heap size" в DB2 Information Center):
      • UPDATE DB CFG USING APP_CTL_HEAP_SZ xxx

Требования к клиенту

Реализация этой методики на Java-коде может выполняться на клиентских машинах с Windows, Linux или UNIX, на которых имеется:

  1. Java Runtime Environment (JRE) 1.4 или старше.
  2. Измененный CLASSPATH, в который включены следующие jar-файлы, загруженные из DB2:
    • db2jcc.jar
    • db2jcc_license_cu.jar

Способ загрузки DB2, Java-технологии или того и другого описан в разделе "Ресурсы".


Заключение

В данной статье рассматривалась методика, использующая возможности объявленных глобальных временных таблиц для повышения производительности вставки строк данных, с применением "тонких" клиентских приложений баз данных, написанных на Java/JDBC. Эту методику можно также использовать для выполнения SQL-команд query, merge и delete путем загрузки данных из однородных файлов в DGTT и соединения с постоянной таблицей в базе данных.


Благодарности

Спасибо Тому Буселоту (Tom Bucelot) и Карлосу Фонсеке (Carlos Fonseca) за их помощь и поддержку при написании данной статьи.

Ресурсы

Научиться

Получить продукты и технологии

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=317269
ArticleTitle=Высокопроизводительные операции вставки с использованием JDBC Type 4 в средах с ограниченными ресурсами
publish-date=06272008