Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: SELECT Primary keys

    Hello,

    I have a database from some tables. Each of them has a primary key.
    I have to write a query to get tables, which have primary keys as an answer.
    Could anyone give some ideas how to do this?

    Thanks for help,
    Elline

  2. #2
    Join Date
    Feb 2004
    Posts
    24

    Re: SELECT Primary keys

    Hi,

    u have to write a query with a join over sysibm.systables sysibm.syscolumns and maybe sysibm.syskeys (for the index names).

    hope this will help !

    regards

    marc gaines
    Originally posted by elline
    Hello,

    I have a database from some tables. Each of them has a primary key.
    I have to write a query to get tables, which have primary keys as an answer.
    Could anyone give some ideas how to do this?

    Thanks for help,
    Elline

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SELECT Primary keys

    If you are on Unix, you can use the UNIQUERULE Column of the SYSCAT.INDEXES view ... UNIQUERULE is 'P' for primary keys ...

    Please read 'Must Read Before Posting' thread ....

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Feb 2004
    Posts
    24

    Re: SELECT Primary keys

    a little example (db2 for z/os):

    select distinct t.name
    from sysibm.systables t
    inner join sysibm.syscolumns c
    on t.name = c.tbname
    where t.creator = c.tbcreator
    and c.keyseq > 0

    just an idea !

    regards

    marc gaines

    Originally posted by elline
    Hello,

    I have a database from some tables. Each of them has a primary key.
    I have to write a query to get tables, which have primary keys as an answer.
    Could anyone give some ideas how to do this?

    Thanks for help,
    Elline

  5. #5
    Join Date
    Feb 2004
    Posts
    7

    Re: SELECT Primary keys

    Hi Elline

    You have to join SYSIBM.SYSINDEXES and SYSIBM.SYSKEYS. For a given table and table creator (qualifier), join IXNAME and IXCREATOR columns between these two catalogs.

    You will get the keys. Have the query sort by COLSEQ column of SYSKEYS.

    Hope this helps

    =============================================
    Originally posted by elline
    Hello,

    I have a database from some tables. Each of them has a primary key.
    I have to write a query to get tables, which have primary keys as an answer.
    Could anyone give some ideas how to do this?

    Thanks for help,
    Elline

  6. #6
    Join Date
    Jan 2004
    Location
    Egypt
    Posts
    61

    Re: SELECT Primary keys

    Originally posted by elline
    Hello,

    I have a database from some tables. Each of them has a primary key.
    I have to write a query to get tables, which have primary keys as an answer.
    Could anyone give some ideas how to do this?

    Thanks for help,
    Elline
    one workaround idea that may help is to change DB2 diaglevel to 4, then use db2cc to list primary key of any table, then check db2diag.log and see the SQL used to list the primary keys, try to apply the same SQL on all tables using batch file

    this donot need DB2 knowledge

    donot forget to return yr diaglevel to normal ( 2 or 3 )
    Last edited by hanyheggy; 03-01-04 at 00:27.
    hany heggy,
    IBM certified Professional, AIX system support
    www.melodyhits.tv

Posting Permissions

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