Identity column problems

[login to unmask email]

Identity column problems
Okay, we don't have version 8.2 yet (8.1.5)

We have tables that have identity column with generate always.

We want to modify the table (drop a column) and preserve the data.

We are using power builder and it comes up with something that doesn't
work (set identity_insert IMPACT.tmp_TESTER off;)

We can migrate the data to a temporary created table, but when we want to
reload the modified table we want generate always as identity and UDB
won't let us specify the primary key Col in the insert statement.

Are we 'SOL' until we get version 8.2 where they have the alter table
alter column with the new identity options?

Dean J. Burchill
248-576-8319
(8) 776-8319

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ian Davies

Re: Identity column problems
(in response to DB46@DAIMLERCHRYSLER.COM)
Hi Dean,

I am assuming DB2 for LUW here.

Look at the identityoverride option on the load utility.

You could then rename the old table, create the new one and do a load
from cursor to re-populate the table.

Cheers,
Ian.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of [login to unmask email]
Sent: Thursday - December 8, 2005 15:05
To: [login to unmask email]
Subject: [DB2-L] Identity column problems



Okay, we don't have version 8.2 yet (8.1.5)

We have tables that have identity column with generate always.

We want to modify the table (drop a column) and preserve the
data.

We are using power builder and it comes up with something that
doesn't work (set identity_insert IMPACT.tmp_TESTER off;)

We can migrate the data to a temporary created table, but when
we want to reload the modified table we want generate always as identity
and UDB won't let us specify the primary key Col in the insert
statement.

Are we 'SOL' until we get version 8.2 where they have the alter
table alter column with the new identity options?

Dean J. Burchill
248-576-8319
(8) 776-8319
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

JOHN KRUGER

Re: Identity column problems
(in response to Ian Davies)

NO problemo!

If you export the generated data and you wish to import the data back
intot the generated column, you must use the GENERATEDIGNORE Option
and it works like MAGIC! :-))








>>> [login to unmask email] 12/08/05 4:05 PM >>>

Okay, we don't have version 8.2 yet (8.1.5)

We have tables that have identity column with generate always.

We want to modify the table (drop a column) and preserve the data.

We are using power builder and it comes up with something that doesn't
work (set identity_insert IMPACT.tmp_TESTER off;)

We can migrate the data to a temporary created table, but when we want
to reload the modified table we want generate always as identity and UDB
won't let us specify the primary key Col in the insert statement.

Are we 'SOL' until we get version 8.2 where they have the alter table
alter column with the new identity options?

Dean J. Burchill
248-576-8319
(8) 776-8319
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Identity column problems
(in response to JOHN KRUGER)
Can you give an example, I've tried the following and it doesn't work:

import from t1.del of del modified by GENERATEDIGNORE replace into tester;

Get

SQL3526N The modifier clause "GENERATEDIGNORE" is inconsistent with the
current load command. Reason code: "3".



Dean J. Burchill
248-576-8319
(8) 776-8319

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

JOHN KRUGER

Re: Identity column problems
(in response to DB46@DAIMLERCHRYSLER.COM)

I'm our the door (BIG emergency) but it's one of these ..............


I will return tomorrow morning



LOAD FROM 'C:\MYDIRECTORY\t1.del' OF DEL MODIFIED BY
GENERATEDOVERRIDE IDENTITYOVERRIDE
MESSAGES 'C:\outfile.msg' INSERT INTO DHSDB2.TABLENAME ALLOW NO
ACCESS;









John P. Kruger
Database Administrator
Management Information Systems
IL Department of Human Services
(217) 557-9720
FAX (217) 782-6089


>>> [login to unmask email] 12/08/05 4:29 PM >>>

Can you give an example, I've tried the following and it doesn't work:


import from t1.del of del modified by GENERATEDIGNORE replace into
tester;

Get

SQL3526N The modifier clause "GENERATEDIGNORE" is inconsistent with
the
current load command. Reason code: "3".



Dean J. Burchill
248-576-8319
(8) 776-8319
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: Identity column problems
(in response to JOHN KRUGER)
Reason code 3 = Generated or identity related file type modifiers
have been specified but the target table contains no such columns.

Does it have an identity column?

James Campbell

On 8 Dec 2005 at 17:29, [login to unmask email] wrote:

>
>
> Can you give an example, I've tried the following and it doesn't work:
>
> import from t1.del of del modified by GENERATEDIGNORE replace into tester;
>
> Get
>
> SQL3526NThe modifier clause "GENERATEDIGNORE"is inconsistent with the
> current load command. Reason code: "3".
>
>
>
> Dean J. Burchill
> 248-576-8319
> (8) 776-8319 --------------------------------------------------------------------------------- Welcome to the
> IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-
> l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is
> at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ian Davies

Re: Identity column problems
(in response to James Campbell)
Hi Dean,

This works for us assuming identity columns and no other generated
columns:

declare c1 cursor for
SELECT identity_col, col1, col2, ...
FROM SCHEMA1.TABLE_NAME_OLD
WITH UR;
load from c1 of cursor
modified by identityoverride
messages /msgpath/load.msg
replace into SCHEMA1.TABLE_NAME
(identity_col, col1, col2, ...)
statistics use profile
nonrecoverable;
--may need to alter table TABLE_NAME alter column identity_col restart
with xxx

The modified ignore options usually mean that the input data is ignored
rather then overriding the normal behaviour of the target table.

Cheers,
Ian.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of [login to unmask email]
Sent: Thursday - December 8, 2005 15:30
To: [login to unmask email]
Subject: Re: [DB2-L] Identity column problems



Can you give an example, I've tried the following and it doesn't
work:

import from t1.del of del modified by GENERATEDIGNORE replace
into tester;

Get

SQL3526N The modifier clause "GENERATEDIGNORE" is inconsistent
with the
current load command. Reason code: "3".



Dean J. Burchill
248-576-8319
(8) 776-8319
------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jay Reavill

Re: Identity column problems
(in response to Ian Davies)
I believe the keyword you're looking for is IDENTITYOVERRIDE. Which
according to the V8.2 Command Guide appears to be only an option with LOAD,
not IMPORT.

HTH,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"James Campbell"
<[login to unmask email] To: [login to unmask email]
INK.NET.AU> cc:
Sent by: "DB2 Subject: Re: [DB2-L] Identity column problems
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/08/2005 09:15
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





Reason code 3 = Generated or identity related file type modifiers
have been specified but the target table contains no such columns.

Does it have an identity column?

James Campbell

On 8 Dec 2005 at 17:29, [login to unmask email] wrote:

>
>
> Can you give an example, I've tried the following and it doesn't work:
>
> import from t1.del of del modified by GENERATEDIGNORE replace into
tester;
>
> Get
>
> SQL3526NThe modifier clause "GENERATEDIGNORE"is inconsistent with the
> current load command. Reason code: "3".
>
>
>
> Dean J. Burchill
> 248-576-8319
> (8) 776-8319
---------------------------------------------------------------------------------
Welcome to the
> IDUG DB2-L list. To unsubscribe, go to the archives and home page at
http://www.idugdb2-
> l.org/archives/db2-l.html. From that page select "Join or Leave the
list". The IDUG DB2-L FAQ is
> at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm

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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [login to unmask email]
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Identity column problems
(in response to Jay Reavill)
Actually used IDENTITYIGNORE and it worked fine. Thanks all.

Dean J. Burchill
248-576-8319
(8) 776-8319




Jay Reavill <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/09/2005 09:56 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Identity column problems






I believe the keyword you're looking for is IDENTITYOVERRIDE. Which
according to the V8.2 Command Guide appears to be only an option with
LOAD,
not IMPORT.

HTH,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"James Campbell"
<[login to unmask email] To: [login to unmask email]

INK.NET.AU> cc:
Sent by: "DB2 Subject: Re: [DB2-L]
Identity column problems
Data Base
Discussion List"
<[login to unmask email]
.ORG>


12/08/2005 09:15
PM
Please respond
to "DB2 Database
Discussion list
at IDUG"





Reason code 3 = Generated or identity related file type modifiers
have been specified but the target table contains no such columns.

Does it have an identity column?

James Campbell

On 8 Dec 2005 at 17:29, [login to unmask email] wrote:

>
>
> Can you give an example, I've tried the following and it doesn't work:
>
> import from t1.del of del modified by GENERATEDIGNORE replace into
tester;
>
> Get
>
> SQL3526NThe modifier clause "GENERATEDIGNORE"is inconsistent with the
> current load command. Reason code: "3".
>
>
>
> Dean J. Burchill
> 248-576-8319
> (8) 776-8319
---------------------------------------------------------------------------------
Welcome to the
> IDUG DB2-L list. To unsubscribe, go to the archives and home page at
http://www.idugdb2-
> l.org/archives/db2-l.html. From that page select "Join or Leave the
list". The IDUG DB2-L FAQ is
> at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm

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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select
"Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm








------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[login to unmask email]
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jay Reavill

Re: Identity column problems
(in response to DB46@DAIMLERCHRYSLER.COM)
Hmm, now I'm confused (as usual) because the v8.2 Command Guide says in
relation to IDENTITYIGNORE...

"This modifier informs the import utility that data for the identity column
is present in the data file but should be ignored.
This results in all identity values being generated by the utility. The
behavior will be the same for both GENERATED
ALWAYS and GENERATED BY DEFAULT identity columns."

So that says to me that no matter what, the values will be generated. Any
input values will be ignored.

As opposed to what it says in relation to IDENTITYOVERRIDE...

"This modifier should be used only when an identity column defined as
GENERATED ALWAYS is present in the table
to be loaded. It instructs the utility to accept explicit, non-NULL data
for such a column (contrary to the normal rules
for these types of identity columns). This is useful when migrating data
from another database system when the table
must be defined as GENERATED ALWAYS."

Is the manual just misleading or am I just reading it wrong?

Thanks,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




[login to unmask email]
SLER.COM To: [login to unmask email]
Sent by: "DB2 cc:
Data Base Subject: Re: [DB2-L] Identity column problems
Discussion List"
<[login to unmask email]
.ORG>


12/09/2005 10:24
AM
Please respond
to "DB2 Database
Discussion list
at IDUG"






Actually used IDENTITYIGNORE and it worked fine. Thanks all.

Dean J. Burchill
248-576-8319
(8) 776-8319



Jay Reavill <[login to unmask email]>
Sent by: DB2 Data Base Discussion List
<[login to unmask email]> To
[login to unmask email]
.ORG
12/09/2005 09:56 AM cc

Subject
Please respond to Re: [DB2-L]
DB2 Database Discussion list at IDUG Identity column
<[login to unmask email]> problems












I believe the keyword you're looking for is IDENTITYOVERRIDE. Which
according to the V8.2 Command Guide appears to be only an option with LOAD,
not IMPORT.

HTH,
Jay


----------------------------------------
Jay Reavill DBA
Certegy Card Services
11601 Roosevelt Blvd.
St. Petersburg, FL. 33716
Office (727) 227-2144
----------------------------------------




"James Campbell"

<[login to unmask email] To:
[login to unmask email]

INK.NET.AU> cc:

Sent by: "DB2 Subject: Re: [DB2-L] Identity
column problems
Data Base

Discussion List"

<[login to unmask email]

.ORG>



12/08/2005 09:15

PM

Please respond

to "DB2 Database

Discussion list

at IDUG"





Reason code 3 = Generated or identity related file type modifiers
have been specified but the target table contains no such columns.

Does it have an identity column?

James Campbell

On 8 Dec 2005 at 17:29, [login to unmask email] wrote:

>
>
> Can you give an example, I've tried the following and it doesn't work:
>
> import from t1.del of del modified by GENERATEDIGNORE replace into
tester;
>
> Get
>
> SQL3526NThe modifier clause "GENERATEDIGNORE"is inconsistent with the
> current load command. Reason code: "3".
>
>
>
> Dean J. Burchill
> 248-576-8319
> (8) 776-8319
---------------------------------------------------------------------------------

Welcome to the
> IDUG DB2-L list. To unsubscribe, go to the archives and home page at
http://www.idugdb2-
> l.org/archives/db2-l.html. From that page select "Join or Leave the
list". The IDUG DB2-L FAQ is
> at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
DB2-L-
> [login to unmask email] Find out the latest on IDUG conferences at
> http://conferences.idug.org/index.cfm

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


Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm








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

This message contains information from Certegy, Inc which may be
confidential and privileged. If you are not an intended recipient, please
refrain from any disclosure, copying, distribution or use of this
information and note that such actions are prohibited. If you have
received this transmission in error, please notify by e:mail
[login to unmask email]
=====


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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm






------------------------------------------------------------------------------
This message contains information from Certegy, Inc which may be confidential and privileged. If you are not an intended recipient, please refrain from any disclosure, copying, distribution or use of this information and note that such actions are prohibited. If you have received this transmission in error, please notify by e:mail [login to unmask email]
=====

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm