PDCR DBQL NULL values for WDID,OpEnvName,SysConName

Database

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?

2 REPLIES

Re: PDCR DBQL NULL values for WDID,OpEnvName,SysConName

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

Re: PDCR DBQL NULL values for WDID,OpEnvName,SysConName

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;