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.
LVL 17
k_murli_krishnaAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Will IDENTITY column used to generate PK and FK derived from it give any problem while relating tables with a join.

No issues with PK and FK values out of this.

>> Also, how does DB2 generate this value incrementally even while there are errors occurring and INSERT not taking place.

When you try to insert any record to the table having Identity values, it would check for the Maximum value last generated and then increment with the increment value. Once generated it would be marked as an used value ( Internally). If that insert fails, then it would not be available in your table, but that value is marked internally as used.
Best example is in your question itself.

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

No, Identity values can't start with 0 and it should be NOT NULL.
And advised to use Identity columns in tables where it would be a primary key and hence in the Referenced child table it can be either NULL or NOT NULL column.

>> Which is better to have GENERATED ALWAYS AS IDENTITY/BY DEFAULT?  

In SQL Server( hope I have seen your postings), you can insert explicit values into Identity columns.
If you want to do so, then use GENERATED BY DEFAULT.
GENERATED ALWAYS will not allow explicit values to be inserted into that column and use it based on your requirements.

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

If you don't have IDENTITY column, then you need to do some application logic to create unique records for that table, else it would through Unique violation error leading to loss of result sets.

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

Yes, you can create clustered index on that column for better performance and if it is involved in all WHERE clause conditions. else you can create Clustered index on some other columns for better performance.

Hope this clarifies.
0
 
momi_sabagCommented:
for your first question,
a db2 identity value that was consumed can't be returned (unless you alter the identity column), which means, that if your transactions roll back, you will have gaps. This is just the way it works, nothing to do with it
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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?

No, This is by design the default behavior of Identity columns to capture error values too.

>> Also, when I generate DDL using db2look for some tables IDENTITY is shown to RESTART with 40. From where could this have come?

You have received 40 since values till 39 would be present in that table.
And again this is also designed by default and we have nothing to do about it.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
k_murli_krishnaAuthor Commented:
Thanks momi and rrjegan17.

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.
0
 
k_murli_krishnaAuthor Commented:
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?

0
 
tliottaCommented:
> Is this not ridiculous.

Only if you really expect to have billions of rows in your tables. Otherwise it doesn't matter at all.

That is, it _shouldn't_ matter. An IDENTITY column identifier value should not have information encoded within its value. A series of sequential values should not be assumed by an application to have been created in that order. That would be an example of encoded information where ascending numbers imply order of creation.

An IDENTITY should never be assumed to be anything but a unique identifier. As such, it doesn't matter if they're in sequence, out of sequence, ascending, descending, random or patterned. All that matters is that one and only one row can be identified by an IDENTITY value.

Tom
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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?

Even if you have an Identity column, Primary key column would automatically check for unique violation before inserting records into the table. And hence Unique violation would be checked both the times even if you have Identity or application logic is used for creating unique columns.
But overhead would be less if you have Identity since records would be present in a sorted manner and Application logic may or may not be sequential.

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

No, Identity column can't start with 0.
If you have PK and FK defined on your tables, then it would be internally handled for better performance and hence no need to create Index on a FK column.( Point No 1).
If you have more no. of Null values in your FK column, then your database normalization or design needs improvement( Point No 2).
An Ideal design would not have NULL values in any of the columns.( You can't achieve it all the times but keep that in mind to obtain that)

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

As mentioned earlier, Index is not required on FK columns but create it if more operations are dependent on that column. If you have more Nulls, then try to reduce it to some extent and then create index on it( as required) which can work optimally well with columns having less Nulls.
0
 
k_murli_krishnaAuthor Commented:
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.
0
 
momi_sabagCommented:
just a couple of points:
1) regarding performance - uniqueness is always checked using an index. An index is always sorted tree, so in the worst case, the amount of logical io required for this check is the level of the index tree (which is probably 2-3 in most medium to large size tables) so no need to worry there.

2) I don't see any reason to create a special value other that null for the foreign key to represent records without a parent record. Any solution you will choose will be non-standard. There is no problem in terms of performance with null values, they are indexed as well.

3) it is always recommended to create indexes on the foreign keys since every time you perform an update or delete on the parent table, db2 has to check the foreign key in order to enforce the on delete rule
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> it is always recommended to create indexes on the foreign keys since every time you perform an update or delete on the parent table, db2 has to check the foreign key in order to enforce the on delete rule

Thanks momi for correcting that..
In SQL Server, this is not the case unless there is heavy updates and deletes happening on that Foreign key column.

And Thanks k_murli_krishna
0
 
k_murli_krishnaAuthor Commented:
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.
0
 
k_murli_krishnaAuthor Commented:
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/description/observation columns. Even if one does it will be a LIKE search which most of the times will not pick index.

One will search across business date, timestamp, integers and strings name/adjective/flag/boolean columns and even sometimes across decimal/float/double ones denoting price/amount. Please comment/clarify.
0
 
k_murli_krishnaAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Instead search using =1 or != 1 makes it type 2 predicate which picks the index. Is this correct?

Not correct, It entirely depends upon the volume and type of data present in your table.
If you have less cardinality ( Unique values) in a column, then index would not be used optimally.
In order to use Index in a optimal manner, try creating it in a column having unique values so that Index can traverse accordingly.
If you have more NULL values in that column, then those values would not be considered while that index is used and Table Scan would be done at that

>> 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/description/observation columns. Even if one does it will be a LIKE search which most of the times will not pick index.

If a particular query doesn't involves your Primary key, then there are chances that it might involve where clauses for Foreign key column and hence it entirely depends upon the way developers use the tables and columns.
And for the rest of the questions above, it is mostly correct but have exceptions in some set of cases. ( As far as I have experienced)

>> One will search across business date, timestamp, integers and strings name/adjective/flag/boolean columns and even sometimes across decimal/float/double ones denoting price/amount. Please comment/clarify.

Depends entirely upon the columns and the kind of data stored upon.
Hence it purely depends upon your application.

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

AFAIK, in EE you can't assign points more than 500 for a single question and it is already there.
Even Moderators would be responding to you the same answer ( I Hope).
0
 
k_murli_krishnaAuthor Commented:
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.
0
 
k_murli_krishnaAuthor Commented:
Highly satisfied and gratified. Thanks a ton.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.