Troubleshooting¶

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Refer to Troubleshooting.

Type of Error

Cause

Solution

The connector responded with the following

error message: Username cannot be null, Action: Contact Alation support for further assistance.

The cause of this error is if the configuration fields are empty.

Make sure that the configuration fields are populated with the necessary details.

Cross Lineage not getting populated after Lineage Extraction.

If Cross Lineage is not constructed after successful MDE.

Execute the Lineage API, see Cross-System Lineage API for details.

Also, verify the data from backend using the queries provided in Oracle Database Queries and SQL Server Database Queries.

Oracle Database Queries¶

Filter Folder List Query¶

SELECT * FROM REP_SUBJECT

Schema Query¶

SELECT DISTINCT
  c.subject_area CATALOG,
  a.task_name workflow_name,
  b.instance_name session_name,
c.mapping_name,
CONCAT(CONCAT(a.task_name,'/'),c.mapping_name) AS SCHEMA
FROM REP_ALL_TASKS a,REP_TASK_INST b,REP_LOAD_SESSIONS c
WHERE a.subject_id=c.subject_id AND a.task_type=71
AND a.task_id=b.workflow_id AND b.task_type=68 AND b.task_id=c.session_id
AND c.MAPPING_ID <> 0
ORDER BY 1,2,3,4

Table Query¶

SELECT CATALOG,SCHEMA,TABLE_NAME,TABLE_TYPE,REMARKS FROM (
SELECT DISTINCT
  a.SUBJECT_AREA AS CATALOG,
  CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS SCHEMA,
  a.FROM_OBJECT_NAME AS TABLE_NAME,
  a.FROM_OBJECT_INSTANCE_ID AS TABLE_TYPE,
  a.FROM_OBJECT_TYPE_NAME REMARKS
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
ON b.session_id=c.session_id
INNER JOIN REP_ALL_TASKS d
ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
ON e.workflow_id=c.workflow_id
AND e.task_type=68
AND e.task_id=c.session_id
AND b.MAPPING_ID <> 0
ORDER BY 1,2,3,4)
UNION ALL
SELECT CATALOG,SCHEMA,TABLE_NAME,TABLE_TYPE,REMARKS FROM (
SELECT DISTINCT
  a.SUBJECT_AREA AS CATALOG,
  CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS SCHEMA,
  a.TO_OBJECT_NAME AS TABLE_NAME,
  a.TO_OBJECT_INSTANCE_ID AS TABLE_TYPE,
  a.TO_OBJECT_TYPE_NAME REMARKS
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
ON b.session_id=c.session_id
INNER JOIN REP_ALL_TASKS d
ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
ON e.workflow_id=c.workflow_id
AND e.task_type=68
AND e.task_id=c.session_id
AND b.MAPPING_ID <> 0
AND a.TO_OBJECT_TYPE = 2
ORDER BY 1,2,3,4)
ORDER BY SCHEMA,TABLE_TYPE

Column Query¶

SELECT
  CATALOG,
  SCHEMA,
  TABLE_NAME,
  TYPE_NAME,
  DATA_TYPE,
  COLUMN_NAME,
  ORDINAL_POSITION,
  IS_NULLABLE,
  REMARKS,
  ' ' AS COLUMN_DEFAULT
FROM (
 SELECT DISTINCT
      a.SUBJECT_AREA AS CATALOG,
      CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS SCHEMA,
      a.FROM_OBJECT_NAME AS TABLE_NAME,
      a.FROM_OBJECT_FIELD_NAME AS COLUMN_NAME,
      a.from_object_type_name AS REMARKS,
      a.from_object_type,
      a.from_object_id as FROMID,
      a.to_object_id,
      a.FROM_OBJECT_INSTANCE_ID,
      a.TO_OBJECT_INSTANCE_ID,
       A.MAPPING_ID as MAPID,
        COALESCE(f.COLUMN_NUMBER,0) AS ORDINAL_POSITION,
    CASE
      WHEN f.SOURCE_TYPE IN ('varchar2','nvarchar','nvarchar2','number') THEN f.SOURCE_TYPE || '(' || f.DATA_PRECISION || ')'
      WHEN f.SOURCE_TYPE = 'number(p,s)' THEN 'number(' || f.DATA_PRECISION || ',' || f.DATA_SCALE || ')'
      WHEN f.SOURCE_TYPE IS NOT NULL THEN f.SOURCE_TYPE
      ELSE 'VARCHAR2' END AS TYPE_NAME,
    COALESCE(f.SOURCE_TYPE,'VARCHAR2') AS DATA_TYPE,
    0 AS IS_NULLABLE
    FROM REP_MAPPING_CONN_PORTS a
    INNER JOIN OPB_SESSION b
    ON a.mapping_id=b.mapping_id
    INNER JOIN REP_SESSION_INSTANCES c
    ON b.session_id=c.session_id
    INNER JOIN REP_ALL_TASKS d
    ON d.task_id=c.workflow_id
     LEFT JOIN REP_SRC_TBL_FLDS f ON (a.FROM_OBJECT_ID = f.TABLE_ID AND a.FROM_OBJECT_FIELD_NAME = f.COLUMN_NAME)
    INNER JOIN REP_TASK_INST e
    ON e.workflow_id=c.workflow_id
    WHERE d.subject_id=a.subject_id AND d.task_type=71
    AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
    AND b.MAPPING_ID <> 0
    AND A.from_object_type  IN(1)
  UNION ALL
  SELECT DISTINCT
      a.SUBJECT_AREA AS CATALOG,
      CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS SCHEMA,
      a.FROM_OBJECT_NAME AS TABLE_NAME,
      a.FROM_OBJECT_FIELD_NAME AS COLUMN_NAME,
      a.from_object_type_name AS REMARKS,
      a.from_object_type,
      a.from_object_id as FROMID,
      a.to_object_id,
      a.FROM_OBJECT_INSTANCE_ID,
      a.TO_OBJECT_INSTANCE_ID,
       A.MAPPING_ID as MAPID,
       COALESCE(c.COLUMN_NUMBER,0) AS ORDINAL_POSITION,
    CASE
      WHEN c.DATA_TYPE IN ('varchar2','nvarchar','nvarchar2','number') THEN c.DATA_TYPE || '(' || c.DATA_PRECISION || ')'
      WHEN c.DATA_TYPE = 'number(p,s)' THEN 'number(' || c.DATA_PRECISION || ',' || c.DATA_SCALE || ')'
      WHEN c.DATA_TYPE IS NOT NULL THEN c.DATA_TYPE
      ELSE 'VARCHAR2' END AS TYPE_NAME,
    COALESCE(c.DATA_TYPE,'VARCHAR2') AS DATA_TYPE,
    COALESCE(c.IS_NULLABLE,0) AS IS_NULLABLE
    FROM REP_MAPPING_CONN_PORTS a
    INNER JOIN OPB_SESSION b
    ON a.mapping_id=b.mapping_id
    INNER JOIN REP_SESSION_INSTANCES c
    ON b.session_id=c.session_id
     LEFT JOIN REP_TARG_TBL_COLS c ON (a.TO_OBJECT_NAME = c.TABLE_NAME AND a.TO_OBJECT_FIELD_NAME = c.COLUMN_NAME)
    INNER JOIN REP_ALL_TASKS d
    ON d.task_id=c.workflow_id
    INNER JOIN REP_TASK_INST e
    ON e.workflow_id=c.workflow_id
    WHERE d.subject_id=a.subject_id AND d.task_type=71
    AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
    AND b.MAPPING_ID <> 0
    AND A.from_object_type NOT IN(1)
    UNION ALL
    SELECT DISTINCT
      a.SUBJECT_AREA AS CATALOG,
      CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS SCHEMA,
      a.TO_OBJECT_NAME AS TABLE_NAME,
      a.TO_OBJECT_FIELD_NAME AS COLUMN_NAME,
      a.to_object_type_name as REMARKS,
      a.to_object_type,
       a.from_object_id as FROMID,
      a.to_object_id,
       a.FROM_OBJECT_INSTANCE_ID,
      a.TO_OBJECT_INSTANCE_ID,
       A.MAPPING_ID as MAPID,
       COALESCE(f.COLUMN_NUMBER,0) AS ORDINAL_POSITION,
    CASE
      WHEN f.SOURCE_TYPE IN ('varchar2','nvarchar','nvarchar2','number') THEN f.SOURCE_TYPE || '(' || f.DATA_PRECISION || ')'
      WHEN f.SOURCE_TYPE = 'number(p,s)' THEN 'number(' || f.DATA_PRECISION || ',' || f.DATA_SCALE || ')'
      WHEN f.SOURCE_TYPE IS NOT NULL THEN f.SOURCE_TYPE
      ELSE 'VARCHAR2' END AS TYPE_NAME,
    COALESCE(f.SOURCE_TYPE,'VARCHAR2') AS DATA_TYPE,
    0 AS IS_NULLABLE
    FROM REP_MAPPING_CONN_PORTS a
    INNER JOIN OPB_SESSION b
    ON a.mapping_id=b.mapping_id
    INNER JOIN REP_SESSION_INSTANCES c
    ON b.session_id=c.session_id
    LEFT JOIN REP_SRC_TBL_FLDS f ON (a.FROM_OBJECT_ID = f.TABLE_ID AND a.FROM_OBJECT_FIELD_NAME = f.COLUMN_NAME)
    INNER JOIN REP_ALL_TASKS d
    ON d.task_id=c.workflow_id
    INNER JOIN REP_TASK_INST e
    ON e.workflow_id=c.workflow_id
    WHERE d.subject_id=a.subject_id AND d.task_type=71
    AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
    AND b.MAPPING_ID <> 0
    and a.to_object_type not in (1)
    )

Foreign Key Query¶

SELECT DISTINCT
a.SUBJECT_AREA AS PK_CATALOG,
CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS PK_SCHEMA,
a.FROM_OBJECT_NAME AS PK_TABLE,
a.FROM_OBJECT_FIELD_NAME AS PK_COLUMN,
a.SUBJECT_AREA AS FK_CATALOG,
CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS FK_SCHEMA,
a.TO_OBJECT_NAME AS FK_TABLE,
a.TO_OBJECT_FIELD_NAME AS FK_COLUMN
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
ON b.session_id=c.session_id
INNER JOIN REP_ALL_TASKS d
ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
ON e.workflow_id=c.workflow_id
WHERE d.subject_id=a.subject_id AND d.task_type=71
AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
AND b.MAPPING_ID <> 0
ORDER BY 1,2,3,4

Transformation Expression and Condition Query¶

SELECT wfSchema.CATALOG, wfSchema.subject_id,wfSchema.SCHEMA,
exp.from_object_name as TABLE_NAME,exp.Remarks,exp.expression,exp.Column_Name
FROM  (SELECT DISTINCT c.subject_area as CATALOG,
 a.subject_id,
 c.mapping_id,
 Concat(Concat(a.task_name, '/'), c.mapping_name) AS SCHEMA
 FROM   rep_all_tasks a,
        rep_task_inst b,
        rep_load_sessions c
 WHERE  a.subject_id = c.subject_id
       AND a.task_type = 71
        AND a.task_id = b.workflow_id
        AND b.task_type = 68
        AND b.task_id = c.session_id
        AND c.mapping_id <> 0
) wfSchema
INNER JOIN (SELECT DISTINCT mcp.subject_id,
                            mcp.mapping_id,
                         mcp.from_object_name,
                          wf.expression AS EXPRESSION,
                            wi.widget_type_name as Remarks,
                            wf.field_name as Column_Name
            FROM   rep_mapping_conn_ports mcp,
                   rep_widget_inst wi,
                   rep_widget_field wf
            WHERE  (mcp.mapping_id = wi.mapping_id
            AND mcp.from_object_instance_id = wi.instance_id
            AND wi.widget_type_name = mcp.from_object_type_name)
                   AND  (wi.widget_type NOT IN ( 1, 2, 3 ))
                   AND (wi.widget_id = wf.widget_id )
                   AND  (wf.field_name != wf.expression)
  )exp
ON exp.subject_id = wfSchema.subject_id
AND exp.mapping_id = wfSchema.mapping_id

Verify Data (Transformation Expression)¶

This query can be used for conditions like Filter Condition, Joiner Condition, and more.

SELECT
wfSchema.CATALOG, wfSchema.subject_id, wfSchema.SCHEMA,
attr1.instance_name as TABLE_NAME, attr1.widget_type, attr1.widget_id, attr1.attr_name as REMARKS,
attr1.attr_value as Condition
FROM  (SELECT DISTINCT c.subject_area as CATALOG,
  a.subject_id,
  c.mapping_id,
  Concat(Concat(a.task_name, '/'), c.mapping_name) AS SCHEMA
FROM   rep_all_tasks a,
        rep_task_inst b,
        rep_load_sessions c
WHERE  a.subject_id = c.subject_id
        AND a.task_type = 71
        AND a.task_id = b.workflow_id
        AND b.task_type = 68
        AND b.task_id = c.session_id
        AND c.mapping_id <> 0
) wfSchema
INNER JOIN (
SELECT wi.mapping_id,
      rls.mapping_name,
      wi.widget_id,
      instance_id,
      wi.instance_name,
      rls.subject_id,
      rls.session_id,
      attr.attr_name,
      attr.attr_value,
      attr.widget_type
  FROM
  opb_widget_inst wi,
  rep_load_sessions rls,
  (
      SELECT wa.widget_id ,wa.widget_type,wa.attr_name,wa.attr_value FROM rep_widget_attr wa WHERE wa.widget_type=10 AND wa.attr_id=1 AND wa.attr_type=2  AND wa.attr_value NOT IN ('0')
      UNION ALL
      SELECT wa.widget_id,wa.widget_type,wa.attr_name,wa.attr_value FROM rep_widget_attr wa WHERE wa.widget_type=12 AND wa.attr_id=3 AND wa.attr_type=2  AND wa.attr_value NOT IN ('0')
      UNION ALL
      SELECT wa.widget_id,wa.widget_type,wa.attr_name,wa.attr_value FROM rep_widget_attr wa WHERE wa.widget_type=80 AND wa.attr_id=8 AND wa.attr_type=5 AND wa.attr_value NOT IN ('0')
  )attr
  WHERE   wi.widget_type IN (10,12,80)
          AND wi.mapping_id=rls.mapping_id
          AND wi.widget_id=attr.widget_id
          AND rls.subject_id!=9
) attr1
ON attr1.subject_id = wfSchema.subject_id
AND attr1.mapping_id = wfSchema.mapping_id

Connection Details Query¶

SELECT
a.Object_ID,a.Object_Name,a.Object_Subtype,
a.user_name,a.connect_string,
MAX(CASE WHEN c.ATTR_NAME = 'Database name' THEN b.attr_value END) AS DATABASE_Name,
MAX(CASE WHEN c.ATTR_NAME = 'Server name' THEN b.attr_value END) AS ServerName
FROM opb_cnx a
INNER JOIN
opb_cnx_ATTR b ON a.Object_ID = b.Object_ID
INNER JOIN
OPB_MMD_CNX_attr c ON b.ATTR_ID = c.ATTR_ID
AND b.Object_Subtype = c.Object_Subtype
GROUP BY
a.Object_ID,a.Object_Name,a.Object_Subtype,a.user_name,a.connect_string

Schema Name for Source and Target¶

SELECT
CATALOG,SOURCE_SCHEMA,TABLE_NAME,TARGET_SCHEMA,CONNECTION_NAME FROM(SELECT
rp.mapping_id,
rp.session_id,
CONCAT(si.workflow_name,CONCAT('/',rp.mapping_name)) as TARGET_SCHEMA,
rp.subject_area as CATALOG,
rp.subject_id,
wi.widget_type,
wi.widget_type_name,
wi.instance_id,
wi.instance_name as TABLE_NAME,
ati.attr_value as SOURCE_SCHEMA,
si.connection_name
from rep_load_sessions rp,
rep_widget_inst wi,
(select case when is_target=0 then 1 else 2 end as widget_type,
      si.subject_area,
      si.subject_id,
      si.connection_name,
      si.workflow_name,
      si.session_id
from  rep_session_instances si
) si,
(select
      sat.session_id,
      sat.attr_value,
      sat.attr_id ,
      case when sat.attr_id=3 then 2
           when sat.attr_id=28 then 1
      end as widget_type,
      sat.sess_widg_inst_id,
      inst.instance_id
  from opb_swidget_attr sat,
       opb_swidget_inst inst
  where sat.session_id=inst.session_id
   and sat.sess_widg_inst_id=inst.sess_widg_inst_id
   and sat.attr_id in(3,28)) ati
where (rp.mapping_id=wi.mapping_id and rp.subject_id=wi.subject_id)
and (rp.session_id=si.session_id and rp.subject_id=si.subject_id)
and (rp.session_id=ati.session_id and wi.instance_id=ati.instance_id)
and wi.widget_type=si.widget_type
and wi.widget_type in (1,2))

SQL Server Database Queries¶

Filter Folder List Query¶

SELECT * FROM REP_SUBJECT

Schema Query¶

SELECT DISTINCT
c.subject_area CATALOG,
a.task_name workflow_name,
b.instance_name session_name,
c.mapping_name,
CONCAT(CONCAT(a.task_name,'/'),c.mapping_name) AS 'SCHEMA'
FROM REP_ALL_TASKS a,REP_TASK_INST b,REP_LOAD_SESSIONS c
WHERE a.subject_id=c.subject_id AND a.task_type=71
AND a.task_id=b.workflow_id AND b.task_type=68 AND b.task_id=c.session_id
AND c.MAPPING_ID <> 0
ORDER BY 1,2,3,4

Table Query¶

SELECT DISTINCT
a.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS 'SCHEMA',
a.FROM_OBJECT_NAME AS TABLE_NAME,
a.FROM_OBJECT_INSTANCE_ID AS TABLE_TYPE,
a.FROM_OBJECT_TYPE_NAME REMARKS
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
ON b.session_id=c.session_id
INNER JOIN REP_ALL_TASKS d
ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
ON e.workflow_id=c.workflow_id
AND e.task_type=68
AND e.task_id=c.session_id
AND b.MAPPING_ID <> 0
UNION ALL
SELECT DISTINCT
a.SUBJECT_AREA AS CATALOG,
CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS 'SCHEMA',
a.TO_OBJECT_NAME AS TABLE_NAME,
a.TO_OBJECT_INSTANCE_ID AS TABLE_TYPE,
a.TO_OBJECT_TYPE_NAME REMARKS
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
ON b.session_id=c.session_id
INNER JOIN REP_ALL_TASKS d
ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
ON e.workflow_id=c.workflow_id
AND e.task_type=68
AND e.task_id=c.session_id
AND b.MAPPING_ID <> 0
AND a.TO_OBJECT_TYPE = 2
ORDER BY 1,2,3,4
OFFSET 0 ROWS

Column Query¶

SELECT DISTINCT
    a.SUBJECT_AREA AS CATALOG,
    CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS 'SCHEMA',
    a.FROM_OBJECT_NAME AS TABLE_NAME,
    a.FROM_OBJECT_FIELD_NAME AS COLUMN_NAME,
    a.from_object_type_name AS REMARKS,
    a.from_object_type,
    '' AS COLUMN_DEFAULT,
    COALESCE(f.COLUMN_NUMBER,0) AS ORDINAL_POSITION,
    CASE
        WHEN f.SOURCE_TYPE IN ('varchar2','nvarchar','nvarchar2','number') THEN CONCAT(f.SOURCE_TYPE,CONCAT('(',CONCAT(f.DATA_PRECISION,')')))
        WHEN f.SOURCE_TYPE = 'number(p,s)' THEN CONCAT('number(',CONCAT(f.DATA_PRECISION,CONCAT(',',CONCAT(f.DATA_SCALE,')'))))
        WHEN f.SOURCE_TYPE IS NOT NULL THEN f.SOURCE_TYPE
        ELSE 'VARCHAR2' END AS TYPE_NAME,
    COALESCE(f.SOURCE_TYPE,'VARCHAR2') AS DATA_TYPE,
    0 AS IS_NULLABLE
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
    ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
    ON b.session_id=c.session_id
LEFT JOIN REP_SRC_TBL_FLDS f ON (a.FROM_OBJECT_ID = f.TABLE_ID AND a.FROM_OBJECT_FIELD_NAME = f.COLUMN_NAME)
INNER JOIN REP_ALL_TASKS d
    ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
    ON e.workflow_id=c.workflow_id
WHERE d.subject_id=a.subject_id AND d.task_type=71
    AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
    AND b.MAPPING_ID <> 0
    AND a.from_object_type IN(1)
UNION ALL
SELECT DISTINCT
    a.SUBJECT_AREA AS CATALOG,
    CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS 'SCHEMA',
    a.FROM_OBJECT_NAME AS TABLE_NAME,
    a.FROM_OBJECT_FIELD_NAME AS COLUMN_NAME,
    a.from_object_type_name AS REMARKS,
    a.from_object_type,
    '' AS COLUMN_DEFAULT,
    COALESCE(f.COLUMN_NUMBER,0) AS ORDINAL_POSITION,
    CASE
        WHEN f.SOURCE_TYPE IN ('varchar2','nvarchar','nvarchar2','number') THEN CONCAT(f.SOURCE_TYPE,CONCAT('(',CONCAT(f.DATA_PRECISION,')')))
        WHEN f.SOURCE_TYPE = 'number(p,s)' THEN CONCAT('number(',CONCAT(f.DATA_PRECISION,CONCAT(',',CONCAT(f.DATA_SCALE,')'))))
        WHEN f.SOURCE_TYPE IS NOT NULL THEN f.SOURCE_TYPE
        ELSE 'VARCHAR2' END AS TYPE_NAME,
    COALESCE(f.SOURCE_TYPE,'VARCHAR2') AS DATA_TYPE,
    0 AS IS_NULLABLE
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
    ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
    ON b.session_id=c.session_id
LEFT JOIN REP_SRC_TBL_FLDS f ON (a.FROM_OBJECT_ID = f.TABLE_ID AND a.FROM_OBJECT_FIELD_NAME = f.COLUMN_NAME)
INNER JOIN REP_ALL_TASKS d
    ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
    ON e.workflow_id=c.workflow_id
WHERE d.subject_id=a.subject_id AND d.task_type=71
    AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
    AND b.MAPPING_ID <> 0
    AND a.from_object_type NOT IN(1)
UNION ALL
SELECT DISTINCT
    a.SUBJECT_AREA AS CATALOG,
    CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS 'SCHEMA',
    a.TO_OBJECT_NAME AS TABLE_NAME,
    a.TO_OBJECT_FIELD_NAME AS COLUMN_NAME,
    a.to_object_type_name AS REMARKS,
    a.to_object_type,
    '' AS COLUMN_DEFAULT,
    COALESCE(targ_col.COLUMN_NUMBER,0) AS ORDINAL_POSITION,
    CASE
        WHEN targ_col.DATA_TYPE IN ('varchar2','nvarchar','nvarchar2','number') THEN CONCAT(targ_col.DATA_TYPE,CONCAT('(',CONCAT(targ_col.DATA_PRECISION,')')))
        WHEN targ_col.DATA_TYPE = 'number(p,s)' THEN CONCAT('number(',CONCAT(targ_col.DATA_PRECISION,CONCAT(',',CONCAT(targ_col.DATA_SCALE,')'))))
        WHEN targ_col.DATA_TYPE IS NOT NULL THEN targ_col.DATA_TYPE
        ELSE 'VARCHAR2' END AS TYPE_NAME,
    COALESCE(targ_col.DATA_TYPE,'VARCHAR2') AS DATA_TYPE,
    COALESCE(targ_col.IS_NULLABLE,0) AS IS_NULLABLE
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
    ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
    ON b.session_id=c.session_id
LEFT JOIN REP_TARG_TBL_COLS targ_col ON (a.TO_OBJECT_NAME = targ_col.TABLE_NAME AND a.TO_OBJECT_FIELD_NAME = targ_col.COLUMN_NAME)
INNER JOIN REP_ALL_TASKS d
    ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
    ON e.workflow_id=c.workflow_id
WHERE d.subject_id=a.subject_id AND d.task_type=71
    AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
    AND b.MAPPING_ID <> 0
    AND a.to_object_type NOT IN (1)

Foreign Key Query¶

SELECT DISTINCT
a.SUBJECT_AREA AS PK_CATALOG,
CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS PK_SCHEMA,
a.FROM_OBJECT_NAME AS PK_TABLE,
a.FROM_OBJECT_FIELD_NAME AS PK_COLUMN,
a.SUBJECT_AREA AS FK_CATALOG,
CONCAT(CONCAT(d.task_name,'/'),a.mapping_name) AS FK_SCHEMA,
a.TO_OBJECT_NAME AS FK_TABLE,
a.TO_OBJECT_FIELD_NAME AS FK_COLUMN
FROM REP_MAPPING_CONN_PORTS a
INNER JOIN OPB_SESSION b
ON a.mapping_id=b.mapping_id
INNER JOIN REP_SESSION_INSTANCES c
ON b.session_id=c.session_id
INNER JOIN REP_ALL_TASKS d
ON d.task_id=c.workflow_id
INNER JOIN REP_TASK_INST e
ON e.workflow_id=c.workflow_id
WHERE d.subject_id=a.subject_id AND d.task_type=71
AND d.task_id=c.workflow_id AND e.task_type=68 AND e.task_id=b.session_id
AND b.MAPPING_ID <> 0

Transformation Column Expression Query¶

SELECT wfSchema.CATALOG, wfSchema.subject_id,wfSchema."SCHEMA",
exp.from_object_name as TABLE_NAME,exp.Remarks,
 exp.expression,exp.Column_Name
FROM  (SELECT DISTINCT c.subject_area as CATALOG,
 a.subject_id,
 c.mapping_id,
 Concat(Concat(a.task_name, '/'), c.mapping_name) AS "SCHEMA"
 FROM   rep_all_tasks a,
        rep_task_inst b,
        rep_load_sessions c
 WHERE  a.subject_id = c.subject_id
        AND a.task_type = 71
        AND a.task_id = b.workflow_id
        AND b.task_type = 68
        AND b.task_id = c.session_id
        AND c.mapping_id <> 0
 ) wfSchema
INNER JOIN (SELECT DISTINCT mcp.subject_id,
                            mcp.mapping_id,
                            mcp.from_object_name,
                            CAST(wf.expression AS VARCHAR(MAX)) AS EXPRESSION,
                            wi.widget_type_name as Remarks,
                            wf.field_name as Column_Name
            FROM   rep_mapping_conn_ports mcp,
                   rep_widget_inst wi,
                   rep_widget_field wf
            WHERE  (mcp.mapping_id = wi.mapping_id AND mcp.from_object_instance_id = wi.instance_id AND wi.widget_type_name = mcp.from_object_type_name)
                   AND  (wi.widget_type NOT IN ( 1, 2, 3 ))
                   AND (wi.widget_id = wf.widget_id )
                   AND  (wi.widget_type NOT IN ( 1, 2, 3 ))
                   AND (wi.widget_id = wf.widget_id )
         AND  (wf.field_name != CAST(wf.expression AS VARCHAR(MAX)))
  )exp
ON exp.subject_id = wfSchema.subject_id
AND exp.mapping_id = wfSchema.mapping_id

Transformation Column Expression Query¶

SELECT
wfSchema.CATALOG,
wfSchema.subject_id,
wfSchema."SCHEMA",
attr1.instance_name as TABLE_NAME,
attr1.widget_type,
attr1.widget_id,
attr1.attr_name as REMARKS,
attr1.attr_value as Condition
FROM
(
SELECT DISTINCT
  c.subject_area as CATALOG,
  a.subject_id,
  c.mapping_id,
  Concat(Concat(a.task_name, '/'), c.mapping_name) AS "SCHEMA"
FROM
  rep_all_tasks a,
  rep_task_inst b,
  rep_load_sessions c
WHERE
  a.subject_id = c.subject_id
  AND a.task_type = 71
  AND a.task_id = b.workflow_id
  AND b.task_type = 68
  AND b.task_id = c.session_id
  AND c.mapping_id <> 0
) wfSchema
INNER JOIN
(
SELECT
  wi.mapping_id,
  rls.mapping_name,
  wi.widget_id,
  instance_id,
  wi.instance_name,
  rls.subject_id,
  rls.session_id,
  attr.attr_name,
  attr.attr_value,
  attr.widget_type
FROM
  opb_widget_inst wi,
  rep_load_sessions rls,
  (
     SELECT
          wa.widget_id,
          wa.widget_type,
          wa.attr_name,
          CONVERT(varchar(MAX), wa.attr_value) AS attr_value
      FROM
          rep_widget_attr wa
      WHERE
          (wa.widget_type = 10 AND wa.attr_id = 1 AND wa.attr_type = 2 AND CAST(wa.attr_value AS varchar(MAX)) NOT IN ('0'))
          OR
          (wa.widget_type = 12 AND wa.attr_id = 3 AND wa.attr_type = 2 AND CAST(wa.attr_value AS varchar(MAX)) NOT IN ('0'))
          OR
          (wa.widget_type = 80 AND wa.attr_id = 8 AND wa.attr_type = 5 AND CAST(wa.attr_value AS varchar(MAX)) NOT IN ('0'))
  ) attr
WHERE
  wi.widget_type in (10,12,80)
  AND wi.mapping_id=rls.mapping_id
  AND wi.widget_id=attr.widget_id
  AND rls.subject_id!=9
) attr1
ON
attr1.subject_id = wfSchema.subject_id
AND attr1.mapping_id = wfSchema.mapping_id;

Connection Details Query¶

SELECT
rp.mapping_id,
rp.session_id,
CONCAT(si.workflow_name,CONCAT('/',rp.mapping_name)) as TARGET_SCHEMA,
rp.subject_area as CATALOG,
rp.subject_id,
wi.widget_type,
wi.widget_type_name,
wi.instance_id,
wi.instance_name as TABLE_NAME,
ati.attr_value as SOURCE_SCHEMA,
si.connection_name
from rep_load_sessions rp,
rep_widget_inst wi,
(select case when is_target=0 then 1 else 2 end as widget_type,
      si.subject_area,
      si.subject_id,
      si.connection_name,
      si.workflow_name,
      si.session_id
from  rep_session_instances si
) si,
(select
      sat.session_id,
      sat.attr_value,
      sat.attr_id ,
      case when sat.attr_id=3 then 2
           when sat.attr_id=28 then 1
      end as widget_type,
      sat.sess_widg_inst_id,
      inst.instance_id
  from opb_swidget_attr sat,
       opb_swidget_inst inst
  where sat.session_id=inst.session_id
   and sat.sess_widg_inst_id=inst.sess_widg_inst_id
   and sat.attr_id in(3,28)) ati
where (rp.mapping_id=wi.mapping_id and rp.subject_id=wi.subject_id)
and (rp.session_id=si.session_id and rp.subject_id=si.subject_id)
and (rp.session_id=ati.session_id and wi.instance_id=ati.instance_id)
and wi.widget_type=si.widget_type
and wi.widget_type in (1,2)

Troubleshooting¶

Oracle Database Queries¶

Filter Folder List Query¶

Schema Query¶

Table Query¶

Column Query¶

Foreign Key Query¶

Transformation Expression and Condition Query¶

Verify Data (Transformation Expression)¶

Connection Details Query¶

Schema Name for Source and Target¶

SQL Server Database Queries¶

Filter Folder List Query¶

Schema Query¶

Table Query¶

Column Query¶

Foreign Key Query¶

Transformation Column Expression Query¶

Transformation Column Expression Query¶

Connection Details Query¶

Alation User Documentation PDFs