Automic Workload Automation

  • Private Community

  • 1.  How can I proof if data are consistent in Master-/Detail table relation (in an Oracle database)?

    Posted Nov 15, 2016 09:14 AM

    Description

    The data model of the automation engine database includes some foreign key constraints.
    A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.
    In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.
     
    Sometime it is necessary to disable a constraint on a table (e.g. for testing purposes).
    But it could happen that people forget to enable the constraint again.
    Therefore it is good to know if the database is in a consistent state that means that no records are existing in a child table without any parent record.


  • 2.  How can I proof if data are consistent in Master-/Detail table relation (in an Oracle database)?

    Posted Nov 15, 2016 09:20 AM

    Solution

    To check if the data are consistent the attached script is developed.
    There are 3 variables where following default values are defined (this values can be changed with a text editor like notepad++):
     
    1) OWNER_NAME='UC4'
    2) Parent_Table_Name='%'
    3) Child_Table_Name='%'
     
    You should change at least the variable 'OWNER_NAME' to the name of your automation engine schema.

    The variables 'Parent_Table_Name' and 'Child_Table_Name' can be empty because the default value is ‘%’.

    Execute script DataConsistencyCheck.sql in SQL*Plus from the command line:

    d1dk290ktfgr.png

    The result is a script (data_cons_check.sql) created in the same directory where the script DataConsistencyCheck.sql is executed.

    You can execute the generated command script in data_cons_check.sql to check if there are any data in a child table with not existing parent record.
    e.g.: select ABLOB_AH_IDNR from ABLOB MINUS select AH_IDNR from AH;

    If the result of the command is 'no rows selected' everthing is fine.
     
    Please check out the script below (DataConsistencyCheck.sql).


  • 3.  How can I proof if data are consistent in Master-/Detail table relation (in an Oracle database)?

    Posted Nov 15, 2016 09:21 AM
    DataConsistencyCheck.sql:

    def OWNER_NAME='UC4'
    def Parent_Table_Name='%'
    def Child_Table_Name='%'

    col owner           format a10 noprint
    col CHILDCOL        format a20 noprint
    col PARENTCOL       format a20 noprint
    col CHILD_TABLE     format a10 noprint
    col PARENT_TABLE    format a10 noprint
    col constraint_name format a20 noprint
    col position        format 99 noprint
    col check_command   format a80
    col del_command     format a80

    set pages 1000 lines 500 term on echo on timi on trimspool on
    spool data_cons_check.sql
    select b.owner,
           b.table_name CHILD_TABLE,
           b.column_name CHILDCOL,
           c.table_name PARENT_TABLE,
           c.column_name PARENTCOL,
           a.constraint_name,
           b.position,
           a.status,        
           'select ' || b.column_name || ' from ' || b.table_name || ' MINUS select ' || c.column_name || ' from ' || c.table_name || ';' check_command,
           'delete from ' || b.table_name || ' where ' || b.column_name || ' in (' || 'select ' || b.column_name || ' from ' || b.table_name || ' MINUS select ' || c.column_name || ' from ' || c.table_name || ');' delete_command
        from all_cons_columns b,
             all_cons_columns c,
             all_constraints a
       where b.constraint_name = a.constraint_name
         and a.owner           = b.owner
         and b.position        = c.position
         and c.constraint_name = a.r_constraint_name
         and c.owner           = a.r_owner
         and a.constraint_type = 'R'
         and c.owner      like case when upper('&OWNER_NAME') is null then '%'
                                    else upper('&OWNER_NAME') end
         and c.table_name like case when upper('&PARENT_TABLE_NAME') is null then '%'
                                    else upper('&PARENT_TABLE_NAME') end
         and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
                                    else upper('&CHILD_TABLE_NAME') end
    order by 1,2,3,4,6
    /
    spoo off


  • 4.  How can I proof if data are consistent in Master-/Detail table relation (in an Oracle database)?

    Posted Nov 15, 2016 09:43 AM
    If you found any data and you want to correct them you have to execute the generated query in column 'delete_command'.
    e.g.: delete from RH where RH_AH_IDNR in (select RH_AH_IDNR from RH MINUS select AH_IDNR from AH);

    upat31zxcgtv.png