DB2 IDENTITY Column
1) We have 2 types of IDENTITY in DB2 i.e. GENERATED ALWAYS AS IDENTITY(does not allow INSERT/UPDATE) and GENERATED ALWAYS BY DEFAULT(Allows). I am using the former as:
CREATE TABLE t1(c1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1), c2 CHAR(1) NOT NULL);
Now, when I insert values for c2 & let us say I get 10 records. They come in sequence 1 - 10 which is fine. Now I delete 6-10 and INSERT 5 more and I get 1-5 and 11-15 which is also fine.
But if I try a failed insert like 5.5 for c2 5 times and then insert a correct value, I land up inserting 21. Is this not ridiculous. How to avoid this from happening?
Also, when I generate DDL using db2look for some tables IDENTITY is shown to RESTART with 40. From where could this have come?
Both these problems have dumbfounded me into a nitwit. Please help.
CREATE TABLE t1(c1 SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1), c2 CHAR(1) NOT NULL);
Now, when I insert values for c2 & let us say I get 10 records. They come in sequence 1 - 10 which is fine. Now I delete 6-10 and INSERT 5 more and I get 1-5 and 11-15 which is also fine.
But if I try a failed insert like 5.5 for c2 5 times and then insert a correct value, I land up inserting 21. Is this not ridiculous. How to avoid this from happening?
Also, when I generate DDL using db2look for some tables IDENTITY is shown to RESTART with 40. From where could this have come?
Both these problems have dumbfounded me into a nitwit. Please help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot, rrjegan17. For both you and momi, I wanted to increase points but unable to do so and I have raised a request. 3 last questions:
>> Also, if I do not have any IDENTITY, we will have to generate the PK and when we INSERT new record and lots of records are already present, then will it not cost a lot since it will scan the PK column before allowing to INSERT or throwing unique violation error which will not be the case if IDENTITY is there?
Partially answered above.
>> Can IDENTITY column start with 0? This is so since for optional relation, in FK, I want to keep NOT NULL DEFAULT 0 instead of NULL to make index on it get picked. How true is this?
Again partially answered above. Please suggest on index point of view.
>> Can I reserve let us say 1 for optional relationship which we will never delete and keep FKCol NOT NULL DEFAULT 1 since START WITH 0 is not allowed? Basically how does one handle if one does not want NULL in FKColumn while still sticking to optional relationship?
>> Also, if I do not have any IDENTITY, we will have to generate the PK and when we INSERT new record and lots of records are already present, then will it not cost a lot since it will scan the PK column before allowing to INSERT or throwing unique violation error which will not be the case if IDENTITY is there?
Partially answered above.
>> Can IDENTITY column start with 0? This is so since for optional relation, in FK, I want to keep NOT NULL DEFAULT 0 instead of NULL to make index on it get picked. How true is this?
Again partially answered above. Please suggest on index point of view.
>> Can I reserve let us say 1 for optional relationship which we will never delete and keep FKCol NOT NULL DEFAULT 1 since START WITH 0 is not allowed? Basically how does one handle if one does not want NULL in FKColumn while still sticking to optional relationship?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks all and specially rregan17 => tremendous answers with lot of patience. Thanks a lot. momi and tliotta are my long time gurus. I myself am an expert in our beloved EE but do need lot of catching up. I raised request for increase in points. If this is not possible I will raise points for expert if that is valid in separate post and put the link here.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, momi. Your post throws more light. Why I raised about avoiding nulls is that you need to search with IS NULL or IS NOT NULL. This is type 3 predicate and does not pick an index. Instead search using =1 or != 1 makes it type 2 predicate which picks the index. Is this correct?
About joins you are correct since it is going to scan the entire relational PK = FK join columns of a join across tables for business requirement.
About joins you are correct since it is going to scan the entire relational PK = FK join columns of a join across tables for business requirement.
ASKER
But I guess one will not search across foreign keys if they are not business/functional ones. One will also not search across remarks/comments/descripti
One will search across business date, timestamp, integers and strings name/adjective/flag/boolea
One will search across business date, timestamp, integers and strings name/adjective/flag/boolea
ASKER
My above post after 1st sentence I feel is little out of context. Please excuse me. It is optional for you to answer. Also please suggest how do I increase the points of this question to award you more that you deserve. I raised a request but no reply till now.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all of you. That's a whole load of information. Now I really feel I am getting back richly for what little I am giving.
ASKER
Highly satisfied and gratified. Thanks a ton.
ASKER
Will IDENTITY column used to generate PK and FK derived from it give any problem while relating tables with a join.
Also, how does DB2 generate this value incrementally even while there are errors occurring and INSERT not taking place.
Can IDENTITY column start with 0? This is so since for optional relation, in FK, I want to keep NOT NULL DEFAULT 0 instead of NULL to make index on it get picked. How true is this?
Which is better to have GENERATED ALWAYS AS IDENTITY/BY DEFAULT?
Also, if I do not have any IDENTITY, we will have to generate the PK and when we INSERT new record and lots of records are already present, then will it not cost a lot since it will scan the PK column before allowing to INSERT or throwing unique violation error?
Also, since PK is not updated whether GENERATED ALWAYS AS IDENTITY or not, will it not be wise to make IDENTITY PK column as a clustered index?
I am asking some additional questions (some related some unrelated) but I am increasing the points since I would not like to make it 2 discontinuous questions. Thanks.