Ok, I need someone who can read DB2 SQL to help me with this one.

I have never been able to get the DBMS SQL FLOW feature of data migrator to work very effectively for me. One of the biggest things I need to be able to do is subselects as I can do in regular SQL. Here is an example of one that I am trying to create a flow for:

Select distinct
AP.Acctg_Per_Id as Acctg_Per_Id,
BT.Bdgt_Typ_Id as Bdgt_Typ_Id
From DWDBAS.BDGT_TYP_T BT,
(Select distinct
APM.Acctg_Per_Id,
APY.Acctg_Per_Num as CalYr, APY.Acctg_Per_Id as YrId,
APQ.Acctg_Per_Num as CalQtr, APQ.Acctg_Per_Id as QtrId,
APM.Acctg_Per_Num as CalMth, APM.Acctg_Per_Id as MthId
From DWDBAS.Acctg_Per_T APY
Join DWDBAS.Per_Typ_T PTY On APY.Per_Typ_Id = PTY.Per_Typ_Id
Join DWDBAS.Acctg_Per_T APQ On APQ.Prnt_Acctg_Per_Id = APY.Acctg_Per_Id
Join DWDBAS.Per_Typ_T PTQ On APQ.Per_Typ_Id = PTQ.Per_Typ_Id
Join DWDBAS.Acctg_Per_T APM On APM.Prnt_Acctg_Per_Id = APQ.Acctg_Per_Id
Join DWDBAS.Per_Typ_T PTM On APM.Per_Typ_Id = PTM.Per_Typ_Id


Where PTY.Per_Typ_Desc = 'CALENDAR YEAR' and
PTQ.Per_Typ_Desc = 'CALENDAR QUARTER' and
PTM.Per_Typ_Desc = 'CALENDAR MONTH'
) AP,

(Select distinct PR.Prty_Id
From DWDBAS.PRTY_ROL_T PR
Join DWDBAS.ROL_TYP_T RT On PR.Rol_Typ_Id = RT.Rol_Typ_Id
Join DWDBAS.PRTY_T P On PR.Prty_Id = P.Prty_Id

Where RT.Rol_Typ_Desc = 'DIVISION'
Order by 1
) Org
Where AP.CalYr In (2008,2009,2010) and
BT.Bdgt_Typ_Desc = 'MONTHLY OPERATIONAL BUDGET'


The first error that pops up when I do this is that all of my filenames that have a qualifier of DWDBAS are invalid. The help text tells me that a DBMS SQL FLOW just passes the syntax to the SQL engine, so why is this incorrect?

Other problems I get are errors encountered at SELECT which I assume are the subselects that it doesn't know how to handle. Any advice on this?

This message has been edited. Last edited by: Kerry,

Hi T,

Here are some suggestions from our iWay people: I would suggest to check the DM Log to see if the request is being submitted via passthru, and if not, that would explain why the creator names are not recognized and you should probably use the synonym names. Otherwise, more research would be needed to understand why passthru is not being used and this would likely require a case opened with Customer Support Services.

As far as the subselect is concerned, there are other cases where custom SQL code can be used for subselects so this should be supported.

Cheers,

Kerry

1) try to put a '/' instead of a '.' between then library name and the file name (DWDBAS/BDGT_TYP_T)

2) for the field CALYR, separate the values with a blank character after the comma (2008, 2009, 2010)

Hope this helps.
Catherine