Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Sysibm.syskeys ??

    Hi,

    DB2V9.5 Fixpack 5 on Win2K3 Server

    I just saw an old post http://www.dbforums.com/db2/1645408-...ml#post6411709

    Can some one tell me what happened to SYSIBM.SYSKEYS

    I cannot use that query because I do not have SYSIBM.SYSKEYS

    Thanks in advance

    DBFinder

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The supported method of accessing the catalog is via SYSCAT.* views, precisely for this reason.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks, but my concern is to use that indexes query, just replacing by syscat.views may not help.

    Regards

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Send a note to Lenny and ask him to update his query for DB2 LUW 9.5.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Now query improved and became more informative

    Now this query a little bit modified and become more informative:

    Code:
    select 
           case 
             when (mn.minix = si.name and sk.colseq = 1) then si.tbname
             else ' '
            end  table_nme,
           case 
             when (sk.colseq = 1) then si.name 
             else ' '
           end index_nme,
           sk.colname, 
           case
              when sc.coltype = 'CHAR' 
             then  strip(sc.coltype) || '(' || varchar(sc.length) || ')' 
              when sc.coltype = 'FLOAT' and sc.length = 2
             then  'DOUBLE'
              when sc.coltype = 'VARCHAR' 
             then  strip(sc.coltype) || '(' || varchar(sc.length) || ')'
              when sc.coltype = 'DECIMAL' 
             then  strip(sc.coltype) || '(' || varchar(sc.length) || ', ' 
                                            || varchar(scale) || ')'
             else  sc.coltype 
           end    as coltype,      
           sk.colseq,
           case 
             when (sk.colseq = 1) then '      ' || si.UNIQUERULE
             else ' '
           end UNIQUERULE
     from sysibm.sysindexes  si
       join
          sysibm.syskeys     sk 
       on si.name      = sk.ixname
       join  
          sysibm.syscolumns  sc 
       on sc.tbname    = si.tbname    
          and
          sc.name      = sk.colname
    	and
    	sc.tbcreator = si.tbcreator
       join table
          (select min(name) minix, tbname
             from sysibm.sysindexes mn
             where   mn.tbcreator = sc.tbcreator 
             group by tbname) mn
       on
           mn.tbname = si.tbname    
               
    where     
    si.tbname in (ucase('your_tab1'), 
                       ucase('your_tab2'))                      
    and
     si.tbcreator = ucase('your_creator')
    
    order by si.tbname, si.name, sk.colseq
    with ur;
    Lenny
    Last edited by Lenny77; 04-21-10 at 18:29.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lenny77 View Post
    Now this query a little bit modified and become more informative:
    Is it still "working in all system of DB2 and good for all DB2 versions"?

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Smile I remember tale about Bear-painter

    Quote Originally Posted by n_i View Post
    Is it still "working in all system of DB2 and good for all DB2 versions"?
    I am using this query heavy.

    At least it's good for one man in Universe.

    I know somebody in my company who use it also.

    Lenny

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    It looks like no one had tried running Lenny's original query on DB2 LUW.

    The SYSIBM.SYSKEYS table contains one row for each column of an index key:
    IBM Information Management Software for z/OS Solutions Information Center


    SYSCAT.INDEXCOLUSE catalog view - Each row represents a column that participates in an index:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows


    I guess DBFinder could modify the query to use this view instead of syskeys.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you use any of the tables in schema SYSIBM (except for SYSIBM.SYSDUMMY1), you are begging for problems in the long run. You should only use the documented interfaces, i.e. SYSCAT.INDEXES.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Lenny77 View Post
    I am using this query heavy.

    At least it's good for one man in Universe.

    I know somebody in my company who use it also.

    Lenny
    What version DB2 ?

    I am on V9.5.5, this does not have sysibm.syskeys.

    Will work around, just wanted to make sure that I am not making any mistake !!

    Thanks Lenny, this was a good contribution.

    DBFinder

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down V8...

    Quote Originally Posted by DBFinder View Post
    What version DB2 ?

    I am on V9.5.5, this does not have sysibm.syskeys.

    Will work around, just wanted to make sure that I am not making any mistake !!

    Thanks Lenny, this was a good contribution.

    DBFinder
    Unfortunately for our common business, I am working for huge and conservative organization. Maybe the biggest in the world.

    We still use Version 8 of DB2.

    Lenny

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Lenny77 View Post
    Unfortunately for our common business, I am working for huge and conservative organization. Maybe the biggest in the world.

    Lenny
    Not bad, as long as you are progressive.

    I wish, ever, me and the girl visit your wonderful organization.

    BTW: I have upgraded all servers in our small company, I am still suffering. May be your guys are not willing to take risk.
    Let me make clear, suffering means that I had hard time fixing incompatibilities by work-arounds. We have IBM support but by the time they really help you ; you cannot afford even 1 hour downtime on your production databases.

    Cheers
    DBFinder
    Last edited by DBFinder; 04-22-10 at 12:36.

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink Nashe delo pravoe - pobeda budet za nami !

    Quote Originally Posted by DBFinder View Post
    Not bad, as long as you are progressive.

    I wish, ever, me and the girl visit your wonderful organization.

    BTW: I have upgraded all servers in our small company, I am still suffering. May be your guys are not willing to take risk.
    Let me make clear, suffering means that I had hard time fixing incompatibilities by work-arounds. We have IBM support but by the time they really help you ; you cannot afford even 1 hour downtime on your production databases.

    Cheers
    DBFinder
    Remember what said Molotov on June 22 1941 !

    Lenny

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by DBFinder View Post
    What version DB2 ?

    I am on V9.5.5, this does not have sysibm.syskeys.

    DBFinder

    Lenny is using mainframe so he doesn't need to worry about those syscat views.

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Lenny, you would need to translate "Nashe ....." for DBFinder

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •