1 – Sybase Confidential – February 13, 2009 Quick Start Обзорный тренинг Москва, 4 июня, 2010 Андрей Хромов, технический консультант, Sybase CIS. - презентация

1 1 – Sybase Confidential – February 13, 2009 Quick Start Обзорный тренинг Москва, 4 июня, 2010 Андрей Хромов, технический консультант, Sybase CIS

2 2 – Sybase Confidential – February 13, 2009 Модуль 1. Что такое Sybase IQ9:00 – 9:30 Модуль 2.Архитектура и терминология9:30 – 10:15 Модуль 3.Инсталляция Sybase IQ10:15 – 10:45 ПЕРЕРЫВ – 15 мин Модуль 4.Создание базы данных11:00 – 11:45 Модуль 5.Конфигурирование сервера11:45 – 12:30 ОБЕД – 1 час Модуль 6.Типы данных. Таблицы. Индексы.13:30 – 15:15 ПЕРЕРЫВ – 15 мин Модуль 7.Загрузка данных 15:30 – 16:15 Модуль 8.Работа с Sybase IQ 16:15 – 16:45 ВОПРОСЫ И ОТВЕТЫ – 15 мин Наша программа:

3 Модуль 1 Что такое Sybase IQ

4 S YBASE IQ СУБД, разработанная специально для Хранилищ Данных, оптимизированная для: –Скорости: А) обработка запросов, Б) загрузка данных –Экономичности: А) администрирование, Б) платформа –Простоты: А) разработка, Б) эксплуатация –Масштабируемости: любой размер проекта Sybase IQ : –предназначена для BI проектов –это НЕ «OLTP» и НЕ «универсальная» СУБД –обладает уникальными характеристиками

5 Архитектура Хранилища данных Base décisionnelle Data Warehouse Data Marts Извлечение Трансформация Загрузка Анализ, Запросы, Отчеты

6 S YBASE IQ: в чем уникальность подхода 1. Вертикальное секционирование Традиционные СУБД (Sybase, Oracle, IBM, Microsoft) Sybase IQ Сокращение Ввода/Вывода, Избавление от эффекта "table scan"

7 S YBASE IQ: в чем уникальность подхода 2. Полное индексирование всей базы Отсутствие необходимости в сложном специальном тюнинге Полная готовность к ad-hoc запросам

8 S YBASE IQ : в чем уникальность подхода 3. Сжатие данных Производительность Расходы на диски снижаются на 50%

9 S YBASE IQ: Основные характеристики Очень высокая скорость обработки запросов (операции чтения) Оптимизированная массовая загрузка Внешне выглядит как обычная реляционная база данных Стандартный SQL интерфейс (Sybase, ODBC, JDBC) Открытость к модели данных (реляционная, многомерная) Простота настройки и конфигурирования Низкие затраты на администрирование Широкий выбор платформ (Unix, Linux, Windows) Линейная масштабируемость Наращиваемость: архитектура IQ Multiplex

10 Наращиваемость: Sybase IQ Multiplex «Программный» кластер Наращивание мощности по принципу «Plugn Play» Линейная масштабируемость, низкий TCO Два вида узлов кластера: «Читатель» или «Писатель» Балансировка нагрузки, управление ресурсами Обеспечение функции «Высокой доступности» Интернет-пользователи Балансировка нагрузки Поступление порции новых данных Внутренние пользователи: Маркетинг, Аналитики(Data Mining)

11 Загрузка данных 1 выделенный сервер Нагрузка на ЦП: Обработка запросов 1 сервер 4 процессора 8 ГБ ОЗУ IQ Multiplex в цифрах Тестовая база Таблицы размером 1-5 млрд. строк От 10 до 50 одновременных запросов

12 IQ Multiplex в цифрах. Загрузка одновременно с обработкой запросов Результаты теста оказались неожиданными. Времена исполнения запросов на сервере с нагрузкой были ниже, чем на сервере без нагрузки. Вероятнее всего это связано с кэшированием ввода/вывода в диском массиве MSA Поскольку в этом тесте ввод/вывод не был узким местом, то загрузка данных не принесла ожидаемого негативного эффекта. Сервер «Писатель», принимавший входящий поток данных, также не почувствовал никакого ухудшения производительности

13 Итак, SYBASE IQ – это: На 100% решение для BI задач, разработанное для: –Радикального увеличения производительности, снижения затрат (на ресурсы, на «тюнинг» базы и запросов, на эксплуатацию), для упрощения процедуры развертывания и дальнейший роста платформы –Возможности работать с данными ПО-ДРУГОМУ –Обеспечения гарантии успеха Ваших проектов Решение, с которым мы сейчас познакомимся поближе Решение, которое стоит попробовать

14 Модуль 2 Архитектура и Терминология

15 Платформы Sybase IQ Платформы, поддерживаемые Sybase IQ 15.2 Sun Solaris (64-bit) –SPARC64 –UltraSPARC T –X64 (Opteron) IBM AIX (64-bit) HP-UX Itanium (64-bit) –HP-UX PA-RISC (64-bit) – только IQ 12.7 Linux –Linux x86/x64 (32- и 64-bit) – RHEL и SLES –Linux on IBM POWER (64-bit) Windows (32- и 64-bit) –Windows Server 2003, 2008 –Windows XP, Vista, W7

16 Сервер Sybase IQ Сервер Sybase IQ – это процесс ОС, запущенный на серверном компьютере Этот сервер баз данных является «мозгом» системы Sybase IQ Пользователи подсоединяются к серверу IQ, чтобы получить доступ к базе IQ Включает в себя возможности настройки Рассчитан на работу только с одной базой Sybase IQ Иметь несколько баз на одном сервере не рекомендуется Пользователи могут эмулировать работу с несколькими базами данных внутри одной базы IQ путем логического разделения объектов Использует функционал Sybase SQL Anywhere (ASA) Для поддержки ряда функций Sybase IQ используется код сервера ASA

17 Sybase IQ и ASA SQL Anywhere (ASA) – это реляционная СУБД, которая может существовать как сама по себе, так и в связке с Sybase IQ –Поддерживает ANSI SQL и ряд дополнительных конструкций –Зрелая СУБД, давно и с успехом существует на рынке. Sybase IQ –Патентованная технология хранения –Вертикальное хранение данных –Sybase IQ использует некоторый функционал ASA

18 Sybase IQ и ASA IQ и ASA – это симбиоз двух продуктов Функционал ASA, используемый в IQ –Механизм обслуживания подключений пользователей –Синтаксический разбор SQL-запросов –Система безопасности (пользователи, права, ит.п.) –Механизм Component Integration Services (CIS) Хранимые процедуры Sybase IQ можно писать, используя один из двух SQL-диалектов ASA - T-SQL или WatcomSQL –Системные хранимые процедуры Sybase IQ написаны на WatcomSQL

19 База данных Sybase IQ Включает следующие компоненты –Три накопителя - Stores Catalog Store (для метаданных) IQ Store (для данных) IQ Temporary Store (для временных данных) –Файлы сервера Журнал сообщений IQ (IQ message log) Журнал транзакций Catalog Store (Catalog store transaction log) Журналы сервера IQ (IQ server log)

20 Архитектура Sybase IQ Общая картина (конфигурация с одним сервером)

21 Пространства БД (dbspaces) Пространство БД (dbspace) – это логическое имя для дискового пространства, используемого базой данных IQ –dbspace может быть файлом ОС или «сырым» устройством, выделенным под Sybase IQ База данных Sybase IQ обычно включает в себя несколько dbspace: –IQ Store, Catalog Store и IQ Temporary Store – все они состоят из одного или нескольких dbspace 12.7

22 Пространства БД (dbspaces) В Sybase IQ 15 dbspace – это логическое имя контейнера, объединяющего в себе несколько dbfiles Иерархическая структура базы данных в Sybase IQ 15: –База IQ состоит из 3 Накопителей (Stores): IQ Store, Catalog Store, IQ Temporary Store –Каждый из Stores может состоять из 1 или нескольких dbspace Catalog Store и IQ Store - из 1 или нескольких dbspace IQ Temporary Store - всегда только из 1 dbspace –Пространство БД (dbspace) может состоять из 1 или нескольких файлов БД (dbfiles) Некоторые dbspace могут состоять только из 1 dbfile –Файл БД (dbfile) может быть файлом ОС или «сырым» устройством IQ 15

23 Иерархия структуры базы в IQ 15 Stores dbspaces dbfiles IQ 15

24 Пространства БД (dbspaces) в IQ 15 dbspace – логический контейнер файлов, относящихся к одному из Stores. Аналог базы данных в ASE Catalog Store имеет только 1 dbspace –SYSTEM – для размещения таблиц каталога БД –Может состоять лишь из 1 dbfile IQ Store после создания нового сервера имеет 1 dbspace. В дальнейшем обычно имеет несколько dbspaces –IQ_SYSTEM_MAIN – содержит системные метаданные (аналог базы данных master в ASE) –Дополнительно создаваемые dbspace служат для размещения пользовательских данных –Каждый dbspace может включать в себя множество dbfile IQ Temporary Store имеет только 1 dbspace –IQ_SYSTEM_TEMP – для хранения временных данных –Может состоять из множества dbfiles IQ 15

25 Накопитель Catalog Store Каждая база IQ имеет Сatalog Store. Это всегда 1 dbspace –Это всегда файл ОС –Этот dbspace растет автоматически по мере добавления новых объектов базы Содержит служебную информацию о базе IQ –Метаданные (хранятся в системных таблицах) –Хранимые процедуры (системные и пользовательские) Также известен как ASA-файл базы (database file) –Физическое имя файла.db Логическое имя Сatalog Store - SYSTEM

26 Накопитель IQ Store Служит для хранения данных – в виде сжатых индексов –Содержит журнал транзакций для данных в таблицах IQ –Содержит структуру для управления распределением места (список свободного места, free list) Каждая база IQ имеет только один IQ Store –Обычно состоит из нескольких dbspace –Эти dbspace могут создаваться как «сырые» устройства или как файлы ОС Логическое имя первого dbspace - IQ_SYSTEM_MAIN –Имя первого физического файла -.iq (если dbspace был создан как файл ОС)

27 Накопитель IQ Temporary Store «Рабочая область» базы данных –Здесь сортируются данные для построения тех индексов, что строятся в процессе загрузки –Здесь производится сортировки и группировки, необходимые для обработки запросов Имя физического файла -.iqtmp (если dbspace создан на файловой системе) Каждая база имеет только одно IQ Temporary Store –Может состоять из нескольких физических файлов –Может располагаться на «сырых» устройствах или на файлах ОС Логическое имя первого dbspace - IQ_SYSTEM_TEMP

28 Журнал сообщений IQ (Message Log) Текстовый файл. Формат пригодный для чтения. Содержит: –Сообщения об массовых операциях INSERT и DELETE –Сообщения об ошибках –Информационные сообщения –Планы запросов Физическое имя файла -.iqmsg –Каждая база IQ имеет один журнал сообщений Это всегда файл ОС

29 Журнал транзакций Catalog Store Это журнал транзакций ASA-базы Сatalog Store –К данным IQ Store и изменениям данных, проходящим в IQ Store, отношения не имеет Используется для отката (roll back) или наката (roll forward) изменений в базе Catalog Store Физическое имя файла -.log Это небольшой файл ОС, который с течением времени медленно увеличивается

30 База данных IQ. Общая картина Компоненты базы Sybase IQ (конфигурация с одним сервером)

31 Понятие «Таблица» в IQ Таблицы IQ - это «логические» таблицы, определенные в Сatalog Store –На самом деле таблица – это просто набор колонок Три типа таблиц –Основные таблицы (Base tables ) –Локальные временные таблицы (Local temporary tables) –Глобальные временные таблицы (Global temporary tables)

32 Понятие «Индекс» в IQ Каждая колонка в таблице IQ всегда содержит по крайней мере один индекс, используемый для выборки данных и обработки запросов –Индекс «по умолчанию» создается в момент создания таблицы –Для обеспечения скорости выборки на большинстве колонок обычно создаются дополнительные индексы Тип нужного индекса определяется по –Ожидаемой кардинальности данных в колонке –Тому, как эта колонка используется в запросах 9 типов индексов Fast Projection (FP) = «Индекс по умолчанию» Low Fast (LF)Date (DATE) High Non Group (HNG)Date & Time (DTTM) High Group (HG)Time (TIME) Compare (CMP)Word (WD)

33 Модуль 3 Инсталляция Sybase IQ

34 Размер требуемого места на диске Бинарники IQ –Unix / Linux: до 800MB –Windows: до 375MB (283MB для IQ) База данных –IQ Store: от 70 до 100% размера входных данных –IQ Temporary Store: от 20 до 25% размера IQ Store; определяется в зависимости от количества пользователей, индексов и данных Пространство для файлов загрузки –Пространство, необходимое для размещения файлов с исходными данными, для последующей загрузки в IQ Файл подкачки –Равный или удвоенный размер объема физической памяти (RAM)

35 Рекомендации к оборудованию Чем больше ПРОЦЕССОРА, тем лучше –Ускоряется Загрузка данных (Load Table умеет использовать SMP) –Улучшается производительность при многопользовательской нагрузке –Более быстрые CPU предпочтительнее Рекомендуется 1 GHz (или выше) –Производительность сервера IQ практически линейно зависит от скорости процессора –IQ больше нагружает CPU, чем подсистему Ввода/вывода Чем больше ПАМЯТИ, тем лучше –Минимум 4 GB на каждый CPU –Лучше 8 ГБ на 1 CPU

36 Инсталляция Sybase IQ Перед началом инсталляции выберите, какие компоненты вы хотите установить:

37 Старт сервера Sybase IQ - Windows Старт Sybase IQ 1.Выберите: Пуск > Программы > Sybase > Server IQ 15.1 > Start Sybase IQ Demo Database 2.Запустится база данных iqdemo на сервере Sybase IQ с именем hostname_iqdemo Рекомендуемый метод запуска сервера Sybase IQ будет обсуждается в модуле Настройки сервера и Опции БД –Там обсуждаются настройки и опции сервера

38 Остановка сервера Sybase IQ - Windows Остановка IQ – Windows 1.Откройте окно консоли сервера Sybase IQ. Для этого щелкните мышкой на иконку IQ на системном трэе – в нижнем правом углу экрана. 2.Нажмите Shutdown (Остановить). 3.Опции командной строки запуска сервера определяют режим остановки: ждать завершения активных соединений или нет..

39 Соединение с сервером Sybase IQ Sybase IQ поддерживает следующие типы соединений: –ODBC, OLE-DB и Embedded SQL –Sybase Open Client –JDBC С Sybase IQ поставляются клиентские приложения: –Sybase Central – соединяется через ODBC или JDBC –dbisql (Interactive SQL Java) – соединяется через ODBC или JDBC –dbisqlc (Interactive SQL клиент) – соединяется через ODBC –iqisql (Open Client Interactive SQL) – соединяется через OpenClient

40 Соединение с помощью DBISQL DBISQL - это клиент для связи с Sybase IQ –Поставляется вместе с Sybase IQ –Это рекомендуемый метод для взаимодействия с Sybase IQ –Будет полезен для администраторов баз данных –Может использоваться как инструмент написания запросов DBISQL имеет два GUI-интерфейса и два режима работы –GUI (графический) Interactive SQL Java (тонкий java клиент) Interactive SQL Classic (толстый клиент, написан на C) –Режим работы без GUI (беззвучный режим)

41 Соединение с помощью Sybase Central Графическая консоль для управления серверами Sybase –Данная Java-версия Sybase Central предназначена только для использования с IQ –Использует соединение ODBC или JDBC Для соединения с Sybase IQ (Windows): –Меню Пуск > Sybase > Sybase IQ 15.1 > Sybase Central Java Edition –Логин - DBA / Пароль– SQL Для соединения с Sybase IQ (Unix / Linux): –Запустить из командной строки Sybase Central Agent –Запустить из командной строки Sybase Central

42 Возможности Sybase Central Создание новых баз данных Sybase IQ Запуск и остановка серверов Sybase IQ Добавление и удаление dbspaces, dbfiles Создание таблиц, представлений и индексов –Изменение таблиц и представлений –Перестройка индексов –Просмотр и изменение данных –Генерация DDL скриптов Создание хранимых процедур Добавление пользователей и групп, управление правами

43 Соединение через Open Client Open Client – это дополнительный способ соединения с Sybase IQ –Использует TDS протокол Sybase Зачем нужно использовать Open Client? –Предоставляет возможность соединения с Sybase IQ через командную строку –Для загрузки данных из других удаленных серверов/баз Соединение с Sybase IQ через Open Client имеет ряд особенностей: –Использует режим транзакций unchained –Не является ANSI стандартом –Не может распознавать некоторые типы данных IQ

44 Роль интерфейсного файла TDS клиенты и сервера находят друг друга посредством просмотра интерфейсных файлов Интерфейсные файлы необходимы: –Серверам для указания сетевых адресов, которые они должны слушать, чтобы клиенты могли к ним подключиться –Клиентам, чтобы определить, куда направлять свои запросы на подключение –Чтобы определить местонахождение сервера из которого нужно загрузить данные Интерфейсные файлы имеют различные имена на разных платформах: –UNIX: $SYBASE/interfaces –Windows: %SYBASE%\ini\sql.ini

45 Модуль 4 Создание баз данных

46 Состав базы данных IQ База создается при помощи команды CREATE DATABASE

47 Пространство БД (dbspace) dbspace – это логическое имя части дискового пространства, выделенной под использование базой IQ Каждая база IQ включает в себя несколько dbspace –Максимально допустимое количество dbspace для одной базы В каждой новой базе для каждого Накопителя (Store) имеется как минимум один dbspace Впоследствии к базе могут добавляться дополнительные dbspace

48 dbspace Новые данные в базе распределяются по всем dbspace –Правильный подход: при создании базы сразу выделить для нее все необходимое пространство Данные распределяются между всеми dbspace равномерно –Данные записываются во все dbspace по алгоритму round-robin –Такой подход является рекомендуемым для наилучшей производительности Объекты могут быть помещены в заданный dbspace –Объекты можно перемещать из одной dbspace в другую –Функции управления dbspace рассматриваются позднее Планируйте по крайней мере 10% резервного пространства в вашей базе –Место может понадобиться, например для новых версий таблиц (рассматривается позднее)

49 Планирование dbspace Перед созданием базы необходимо определиться с типом и размером выделяемых под нее дисков –Размер Catalog Store не должен превышать 1TB (или 4GB на Windows с файловой системой Fat32) –Размер IQ Store и IQ Temp Store На сырых устройствах – без ограничений (может зависеть от платформы) На файловой системе – 4TB dbspace для IQ Store и IQ Temp Store в последствии могут быть расширены или удалены из базы данных –Для того, чтобы можно было расширить существующую секцию dbspace, она должна быть создана определенным образом (дополнительный синтаксис при создании) – Пустая секция dbspace может быть удалена

50 Catalog Store Всегда создается на файловой системе –Рекомендуется создавать этот файл на той же машине, на которой работает сервер IQ Растет по мере добавления новых объектов в базу –Никогда не сокращается, даже в случае удаления объектов На файловой системе должно быть достаточно места под файл каталога –Другие файлы IQ (включая файл сообщений IQ и серверные журналы IQ) обычно располагаются на той же файловой системе, что и Catalog Store. –Если на файловой системе закончится место, сервер «зависнет»

51 dbspace для IQ Store и IQ Temp Store Могут создаваться на «сырых» дисках или на файловой системе Состоят из заранее выделенного набора страниц данных Могут быть увеличены на величину «зарезервированного» при создании dbspace места –Существует специальный синтаксис, выполняющий команду увеличения dbspace и создания dbspace с последующей возможностью расширения Зарезервированное пространство позволяет: –Добавлять место в существующий dbspace –dbspaces, занявший все свободное место на диске может быть перенесен на другой диск бОльшего размера (для этого нужно остановить сервер) и затем расширен.

52 Порядок действий при создание базы Шаг 1.Определить тип сервера, который вы хотите создать Шаг 2.Выбрать тип дискового устройства Шаг 3.Оценить размер базы данных Шаг 4.Создать базу данных

53 Шаг 1: Выбрать тип устройства ПреимуществаНедостатки Сырой диск Управляются напрямую приложением (СУБД), минуя файловую систему Производительность может быть выше Размер устройства может быть больше IQ получает полный контроль над кэшем данных Сложнее в управлении Файловая система Легче в управлении Дополнительные потери на операции чтения Файловая система не имеет представления о страницах IQ «Сырые» диски против Файловой системы

54 Устройства для Sybase IQ Рекомендации по производительности –ПО для управления Логическими Томами использовать не рекомендуется –Для IQ Store используйте устройства бОльшего размера –Для IQ Temp Store cоздавайте несколько dbspace (IQ 12.7) или dbfiles (IQ 15) »Не менее 1-2 dbspace (dbfiles) на 1 CPU –Используйте «сырые» устройства для лучшей производительности Работа с «сырыми» устройствами на Unix / Linux –Используйте «сим.линки» для «сырых» устройств. Это даст вам большую гибкость при переконфигурировании устройства

55 Шаг 2: Оценить размер базы IQ Store – 75% от общего размера исходных данных –Объем требуемого места зависит от нескольких факторов: Характер данных (повторяющиеся значения, типы данных) Количество и типы IQ-индексов, которые будут созданы –Пространства dbspace для IQ store впоследствии могут добавляться и удаляться по мере необходимости IQ Temporary Store – обычно 20% от размера IQ Store –Размер под IQ Temporary Store зависит от: Количества HG индексов (перестраиваемых при загрузке данных) Количества активных пользователей Сложности пользовательских запросов –Пространства dbspace для IQ Temp Store впоследствии могут добавляться и удаляться по мере необходимости

56 Шаг 3: Создать базу данных –Если вы используете DBISQL, подсоединитесь к серверу IQ и запустите скрипт Можно использовать команду CREATE DATABASE в DBISQL или «мастер» в Sybase Central

57 Файлы, создаваемые при создании базы dbspaces, создаваемые в процессе создания базы (на файловой системе) –имябазы.db – IQ catalog store –имябазы.iq* - IQ store –имябазы.iqtmp* - IQ temporary store Другие объекты, создаваемые в процессе создания базы –имябазы.log – журнал транзакций –имябазы.iqmsg – IQ журнал сообщений

58 Параметры создания базы Следующие несколько слайдов посвящены наиболее важным для базы данных параметрам, многие из которых оказывают прямое влияние на производительность Эти параметры нельзя изменить после того, как база создана –Отнеситесь к ним с осторожностью –Если параметры настроены некорректно, базу необходимо пересоздать

59 Параметры создания базы CASE – определяет чувствительность к регистру в запросах –По умолчанию, данные чувствительны к регистру (строчные и заглавные буквы различаются) –В базах данных, чувствительных к регистру, производительность выше PAGE SIZE – размер страницы для catalog store –По умолчанию: 4096 байт (4K) –Варианты: 4096, 8192, 16384, –Для баз данных с очень широкими таблицами или с очень сложными запросами (длинные SQL-команды) может потребоваться увеличение размера страницы

60 Параметры создания базы COLLATION – последовательность сортировки, по умолчанию равна значению ISO_BINENG –Это та же последовательность, что и для набора символов ASCII (сначала заглавные потом строчные буквы) JAVA – разрешение на хранимые процедуры на Java (по умолчанию ON) JCONNECT – разрешение на подключение через JDBC (по умолчанию ON)

61 IQ PAGE SIZE – какой размер выбрать? Определяющие факторы (также см. документацию) Тип платформы (32 или 64 битных ОС) –для 32-битных OS -> 64K или 128K –для 64 битных OS -> 128K или больше (256K, 512K) Количество памяти на машине (RAM) –Размер страницы IQ определяет размер «буфера» кэша IQ –Если страница IQ большого размера, то в кэше IQ поместится меньшее кол-во соответствующих «буферов» страниц Размер базы и размер самой большой таблицы (# записей) –Чем больше база данных, тем больший размер рекомендуется для страницы IQ –Чем больше таблица, тем больший размер рекомендуется для страницы IQ Количество конкурентных пользователей –Больше пользователей требуют большее число «буферов» ! !

62 Создание баз данных: Sybase Central Мастер создания баз данных будет запрашивать информацию, требуемую для создания базы

63 Удаление базы данных Удаляет базу данных и все ее содержимое Пример: DROP DATABASE D:\\mydb\\mydb.db Удаляет базу данных mydb Нужно указать имя файла базы, которое задавалось при создании – в операторе CREATE DATABASE База данных должна быть остановлена перед удалением

64 Проверка состояния базы - sp_iqstatus sp_iqstatus Предоставляет детальную информацию о текущей базе данных –Информация о версии –Размер базы –Объем свободного места в IQ store и IQ temporary store Пример на следующем слайде

65 Проверка состояния базы - sp_iqstatus

66 Другие полезные хранимые процедуры sp_iqcheckdb –Проверка целостности текущей базы sp_iqdbsize –Распечатка данных о размере текущей базы sp_iqdbstatistics –Статистика по IQ store sp_iqspaceused –Отображение информации о свободном и занятом месте в IQ Store и в IQ Temporary Store

67 Модуль 5 Конфигурирование сервера

68 Конфигурирование Сервера/ Базы Каждый сервер и база IQ требуют конфигурирования Конфигурирование сервера IQ: –Ключи командной строки во время старта сервера И/ИЛИ –Конфигурационный файл (.cfg файл) - передается со Это предпочтительный метод старта сервера/базы данных Sybase Central при создании базы создает конфигурационный файл «по умолчанию» с названием params.cfg Конфигурирование базы данных: –Команда Set Option

69 Конфигурирование сервера IQ Конфигурирование сервера –Для управления поведением сервера IQ используют ключи –Ключи задаются в командной строке или в конфигурационном файле (.cfg), переданном со Командная строка – start_iq server-switches database_file Конфигурационный файл – database_file Комбинация ключей и конфигурационного файла start_iq –n database_file

70 Ключи для управления памятью IQ Эти ключи конфигурационного файла IQ настраивают размер памяти, выделяемой под кэши IQ -iqmc = размер основного кэша Нужно задать размер основного кэша в MB, по умолчанию 16 MB -iqtc = размер временного кэша Нужно задать размер временного кэша в MB, по умолчанию 12MB Позднее будет рассказано как правильно задать размер памяти

71 Кэши IQ (кэши буферов IQ) Основной кэш (MAIN CACHE) – Область памяти для буферов IQ Store –Используется совместно всеми пользователями Временный кэш (TEMP CACHE) –Область памяти для буферов IQ Temporary Store –Используется совместно всеми пользователями Примечание: –перед началом процесса загрузки данных размер для обоих кэшей должен быть выставлен

72 Зачем нужно настраивать кэши? Размеры кэшей «по умолчанию» для реальной работы слишком малы Размеры «по умолчанию» достаточны для запуска сервера, но для работы с реальными большими объемами данных они должны быть увеличены Чем больше размер кэшей, тем меньше физического ввода/вывода, тем выше производительность –Кэши используются для всех операций дискового чтения/записи –Снижают объем физического ввода/вывода –Повышают производительность операции загрузки данных На время загрузки вам, возможно, понадобиться изменить пропорцию распределения памяти между основным и временным кэшами - для оптимизации загрузки таблиц с большим количеством индексов

73 ОСНОВНОЙ кэш (Main Buffer Cache) Инициализируется в момент старта базы Размер по умолчанию для основного кэша - 16 MB –Это слишком мало для большинства приложений После изменения этого параметра, необходимо выполнить перезапуск сервера IQ Рекомендуемый стартовый размер - 40% общей памяти, доступной под сервер IQ –Расчеты и примеры приводятся дальше

74 ВРЕМЕННЫЙ кэш (Temporary Buffer Cache) Инициализируется во время старта базы Этот кэш используется для выполнения операций группировки, сортировки, хэширования, и объединения в режиме ad-hoc По умолчанию, размер этого кэша - 12 MB –Это очень мало для большинства приложений После изменения этого параметра, необходим перезапуск сервера IQ Рекомендуемый стартовый размер - 60% общей памяти, доступной для сервера IQ –Расчеты и примеры приводятся далее

75 Просмотр размера кэшей Используйте команду sp_iqstatus Размер основного кэша IQ = 16MB Размер временного кэша IQ = 12MB

76 Память и «Активные» пользователи Каждый пользователь, подключающийся к серверу IQ увеличивает объем памяти, используемый процессом IQ –Память, используемая сервером IQ, увеличивается динамически, по мере увеличения числа подключенных пользователей Каждый активный пользователь увеличивает память, занимаемую сервером на 10MB –Активные пользователи исполняют запросы или загружают данные –Для бездействующих пользователей нужно меньше памяти (5MB на соединение) Вам будет нужно оценить количество активных и количество подключенных пользователей, чтобы иметь возможность оценить общее количество памяти, необходимой вашему серверу

77 Память под загрузку данных Во время процесса загрузки данных из плоских файлов IQ требуется дополнительная память –Эта память используется для буферизации чтений с диска –IQ НЕ использует эту память для загрузки данных с других серверов, для операций UPDATE и DELETE Объем необходимой памяти зависит от количества колонок и от ширины строки, а не от количества записей, которые необходимо загрузить Эта память нужна IQ в дополнение к той, которая выделятся для работы серверного процесса IQ и для кэшей IQ –Память для загрузки контролирует опция базы данных LOAD_MEMORY_MB

78 Опция LOAD_MEMORY_MB Контролирует объем памяти, выделяемой под буферизацию чтений из файлов на диске в процессе загрузки таблиц Каждая отдельная операция загрузки данных использует свои собственные буфера SET OPTION PUBLIC.LOAD_MEMORY_MB = 300 Может быть задана как постоянный параметр, как временный параметр и для заданного пользователя –Может меняться динамически (не требуется перезагрузка сервера) –Используйте эту опцию, если в процессе загрузки вы увидели следующую ошибку памяти: All virtual memory has been allocated (Вся виртуальная память занята) Также, эта ошибка может возникнуть, если лимит для заданного пользователя слишком низок

79 Основной и временный кэши IQ Размер кэшей определяется после того, как становится понятен размер оставшейся памяти Рекомендуется поделить оставшуюся память между основным и временным кэшами в следующей пропорции: »40% - на основной кэш »60% - на временный кэш Эти значения – хорошая стартовая точка. Дальнейшая подгонка размеров кэшей выполняется в соответствии с характером вашего приложения.

80 Ключи для сетевых соединений Ключи конфигурационного файла IQ, управляющие параметрами сетевых коммуникаций Параметр –x задает тип протокола, используемого для сетевых коммуникаций по умолчанию это TCP/IP с портом x tcpip(port=2640) Запустить сервер на порте x tcpip(MyIP = :2637) Установить IP адрес и порт равный x tcpip Использовать только TCP/IP с портом по умолчанию

81 Опции базы данных IQ Команда SET OPTION изменяет опции базы Может изменить поведение базы как для всех пользователей, так и для заданного пользователя –Изменения могут быть временными и постоянными SET [TEMPORARY] OPTION [user_id. | PUBLIC.] option-name = [option-value]

82 Опции базы данных IQ Область видимости и время действия Область видимости определяет то, по отношению к кому применяется данная опция –PUBLIC – воздействует на всех пользователей –USER_ID – воздействует на отдельного пользователя группы Настройки для индивидуального пользователя перекрывают настройки, установленные с ключом PUBLIC Время действия определяет длительность применяемой опции –Public – постоянная(или до тех пор пока не изменят) Разрешено устанавливать только DBA –Temporary – на время жизни сессии Перекрывает установку Public –Temporary … Public – постоянно, до тех пор пока сервер IQ не будет перегружен Разрешено устанавливать только DBA

83 Опции базы данных IQ Опции для повышения производительности Public.Force_No_Scroll_Cursors=On; (по умолчанию off) –Установка в On предотвращает кэширование результатов запросов пользователей –Выключает возможность прокручивания Result Set от начала к концу Public.Query_Temp_Space_Limit = 0; (по умолчанию 2000 MB) –Ограничение в 2000 MB может быть слишком маленьким для некоторых пользователей –0 снимает ограничения к объему памяти, выделяемой под временный кэш

84 Проверка опций базы данных sp_iqcheckoptions Показывает любые опции базы данных, которые были изменены со значений по умолчанию

85 Модуль 6 Типы данных. Таблицы. Индексы.

86 ТИПЫ ДАННЫХ Модуль 6

87 Типы данных Sybase IQ Поддерживается очень широкий спектр различных типов данных –Некоторые типы данных не поддерживаются через Open Client (isql) –Существует ряд ограничений между индексами IQ и типами данных Для лучшей производительности и компрессии данных –Выбирайте наиболее эффективный тип данных для этой колонки Проверяйте количество байтов, необходимое для хранение каждого типа данных Обратите особое внимание на следующие типы данных –Date / Datetime Используйте Date вместо Datetime если компонента «время» не используется –Numeric / Decimal Выбирайте разумное число знаков после запятой

88 Типы данных IQ Тип данных ASIQДиапазонМаксимальное число знаков Размер (в байтах) CHAR(n) CHARACTER(n) 1

89 Типы данных IQ Типы данных ASIQДиапазонМаксимальное число знаков Размер (В байтах) BIGINT(n) UNSIGNED BIGINT(n) -9.2(^18)

90 Типы данных IQ Тип данных ASIQДиапазонМаксимальное число знаков Размер (в Байтах) TIME00:00: :59: n/a8 DECIMAL(p,s) NUMERIC(p,s) От 2 до 69 DOUBLE2.22(^-308) (^308)158 BIT0, 1n/a1 bit MONEY1916 SMALLMONEY108 BINARY (длина)n/aОт 1 до 255 VARBINARY (длина)n/aОт 1 до (32K – 1) UNIQUEIDENTIFIERSTR36n/a36

91 Пользовательские типы данных (UDT) Являются надстройкой над встроенными типами данных Создаются командой CREATE DOMAIN CREATE DOMAIN имя_домена системный_тип Также могут быть созданы при помощи хранимой процедуры sp_addtype sp_addtype имя_типа, системный_тип

92 Хранение больших объектов (BLOB) IQ поддерживает возможность хранения LOB данных (такая возможность является отдельной лицензируемой опцией). –Для этих целей используются типы данных LONG BINARY и LONG VARCHAR –Максимальный размер объекта напрямую связан с размером страницы IQ Максимальный размер LOB = Размер страницы IQ x 4GB Данные типа BLOB могут только храниться в базе данных –Поиск по колонкам типа BLOB невозможен –Поддерживаются некоторые системные функции Для типа данных LONG VARCHAR поддерживается функция SUBSTRING64 –Поиск по подстроке возможен –Поддерживается ряд системных функцй См. «Large Objects Management» (Управление крупными объектами) в документации Sybase IQ

93 СОЗДАНИЕ ТАБЛИЦ Модуль 6

94 Создание таблиц в базе данных IQ

95 Команда CREATE TABLE Ограничение UNIQUE (уникальное значение) –Не допускает ввода повторных значений в колонке или группе колонок –Создается уникальный HG индекс на колонке (колонках) Ограничение PRIMARY KEY (первичный ключ) –То же самое что и констрейнт UNIQUE, но в таблице может быть задан только один PRIMARY KEY Опция IQ UNIQUE (кардинальность колонки) –Задает примерное количество уникальных значений в колонке –Определяет тип FP индекса

96 Команда CREATE TABLE Ограничение CHECK (проверка) –Позволяет задать условия, которые должны быть проверены перед выполнением операций INSERT и UPDATE Ограничение FOREIGN KEY (внешний ключ) –FK – это колонка (колонки) чьи значения являются подмножеством значений другой колонки (колонок) –Колонка (колонки), на которую идет ссылка должны быть либо основным ключом (Primary Key), либо для них должен быть задан констрейнт Unique –Для колонок типа Foreign Key создается неуникальный HG индекс

97 Производительность запросов и PK, FK Первичные ключи (Primary или Unique) являются критичными для производительности запросов –Оптимизатор использует ключи, чтобы определить взаимосвязь таблиц при выполнении операции join –Необходимо задавать констрейнты Primary или Unique в таблицах, используемых в операциях join В качестве ключей задавайте колонки, которые вы будете использовать для операций join Для внешних ключей (Foreign Key) и других колонок нужен HG-индекс –Констрейнт Foreign Key создает HG-индекс –Констрейнт FK не является обязательным, - фактически он нужен только в том случае, когда есть проблема целостности реляционных данных Если вы не используете внешние ключи, создавайте HG-индекс на колонках, участвующих в операции join

98 Создание таблицы в Sybase Central Нажмите на иконку New Table (новая таблица) в верхней части экрана Нажмите правой кнопкой мышки на области экрана со списком таблиц и выберите New > Table (Новая > Таблица)

99 Временные таблицы в IQ Глобальные и Локальные Глобальные временные таблицы –Таблицы будут существовать в базе данных, до тех пор пока их не удалят –Каждый пользователь будет иметь свой собственный набор данных в такой таблице Строки будут видны только в рамках того соединения, в котором их добавили в таблицу Эти строки удаляются когда соединение закрывается Локальные временные таблицы

100 Другие команды для работы с таблицами Просмотреть информацию о таблице sp_iqtable [table-name] Изменить структуру таблицы ALTER TABLE [owner.] table-name etc. Очистить таблицу TRUNCATE TABLE [owner.] table-name Удалить таблицу DROP TABLE tablename Полный синтаксис см. в документации Sybase IQ

101 ИНДЕКСЫ Модуль 6

102 Индексы традиционных СУБД Многие базы данных используют индекс, построенный по принципу сбалансированного дерева (B-tree)

103 Индексы IQ типа «битмап» Bitmap представляет заданное значение в виде битовой маски (например: 0, 1) Эффективно индексирует данные вне зависимости от их кардинальности Легок в поддержке (нет необходимости в удалении и перестройке индексов)

104 Особенности индексов Sybase IQ Быстро строятся, быстро работают (для запросов) Компактность –Требуют меньше дискового пространства чем традиционные B-tree индексы –Больше данных может находиться в памяти Поколоночная структура хранения –Ключевой фактор для скорости обработки запросов Выбор «правильного» индекса IQ делается на основании: –типа данных колонки, –кардинальности колонки –каким образом колонка будет использоваться в запросах Каждый индекс (метод доступа) хранится и управляется по-своему Вы можете последовательно добавлять, обновлять и удалять строки в таблицы IQ без перестройки индексов

105 Работа индексов IQ в запросах В отличие от реляционных баз данных, IQ использует все индексы, всех колонок, так или иначе участвующих в конструкциях sql-запроса: –в функциях - Count(), Count Distinct, Datepart –в JOIN таблиц –в аргументы поиска (SARG) в операторе Where –в GROUP BY Для реализации среды, способной качественно справляться с любыми ad-hoc запросами, вам потребуется много индексов на в ваших таблицах

106 Девять типов индексов Sybase IQ

107 Индекс FP (Fast Projection, «Проекция») Этот индекс создается автоматически, во время выполнения операции CREATE TABLE –Также известен как Default Index (индекс по умолчанию) –Сервер создает индекс с названием ASIQ_IDX_..._FP –Может быть удален только при помощи команды Alter Table Drop Используется сервером IQ в большом количестве операций –Для выборки данных (проецировании) при операции SELECT –В поиске по шаблону – LIKE %sys% –В вычислении выражений – SUM(A+B) –В операциях Join – устанавливает соответствия между значениями колонок Единственный индекс, который может быть использован в колонках с типом данных BIT

108 Как IQ хранит FP индексы Существует 4 формы хранения FP-индексов –Форма хранения зависит от кардинальности колонки Плоский FP индекс – Flat FP –Данные хранятся в той же форме, в какой они загружались (без оптимизации структуры хранения) –Сколько места потребуется для хранения определяется типом данных колонки Char(10) – 10 байтов, Integer – 4 байта, и т.п. Оптимизированные FP индексы - FP(1),FP(2),FP(3) –Хранятся как 1, 2 или 3 байта – в зависимости от кардинальности колонки –Для оптимизации структуры хранения DBA должен включить специальную опцию IQ или для каждой колонки указать констрейнт IQ UNIQUE в команде Create Table

109 Оптимизированный FP(1) – 1 байт Входные данные конвертируются в 1 байт. Далее, создается таблица соответствий, содержащая все уникальные значения и указатели на их расположение в таблице

110 Оптимизированный FP(2) – 2 байта Когда кардинальность составляет ,536, данные сохраняются уже в двух байтах Аналогично, создается таблица соответствий со всеми уникальными значениями

111 Оптимизированный FP(3) – 3 байта IQ 15 Появился в Sybase IQ 15 Используется для данных с кардинальностью > 65,536 Структура такая же, как у FP(1) и FP(2), за исключением: –Максимальный размер таблицы соответствий - 16,777,216 –Данные сохраняются в трех байтах Так как размер колонки должен быть больше 3 байт, индекс FP(3) не может быть создан на –Bit, tinyint, smallint, char(

112 Плоский FP (Flat) Данные колонки сжимаются, но не оптимизируются В случае плоского FP индекса, данные хранятся в том же виде, в котором они загружены Continued …

113 Опция колонки IQ UNIQUE() IQ UNIQUE - это опция колонки в команде Create Table Create Table customer ( id char(10) NOT NULL IQ UNIQUE(3000) …) Ее значение задает оценочную кардинальность колонки –< сервер попытается сохранить FP индекс в 1 байте –Между 256 и попытается сохранить FP-индекс в 2 байтах –Между и 16 млн. - попытается сохранить FP-индекс в 3 байтах –> 16 млн (или опция IQ UNIQUE не задана) – данные будут сохранены в виде Плоского FP индекса Значение, указанное в IQ UNIQUE служит только одной цели – определить тип создаваемого FP индекса, поэтому оно не обязательно должно быть точным

114 Опция БД - Minimize_Storage Когда эта опция включена (= 'ON), команда Create Table будет автоматически выставлять значение IQ Unique(255) для всех колонок –Эта опция снимает необходимость добавления опции IQ UNIQUE для всех колонок в скриптах создания таблиц Использование этой опции позволяет вам быть уверенными, что вы получите максимальное сжатие данных и наилучшую производительность запросов Примечание: Существует ситуация, когда эта опция может вызвать проблемы производительности при загрузке данных. Это происходит когда производится первичная загрузка очень широкой таблицы (1000 колонок и более) и при этом приходится выполнять переформатирование из 2х- или 3х- байтового в плоский FP. Для таких таблиц эта опция должна быть выключена

115 FP индексы и Загрузка данных Ситуация: работает процесс массовой загрузки данных в таблицу, на которой созданы оптимизированные FP индексы Что произойдет, если в процессе загрузки выяснится, что значение, заданное в IQ Unique() неверно? –однобайтный FP преобразуется в двухбайтный FP индекс без потерь производительности –Двухбайтный FP преобразуется в трехбайтный FP индекс, если достаточно свободного кэша памяти –Трехбайтный FP будет полностью переформирован - в плоский FP индекс Трансформация FP(2) или FP(3) индекса – в плоский FP –Потери в производительности будут, но это разовая «неприятность» –Будут перестроены страницы данных для этой колонки –Это произойдет на лету и только один раз – Эта процедура не повлияет на последующие загрузки

116 Формат хранение FP индекса Эффективное хранение FP индекса является критичным для скорости запросов и степени сжатия данных –Эти индексы используются оптимизатором Sybase IQ –Это может существенно уменьшить дисковое пространство, занимаемое колонкой –Существенно снизится дисковый ввод/вывод во время исполнения запросов Перед началом создания таблиц DBA и команда разработчиков должны четко осознавать важность и степень влияния оптимизированных (1,2,3 байтных) FP индексов на систему –Даже несколько неправильно настроенных колонок могут существенно ухудшить производительность

117 Быстрые индексы (LF и HG) Эти индексы хранят информацию о кардинальности колонки и распределении данных –Используются оптимизатором запросов IQ для формирования планов запросов LF и HG индексы являются критичными для производительности запросов –LF индекс рекомендуется для колонок с низкой кардинальностью –HG индекс рекомендуется для колонок с высокой кардинальностью, а также для колонок-ключей и колонок, участвующих в операции join Оба типа индексов представляют собой структуры B-Tree –LF и HG – это единственные индексы, которые могут быть уникальными

118 Индекс Low Fast (LF) B-Tree + Bitmap для данных с низкой кардинальностью Для обработки запросов используется набор битовых карт, построенных для каждого уникального значения (для регулярного LF) Лучше всего подходит для колонок с небольшим количеством уникальных значений (

119 Использование индекса LF LF индекс является критичным для следующих типов операций: –Аргументы Поиска (SARG) в предложении WHERE: равенство (=), неравенство (!=), операторы IN и NOT IN –Аргументы агрегативных функций MIN(), MAX(), COUNT(), COUNT DISTINCT –Group By, Order By LF индекс также рекомендуется для запросов по диапазону –>, =, Between, Not Between)

120 Подробнее об индексе LF Каждое конкретное значение колонки сохраняется в цепочке страниц –Нули не хранятся, хранятся только 1 (true) биты Повторяющиеся значения сжимаются –LF это дешевый индекс для данных с низкой кардинальностью Максимальная рекомендуемая кардинальность –Структуры B-Tree начинают «раздуваться» при повышении кардинальности –Если при загрузке данных будет зафиксировано более уникальных значений, загрузка ОСТАНОВИТСЯ и произойдет откат назад

121 Индекс High Group (HG) B-Tree + Групповой массив (G-Array) Расширенный B-tree индекс. Для операций Join, операций сравнения (=, !=) и Group By

122 Подробнее об индексе HG В листьях этого B-tree хранится уникальное значение колонки и указатель на цепочку страниц, содержащую номера строк (ROW ID) для этого значения –Уникальный HG индекс хранит ROW ID прямо в своих страницах- листьях. Поэтому цепочек страниц в этом случае просто нет. HG индекс – это единственный индекс, который может быть составным (задаваться для нескольких колонок сразу) –Оптимизатор IQ НЕ использует составные HG индексы для поиска по отдельной колонке в индексе HG индекс требует памяти Temp Memory и места в Temp Store –для быстрой загрузки HG индекса необходимо иметь Temp-кэш достаточного размера

123 HG индекс в запросах HG индекс рекомендуется для следующих операций: ВСЕ колонки, являющиеся КЛЮЧАМИ, или по которым происходит JOIN таблиц (независимо от кардинальности) Аргументы Поиска (SARG) в предложении WHERE =, !=, IN, >, =,

124 Индекс High Non Group (HNG) Bit-Wise Индекс. Оптимизирован для операций Поиска по диапазону и агрегатных функций Пример: SELECT SUM(sales) FROM customer (2 * 64) + (3 * 32) + (2 * 16) + (1 * 8) + (3 * 4) + (2 * 2) + (3 * 1) = 283

125 HNG Индекс Разработан для запросов, где операции поиска по диапазону или функции агрегации используются для колонок с высокой кардинальностью Исключение: »колонки типа Date/Datetime »колонки с оптимизированным FP индексом Не может быть объявлен как уникальный (Unique) Не может быть составным (для нескольких колонок) Идеален для колонок, используемых в операциях: –Поиска по диапазону –BETWEEN –Функциях Min(), Max(), Sum(), Avg()

126 Подробнее об индексе HNG Индекс HNG хорошо сжимается и быстро строится Важность этого индекса несколько уменьшилась после появления в IQ других новых типов индексов –Индексы DATE/DATETIME работают быстрее на запросах по диапазону для данных типа DATE и DATETIME –Оптимизированный FP индекс может работать быстрее на запросах по диапазону, потому что он может использовать многопоточность в процессе поиска Рекомендация – Использовать HNG индекс только для колонок с очень высокой кардинальностью (> уникальных значений)

127 Индекс Compare (CMP) Этот индекс IQ используется для сравнения данных двух колонок одной и той же таблицы Набор битовых масок, хранящих бинарный результат сравнения (>,

128 Индекс CMP Для этого индекса не поддерживаются типы данных: –Bit, Float, Real, и Double Используется для выполнения следующих операций в операторе Where, = Примеры данных колонок: –Цена продажи, Цена прайслиста –ID сотрудника, ID менеджера –Date / Datetime

129 Подробнее об индексе CMP CMP – это индекс, который быстро строится и не требует много места для хранения Пользователи IQ сравнительно редко используют этот индекс –Это обусловлено ограничениями индекса (обе колонки должны быть в одной таблице и одного и того же типа данных) Также, при создании CMP индекса обе колонки становятся обязательными для вставки значения –Эквивалентно созданию колонки со свойством NOT NULL

130 Индекс Word (WD) Индекс WD предоставляет самый быстрый способ работы с данными, представляющими собой «список слов» –Применение ограничено типами данных Char() и Varchar() Слова разделяются пробелами, знаками препинания, специальными символами –По умолчанию разделителем считается любой символ, не принадлежащий к алфавитно-цифровому ряду за исключением: Дефис (-) и одинарная кавычка (') Используется для операций поиска, где в операторе WHERE содержится –Ключевое слово CONTAINS, или –Оператор LIKE (необходимы разделители, например: like % поисковое_слово %)

131 Индекс WD Пример WD индекса применительно к колонке «Адрес»: Пример запроса: Select count(*) from customer where address contains (Main)

132 Подробнее об индексе WD Индекс WD в чем-то похож на индекс HG –Требует дополнительных ресурсов при загрузке –Требует много места для хранения Важно понимать, в каких случаях следует использовать индекс WD –Поиск должен осуществляться по целому слову –Предикаты, в которых используется только часть слова и шаблон поиска (like %олок% – «молоко»), не используют WD индекс

133 Индексы DATE, TIME и DTTM Три типа индексов для обработки запросов, включающих в себя работу с данными типа Date, Time, Datetime Индекс DATE предназначен только для колонок типа DATE и используется для обработки запросов, включающих в себя работу с датой Индекс TIME предназначен только для колонок типа TIME и используется для обработки запросов, включающих в себя работу со временем Индекс DTTM предназначен только для колонок типа DATETIME или TIMESTAMP используется для обработки запросов, включающих в себя работу с датой-временем

134 Индексы DATE, TIME и DTTM Индекс ДеньМесяцГодЧасМинутаСек. День недели Квартал года Неделя года DTTM DATE TIME

135 Индексы DATE, TIME и DTTM Используйте DATE, TIME, или DTTM в следующих случаях: –Запросы по диапазону (>, =,

136 Подробнее о DATE, TIME и DTTM Как и в случае с HNG индексом, запросы по диапазону для соответствующих типов данных могут быть быстрее, с использованием оптимизированного FP индекса –Поиск с использованием оптимизированного FP индекса может быть многопоточным Возможно, нет смысла стоит строить эти индексы, если на колонке есть оптимизированный FP индекс и он используется Оптимизатором –Протестируйте время исполнения запроса с этими индексами и без них –Перестройка этих индексов происходит быстро

137 Общий алгоритм для выбора индексов 8 шагов 2. Определите колонки, которые будут использоваться в операциях JOIN 3. Определите колонки с небольшим количеством уникальных значений (1500), которые используются: в выражении SELECT в функциях как предикаты в операторе WHERE в GROUP BY FASTPROJECTION LOWFAST HIGHGROUP 1. По умолчанию, после создания таблицы (CREATE TABLE) все колонки получают FP индекс

138 Общий алгоритм для выбора индексов 8 шагов (продолжение) 5. Определите колонки с типом данных Дата или Время и использующиеся в Сравнениях BETWEEN или RANGE 6.Определите колонки, использующиеся в: Сравнениях BETWEEN или RANGE функциях AVG и SUM 7. Определите колонки, из одной и той же таблицы, которые могут участвовать в операциях сравнения: >,

139 Допустимые комбинации индексов Комбинации, которые имеют смысл, помечены *

140 Модуль 7 Загрузка данных

141 Источники данных Sybase IQ может осуществлять Вставку (insert) или Загрузку (load) данных в таблицы из следующих источников: Плоские файлы –Фиксированной ширины –Переменной ширины Именованные каналы (Named pipes) Таблицы внешних СУБД (Sybase ASE, Microsoft, Oracle, IBM DB2) Таблицы самой Sybase IQ Прямой ввод данных (insert A, insert B, …) Продукты ETL – Sybase ETL (Solonde), а также популярные инструменты третьих фирм: Informatica, Datastage, и др. Репликация Sybase Replication Server – Real-Time Loading

142 Загрузка таблиц LOAD TABLE –Загружает данные из файлов или из именованных каналов INSERT FROM SELECT –Загружает данные из таблицы в таблицу в той же базе IQ INSERT … LOCATION –Загружает данные из таблиц другого сервера в таблицы IQ CIS и INSERT FROM SELECT –Загружает данные из таблиц другого сервера в таблицы IQ INSERT VALUES –ручная вставка данных в колонки IQ Утилита iq_bcp (только в версии IQ 12.7) –Загружает данные из файлов

143 LOAD TABLE Позволяет осуществлять массовую загрузку данных в отдельную таблицу, из плоских файлов или именованных каналов, содержащих текстовые(ASCII) или бинарные данные –Это самый быстрый метод загрузки данных в таблицу Позволяет добавлять данные к существующим строкам таблицы –По умолчанию, сначала заполняет пустые строки таблицы, затем добавляет новые строки –Не изменяет содержимого существующих строк Включает опции, позволяющие обрабатывать ошибки загрузки данных и переносить не прошедшие загрузку строки в специальный файл ОС Пользователь, загружающий данные, должен обладать правами на выполнение команды LOAD TABLE для данной таблицы

144 Загрузка и блокировка данных Sybase IQ накладывает эксклюзивную DML блокировку таблицы на запись для команд LOAD TABLE, INSERT, UPDATE и DELETE Влияние DML блокировки –В то время, когда кто-то загружает, вставляет или модифицирует данные таблицы, другие пользователи могут продолжать выполнять запросы к той же таблице –Операции по загрузке/изменению данных могут выполнятся несколькими пользователями одновременно Но они должны модифицировать разные таблицы –Если пользователи попытаются одновременно изменять данные одной и той же таблицы, «победит» тот, кто успел первым поставить DML блокировку Другие пользователи получат сообщение об ошибке

145 Синтаксис команды LOAD TABLE LOAD [ INTO ] TABLE [ owner ].table-name... ( load-specification [,...] )... [ FROM | USING [CLIENT] FILE ] { 'filename-string' | filename-variable } [,...]... [ CHECK CONSTRAINTS { ON | OFF } IGNORE CONSTRAINT constrainttype[,...] ]... QUOTES OFF... ESCAPES OFF... [ FORMAT { 'ascii' | 'binary | bcp } ]... [ DELIMITED BY 'string' ]... [ STRIP { ON | OFF } ]... [ WITH CHECKPOINT { ON | OFF } ]... [ { BLOCK FACTOR number | BLOCK SIZE number } ]... [ BYTE ORDER { NATIVE | HIGH | LOW } ]... [ LIMIT number-of-rows ]... [ NOTIFY number-of-rows ]... [ ON FILE ERROR { ROLLBACK | FINISH | CONTINUE} ]... [ PREVIEW { ON | OFF } ]... [ ROW DELIMITED BY 'delimiter-string' ]... [ SKIP number-of-rows ]... [ START ROW ID number ]... [ UNLOAD FORMAT ]... [ IGNORE CONSTRAINT constrainttype [,...] ]... [ MESSAGE LOG string ROW LOG string [ ONLY LOG logwhat [,...] ]... [ LOG DELIMITED BY string ]

146 Команда LOAD TABLE Содержит три части: Спецификации колонок и формата их загрузки –Описывает каждую загружаемую колонку и формат соответствующих данных во входном файле Оператор FROM –Описывает входной файл (файлы), который должны быть загружен –В IQ 15 FROM больше не используется Опции загрузки –Дальнейшее описание набора входных данных –Контроль поведения операции загрузки

147 Спецификации колонок Определяет колонки таблицы, которые должны быть загружены Описывает раскладку входного файла с помощью спецификаторов колонок –Задает длину (для полей фиксированной длины) или указывает разделитель (для полей переменной длины) Либо можно указать глобальный разделитель в блоке опций загрузки –Задает формат для входных данных типа Date, Time или Datetime –Задает, какие строковые значения входных данных должны быть интерпретированы как NULL –Указывает, какие части входного файла должны быть проигнорированы (используя FILLER) Для корректного составления спецификации загрузки, нужно внимательно просчитать каждый загружаемый байт файла данных

148 Пример: Загрузка из текстового файла Данные переменной длины, Разделитель – вертикальная черта (кроме последнего поля) LOAD TABLE customer (customer_id '|', cust_type '|', organization '|', contact_name '|', contact_phone '|', address '|', city '|', state_province '|', postalcode '|', country '\x0a' ) FROM '/work/data/customer1.dat' ESCAPES OFF QUOTES OFF;

149 Оператор FROM Определяет входной файл(ы) /именованный канал(ы), который должен быть загружен –Путь задается относительно машины, на которой находится сервер Sybase IQ FROM 'filename-string' [,…] filename-string передается серверу как строка –Имя файла может быть именованным каналом(Named Pipe) –Для корректного задания пути к директории на Windows, обратная косая черта (\) должна быть представлена в виде двух косых черт (\\) Пример загрузки FROM C:\\iqdata\\input.dat' 12.7

150 Оператор USING FILE Определяет входной файл(ы), который должен быть загружен –Путь задается относительно машины, на которой находится сервер Sybase IQ USING [CLIENT] FILE 'filename-string' [,…] filename-string передается серверу как строка –Исходный файл может находиться на клиентской машине –Исходный файл может находиться на серверной машине Sybase IQ –Для корректного задания пути к директории на Windows, обратная косая черта (\) должна быть представлена в виде двух косых черт (\\) Пример загрузки локального файла, расположенного на том же сервере, где работает и сам сервер Sybase IQ: – USING FILE C:\\iqdata\\input.dat' IQ 15

151 Загрузка из нескольких входных файлов Позволяет загрузить большой объем входных данных в один этап

152 Загрузка из нескольких файлов Когда есть множество файлов, которые нужно загрузить в таблицу, - лучше выполнить эту загрузку в одной транзакции –Эта техника гораздо быстрее, чем загрузка и подтверждение транзакции для каждого отдельного файла В команде LOAD TABLE нужно задать все входные файлы в операторе from –Используйте опцию ON FILE ERROR для контроля поведения загрузки, в случае, если система не может получить доступ к входному файлу

153 Обработка проблем загрузки данных в команде LOAD TABLE Выполнение команды LOAD TABLE остановится (выполнится откат) in в каждой из следующих ситуаций: –Повторение первичного или повторение уникального ключа –Внешний ключ не найден в таблице, на которую он ссылается –Ошибки преобразования данных В команде LOAD TABLE есть дополнительный синтаксис для : –Возможности отбросить входные строки, нарушающие какие-то ограничения (Primary Key и т.п.) или не проходящие по формату, и продолжить загрузку –Задания предельного количества нарушений ограничений (constraints) перед тем, как начать откат операции загрузки –Журналирования всех возникших ошибок –Создания «файла отказов», куда будут помещены все отброшенные записи

154 Рекомендации по загрузке Использовать команду LOAD TABLE для загрузки из файлов –Стараться выполнять загрузку всех файлов в одной транзакции Располагать файлы для загрузки на отдельном диске (отдельном от дисков базы данных) Создавать все индексы IQ до загрузки данных Настраивать память под загрузку –Использовать опцию LOAD_MEMORY_MB для ограничения размера кучи (heap size) –Правильно настроить IQ Temp Cache для HG индексов Выполнять загрузку в часы наименьшей нагрузки на сервер –Для загрузки больших объемов данных рассмотреть возможность использования IQ Multiplex и выделенного узла «Писателя»

155 Вставка данных Команда INSERT from SELECT –Позволяет выполнить массовую вставку в таблицу результатов, полученных от выполнения оператора SELECT из таблицы в той же базе данных Команда INSERT from SELECT (другой сервер / база данных) - CIS –Позволяет осуществить выборку строк для вставки из другой базы данных. Команда INSERT …. LOCATION –Выполняет вставку непосредственно из удаленной базы данных Команда INSERT VALUES –Вставка одной строки с заданными значениями

156 Удаление данных Выражение DELETE –Удаляет данные в соответствии с заданными критериями DROP TABLE –Полностью удаляет таблицу, вместе с ее данными и индексами TRUNCATE TABLE –Удаляет все строки из таблицы

157 Изменение данных Команда UPDATE –Используется для модификации данных таблицы –Это может быть основная или временная таблица

158 Реляционные базы - источники База данных IQ Плоские файлы Sybase IQ Разделяемые файлы UNIX NFS Mount Windows Network Drive ETL Сервер DB API FTP ODBC Плоские файлы Client-side Load Server-side Load Архитектура загрузки ETL инструментом

159 Businesses demand fast, flexible access to mission- critical information. Sybase IQ 15.2 now delivers: Flexibility via Sybase ETL data transformation and load capability, Speed via Sybase Replication Servers Real-Time Loading capability. Sybase IQ Replication Server 15.5 = Real-time loading Replication Server – Real Time Loading Edition 15.5 now supports both batch and continuous data movement from ASE to Sybase IQ Sybase ETL 4.9 with CDC capability using Replication Server as CDC agent provides real- time data loading Sybase ETL 4.9 provides upsert (micro-batching) capability for data from other sources Sybase IQ ASE DB2 Oracle Replication Server Sybase ETL Fast Loading Data Transformation Needed for: Real-time data access for customer service, troubleshooting, order fulfillment, other just-in-time reporting

160 REAL-TIME LOADING PERFORMANCE Use design partner data and transactions at primary DB –Replicate data to DBon with RTL on –Replicate data to DBoff with RTL off Show performance difference between DBon and DBoff DS (ASE) DB RS DBon DSon (IQ) PUBSUBon DBoff DSoff (IQ) SUBoff

161 REAL-TIME LOADING PERFORMANCE TEST DATA –Two tables (56 columns and 13 columns) –685MB log size generated by captured design partner transactions –6,937 Transactions with 1,192,642 CMDs (172 CMD/Tran) Largest transaction has 289,532 commands 91% inserts, 8% deletes, and 1% updates

162 Replication Server для загрузки в IQ Когда репликация является подходящим механизмом? –Когда объем данных невелик –Когда изменения происходят в течение дня –Когда режим реального времени не является обязательным условием –Когда у вас есть RS 15.5 ! загрузка в реальном масштабе времени

163 Использование iq_bcp Клиентская утилита для загрузки данных в IQ –Доступна только в версии IQ 12.7 Альтернатива серверному механизму массовой загрузки (LOAD TABLE) В настоящее время имеет ряд ограничений –Не задействуют механизм массовой загрузки IQ –Осуществляет вставку данных в виде пакета insert предложений –Быстрее чем вставка одной строки, но даже близко не сравнима по скорости с операциями LOAD TABLE и INSERT FROM LOCATION 12.7

164 Модуль 8 Работа с Sybase IQ

165 dbisql и dbisqlc Обе утилиты поставляются с серверным и клиентским программным обеспечением IQ –ODBC драйвер для Sybase IQ включен в дистрибутив IQ DBISQL (Interactive SQL Java) – более предпочтительный клиент –Лучше использовать его (кроме случаев, когда документация явно рекомендует что-то другое) –Обладает более удобным пользовательским интерфейсом и более широким функционалом Возможность поиска сервера Сохраняет историю введенных SQL запросов Поддержка кнопок мыши под Unix Возможность выполнять операции копирования и вставки

166 Sybase Central Предоставляет графический интерфейс для администрирования сервера IQ –Большинство операций по администрированию IQ может быть выполнено при помощи SQL команд –Одним из исключений является создание кластера IQ Multiplex. Для этого используйте Sybase Central. Позволяет подключать «плагины» других серверных продуктов Sybase

167 Возможности Sybase Central Создание баз данных Старт и остановка сервера Управление пользователями/группами и их правами Добавление/удаление dbspace Администрирование кластера Multiplex Далее следует краткий обзор некоторых его функций

168 Sybase Central – Подключение к IQ Из строки меню, пункт Tools Ручное соединение, используя Connect Создание «Профиля» для соединения (Connection profile) – для более быстрого доступа к базе IQ Регистрация плагинов других серверных продуктов, используя пункт меню Plug-ins Соединение с сервером, используя ODBC или JDBC Использует такой же диалог для подключения, что и в клиенте dbisql

169 Навигация по Sybase Central Соединиться – Отсоединиться – Профайлы – Обновить - Свойства Имя сервера «Бочка» базы данных Тулбар Папки разных объектов базы данных Плагин IQ Нажмите правой кнопкой мыши на этой панели для отображения возможных действий

170 Свойства таблицы Нажмите правой кнопкой мыши на таблице, чтобы открыть ее свойства Закладка Columns отображает информацию, касающуюся колонок Тип FP индекса, плотность Закладка Placement отображает размер индексов и местоположение dbspace

171 Свойства колонок таблицы

172 Язык SQL в Sybase IQ Соответствует стандартам ANSI SQL 92/99 Поддерживает функции и команды, не входящие в стандарт ANSI –Это так называемые расширения поставщика для ANSI SQL Совместимость с другими серверными продуктами Sybase –В хранимых процедурах используется тот же T-SQL и Watcom SQL что и в ASA Существует ряд различий между ASE T-SQL и ASA T-SQL –Существует ряд различий в поведении при использовании соединения через Open Client и других соединений (ODBC, JDBC и т.п.) Соединение Open Client не соответствует стандарту ANSI

173 Экспорт данных из Sybase IQ Экспорт данных производится командой SELECT, используя опции TEMP_EXTRACT Экспортирует данные в файл на той машине, где работает Sybase IQ Server Может выводить данные в несколько файлов параллельно (максимум 8) Это самый быстрый метод экспорта данных Перенаправление, используя команду SELECT и значок ># Экспортирует данные в файл на клиентской машине Внешняя утилита: iq_bcp { in | out } только для IQ 12.7 В $ASDIR/bin – там где располагаются бинарные файлы сервера IQ

174 174 – Sybase Confidential – February 13, 2009 ВОПРОСЫ и ОТВЕТЫ

175 Quick Start

176 176 – Sybase Confidential – February 13, 2009 ВВЕДЕНИЕ в SYBASE IQ Обзорный тренинг Москва, 4 июня, 2010 Андрей Хромов, технический консультант, Sybase CIS