Is there a way to check the order in which SET INTEGRITY needs to be applied?

[Brian Tkatch]

Is there a way to check the order in which SET INTEGRITY needs to be
applied? This would be for a script with a dynamic list of TABLEs.

B.

[Lennart]

Brian Tkatch wrote:

Is there a way to check the order in which SET INTEGRITY needs to be
applied? This would be for a script with a dynamic list of TABLEs.

B.

Not exactly the same problem (I use it to archive old data, and need to
delete from the tables in a certain order to not violate foreign keys),
but the solution should work I think. First I select "independent
tables in the set of tables, and the follow foreign keys recursively.
Since a table might occur several times in the graph, I choose the
largest level for each table. Finally I sort the tables according to
the level. As mentioned it is not exactly what you want, but it should
give you some ideas (Not fully tested though):

#!/bin/sh

ADMISSION_ROUND=$1

if [ "x${ADMISSION_ROUND}" = "x" ]; then
prog=`basename $0`
echo "Usage: $prog <admissionround_id>"
exit 1
fi

iter="with t (tabschema, tabname, level) as ( \
values ('NYA', 'ADMISSION_ROUND', 0) \
union all \
select c.tabschema, c.tabname, 0 \
from syscat.columns c \
where c.colname = 'ADMISSIONROUND_ID' \
and c.tabschema = 'NYA' \
and not exists ( \
select 1 from syscat.references r \
where (r.tabschema, r.tabname) = (c.tabschema,
c.tabname) \
and fk_colnames <'ADMISSIONROUND_ID' \
) \
union all \
select r.tabschema, r.tabname, level+1 \
from t, syscat.references r \
where (t.tabschema, t.tabname) = (r.reftabschema, r.reftabname)
\
and r.tabschema = 'NYA' \
and level < 100 \
), res(tabname,level) as ( \
select tabname, max(level) as level from t \
group by tabname \
) select x.tabname from res x, syscat.tables t \
where ('NYA', x.tabname) = (t.tabschema, t.tabname) \
and t.type = 'T' \
order by level desc"

db2 connect to ltjn

for t in `db2 -x "$iter"`; do
echo "Archiving $t ..."
db2 -v "export to $t.ixf of ixf select * from nya.$t \
where admissionround_id = '${ADMISSION_ROUND}'"
if [ $? -ne 0 ]; then
echo "Failure"
exit 1
fi
# db2 +c -v "delete from nya.$t \
db2 -v "delete from nya.$t \
where admissionround_id = '${ADMISSION_ROUND}'"
if [ $? -gt 1 ]; then
echo "Failure"
exit 1
fi
# db2 rollback
done

tar cvfz ${ADMISSION_ROUND}-archive.tgz *.ixf /dev/null 2>&1

[Brian Tkatch]

Lennart wrote:

Brian Tkatch wrote:

Is there a way to check the order in which SET INTEGRITY needs to be
applied? This would be for a script with a dynamic list of TABLEs.

B.

Not exactly the same problem (I use it to archive old data, and need to
delete from the tables in a certain order to not violate foreign keys),
but the solution should work I think. First I select "independent
tables in the set of tables, and the follow foreign keys recursively.
Since a table might occur several times in the graph, I choose the
largest level for each table. Finally I sort the tables according to
the level. As mentioned it is not exactly what you want, but it should
give you some ideas (Not fully tested though):

#!/bin/sh

ADMISSION_ROUND=$1

if [ "x${ADMISSION_ROUND}" = "x" ]; then
prog=`basename $0`
echo "Usage: $prog <admissionround_id>"
exit 1
fi

iter="with t (tabschema, tabname, level) as ( \
values ('NYA', 'ADMISSION_ROUND', 0) \
union all \
select c.tabschema, c.tabname, 0 \
from syscat.columns c \
where c.colname = 'ADMISSIONROUND_ID' \
and c.tabschema = 'NYA' \
and not exists ( \
select 1 from syscat.references r \
where (r.tabschema, r.tabname) = (c.tabschema,
c.tabname) \
and fk_colnames <'ADMISSIONROUND_ID' \
) \
union all \
select r.tabschema, r.tabname, level+1 \
from t, syscat.references r \
where (t.tabschema, t.tabname) = (r.reftabschema, r.reftabname)
\
and r.tabschema = 'NYA' \
and level < 100 \
), res(tabname,level) as ( \
select tabname, max(level) as level from t \
group by tabname \
) select x.tabname from res x, syscat.tables t \
where ('NYA', x.tabname) = (t.tabschema, t.tabname) \
and t.type = 'T' \
order by level desc"

db2 connect to ltjn

for t in `db2 -x "$iter"`; do
echo "Archiving $t ..."
db2 -v "export to $t.ixf of ixf select * from nya.$t \
where admissionround_id = '${ADMISSION_ROUND}'"
if [ $? -ne 0 ]; then
echo "Failure"
exit 1
fi
# db2 +c -v "delete from nya.$t \
db2 -v "delete from nya.$t \
where admissionround_id = '${ADMISSION_ROUND}'"
if [ $? -gt 1 ]; then
echo "Failure"
exit 1
fi
# db2 rollback
done

tar cvfz ${ADMISSION_ROUND}-archive.tgz *.ixf /dev/null 2>&1

Wow, that's bigger than the statement i wanted to see, like SET
INTEGRITY FOR <tableIMMEDIATE CHECKED
AND_I_DO_MEAN_ALL_SO_FOLLOW_THOSE_KEY_TREES *ehem* i mean: SET
INTEGRITY FOR <tableIMMEDIATE CHECKED WITH CASCADE;

I'm going to look at what you wrote (thanx!). In the meanwhile, i came
up with another idea. The basic thought is that if SET INTEGRITY is run
on a list of TABLEs in a loop, and the loop runs once for each TABLE in
the list, even when it is in reverse order (which would be just my
luck) it's got to zap 'em all by the end.

Then i thought, maybe i could check the COUNT(*) WHERE Status = 'C'
(with an IN list) and just run it until it returns 0 (or it doesn't go
down between runs).

My question is, is your script faster or slower than my brute force
idea? :)

B.

[Lennart]

Brian Tkatch wrote:
[...]

>
Wow, that's bigger than the statement i wanted to see, like SET
INTEGRITY FOR <tableIMMEDIATE CHECKED
AND_I_DO_MEAN_ALL_SO_FOLLOW_THOSE_KEY_TREES *ehem* i mean: SET
INTEGRITY FOR <tableIMMEDIATE CHECKED WITH CASCADE;

I'm going to look at what you wrote (thanx!). In the meanwhile, i came
up with another idea. The basic thought is that if SET INTEGRITY is run
on a list of TABLEs in a loop, and the loop runs once for each TABLE in
the list, even when it is in reverse order (which would be just my
luck) it's got to zap 'em all by the end.

Problem is, the number of tables you will have to set integrity on
might increase. Assume you have table T with status C. If you set
integrity on it, you will have to set integrity on tables dependent of
it. So you might end up with

iteration table
1 T1
2 T2, T3, T4
3 T5, T6, T7, T8, T9
etc

If you are unlucky, you might end up setting integrity on each and
every table in the database (and some tables more than once).

Then i thought, maybe i could check the COUNT(*) WHERE Status = 'C'
(with an IN list) and just run it until it returns 0 (or it doesn't go
down between runs).

Something like (pseducode):

while (true)
for t in `db2 "select tabname from syscat.tables where status =
'C'"; do
db2 "set integrity ..."
done
# test if tables remain, if so break;
done

My question is, is your script faster or slower than my brute force
idea? :)

In general I would say that it is faster trying to figure out how to do
it in right order. On the other hand, if few people use the database at
the time of execution of the script, it might not be worth it. A brute
force solution should not take that long to implement, if that is not
fast enough, concider other things.

If you have access to python/perl or something similar, another option
is to build a graph from syscat.references and use topological sort to
determine the order. I think I have some pythoncode lying around, drop
me a note if interested.

/Lennart

[PaulR]

fwiw.

I have a shell script that does this brute force method for our
Database of some 300+ Tables and it works just fine i.e the overhead
trying to set integrity where it's not required is miinimal.

Brian Tkatch wrote:

My question is, is your script faster or slower than my brute force
idea? :)

B.

[Brian Tkatch]

Lennart wrote:

Brian Tkatch wrote:
[...]

Wow, that's bigger than the statement i wanted to see, like SET
INTEGRITY FOR <tableIMMEDIATE CHECKED
AND_I_DO_MEAN_ALL_SO_FOLLOW_THOSE_KEY_TREES *ehem* i mean: SET
INTEGRITY FOR <tableIMMEDIATE CHECKED WITH CASCADE;

I'm going to look at what you wrote (thanx!). In the meanwhile, i came
up with another idea. The basic thought is that if SET INTEGRITY is run
on a list of TABLEs in a loop, and the loop runs once for each TABLE in
the list, even when it is in reverse order (which would be just my
luck) it's got to zap 'em all by the end.

Problem is, the number of tables you will have to set integrity on
might increase. Assume you have table T with status C. If you set
integrity on it, you will have to set integrity on tables dependent of
it. So you might end up with

iteration table
1 T1
2 T2, T3, T4
3 T5, T6, T7, T8, T9
etc

If you are unlucky, you might end up setting integrity on each and
every table in the database (and some tables more than once).

Then i thought, maybe i could check the COUNT(*) WHERE Status = 'C'
(with an IN list) and just run it until it returns 0 (or it doesn't go
down between runs).

Something like (pseducode):

while (true)
for t in `db2 "select tabname from syscat.tables where status =
'C'"; do
db2 "set integrity ..."
done
# test if tables remain, if so break;
done

My question is, is your script faster or slower than my brute force
idea? :)

In general I would say that it is faster trying to figure out how to do
it in right order. On the other hand, if few people use the database at
the time of execution of the script, it might not be worth it. A brute
force solution should not take that long to implement, if that is not
fast enough, concider other things.

If you have access to python/perl or something similar, another option
is to build a graph from syscat.references and use topological sort to
determine the order. I think I have some pythoncode lying around, drop
me a note if interested.

/Lennart

Wow, you're amazing. I appreciate you taking the time to help me.

Problem is, the number of tables you will have to set integrity on
might increase. Assume you have table T with status C. If you set
integrity on it, you will have to set integrity on tables dependent of it.

Because of that, i figured i didn't want to mess. I just want he script
to SET INTEGRITY on its own TABLEs, and on nothing else. So, if the
number increases outside of the listed TABLEs, the process itself is
flawed.

Now to that pseudocode:

x1=`db2 "select COUNT(*) from syscat.tables where status =
'C' AND (TabSchema, TabName) IN (<schema and tables in file>)"
while (read schema table)
do
db2 "set integrity ..."
x2=`db2 "select COUNT(*) from syscat.tables where status =
'C' AND (TabSchema, TabName) IN (<schema and tables in file>)" ;
if ! (x2 < x1) break; else x1=x2;
done < file

It could be fancier by checking the status of each table afterwards and
see if it got reset. Plus, set integrity can do all the tables in the
same statement.

In general I would say that it is faster trying to figure out how to do
it in right order. On the other hand, if few people use the database at
the time of execution of the script, it might not be worth it. A brute
force solution should not take that long to implement, if that is not
fast enough, concider other things.

Yeah, i agree.

B.

[Brian Tkatch]

PaulR wrote:

fwiw.

I have a shell script that does this brute force method for our
Database of some 300+ Tables and it works just fine i.e the overhead
trying to set integrity where it's not required is miinimal.

Care to share? :)

B.

[Lennart]

Brian Tkatch wrote:
[...]

Problem is, the number of tables you will have to set integrity on
might increase. Assume you have table T with status C. If you set
integrity on it, you will have to set integrity on tables dependent of it.

Because of that, i figured i didn't want to mess. I just want he script
to SET INTEGRITY on its own TABLEs, and on nothing else. So, if the
number increases outside of the listed TABLEs, the process itself is
flawed.

I did a simple test and perhaps the problem is less complicated than I
thought:

create table x.a (x int not null primary key);
create table x.b (x int not null primary key);
create table x.c (x int not null primary key);
create table x.d (x int not null primary key);
create table x.e (x int not null primary key);

alter table x.a add foreign key (x) references x.b;
alter table x.b add foreign key (x) references x.c;
alter table x.c add foreign key (x) references x.d;
alter table x.d add foreign key (x) references x.e;

-- make x.a check pending
load from /dev/null of ixf replace into x.e;

As expected x.e is now check pending.

[ltjn@lelles ~/src/integrity]$ db2 set integrity for x.e immediate
checked
SQL3601W The statement caused one or more tables to automatically be
placed
in the check pending state. SQLSTATE=01586
[ltjn@lelles ~/src/integrity]$ db2 "select tabname from syscat.tables
where status = 'C'"

TABNAME

--------------------------------------------------------------------------------------------------------------------------------
A

B

C

D
4 record(s) selected.

Now, that was a surprise for me. I thought that only X.D would be
check pending, but apparently DB2 is smart enough to cascade through
the graph. Hence, from here the set of "check pending" tables should
decrease. Can anyone confirm that this is true at all times, or could
it still happen that a new table is added to the set of check pending
tables?

Brian, as mentioned earlier I use the algorithm for a different
purpose, and perhaps it is way overkill for your problem. Sorry for any
confusion I may have caused

/Lennart

[...]

[Brian Tkatch]

Lennart wrote:

Brian Tkatch wrote:
[...]

Problem is, the number of tables you will have to set integrity on
might increase. Assume you have table T with status C. If you set
integrity on it, you will have to set integrity on tables dependent of it.

Because of that, i figured i didn't want to mess. I just want he script
to SET INTEGRITY on its own TABLEs, and on nothing else. So, if the
number increases outside of the listed TABLEs, the process itself is
flawed.

I did a simple test and perhaps the problem is less complicated than I
thought:

create table x.a (x int not null primary key);
create table x.b (x int not null primary key);
create table x.c (x int not null primary key);
create table x.d (x int not null primary key);
create table x.e (x int not null primary key);

alter table x.a add foreign key (x) references x.b;
alter table x.b add foreign key (x) references x.c;
alter table x.c add foreign key (x) references x.d;
alter table x.d add foreign key (x) references x.e;

-- make x.a check pending
load from /dev/null of ixf replace into x.e;

As expected x.e is now check pending.

[ltjn@lelles ~/src/integrity]$ db2 set integrity for x.e immediate
checked
SQL3601W The statement caused one or more tables to automatically be
placed
in the check pending state. SQLSTATE=01586
[ltjn@lelles ~/src/integrity]$ db2 "select tabname from syscat.tables
where status = 'C'"

TABNAME

--------------------------------------------------------------------------------------------------------------------------------
A

B

C

D
4 record(s) selected.

Now, that was a surprise for me. I thought that only X.D would be
check pending, but apparently DB2 is smart enough to cascade through
the graph. Hence, from here the set of "check pending" tables should
decrease. Can anyone confirm that this is true at all times, or could
it still happen that a new table is added to the set of check pending
tables?

Brian, as mentioned earlier I use the algorithm for a different
purpose, and perhaps it is way overkill for your problem. Sorry for any
confusion I may have caused

/Lennart

[...]

Actually, the results you just displayed is what i expected. I cannot
test these things myself easily, as i do not have CREATE TABLE rights,
even on dev. :(

In my case, i loaded most of the TABLEs from production into
development (except for a few that are still in the works) and a
couple of those went into CHECK PENDING only after the SET INTEGRITY
statement (actually, i'm basing this on the resulting message of SET
INTEGRITY). I am not sure i fully iunderstand why. This should be the
equivalent to your case.

What i did just notice, however, is that i can SET INTEGRITY on
multiple TABLEs at the same time. And, at first look, as long as all of
the TABLEs can be set by that user, they indeed will be set. Could your
perchance test that for me? That is, using the example given above, do
a SET INTEGRITY FOR x.a, x.b, x.c, x.d, x.e IMMEDIATE CHECKED.

I think i shall write a query now to generate a statement to put
together a full SET INTEGRITY statement that includes:
1) TABLEs (Type = 'T')
2) in CHECK PENDING state (Status = 'C')
3) in a provided list ( (TabSchema, TabName) IN (list from file))

After it runs it, it will, need to SET INTEGRITY on any TABLEs that it
put into CHECK PENDING. The question now is, how does it know what it
put into CHECK PENDING? The two ideas i have are to take a snapshot
before and after the SET INTEGRITY statement. The other idea would be
to specifically check TABLEs that REFERENCE the TABLEs that were SET
(via SysCat.References).

B.

[Lennart]

Brian Tkatch wrote:
[...]

Actually, the results you just displayed is what i expected. I cannot
test these things myself easily, as i do not have CREATE TABLE rights,
even on dev. :(

Check if you can install a personal developer edition on your
desktop.<duckIf you are unfortenate enough to run windows</duck>, you
could use cygwin :-)

[...]

>
What i did just notice, however, is that i can SET INTEGRITY on
multiple TABLEs at the same time. And, at first look, as long as all of
the TABLEs can be set by that user, they indeed will be set. Could your
perchance test that for me? That is, using the example given above, do
a SET INTEGRITY FOR x.a, x.b, x.c, x.d, x.e IMMEDIATE CHECKED.

Sure, given:

drop table x.a;
drop table x.b;
drop table x.c;
drop table x.d;
drop table x.e;

create table x.a (x int not null primary key);
create table x.b (x int not null primary key);
create table x.c (x int not null primary key);
create table x.d (x int not null primary key);
create table x.e (x int not null primary key);

alter table x.a add foreign key (x) references x.b;
alter table x.b add foreign key (x) references x.c;
alter table x.c add foreign key (x) references x.d;
alter table x.d add foreign key (x) references x.e;

-- make x.a check pending
load from /dev/null of ixf replace into x.e;

SET INTEGRITY FOR x.a, x.b, x.c, x.d, x.e IMMEDIATE CHECKED;

select tabname from syscat.tables where status = 'C';

returns:

TABNAME

--------------------------------------------------------------------------------------------------------------------------------

0 record(s) selected.

So that works indeed. I wasnt aware that one could specify several
tables, so I'll keep that in mind.

I think i shall write a query now to generate a statement to put
together a full SET INTEGRITY statement that includes:
1) TABLEs (Type = 'T')
2) in CHECK PENDING state (Status = 'C')
3) in a provided list ( (TabSchema, TabName) IN (list from file))

After it runs it, it will, need to SET INTEGRITY on any TABLEs that it
put into CHECK PENDING. The question now is, how does it know what it
put into CHECK PENDING? The two ideas i have are to take a snapshot
before and after the SET INTEGRITY statement. The other idea would be
to specifically check TABLEs that REFERENCE the TABLEs that were SET
(via SysCat.References).

Given the results above I would go for a brute force solution like:

while (true)
set integrity for <tables in check pendingimmediate checked
if <tables in check pendingis empty
break

/Lennart

[Brian Tkatch]

Lennart wrote:

Brian Tkatch wrote:
[...]

Actually, the results you just displayed is what i expected. I cannot
test these things myself easily, as i do not have CREATE TABLE rights,
even on dev. :(

Check if you can install a personal developer edition on your
desktop.<duckIf you are unfortenate enough to run windows</duck>, you
could use cygwin :-)

Heh. I am using Windows. I'm also a holdout, in that i'm on NT, but i
ought to be forced to "upgrade" to XP soon (*shudder*).

In is an interesting idea though.

>
[...]

What i did just notice, however, is that i can SET INTEGRITY on
multiple TABLEs at the same time. And, at first look, as long as all of
the TABLEs can be set by that user, they indeed will be set. Could your
perchance test that for me? That is, using the example given above, do
a SET INTEGRITY FOR x.a, x.b, x.c, x.d, x.e IMMEDIATE CHECKED.

Sure, given:

drop table x.a;
drop table x.b;
drop table x.c;
drop table x.d;
drop table x.e;

create table x.a (x int not null primary key);
create table x.b (x int not null primary key);
create table x.c (x int not null primary key);
create table x.d (x int not null primary key);
create table x.e (x int not null primary key);

alter table x.a add foreign key (x) references x.b;
alter table x.b add foreign key (x) references x.c;
alter table x.c add foreign key (x) references x.d;
alter table x.d add foreign key (x) references x.e;

-- make x.a check pending
load from /dev/null of ixf replace into x.e;

SET INTEGRITY FOR x.a, x.b, x.c, x.d, x.e IMMEDIATE CHECKED;

select tabname from syscat.tables where status = 'C';

returns:

TABNAME

--------------------------------------------------------------------------------------------------------------------------------

0 record(s) selected.

So that works indeed. I wasnt aware that one could specify several
tables, so I'll keep that in mind.

Kewl. As i said, i just noticed it. I really noticed in when i was
looking at the EXCEPTIONS clause because the example shows multiple
TABLEs, it was then that i looked and saw the repeatable comma in the
diagram. Silly me, how many times have i missed the comma.

So, IIUC, this removed the dependency order issue. Now it's just the
collateral damage i need to worry about. :P

I think i shall write a query now to generate a statement to put
together a full SET INTEGRITY statement that includes:
1) TABLEs (Type = 'T')
2) in CHECK PENDING state (Status = 'C')
3) in a provided list ( (TabSchema, TabName) IN (list from file))

After it runs it, it will, need to SET INTEGRITY on any TABLEs that it
put into CHECK PENDING. The question now is, how does it know what it
put into CHECK PENDING? The two ideas i have are to take a snapshot
before and after the SET INTEGRITY statement. The other idea would be
to specifically check TABLEs that REFERENCE the TABLEs that were SET
(via SysCat.References).

Given the results above I would go for a brute force solution like:

while (true)
set integrity for <tables in check pendingimmediate checked
if <tables in check pendingis empty
break

Two issues.

1) There is no infinite loop protection here.
2) It may cause TABLEs that the script didn't put into CHECK PENDING to
be reset.

The infinite loop is a real issue. In my case, we added a new username
for testing, and a table that exists in development (but not
production) was thrown into CHECK PENDING because of the LOAD on the
user TABLE. There was no way out of it because of the failed value. I
had to use the EXCEPTIONS FOR clause to dump the records. (Which, as an
aside, did not work with a GLOBAL TEMPORARY TABLE; it returned a no
INSERT rights error.) With the above code, that'd be an infinite loop.

The don't touch issue, is that someone may want a TABLE in a CHECK
PENDING state (it does not matter why), and therefore only TABLEs that
(can reasonably be assumed that) changed because of the script should
be reset. This is more of a goal than a requirement, as most likely the
user would be happy if we reset it all.

As such, i think a loop is a Bad Idea (tm). It could be made better by
checking that the number of changed TABLEs always decreases, but in
reality, if that many TABLEs are hit (which are not in the LOADed TABLE
list) perhaps the user needs to rethink what he is doing anyway.

Instead, i'm now looking at two statements. The first to SET INTEGRITY
on all LOADed TABLEs, and the second to pick up the affected TABLEs.
I'm even wondering if we can guess what outside TABLEs will be
affected, and just put them into one big SET INTEGRITY statement.

Oh, and thanx for all the help. :)

B.

>
/Lennart

[PaulR]

Here it is, you will need to set $DBATMPPATH and $DBALOGPATH env. vars

I run this 10 times (i.e 10 levels in our Table dependencies)
i.e
for i in 1 2 3 4 5 6 7 8 9 0
do
set.integrity.all.ksh
done

#!/usr/bin/ksh

# Edit this list to include the partitions you want to run on
NODELIST="00 "
DATE=`date +"%Y%m%d.%H.%M"`

if [[ $# -ne 1 ]] then
echo
echo "------------------------------------------------------"
echo "Error: This command requires a database name"
echo
echo "usage: "
echo " $0 databasename"
echo "------------------------------------------------------"
exit 1
fi

DBNAME=$(echo $1 | tr 'a-z' 'A-Z')

FILENAME=$(basename $0)

i=0
for n in ${NODELIST?}
do
((i=i+1))
#i=`echo "${i} + 1" | bc `
node[i]=${n?}
done
NUMNODES=${i?}
DBNAME=$1 #database name
LOGFILE=$DBALOGPATH/$(basename $0).${DBNAME}.log #log
file
xx4=$DBATMPPATH/xx4
xx5=$DBATMPPATH/xx5
xx6=$DBATMPPATH/xx6

chmod -f 666 $xx4 $xx5 $xx6

mkdir -p $DBADATPATH/$DATE

db2 connect to $DBNAME
if [[ $? -ne 0 ]]
then
echo "Fail to connect to database $DBNAME"
exit 1
fi

rm -f $LOGFILE #remove the old log file
date >$LOGFILE
chmod 666 $LOGFILE

echo "Reading SYSCAT.TABLES ...."

#sort -r $xx4 $xx5
db2 -x +w "select RTRIM(tabschema)||'.'||RTRIM(tabname) from
SYSCAT.TABLES where status='C' and type = 'T' order by tabname asc"

>$xx5

sleep 2
no_lines=$(cat $xx5 |wc -l)
n=1

echo Generating Reverse Table list....
while read schema tablename
do
tablesr="${tablesr} ${schema}${tablename}"
done < $xx5

db2 connect to $DBNAME ;
total=0
for t in $tablesr
do
((total=total+1))
echo "Run Node=${node[n]} Table=$t"
#echo $t >$DBADATPATH/$DATE/EXPORT.LST
db2 "set integrity for ${t} immediate checked force
generated" | tee -a $LOGFILE

if [[ ${n} = ${NUMNODES} ]] then
echo waiting...
n=1
wait
echo
echo "Total so far ${total} out of ${no_lines} "
date
else
((n=n+1))
fi
done
wait
echo
echo "Total ${total} out of ${no_lines}"

#rm -f $xx4 $xx5 $xx6
#rm -f $xx5 $xx6
date |tee -a $LOGFILE
db2 connect reset
echo
echo

Brian Tkatch wrote:

PaulR wrote:

fwiw.

I have a shell script that does this brute force method for our
Database of some 300+ Tables and it works just fine i.e the overhead
trying to set integrity where it's not required is miinimal.

Care to share? :)

B.

[Brian Tkatch]

PaulR wrote:

Here it is, you will need to set $DBATMPPATH and $DBALOGPATH env. vars

I run this 10 times (i.e 10 levels in our Table dependencies)
i.e
for i in 1 2 3 4 5 6 7 8 9 0
do
set.integrity.all.ksh
done

#!/usr/bin/ksh

# Edit this list to include the partitions you want to run on
NODELIST="00 "
DATE=`date +"%Y%m%d.%H.%M"`

if [[ $# -ne 1 ]] then
echo
echo "------------------------------------------------------"
echo "Error: This command requires a database name"
echo
echo "usage: "
echo " $0 databasename"
echo "------------------------------------------------------"
exit 1
fi

DBNAME=$(echo $1 | tr 'a-z' 'A-Z')

FILENAME=$(basename $0)

i=0
for n in ${NODELIST?}
do
((i=i+1))
#i=`echo "${i} + 1" | bc `
node[i]=${n?}
done
NUMNODES=${i?}
DBNAME=$1 #database name
LOGFILE=$DBALOGPATH/$(basename $0).${DBNAME}.log #log
file
xx4=$DBATMPPATH/xx4
xx5=$DBATMPPATH/xx5
xx6=$DBATMPPATH/xx6

chmod -f 666 $xx4 $xx5 $xx6

mkdir -p $DBADATPATH/$DATE

db2 connect to $DBNAME
if [[ $? -ne 0 ]]
then
echo "Fail to connect to database $DBNAME"
exit 1
fi

rm -f $LOGFILE #remove the old log file
date >$LOGFILE
chmod 666 $LOGFILE

echo "Reading SYSCAT.TABLES ...."

#sort -r $xx4 $xx5
db2 -x +w "select RTRIM(tabschema)||'.'||RTRIM(tabname) from
SYSCAT.TABLES where status='C' and type = 'T' order by tabname asc"

$xx5

sleep 2
no_lines=$(cat $xx5 |wc -l)
n=1

echo Generating Reverse Table list....
while read schema tablename
do
tablesr="${tablesr} ${schema}${tablename}"
done < $xx5

db2 connect to $DBNAME ;
total=0
for t in $tablesr
do
((total=total+1))
echo "Run Node=${node[n]} Table=$t"
#echo $t >$DBADATPATH/$DATE/EXPORT.LST
db2 "set integrity for ${t} immediate checked force
generated" | tee -a $LOGFILE

if [[ ${n} = ${NUMNODES} ]] then
echo waiting...
n=1
wait
echo
echo "Total so far ${total} out of ${no_lines} "
date
else
((n=n+1))
fi
done
wait
echo
echo "Total ${total} out of ${no_lines}"

#rm -f $xx4 $xx5 $xx6
#rm -f $xx5 $xx6
date |tee -a $LOGFILE
db2 connect reset
echo
echo

Brian Tkatch wrote:

PaulR wrote:

fwiw.
>
I have a shell script that does this brute force method for our
Database of some 300+ Tables and it works just fine i.e the overhead
trying to set integrity where it's not required is miinimal.
>

Care to share? :)

B.

Very nice, thanx. I see where i did the same things, and where i did
not. I appreciate you showing this.

Is there a reason the script reconnects to the DB instead of doing it
all one one connection?
And, is there a reason the script does not send a TERMINATE after the
CONNECT RESET?

As i saw in the other sub-thread, it looks as if SET INTEGRITY can do
more than one level of dependency at the same time, regardless of
order, as long as they are in the same statement. I plan to test that
as soon when i have a chance.

B.

[PaulR]

Is there a reason the script reconnects to the DB instead of doing it
all one one connection?

No reason.

And, is there a reason the script does not send a TERMINATE after the
CONNECT RESET?

Again, no reason.

This script has evolved from several others which do similar things for
all tables. Hence, it is a little messy in places.

[Brian Tkatch]

PaulR wrote:

Is there a reason the script reconnects to the DB instead of doing it
all one one connection?

No reason.

And, is there a reason the script does not send a TERMINATE after the
CONNECT RESET?

Again, no reason.

This script has evolved from several others which do similar things for
all tables. Hence, it is a little messy in places.

That's isn't messy. It's just not efficient. :) Either way, thanx for
sharing.

I'm still wondering if you are able to due the one SET INTEGRITY for
all TABLEs at the same time.

B.

[Brian Tkatch]

Lennart wrote:

Given the results above I would go for a brute force solution like:

while (true)
set integrity for <tables in check pendingimmediate checked
if <tables in check pendingis empty
break

/Lennart

Here's another thing. The LOAD command itself has an option CHECK
PENDING CASCADE IMMEDIATE. If that is passed, any TABLE the SET
INTEGRITY would put in CHECK PENDING, would be put into CHECK PENDING
at the LOAD statement instead. This means no suprises after a first SET
INTEGRITY is issued.

However, how to find out what made which TABLEs go into CHECK PENDING?
I don't think that is possible, so i just want to issue one statement
that gets all TABLEs currently in CHECK PENDING state. (This itself
could fail due to a previous TABLE being LOADed without CHECK PENDING
CASCADE or even explicitly put into CHECK PENDING, but there's no real
way to check for this, so i'm just ignoring it.)

The only issue now is that if using one statement all TABLEs mentioned
in the statement must be in CHECK PENDING (or would become CHECK
PENDING because of another TABLE in the same statement) and that the
user has SysADM, DbAdm, or has the following per TABLE access, CONTROL
or both LOAD and SELECT (for LOCKing). I'm guessing CONTROL does not
also require LOCK (SELECT) priviliges. I cannot check it, and the
documentation does not even mention this restriction.

I've come up with the following statement. I do not check for SysAuth
because i do not know how. I do not check for group priviliges, because
i do not know how (i also don't bother checking that GranteeType is
'U', i'm avoiding this issue until i become a bit more enlightened on
the matter).

SELECT
'SET INTEGRITY FOR ' ||
SUBSTR
(
REPLACE
(
REPLACE
(
XML2CLOB
(
XMLAGG
(
XMLELEMENT
(
NAME A,
RTRIM(TabSchema)
|| '.'
|| RTRIM(TabName)
)
)
),
'<A>',
', '
),
'</A>',
''
),
3
)
|| ' IMMEDIATE CHECKED'
FROM
SysCat.Tables Tables
WHERE
Tables.Type = 'T'
AND Tables.Status = 'C'
AND (
EXISTS
(
SELECT
*
FROM
SysCat.DbAuth Auth
WHERE
Auth.Grantee = USER
AND Auth.DbAdmAuth = 'Y'
)
OR EXISTS
(
SELECT
*
FROM
SysCat.TabAuth Auth
WHERE
Auth.Grantee = USER
AND Auth.TabSchema = Tables.TabSchema
AND Auth.TabName = Tables.TabName
AND (
Auth.ControlAuth IN ('G', 'Y')
OR (
Auth.SelectAuth IN ('G', 'Y')
AND EXISTS
(
SELECT
*
FROM
SysCat.DbAuth Auth
WHERE
Auth.Grantee = USER
AND Auth.LoadAuth = 'Y'
)
)
)
)
)

B.

[Pierre Saint-Jacques]

Just a little add on.
The CONST_CHECKED col. used to say this (haven't checked in V8):
Pos1 of 32 Y means system checked for RI
Pos2 of 32 Y means system checked for Column constraint.
Pos5 of 32 Y means system checked for referesh MQT.
As for other positions they are "for future" use. Probably current docs on
8.2 or 9.1 would tell more.
Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Brian Tkatch" <Ma***********@ThePentagon.coma écrit dans le message de
news: 11*********************@f1g2000cwa.googlegroups.co m...

Lennart wrote:

>Given the results above I would go for a brute force solution like:

while (true)
set integrity for <tables in check pendingimmediate checked
if <tables in check pendingis empty
break

/Lennart

Here's another thing. The LOAD command itself has an option CHECK
PENDING CASCADE IMMEDIATE. If that is passed, any TABLE the SET
INTEGRITY would put in CHECK PENDING, would be put into CHECK PENDING
at the LOAD statement instead. This means no suprises after a first SET
INTEGRITY is issued.

However, how to find out what made which TABLEs go into CHECK PENDING?
I don't think that is possible, so i just want to issue one statement
that gets all TABLEs currently in CHECK PENDING state. (This itself
could fail due to a previous TABLE being LOADed without CHECK PENDING
CASCADE or even explicitly put into CHECK PENDING, but there's no real
way to check for this, so i'm just ignoring it.)

The only issue now is that if using one statement all TABLEs mentioned
in the statement must be in CHECK PENDING (or would become CHECK
PENDING because of another TABLE in the same statement) and that the
user has SysADM, DbAdm, or has the following per TABLE access, CONTROL
or both LOAD and SELECT (for LOCKing). I'm guessing CONTROL does not
also require LOCK (SELECT) priviliges. I cannot check it, and the
documentation does not even mention this restriction.

I've come up with the following statement. I do not check for SysAuth
because i do not know how. I do not check for group priviliges, because
i do not know how (i also don't bother checking that GranteeType is
'U', i'm avoiding this issue until i become a bit more enlightened on
the matter).

SELECT
'SET INTEGRITY FOR ' ||
SUBSTR
(
REPLACE
(
REPLACE
(
XML2CLOB
(
XMLAGG
(
XMLELEMENT
(
NAME A,
RTRIM(TabSchema)
|| '.'
|| RTRIM(TabName)
)
)
),
'<A>',
', '
),
'</A>',
''
),
3
)
|| ' IMMEDIATE CHECKED'
FROM
SysCat.Tables Tables
WHERE
Tables.Type = 'T'
AND Tables.Status = 'C'
AND (
EXISTS
(
SELECT
*
FROM
SysCat.DbAuth Auth
WHERE
Auth.Grantee = USER
AND Auth.DbAdmAuth = 'Y'
)
OR EXISTS
(
SELECT
*
FROM
SysCat.TabAuth Auth
WHERE
Auth.Grantee = USER
AND Auth.TabSchema = Tables.TabSchema
AND Auth.TabName = Tables.TabName
AND (
Auth.ControlAuth IN ('G', 'Y')
OR (
Auth.SelectAuth IN ('G', 'Y')
AND EXISTS
(
SELECT
*
FROM
SysCat.DbAuth Auth
WHERE
Auth.Grantee = USER
AND Auth.LoadAuth = 'Y'
)
)
)
)
)

B.