Report SQL Different in DB2 and in SQL server- Urgent

[sanjaydhol]

Thanks a lot for the last reply. I changed the VLDB property and it solve the problem. I have another question. And most probably it is something related with the same VLDB property. I have one report generated from two MSTR project( Identical projects). One is connecting to DB2 DW (Metadata is in ORACLE) and another one is using MS Sql server as DW(Metadata is MS Access).

In the report we have several metrics. For each one of these Metrics we have some aggregate function. Now in Sql server version the report SQL query it is doing the aggregate and then storing the result in temp table in one pass.
But in DB2 version it is doing the same in three two steps. First one taking the distinct values in one table then from that table it is doing the aggregate and storing it in another table and then from that it is doing the rest join.

I am attaching the SQL query portion for clarification.

SQL Server Version
select a11.MODEL_WO_NUM MODEL_WO_NUM,
a13.EQUIP_TD_CAT_CD EQUIP_TD_CAT_CD,
a13.CLIENT_ID CLIENT_ID,
count(a11.PEND_WO_COUNT_CD) WJXBFS1
into #ZZMD00
from WDW210_WO_TSK a11
left outer join WDW212_WO_AST_DTLS a12
on (a11.WO_TSK_ID = a12.WO_TSK_ID)
left outer join WDW201_EQ a13
on (a12.EQUIP_SEQ_NUM = a13.EQUIP_SEQ_NUM)
where (a11.PEND_WO_COUNT_CD in (1)
and a11.WORK_ORDER_TYPE_CD in ('PD')
and a13.EQUIPMENT_NAM in ('POLE-DISTRIBUTION ', 'POLE-TRANSM/DISTRN ')
and a11.MODEL_WO_NUM in ('28077720', '28077721', '28077722', '28068951')
and a13.EQUIP_TD_CAT_CD in ('Distribution'))
group by a11.MODEL_WO_NUM,
a13.EQUIP_TD_CAT_CD,
a13.CLIENT_ID

In DB2

select a15.REGION_NUM REGION_NUM,
a11.MODEL_WO_NUM MODEL_WO_NUM,
a13.EQUIP_TD_CAT_CD EQUIP_TD_CAT_CD,
a11.PEND_WO_COUNT_CD WJXBFS1
into session.ZZOT00
from WDWT.WDW210_WO_TSK a11
left outer join WDWT.WDW212_WO_AST_DTLS a12
on a11.WO_TSK_ID = a12.WO_TSK_ID
left outer join WDWT.WDW201_EQ a13
on a12.EQUIP_SEQ_NUM = a13.EQUIP_SEQ_NUM
left outer join WDWT.WDW101_EQ_TYPE a14
on a13.EQUIP_TYPE_ID = a14.EQUIP_TYPE_ID
left outer join WDWT.WDW103_HIERARCHY a15
on a13.HIERARCHY_ID = a15.HIERARCHY_ID
where (a14.EQUIP_TYPE_CD in ('POLE ')
and a14.EQUIP_SUB_TYP_CD in ('DF', 'SP', 'WC', 'WL', 'OT', 'UN', 'WP', 'TM')
and a13.EQUIPMENT_NAM in ('POLE-DISTRIBUTION ', 'POLE-TRANSM/DISTRN ', 'POLE-TRANSMISSION ')
and a11.WORK_ORDER_TYPE_CD in ('PD')
and a11.MODEL_WO_NUM in ('28077720', '28077721', '28077722', '28068951')
and a11.WO_STS_CD not in ('COMPLETE', 'CLOSED', 'CANCEL'))

select pa1.REGION_NUM REGION_NUM,
pa1.MODEL_WO_NUM MODEL_WO_NUM,
pa1.EQUIP_TD_CAT_CD EQUIP_TD_CAT_CD,
count(*) WJXBFS1
into session.ZZMD01
from session.ZZOT00 pa1
group by pa1.REGION_NUM,
pa1.MODEL_WO_NUM,
pa1.EQUIP_TD_CAT_CD

As it is doing some extra step in db2 hence the total report generation time is becoming more.

Will appreciate any help on this. Thanks Sanjay.

 

[sanjaydhol]

Ok I forgot to mention one thing in my last mail. I was geting the SQL query from DB2 version when I set the VLDB property tables - table creation type = 'Implicit Table' but when I try to run the query with this changed VLDB property it is giving me the following error.

Report: Distribution Pole Corrective Actions CM

Job: 6403

Status: Execution failed

Error: QueryEngine encountered error: Call SQLExecDirect got SQL_ERROR
[42601:-104: on HSTMT] [IBM][CLI Driver][DB2] SQL0104N An unexpected token "SESSION" was found following "". Expected tokens may include: ":". SQLSTATE=42601


Error in Process method of Component: QueryEngineServer, Project 12, Job 6403, Error Code= -2147212544.



Otherwise If I dont set the property by default it is Explicite table. and then the query I am getting for the DB2 report is as follows..

declare global temporary table session.ZZOT00(
REGION_NUM CHAR(20),
MODEL_WO_NUM CHAR(8),
EQUIP_TD_CAT_CD CHAR(12),
WJXBFS1 INTEGER) on commit preserve rows

insert into session.ZZOT00
select a15.REGION_NUM REGION_NUM,
a11.MODEL_WO_NUM MODEL_WO_NUM,
a13.EQUIP_TD_CAT_CD EQUIP_TD_CAT_CD,
a11.PEND_WO_COUNT_CD WJXBFS1
from WDWT.WDW210_WO_TSK a11
left outer join WDWT.WDW212_WO_AST_DTLS a12
on a11.WO_TSK_ID = a12.WO_TSK_ID
left outer join WDWT.WDW201_EQ a13
on a12.EQUIP_SEQ_NUM = a13.EQUIP_SEQ_NUM
left outer join WDWT.WDW101_EQ_TYPE a14
on a13.EQUIP_TYPE_ID = a14.EQUIP_TYPE_ID
left outer join WDWT.WDW103_HIERARCHY a15
on a13.HIERARCHY_ID = a15.HIERARCHY_ID
where (a14.EQUIP_TYPE_CD in ('POLE ')
and a14.EQUIP_SUB_TYP_CD in ('DF', 'SP', 'WC', 'WL', 'OT', 'UN', 'WP', 'TM')
and a13.EQUIPMENT_NAM in ('POLE-DISTRIBUTION ', 'POLE-TRANSM/DISTRN ', 'POLE-TRANSMISSION ')
and a11.WORK_ORDER_TYPE_CD in ('PD')
and a11.MODEL_WO_NUM in ('28077720', '28077721', '28077722', '28068951')
and a11.WO_STS_CD not in ('COMPLETE', 'CLOSED', 'CANCEL'))

declare global temporary table session.ZZMD01(
REGION_NUM CHAR(20),
MODEL_WO_NUM CHAR(8),
EQUIP_TD_CAT_CD CHAR(12),
WJXBFS1 INTEGER) on commit preserve rows

insert into session.ZZMD01
select pa1.REGION_NUM REGION_NUM,
pa1.MODEL_WO_NUM MODEL_WO_NUM,
pa1.EQUIP_TD_CAT_CD EQUIP_TD_CAT_CD,
count(*) WJXBFS1
from session.ZZOT00 pa1
group by pa1.REGION_NUM,
pa1.MODEL_WO_NUM,
pa1.EQUIP_TD_CAT_CD

Thanks in advance for the help.
Sanjay.

 

[nlim]

Sanjaydhol, what is the problem you want to solve? Just because the sql is different does not mean there is a problem. In fact, the sql engine is optimizing for the db2 database.

1) your db2 report has more filter criteria than the test report in ss2000. We can see there are where clauses in the db2 sql. So your reports are not identical. Also you are requesting region in the db2 report, but not in the ss2000 report...

2) you are also using 2 more tables (hierarchy and equipment type) because of this filter requirement. Therefore the sql looks different as it depends on the level of these tables.

3) do the data look correct to you? When you read thru the SQL, is it pulling the correct data? You cannot require the sql to be the same for ss2000 and db2 because MSTR will generate the best sql for the specific db.

4) is the performance acceptable.

5) looks like your table prefix is session. This is a reserved word, I would change it.

finally, I recommend you attend an advanced education class if you want to interpret the sql...