Solved

creat a table in db2 control center

Posted on 2006-11-21
28
852 Views
Last Modified: 2008-02-01
hello ,

i created a table in db2 control center. one field is called userID and it's a integer data type. userID is a primary key. is that possible to set this field automatically increased by 1?

what i want to do is in a jsp page, there is an interface for adding user. after click the submit button, the user's info should be added into the database, the id field value should be increased by 1

in mysql, this function is available.

thanks very much for your reply,

lilyyan
0
Comment
Question by:lilyyan
  • 17
  • 7
  • 3
  • +1
28 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 17990207
Hi lilyyan,

How about recreating the table?  (It's easier than altering it.)  Extract the SQL/DDL for the table.  Then replace the definition of the userID field with this:

  userID    integer not null generated by default as identity,

or

  userID    integer not null generated always as identity,

That will define userID as an integer column that must by unique within the table.  Using the first line above, you can insert your own value for userID or accept the system generated value.  Using the second line DB2 will always generate the userID value.


Good Luck,
Kent
0
 

Author Comment

by:lilyyan
ID: 17990933
Hi Kent,

Thanks for your suggestion. i got a question to ask for:

if the database located in my local pc, it's easy to alter the userID field throught db2cc

but if the database located in a remote db server, is there a way to alter the userID field? the table has been created with around 100 records. it's not easy to recreat it

lilyyan





0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17993666
hi lilyyan
 
first of all, when you work with the control center it does not matter where the table resides
all you have to do is to catalog the remote db on your local pc and you can  alter it as if it was a local table
second
if you want to drop create your table. all you have to do is
create a new table like the existing one
insert all the rows from your table to the new one
recreate the original table with the identity column generated by defualt
insert your data back into the original table
thats it
0
 
LVL 45

Expert Comment

by:Kdo
ID: 17995058

Hi lilyyan,

Momi's right -- DB2 isn't as "friendly" about on-the-fly changes.  Remember though, that DB2 was engineered to handle large databases in the terrabyte range.  The "laws of unintended consequences" tell us that making a small change on a database that large can result in huge issues if DB2 were to try and automatically move and refactor data.

Following Momi's suggestion, you can do it yourself in just a few lines of SQL.

create table new_table as (select * from old_table) definition only;
insert into new_table select * from old_table;
drop table old_table;
create table old_table
(
  userID    integer not null generated by default as identity,
-- rest of table definition
);
insert into old_table .....
drop table new_table;

Good Luck,
Kent
0
 

Author Comment

by:lilyyan
ID: 17995502
Hi,

Thanks for all the replies.

to Momi: it should be seems that no matter the database located on local pc or remote pc, for using db2cc, itshould be seem.
 
but yesterday, when i created a table in db2cc in my local pc, and tried to change one column, i clearly rember that "value generation" is clickable and modifiable. today, i tried again, the "value generation" is not clickable anymore. i don't know if i missed some steps.

to Kent: i might receate the table, just confirm that complete
0
 

Author Comment

by:lilyyan
ID: 17995561
SQL code should be: (looks like i accidently clicked the sumbit button, sorry)
---------------------------------------------------------------------------------------
create table new_table as (select * from old_table) definition only;
insert into new_table select * from old_table;
drop table old_table;
create table old_table
(
  userID    integer not null generated by default as identity,
-- rest of table definition
);
insert into old_table  select * from new_table;
drop table new_table;

---------------------------------------------------------------------------------------

another question is:

is that ok,  if i just rename the old table (name OldTableOne) from db2cc, for example : newTableOne

then execute the following SQL code:


----------------------------------------------

create table OldTableOne
(
  userID    integer not null generated by default as identity,
-- rest of table definition
);
insert into OldTableOne  select * from newTableOne;
drop table newTableOne;

------------------------------------------------------


0
 

Author Comment

by:lilyyan
ID: 17995708
also the userID (integer)field is a primary key of the table. In a jsp file, there is a add member function, if i don't alter the userID to something like: "integer not null generated always as identity" , is there a way that the userID value can be increased uniquely in the program itself?

it seesm not too easy, because i need get all the values of userID, then make sure the new added userID value is unique
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 17995877
you should try and explain a little more clearly what exactly you want to do.
if you want a guaranteed unique value, then Kent's and Momi's solution will work.
But now you seem to be saying that you want a guaranteed unique value, but you want to query the table first to see what the last unique value is, and then you want to increment that value by one. If this is what you want to do, then you need to use a sequence, but sequence functions also have their problems, especially if you want to guarantee that the unique number is consecutive (no gaps in the numbering).
So, another approach you could take is to get the max value of the id number from the table, increment it by one, then insert the record. If you use this approach,make sure you lock the row in exclusive mode when you read the max value, like
select max(id) into (host variable) from table for update of id then
insert .....
commit when sqlcode=0 otherwise rollback

0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 100 total points
ID: 17996034
Hi lilyyan,

An identity column becomes the functional primary key of the table.  When you think about database design a bit this makes a lot of sense -- the column guarantees uniqueness (as does a primary key) and because it's an integer virtually guarantees the smallest index space and fastest index access/lookup.
Any time that a query filters on an identity column DB2 will filter on the identity column before filtering on other columns.

I thought about the SQL that I provided earlier and the process can be made slightly easier when the identity value is 'generated by default'.

create table new_table as (select * from old_table) definition only;
insert into new_table select * from old_table;
drop table old_table;
create table old_table
(
  userID    integer not null generated by default as identity,
-- rest of table definition
);
insert into old_table select row_number() over (), * from new_table;
drop table new_table;


Kent
0
 

Author Comment

by:lilyyan
ID: 17996057
hello,

thanks for your reply. basically i want to use an easy and reliable way to make the userID field incremented by one either from program itself or from the database itself.

based on your suggestion, i think from database itself to modify userID field should be an easier way.

another comments i  found in db2cc is: when i alter a table, sometimes the colums is modifiable, namely the datatype or value generation of a column can be changed "on the fly". sometimes not. i don't know if i missed some steps when i define a table in db2cc.

0
 

Author Comment

by:lilyyan
ID: 17996078
one thing to add: fro example:  there is one drop down list shown up for datatype
0
 

Author Comment

by:lilyyan
ID: 17996275
Hi Kent,

Thanks for your reply. got a few question about the sql code:

--------------------------------
create table new_table as (select * from old_table) definition only;
insert into new_table select * from old_table;
drop table old_table;
create table old_table
(
  userID    integer not null generated by default as identity, ----? please refer to q1
-- rest of table definition
);
insert into old_table select row_number() over (), * from new_table; -----? please refer to q2
drop table new_table;

------------------------------------------------------------------------------

q1.why would "generated by default as identity" is easier than "generated always as identity"


q2. can this line be: insert into old_table select * from new_table;  

insert into old_table select row_number() over (), * from new_table;  
would you please explain: what does row_number() over (), * mean?

Thanks for your reply,

lilyyan

0
 

Author Comment

by:lilyyan
ID: 17998088
hi,

as you know, i just created a new table (there is no records yet) in my another posting . when i open this table in db2cc, the userID column is editable, i.e. the value generation can be modified to "generated always as identity or generated by default as identity", orignally its value will generated by input an integer.

so sometimes in db2cc, a column in a table is changeable
0
 

Author Comment

by:lilyyan
ID: 17998540
hi Kent,

after run the command:insert into old_table select row_number() over (), * from new_table;  

the result is:
--------------------------------------------
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "*" was found following "ow_number() over (),".
Expected tokens may include:  "<select_sublist>".  SQLSTATE=42601


-------------------
then i run the command: insert into old_table select * from new_table;  

the result is ok
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:ghp7000
ID: 17998606
change
after run the command:insert into old_table select row_number() over (), * from new_table;  
to
after run the command:insert into old_table select row_number() over () * from new_table;  
notice that I took out the comma after over()

regarding the control center and changing datatypes:
only char and varchar datatypes can be changed by control center, any other datatype is not changeable


0
 

Author Comment

by:lilyyan
ID: 17998837
hi,

thanks for your reply.

could you please tell the difference between:

insert into old_table select row_number() over () * from new_table;  

and

insert into old_table select * from new_table;  

aslo userID is an integer dadatype in the new created table. and i found it's changeable
0
 

Author Comment

by:lilyyan
ID: 17998984
namely, the purpose to use: row_number() over ()
0
 

Author Comment

by:lilyyan
ID: 17999148
hi momi_sabag

still i found when you open a database located in a remote pc is different from the local database.

when i alter a table from a local db, all columns are changeable and removable

if it's a remote db, all columns are not changeable and not removable
0
 
LVL 13

Assisted Solution

by:ghp7000
ghp7000 earned 400 total points
ID: 17999547
row_number() over is an OLAP function, what it does it assigns a unique number to each row as 1,2,3,4,5....., so when insertiing records from old table into new table thenew table will ahve the id numbers assigned 'on the fly'.
if you have id numbers already assigned in the old table, the row number function will overwrite the existing id numbers,
therefore,
if you have id numbers already assigned in old table and want to keep them in new table use:
insert into old_table select * from new_table;  

if you have id numbers already assigned in old table and want to overwrite them with sequentially numbered new values use:
insert into old_table select row_number() over () * from new_table;  

if you have no id numbers in old table use:
insert into old_table select row_number() over () * from new_table;  
0
 

Author Comment

by:lilyyan
ID: 18012584
hello ghp7000,

thanks for your reply about the row_number() over () *.

q1. would you please explain what's the purpose/benefits to use row_number() over () *, is this very necessary?

q2. when you say the row_number assign an id number to each row, this value is nothing to do with the userID field of the table in my question, right?

q3.
>if you have no id numbers in old table use:
>insert into old_table select row_number() over () * from new_table;

i'm not sure if there is id number in the old table. how to check this?

i'm new to db2, sorry for these questions,

thanks so much for your reply,

lilyyan


0
 

Author Comment

by:lilyyan
ID: 18012894
hi ghp7000 ,

as you suggested, i executed the commdand as following :

db2 =>  insert into TESTMEMBERDBSCHEMA.TESTMEMBERKEYWPORD select row_number() over () * from TESTMEMBERDBSCHEMA.newmembertable;

the result is:
====================================================
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "." was found following "from TEXSTMEMBERDBSCHEMA".
Expected tokens may include:  "FROM".  SQLSTATE=42601
===================================================

then i add the comma between over () and *, i.e. over () , *  stil there is error.

then i executed:

db2 => insert into TESTMEMBERDBSCHEMA.TESTMEMBERKEYWPORD select * from TESTMEMBERDBSCHEMA.newmembertable;

this time, the result is ok.

thanks so much for replying my previous two postings,

lilyyan
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 400 total points
ID: 18021702
yes, sorry, I ddnt see that typo (over () , * )  the correct syntax is
insert into old_table select row_number() over ()  from new_table;  

regarding your question 1
would you please explain what's the purpose/benefits to use row_number() over () *, is this very necessary?
the answer is:
it depends on what you want to do. If you want to insert the rows from old table into new table and at the same time you want o assign each inserted row a unique id, then the row_number() over() function will do this for you. So, it isn't necessary, it is not a requirement, it is a method to acheive a pre-determined objective. Note that the row_number() over() function as demonstrated in this post is the most basic form of its use, it can be extended to do partitioning and ordering as well.
q2. when you say the row_number assign an id number to each row, this value is nothing to do with the userID field of the table in my question, right?
As I said perviously, if you have a value already assigned to the column id, and if you use the row_number() over() function in your insert statement, then the old id numbers are overwritten by the new row number values.
q3. I'm not sure if there is id number in the old table. how to check this?
This is quite simple and perhaps you have not phrased your question properly. To see any value in any table for which you have been granted the select privlidge, do
select id from <table name>
this will return each value for each row for the column id for the table name specified
or
if you know your table has thousands of rows and you dont want to see the lengthy output, you can just test to see if some rows have a value in the id column:
select max(id), min(id) from <table_name>
this will show the maximum and minimum value for the column id
or
let's say you know your table has duplicate values for the id column and you want to know which numbers are duplicated
select id, count(*) from <table_name) group by id having count(*) > 1
this will return the id numbers that are duplicated as well as the number of times they are duplicated (count column) for the column id of the specified tabel name, as long as the id number is duplicated more than once. If you want to know which id numbers are duplicated more than 10 times, then do
select id, count(*) from <table_name) group by id having count(*) > 10
0
 

Author Comment

by:lilyyan
ID: 18028471
Hi ghp7000,

Appreciate so much for your replies. very helpful :D

only one question:

now i knew this function row_number() over () will assisgn an unique id to each row.

one trick here is: in my table, there is a field called userID and it's a primary key.

in my question 3, i want to know how to check if my old table has an unique "id" which is assigned by row_number() over () in each row,
this "id" is nothing to do with the "userID" colmun, i believe your reply is about  how to select "userID", right?

then how to check if my old table has an unique id to each row, which is assigned by row_number() over () .

lilyyan

0
 
LVL 13

Expert Comment

by:ghp7000
ID: 18031660
hmmm, Im not sure I understand your question, but I will try and answer
>>how to check if my old table has an unique id to each row, which is assigned by row_number() over () .
the row_number() over() function can never assign the same row number to a row when it used in way we have discussed.
It simply assigns a unique number to each row in the table.
If you use:
row_number() over(partition by <column name>, <column name>), then the function can assign duplicate numbers to different rows if the column names to be partitioned are part of a composite primary key and one of the column values appears more than once in the table. So, assume that your table named test has a composite primary key made up of ID and LAST_NAME and the table has the following rows:
LAST_NAME       ID
smith                 1
smith                 2
select last_name, id, row_number() over(partition by last_name, id) from test
result will be:
LAST_NAME  ID   3
smith             1  1
smith             2  1
here we see that ID and row_number (column 3) are the same
is this what you mean when you say you want to check if the row_number function will assign the same id value?
If your answer is yes, then there is still more work to be done.
Since you have closed out the question, I suggest you continue this discussion in another post, explaining in great detail what your objective is.
0
 

Author Comment

by:lilyyan
ID: 18037162
Hi ghp7000,

Thanks for the reply. :D

the example you posted here:
select last_name, id, row_number() over(partition by last_name, id) from test
result will be:
LAST_NAME  ID   3
smith             1  1
smith             2  1

as you mentioned,  the row_number can be same, but what is the benefit to use row_number() over(partition by last_name, id) in the above example?

if the table is something like:

Last Name  userID (PRIMERY KEY)

smith           1
smith           2
bill              3

in this case, how the row_mumber will be?

thanks for your reply,

regards,

lilyyan


0
 
LVL 13

Expert Comment

by:ghp7000
ID: 18037185
well, the best way to find out is to try it out yourself
0
 

Author Comment

by:lilyyan
ID: 18037845
but what is the benefit to use row_number() over(partition by last_name, id) in the above example if the row_number is same?
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 18044120
I dont know
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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…

707 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

18 Experts available now in Live!

Get 1:1 Help Now