Archives of the TeradataForum

Message Posted: Mon, 30 Dec 2002 @ 19:15:04 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Does the following sound as if things are working the way they should:

We have a query that reads data from DBC.Databases and DBC.Tables and compares it with another table we build that captures catalog history. We recently noticed that we received different results depending upon who ran the query and from what machine. We were able to isolate the root cause to be the choice of the default database specified on the ODBC driver. If no default database or our metadata database is specified, then the query completes successfully. If another database is specified then the query returns fewer rows than expected. The rows seem related to but not limited to that which is specified as the default database.

Any suggestions what to check?

Attached is the table we are inserting rows into as well as the query.... any help would be appreciated.

Thanks

Jim

CREATE TABLE WORKDB_METADATA.MDLD_DBC_050_VIEWCOLUMN
        ,NO FALLBACK
      ,NO BEFORE JOURNAL
      ,NO AFTER JOURNAL
      ,MAXIMUM DATABLOCKSIZE
      (
       Root                      INTEGER
      ,Platform                  VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(80)'
      ,DBMS                      VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(80)'
      ,"Database"                VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(80)'
      ,"View"                    VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(80)'
      ,Metadata_id               INTEGER
      ,Metadata_Nm               VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(80)'
      ,Metadata_Type_Ds          CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC
      ,Metadata_Subtype_Ds       CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC
      ,Metadata_Ds               VARCHAR(32000) CHARACTER SET LATIN CASESPECIFIC
      ,Parent_Metadata_id        INTEGER
      ,Related_Metadata_id       INTEGER
      ,Metadata_Load_Dt          DATE FORMAT 'YYYY/MM/DD' DEFAULT DATE
      ,Metadata_Source_Ds        VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC
      ,Scratchpad_Ds             VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC
      ,Metadata_Loaded_By_Userid VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT USER
      ,Metadata_Status_In        BYTEINT DEFAULT 20
      ,Metadata_Group_Ds         VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC
      ,Metadata_Hierarchy_Ds     VARCHAR(4096) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( Metadata_Nm );

EXPLAIN
INSERT INTO WORKDB_METADATA.MDLD_DBC_050_VIEWCOLUMN
SELECT      ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  Root
            WHEN  Type_Of_Action = 'Delete'     THEN  Root_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Root,Root_MDO))
      END ) AS Root
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  Platform
            WHEN  Type_Of_Action = 'Delete'     THEN  Platform_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Platform,Platform_MDO))
      END ) AS Platform
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  DBMS
            WHEN  Type_Of_Action = 'Delete'     THEN  DBMS_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(DBMS,DBMS_MDO))
      END ) AS DBMS

,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  "Database"
            WHEN  Type_Of_Action = 'Delete'     THEN  Database_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE("Database",Database_MDO))
      END ) AS "Database"
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  "View"
            WHEN  Type_Of_Action = 'Delete'     THEN  View_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE("View",View_MDO))
      END ) AS "View"
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  Metadata_Id
            WHEN  Type_Of_Action = 'Delete'     THEN  Metadata_Id_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Metadata_Id, Metadata_Id_MDO))
      END ) AS Metadata_Id
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  Metadata_Nm
            WHEN  Type_Of_Action = 'Delete'     THEN  Metadata_Nm_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Metadata_Nm, Metadata_Nm_MDO))
      END ) AS Metadata_Nm
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN Metadata_Type_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN  Metadata_Type_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Metadata_Type_Ds, Metadata_Type_Ds_MDO))
      END ) AS Metadata_Type_Ds
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN Metadata_SubType_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN  Metadata_SubType_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Metadata_SubType_Ds, Metadata_SubType_Ds_MDO))
      END ) AS Metadata_SubType_Ds
,     ( CASE      WHEN  Type_Of_Action = 'Insert'     THEN  Metadata_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN  Metadata_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN  (COALESCE(Metadata_Ds, Metadata_Ds_MDO))
      END ) AS Metadata_Ds
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Parent_Metadata_Id
            WHEN  Type_Of_Action = 'Delete'     THEN   Parent_Metadata_Id_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Parent_Metadata_Id, Parent_Metadata_Id_MDO))
      END ) AS Parent_Metadata_Id
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Related_Metadata_Id
            WHEN  Type_Of_Action = 'Delete'     THEN   Related_Metadata_Id_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Related_Metadata_Id, Related_Metadata_Id_MDO))
      END ) AS Related_Metadata_Id
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Metadata_Load_Dt
            WHEN  Type_Of_Action = 'Delete'     THEN   Metadata_Load_Dt_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Metadata_Load_Dt, Metadata_Load_Dt_MDO))
      END ) AS Metadata_Load_Dt
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Metadata_Source_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN   Metadata_Source_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Metadata_Source_Ds, Metadata_Source_Ds_MDO))
      END ) AS Metadata_Source_Ds
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Scratchpad_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN   Scratchpad_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Scratchpad_Ds, Scratchpad_Ds_MDO))
      END ) AS Scratchpad_Ds
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Metadata_Loaded_By_Userid
            WHEN  Type_Of_Action = 'Delete'     THEN   Metadata_Loaded_By_Userid_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Metadata_Loaded_By_Userid, Metadata_Loaded_By_Userid_MDO))
      END ) AS Metadata_Loaded_By_Userid
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Metadata_Status_In
            WHEN  Type_Of_Action = 'Delete'     THEN   Metadata_Status_In_MDO * (-1)
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Metadata_Status_In, Metadata_Status_In_MDO))
      END ) AS Metadata_Status_In
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Metadata_Group_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN   Metadata_Group_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Metadata_Group_Ds, Metadata_Group_Ds_MDO))
      END ) AS Metadata_Group_Ds
,     ( CASE      WHEN  Type_Of_Action = 'Insert' THEN Metadata_Hierarchy_Ds
            WHEN  Type_Of_Action = 'Delete'     THEN   Metadata_Hierarchy_Ds_MDO
            WHEN  Type_Of_Action = 'Update'     THEN   (COALESCE(Metadata_Hierarchy_Ds, Metadata_Hierarchy_Ds_MDO))
      END ) AS Metadata_Hierarchy_Ds
FROM
(SELECT     XXX.Root
,     XXX.Platform
,     XXX.DBMS
,     XXX."Database"
,     XXX."View"
,     XXX.Metadata_Id
,     XXX.Metadata_Nm
,     XXX.Metadata_Type_Ds
,     XXX.Metadata_Subtype_Ds
,     XXX.Metadata_Ds
,     XXX.Parent_Metadata_Id
,     XXX.Related_Metadata_Id
,     XXX.Metadata_Load_Dt
,     XXX.Metadata_Source_Ds
,     XXX.Scratchpad_Ds
,     XXX.Metadata_Loaded_By_Userid
,     XXX.Metadata_Status_In
,     XXX.Metadata_Group_Ds
,     XXX.Metadata_Hierarchy_Ds
,     MDO.Root                AS Root_MDO
,     MDO.Platform                  AS Platform_MDO
,     MDO.DBMS                AS DBMS_MDO
,     MDO."Database"                AS Database_MDO
,     MDO."View"              AS View_MDO
,     MDO.Metadata_Id         AS Metadata_Id_MDO
,     MDO.Metadata_Nm         AS Metadata_Nm_MDO
,     MDO.Metadata_Type_Ds          AS Metadata_Type_Ds_MDO
,     MDO.Metadata_Subtype_Ds       AS Metadata_Subtype_Ds_MDO
,     MDO.Metadata_Ds         AS Metadata_Ds_MDO
,     MDO.Parent_Metadata_Id        AS Parent_Metadata_Id_MDO
,     MDO.Related_Metadata_Id       AS Related_Metadata_Id_MDO
,     MDO.Metadata_Load_Dt          AS Metadata_Load_Dt_MDO
,     MDO.Metadata_Source_Ds        AS Metadata_Source_Ds_MDO
,     MDO.Scratchpad_Ds             AS Scratchpad_DS_MDO
,     MDO.Metadata_Loaded_By_Userid       AS Metadata_Loaded_By_Userid_MDO
,     MDO.Metadata_Status_In        AS Metadata_STatus_In_MDO
,     MDO.Metadata_Group_Ds         AS Metadata_Group_Ds_MDO
,     MDO.Metadata_Hierarchy_Ds     AS Metadata_Hierarchy_Ds_MDO

,     CASE  WHEN  TRIM(XXX.Metadata_Nm) = TRIM(MDO.Metadata_Nm)
            AND   (TRIM(XXX.Metadata_Ds) <> TRIM(MDO.Metadata_Ds)
            OR    TRIM(XXX.Parent_Metadata_Id) <> TRIM(MDO.Parent_Metadata_Id))
            AND   TRIM(XXX.Metadata_Ds) IS NOT NULL
            AND   TRIM(XXX.Metadata_Ds) <> ''
            THEN  'Update'
            ELSE (
            CASE  WHEN  TRIM(XXX.Metadata_Nm) IS NULL
                  AND   TRIM(MDO.Metadata_Nm) IS NOT NULL
                  AND   MDO.Metadata_Status_In > 0
                  THEN  'Delete'
                  ELSE (
                  CASE  WHEN  TRIM(XXX.Metadata_Nm) IS NOT NULL
                        AND   TRIM(MDO.Metadata_Nm) IS NULL
                        THEN  'Insert'
                        ELSE  'Ignore'
                  END )
            END )
      END (NAMED Type_Of_Action)

FROM (
SELECT      0 (Integer)       AS Root
,     'Galaxy Production'     AS Platform
,     'TERADATA'        AS DBMS
,     TRIM(DatabaseName)      AS "Database"
,     TRIM(TableName)         AS "View"
,     NULL (Integer)          AS Metadata_Id
,     TRIM(ColumnName)  AS Metadata_Nm
,     'View Column'           AS Metadata_Type_Ds
,     NULL              AS Metadata_Subtype_Ds
,     CommentString           AS Metadata_Ds
,     NULL (Integer)          AS Parent_Metadata_Id
,     NULL              AS Related_Metadata_Id
,     DATE              AS Metadata_Load_Dt
,     'SQL from XXX.COLUMNS into MDLD_XXX_050_VIEWCOLUMN'       AS Metadata_Source_Ds
,     NULL              AS Scratchpad_Ds
,     USER              AS Metadata_Loaded_By_Userid
,     120 (Integer)           AS Metadata_Status_In
,     NULL              AS Metadata_Group_Ds
,     NULL              AS Metadata_Hierarchy_Ds
FROM  DBC.COLUMNS
WHERE (DatabaseName, TableName) IN (
      SELECT      Database, TableName
      FROM  DBC.TABLES
      WHERE TableKind = 'V'
      AND   DatabaseName IN (
            SELECT      DatabaseName
            FROM  DBC.DATABASES
            WHERE dbkind = 'D'
            )
      )
) XXX

FULL OUTER JOIN METADATA.METADATA_VIEWCOLUMN MDO
ON (  XXX.Root = MDO.Root
AND   XXX.Platform = MDO.Platform
AND   XXX.DBMS = MDO.DBMS
AND   XXX."Database" = MDO."Database"
AND   XXX."View" = MDO."View"
OR    XXX.Parent_Metadata_Id = MDO.Parent_Metadata_Id )
AND   XXX.Metadata_Nm = MDO.Metadata_Nm
) TEMP
WHERE Type_Of_Action <> 'Ignore'
;

	<Prev	Next>		<<First	<Prev	Next>	Last>>