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

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.

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.

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.

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.

and more: How To Ask Questions The Smart Way

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

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.

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.

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...

http://www.dbforums.com/profile.php?do=editsignature

else PM me.

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.