Problem when moving project from SQL Server to DB2- Urgent

[sanjaydhol]

Hi,
Thanks for all responses that I received so far from this forum.

We have migrated the project DW from SQL Server to DB2.

Now after the migration we found that some of the report SQL have changed.

For example we have one report which is generating the following SQL when we are running from the server project which is connecting to DB2.

select a12.DIVISION_NUM DIVISION_NUM,
a12.REGION_NUM REGION_NUM,
sum(a11.PLND_PM_CNT) WJXBFS1
from WDWT.WDW213_PM_PLAN a11
join WDWT.WDW103_HIERARCHY a12
on a11.HIERARCHY_ID = a12.HIERARCHY_ID
join WDWT.WDW101_EQ_TYPE a13
on a11.EQUIP_TYPE_ID = a13.EQUIP_TYPE_ID
where ((exists (select *
from WDWT.WDW201_EQ c21
join WDWT.WDW103_HIERARCHY c22
on c21.HIERARCHY_ID = c22.HIERARCHY_ID
where c21.EQUIP_TD_CAT_CD in ('Distribution')
and c22.REGION_NUM = a12.REGION_NUM))
and a11.RPT_YR_VAL in (2003)
and a13.EQUIP_TYPE_CD in ('CKT ')
and a11.PM_CAT_CD in ('PTRL', 'TPAT'))
group by a12.DIVISION_NUM,
a12.REGION_NUM

The same report is generating the following SQL when we are running from the local project which is connecting to SQL Server Database.

select a12.DIVISION_NUM DIVISION_NUM,
a12.REGION_NUM REGION_NUM,
sum(a11.PLND_PM_CNT) WJXBFS1
from WDW213_PM_PLAN a11
join WDW103_HIERARCHY a12
on (a11.HIERARCHY_ID = a12.HIERARCHY_ID)
join WDW101_EQ_TYPE a13
on (a11.EQUIP_TYPE_ID = a13.EQUIP_TYPE_ID)
where (((a12.REGION_NUM)
in (select c22.REGION_NUM
from WDW201_EQ c21
join WDW103_HIERARCHY c22
on (c21.HIERARCHY_ID = c22.HIERARCHY_ID)
where c21.EQUIP_TD_CAT_CD in ('Distribution')))
and a11.RPT_YR_VAL in (2003)
and a13.EQUIP_TYPE_CD in ('CKT ')
and a11.PM_CAT_CD in ('PTRL', 'TPAT'))
group by a12.DIVISION_NUM,
a12.REGION_NUM

Though these two projects are identical, the schemas and metrics are exactly same. The metadata for the local project is MS Access and for the server project it is ORACLE.
I will really appreciate any help why it is happening.
Thanks,

Sanjay.

 

[nlim]

1) do the numbers look the same on both reports?

2)
((exists (select *
from WDWT.WDW201_EQ c21
join WDWT.WDW103_HIERARCHY c22
on c21.HIERARCHY_ID = c22.HIERARCHY_ID
where c21.EQUIP_TD_CAT_CD in ('Distribution')
and c22.REGION_NUM = a12.REGION_NUM))

and
((a12.REGION_NUM)
in (select c22.REGION_NUM
from WDW201_EQ c21
join WDW103_HIERARCHY c22
on (c21.HIERARCHY_ID = c22.HIERARCHY_ID)
where c21.EQUIP_TD_CAT_CD in ('Distribution')))

do exactly the same thing in sql. You should have nothing to worry about. The only part of the sql that changed was to accomodate the better performing "exist" clause that is supported by DB2 and not SS.

 

[nlim]

oh i forgot to mention that if you want to switch the query back to the SS syntax, you can edit the vldb setting.

In the report editor, under data, choose vldb options. click on query optimizations and select the "sub query type". there you can select the one that will switch the SQL back to the SS version you liked.