Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: Determining Primary Key colums

    I have a set of fairly complex views (3 to 10 table joins). They perform horribly. I am going to create tables that match the views, and etl will load the table, so users have it easier and it will actually work!

    The problem is I need to determine PKs for these new tables. What I've been doing is try every combination I can find of the table columns to see what returns all the rows. So, first query is:
    Select count(*) from V_View1 -- 658803 (this one returned 658803 rows).

    then, I try combinations of 2 or more columns like this:
    Select count(*) from (SELECT Distinct ITM_NB from V_View1) a --430471Select count(*) from (SELECT Distinct ITM_NB, START_DT from V_View1) a --494612

    etc till I get a combination that gives me 658803 rows.

    There MUST be a better way! I have a table now that has 23 columns! While some are obviously not going to work, at least 18 are good candidates!

    Any ideas?

    Steve

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In my opinion, you should not be determining the "primary key" for your table (any table for that matter) based on the current set of data in the table. PK must be viewed as a business key and NOT a technical one.

    What if you find a unique combination of columns based on today's data, but later as the data changes the column combination is required to have duplicates ?

    If you wish to find it purely from a db perspective only, by carefully examining your view definition, you can identify the unique key.

    Don't be surprised if you are unable to find a unique key at all. Depends on how and why the view is designed. These are summary tables that you are creating to match the existing rules. So, don't be too concerned about creating key definitions.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Sathyaram is right - in most cases it is business decision. Though in my prev. company we used PKs for import with INSERT_UPDATE option (you need PKs for it).

    Do not forget that creating PKs will create indexes and it will take additional storage.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Aug 2004
    Posts
    4
    I'm aware of how this should be done. After 20 years in this business, mostly working with customers to design things, I can do that. Doing this, finding primary keys for these tables, is new to me, I've not done this before. Hence the question. And I do not have an option of not doing this. It has to be done.

    On the current table I'm up to 10 key members in my queries, and still not returning all rows. Just looking for a shortcut.

    And I've looked over the join sql. 5 tables, 3 of them used several times. But that still doesn't give me the key.

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by SPThomas View Post
    I'm aware of how this should be done. After 20 years in this business, mostly working with customers to design things, I can do that. Doing this, finding primary keys for these tables, is new to me, I've not done this before. Hence the question. And I do not have an option of not doing this. It has to be done.

    On the current table I'm up to 10 key members in my queries, and still not returning all rows. Just looking for a shortcut.

    And I've looked over the join sql. 5 tables, 3 of them used several times. But that still doesn't give me the key.
    it still does not explain why you need primary keys. looks like you are doing implementation before finalizing requirements. bad PM's and architect's work.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Aug 2004
    Posts
    4
    Ok, never mind. My question was HOW can I do this, and everyone wants to address WHY I'm doing this, which is not in my control.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by SPThomas View Post
    Ok, never mind. My question was HOW can I do this, and everyone wants to address WHY I'm doing this, which is not in my control.
    Quote Originally Posted by other smart people
    Describe the goal, not the step

    If you are trying to find out how to do something (as opposed to reporting a bug), begin by describing the goal. Only then describe the particular step towards it that you are blocked on.

    Often, people who need technical help have a high-level goal in mind and get stuck on what they think is one particular path towards the goal. They come for help with the step, but don't realize that the path is wrong. It can take substantial effort to get past this.
    and more: How To Ask Questions The Smart Way

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    it seems that you are doing it right trying select count(*) with distinct. what if all cols have to be PKs?
    and why PKs? you can just create a unique index.
    you might need to normalize your new table so will have to create more than one tables.
    and do all tables need to have PKs? may be this table can live without them.

    just throwing ideas. it is not a busy day in the office today
    Last edited by MarkhamDBA; 02-10-12 at 15:27.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    you might need to normalize your new table so will have to create more than one tables.
    The OP is building a physical table instead of a view to improve performance. I guess this suggestion defeats the purpose. ;-)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by sathyaram_s View Post
    The OP is building a physical table instead of a view to improve performance. I guess this suggestion defeats the purpose. ;-)
    you are right.
    and we still do not know the purpose of creating PKs on this table.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by MarkhamDBA View Post
    it is not a busy day in the office today
    Lucky guy.


    Quote Originally Posted by MarkhamDBA View Post
    __________________
    DB2 v9.5 ESE on AIX v6.1
    I thought you switched to DB2 v10 on zOS

  12. #12
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by db2girl View Post
    Lucky guy.
    I thought you switched to DB2 v10 on zOS
    I am on db2 zOS but can not change my signature here for some reason.

    actually they asked me to move to db2 LUW. do you think it is better for my future? i know once i am there i will not have slow days and will have to work a lot at night and on the weekends but it is more dynamic.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Not sure... What do you like more or feel more comfortable with? Can you do both? Does your company pay overtime? You had some valid reasons to switch to mainframe.

    I have nothing bad to say about db2 on mainframe except for the "green screen" (although I used a very similar "green screen" to handle pmrs and didn't like the "user-friendly" Windows version of it). I like DB2 on AIX and Linux (hate DB2 on Windows). I think I would not want to spend more than 50% of my time with DB2 on mainframe, but you can't always choose what you like...

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by MarkhamDBA View Post
    I am on db2 zOS but can not change my signature here for some reason.
    .
    http://www.dbforums.com/profile.php?do=editsignature

    else PM me.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  15. #15
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by db2girl View Post
    Not sure... What do you like more or feel more comfortable with? Can you do both? Does your company pay overtime? You had some valid reasons to switch to mainframe.

    I have nothing bad to say about db2 on mainframe except for the "green screen" (although I used a very similar "green screen" to handle pmrs and didn't like the "user-friendly" Windows version of it). I like DB2 on AIX and Linux (hate DB2 on Windows). I think I would not want to spend more than 50% of my time with DB2 on mainframe, but you can't always choose what you like...
    it's funny. "green screen". you can actually change it to any color you want incl. same color as unix session.

    well, in my case they actually asked me and i said i would go to unix but 100% only and it has to be permanent. my management would be very happy if i worked on both unix and zOS but i do not feel like it is possible for me. those two are too different.

    yes, they pay overtime. sometimes you don't want it though. you just want to be left alone in the evenings and on the weekend

    i did not work with db2 on Windows but i am pretty sure i will not like it.
    all environments on windows are very unstable.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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