How DB2(v10.5.0.5) add auto increment column to an exists table
I'm trying to add an auto increment column in an existing table of DB2.
DB2 version is v10.5.0.5.
Following is my query:
alter table DB2INST1.AAA_BJ_BOND
ADD COLUMN id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
but I got following error:
"com.ibm.db2.jcc.am.SqlSyntaxErrorException: ALTER TABLE "DB2INST1.AAA_BJ_BOND"
specified attributes for column "ID" that are not compatible with the existing
column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.13.127"
What can I do to solve this problem?
sql db2
edited Jan 2 at 9:58
rdj7
8291819
asked Jan 2 at 6:31
skylineskyline
84
add a comment |
I'm trying to add an auto increment column in an existing table of DB2.
DB2 version is v10.5.0.5.
Following is my query:
alter table DB2INST1.AAA_BJ_BOND
ADD COLUMN id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
but I got following error:
"com.ibm.db2.jcc.am.SqlSyntaxErrorException: ALTER TABLE "DB2INST1.AAA_BJ_BOND"
specified attributes for column "ID" that are not compatible with the existing
column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.13.127"
What can I do to solve this problem?
sql db2
edited Jan 2 at 9:58
rdj7
8291819
asked Jan 2 at 6:31
skylineskyline
84
I don't know about DB2 (it's might simply not allow adding a sequence to an existing table), but did you try to combine both ALTER?:alter table DB2INST1.AAA_BJ_BOND ADD COLUMN id INTEGER NOT NULL generated always as identity (start with 1);
– dnoeth
Jan 2 at 9:41
This will still lead to errors, but the solution is given below.
– skyline
Jan 9 at 3:01
add a comment |
I'm trying to add an auto increment column in an existing table of DB2.
DB2 version is v10.5.0.5.
Following is my query:
alter table DB2INST1.AAA_BJ_BOND
ADD COLUMN id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
but I got following error:
"com.ibm.db2.jcc.am.SqlSyntaxErrorException: ALTER TABLE "DB2INST1.AAA_BJ_BOND"
specified attributes for column "ID" that are not compatible with the existing
column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.13.127"
What can I do to solve this problem?
sql db2
edited Jan 2 at 9:58
rdj7
8291819
asked Jan 2 at 6:31
skylineskyline
84
I'm trying to add an auto increment column in an existing table of DB2.
DB2 version is v10.5.0.5.
Following is my query:
alter table DB2INST1.AAA_BJ_BOND
ADD COLUMN id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
but I got following error:
"com.ibm.db2.jcc.am.SqlSyntaxErrorException: ALTER TABLE "DB2INST1.AAA_BJ_BOND"
specified attributes for column "ID" that are not compatible with the existing
column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=4.13.127"
What can I do to solve this problem?
sql db2
sql db2
edited Jan 2 at 9:58
rdj7
8291819
asked Jan 2 at 6:31
skylineskyline
84
edited Jan 2 at 9:58
rdj7
8291819
asked Jan 2 at 6:31
skylineskyline
84
edited Jan 2 at 9:58
rdj7
8291819
edited Jan 2 at 9:58
rdj7
8291819
edited Jan 2 at 9:58
rdj7
8291819
8291819
asked Jan 2 at 6:31
skylineskyline
84
asked Jan 2 at 6:31
skylineskyline
84
asked Jan 2 at 6:31
skylineskyline
84
84
I don't know about DB2 (it's might simply not allow adding a sequence to an existing table), but did you try to combine both ALTER?:alter table DB2INST1.AAA_BJ_BOND ADD COLUMN id INTEGER NOT NULL generated always as identity (start with 1);
– dnoeth
Jan 2 at 9:41
This will still lead to errors, but the solution is given below.
– skyline
Jan 9 at 3:01
add a comment |
I don't know about DB2 (it's might simply not allow adding a sequence to an existing table), but did you try to combine both ALTER?:alter table DB2INST1.AAA_BJ_BOND ADD COLUMN id INTEGER NOT NULL generated always as identity (start with 1);
– dnoeth
Jan 2 at 9:41
This will still lead to errors, but the solution is given below.
– skyline
Jan 9 at 3:01
I don't know about DB2 (it's might simply not allow adding a sequence to an existing table), but did you try to combine both ALTER?:
alter table DB2INST1.AAA_BJ_BOND ADD COLUMN id INTEGER NOT NULL generated always as identity (start with 1);– dnoeth
Jan 2 at 9:41
I don't know about DB2 (it's might simply not allow adding a sequence to an existing table), but did you try to combine both ALTER?:
alter table DB2INST1.AAA_BJ_BOND ADD COLUMN id INTEGER NOT NULL generated always as identity (start with 1);– dnoeth
Jan 2 at 9:41
This will still lead to errors, but the solution is given below.
– skyline
Jan 9 at 3:01
This will still lead to errors, but the solution is given below.
– skyline
Jan 9 at 3:01
add a comment |
2 Answers
2
active
oldest
votes
You must drop the column DEFAULT value first.
This is mentioned in the description of SQL0190N:
If SET GENERATED ALWAYS AS (expression) is specified, but the column
is already defined with a form of generation (default, identity, or
expression) and there is no corresponding DROP in the same statement.
ALTER TABLE DB2INST1.AAA_BJ_BOND
ALTER COLUMN id drop default;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
– skyline
Jan 9 at 2:59
add a comment |
Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
answered Jan 9 at 3:00
skylineskyline
84
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002166%2fhow-db2v10-5-0-5-add-auto-increment-column-to-an-exists-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You must drop the column DEFAULT value first.
This is mentioned in the description of SQL0190N:
If SET GENERATED ALWAYS AS (expression) is specified, but the column
is already defined with a form of generation (default, identity, or
expression) and there is no corresponding DROP in the same statement.
ALTER TABLE DB2INST1.AAA_BJ_BOND
ALTER COLUMN id drop default;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
– skyline
Jan 9 at 2:59
add a comment |
You must drop the column DEFAULT value first.
This is mentioned in the description of SQL0190N:
If SET GENERATED ALWAYS AS (expression) is specified, but the column
is already defined with a form of generation (default, identity, or
expression) and there is no corresponding DROP in the same statement.
ALTER TABLE DB2INST1.AAA_BJ_BOND
ALTER COLUMN id drop default;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
– skyline
Jan 9 at 2:59
add a comment |
You must drop the column DEFAULT value first.
This is mentioned in the description of SQL0190N:
If SET GENERATED ALWAYS AS (expression) is specified, but the column
is already defined with a form of generation (default, identity, or
expression) and there is no corresponding DROP in the same statement.
ALTER TABLE DB2INST1.AAA_BJ_BOND
ALTER COLUMN id drop default;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
You must drop the column DEFAULT value first.
This is mentioned in the description of SQL0190N:
If SET GENERATED ALWAYS AS (expression) is specified, but the column
is already defined with a form of generation (default, identity, or
expression) and there is no corresponding DROP in the same statement.
ALTER TABLE DB2INST1.AAA_BJ_BOND
ALTER COLUMN id drop default;
ALTER TABLE DB2INST1.AAA_BJ_BOND ALTER COLUMN id
set generated always as identity (start with 1);
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
answered Jan 2 at 9:45
Mark BarinsteinMark Barinstein
1,916125
1,916125
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
– skyline
Jan 9 at 2:59
add a comment |
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
– skyline
Jan 9 at 2:59
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0; ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT; ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);– skyline
Jan 9 at 2:59
Your answer helped me solve this problem. Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0; ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT; ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);– skyline
Jan 9 at 2:59
add a comment |
Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
answered Jan 9 at 3:00
skylineskyline
84
add a comment |
Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
answered Jan 9 at 3:00
skylineskyline
84
add a comment |
Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
answered Jan 9 at 3:00
skylineskyline
84
Now I have successfully added auto-increasing ID to the table through the following three steps:
ALTER TABLE DB2INST1.AAA_SEAT ADD COLUMN ID INTEGER NOT NULL DEFAULT 0;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID DROP DEFAULT;
ALTER TABLE DB2INST1.AAA_SEAT ALTER COLUMN ID SET GENERATED ALWAYS AS IDENTITY (START WITH 1);
answered Jan 9 at 3:00
skylineskyline
84
answered Jan 9 at 3:00
skylineskyline
84
answered Jan 9 at 3:00
skylineskyline
84
answered Jan 9 at 3:00
skylineskyline
84
84
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002166%2fhow-db2v10-5-0-5-add-auto-increment-column-to-an-exists-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't know about DB2 (it's might simply not allow adding a sequence to an existing table), but did you try to combine both ALTER?:
alter table DB2INST1.AAA_BJ_BOND ADD COLUMN id INTEGER NOT NULL generated always as identity (start with 1);– dnoeth
Jan 2 at 9:41
This will still lead to errors, but the solution is given below.
– skyline
Jan 9 at 3:01