Solved

DB2 Generated by Default

Posted on 2009-05-05
15
6,513 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Raja Jegan R
  • 7
  • 4
  • 3
15 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 300 total points
ID: 24310540
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
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 200 total points
ID: 24310622
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
 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24320500
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
 
LVL 45

Expert Comment

by:Kdo
ID: 24325317
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
 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24346773
<< 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
 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24427974
Kent,
   Any other ideas so that I can close this question accordingly.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 300 total points
ID: 24432415
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24432644
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
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 300 total points
ID: 24433822
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
 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24437820
<< 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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24441345
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
 
LVL 57

Author Comment

by:Raja Jegan R
ID: 24441505
<< 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
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 200 total points
ID: 24444042
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
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 0 total points
ID: 24449146
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now