I have a application say 200-300 tables. This application is tightly defined with RI.
Is there a way to get to know the table load order from DB2 catalog tables? say writing some queries hitting SYSKEYS/SYSFORIEGNKEYS etc. I mean which table has to be loaded first and so on... Tab1-->tab2-->tab3...tabn
if yes, Can you please let me know or give some sample on how to write such query?
I found this query.... but i am not able to understand it properly.
Can you please explain me.
I have few questions
1. "ROOT.REFTBNAME = 'T16'" here T16 is the table where I have to give my tablename?
2. what value "&table" do i have to specify?
3. what value "&level" do I have to specify?
4. How can I get the table load order based on schema name instead of one tablename?
Code:
WITH RISET (LEVEL, REFTBNAME, TBNAME, ENFORCED) AS
(
SELECT 1, ROOT.REFTBNAME, ROOT.TBNAME, ROOT.ENFORCED
FROM SYSIBM.SYSRELS ROOT
WHERE ROOT.REFTBNAME = 'T16'
AND ROOT.ENFORCED IN ('Y','N')
UNION ALL
SELECT 1, ROOT.TBNAME, ROOT.REFTBNAME, ROOT.ENFORCED
FROM SYSIBM.SYSRELS ROOT
WHERE ROOT.REFTBNAME = &table
AND ROOT.ENFORCED IN ('Y','N')
UNION ALL
SELECT PARENT.LEVEL + 1, CHILD.REFTBNAME, CHILD.TBNAME,
PARENT.ENFORCED
FROM RISET PARENT, SYSIBM.SYSRELS CHILD
WHERE PARENT.TBNAME = CHILD.REFTBNAME
AND PARENT.ENFORCED IN ('Y','N')
AND PARENT.LEVEL < &level
UNION ALL
SELECT PARENT.LEVEL + 1, CHILD.TBNAME, CHILD.REFTBNAME,
PARENT.ENFORCED
FROM RISET PARENT, SYSIBM.SYSRELS CHILD
WHERE PARENT.REFTBNAME = CHILD.TBNAME
AND PARENT.ENFORCED IN ('Y','N')
AND PARENT.LEVEL < &level
)
SELECT DISTINCT REFTBNAME AS PARENT, LEVEL, TBNAME AS CHILD
FROM RISET
When the RI is violated, DB2 sets the Check Pending flag for the table.
This is wrong and a common misinterpretation.
It is not a matter of "When the RI is violated". DB2 will set it always (when loading without check).
daveporcelan wrote:
Please note that it is assumed that the data itself is correct from an RI standpoint.
That is a pretty big assumption, isn't it.
At least if you don't know where the data comes from.
what you can do is :(which is basically what the query in the redbook does)
1) Select all tables that don't have parents (except themself)
2) select all children, grandchildren, greatgrandchildren,... (recursive) avoiding circular references by checking if the table already is part of the chain.
3) some tables will get reported multiple times. thus you should use the position furthest in the chain.
4) verify if you have all tables(fully circular references won't show up.)
something like this :
Code:
with alltables(lvl,creator,name,list) as (
select 0 ,creator, name,cast(strip(name) as varchar(2000)) from sysibm.systables T
where type = 'T' and
-- creator = 'xyz' and
dbname = 'DSNDB06'
and not exists (select * from sysibm.sysrels R
where R.tbname = t.name and R.CREATOR = t.CREATOR
and R.reftbname <> t.name and R.reftbCREATOR = t.CREATOR)
union all
select P.lvl + 1, r2.creator,r2.tbname,p.list || '>' || strip(tbname)
from alltables P, sysibm.sysrels R2
where r2.reftbname = p.name and r2.reftbcreator = p.creator
and position(strip(r2.tbname),p.list,CODEUNITS32 ) = 0
and lvl < 20
)
select creator, name, max(lvl) from alltables group by creator, name
order by 3,1,2
In my previous shop, we had a internet application which had lots of RI's. We used load replace(ENFORCE NO) all tables in that application everyday and run CHECK DATA WITH DELETE YES to move the violating rows from the source table to the exception tables and send(FTP) all those bad data to the business team. It worked, reading the Data Integrity manual, it seems we followed the second option in (3.10.1).
Try this also, i have put level 0 as the starting point
Code:
--Search for Parents
WITH PAR(CREATOR, PARENT, LEVEL) AS
( --THIS SELECT'S PARENT TABLE DETAILS
SELECT DISTINCT START.REFTBCREATOR, START.REFTBNAME, 0
FROM SYSIBM.SYSRELS START
WHERE START.REFTBNAME = 'SYSKEYCOLUSE' --<--Input
AND START.REFTBCREATOR = 'SYSIBM' --<--Input
AND START.ENFORCED IN ('Y','N')
UNION ALL
--JIC, IF TABLE IS JUST A KID
SELECT DISTINCT START.CREATOR, START.TBNAME, 0
FROM SYSIBM.SYSRELS START
WHERE START.TBNAME = 'SYSKEYCOLUSE' --<--Input
AND START.CREATOR = 'SYSIBM' --<--Input
AND START.ENFORCED IN ('Y','N')
UNION ALL
--THIS IS FOR GETTING TO TOP
SELECT TOP.REFTBCREATOR, TOP.REFTBNAME, P.LEVEL -1
FROM PAR P, SYSIBM.SYSRELS TOP
WHERE TOP.TBNAME = P.PARENT
AND TOP.CREATOR = P.CREATOR
AND TOP.ENFORCED IN ('Y','N')
AND P.LEVEL + 1 < 20
),
--Search for Childrens
CHI(CREATOR, CHILD, LEVEL) AS
(
SELECT START.REFTBCREATOR, START.REFTBNAME, P.LEVEL
FROM PAR P, SYSIBM.SYSRELS START
WHERE START.REFTBNAME = P.PARENT
AND START.REFTBCREATOR = P.CREATOR
AND START.ENFORCED IN ('Y','N')
AND P.LEVEL = 0
UNION ALL
SELECT BOTTOM.CREATOR, BOTTOM.TBNAME, C.LEVEL +1
FROM CHI C, SYSIBM.SYSRELS BOTTOM
WHERE BOTTOM.REFTBNAME = C.CHILD
AND BOTTOM.REFTBCREATOR = C.CREATOR
AND BOTTOM.ENFORCED IN ('Y','N')
AND C.LEVEL + 1 < 20
)
SELECT DISTINCT
CHAR(CONCAT(SPACE(INT(REPLACE(CHAR(LEVEL),'-',''))) ,TBNAME),40)
AS TBNAME, LEVEL
FROM (
SELECT SUBSTR(PARENT,1,25) AS TBNAME, LEVEL FROM PAR
UNION ALL
SELECT SUBSTR(CHILD,1,25) AS TBNAME, LEVEL FROM CHI
)AS RISET
ORDER BY LEVEL
;
Outputs
Code:
TBNAME LEVEL
---------+---------+---------+---------+---------+-----
SYSTABLES -1
SYSCHECKS 0
SYSCHECKDEP 1
SYSCHECKS2 1
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
Code:
TBNAME LEVEL
---------+---------+---------+---------+---------+---
SYSTABLES -2
SYSTABCONST -1
SYSKEYCOLUSE 0
DSNE610I NUMBER OF ROWS DISPLAYED IS 3
Running this query, you will get a warning als
Code:
DSNT404I SQLCODE = 347, WARNING: THE RECURSIVE COMMON TABLE EXPRESSION PAR
MAY CONTAIN AN INFINITE LOOP
DSNT418I SQLSTATE = 01605 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODML SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 13192 1130301145 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00003388' X'435F06D9'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Update to my Earlier Query, this is more bigger
This will list all the tables based on the Qualifier/Schema. The Top -(minus) level will be the first tables(dept, emp) to be loaded in the heirarchy.
Code:
--SEARCH FOR PARENTS
WITH PAR(CREATOR, PARENT, LEVEL, PARENTOF) AS
( --THIS SELECT'S PARENT TABLE DETAILS
SELECT DISTINCT START.REFTBCREATOR, START.REFTBNAME, 0
,CAST('-----------' AS CHAR(255))
FROM SYSIBM.SYSRELS START
WHERE --START.REFTBNAME = 'EMPPROJACT'
-- AND
START.REFTBCREATOR = 'ARNOLAB' --<<-- Qualifier/Schema Name
AND START.ENFORCED IN ('Y','N')
UNION ALL
--JIC, IF TABLE IS JUST A KID
SELECT DISTINCT START.CREATOR, START.TBNAME, 0
,CAST('-----------' AS CHAR(255))
FROM SYSIBM.SYSRELS START
WHERE --START.TBNAME ='EMPPROJACT'
-- AND
START.CREATOR = 'ARNOLAB' --<<-- Qualifier/Schema Name
AND START.ENFORCED IN ('Y','N')
UNION ALL
--THIS IS FOR GETTING TO TOP
SELECT TOP.REFTBCREATOR, TOP.REFTBNAME, P.LEVEL -1
,P.PARENT
FROM PAR P, SYSIBM.SYSRELS TOP
WHERE TOP.TBNAME = P.PARENT
AND TOP.CREATOR = P.CREATOR
AND TOP.ENFORCED IN ('Y','N')
AND (P.LEVEL > -20 )
AND
TOP.REFTBNAME <>TOP.TBNAME
AND (P.PARENT,P.PARENTOF) NOT IN (SELECT TBNAME,REFTBNAME
FROM SYSIBM.SYSRELS
WHERE TBNAME = TOP.TBNAME
)
),
--SEARCH FOR CHILDRENS
CHI(CREATOR, CHILD, LEVEL,CHILDOF) AS
(
SELECT START.REFTBCREATOR, START.REFTBNAME, P.LEVEL
,CAST('-----------' AS CHAR(255))
FROM PAR P, SYSIBM.SYSRELS START
WHERE START.REFTBNAME = P.PARENT
AND START.REFTBCREATOR = P.CREATOR
AND START.ENFORCED IN ('Y','N')
AND P.LEVEL = 0
UNION ALL
SELECT BOTTOM.CREATOR, BOTTOM.TBNAME, C.LEVEL +1
, BOTTOM.REFTBNAME
FROM CHI C, SYSIBM.SYSRELS BOTTOM
WHERE BOTTOM.REFTBNAME = C.CHILD
AND BOTTOM.REFTBCREATOR = C.CREATOR
AND BOTTOM.ENFORCED IN ('Y','N')
AND (C.LEVEL < 20 )
AND (
BOTTOM.REFTBNAME <> BOTTOM.TBNAME
AND (C.CHILD,C.CHILDOF) NOT IN (SELECT REFTBNAME,TBNAME
FROM SYSIBM.SYSRELS
WHERE TBNAME = BOTTOM.TBNAME
)
)
)
SELECT DISTINCT
CHAR(CONCAT(SPACE(INT(REPLACE(CHAR(LEVEL),'-',''))) ,TBNAME),40)
, LEVEL
--, PARENTOF --<--Uncomment, if you want to know PARENTOF/CHILDOF tablename
FROM (
SELECT
SUBSTR(PARENT,1,45) AS TBNAME,
LEVEL,
PARENTOF AS PARENTOF FROM PAR
UNION ALL
SELECT SUBSTR(CHILD,1,45) AS TBNAME,
LEVEL, CHILDOF AS PARENTOF FROM CHI
)AS RISET
ORDER BY LEVEL
FETCH FIRST 1000 ROWS ONLY
;
The query will list certain tables multiple times to know the reason you can uncomment the below line in the above query to know the reason, they will let you know the parent name.
Code:
--, PARENTOF --<--Uncomment, if you want to know PARENTOF/CHILDOF tablename
I would be very supprised if you have a 200-300 table model and all the tables have at least 1 parent within the same schema.
try this select and see if their are any rows with 0.
Code:
select name , type,
(select count(*) from sysibm.sysrels R
where R.creator = tb.creator and R.tbname = tb.name
and r.reftbcreator = tb.creator and r.reftbname <> tb.name)
from sysibm.systables tb
where creator = 'HMP'
order by 2 asc
maybe you forgot to comment out the criteria on dbname in my original query ?
All the table with type = P has count 0 - 90 total Rows
All the table with type = X has count 0 - 26 Total Rows
All the table with type = V has count 0 - 1 Total Rows
All History tables with type = T has count 0 - 158 Total rows
(Basically we dont have RI in History Tables)
Rest 332 rows with Type = T have Count starting from 1 to 16.
ok, so you are saying that
- there is no table without a parent.
- you don't know which table(s) to put first;
Let's see if there are tables without children (they are last in the load order) :
Code:
select name ,
(select count(*) from sysibm.sysrels R
where R.creator = tb.creator and R.tbname <> tb.name
and r.reftbcreator = tb.creator and r.reftbname = tb.name)
from sysibm.systables tb
where creator = 'xxx'
and type = 'T'
and not( criteria_for_historytables)
order by 2 asc
Otherwise you might want to :
- exclude not-enforced RI (probably all defined RI is enforced as in most shops)
- exclude the RI with DELETERULE = Set NULL
Code:
select name ,
(select count(*) from sysibm.sysrels R
where R.creator = tb.creator and R.tbname = tb.name
and r.reftbcreator = tb.creator and r.reftbname <> tb.name
and ENFORCED = 'Y'
and DELETERULE <> 'N')
from sysibm.systables tb
where creator = 'xxx'
and type = 'T'
and not( criteria_for_historytables)
order by 2 asc
I find it very discomforting that you have 333 tables and that there is a table with 584 children
ok, this is the reversed from my first query :
Select all tables without children ;
Select all parents,grandparents,... from these tables (recursive)
from all duplicate occurences of a table take the minimum lvl (= highest up in the tree)
Code:
with alltables(lvl,creator,name,list) as (
select 0 ,creator, name,cast(strip(name) as varchar(2000)) from sysibm.systables T
where type = 'T'
and creator = ?
and not( criteria_for_historytables)
and not exists (select * from sysibm.sysrels R
where R.reftbname = t.name and R.CREATOR = t.CREATOR
and R.tbname <> t.name and R.reftbCREATOR = t.CREATOR)
union all
select P.lvl - 1, r2.reftbcreator,r2.reftbname,strip(reftbname) || '>' || p.list
from alltables P, sysibm.sysrels R2
where r2.tbname = p.name and r2.creator = p.creator
and position(strip(r2.reftbname),p.list,CODEUNITS32 ) = 0
and lvl > -20
)
select
--*
creator, name, min(lvl)
from alltables
group by creator, name
order by 3,1,2
.jpg)
