ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Опять pivot!
16 сообщений из 16, страница 1 из 1
Опять pivot!
    #37559069
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Господа, прошу помощи в транспонировании строк в столбцы (DB2 9.1 Z/OS). Есть таблица ТИКЕТ и связанная с ней ТИКЕТ ХИСТОРИ.Тикет_намбер - РК в ТИКЕТ. ТИКЕТ ХИСТОРИ имеет составной РК, в который входит и Тикет_намбер. Одной записи в ТИКЕТ может соответствовать любое кол-во записей в ТИКЕТ ХИСТОРИ. Задача: нужен запрос(не процедура!) который для каждого тикет_намбер возвращает ОДНУ запись где будут аггрегированы ВСЕ записи из ТИКЕТ ХИСТОРИ, относящиеся к этому тикет намберу.

Решение: http://it.toolbox.com/blogs/db2luw/pivot-query-12757 НЕ работает, так как может быть несколько хистори записей одного типа, и надо выбрать их все.
Решение с использованием xmlaggregate - работает отлично на тест инстансе.
НО вот проблема - DB2 Z/OS продакшн инстанс НЕ имеет xml функций! (И ничего с этим сделать нельзя :( )
Задача довольно срочная, прошу помощи!

ПС: если нужно могу выложить ДДЛ таблиц и работающий запрос с xml функциями.
...
Рейтинг: 0 / 0
05.12.2011, 12:45
| Ответить | Цитировать | Написать  
Опять pivot!
    #37559281
Евгений Хабаров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Евгений Хабаров
Гость
Vlad251470,

Если у вас действительно DB2 for z/OS версии 9.1, то функция XMLAGG там есть в обязательном порядке, т.к. это часть "движка" версии 9.1. Что выдаст следующий запрос на вашей системе DB2 for z/OS?
Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT XMLAGG(XMLELEMENT(NAME "A",RESULTS)) FROM 
( 
SELECT '1' AS RESULTS FROM SYSIBM.SYSDUMMY1 
UNION ALL 
SELECT '2' AS RESULTS FROM SYSIBM.SYSDUMMY1 
) AS A


Должен вернуть:
Код: plaintext
1.
<?xml version="1.0" encoding="IBM01025"?><A>1</A><A>2</A>
...
Рейтинг: 0 / 0
05.12.2011, 14:28
| Ответить | Цитировать | Написать  
Опять pivot!
    #37559510
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
возвращает: 5:03:17 [SELECT - 0 row(s), 0.000 secs] [Error Code: -270, SQL State: 42997] DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=null, DRIVER=3.57.82
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
...
Рейтинг: 0 / 0
05.12.2011, 16:04
| Ответить | Цитировать | Написать  
Опять pivot!
    #37559740
Евгений Хабаров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Евгений Хабаров
Гость
Vlad251470,
Понимаю, что это не ответ на изначальный вопрос, но, либо указанная подсистема не является полнофункциональной (New Function) 9.1, т.е. либо это более низкая версия, или же в ней еще идет процесс миграции в 9-ку (режим Compatibility Mode).
Но, возможно что тут идет сквозное подключение к еще одной удаленной подсистеме. Т.е. физически эта таблица находится не в той подсистеме, к которой выполнено подключение, а в еще одной, которая "спрятана" за этой.
...
Рейтинг: 0 / 0
05.12.2011, 17:42
| Ответить | Цитировать | Написать  
Опять pivot!
    #37559781
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Хоть и не ответ, но все равно спасибо за внимаие:) Скорее всего - второе.
...
Рейтинг: 0 / 0
05.12.2011, 18:05
| Ответить | Цитировать | Написать  
Опять pivot!
    #37560563
Mark Barinstein
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mark Barinstein
Гость
Vlad251470,
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
with 
-- исходные данные
  ticket(ticket_number) as (
          select 1 from sysibm.sysdummy1
union all select 2 from sysibm.sysdummy1
union all select 3 from sysibm.sysdummy1
)
, ticket_history(ticket_number, ticket_h_id, ticket_h_val) as (
          select 1, 1,  't1_h1' from sysibm.sysdummy1
union all select 1, 3,  't1_h2' from sysibm.sysdummy1
union all select 2, 10, 't2_h1' from sysibm.sysdummy1
union all select 2, 20, 't2_h2' from sysibm.sysdummy1
union all select 2, 30, 't2_h3' from sysibm.sysdummy1
)
-- перенумерация ticket_h_id для удобства
, ticket_history_o(ticket_number, ticket_o_id, ticket_h_val) as (
select ticket_number, rownumber() over(partition by ticket_number order by ticket_h_id), ticket_h_val
from ticket_history
)
-- рекурсивное общее табличное выражение, где мы агрегируем значения в последнем поле
, a(ticket_number, ticket_o_id, ticket_h_val_agg) as (
select ticket_number, ticket_o_id, cast(ticket_h_val as varchar(1000))
from ticket_history_o 
where ticket_o_id=1
  union all
select a.ticket_number, o.ticket_o_id, a.ticket_h_val_agg||', '||o.ticket_h_val
from a, ticket_history_o o
where o.ticket_number=a.ticket_number and o.ticket_o_id=a.ticket_o_id+1
)
-- заключительный запрос
select t.ticket_number, h.ticket_h_val_agg
from ticket t
left join (
select a.ticket_number, a.ticket_h_val_agg
from a
join (
select ticket_number, max(ticket_o_id) ticket_o_id
from a
group by ticket_number
) g on a.ticket_number=g.ticket_number and a.ticket_o_id=g.ticket_o_id
) h on t.ticket_number=h.ticket_number
...
Рейтинг: 0 / 0
06.12.2011, 11:12
| Ответить | Цитировать | Написать  
Опять pivot!
    #37561655
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Огромное спасибо за подсказку! :) Буду пытаться прикручивать к своим таблицам (надо еще результат выдать в json).
Сам начал отлаживать две временные таблицы, как у вас - но:
-либо у меня всегда выбиралась только запись первого тикета (в вашем примере - 1 t1_h1, t1_h2)
-либо упирался в -433 ошибку (Short Description: VALUE IS TOO LONG)- возможно потому что отлаживал без заключительных джойнов, просто с select * по второй временной таблице.
...
Рейтинг: 0 / 0
06.12.2011, 18:55
| Ответить | Цитировать | Написать  
Опять pivot!
    #37564883
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Mark Barinstein,

еще раз огромное спасибо за помощь! Запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
with 
-- order history recs
 ticket_history_o(ticket_number, hist_ssc_id, hist_src_uid, hist_h_t, ticket_o_id, hist_c_val, hist_src_dttm) as (
select ticket_number, char(SOURCE_SYSTEM_CUST_ID), char(SRC_UID), HISTORY_TYPE, 
rownumber() over(partition by ticket_number, SOURCE_SYSTEM_CUST_ID order by SRC_MODIFY_DTTM), 
CURR_VALUE, 
char(SRC_MODIFY_DTTM)
from gsmrtacc.T_TICKET_HISTORY  where ticket_number like 'IN10%' 
),
a(ticket_number, hist_ssc_id_agg, hist_src_uid_agg, hist_h_t_agg, ticket_o_id, hist_c_val_agg, hist_src_dttm_agg) as (
select ticket_number, 
cast('hist_ssc_id'||ltrim(rtrim(char(ticket_o_id)))||':'||ltrim(rtrim(hist_ssc_id)) as varchar(3000)),
cast('hist_src_uid'||ltrim(rtrim(char(ticket_o_id)))||':'||ltrim(rtrim(hist_src_uid)) as varchar(3000)),
CAST('h_type'||ltrim(rtrim(char(ticket_o_id)))||':'||hist_h_t as varchar(3000)) ,
ticket_o_id, 
cast('hist_c_val'||ltrim(rtrim(char(ticket_o_id)))||':'||hist_c_val as varchar(3000)),
cast('hist_src_dttm'||ltrim(rtrim(char(ticket_o_id)))||':'||hist_src_dttm as varchar(3000))
from ticket_history_o 
where ticket_o_id=1
  union all
select a.ticket_number, 
a.hist_ssc_id_agg||',hist_ssc_id'||ltrim(rtrim(char(o.ticket_o_id)))||':'||ltrim(rtrim(o.hist_ssc_id)),
a.hist_src_uid_agg||',hist_src_uid '||ltrim(rtrim(char(o.ticket_o_id)))||':'||ltrim(rtrim(o.hist_src_uid)),
a.hist_h_t_agg||',h_type'||ltrim(rtrim(char(o.ticket_o_id)))||':'||o.hist_h_t ,
o.ticket_o_id , 
a.hist_c_val_agg||',hist_c_val '||ltrim(rtrim(char(o.ticket_o_id)))||':'||o.hist_c_val ,
a.hist_src_dttm_agg||',hist_src_dttm'||ltrim(rtrim(char(o.ticket_o_id)))||':'||o.hist_src_dttm 
from a, ticket_history_o o
where o.ticket_number=a.ticket_number and o.ticket_o_id=a.ticket_o_id+1
)
--final select 
select t.ticket_number, h.hist_ssc_id_agg, h.hist_src_uid_agg, h.hist_h_t_agg, h.hist_c_val_agg, h.hist_src_dttm_agg, h.ticket_o_id as no_of_hist_recs
from gsmrtacc.t_ticket t
left join (
select a.ticket_number, a.hist_ssc_id_agg,
a.hist_src_uid_agg,
a.hist_h_t_agg, a.hist_c_val_agg, a.hist_src_dttm_agg, a.ticket_o_id
from a
join (
select ticket_number, max(ticket_o_id) ticket_o_id
from a
group by ticket_number
) g on a.ticket_number=g.ticket_number and a.ticket_o_id=g.ticket_o_id
) h on t.ticket_number=h.ticket_number;



работает отлично, НО - только если кол-во записей в первой временной таблице невелико. Стоит закомментировать where в ticket_history_o - запрос на 300К записей работает очень долго (честно мне не удалось дождаться его окончания, прибил после 30 минут.)
Также долго работает и простая выборка из а:
with
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
-- order history recs
 ticket_history_o(ticket_number, hist_ssc_id, hist_src_uid, hist_h_t, ticket_o_id, hist_c_val, hist_src_dttm) as (
select ticket_number, char(SOURCE_SYSTEM_CUST_ID), char(SRC_UID), HISTORY_TYPE, 
rownumber() over(partition by ticket_number, SOURCE_SYSTEM_CUST_ID order by SRC_MODIFY_DTTM), 
CURR_VALUE, 
char(SRC_MODIFY_DTTM)
from gsmrtacc.T_TICKET_HISTORY  --where ticket_number like 'IN10%' 
),
a(ticket_number, hist_ssc_id_agg, hist_src_uid_agg, hist_h_t_agg, ticket_o_id, hist_c_val_agg, hist_src_dttm_agg) as (
select ticket_number, 
cast('hist_ssc_id'||ltrim(rtrim(char(ticket_o_id)))||':'||ltrim(rtrim(hist_ssc_id)) as varchar(3000)),
cast('hist_src_uid'||ltrim(rtrim(char(ticket_o_id)))||':'||ltrim(rtrim(hist_src_uid)) as varchar(3000)),
CAST('h_type'||ltrim(rtrim(char(ticket_o_id)))||':'||hist_h_t as varchar(3000)) ,
ticket_o_id, 
cast('hist_c_val'||ltrim(rtrim(char(ticket_o_id)))||':'||hist_c_val as varchar(3000)),
cast('hist_src_dttm'||ltrim(rtrim(char(ticket_o_id)))||':'||hist_src_dttm as varchar(3000))
from ticket_history_o 
where ticket_o_id=1
  union all
select a.ticket_number, 
a.hist_ssc_id_agg||',hist_ssc_id'||ltrim(rtrim(char(o.ticket_o_id)))||':'||ltrim(rtrim(o.hist_ssc_id)),
a.hist_src_uid_agg||',hist_src_uid '||ltrim(rtrim(char(o.ticket_o_id)))||':'||ltrim(rtrim(o.hist_src_uid)),
a.hist_h_t_agg||',h_type'||ltrim(rtrim(char(o.ticket_o_id)))||':'||o.hist_h_t ,
o.ticket_o_id , 
a.hist_c_val_agg||',hist_c_val '||ltrim(rtrim(char(o.ticket_o_id)))||':'||o.hist_c_val ,
a.hist_src_dttm_agg||',hist_src_dttm'||ltrim(rtrim(char(o.ticket_o_id)))||':'||o.hist_src_dttm 
from a, ticket_history_o o
where o.ticket_number=a.ticket_number and o.ticket_o_id=a.ticket_o_id+1
)
select count(*) from a;


Это из-за того что а не имеет индексов? Или я чтото сделал не так? Как проверить не входит ли в бесконечную рекурсию? Можно ли както оптимизировать?
ПС: Данные в тикет хистори стянуты с продакшена, так что скорее всего они корректные.
...
Рейтинг: 0 / 0
08.12.2011, 12:31
| Ответить | Цитировать | Написать  
Опять pivot!
    #37565224
Mark Barinstein
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Mark Barinstein
Гость
Vlad251470,

1.
Если вы используете нумерацию внутри группы из 2-х полей:
Код: plaintext
rownumber() over(partition by ticket_number, SOURCE_SYSTEM_CUST_ID order by SRC_MODIFY_DTTM)
то везде вместо
Код: plaintext
T1.ticket_number=T2.ticket_number
вы должны использовать
Код: plaintext
T1.ticket_number=T2.ticket_number and T1.SOURCE_SYSTEM_CUST_ID=T2.SOURCE_SYSTEM_CUST_ID

и соотв. вместо
Код: plaintext
group by ticket_number
использовать
Код: plaintext
group by ticket_number, SOURCE_SYSTEM_CUST_ID

2.
Можете создать временную таблицу и соотв. в запросе обращаться к ней:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
declare global temporary table session.ticket_history_o ...;

insert into session.ticket_history_o(ticket_number, hist_ssc_id, hist_src_uid, hist_h_t, ticket_o_id, hist_c_val, hist_src_dttm)
select ticket_number, char(SOURCE_SYSTEM_CUST_ID), char(SRC_UID), HISTORY_TYPE, 
rownumber() over(partition by ticket_number, SOURCE_SYSTEM_CUST_ID order by SRC_MODIFY_DTTM), 
CURR_VALUE, 
char(SRC_MODIFY_DTTM)
from gsmrtacc.T_TICKET_HISTORY;

create index ticket1 on session.ticket_history_o(ticket_number, SOURCE_SYSTEM_CUST_ID, ticket_o_id);
...
Рейтинг: 0 / 0
08.12.2011, 14:16
| Ответить | Цитировать | Написать  
Опять pivot!
    #37566002
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Mark Barinstein,

1- не придал значения т к тест. данные уникальны по тикет намбер. Поправил. Производительность НЕ улучшилась. Попробую через декларе с индексом. Спасибо!
...
Рейтинг: 0 / 0
08.12.2011, 18:32
| Ответить | Цитировать | Написать  
Опять pivot!
    #37566403
Victor Metelitsa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor Metelitsa
Участник
Предлагаю попробовать ленивый способ - залить данные в DB2 for Unix/Linux/Windows (пробную или Express-C), собрать статистику и спросить её Index Adviser, что он думает по поводу вашего запроса. А он может насоветовать кучу полезных индексов.
...
Рейтинг: 0 / 0
08.12.2011, 23:38
| Ответить | Цитировать | Написать  
Опять pivot!
    #37567207
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Victor Metelitsa,

есть инстанс с похожими данными на аиксе, но у меня нет доступа ни к одному из дб2 серверов:(.
Попробую спросить админов насчет индекс адвайзера.
Спасибо за совет!
...
Рейтинг: 0 / 0
09.12.2011, 12:41
| Ответить | Цитировать | Написать  
Опять pivot!
    #37567829
Victor Metelitsa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor Metelitsa
Участник
Цены на память так упали, что я на днях без напряга собрал себе "домашний" писюк с 24 гигами ОЗУ (сокет 1366). Не за горами большое падение цен на 8-гиговые модули, можно будет на сокете 1155 набивать по 32 гига по малой цене. SATA-диски хоть подорожали, но ведь их объёмы таки гигантские, по совсем недавним меркам, много каких баз можно разместить. В продакшен такое, конечно, нельзя, но для развлечений вполне сойдёт.
...
Рейтинг: 0 / 0
09.12.2011, 16:15
| Ответить | Цитировать | Написать  
Опять pivot!
    #37567949
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Victor Metelitsa,

под какой системой эти 24 гига?
...
Рейтинг: 0 / 0
09.12.2011, 17:03
| Ответить | Цитировать | Написать  
Опять pivot!
    #37568484
Victor Metelitsa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Victor Metelitsa
Участник
Vlad251470,

какая разница, какая система? На своём компьютере можно переставлять систему и ставить СУБД хоть каждый день по нескольку раз, и не надо ни у кого ничего спрашивать. Это свобода. Я, конечно, хотел, чтобы раскошелилась моя контора, но, в конце концов, пожав плечами, купил сам. Конечно, DB2 for Z/OS и for LUW - это далеко не одно и то же, но, тем не менее...

Впрочем, быть может, на мэйнфреймовой DB2 тоже есть Index Adviser, но мне она абсолютно недоступна и я потому не вижу смысла интересоваться его наличием. На DB2 for LUW он может давать гигантское ускорение, а принципы схожи.
...
Рейтинг: 0 / 0
09.12.2011, 23:01
| Ответить | Цитировать | Написать  
Опять pivot!
    #37571233
Vlad251470
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad251470
Гость
Victor Metelitsa,

ok идея понятна.
...
Рейтинг: 0 / 0
12.12.2011, 16:33
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / Опять pivot!
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]