 | Уровень сложности: средний Димпл Бхатиа, инженер-программист, IBM Silicon Valley Laboratory
Куинлонг (Питер) Ху, инженер-программист, консультант, IBM Silicon Valley Lab
Ханс Зеллер, старший инженер, IBM Silicon Valley Lab
30.04.2008 Узнайте, когда, где и как использовать интегрированные процедуры (federated procedures) в WebSphere® Federation Server. В данной статье описывается эта новая функциональность с точки зрения разработчика приложений. Рассказывается, как создать и вызвать интегрированные процедуры, а также как обнаружить некоторые из наиболее типичных проблем и избежать их. Материал был обновлен для пакета исправления ошибок (fix pack) 3.
Введение
WebSphere Federation Server (ранее называвшийся WebSphere Information Integrator) использует "псевдонимы" (nicknames) для интегрирования доступа к наборам данных удаленных источников данных. Псевдонимы выступают в роли локальных прокси для удаленных таблиц или табличных наборов данных. В разделе "Ресурсы" приведены ссылки на ознакомительные статьи для тех читателей, которые не знакомы с такими концепциями интеграции, как серверы, упаковщики (wrappers) и псевдонимы (nicknames).
Интегрированные процедуры похожи на псевдонимы в том, что они позволяют обращаться к удаленным хранимым процедурам. Однако они не называются "псевдонимами хранимых процедур", поскольку псевдоним рассматривается как объект, имеющий качества таблицы, а не процедуры. Создание и вызов интегрированных процедур в большинстве случаев является очень простым процессом. Допустим, например, что вы хотите вызвать удаленную хранимую процедуру rem_proc и уже имеете сервер s. Следующие два SQL-выражения создают интегрированную процедуру my_fed_proc и вызывают ее с входным и выходным параметром:
CREATE PROCEDURE my_fed_proc SOURCE rem_schema.rem_proc FOR SERVER s
CALL my_fed_proc(3,?)
|
Конечно же, это не все. И мы надеемся, что следующие разделы статьи смогут предоставить полезную информацию для более сложных вариантов применения:
Зачем нужны интегрированные процедуры
Преимущества хранимых процедур
Хранимые процедуры были придуманы для решения проблем распределенных вычислений. Они позволяют клиентскому приложению выполнять несколько операций на сервере базы данных путем передачи одного сообщения. Важные побочные эффекты: они позволяют администраторам базы данных (DBA) инкапсулировать бизнес-логику, гарантировать целостность данных и обеспечить возможность повторного использования. Во многих случаях эти побочные эффекты становятся основной причиной использования хранимых процедур. Другими словами, хранимые процедуры предоставляют следующие ключевые преимущества:
-
Производительность - Производительность хранимых процедур главным образом достигается через уменьшение количества сообщений, передаваемых между клиентским приложением (часто выполняющимся на удаленном компьютере) и сервером базы данных. Следовательно, производительность обычно повышается тогда, когда в процедуре имеется более одного SQL-выражения.
-
Инкапсуляция - Хранимые процедуры являются отличным средством реализации самого низшего уровня бизнес-логики.
-
Защищенность - Поскольку SQL позволяет управлять доступом к отдельным объектам, таким как таблицы, виды и процедуры, администратор базы данных может инкапсулировать бизнес-логику, запрещая доступ к используемым таблицам и принуждая к использованию процедур в случае бизнес-транзакций определенного рода. Это значительно облегчает гарантирование защищенности и целостности данных.
-
SQL-семантики - Обычные SQL-выражения и вызовы хранимых процедур используют общую клиентскую библиотеку и мощные модели транзакций, обработки ошибок и системы защиты SQL-интерфейсов. Использование совместно с доступом к SQL-таблицам и отдельное использование значительно упрощает разработку приложений.
От хранимых процедур к интегрированным процедурам
До сих пор мы говорили о хранимых процедурах. Если вам нужен доступ только к таблицам и процедурам из одного источника данных на знакомом вам SQL-диалекте, то в интегрированных процедурах необходимости нет. Однако часто хранимые процедуры и другие SQL-объекты размещаются в различных источниках данных и могут использовать различные SQL-диалекты. Это может усложнить приложение. Вот когда выходит на сцену новая функциональная возможность WebSphere Federation Server V9.1 - интегрированные процедуры. Они объединяют преимущества хранимых процедур, рассмотренные нами выше, с преимуществами интеграции:
-
Независимость от месторасположения - Прикладная программа не должна беспокоиться о месторасположении конкретных объектов и может использовать объекты (псевдонимы и процедуры) нескольких источников данных в одном соединении с базой данных, в одной транзакции и даже в одном SQL-выражении.
-
Прозрачность - Вызовы интегрированных процедур похожи на вызовы локальных DB2-процедур: используется обычный SQL-диалект и набор типов данных через клиентский интерфейс DB2, а также стандартный способ обработки наборов результатов. В модель DB2 входит соединение с базой данных, обеспечивающее распределенные транзакции и устраняющее необходимость аутентификации для каждого вызова процедуры.
-
Гетерогенность - Интегрированные процедуры могут активизировать процедуры в различных источниках данных. В текущей версии выбор ограничен процедурами DB2 (LUW, z/OS, iSeries), Microsoft SQL Server, Sybase и Oracle (ограничения текущей версии более подробно обсуждаются в разделе "Ограничения" данной статьи). Во многих ситуациях интегрированные процедуры требуют компенсации различий в реализации этих источников данных. Это особенно важно при обработке наборов результатов, которая в различных источниках данных отличается значительно.
-
Автономность - Интегрированные процедуры не требуют настройки, установки или конфигурирования источников данных. Существующие в источниках данных процедуры могут использоваться такими, какими они есть.
Действительно ли необходимы интегрированные процедуры
Выше описаны преимущества интегрированных процедур. В данном разделе рассматриваются некоторые возможные альтернативы, их место и сравнение с другими средами, такими как web-сервисы. Возьмем, к примеру, приложение, которое должно вызвать две хранимых процедуры различных источников данных в одной единице работы. Сравниваются несколько сценариев, основанных на том, выполняет ли прикладная программа следующие пункты:
-
Использует клиентскую библиотеку.
- Управляет распределенными транзакциями.
- Управляет распределенной системой защиты.
- Знает о месторасположении объектов в различных источниках данных.
- Знает различия в семантиках и типах данных.
Первым вариантом является использование клиентов базы данных (например, Java Database Connectivity [JDBC]) для прямого доступа к каждому источнику данных - без применения интегрированных процедур. Хотя такой метод может быть очень полезным для простого приложения, оно должно выполнить все пять пунктов, перечисленных выше. Для DB2, как и для WebSphere Federation Server, вариантами клиентских интерфейсов являются встроенный SQL (не поддерживает наборы результатов хранимых процедур), CLI, Distributed Relational Database Architecture (DRDA), Open Database Connectivity (ODBC), JDBC и Structured Query Language for Java (SQLJ). Эти интерфейсы не позволяют применить ни SQL-запросы, ни хранимые процедуры, которые используют объекты из обоих источников данных. На рисунке 1 показана схема такого распределенного приложения.
Рисунок 1. Простое приложение, использующее клиентские интерфейсы
Разработчик приложения может не захотеть использовать клиентские интерфейсы базы данных. Чтобы решить эту задачу, вы могли бы использовать провайдеры Web-сервисов для каждого источника данных. Теперь приложению больше не нужно использовать клиентскую библиотеку базы данных, но все равно ему необходимо управлять всеми другими аспектами распределения, упомянутыми в приведенном выше списке.
Рисунок 2. Приложение, использующее web-сервисы
Вместо варианта управления упомянутыми аспектами вы можете выбрать использование интегрированного сервера (federated server) и виртуализировать их в один SQL-интерфейс. Этот метод рассматривается более подробно в оставшейся части статьи. При интеграции не нужно больше выполнять ни один из пунктов, перечисленных выше, за исключением первого.
Рисунок 3. Интегрированное приложение
Если приложение предпочитает использовать интерфейс Web-сервисов вместо клиентской SQL-библиотеки, оно может объединить преимущества концепций Web-сервисов и интеграции и обращаться к интегрированному серверу через Web-сервисы, как показано на рисунке 4.
Рисунок 4. Интегрированное приложение с провайдером Web-сервисов
Теперь приложение больше не нуждается ни в одном из перечисленных выше пунктов. Естественно, Web-сервисы предоставляют больше преимуществ, чем просто устранение необходимости в клиентской библиотеке. Дальнейшей вариацией на данную тему могло бы быть использование в Federation Server упаковщика (wrapper) Web-сервисов. Как уже упоминалось, оставшаяся часть данной статьи посвящена конфигурации, изображенной на рисунке 3.
Ограничения
Перед проектированием приложения необходимо помнить о следующих ограничениях при использовании интегрированных процедур. Некоторые из этих ограничений относятся к DB2-процедурам или являются типичными для концепции интеграции, а некоторые специфичны для интегрированных процедур:
Источники данных:
- В настоящее время интегрированные процедуры поддерживаются для следующих источников данных:
- Oracle(V9.1 GA)
- Sybase(V9.1 GA)
- Microsoft SQL Server(V9.1 Fix Pack 1)
- DB2 for LUW (V9.1 Fix Pack 3)
- DB2 for z/OS(V9.1 Fix Pack 3)
- DB2 for iSeries(V9.1 Fix Pack 3)
Поддержка изолированного упаковщика (fenced wrapper):
- Интегрированными процедурами не поддерживаются изолированные упаковщики.
Вызов интегрированных процедур:
- Интегрированная процедура, активизированная из внешней определенной пользователем функции (user-defined function) не должна иметь уровень доступа READS SQL DATA или MODIFIES SQL DATA на интегрированном сервере (общее ограничение для интегрированных запросов).
- Интегрированные процедуры поддерживают только CALL RESOLUTION IMMEDIATE (вариант связывания CALL RESOLUTION DEFERRED является исключенной функциональностью в DB2).
- В Oracle можно определить функции, выводящие результаты в буфер или на стандартное устройство вывода. Пользователи не должны ожидать увидеть эти данные при использовании интегрированных процедур, определенных с такими функциями (данные могут выводиться или не выводиться в источнике данных).
Поддержка типов данных:
- Интегрированные процедуры поддерживают все типы данных параметров процедур, поддерживаемые для псевдонимов, за исключением больших объектов (LOBs/XML).
Поддержка транзакций:
- Интегрированные процедуры с уровнем доступа MODIFIES SQL DATA не могут быть активизированы из триггеров, динамических составных запросов, скалярных, табличных, строковых функций и методов. После выполнения выражения SAVEPOINT пользователи не могут вызывать интегрированную процедуру с уровнем доступа MODIFIES SQL DATA (общее ограничение для интегрированных запросов).
- Удаленные процедуры, на которые ссылается интегрированная процедура, не должны выполнять выражение COMMIT или ROLLBACK. Поскольку вы не можете заставить выполнять это ограничение, при выполнении удаленной процедуры, имеющей выражение COMMIT или ROLLBACK, может нарушиться целостность данных (см. раздел "Проблемы транзакций в выражениях вызова").
Другие ограничения:
- ALTER PROCEDURE не поддерживается.
- Все параметры являются "входными" параметрами для сервера Sybase версии 12.0. Как результат этого, для источника данных Sybase Version 12.0 нельзя возвратить значение выходного параметра. Это ограничение отсутствует в версиях 12.5 и 15.
Наборы результатов:
- Возвращается только один набор результатов. Если удаленная процедура возвращает более одного набора результатов, остальные наборы результатов отбрасываются.
- Для процедур Sybase и Microsoft SQL Server, одновременно возвращающих выходные параметры и набор результатов, набор результатов отбрасывается.
- Процедуры Sybase и Microsoft SQL Server, возвращающие набор результатов, всегда возвращают 0 как целочисленное состояние возврата, реальное значение состояния игнорируется.
- Параметры Sybase cursor не поддерживаются. Параметры Sybase cursor не разрешены в клиентских операциях и поэтому не поддерживаются (ограничение Sybase).
- Тип данных Microsoft SQL Server cursor для выходных параметров не поддерживается. ODBC-приложения не могут вызывать любые хранимые процедуры Transact-SQL, содержащие выходной параметр cursor (ограничение Microsoft SQL Server).
- Входные параметры Oracle с типом REFCURSOR не поддерживаются (поддерживается один выходной параметр типа REFCURSOR, который отображается в набор результатов).
- Обновляемые (updatable), удерживаемые (holdable) и прокручиваемые (scrollable) курсоры в удаленных процедурах не поддерживаются. Если удаленная процедура определяет наборы результатов WITH HOLD и SCROLLABLE, атрибуты курсора (WITH HOLD и SCROLLABLE) теряются, а возвращаемый набор результатов не является удерживаемым или прокручиваемым и доступен только для чтения. Удерживаемый набор результатов разрешает приложению оставить курсор в открытом состоянии после явной или не явной фиксации транзакции. Прокручиваемый набор результатов позволяет курсору перемещаться в любую строку набора результатов.
 |
Внешний вид интегрированных процедур
Как уже упоминалось во введении, интегрированные процедуры обеспечивают прозрачность месторасположения. Будучи созданными, они могут вызываться точно так же, как и DB2-процедуры. Они придерживаются модели DB2-процедур и предоставляют гомогенный интерфейс, отображающий много специальных возможностей источников данных в DB2. Интегрированные процедуры можно создать при помощи выражения CREATE PROCEDURE (Sourced). Пример выражения CREATE PROCEDURE (Sourced), использующего необходимые операторы, был приведен в разделе "Введение". Здесь рассматриваются необязательные операторы, а также (более подробно) поведение выражения CREATE PROCEDURE (Sourced). Подробная информация о синтаксисе CREATE PROCEDURE (Sourced) приведена на странице DB2 Information Center (см. раздел "Ресурсы"). Выражение CREATE PROCEDURE (Sourced) предоставляет следующие возможности:
- Создание процедур над Oracle-функциями.
Возвращаемое значение становится первым параметром интегрированной процедуры. Это выходной параметр с именем "DEFAULT".
- Поддержка Oracle-процедур в пакете.
Выражение CREATE PROCEDURE предоставляет специальный синтаксис для Oracle-процедур в пакетах, имя из трех частей в операторе SOURCE, где средняя часть идентифицирует пакет.
- Выходные параметры Oracle REFCURSOR возвращаются как наборы данных DB2.
Примечание: Эти параметры подсчитываются в операторе NUMBER OF PARAMETERS, но они не появляются как параметры интегрированной процедуры.
- Создание процедур над процедурами перегруженных источников данных.
Это можно сделать двумя способами:
- Если две или больше процедур источников данных имеют идентичные схему и имена процедур, но различное число параметров, тогда в выражении CREATE PROCEDURE можно указать NUMBER OF PARAMETERS.
Примечание: Это количество параметров удаленной процедуры, включая параметры REFCURSOR, но не включая возвращаемое значение функции. Создаваемая интегрированная процедура может иметь различное число параметров.
- Если более одной процедуры или функции источника данных имеют одинаковое имя и число параметров, необходимо указать UNIQUE ID. Это не обязательно (и даже не поддерживается) для процедур Sybase, Microsoft SQL Server, DB2 (LUW, z/OS, iSeries). Для Oracle-процедур значением UNIQUE ID является (символьное) значение столбца ALL_ARGUMENTS.OVERLOAD, которое уникально идентифицирует процедуру.
- Объявление возвращаемых клиенту (вызывающей стороне) наборов результатов зависит от приложения. См. раздел "Наборы результатов".
- Указание атрибутов уровня доступа, детерминированности и внешних действий.
Поскольку удаленная процедура, на которую ссылается интегрированная процедура, не управляется интегрированным сервером, выбранный уровень не может быть навязан источнику данных во время выполнения процедуры. Если между указанным пользователем уровнем доступа и реальным уровнем доступа к источнику данных, реализуемого удаленной процедурой, имеется несовместимость, может возникнуть нарушение целостности данных. Если пользователь указывает эти параметры, и существует конфликт со значением параметра в источнике данных, возвращается ошибка (SQL 1255N). Если пользователь не указывает какие-либо значения для этих параметров, используется значение в источнике данных. Если этот параметр в источнике данных не доступен, значениями по умолчанию являются MODIFIES SQL DATA, NOT DETERMINISTIC и EXTERNAL ACTION.
Наборы данных
Хранимые процедуры могут возвращать ноль или более наборов результатов, каждый из которых состоит из набора строк. Хотя все строки в наборе результатов имеют одинаковое число столбцов и одинаковые типы столбцов, различные наборы данных могут иметь различные схемы столбцов. Различные источники данных существенно отличаются по способу реализации наборов результатов.
- В DB2 (LUW, z/OS, iSeries) все наборы результатов генерируются без объявления в качестве параметров. Имеется два типа наборов результатов: "RETURN TO CLIENT" и "RETURN TO CALLER". Первый тип проходит по всем вызывающим процедурам и возвращается непосредственно клиенту, а второй тип может потребляться только вызывающей стороной.
- Нормальные наборы результатов Sybase и Microsoft SQL Server не объявляются как параметры и имеют семантику, аналогичную называемой "RETURN TO CLIENT" в DB2. Для того чтобы иметь способность потреблять наборы результатов вызывающей стороной, Sybase добавляет параметры cursor, но эти параметры могут использоваться только внутри сервера Sybase, не из клиентского интерфейса Sybase, например, при интеграции. Microsoft SQL Server может возвратить курсор Transact-SQL, но хранимую процедуру Transact-SQL, содержащую выходной параметр cursor, нельзя вызывать в ODBC-приложении, таком как ODBC-упаковщик Microsoft SQL Server.
- В Oracle все наборы результатов явно объявляются как параметры типа REFCURSOR. Они должны быть явно переданы в (и из) вызывающую и вызываемую стороны.
Оператор WITH RETURN TO CALLER/CLIENT в выражении CREATE PROCEDURE помогает достичь желаемого поведения для наборов данных Sybase, Microsoft SQL Server, DB2 и Oracle. Дополнительная информация приведена в разделах "Пример" и "Определение проблем во время разработки интегрированных процедур".
Типы данных параметров и наборов результатов интегрированных процедур
- При создании интегрированных процедур типы данных параметров удаленной процедуры и столбцов набора результатов отображаются в типы данных DB2 с использованием прямого отображения типов по умолчанию.
- Типы данных, точность, масштаб и длина которых отсутствует в Oracle, отображаются в строку с максимальной длиной или в тип DOUBLE в DB2 для получения максимального диапазона во время выполнения выражения CREATE PROCEDURE.
- Пользователь может использовать выражение CREATE TYPE MAPPING для переопределения отображения типов по умолчанию для параметров процедуры. Однако отображение типов набора результатов не зависит от определенных пользователем отображений типов.
Каталоги интегрированных процедур
Если сигнатура интегрированной процедуры неизвестна, пользователи могут найти ее, используя каталоги, перечисленные ниже. В разделе "Пример" рассматриваются запросы для поиска в каталогах. Если предположить, что имя и схема интегрированной процедуры известны, нужно начинать поиск с каталога SYSCAT.ROUTINEPARMS. В разделе "Пример" приведены некоторые примеры запросов.
-
SYSCAT.ROUTINESFEDERATED - Содержит информацию об определенных вами интегрированных процедурах, такую как имя источника данных, имя удаленной процедуры, имя удаленной схемы.
-
SYSCAT.ROUTINES - Содержит информацию об определенных пользователем процедурах (табличные функции, скалярные функции и интегрированные процедуры).
-
SYSCAT.ROUTINEPARMS - Содержит информацию о параметрах процедуры для процедур, определенных в SYSCAT.ROUTINES.
-
SYSCAT.ROUTINEOPTIONS - Содержит информацию о значениях вариантов интегрированных процедур, такую как имя источника данных, имя удаленной процедуры, имя удаленной схемы и число параметров в удаленной процедуре. В случае с источником данных Oracle этот каталог содержит также число REFCURSORS.
-
SYSCAT.ROUTINEPARMOPTIONS - Содержит информацию о значениях вариантов параметров интегрированных процедур, такую как имя удаленного параметра и имя типа удаленного параметра.
Вызов интегрированной процедуры
Интегрированные процедуры активизируются при помощи выражения CALL. Целочисленные коды возврата отображаются в переменную DB2_RETURN_STATUS в выражении GET DIAGNOSTICS. Для Sybase и Microsoft SQL Server целочисленный код возврата извлекается только тогда, когда наборы результатов отсутствуют. Необходимые привилегии и пользовательские отображения аналогичны требуемым для псевдонимов:
- Пользователи используют авторизацию EXECUTE в источнике данных. Это истинно даже при непрямом вызове интегрированных процедур через локальные выражения или процедуры.
- Привилегии интегрированным процедурам можно назначить при помощи выражения GRANT/REVOKE (routine privileges).
Удаление интегрированной процедуры
- Интегрированные процедуры можно удалить при помощи выражения DROP.
- При удалении интегрированной процедуры она удаляется из локальных каталогов, каталоги в источниках данных не изменяются.
- Пакеты, зависящие от процедуры, становятся недействительными.
Примеры применения выражения DROP приведены в разделе "Пример".
Поддержка интегрированных процедур инструментальными средствами
До сих пор мы рассматривали внешний вид интегрированных процедур. Теперь перейдем к поддержке этой функциональности пользовательским интерфейсом. Имеется два интерфейса:
-
Control center - Центр управления предоставляет следующие возможности:
- Создание и удаление интегрированных процедур.
- Назначение и отмена привилегий для интегрированных процедур.
- Обнаружение удаленных процедур на основе имени удаленной схемы, имени удаленного пакета и имени удаленной процедуры.
- Ручное указание свойств удаленной процедуры.
-
DB2 Workbench - Программа DB2 Workbench позволяет выполнять процедуры, созданные с использованием Control center. Пользователи могут просматривать состояние выполнения, выходные параметры и наборы результатов.
Пример
Мы уже приводили простой пример в разделе "Введение". В следующем примере рассмотрим, как работать с перегруженными процедурами и запрашивать локальные атрибуты в каталогах. Покажем также, как воспользоваться преимуществами синтаксиса наборов результатов в выражении CREATE PROCEDURE.
Брокерская фирма XYZ имеет следующие хранимые процедуры Oracle в пакете StockOptions:
- Процедура StockInfo с входным параметром username (имя пользователя), имеющим тип char; StockInfo (username IN char). Возвращает набор результатов с имеющимися у пользователя акциями. Каждая строка набора результатов содержит два столбца:
- Сумма долей в акциях для пользователя.
- Название акций.
- В Oracle набор результатов обычно используется вызывавшей процедуру стороной.
- Процедура StockInfo, принимающая в качестве входного параметра номер счета; StockInfo (accountNum IN integer). Номер счета определяется как integer. Она возвращает набор результатов с записями:
- Сумма долей в акциях для данного счета.
- Общая сумма этих долей на основе котировок в реальном времени.
- Название акции.
- При вызове этой процедуры из Oracle вызывающая сторона обычно передает набор результатов клиенту.
В брокерской фирме определена также следующая Sybase-процедура:
- Процедура StockQuote, принимающая в качестве входного параметра название акции и дату; StockQuote(stockName IN char, transDate IN DATE). Она возвращает в качестве выходного параметра стоимость данной акции на заданную дату. Обратите внимание на то, что поскольку Sybase-процедуры позволяют пользователю передавать начальное значение для параметра OUT, DB2 рассматривает параметры OUT Sybase как параметры INOUT. В зависимости от того, как кодируется процедура, может быть обязательным или необязательным указание входного значения.
Администратор базы данных Боб создал:
- Упаковщики (оболочки) для Oracle и Sybase.
- Серверные определения: SORA для Oracle и SSYB для Sybase.
Затем Боб создает интегрированные процедуры над удаленными процедурами Oracle и Sybase. Поскольку две Oracle-процедуры имеют одинаковые названия и количество параметров, но разные сигнатуры, администратор базы данных ищет UNIQUE ID в столбце ALL_ARGUMENTS.OVERLOAD каталога Oracle System.
Для Oracle-процедуры создается интегрированная процедура ORASTOCKINFO, принимающая в качестве входного параметра username:
CREATE PROCEDURE ORASTOCKINFO SOURCE BOB.STOCKOPTIONS.STOCKINFO
UNIQUE ID '2' FOR SERVER SORA
|
Для Oracle-процедуры создается интегрированная процедура ORASTOCKVALUE, принимающая в качестве входного параметра номер счета:
CREATE PROCEDURE ORASTOCKVALUE SOURCE BOB.STOCKOPTIONS.STOCKINFO
UNIQUE ID '3' FOR SERVER SORA
WITH RETURN TO CLIENT ALL
|
Для удаленной Sybase-процедуры создается интегрированная процедура SYBSTOCKQUOTE, принимающая в качестве входного параметра название акции и дату:
CREATE PROCEDURE SYBSTOCKQUOTE SOURCE BOB.STOCKQUOTE FOR SERVER SSYB
|
Пользователи могут искать атрибуты процедуры, такие как схему процедуры, специфичное имя, количество параметров, детерминированность, внешние действия и уровень доступа, используя каталог SYSCAT.ROUTINESFEDERATED.
Атрибуты процедур ORASTOCKINFO и ORASTOCKVALUE можно найти при помощи следующего запроса:
SELECT ROUTINESCHEMA, ROUTINENAME, PARM_COUNT, DETERMINISTIC,
EXTERNAL_ACTION, SQL_DATA_ACCESS
FROM SYSCAT.ROUTINESFEDERATED
WHERE ROUTINENAME IN ('ORASTOCKINFO', 'ORASTOCKVALUE')
ROUTINESCHEMA ROUTINENAME PARM_COUNT DETERMINISTIC EXTERNAL_ACTION SQL_DATA_ACCESS
------------- ------------- ---------- ------------- --------------- ---------------
BOB ORASTOCKINFO 1 N E M
BOB ORASTOCKVALUE 1 N E M
E = Procedure has external action
|
routineschema - это имя схемы интегрированной процедуры, а не процедуры источника данных.
Атрибуты процедуры SYBSTOCKQUOTE можно найти при помощи следующего запроса:
SELECT ROUTINESCHEMA, SPECIFICNAME, PARM_COUNT, DETERMINISTIC,
EXTERNAL_ACTION, SQL_DATA_ACCESS
FROM SYSCAT.ROUTINESFEDERATED
WHERE ROUTINENAME = 'SYBSTOCKQUOTE'
ROUTINESCHEMA SPECIFICNAME PARM_COUNT DETERMINISTIC EXTERNAL_ACTION SQL_DATA_ACCESS
------------- ------------------ ---------- ------------- --------------- ---------------
BOB SQL060412112907000 2 N E M
|
Такую информацию о параметрах, как порядковый номер, имя параметра, режим параметра, тип параметра, можно найти при помощи каталога SYSCAT.ROUTINEPARMS.
Информация о параметрах процедур ORASTOCKINFO и ORASTOCKVALUE может быть найдена при помощи следующего запроса:
SELECT CHAR(ROUTINENAME,30) AS ROUTINENAME, ORDINAL, CHAR(PARMNAME,30) AS NAME,
ROWTYPE, CHAR(TYPENAME,30) AS TYPE
FROM SYSCAT.ROUTINEPARMS
WHERE ROUTINENAME IN ('ORASTOCKINFO', 'ORASTOCKVALUE') AND ROUTINESCHEMA ='BOB'
ORDER BY ROUTINENAME, ORDINAL;
ROUTINENAME ORDINAL NAME ROWTYPE TYPE
-------------- ------- --------------- -------- -----------
ORASTOCKINFO 1 USERNAME P VARCHAR
ORASTOCKVALUE 1 ACCTNUM P DOUBLE
P = Input Parameter
|
Информация о параметрах процедуры SYBSTOCKQUOTE может быть найдена при помощи следующего запроса:
SELECT ORDINAL, CHAR(PARMNAME,30) AS NAME,ROWTYPE,
CHAR(TYPENAME,30) AS TYPE
FROM SYSCAT.ROUTINEPARMS
WHERE ROUTINENAME= 'SYBSTOCKQUOTE' AND ROUTINESCHEMA ='BOB'
ORDER BY ORDINAL;
ORDINAL NAME ROWTYPE TYPE
------- --------------- -------- -----------
1 STOCKNAME P CHAR
2 DATE P TIMESTAMP
3 STOCKVAL B DOUBLE
P = Input Parameter
O = Output Parameter
B = InOut Parameter
|
Теперь предположим, что имеется UDB-процедура computeStockValue(), которая:
- Принимает в качестве входных параметров имя пользователя и дату.
- Вызывает интегрированную процедуру для Oracle OraStockInfo().
- Для каждого названия акции, возвращаемого из OraStockInfo(), она вызывает интегрированную процедуру SybStockQuote, передавая название акции и дату.
- На основе возвращенной из процедуры SybStockQuote котировки акции она вычисляет рыночную стоимость путем перемножения доли и котировки акции.
Рисунок 5. UDB-процедура, вызывающая процедуры OraStockInfo() и SybStockQuote()
Предположим, что есть еще одна UDB-процедура GetStockValue(), которая:
- Принимает в качестве входного параметра номер счета.
- Вызывает интегрированную процедуру OraStockValue().
Поскольку в процедуре OraStockValue() определен набор результатов, возвращаемых клиенту, UDB-процедура не может обратиться к ним, и значение долей для данного номера счета возвращается приложению путем вызова UDB-процедуры.
Рисунок 6. UDB-процедура GetStockValue(), вызывающая OraStockValue()
Если пользователи хотят удалить интегрированные процедуры, которые вы только что создали, они могут использовать выражение DROP.
В данном примере, поскольку процедуры не перегружены, вы можете удалить процедуру ORASTOCKINFO, используя ее имя.
DROP PROCEDURE ORASTOCKINFO
|
Если бы существовала еще одна процедура ORASTOCKINFO с другим количеством параметров, вы могли бы использовать для удаления процедуры ее сигнатуру или специфичное имя. То есть выражение DROP могло бы выглядеть так:
DROP PROCEDURE ORASTOCKINFO(VARCHAR(10))
|
либо так:
DROP SPECIFIC PROCEDURE SQL060412112756100
|
Определение проблем во время проектирования интегрированных процедур
При разработке приложений имеется несколько общих проблем, о которых нужно знать. В данном разделе приведены некоторые советы по устранению проблем, которые могут возникнуть во время проектирования интегрированных процедур.
Для ошибки SQL1822N содержимое сообщения, получаемого от удаленного источника данных, часто приходит урезанным. Полное сообщение об ошибке можно найти в файле db2diag.log. Например:
call fstp(20)
SQL1822N Unexpected error code "6550" received from data source "SERV".
Associated text and tokens are "ORA-06550: line 1, column 7: PLS-00201:
identifier 'J15USER1". SQLSTATE=560BD
|
Эта ошибка генерируется, если удаленная Oracle-процедура удалена. db2diag.log содержит полное сообщение об ошибке:
db2diag -filter 'function:=error_report'
2006-04-24-16.56.44.033504-420 E928575A670 LEVEL: Error
PID : 7000234 TID : 1 PROC : db2agent (FSPDB)
INSTANCE: phu NODE : 000 DB : FSPDB
APPHDL : 0-7 APPID: *LOCAL.phu.060424235433
AUTHID : NEWTON
FUNCTION: DB2 UDB, net8 wrapper, Net8_Statement::error_report, probe:10
DATA #1 : String, 28 bytes
Oracle Error - Current User
DATA #2 : String with size, 6 bytes
NEWTON
DATA #3 : String, 22 bytes
Oracle Error Received
DATA #4 : String, 142 bytes
ORA-06550: line 1, column 7:
PLS-00201: identifier 'J15USER1.ORA_STP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
|
Создание интегрированной процедуры
Если во время выполнения выражения CREATE PROCEDURE генерируется ошибка SQL1253N (The source procedure specified in the CREATE PROCEDURE (Sourced) statement was not found at the data source (Исходная процедура, указанная в выражении CREATE PROCEDURE (Sourced) в источнике данных не обнаружена)), вы можете выполнить приведенные далее запросы к таблицам удаленного каталога для проверки информации об удаленных процедурах.
Для Oracle-процедур в пакете используйте следующий запрос к серверу Oracle в режиме passthru:
select owner, package_name, object_name, overload, parm_count
from (
select owner, package_name, object_name, overload,
sum(case when data_type is null then 0 else 1 end) as parm_count
from sys.all_arguments
where data_level = 0
group by owner, package_name, object_name, overload
) aa
where object_name = '<remote_proc_name>' and
package_name = '<remote_package>' and
owner = '<remote_schema>' and
overload = '<unique_id>' and -- optional
parm_count = <num_of_parameters> -- optional
|
Используйте запрос, указанный ниже, для Oracle-процедур вне пакета:
select object_name, object_type, status
from sys.all_objects
where owner = '<remote_schema>' and
object_name = '<remote_proc_name>' and
object_type in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
|
Для Sybase-процедур используйте следующий запрос к серверу Sybase в режиме passthru:
select id
from dbo.sysobjects
where id = object_id('<remote_schema>.<remote_proc_name>') and
(type = 'P' or type ='XP')
|
Для процедур Microsoft SQL Server используйте следующий запрос к серверу Microsoft SQL в режиме passthru:
select specific_name
from information_schema.routines
where specific_schema= '<remote_schema>' and
specific_name = '<remote_proc_name>' and
routine_type = 'PROCEDURE'
|
Для процедур DB2 for LUW используйте следующий запрос к серверу DB2 LUW в режиме passthru:
select routinename
from syscat.routines
where ROUTINENAME = '<remote_proc_name>' and
ROUTINESCHEMA = '<remote_schema>' and
ROUTINETYPE = 'P' and
PARM_COUNT = <remote_proc_parm_num>
|
Для процедур DB2 for z/OS используйте следующий запрос к серверу DB2 z/OS в режиме passthru:
select name
from sysibm.sysroutines
where NAME = '<remote_proc_name>' and
SCHEMA = '<remote_schema>' and
ROUTINETYPE = 'P' and
PARM_COUNT = <remote_proc_parm_num>
|
Для процедур DB2 for iSeries используйте следующий запрос к серверу DB2 iSeries в режиме passthru:
select routine_name
from qsys2.sysroutines
where ROUTINE_NAME = '<remote_proc_name>' and
ROUTINE_SCHEMA = '<remote_schema>' and
ROUTINE_TYPE = 'PROCEDURE' and
(IN_PARMS + OUT_PARMS + INOUT_PARMS = <remote_proc_parm_num>)
|
Если какой-либо из приведенных выше запросов не возвращает желаемую удаленную процедуру, во время выполнения CREATE PROCEDURE генерируется ошибка SQL1253N.
Пример
Используя Oracle в качестве примера, рассмотрим следующее SQL-выражение:
CREATE PROCEDURE FSTP1 SOURCE "J15USER1"."ORA1"
NUMBER OF PARAMETERS 3
FOR SERVER SERV
|
Возвращается следующая ошибка, указывающая на то, что процедура с таким именем не обнаружена:
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL1253N The source procedure with the name "J15USER1.ORA_1"
was not found at the data source. Reason code = "5". LINE NUMBER=1.
SQLSTATE=42883
|
Первым действием является использование приведенного выше запроса для проверки существования процедуры в таблицах каталога Oracle. Если выполнить следующий запрос в режиме passthru, он, вероятно, не возвратит никаких строк:
select owner, package_name, object_name, overload, parm_count
from (
select owner, package_name, object_name, overload,
sum(case when data_type is null then 0 else 1 end) as parm_count
from sys.all_arguments
where data_level = 0
group by owner, package_name, object_name, overload
) aa
where object_name = 'ORA1' and
package_name IS NULL and
owner = 'J15USER1' and
parm_count = 3
OWNER PACKAGE_NAME OBJECT_NAME OVERLOAD PARM_COUNT
-------- ------------ ----------- -------- ----------
0 record(s) selected.
|
Для определения причины того, почему процедура не была обнаружена, придется, возможно, изменить запрос и удалить некоторые из критериев поиска. Сначала удалим условие для названия пакета:
select owner, package_name, object_name, overload, parm_count
from (
select owner, package_name, object_name, overload,
sum(case when data_type is null then 0 else 1 end) as parm_count
from sys.all_arguments
where data_level = 0
group by owner, package_name, object_name, overload
) aa
where object_name = 'ORA1' and
owner = 'J15USER1' and
parm_count = 3
OWNER PACKAGE_NAME OBJECT_NAME OVERLOAD PARM_COUNT
-------- ------------ ----------- -------- ----------------------
J15USER1 NEWTON_PACK1 ORA1 2 +3.00000000000000E+000
1 record(s) selected.
|
Теперь видна искомая процедура и становится ясно, что нужно указывать название пакета. Исправленное выражение на языке определения данных (Data Definition Language - DDL) работает:
CREATE PROCEDURE FSTP1 SOURCE "J15USER1"."NEWTON_PACK1"."ORA1"
NUMBER OF PARAMETERS 3
FOR SERVER SERV
DB20000I The SQL command completed successfully.
|
Вызов интегрированной процедуры
Интегрированная процедура может применяться в большинстве ситуаций, когда могла бы использоваться DB2-процедура. Однако существует несколько ограничений, специфичных для вызовов интегрированных процедур. Если вызов интегрированной процедуры заканчивается неудачей, проверьте, не является ли это следствием следующих причин:
- Если процедура имеет уровень доступа READS SQL DATA или MODIFIES SQL DATA, она блокируется во внешних UDF, и возвращается ошибка SQL20136.
- Если процедура имеет уровень доступа MODIFIES SQL DATA, она блокируется в триггерах, динамических составных выражениях, функциях в теле SQL и если были созданы точки сохранения. Возвращается ошибка SQL30090 с кодом причины 22.
- Если удаленная процедура возвращает неподдерживаемый тип набора результатов, например, LOB или XML, CALL возвращает ошибку SQL3324N. В этом случае удаленные процедуры выполняются, но набор результатов не возвращается.
- Если возвращается ошибка SQL30090 с кодом причины 21, причины могут быть разными, например, попытка создать интегрированную процедуру в изолированном упаковщике, или, в режиме интегрированной двухфазной фиксации, Oracle-процедура содержит фиксацию транзакции.
Для объяснения выражения CALL можно использовать программу db2expln. Предполагая, что выражением CALL является CALL fstp1(10,?), покажем выводимые программой db2expln данные:
db2expln -d fspdb -statement "CALL fstp1(10,?)" -terminal
******************** DYNAMIC ***************************************
==================== STATEMENT ===========================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"SYSIBMADM", "NEWTON"
Statement:
CALL fstp1(10, ?)
Section Code Page = 819
Estimated Cost = 0.004800
Estimated Cardinality = 0.000000
Return Data to Application
| #Columns = 1
Federated Procedure #1:
Server: SERV (ORACLE 9.1.1)
SQL Statement:
BEGIN "J15USER1" ."NEWTON_PACK1" ."ORA1" ( +1.00000000000000E+001,
:H0 , :H1 ); IF :H0%isopen
then :R0 :=1;
else :R0 :=0; END IF; END;
End of section
|
Примечание: Удаленное выражение имеет некоторую дополнительную логику, используемую внутренне для проверки пустоты наборов результатов. В нем также показан способ преобразования параметров.
Проблемы транзакций для выражений CALL
Выражения CALL, точно так же как и другие DML, принадлежат области видимости определенных транзакций. Источник данных интегрированной процедуры рассматривается как участник транзакции; если SQL-выражение выполняется неудачно, это не оказывает влияния на другие SQL-выражения, выполняемые до или после неудачного SQL-выражения.
Удаленные процедуры, используемые в интегрированных процедурах, не должны фиксировать или выполнять откат текущей транзакции, поскольку это может привести к нарушению целостности данных. Интегрированная база данных не может гарантировать в данном случае транзакционное поведение. Для DRDA (Linux, UNIX и Windows, z/OS и iSeries) удаленная процедура выполняется неудачно в среде интегрированных двухфазных фиксаций транзакций, и на интегрированный сервер передается ошибка. Oracle-процедуры будут выполняться неудачно, если имеется фиксация транзакции в процедуре источника данных, однако они будут выполняться успешно, если в Oracle-процедуре есть откат транзакции. Источник данных выполняет откат всех выражений, выполненных до этого выражения ROLLBACK с этим же источником данных, но на интегрированном сервере или в других источниках данных. Для Microsoft SQL Server и Sybase фиксация или откат транзакции в процедуре источника данных может вызвать нарушение целостности данных даже в среде интегрированных двухфазных транзакций. Таким образом, транзакционное поведение зависит от процедуры источника данных, и интегрированная процедура не может управлять этим типом удаленных процедур.
Использование наборов результатов
Если набор результатов не возвращается клиенту или вызвавшей стороне, это может быть следствием одной из следующих причин:
- Если интегрированная процедура вызывается локальной DB2-процедурой, этой DB2-процедурой могут извлекаться только наборы результатов, определенные как "return to caller" (по умолчанию), а приложением могут извлекаться только наборы результатов, определенные как "return to client". Эти определения указываются в выражении CREATE PROCEDURE для интегрированной процедуры, а не в источнике данных.
- Интегрированная процедура вызывается в триггере или определенной пользователем функции.
- Интегрированная процедура не определяется корректно в выражении CREATE PROCEDURE (Sourced).
- Приложение написано с использованием встроенного языка SQL. Проверьте также, имеет ли клиент какие-либо другие ограничения на обработку набора результатов.
- Как упоминалось в разделе "Ограничения", удерживаемые наборы результатов не поддерживаются. Если набор результатов был сгенерирован в предыдущей транзакции, явная или неявная операция фиксации транзакции закрывает набор результатов.
- Интегрированные процедуры возвращают только первый набор результатов. Если порядок наборов результатов не всегда одинаковый, интегрированная процедура может возвратить различные наборы результатов, в зависимости от того, какой из них является первым. Например, удаленная процедура A в источнике данных может быть определена как:
create procedure A ()
begin
declare cur1 cursor with return to client
for select * from t;
open cur1
end
|
Удаленная процедура B в источнике данных может быть определена как:
create procedure B (arg1 int)
begin
declare cur2 cursor with return to client
for select * from t;
if (arg1 < 10) then
call A();
end if;
open cur2;
end
|
Для удаленной процедуры B создается интегрированная процедура FedP1:
CREATE PROCEDURE FEDP1 SOURCE B
FOR SERVER S1
NUMBER OF PARAMETERS 1
WITH RETURN TO CLIENT ALL
|
Имеется локальная UDB-процедура P1, активизирующая интегрированную процедуру и определенная как:
CREATE PROCEDURE P1 (ARG1 INT)
BEGIN
CALL FEDP1(ARG1)
END
|
Затем приложение вызывает UDB-процедуру как:
CALL P1(1); - Будет возвращаться набор результатов CUR1, CUR2 не возвращается.
CALL P1(20); - Будет возвращаться набор результатов CUR2.
|
|
Перспективы
В готовящейся к выходу версии V9.5 мы реализовали следующие улучшения:
- Для Sybase-процедур поддерживается изолированный упаковщик.
- Для Oracle-процедур предоставляется поддержка ALTER PROCEDURE для изменения типов данных параметров.
Ресурсы Научиться
Получить продукты и технологии
Обсудить
Об авторах | | |
Димпл Бхатиа (Dimple Bhatia) пришла в IBM в 2000 году и в настоящее время работает над технологией интегрирования в отделе Websphere Federation Server. До этого она занималась проектом Data Warehousing в Business Intelligence. Она работала в Lucent Technologies, NJ до прихода в IBM. Получила степень магистра по вычислительным системам в Syracuse University, NY. |
| | |
Куинлонг (Питер) Ху (Qinglong (Peter) Hu) пришел в IBM в 2000 и в настоящее время работает над технологией интегрирования. Занимался также такими темами как хранимые процедуры, federated passthru SQL, 2PC, Data Links и производительность DB2 Everyplace. До прихода в IBM работал научным сотрудником в Waterloo University в Канаде и занимался управлением данными для мобильных вычислений. Получил степень доктора по вычислительной технике в Hong Kong University of Science " Technology, Гонконг, Китай. |
| | |
Ханс Зеллер (Hans Zeller) пришел в IBM в отдел по интегрированным исследованиям в 2004. Работал над встроенными процедурами, асинхронностью, производительностью и функциональностью 2PC для WebSphere Federation Server V9. До этого 15 лет работал в Tandem, Compaq и HP над параллельными и масштабируемыми системами управления базами данных и оптимизацией запросов. Получил степень доктора по вычислительной технике в University of Stuttgart, Германия. |
Выскажите мнение об этой странице
| |
