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
 

 

Create index DDL

 
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: Wed May 16, 2012 4:08 pm    Post subject: Create index DDL
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: 6968
Location: porcelain throne

PostPosted: Wed May 16, 2012 4:39 pm    Post subject:
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    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Wed May 16, 2012 5:28 pm    Post subject:
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    Post subject:
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    Post subject:
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: 6968
Location: porcelain throne

PostPosted: Wed May 16, 2012 9:32 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. rexx to create a tso command Bharath Vikraman CLIST & REXX 1 Tue Aug 08, 2017 3:32 pm
This topic is locked: you cannot edit posts or make replies. rexx code to create a ps file Bharath Vikraman CLIST & REXX 4 Mon Aug 07, 2017 10:30 am
No new posts How do you create Eclipse based dialogs jasorn IBM Tools 0 Thu Aug 03, 2017 5:05 pm
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm


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