Сбор статистики для таблиц MS SQL Server.

Приветствую.

Продолжим изучать описанный ранее подход сбора статистики для таблиц БД.

Моя цель - улучшить скрипт и разобраться немного с процессом сбора статистики для таблиц MS SQL Server. Статистику буду собирать по всем колонкам заданной таблицы.

Итак, документация дает следующее описание синтаксиса команды сбора статистики:

Второй момент, в котором необходимо разобраться - процесс получения списка колонок из метаданных БД. Согласно документации для этого существует представление sys.columns:

В результате получилась следующая процедура:

Вкладка Command On Target:

import string
import java.sql as sql
import java.lang as lang

sourceConnection = odiRef.getJDBCConnection("SRC")

stmt = sourceConnection.createStatement()

sql_use = "USE #s_Catalog; "

sql_columns = sql_use + "select col.name as CName, tab.name as TName from sys.all_columns col inner join sys.all_objects tab on col.object_id = tab.object_id and tab.name = '<%=odiRef.getOption( "TableName" )%>' and tab.type = 'U'"

col_result = stmt.executeQuery(sql_columns)

res = ''
r_TableName = ''
r_ColName = ''

try:
  while (col_result.next()):
    r_TableName = str(col_result.getString("TName"))
    r_ColName = str(col_result.getString("CName"))

    sql_stats = sql_use + "IF EXISTS (SELECT name FROM sys.stats WHERE name = N'" + r_ColName + "' AND object_id = OBJECT_ID(N'" + r_TableName + "')) DROP STATISTICS " + r_TableName + "." + r_ColName + ";"
    sql_stats = sql_stats + 'CREATE STATISTICS ' + r_ColName + ' on ' + r_TableName + ' (' + r_ColName + ');'
    res = res + sql_stats + '\n'
    #sql_stats = 'select 1'
    #stmt2.executeQuery(sql_stats)
except:
  raise res, sql_stats

#sourceConnection.close()

Вкладка Command On Source:

select '<%=odiRef.getCatalogName()%>' as s_Catalog

С какими сложностями пришлось столкнуться:

1. Я не нашел необходимого представления, которое бы возвращало список всех колонок всех таблиц одного сервера. Таким образом, селект по списку колонок возвращал пустой результат для заданного имени таблицы. Изменение представления на sys.all_columns не изменило ситуацию.
2. Причиной этого стало, скорее всего, то, что для логина odi_dev, который был указан как пользователь для схемы Target использовалась БД по-умолчанию tempdb

3. Чтобы можно было использовать эту процедуру для сбора статистики по любой таблице любой БД данного сервера я решил использовать команду USE перед запросом списка колонок из sys.all_columns

4. Получить имя БД оказалось не совсем просто. Можно было бы его передавать как параметр, но я хотел попробовать провернуть такой же трюк, как и с получением подключения с помощью команды odiRef.getJDBCConnection("SRC").
5. Мне нужно было узнать имя физической БД для выбранной на вкладке Command on Source логической схемы. Лучшее, что я смог сделать выглядит как селект имени каталога в переменную подстановки:

6. Полученное имя каталога добавлялось к команде получения списка колонок таблицы.

7. Так как MS SQL Server не имеет команды пересбора статистики, пришлось найти в хелпе команду определения, существует ли статистика для заданной колонки, и, в этом случае, удалять ее перед созданием.
8. По прежнему темной частью джава материи остается для меня необходимость закрытия соединения в конце выполнения сбора статистики. В данном примере при наличии команды закрытия выдается сообщение об ошибке: 0 : null : com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. Думаю вернуться к этому немного позже.

Изначально я планировал показать, как можно сделать сбор статистики частью модуля знаний. Для этого я собирался воспользоваться исключительно методами подстановки, в частности, методом GetColList().

К сожалению, я не смог его использовать, так как метод возвращает исключительно список всех столбцов всех таблиц источников, без названия этих самых таблиц. Поэтому идея собирать статистику с источников перед тем, как загружать данные в I$_ таблицу, сработала бы (с использованием GetColList()) только для интерфейсов с одним источником данных.

Добавлю как пожелание к списку чего нам не хватает в ODI.