Введение
В одной из предыдущих статей уже давал краткий обзор языка 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] <KeyValue> <File>Первая операция устанавливает указатель перед первой записью в пути доступа, значение ключа которой равно или больше заданному (после последней записи с меньшим значением ключа). Для проверки попали ли на запись с точным значением ключа есть логическая 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 системах с высокими требованиями к производительности и утилизации ресурсов, может быть не всегда очевиден и требует определенного опыта, основанного на исследовании производительности различных реализаций с учетом потенциальных сценариев их использования.
vadimr
Всегда приятно почитать про технологии, которые лежат в основе цивилизации, а не про всякие сиюминутные бантики.
SpiderEkb Автор
Спасибо :-)
На самом деле бантики тоже есть. Там где они нужны :-)
Описанное работает на центральных серверах, там, где крутится mission-critical часть. А есть еще много "внешних систем" - там уже другие платформы и другие стеки.