Способы работы с БД DB2 в языке RPG на платформе IBM i
Введение
В одной из предыдущих статей уже давал краткий обзор языка RPG на платформе DB2. В другой приводил пример одной из задач, которые приходится решать при помощи этого языка.
Сейчас хотелось бы подробнее описать возможности, предоставляемые языком для работы с БД на этой платформе.
Следует отметить, что платформа IBM i является «коробочным» решением для бизнес-задач. Т.е. покупается сервер сразу с установленной ОС, которая содержит в себе все необходимое. Здесь нет отдельно ОС и отдельно сервера БД — база данных является часть ОС, интегрирована в нее. Равно как и компиляторы основных языков — RPG, COBOL, C/C++, CL… Любое обновление ОС (TR — Technology Refresh) обычно содержит в себе обновления как ОС, так и БД, компиляторов и много всего.
Исторически работа с БД развивалась двумя командами в двух направлениях — одна команда развивала работу с БД посредством прямого доступа — позиционирование на запись, чтение, запись, удаление и описание структуры БД посредством DDS — Data Definition Specifications. Вторая команда развивала SQL и описание структуры БД средствами DDL.
Тут есть небольшая разница в терминологии. В SQL используется стандартная терминология БД — таблицы, индексы и т.п. В DDS несколько иначе. Вместо таблицы используется термин «физический файл» — хранилище данных. Вместо индексов — «логический файл» который определяет «путь доступа» (access path) к записям в таблице.
Access Path
По определению в документации определяет порядок в котором записи будут читаться из файла. Для физических файлов это порядок, в котором записи расположены в файле (в порядке увеличения RRN). С учетом переиспользования свободных мест (например, удаленных записей) он может отличаться от хронологического порядка добавления записей в файл. Для логических файлов этот порядок задается порядком сортировки записей по набору ключевых полей.
В простейшем случае логический файл тождественен индексу, но на самом деле возможности его шире — он может содержать вычисляемые поля, это может быть логический join файл, описывающий связи между несколькими таблицами и т.п.
В итоге RPG, кроме встроенных средств прямого доступа к БД, получил еще SQL препроцессор и возможность вставлять SQL выражения непосредственно в RPG код оператором exec sql …
Для «чистого» RPG тип исходного элемента устанавливается как RPGLE (ILE RPG), а для элемента на RPG с использованием встроенного SQL — SQLRPGLE. Соответственно, для первого компиляция программы выполняется командой CRTBNDRPG, для второго — CRTSQLRPGI (эта команда сначала вызовет SQL препроцессор и только потом уже компилятор RPG).
В нашей практике используются оба способа доступа к БД. Каждый из них имеет свои преимущества и недостатки.
Как и в предыдущей статье, нет претензий на полное описание всех аспектов работы с БД — это было бы слишком объемно для данного формата, только описание наиболее используемых сценариев. Также следует иметь ввиду, что все это используется там, где на первом (втором и третьем…) месте стоит эффективность использования ресурсов и скорость выполнения и где каждая поставка проходит обязательное нагрузочное тестирование на копии промсреды и может быть возвращена на доработку с заключением типа
Из PEX статистики работы *** видно, что 33% времени и 36% ресурсов CPU тратится на выполнение QSQRPARS в программе ***, т.е. парсинг статических выражений при подготовке SQL запроса,
Поскольку *** один из наиболее активно используемых сервис модулей, необоснованное повышенное ресурсопотребление является малодопустимым. Просьба инициировать доработку ***.
Прямой доступ средствами RPG (RPG op-codes)
В этом режиме работа идет напрямую с файлами. Файл рассматривается как набор записей (атомарных элементов). Работать можно как с физическим файлом, так и с логическим. В первом случае доступ к файлу осуществляется в порядке расположения записей в нем, во втором порядок доступа определяется набором ключевых полей и условиями сортировки.
Прежде чем работать с файлом, нужно его объявить как переменную:
dcl-f RDK01LF disk(*ext)
usage(*input)
block(*yes)
usropn
keyed
qualified
static;В данном примере (и обычно) работать будем с логическим файлом который представляет собой обычный уникальный индекс для таблицы RDRPF по полям RDKCUS, RDKCLC, RDKUCD, RDKOPN. На DDS он описан так:
A UNIQUE
A R RDKPFR PFILE(RDKPF)
A K RDKCUS
A K RDKCLC
A K RDKUCD
A K RDKOPNФормат записи RDKPFR заимствуется из связанного физического файла RDKPF (в общем случае физический файл может содержать более одного формата записи), 4 ключевых поля, никаких дополнительных условий.
В данном случае имя переменной совпадает с именем файла.
Используемые модификаторы:
disk (*ext) говорит о том, что это внешний дисковый файл. Если имя переменной отличается от имени файла, потребовались бы еще модификаторы extfile (*extdesc) и extdesc (Datafile) где Datafile — переменная, константа или строковый литерал с именем файла. В данном случае это не требуется.
usage (*input) — режим работы с файлом. В данном случае — только чтение. Возможные варианты — *input (чтение), *output (запись), *update (изменение) и *delete (удаление) в любых комбинациях
block (*yes) — режим блочного чтения. Режим работает для файлов открытых только на чтение (при соблюдении ряда дополнительных условий о которых речь пойдет ниже). Снижает количество физических обращений к диску за счет чтения сразу нескольких последовательных (по пути доступа) записей в буфер. Повышает производительность в тех случаях, когда требуется последовательное чтение нескольких записей.
usropn — управление открытием файла. В данном случае требуется «ручное» открытие — явный вызов команды open. Без этого модификатора файл будет открываться автоматически как только описанная переменная попадает в область видимости (что не очень хорошо для Hi-Load систем т.к. может приводить к излишним затратам на постоянное открытие/закрытие файлов).
keyed — поскольку в данном случае работаем с логическим файлом, этот модификатор говорит о том, что порядок доступа к записи определяется набором ключевых полей, а не физическим порядком следования записей в хранилище (физическом файле)
qualified — достаточно интересный модификатор. По умолчанию (без него) компилятор автоматически создает набор переменных в той же области видимости, имена и типы которых совпадают с полями записи в файле. Эти переменные автоматически заполняются при операции чтения и должны быть заполнены перед операциями записи/изменения. В случае, когда есть необходимость одновременно работать с несколькими логическими файлами, связанными с одним физическим, это вызывает определенные неудобства т.к. требует дополнительных модификаторов prefix (добавление префикса к именам полей) и rename (переименование имени формата записи) дабы избежать ошибки двойного определения переменных с одинаковым именем. Данный модификатор отключает создание таких переменных, но требует объявления структуры, совпадающей с форматом записи, которая будет использоваться как буфер для операций с файлом.
static — просто static. Как и для любой другой переменной. Используется при объявлении файла локальной переменной внутри процедуры. Косвенно связано с usropn — без этих модификаторов при каждом входе в процедуру файл будет автоматически открываться (вошли в область видимости), а при выходе [из области видимости/процедуры] автоматически же закрываться. Что приводит к ощутимому снижению производительности. В нашей практике файл открывается один раз, перед первым к нему обращению и остается открытым на все время жизни группы активации (далее — ГА) в которой он был открыт. Для этого и используются модификаторы usropn и, для локальных внутри процедуры файлов, static. При закрытии ГА все выделенные в ней ресурсы освободятся автоматически, в том числе закроются все открытые файлы (утечек ресурсов гарантированно не будет).
Чтобы работать с qualified файлами необходимо определить структуру, которая будет использоваться в качестве буфера.
dcl-ds dsRDK likerec(RDK01LF.RDKPFR: *all);Здесь определяется структура данных dsRDK «такая же как» (likerec) структура формата записи RDKPFR в логическом файле RDK01LF (RDK01LF.RDKPFR). Структура будет содержать все (*all) поля записи — имена и типы элементов структуры будут совпадать с именами и типами полей записи.
Если нам нужна отдельная структура, содержащая только ключевые поля, можно описать ее так:
dcl-ds dsRDK01Key likerec(RDK01LF.RDKPFR: *key);Т.е. вместо *all используем *key.
Прежде чем работать с usropn файлом, нужно убедиться что он открыт. Стандартная процедура:
if not %open(RDK01LF);
open RDK01LF;
endif;Тут следует обратить внимание что есть BIF (Built-In-Function) %open которая возвращает логическое значение *on (true) если файл уде открыт или *off (false) в противном случае, и есть оператор (op-code) открытия файла open.
Такая проверка вставляется для всех локальных (внутри процедуры) usropn static файлов — при первом входе в процедуру файлы откроются и будут открытыми все время жизни ГА в которой работает данный модуль.
Позиционирование на запись
RPG предлагает две операции позиционирования на запись — SetLL и SetGT. Формат одинаковый
op-code[SetLL | SetGT] Первая операция устанавливает указатель перед первой записью в пути доступа, значение ключа которой равно или больше заданному (после последней записи с меньшим значением ключа). Для проверки попали ли на запись с точным значением ключа есть логическая BIF %equal — возвращает *on если значение ключа совпадает или *off если значение ключа больше заданного.
Вторая операция наоборот, устанавливает указатель после последней в пути доступа записью со значением ключа меньше или равном заданному (перед первой записью с большим значением ключа). %equal тут не работает (точнее, всегда *off).
Значение ключа может быть не полным. Например, для упомянутого выше файла с ключевыми полями
A K RDKCUS
A K RDKCLC
A K RDKUCD
A K RDKOPNможно использовать позиционирование на первую запись для заданного значения поля RDKCUS:
SetLL CUS RDK01LF;Если мы работаем со структурой, содержащей набор ключевых полей
dcl-ds dsRDK01Key likerec(RDK01LF.RDKPFR: *key);то в качестве аргумента используется BIF %kds
SetLL %kds(dsRDK01Key: 2) RDK01LF;второй (необязательный) аргумент %kds (2 в данном случае) указывает сколько первых полей структуры (ключа) использовать для поиска (в данном примере — первые два поля).
В качестве значения ключа могут использоваться мнемоники. Например, для SetLL и keyed файла
SetLL *loval RDK01LF;означает установку указателя на начало последовательности доступа (перед самой первой записью). Аналогично для установки в конец последовательности используется
SetGT *hival RDK01LF;Если работаем напрямую с физическим файлом, не keyed, возможности поиска по ключу, естественно, отсутствуют. Там можно устанавливать только на начало
SetLL *start PDKPF;или конец
SetGT *end RDKPF;файла.
Важно иметь ввиду, что в общем случае ни SetLL, ни SetGT не читают саму запись. Они только позиционируются на нужное место. Но SetLL можно использовать для быстрой проверки наличия записи в таблице когда ее содержимое не важно, только факт наличия.
Например, нужно проверить, существует ли в таблице RDKPF хотя бы одна запись для RDKCUS = 'AAAAAA'
SetLL ('AAAAAA') RDK01LF;
if %equal(RDK01LF);
// запись существует
else;
// записи таким значением RDKCUS в таблице нет
endif;Операции чтения записи
RPG предоставляет набор операций для чтения записи из физического файла. Все они (кроме chain) предполагают, что внутренний указатель уже спозиционирован предшествующим вызовом SetLL/SetGT. Позиционирование осуществляется «между записями» т.е. вызов операции чтения читает запись в буфер и после этого перемещает указатель вперед (или назад) так что он оказывается между прочитанной и следующей за ней (или предыдущей перед ней) записью. Если таковой нет (уперлись в начало или конец файла), очередная операция чтения установит флаг EOF (BIF %eof будет возвращать *on).
Есть 4 модификации функции Read
Read — чтение записи «вперед» — читается запись, перед которой установлен указатель, указатель после этого перемещается в положение перед следующей записью. Как правило, используется в сочетании с SetLL.
ReadP — чтение записи «назад» — читается запись, после которой установлен указатель, указатель после этого перемещается в положение после предыдущей записи. Как правило, используется в сочетании с SetGT.
ReadE — чтение записи «вперед» с проверкой значения ключа (только для keyed файлов) — работает как Read, но если значение ключа прочитанной записи отличается о заданного, будет установлен статус EOF.
ReadPE — чтение записи «назад» с проверкой значения ключа (только для keyed файлов) — работает как ReadP, но если значение ключа прочитанной записи отличается о заданного, будет установлен статус EOF.
Если файл объявлен как не qualified, аргументом для операций чтения достаточно указать только имя файла
read RDK01LF;При этом в случае успешного чтения будут заполнены автоматически созданные переменные, соответствующие полям записи.
И даже для не qualified файла можно читать в структуру — буфер, объявленную как likerec. Но для этого вместо имени файла нужно указывать на имя файла, а имя формата записи
read RDKPFR dsRDK;При этом автоматически созданные переменные не заполняются.
Если же файл объявлен как qualified, необходимо указывать и полное имя формата записи (qualified name) и буфер, связанный с этим форматом через likerec
read RDK01LF.RDKPFR dsRDK;В случае использования функций с проверкой ключа ReadE/ReadPE необходимо указывать также значение ключа.
reade (CUS) RDK01LF.RDKPFR dsRDK;Ключ задается точно также, как в SetLL/SetGT.
Например, нужно прочитать все записи из RDKPF для значения RDKCUS = 'AAAAAA'
// Устанавливаем указатель перед первой записью на нужным значением ключа
setll ('AAAAAA') RDK01LF;
// Читаем первую запись
read RDK01LF.RDKPFR dsRDK;
// Далее, читаем записи по порядку сортировки с проверкой на конец файла и совпадение ключа
dow not %eof(RDK01LF) and dsRDK.RDKCUS = 'AAAAAA';
// обрабатываем очередную запись
...
// И читаем следующую
read RDK01LF.RDKPFR dsRDK;
enddo;Тоже самое, но с использованием ReadE
// Устанавливаем указатель перед первой записью на нужным значением ключа
setll ('AAAAAA') RDK01LF;
// Читаем первую запись
reade ('AAAAAA') RDK01LF.RDKPFR dsRDK;
// Далее, читаем записи по порядку сортировки с проверкой на конец файла
// совпадение ключа отдельно проверять не надо - это сделает ReadE
dow not %eof(RDK01LF);
// обрабатываем очередную запись
...
// И читаем следующую
reade ('AAAAAA') RDK01LF.RDKPFR dsRDK;
enddo;Казалось бы, ReadE использовать удобнее. «Но есть нюансы» ©.
Тут надо вспомнить про то самое блочное чтение, которое включается модификатором block (*yes) в определении файла. Нюанс в том, что этот режим работает только в том случае, если операции с данным файлом ограничиваются SetLL и Read. Если компилятор «увидит» для этого файла в области его видимости используются какие-то иные операции (ReadE, ReadP, ReadPE или Chain) — этот модификатор будет проигнорирован (с соответствующим предупреждением в листинге компилятора).
Hint
Если в одном модуле (программе) в разных местах требуется как массовое чтение записей через SetLL + Read, так и чтение одной записи по точному значению ключа через Chain, лучше разнести эти операции по разным процедурам и в каждой определить свой локальный экземпляр файла.
Как вариант, можно использовать описанный ниже эквивалент Chain — SetLL + %equal + Read
Два реальных примера со снятием статистик производительности при помощи PEX (Performance EXplorer). В обоих случаях просто проходим по файлу вычитывая записи для заданных значений колюча
SetLL + Read
dcl-f ECA10LF disk(*ext)
usage(*input)
block(*yes)
usropn
keyed
qualified
static;
dcl-ds dsECA likerec(ECA10LF.ECAPFR: *all);
dcl-s LST char(5);
if not %open(ECA10LF);
open ECA10LF;
endif;
for-each LST in %list('PPT': 'OMU': 'PE');
setll (LST) ECA10LF;
read ECA10LF.ECAPFR dsECA;
dow not %eof(ECA10LF) and dsECA.ECALST = LST;
read ECA10LF.ECAPFR dsECA;
enddo;
endfor;Пример синтетический — никакой обработки данных тут нет, только чтение. Делалось специально для снятия сравнительных PEX статистик для Read и ReadE.
Тоже самое, но с использованием SetLL + ReadE
dcl-f ECA10LF disk(*ext)
usage(*input)
usropn
keyed
qualified
static;
dcl-ds dsECA likerec(ECA10LF.ECAPFR: *all);
dcl-s LST char(5);
if not %open(ECA10LF);
open ECA10LF;
endif;
for-each LST in %list('PPT': 'OMU': 'PE');
setll (LST) ECA10LF;
reade (LST) ECA10LF.ECAPFR dsECA;
dow not %eof(ECA10LF);
reade (LST) ECA10LF.ECAPFR dsECA;
enddo;
endfor;Здесь нет block (*yes) в определении файла — все равно он будет проигнорирован компилятором т.к. для файла применяется ReadE.
Каждый блок был оформлен отдельной процедурой (TSTREAD и TSTREADE и вызывался в одной программе 100 раз (стандартный подход для получения сравнительной статистики).
И вот такой результат
_QRNX_DB_READ и _QRNX_DB_READE — внутренние процедуры физического чтения с диска.
Что видим? При одинаковом количестве вызовов Read/ReadE, количество физических обращений к диску для Read в 8 раз меньше (601 201 против 5 406 300) чем для ReadE.Т. е. ReadE обращается к диску за каждой записью, а Read читает сразу по 8 записей и со 2-й по 8-ю берет из внутреннего кеша. Соответственно, для ReadE и потребление ресурсов процессора и время выполнения больше. Если смотреть в абсолютных цифрах, то для Read
для ReadE
Таким образом, с точки зрения производительности и ресурсоэффективности использование Read с «ручной» проверкой значения ключа является предпочтительным.
Говоря об операциях чтения записи следует упомянуть также операцию Chain — чтение одиночной записи по точному значению ключа (читает первую запись с заданным значением ключа в пути доступа).
Использование для qualified и не qualified файлов точно такое же как ReadE с той разницей, что chain не требует предварительной установки указателя посредством SetLL/SetGT.
Если запись найдена и прочитана, устанавливается флаг Found (BIF %found).
Таким образом, chain
chain (CUS) RDK01LF.RDKPFR dsRDK;
if %found(RDK01LF);
// запись найдена и прочитана
endif;полностью эквивалентно
setll (CUS) RDK01LF;
if %equal(RDL01LF);
read RDK01LF.RDKPFR dsRDK;
// запись найдена и прочитана
endif;По эффективности также нет существенных различий.
Ну и, наконец, пример из реальной жизни. Получение записи с максимальным значением.
Есть индекс HDA02LF
A UNIQUE
A R HDAPFR PFILE(HDAPF)
A K HDACUS
A K HDACLC
A K HDATYP
A K HDACRD
По логике требуется найти запись с максимальным значением HDACRD для заданных HDACUS, HDACLC и HDATYP
dcl-f HDA02LF disk(*ext)
usage(*input)
keyed
usropn
qualified
static;
dcl-ds dsHDARec likerec(HDA02LF.HDAPFR: *all);
setgt (CUS: CLC: Typ) HDA02LF;
readpe (CUS: CLC: Typ) HDA02LF.HDAPFR dsHDARec;
if not %eof(HDA02LF);
// запись найдена и прочитана
endif;Аналогично для случая, когда нужно найти запись с минимальным значением HDACRD — просто заменяем SetGT на SETLL, а ReadPE на ReadE
Добавление, изменение, удаление записи
RPG предоставляет набор операций для добавления (write), изменения (update) и удаления (delete) записи.
Операции write и update работают с qualified и не qualified файлами точно также как операции read — для не qualified файлов можно использовать имя файла + предварительно заполненные переменные-поля или имя формата записи + likerec структуру-буфер. Для qualified используется полное имя формата записи + likerec структура-буфер
write RDK01LF.RDKPFR dsRDK;Операция изменения записи применяется к последней прочитанной записи. Иными словами, перед тем как делать update, нужно сделать read или chain.
Естественно, для этих операций файл должен быть объявлен с соответствующим режимом usage — *output для write и *update для update.
Еще один важный момент — в режимах *update и *delete любая операция чтения записи (read/chain) приводит к блокировке прочитанной записи. Т.о., дальше должно следовать или update/delete или, если по какой-то причине принято решение этого не делать, необходимо разблокировать запись командой unlock
chain (CUS) RDL01LFRDKPFR dsRDK;
if %found(RDK01LF); // проверяем что запись прочитана
if .... // нужно ли изменение?
... // вносим изменние в данные
update RDK01LF.RDKPRF dsRDK;
else; // изменения не требуются
unlock RDL01LF;
endif;
endif;Операция удаления записи (delete) может двумя способами — последней прочитанной записи, аналогично операции update
chain (CUS) RDL01LFRDKPFR dsRDK;
if %found(RDK01LF); // проверяем что запись прочитана
if .... // нужно ли удаление?
delete RDK01LF;
else; // удаление не требуется
unlock RDL01LF;
endif;
endif;Также возможно удаление первой записи для заданного значения ключа. В этом случае при успешном удалении (запись найдена и удалена) %found возвращает *on. В этом случае предварительное чтение записи не требуется.
Удаление всех записей для заданного значения ключа
dou not %found(RDK01LF);
delete (CUS) RDL01LF;
enddo;Для операций уделения файл должен быть описан с usage (*delete).
Плюсы и минусы прямых операций с БД
Несомненным плюсом такого подхода является его 100%-й контроль разработчиком. Вы всегда будете знать какой индекс где используется. Также вы можете выстраивать достаточно сложные алгоритмы работы с БД с использованием всех средств языка (и каких-то сторонних библиотек если таковые имеются) для обработки информации непосредственно в процессе ее получения. В том числе, в любой момент прервать чтение большой выборки если в какой-то момент поняли, что получили все что вам требуется.
Также этот подход очень экономичен по использованию ресурсов. Такие операции как получение максимально/минимального значения не являются агрегирующими — это просто чтение одной (первой или последней) записи в пути доступа. Операция проверки наличия записи вообще не требует обращения к хранилищу, достаточно просто проверить ее наличие в индексе.
Также такой подход не требует затрат времени и ресурсов на подготовку запроса (по нашим наблюдениям для SQL это может занимать до 30% от общего времени работы программы).
Основным минусом является сложность реализации запросов по многим таблицам и большим количеством условий выборки (особенно когда требуется сложное агрегирование). Тут для получения более эффективного, нежели SQL решения может потребоваться достаточно много усилий для построения оптимального алгоритма и порядка использования таблиц.
Использование SQL в RPG коде
Все SQL выражения в RPG коде начинаются с exec sql … Интеграция осуществляется SQL препроцессором, который автоматически вызывается перед вызовом RPG компилятора.
SQL препроцессор заменяет все exec sql выражения на вызовы соответствующих системных API после чего SQLRPGLE код превращается в обычный RPGLE.
Как пример, есть процедура получения ошибки SQL запроса. На SQLRPG Выглядит так:
dcl-proc SQLError ;
dcl-pi *n;
#ERR CHAR(37);
end-pi ;
// Your local fields go here
dcl-ds dsErr likeds(DSEPMS);
dcl-s strErrorId char(30);
dcl-s strMessageText char(256);
// Your calculation code goes here
exec sql get diagnostics condition 1 :strErrorId = DB2_MESSAGE_ID,
:strMessageText = Message_Text;
dsErr.@ERM = 'A230004';
dsErr.@PMALL = strErrorId;
#ERR = dsErr;
return ;
begsr *pssr;
dump;
endsr;
end-proc ;Т.е. получаем номер и текст ошибки SQL
После работы препроцессора она будет выглядеть так (это уже «чистый» RPG)
dcl-proc SQLError ;
dcl-pi *n;
#ERR CHAR(37);
end-pi ;
// Your local fields go here
dcl-ds dsErr likeds(DSEPMS);
dcl-s strErrorId char(30);
dcl-s strMessageText char(256);
// Your calculation code goes here
**END-FREE
D DS STATIC GET
D SQL_00018 1 2B 0 INZ(128) length of header
D SQL_00019 3 4B 0 INZ(6) statement number
D SQL_00020 5 8U 0 INZ(0) invocation mark
D SQL_00021 9 9A INZ('0') CCSID(*JOBRUNMIX) data is okay
D SQL_00022 10 127A CCSID(*JOBRUNMIX) end of header
D SQL_00023 129 158A CCSID(*JOBRUNMIX) STRERRORID
D SQL_00024 159 414A CCSID(*JOBRUNMIX) STRMESSAGETEXT
**FREE
//* exec sql get diagnostics condition 1 :strErrorId = DB2_MESSAGE_ID,
//* :strMessageText = Message_Text;
SQLER6 = -4; //SQL 6
SQLROUTE_CALL( //SQL
SQLCA //SQL
: SQL_00018 //SQL
); //SQL
IF SQL_00021 = '1'; //SQL
EVAL STRERRORID = SQL_00023; //SQL
EVAL STRMESSAGETEXT = SQL_00024; //SQL
ENDIF; //SQL
dsErr.@ERM = 'A230004';
dsErr.@PMALL = strErrorId;
#ERR = dsErr;
return ;
begsr *pssr;
dump;
endsr;
end-proc ;Тут все exec sql преобразованы препроцессором в вызовы системных API (плюс добавлены необходимые переменные и структуры данных, в частности SQLCA — SQL Communication Area).
Есть два варианта использования SQL запросов — статический и динамический. В статическом в выражении exec sql используется непосредственно SQL запрос, в динамическом — SQL запрос формируется «на лету» в виде строки, а потом строка уже подставляется в exec sql.
В случае статического SQL вся работа по подготовке запроса происходит на этапе компиляции и не занимает времени ресурсов в рантайме. В случае динамического запроса все происходит в рантайме (что отрицательно сказывается на потреблении ресурсов и производительности).
Пример статического запроса
exec sql declare curRDKMSClients1 cursor for
select RDKCUS,
RDKCLC,
RDKSER,
RDKNUM,
RDKOPN,
RDKEDT,
RDKOSN
from RDKPF RDK
join GFPF
on (GFCUS, GFCLC, GFCTP) =
(RDK.RDKCUS, RDK.RDKCLC, :cltType)
and GFDEL <> 'Y'
join CAFPF
on (CAFCUS, CAFCLC, CAFATR1) =
(RDK.RDKCUS, RDK.RDKCLC, 'Y')
join UIDPF
on (UIDCUS, UIDCLC, UIDSTS, UIDTPI) =
(RDK.RDKCUS, RDK.RDKCLC, 'A', '')
where (RDKUCD, RDKSDL) = ('001', 'Y')
and RDKEDT >= :$Date0
and RDKEDT <= :$DateK;Это строка декларативная — объявление курсора. Здесь используются объявленные ранее хост-переменные: cltType, :$Date0 и :$DateK
А это уже строки времени выполнения. Открытие курсора с использованием текущих значений хост-переменных
exec sql open curRDKMSClients1;Чтение данных выборки
exec sql fetch curRDKMSClients1 for :sqlRows rows into :dsSQLData;Здесь используется блочное чтение, не по одной записи, а сразу блоком из sqlRows записей. Данные читаются в хост-переменную dsSQLData которая является массивом из sqlRows структур, соответствующих формату выборки
dcl-ds t_dsSQLData qualified template;
CUS char(6) inz;
CLC char(3) inz;
SER char(10) inz;
NUM char(35) inz;
OPN zoned(7: 0) inz;
EDT zoned(7: 0) inz;
OSN char(1) inz;
end-ds;
dcl-ds dsSQLData likeds(t_dsSQLData) dim(sqlRows);Если тот же самый запрос реализовать в варианте динамического SQL, выглядеть будет примерно так:
dcl-s stmt char(2000);
exec sql declare curRDKMSClients1 cursor for QRY;Это декларативная часть. Дальше формируем строку запроса «на лету»
stmt = 'select RDKCUS, ' +
' RDKCLC, ' +
' RDKSER, ' +
' RDKNUM, ' +
' RDKOPN, ' +
' RDKEDT, ' +
' RDKOSN ' +
' from RDKPF RDK ' +
' join GFPF ' +
' on (GFCUS, GFCLC, GFCTP) = ' +
' (RDK.RDKCUS, RDK.RDKCLC, ?) ' +
' and GFDEL <> ''Y'' ' +
' join CAFPF ' +
' on (CAFCUS, CAFCLC, CAFATR1) = ' +
' (RDK.RDKCUS, RDK.RDKCLC, ''Y'') ' +
' join UIDPF ' +
' on (UIDCUS, UIDCLC, UIDSTS, UIDTPI) = ' +
' (RDK.RDKCUS, RDK.RDKCLC, ''A'', '''') ' +
' where (RDKUCD, RDKSDL) = (''001'', ''Y'') ' +
' and RDKEDT >= ? ' +
' and RDKEDT <= ?';Тут нет явных ссылок на конкретные хост-переменные, только места для подстановок.
Подготовка запроса (та операция, которая для статического SQL выполняется на этапе компиляции)
exec sql prepare QRY from :stmt;И после этого уже открытие курсора
exec sql open curRDKMSClients1 using :cltType, :$Date0, :$DateK;с указанием конкретных хост-переменных
Дальше все как и в случае со статическим вариантом.
Понятно, что тут с производительностью и ресурсопотреблением все совсем нехорошо. Чуть улучшить ситуацию можно если объявить и руками заполнить структуру SQLDA (SQL Data Area)
dcl-ds SQLDA;
SQLDAID char(8);
SQLDABC int(10);
SQLN int(5);
SQLD int(5);
SQL_VAR char(80) DIM(99);
end-ds;
dcl-ds SQLVAR;
SQLTYPE int(5);
SQLLEN int(5);
SQLRES char(12);
SQLDATA pointer;
SQLIND pointer;
SQLNAMELEN int(5);
SQLNAME char(30);
end-ds;Но это очень муторное занятие — там для каждой хост-переменной в запросе нужно руками заполнять блок SQLVAR примерно таким вот образом:
SQLN += 1; // sql parm def
SQLTYPE = 452; // sql parm def
SQLLEN = %len(prmACus); // sql parm def
SQLDATA = %addr(prmACus); // sql parm def
SQL_VAR(SQLN) = SQLVAR; // sql parm def
SQLN += 1; // sql parm def
SQLTYPE = 452; // sql parm def
SQLLEN = %len(prmACLc); // sql parm def
SQLDATA = %addr(prmACLc); // sql parm def
SQL_VAR(SQLN) = SQLVAR; // sql parm defи затем открываем курсор с передачей заполненной SQLDA
exec sql open RYXX12CUR using descriptor :SQLDA;В общем и целом, динамический SQL используется только в крайних случаях. Там, где статикой не обойтись. Например, набор условий в запросе (или сам запрос) варьируется в зависимости от поданного на вход набора параметров.
Плюсы и минусы встроенного SQL
Плюсы использования встроенного SQL очевидны — в случае построения сложных запросов по нескольким таблицам с большим количеством связей и условий код получается более понятным. Также можно положиться на встроенный оптимизатор SQL запросов и надеяться что он сам выберет наилучший план.
Ну и, как показывается практика, для сложных запросов, особенно в ситуации когда обработка полученных данных занимает кратно больше времени нежели их получение, использование SQL в общем случае даст большую производительность за счет того, что SQL движок берет на себе работу по оптимизации запроса с учетом текущей ситуации (размеры таблиц и т.п).
Минусы тоже есть. Если речь идет о работе с 1–2 таблицами по имеющимся индексам SQL будет не лучшим вариантом. На простых выборках он проигрывает операциям с прямым доступом к БД.
Также производительность SQL резко падает при использовании агрегирования. Бывают ситуации, когда приходится упрощать, «линеаризировать» запрос делая его избыточным, а агрегирование производить в памяти в процессе получения данных. А некоторых случаях такой подход позволяет кратно повысить скорость выполнения задачи На практике был случай когда удаление из запроса конструкции order by по нескольким полям из разных таблиц с занесением результата в динамический сортированный по ключу (ключ состоит из полей по которым требуется сортировка) список давал прирост производительности в 3–5 раз. В некоторых ситуациях эффективным оказывается «смешанный стиль» — основная выборка делается через SQL, но потом в процессе обработки что-то дочитывается прямым доступом. Но это уже на уровне интуитивной магии и большого опыта работы.
Еще на практике замечено что SQL может нестабильно (по скорости) работать в тех случаях, когда плотность вызова модуля, использующего внутри SQL, очень высока — если модуль вызывается одновременно из разных заданий сотни тысяч раз в секунду (а такие у нас есть), дисперсия времени выполнения может резко возрасти — какие-то из вызовов выполняются быстро, какие-то с ощутимой задержкой. Чего не наблюдается в случае реализации с прямым доступом к БД — этот вариант всегда работает стабильно.
Заключение
Можно с уверенностью утверждать что современный RPG предоставляет все необходимые возможности для эффективной работы с БД, в том числе и для реализации сложных запросов по нескольким таблицам и многими условиями.
Однако, выбор способа оптимального реализации, особенно в Hi-Load системах с высокими требованиями к производительности и утилизации ресурсов, может быть не всегда очевиден и требует определенного опыта, основанного на исследовании производительности различных реализаций с учетом потенциальных сценариев их использования.
