DB2 Generated by Default

Hi,
   I am aware that using GENERATED BY DEFAULT option for Identity Columns, DB2 provides me the ability to insert ID(mentioned in the sample code below) of my choice and If I don't provide ID then ID would be created by Server. But when I specify a value myself, Server doesn't caches that value and throws violation since I have those values inserted myself earlier and a Primary Key constraint defined. If I dont have Primary Key defined then it inserts duplicate values.

I need a solution such that I need to insert my own values as well as from Server if I dont specify value.
Is there any other way to overcome this situation ?

Kindly execute the sample code below in order to reproduce my problem.
Thanks in advance for your time and support.
create table test ( 
   id integer primary key generated by default AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE),
   col varchar(10)
);
 
insert into test (id, col) values ( 1, 'ab');
insert into test (id, col) values ( 2, 'cd');
insert into test (id, col) values ( 3, 'ef');
insert into test (col) values ( 'bc');
insert into test (col) values ( 'de');
insert into test (col) values ( 'fg');

Open in new window

LVL 57
Raja Jegan RSQL Server DBA & ArchitectAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectAuthor Commented:
To resolve out this issue, I am going to force developers not to have INSERT statements specifying Identity values.
Then Identity values will be generated by Server and wont create this scenario at all.
0
 
8080_DiverConnect With a Mentor Commented:
Apparently you are not thoroughly understanding the nature of Identity columns that are generated by the DBMS.  Essentially, you have two choices regarding an identifying column for your table: you provide it or the DBMS generates it.  There is no third, hybrid option where you supply it some of the time and the DBMS generates it some of the time.
Why is this so?  Because the DBMS has a mechanism that generates the next ID without having to figure out what the maximum value is for the ID.  Now, if you go sticking values into that column, then there is no constraint that makes you use the next Nvalues for the N reords you insert . . . you might be as likely to stick in 100001 thrugh 100000 + N when the next sequential ID is 100.  Then the DBMS is going to rock merrily along sticking in 100, 101, etc., until it hits 100001 and then it is going to through an exception.
You may want to create an Identity (1-1) column and then another column that you stick values in and then, in your selects, you can COALESCE your column and then the ID column and get the "hybrid-ID" value.  However, you should be aware that this Hybrid-ID value has no guarantee whatsoever of being unique because you can stick anything you want into your Column.
I have to ask, though, why do you want to sometimes provide the ID and sometimes let it be generated by the DBMS?  Under what circumstances would you sometimes have the ID value to put in there and sometimes not?
The only thing I can think of is if you are importing something along the lones of an Order with Order Deatails and the Order already has an ID assigned and the Order Detail records use that as an FK to the Order record.  If this is the case, then you need to either import the data into staging tables and then insert the rows into the appropriate final tables in a manner that maintains their PK-FK relationship based upon the new, DBMS genereated ID or you need to rethink the design of the process.
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi rrjegan17,

You've listed both DB2 and Sql Server zones for this question.  Just to be clear, DB2 and SQL Server treat identity columns a bit differently.  Actually, there is no consistency within the industry as the MySQL auto_increment type behaves differently than either of the IDENTITY types, and Oracle doesn't have an IDENTITY type but does allow you to mimic the functionality with a trigger and sequence.

In the DB2 world, an IDENTITY column is treated as the primary key.  DB2 allows you the option of GENERATED ALWAYS or GENERATED BY DEFAULT, but both enforce uniqueness.  DB2 will not allow you to insert a row into a table with an IDENTITY column if the insertion will cause a uniqueness violation of the IDENTITY value.

8080 has a good idea in that if you might be in a situation where you want to insert a value that might violate the rules of the unique IDENTITY value, you might consider storing the value in a non-unique column and allowing the DBMS to generate its own IDENTITY value.

On the other hand, if you need to replace or update a row when the new data has an IDENTITY value that violates uniqueness, you can handle this quite easily with a MERGE statement.

MERGE INTO mytable t0
(
  select * from mytable t1
) t1
ON t1.id = t1.id
WHEN NOT MATCHED
  THEN INSERT ...
WHEN MATCHED
  UPDATE SET ...


Good Luck,
Kent
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Raja Jegan RSQL Server DBA & ArchitectAuthor Commented:
8080_Diver and kent,
     Zone SQL Server 2005 is added by mistake and since I am not able to remove it myself have requested attention to moderators to remove it out. Hence I am dealing only with DB2.

Ok. Let me give my requirement in more detail. In DB2, we have Identity Values generated always and Generated by Default. Generated Always creates the values for that particular column and doesn't allows users to insert any values for that particular column. And In my requirement I need to give values to that column both explicitly and if I dont provide then Server needs to insert value for that column and hence I used Generated by Default instead of Generated always.

kindly execute the sample code provided and try it out.

In the First Three Inserts I am specifying the ID Column explicitly.
Hence if I execute the fourth Insert, I will obtain an error 3 times and later on it will be inserted when we try it for the fourth time.

Server is not incrementing the values I provide any Inserts with my own value and hence this is an issue with one of my requirement.

P.S. Issuing an alter table statement to restart the identity key will work and I don't prefer that option because I wont be able to track each and every time whenever a developer inserts a value explicitly.

Kindly help me out on this
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi rrjegan17,

The issue has several parts.

An IDENTITY is defined as a column, but internally its association is more closely with the table. A table can have only 1 IDENTITY column.

When an explicit value is stored into an IDENTITY (generated by default) field, DB2 does not change the values associated with the IDENTITY. It does not increment the NEXT value. Much to my surprise, on DB2/LUW Version 9, it does not check to ensure uniqueness. Earlier versions of DB2 did. (This may be a configurable setting where the default is different than it used to be. I'll see if I can find an answer on this.)

Uniqueness is guaranteed by applying a unique index on the column. Declaring the IDENTITY column a primary key automatically creates the unique index.

So when an IDENTITY value is filled by DB2, it simply selects the identity's NEXT value. If a row already exists with that same value, you'll get duplicated data or an INSERT error depending on the existence of a unique index.

Your application doesn't seem particularly well suited to using an IDENTITY column in the manner you want. There isn't an easy workaround, but there are options.

One solution is to add another column that is a true IDENTITY (generated always) column. You are then free to insert any value that you want in the current column.

Another is to use a sequence/trigger combination on the table. When a row is stored, let the trigger manage the column. You can make is smart enough to "find" a legal value for the column before the data is recorded. The trigger can also help you to identify when explicit values are stored into the column.


Kent



0
 
Raja Jegan RSQL Server DBA & ArchitectAuthor Commented:
<< One solution is to add another column that is a true IDENTITY (generated always) column. You are then free to insert any value that you want in the current column. >>

Thanks Kent.. In other databases like SQL Server and Sybase, if we manually insert any values into an Identity column then it would automatically update the Identity values and take care in the next insertion. That's why wondering why it is not working out in DB2.

Kindly reply me if you have any other ideas to achieve this workaround.
0
 
Raja Jegan RSQL Server DBA & ArchitectAuthor Commented:
Kent,
   Any other ideas so that I can close this question accordingly.
0
 
8080_DiverConnect With a Mentor Commented:
rrjegan17,
>In other databases like SQL Server and Sybase, if we manually insert any values
>into an Identity column then it would automatically update the Identity values and
>take care in the next insertion. That's why wondering why it is not working out in DB2.
Actually, if you try to manually insert values into an Identity column in SQL Server, it throws an exception unless you first execute a command to specifically allow insertion of values into the Identity column.  However, having executed that command, you may well introduce duplicates into your identity column that will cause you to NOT be able to get the automatic generation of Identity values to operate correctly and which may well cause you considerably more grief because you won't be able to delete either of the 2 rows with the same Identity value.
If you feel compelled to know when a value has been inserted into the Identity column, perhaps you should set up a stored procedure that accepts the various values to be inserted and that will let the Identity column's value be NULL.  Then, if the Identity column's value is not null, your SP could record pertinent information to a logging table/file before taking the steps necessary to allow the insertion of the Identity value and then Issuing an alter table statement to restart the identity key.
I still do not understand why it is that you feel that you have to insert the Identity value on some occassions and not on others.  If you are dealing with Master/Detail tables and wanting to make sure that you have the Detail rows linked to the Master row, that can be done in ways that conform to the normal and expected use of the Identity column.  The manner in which you are trying to use the Identity column is neither normal nor expected by the DBMS designers.

0
 
Raja Jegan RSQL Server DBA & ArchitectAuthor Commented:
8080_Diver,
     In SQL Server / Sybase, if we insert any identity values manually by specifying identity_insert on, then SQL Server / Sybase recognizes the values inserted and when I insert records for the next time it will continue after the incremented values. I have tested this one several times in both SQL Server and Sybase.

In Oracle, you don't have this issue as it will be using Sequences and no concept of Identity at all.

But this is not happening in DB2 and that is my concern.

Say if I have some tables that needs to be inserted with some default values along with values inserted from the application later on from Application. Hence we have the columns as Identity BY Default columns and we have nearly 400 tables with us and its hard to track each and every user inserting records into those list of tables which has the scenario mentioned above.

Hope this makes sense.
0
 
8080_DiverConnect With a Mentor Commented:
rrjegan17
if we insert any identity values manually by specifying identity_insert on
Are you saying that you leave identity_insert on?!?!?!?!
Say if I have some tables that needs to be inserted with some default values along with values inserted from the application later on from Application.
If this is in the initial set up of the application, then you might try one of the two techniques I use.  The first (and easiest) is to have the script that sets up the tables initially insert the default set of rows in the correct order, thus getting them the same ID in every instance.  The second is to approach it in a similar fashion but using a secondary, set up table that holds the initial set of rows with the proper Identity values and other column values and, then, simply do an INSERT INTO newtable {column list} SELECT {column list} FROM SetUpTable ORDER BY ID;
we have nearly 400 tables with us and its hard to track each and every user inserting records into those list of tables which has the scenario mentioned above.
Are you saying that you create the table, with some default rows, for each user and that you are creating about 400 tables?  At that point, having a SetUpTable might be the fastest way to get them set up.  However, the question arises, after the table is intially set up, does a user really need to be able to supply the Identity value on some occassions and not on others or is this need only at the point where the user's tables are being initially set up?  If it is the latter, then I would definitely recommend having the SetUp table(s) with the initial set of rows and building the other tables from there.  If it is the former, then I would still recommend having a stored procedure that would be used to insert the data so that you only have to code the Identity column manipulations into the SP and from then on the process is handled automaticcally.
Now, if the issue is that doing the manipulations that let you reset the next Identity value are causing performance issues, then you may need to reconsider the manner in which you are trying to use the database.  Even in the case of what you were doing with SQL Server and Sybase, you were seriously bending the normal rules of Identity column usage.  There is a reason that you are finding this hard to do and that reason is that this is really not a good idea as an on-going practice and should only be considered at the point of intially setting up a table, if then.
 
0
 
Raja Jegan RSQL Server DBA & ArchitectAuthor Commented:
<< Are you saying that you leave identity_insert on?!?!?!?!  >>

No for Sure and missed mentioning it.

<< The first (and easiest) is to have the script that sets up the tables initially insert the default set of rows in the correct order, thus getting them the same ID in every instance. >>

Out of 400 tables we have nearly 90 tables that falls in this scenario
And by the way I am following this approach only. I should force the developers not to mention the Identity keys in INSERT scripts to get rid of this problem.

Hope this makes sense.
0
 
8080_DiverCommented:
Out of 400 tables we have nearly 90 tables that falls in this scenario
And by the way I am following this approach only.

If you are following that approach (i.e. having a template table that you start the other tables from), are you saying that, later on, you need to allow the users to insert Identity values?
I should force the developers not to mention the Identity keys in INSERT scripts to get rid of this problem.
What I would do is to set up some stored procedures that the developers could execute to set  up the tables.  You can have the SP set up more than one table, set up indexes, copy the initial data into the table, in a controlled manner, and even set up the permissions for the user or users.  That also lets you more easily deal with changes to the system that may require either more or fewer tables to be initialized.
0
 
Raja Jegan RSQL Server DBA & ArchitectAuthor Commented:
<< you need to allow the users to insert Identity values? >>

If I force developers not to mention Identity Keys while inserting initial set of records into those tables, then Server is generating the Identity value and hence if records are inserted into that table from application then server would continue using that Identity Keys and there is no need for me to Restart the Identity columns right. Hope this makes situation clear right
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi rrjegan17,

Sorry for being MIA the past couple of days, but 8080_Diver has filled in very nicely.  :)

It looks like you've got a mess.  An IDENTITY column (AKA auto_increment, and Oracle's emulation of the functionality) is intended for a very specific purpose -- to provide a very efficient, unique identifier for every row of data in the table.  The SQL Server extension for enforcing the identity value (identity_insert) allows for enforcement to be temporarily suspended so that data issues can be resolved.  DB2 doesn't allow temporary suspension of the rule, but it does allow the rule to be permanently (on a table basis) worked around with the use of GENERATED BY DEFAULT.  Still, that has its limitations.

If the developers are generating their own IDENTITY values, you have a design and/or management issue.  There are very few legitimate reasons for forcing an IDENTITY column to a specific value.  The only one that comes quickly to mind is a situation where data is being imported/migrated into the database and the key values for certain rows must contain specific values to be compatible with other data and/or applications.  Even that's a stretch, but I can understand an issue where 20 years of application development against a particular database saw those kinds of shortcuts make their way into production code.

Tell the developers that they MUST take the database default values for IDENTITY keys.  If they bicker, so what?  They are trying to move control issues from the DBMS to the application and over the long haul, that will always cause more problems than it solves.


Good Luck,
Kent
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.