All Forums Database
cmedved 24 posts Joined 02/14
05 Aug 2014
PDCR DBQL NULL values for WDID,OpEnvName,SysConName

Hi all. I have a requirement to generate reports based on our operating windows via DBQL. As the operating windows are subject to change, I would like to use the OpEnvName field that is populated via the PDCR process.
The problem is, for queries that do not use AMPs (like HELP ______), the OpEnvName field will not be populated even though OpEnvID and SysConID have valid values. I've narrowed down the issue to these queries not logging workloads - WDID and FinalWDID are both null. Queries with 1 or more used AMPs have valid WDID values.
I thought of just joining the OpEnv and SysCon info to the DBQL table via the following sub-query:

select op.openvid, op.openvname, sc.sysconid, sc.sysconname from
(Select
 OpEnvId
,OpEnvName
,ConfigID as ConfigID
from TDWM.OpEnvs
where (OpEnvID, ConfigID, Cast(CreateDate*1000000+CreateTime as BIGINT))
in (select OpEnvID, ConfigID, Max(CAST(CreateDate*1000000+CreateTime as BIGINT))
from TDWM.OpEnvs group by 1, 2)
Group by 1,2,3 ) OP

INNER JOIN

 (Select
 SysConID
,SysConName
,ConfigID as ConfigID
from TDWM.SysCons
where (SysConID, ConfigID, Cast(CreateDate*1000000+CreateTime as BIGINT))
in (select SysConID, ConfigID, Max(CAST(CreateDate*1000000+CreateTime as BIGINT))
from TDWM.SysCons group by 1, 2)
Group by 1,2,3 )  SC

ON OP.ConfigId = SC.ConfigId
group by 1,2,3,4;

Unfortunately, this query will produce multiple rows with different OpEnvNames for certain combinations of OpEnvID and SysConId. These rows seem to have been filtered out by joining the tables to the tdwm.RuleDefs using WDID.
Is there a way to classify these queries into their appropriate operating environment for queries with null WDIDs?

cmedved 24 posts Joined 02/14
05 Aug 2014

Ah, I forgot a vital piece of info - the database version is 13.10.07.27

krishaneesh 140 posts Joined 04/13
06 Aug 2014
select op.openvid, op.openvname, sc.sysconid, sc.sysconname from
(Select
 OpEnvId
,OpEnvName
,ConfigID as ConfigID
from TDWM.OpEnvs
where (OpEnvID, ConfigID, Cast(CreateDate*1000000+CreateTime as BIGINT))
in (select OpEnvID, ConfigID, Max(CAST(CreateDate*1000000+CreateTime as BIGINT))
from TDWM.OpEnvs group by 1, 2)
and removedate=0
Group by 1,2,3 ) OP
 
INNER JOIN
 
 (Select
 SysConID
,SysConName
,ConfigID as ConfigID
from TDWM.SysCons
where (SysConID, ConfigID, Cast(CreateDate*1000000+CreateTime as BIGINT))
in (select SysConID, ConfigID, Max(CAST(CreateDate*1000000+CreateTime as BIGINT))
from TDWM.SysCons group by 1, 2)
and removedate=0

Group by 1,2,3 )  SC
 
ON OP.ConfigId = SC.ConfigId
group by 1,2,3,4;

 

You must sign in to leave a comment.