Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
modify the SELECT (to add the literals necessary to build your IDX Create).
you may want to run this as is,
after putting in your creator literal in the 3 lines,
to see what you have to start with.
Code:
SELECT SUBSTR(I.TBNAME,1,20) AS TBNAME
, SUBSTR(K.IXNAME,1,12) AS IXNAME
, SUBSTR(K.COLNAME,1,16) AS COLNAME
, K.COLSEQ AS SEQ
, REPLACE(REPLACE(SUBSTR(DIGITS(K.COLNO),2,4),'00',' ')
,' 0', ' ') AS COLN
, I.UNIQUERULE AS U_D
, K.ORDERING AS ORD
, SUBSTR(COLTYPE,1,8) AS COLTYPE
, LENGTH AS LEN
, NULLS
FROM SYSIBM.SYSKEYS K
, SYSIBM.SYSINDEXES I
, SYSIBM.SYSCOLUMNS C
WHERE K.IXCREATOR = <<<<<your creator
AND I.TBCREATOR = <<<<<your creator
AND C.TBCREATOR = <<<<<your creator
AND I.NAME = K.IXNAME
AND C.NAME = K.COLNAME
AND I.TBNAME = C.TBNAME
AND I.TBNAME NOT IN (
'DSN_FUNCTION_TABLE'
,'DSN_STATEMENT_CACHE_AUX'
,'DSN_STATEMENT_CACHE_TABLE'
,'DSN_STATEMNT_TABLE'
,'PLAN_HISTORY'
,'PLAN_TABLE'
)
ORDER BY 1, 2, 4
WITH UR
;
the exclusion IN-LIST is based on db2v8.
if you have 9 or 10, there are probably more db2 required-per-creator tables than i have listed
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
you want the output of the sql to be 80-column card imagesl.....
using the UNION will effectively provide your line break,
but i think that you are limited to only one index at a time
you can deal with the ( and ) as separate SELECTs UNIONed before and after the column SELECT,
but I think you are going to have fun determining
if there is yet another column
thus requiring a decision on whether to have a comma (,) or space.
i have always done it the easy way.
single SELECT for either all tables within a creator or a 'selection' of tables
and then parsed the output and generated the DDL with REXX or SORT.
Plus, I can not believe that the DBA's don't have some admin tool that will do this already.
This is a start, you need more stuff like partitioned, unique where not null, compress, bufferpool , ....
Code:
select stmt from
(
select i.creator as seq0,i.name as seq1, 0 as seq2,1 as seq3
, 'create ' || case when uniquerule in ('P','U') then 'UNIQUE ' else '' end
|| 'index '|| strip(creator) || '.' || strip(i.name) as stmt
from sysibm.sysindexes I
where I.dbname like 'DSNDB0%'
union all
select i.creator as seq0, i.name as seq1, 0 as seq2,2 as seq3
, ' on '|| strip(i.tbcreator) || '.' || strip(i.tbname) as stmt
from sysibm.sysindexes I
where I.dbname like 'DSNDB0%'
union all
select i.creator as seq0,i.name as seq1, 1 as seq2, k.colseq as seq3
, case when colseq = 1 then ' (' else ' ,' end ||
strip(k.colname) || case when k.ordering = 'D' then ' DESC' else ' ' end as stmt
from sysibm.sysindexes I
join sysibm.syskeys K on k.ixcreator = I.creator and K.ixname = I.name
where I.dbname like 'DSNDB0%'
union all
select i.creator as seq0, i.name as seq1, 1 as seq2,9999 as seq3, ' )' as stmt
from sysibm.sysindexes I
where I.dbname like 'DSNDB0%'
) A
order by seq1,seq2,seq2,seq3