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: 6966
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: 6966
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: 1278
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: 6966
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 GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am


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