Truncate table with references in 9.5

Ragnar Rova

Truncate table with references in 9.5

I need to find a general procedure to quickly truncate a table which has references to other tables using db2 9.5.3 for windows. I use LOAD FROM NUL OF DEL REPLACE INTO tablename but I need to take care of referenced tables in integrity check pending state. To check integrity, I have borrowed the following from

with gen(tabname, seq) as( select rtrim(tabschema) || '.' || rtrim(tabname)
as tabname, row_number() over (partition by status) as seq
from syscat.tables WHERE status='C' ),r(a, seq1) as (select
CAST(tabname as VARCHAR(3900)), seq
from gen where seq=1 union all select r.a || ','|| rtrim(gen.tabname), gen.seq
from gen , r where (r.seq1+1)=gen.seq ), r1 as (select a, seq1 from r)
select 'SET INTEGRITY FOR ' || a || ' IMMEDIATE CHECKED;' from r1
where seq1=(select max(seq1) from r1)

I have combined load and the dynamic sql above into a stored procedure. I works as expected, but can on rare occasions fail with:


Could some argument, to rtrim, like gen.tabname be undefined here? Or are there sometimes simply no tables in check pending state when the error occurs? Why do some examples for finding tables when building a set integrity statement from syscat.tables sometimes include selections like TABSCHEMA NOT LIKE 'SYS%' and TYPE = 'T' ?

Any hints of why this sometimes fails or pointers to better ways to truncate greatly appreciated.

* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants

If you need to change settings, is the home of IDUG's Listserv