Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Finding Table load order
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Oct 29, 2012 3:19 pm    Post subject: Finding Table load order
Reply with quote

Hi,

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?
Back to top
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Mon Oct 29, 2012 3:33 pm    Post subject:
Reply with quote

http://www.redbooks.ibm.com/redbooks/pdfs/sg247111.pdf
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Mon Oct 29, 2012 3:36 pm    Post subject: Reply to: Finding Table load order
Reply with quote

when posting links to the redbooks I prefer to post the link to the abstract

http://www.redbooks.ibm.com/abstracts/sg247111.html

no waste of time in loading the PDF icon_wink.gif
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Mon Oct 29, 2012 3:59 pm    Post subject: Re: Reply to: Finding Table load order
Reply with quote

enrico-sorichetti wrote:
when posting links to the redbooks I prefer to post the link to the abstract

http://www.redbooks.ibm.com/abstracts/sg247111.html

no waste of time in loading the PDF icon_wink.gif


Probably true if you are in a hurry. Sometimes i want to save a copy
and i prefer to save a PDF instead of webpages.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Oct 29, 2012 4:59 pm    Post subject:
Reply with quote

Hi,

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
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Oct 30, 2012 10:32 am    Post subject:
Reply with quote

Hi,

Can anyone please help me with this?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 30, 2012 9:25 pm    Post subject:
Reply with quote

Hello,

Quote:
I found this query.... but i am not able to understand it properly.
Depending on where you found this, you might consider asking the author or someone at that organization.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Oct 31, 2012 11:20 am    Post subject:
Reply with quote

This query, I found from the IBM Redbook.

http://www.redbooks.ibm.com/redbooks/pdfs/sg247111.pdf

But the explanation is not proper.
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Wed Oct 31, 2012 5:57 pm    Post subject:
Reply with quote

Quote:
Can anyone please help me with this?


What I have done many times is not care about the 'load order'.

I load the tables (we have 600+ tables) in alaphabetical order.
When the RI is violated, DB2 sets the Check Pending flag for the table.

When all tables have been loaded, then reset all the check pending flags.

This has worked thousands of times for me.

Please note that it is assumed that the data itself is correct from an RI standpoint.

Hope this helps.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 31, 2012 7:54 pm    Post subject:
Reply with quote

Hello,

Quote:
This query, I found from the IBM Redbook.
. . .
But the explanation is not proper.
Then you should open in issue with IBM requesting clarification. As it is published by IBM, they will be able to "support" it.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Oct 31, 2012 8:00 pm    Post subject:
Reply with quote

daveporcelan wrote:
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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 31, 2012 8:13 pm    Post subject:
Reply with quote

GuyC,
super post,
that is super addition to my private tools,
thank you.

icon_pray.gif icon_pray.gif icon_pray.gif icon_pray.gificon_pray.gif icon_pray.gif icon_pray.gif icon_pray.gif icon_pray.gif
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Wed Oct 31, 2012 8:32 pm    Post subject:
Reply with quote

Quote:
That is a pretty big assumption, isn't it.
At least if you don't know where the data comes from.


I my case, the data is coming from another Region that has RI, so we know it is correct.

Regarding:
Quote:
When the RI is violated, DB2 sets the Check Pending flag for the table.


You are correct, this statement is correct. The LOAD of a Table that has parents or children would cause the flag to be set.

We use this technique in load test data many times a day.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Oct 31, 2012 9:51 pm    Post subject:
Reply with quote

Hi,

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 icon_biggrin.gif
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                 

Thanks,
Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 31, 2012 10:05 pm    Post subject:
Reply with quote

duly copied, thanks!
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 02, 2012 10:27 am    Post subject:
Reply with quote

Hi GuyC,

When I tried running your query, It returned zero results to me , I think because these is circular RI defined on the tables.

Hi Sushanth,

In my case , I want a query at schema level as I don't know which tables to put 1st in the query.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 09, 2012 4:28 pm    Post subject:
Reply with quote

Hi,

Update to my Earlier Query, this is more bigger icon_eek.gif
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         
;                                   

Output
Code:
                                                LEVEL
---------+---------+---------+---------+---------+---
    DEPT                                           -4
    EMP                                            -4
   DEPT                                            -3
   EMP                                             -3
  ACT                                              -2
  DEPT                                             -2
  EMP                                              -2
  PROJ                                             -2
 ACT                                               -1
 DEPT                                              -1
 EMP                                               -1
 PROJ                                              -1
 PROJACT                                           -1
ACT                                                 0
DEPT                                                0
EMP                                                 0
EMPPROJACT                                          0
PROJ                                                0     
PROJACT                                             0     
 DEPT                                               1     
 EMP                                                1     
 EMPPROJACT                                         1     
 PROJ                                               1     
 PROJACT                                            1     
  EMPPROJACT                                        2     
  PROJ                                              2     
  PROJACT                                           2     
   EMPPROJACT                                       3     
   PROJACT                                          3     
    EMPPROJACT                                      4     
DSNE610I NUMBER OF ROWS DISPLAYED IS 30                   
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100


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


Thanks,
Sushanth
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Nov 14, 2012 10:24 am    Post subject:
Reply with quote

Thanks Shushanth...

I tried running your query... It was running for hours. I think we have circular RI defined that is why the query is going in a loop.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Nov 14, 2012 2:29 pm    Post subject:
Reply with quote

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 ?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Nov 14, 2012 3:29 pm    Post subject:
Reply with quote

Hi Guyc,

I got the output. Here is the result.

Total 607 Records are fetched.

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.

The below table shows rest 332 tables.

Code:
Count   Number of Tables
1   4
2   18
3   26
4   51
5   107
6   76
7   20
8   14
9   3
10   5
11   4
12   1
14   1
15   1
16   1
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Copybook structure in Load module Vai123 COBOL Programming 7 Fri Sep 16, 2016 8:29 pm
This topic is locked: you cannot edit posts or make replies. ERROR WHILE SUBMITTING LOAD JOB ANURADHA NEELAKANTAN JCL & VSAM 12 Thu Aug 25, 2016 11:50 am
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us