Determination of Table Load Sequence

Determination of Table Load Sequence

Post by Jens Junk » Thu, 01 May 2003 16:24:13



Are there any means (tools, sql, scripts, etc) to determine the load
sequence (enforced by referential integrity) of a complex database
(appr. 700 tables).

The goal is to load the data as parallel as possible due to batch
window constraints.

To analyse SYSIBM.SYSRELS 'manually' is time consuming and the result
might be faulty.

LOAD ... ENFORCE NO.. (and CHECK DATA) is not an option.

Many thanks
Jens

 
 
 

Determination of Table Load Sequence

Post by Sean » Fri, 02 May 2003 00:03:29


Jens,

I use this query. If your RI hierarchy has more than 7 levels, you
need to change the literal numeric 7 in the query to the approriate
value. Modify the schema name accordingly, also. Hope this helps.

-----------------------------------------------------------------------------
| show a mapping of all RI in a system, starting with the leaf
children     |
| and working upward. a subselect of syscat.tables provides a list of
all   |
| the leaf children and a recursive sql statement walks up the chains
for   |
| each child, then orders the results in a top-down hierarchy,
depending    |
| on the number of levels in your RI tree                            
     |
-----------------------------------------------------------------------------
with leaf_children (leaf_child) as
    (select tabname from syscat.tables where tabschema = 'DB2ADMIN'
     and parents > 0 and children = 0 ),

    refs (parentschema, parent, childschema, child, levels) as
  ((
     select reftabschema, reftabname, tabschema, tabname, 7
           from syscat.references, leaf_children
        where tabschema = 'DB2ADMIN'
          and tabname = leaf_child)

     union all

    (select reftabschema, reftabname, tabschema, tabname, levels - 1
          from refs r, syscat.references s
         where s.tabschema = r.parentschema
           and s.tabname = r.parent
           and r.levels > 0))

select  distinct substr(r.parent,1,20) as parent, '==> ' ||
substr(r.child,1,20) as child, levels
  from refs r, (select  parent, min(levels) as level
  from refs group by parent) as p
where r.parent = p.parent and r.levels = p.level
union
select substr(leaf_child,1,20), ' ', (select max(levels) from refs) +
1 from leaf_children
order by 3
with ur;

 
 
 

Determination of Table Load Sequence

Post by Anton Verstee » Fri, 02 May 2003 18:12:33


An alternative is to use the command: set integrity off
After the load you can turn it on again for all the tables where it was
turned off.


>Are there any means (tools, sql, scripts, etc) to determine the load
>sequence (enforced by referential integrity) of a complex database
>(appr. 700 tables).

>The goal is to load the data as parallel as possible due to batch
>window constraints.

>To analyse SYSIBM.SYSRELS 'manually' is time consuming and the result
>might be faulty.

>LOAD ... ENFORCE NO.. (and CHECK DATA) is not an option.

>Many thanks
>Jens

--
Anton Versteeg
IBM Netherlands
 
 
 

Determination of Table Load Sequence

Post by Jens Junk » Sat, 03 May 2003 21:58:26



> Jens,

> I use this query. If your RI hierarchy has more than 7 levels, you
> need to change the literal numeric 7 in the query to the approriate
> value. Modify the schema name accordingly, also. Hope this helps.

> -----------------------------------------------------------------------------
> | show a mapping of all RI in a system, starting with the leaf
>  children     |
> | and working upward. a subselect of syscat.tables provides a list of
>  all   |
> | the leaf children and a recursive sql statement walks up the chains
>  for   |
> | each child, then orders the results in a top-down hierarchy,
>  depending    |
> | on the number of levels in your RI tree                            
>      |
> -----------------------------------------------------------------------------
> with leaf_children (leaf_child) as
>     (select tabname from syscat.tables where tabschema = 'DB2ADMIN'
>      and parents > 0 and children = 0 ),

>     refs (parentschema, parent, childschema, child, levels) as
>   ((
>      select reftabschema, reftabname, tabschema, tabname, 7
>            from syscat.references, leaf_children
>         where tabschema = 'DB2ADMIN'
>           and tabname = leaf_child)

>      union all

>     (select reftabschema, reftabname, tabschema, tabname, levels - 1
>           from refs r, syscat.references s
>          where s.tabschema = r.parentschema
>            and s.tabname = r.parent
>            and r.levels > 0))

> select  distinct substr(r.parent,1,20) as parent, '==> ' ||
> substr(r.child,1,20) as child, levels
>   from refs r, (select  parent, min(levels) as level
>   from refs group by parent) as p
> where r.parent = p.parent and r.levels = p.level
> union
> select substr(leaf_child,1,20), ' ', (select max(levels) from refs) +
> 1 from leaf_children
> order by 3
> with ur;

Hi Sean,

thank you very much for the query. Unfortunately, my server is a DB2
for OS/390 V7.1. Advanced SQL features like common table expression
and recursive SQL are not supported (currently).

However, it will help on other platforms.

Jens

 
 
 

Determination of Table Load Sequence

Post by A » Sun, 04 May 2003 03:46:59


Quote:> thank you very much for the query. Unfortunately, my server is a DB2
> for OS/390 V7.1. Advanced SQL features like common table expression
> and recursive SQL are not supported (currently).

> However, it will help on other platforms.

then you could implement all the new features manually:
If there are no table expressions, materialize them in permanent
tables (that gives you an advantage: ability to create
indexes/statistics).
If there is no recursion, just run the script several times.
I've never seen a dabase schema deeper than 20 levels and I think
usually 10 is too much.

Alexander dot Kuznetsov at marshmc dot com

 
 
 

1. Table relationship determination

Hello all,

Can someone tell me how to perform a query on the INFORMATION_SCEMA tables
that will tell me exactly what fields in one table map to other fields in a
related table? The problem arises when there is a composite primary key on
the primary key table. For example, I have a primary key table called 'Test'
with fields 'Code' and 'Type' as the primary key. I have another table
called 'Test2' that has fields 'Code' and 'Type' that map to the fields in
the other table. I also use the following query to determine the mappings:

SELECT a.Constraint_Name AS ConstraintName,
 b.Table_Name AS FromTable,
 b.Column_Name AS FromColumn,
 c.Table_Name AS ToTable,
 c.Column_Name AS ToColumn

FROM Information_Schema.Referential_Constraints a,
 Information_Schema.Constraint_Column_Usage b,
 Information_Schema.Key_Column_Usage c

WHERE a.Constraint_Name = b.Constraint_Name AND
 a.Unique_Constraint_Name = c.Constraint_Name AND
 (b.Table_Name = 'Test' OR c.Table_Name = 'Test')

The resulting set is:

Constraint_Name        FromTable    FromColumn    ToTable        ToColumn

'FK_Test2_Test'            'Test2'            'Code'            'Test'
'Code'
'FK_Test2_Test'            'Test2'            'Type'             'Test'
'Code'
'FK_Test2_Test'            'Test2'            'Code'            'Test'
'Type'
'FK_Test2_Test'            'Test2'            'Type'             'Test'
'Type'

This does not tell me which column maps to which. The following query will
help illustrate why this happens,
but I don't know how to get exactly what I want.

SELECT * FROM Information_Schema.Referential_Constraints a WHERE
CONSTRAINT_NAME = 'FK_Test2_Test'
SELECT * FROM Information_Schema.Constraint_Column_Usage b WHERE
CONSTRAINT_NAME = 'FK_Test2_Test'
SELECT * FROM Information_Schema.Key_Column_Usage c WHERE CONSTRAINT_NAME =
'PK_Test_1'

Thanks in advance,
Shannon

2. Is it possible

3. Loading multiple sequence transaction dumps

4. [Fwd: Repeating Fields - Delete a Row]

5. Timestamp out of sequence while loading dump to standby server

6. MSDE w/ VS Database Designer

7. Code to automatically refresh sequences after loading data?

8. empty data list / data grid on NT4, but fine on Windows98

9. load sequence - multi database question - 8.1.6

10. Using Oracle Sequences in Load control cards

11. sequences and loading from sqlldr

12. BCP to load flat file and keep sequence

13. dump/load - dump out of sequence.