IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Create index DDL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed May 16, 2012 4:08 pm
Reply with quote

Hi,
I want to generate the create index ddl from catalog tables using sql query.

EXAMPLE

Code:
CREATE TABLE ABC
( COL1  CHAR(10) NOT NULL ,
   COL2 CHAR(10) NOT NULL,
   COL3 CHAR(10) NOT NULL,
  COL4 CHAR(10) NOT NULL
)

CREATE UNIQUE INDEX01 ON ABC
(
 COL1 ASC,
COL2 ASC
)


CREATE INDEX02 ON ABC
(
 COL1 ASC,
COL3 ASC
)


once this table is created, the entries are stored in sysibm.indexes and sysibm.syskeys.

how can we generate the create index ddl's from catalog tables using sql query.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed May 16, 2012 4:39 pm
Reply with quote

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

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed May 16, 2012 4:57 pm
Reply with quote

Thanks Dick... But i need to prepare the create index DDL through query...

like
Code:

select 'create index '||ixname||'on' ..... from sysibm.sysindexes
union all
select cols from syskeys
.
.
.


So how we can write the query to achieve this?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed May 16, 2012 5:28 pm
Reply with quote

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

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed May 16, 2012 7:07 pm
Reply with quote

Thanks Dick for the inputs...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed May 16, 2012 9:18 pm
Reply with quote

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

Global Moderator


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

PostPosted: Wed May 16, 2012 9:32 pm
Reply with quote

Quote:
case when colseq = 1 then ' (' else ' ,' end ||

for those of us who only think about lists with the comma at the end,
instead of the beginning.

good post Guy
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu May 17, 2012 11:49 am
Reply with quote

Thanks Dick and GuyC for all the help...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to create a list of SAR jobs with... CA Products 3
No new posts Cobol file using index COBOL Programming 2
No new posts create rexx edit Macro that edits the... CLIST & REXX 3
No new posts COBOL - create and write to output fi... COBOL Programming 0
No new posts Best way to create an automated line ... TSO/ISPF 3
Search our Forums:

Back to Top