Expanding a column

Eric Robida

Expanding a column
I have a bunch of questions regarding expanding a column for this exalted
forum.
Here is what I need to do: expand a char(10) column to char(13). Any existing
data can remain as char(10) with spaces. This column exists in about 30 or so
tables. The tables range from 100,000 - 45 million rows each. I am looking for
an approach which will take a minimum of application changes.
This is what I think will work:
1) DBA does the drop/create table.
2) We load the old data back into the tables using the load utility. I
assume that the load utility will left justify the 10 character data and fill
the last three bytes with spaces.
Now some questions. If the character data is left justified will any
existing queries that are looking for a particular 10 character string need to
change?
Will the nightly insert programs need to be changed if they are still only
inserting 10 characters into this column? Will they automatically left justify
and fill with spaces without any changes? Is there something I am not thinking
of, ( it is safe to assume I know nothing)?

I greatly appreciate any thoughts you may have.



Scott Trometer

Re: Expanding a column
(in response to Eric Robida)
OS/390?

Continue
Else
Exit
End


a) DB2 Load Utility will not do as you expect unless you change the
'syspunch' dataset (data map)
to account for the extra bytes. This file can be created during the
unload via DSNTIAUL and
modified before the load process.

b) Inserts should be fine. In fact, some say this is a good way to perform
the change...
1) Create table like original(copy)
2) load or insert to copy
3) Drop original and make change
4) Insert to New table, Values(select * from copy)

With the number of rows you are talking about, I would avoid the insert
technique for this
implementation.

c) Selects could be a problem if the host variables that the data is being
selected INTO are not
changed to accommodate the new length. You probably have addressed this
if you are using
copybooks and selecting into DCLGEN host variables. A recompile to pick
up the new version
of the copybook will work.



-----Original Message-----
From: Eric Robida [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 2:14 PM
To: [login to unmask email]
Subject: Expanding a column


I have a bunch of questions regarding expanding a column for this
exalted
forum.
Here is what I need to do: expand a char(10) column to char(13). Any
existing
data can remain as char(10) with spaces. This column exists in about 30 or
so
tables. The tables range from 100,000 - 45 million rows each. I am looking
for
an approach which will take a minimum of application changes.
This is what I think will work:
1) DBA does the drop/create table.
2) We load the old data back into the tables using the load utility. I
assume that the load utility will left justify the 10 character data and
fill
the last three bytes with spaces.
Now some questions. If the character data is left justified will any
existing queries that are looking for a particular 10 character string need
to
change?
Will the nightly insert programs need to be changed if they are still only
inserting 10 characters into this column? Will they automatically left
justify
and fill with spaces without any changes? Is there something I am not
thinking
of, ( it is safe to assume I know nothing)?

I greatly appreciate any thoughts you may have.





Eric Robida

Re: Expanding a column
(in response to Scott Trometer)
ER:Yes it is on OS/390 V5.2.


a) DB2 Load Utility will not do as you expect unless you change the
'syspunch' dataset (data map)
to account for the extra bytes. This file can be created during the
unload via DSNTIAUL and
modified before the load process.

ER: So the it sounds like I will need to run the files through a program to pad
it. What happens when the load parm specifies that the data field in the load
file is char(10) and the table column is defined as char(13)? I am not a DBA
btw, so forgive my ignorance.

<<Snip>>


c) Selects could be a problem if the host variables that the data is being
selected INTO are not
changed to accommodate the new length. You probably have addressed this
if you are using
copybooks and selecting into DCLGEN host variables. A recompile to pick
up the new version
of the copybook will work.


ER: Yes, the HOST variables selected into are almost always from the DCLGEN. I
am more worried about this within the where clause. We ran a simple test in
Platinum on a 20 char name field with a where of 'Judy', we found the expected
rows but want to make sure this applies outside of Platinum. As long as trailing
spaces in a char column are irrelevent, I am all set.

<<big snip of original post>>



Harold Lee

Re: Expanding a column
(in response to Eric Robida)
=========
> a) DB2 Load Utility will not do as you expect unless you change the
> 'syspunch' dataset (data map)
> to account for the extra bytes. This file can be created during the
> unload via DSNTIAUL and
> modified before the load process.
===========
> ER: So the it sounds like I will need to run the files through a program
> to pad
> it. What happens when the load parm specifies that the data field in the
> load
> file is char(10) and the table column is defined as char(13)? I am not a
> DBA
> btw, so forgive my ignorance.
+++++++++++++++++++
If you are loading the 10 character data into the new 13 character
field then your "old" load command will work just fine and DB2 will space
fill. You should not need to run the data through a program to pad the
field.



Helen Johnson

Re: Expanding a column
(in response to Harold Lee)
On running the DB2 Load Utility, it will run successfully if the jcl
you have set up defines the limits of the character column either by
defining the starting and ending position or by defining the starting
position and the length of the character input field. We have done
this many times. When the programmer has changed the load file to
increase the length of the input data, we then change the jcl to
account for the increased length.

Helen

Scott Trometer wrote:

>
>
> OS/390?
>
> Continue
> Else
> Exit
> End
>
> a) DB2 Load Utility will not do as you expect unless you change the
> 'syspunch' dataset (data map)
> to account for the extra bytes. This file can be created during
> the unload via DSNTIAUL and
> modified before the load process.
>
> b) Inserts should be fine. In fact, some say this is a good way to
> perform the change...
> 1) Create table like original(copy)
> 2) load or insert to copy
> 3) Drop original and make change
> 4) Insert to New table, Values(select * from copy)
>
> With the number of rows you are talking about, I would avoid the
> insert technique for this
> implementation.
>
> c) Selects could be a problem if the host variables that the data is
> being selected INTO are not
> changed to accommodate the new length. You probably have
> addressed this if you are using
> copybooks and selecting into DCLGEN host variables. A recompile
> to pick up the new version
> of the copybook will work.
>
>
> -----Original Message-----
> From: Eric Robida [mailto:[login to unmask email]
> Sent: Tuesday, December 19, 2000 2:14 PM
> To: [login to unmask email]
> Subject: Expanding a column
>
> I have a bunch of questions regarding expanding a column for
> this exalted
> forum.
> Here is what I need to do: expand a char(10) column to char(13).
> Any existing
> data can remain as char(10) with spaces. This column exists in
> about 30 or so
> tables. The tables range from 100,000 - 45 million rows each. I am
> looking for
> an approach which will take a minimum of application changes.
> This is what I think will work:
> 1) DBA does the drop/create table.
> 2) We load the old data back into the tables using the load
> utility. I
> assume that the load utility will left justify the 10 character data
> and fill
> the last three bytes with spaces.
> Now some questions. If the character data is left justified
> will any
> existing queries that are looking for a particular 10 character
> string need to
> change?
> Will the nightly insert programs need to be changed if they are
> still only
> inserting 10 characters into this column? Will they automatically
> left justify
> and fill with spaces without any changes? Is there something I am
> not thinking
> of, ( it is safe to assume I know nothing)?
>
> I greatly appreciate any thoughts you may have.
>
>
>
> visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of
> the list can

Scott Trometer

Re: Expanding a column
(in response to Helen Johnson)
Right. You have to account for the three extra bytes somewhere before you
load it to the changed table. Another alternative is to use SQL in the
unload to pad the column with 3 spaces. This creates an unload file that
will be in 'load' format.

-----Original Message-----
From: Helen Johnson [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 3:26 PM
To: [login to unmask email]
Subject: Re: Expanding a column


On running the DB2 Load Utility, it will run successfully if the jcl you
have set up defines the limits of the character column either by defining
the starting and ending position or by defining the starting position and
the length of the character input field. We have done this many times.
When the programmer has changed the load file to increase the length of the
input data, we then change the jcl to account for the increased length.

Helen


Scott Trometer wrote:




OS/390?


Continue
Else
Exit
End


a) DB2 Load Utility will not do as you expect unless you change the
'syspunch' dataset (data map)
to account for the extra bytes. This file can be created during the
unload via DSNTIAUL and
modified before the load process.


b) Inserts should be fine. In fact, some say this is a good way to perform
the change...
1) Create table like original(copy)
2) load or insert to copy
3) Drop original and make change
4) Insert to New table, Values(select * from copy)


With the number of rows you are talking about, I would avoid the insert
technique for this
implementation.


c) Selects could be a problem if the host variables that the data is being
selected INTO are not
changed to accommodate the new length. You probably have addressed this
if you are using
copybooks and selecting into DCLGEN host variables. A recompile to pick
up the new version
of the copybook will work.



-----Original Message-----
From: Eric Robida [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Tuesday, December 19, 2000 2:14 PM
To: [login to unmask email]
Subject: Expanding a column


I have a bunch of questions regarding expanding a column for this
exalted
forum.
Here is what I need to do: expand a char(10) column to char(13). Any
existing
data can remain as char(10) with spaces. This column exists in about 30 or
so
tables. The tables range from 100,000 - 45 million rows each. I am looking
for
an approach which will take a minimum of application changes.
This is what I think will work:
1) DBA does the drop/create table.
2) We load the old data back into the tables using the load utility. I
assume that the load utility will left justify the 10 character data and
fill
the last three bytes with spaces.
Now some questions. If the character data is left justified will any
existing queries that are looking for a particular 10 character string need
to
change?
Will the nightly insert programs need to be changed if they are still only
inserting 10 characters into this column? Will they automatically left
justify
and fill with spaces without any changes? Is there something I am not
thinking
of, ( it is safe to assume I know nothing)?


I greatly appreciate any thoughts you may have.




DB2-L webpage at http://www.ryci.com/db2-l < http://www.ryci.com/db2-l > . The
owners of the list can

Kenneth Kornblum

Re: Expanding a column
(in response to Scott Trometer)
There seems to be some confusion here.

Let's say your LOAD command looks like:

LOAD
REPLACE
INTO TABLE MY.TABLE
(FLD1 POSITION(1:15) CHAR (15)
,FLD2 POSITION(16) VARCHAR
,FLD7 POSITION(100:109) CHAR (10)
)

If the field in question is FLD7 it will load JUST FINE into a CHAR(13)
column. The LOAD command describes the INPUT DATA, not the column in the
table. The LOAD utility figures out that the column is CHAR(13) and pads on
the right.

YOU DO NOT have to convert your data to add the extra pad blanks.
YOU DO NOT have to change the LOAD control cards.
YOU DO have to change your application if it SELECTs the column into a 10
character field.
SELECT FLD1, FLD2 FROM MY.TABLE WHERE FLD7 = 'KENK'
works as expected. DB2 assumes the padding on right.

Vendor Plug: Of course, if you want to perform this process REALLY FAST
you'll want to use BMC LOADPLUS for DB2 :-)

Now, you KNEW I was going to say that!

Hope this helps,
Ken Kornblum
BMC Software
Sr Product Developer
Austin, TX



Slot J.P.

Re: Expanding a column
(in response to Kenneth Kornblum)
Just putting in my two cents..

All of my previous listers touched the ins and outs of the load and unload.
I do have a design question, does this expansion have to do with expanding
use of the attribute? Or is this attribute used in another way.
You mentioned hte fact that applications will still use 10 character
strings,
this looks to me like extra functionality is added int this column.
Recently we had a lot of trouble in coding and SQL because two attributes
were combined in one attribute by expanding this particular attribute.
Take good care you don't make a mistake in modelling your data. It might
be wiser to add an extra attribute instead of expanding your existing
attribute.

Kind regards,

Jaap Slot,
sr. DB2 DBA,
Rabobank ICT

Phone 0031 (0)30 215 2220
Mobile 0031 (0)6 5374 0167
e-Mail <mailto:[login to unmask email]>

Reserve your AGENDA for next
DB2 Users Group Conference IDUG Florence
- 8/11 October 2001 - < http://www.idug.org > -

No trees were killed in the sending of this message.
However - a large number of electrons were terribly inconvenienced.

< ALL DISCLAIMERS DO APPLY >


-----Oorspronkelijk bericht-----
Van: Eric Robida [mailto:[login to unmask email]
Verzonden: dinsdag 19 december 2000 20:14
Aan: [login to unmask email]
Onderwerp: Expanding a column


I have a bunch of questions regarding expanding a column for this
exalted
forum.
Here is what I need to do: expand a char(10) column to char(13). Any
existing
data can remain as char(10) with spaces. This column exists in about 30 or
so
tables. The tables range from 100,000 - 45 million rows each. I am looking
for
an approach which will take a minimum of application changes.
This is what I think will work:
1) DBA does the drop/create table.
2) We load the old data back into the tables using the load utility. I
assume that the load utility will left justify the 10 character data and
fill
the last three bytes with spaces.
Now some questions. If the character data is left justified will any
existing queries that are looking for a particular 10 character string need
to
change?
Will the nightly insert programs need to be changed if they are still only
inserting 10 characters into this column? Will they automatically left
justify
and fill with spaces without any changes? Is there something I am not
thinking
of, ( it is safe to assume I know nothing)?

I greatly appreciate any thoughts you may have.







De informatie opgenomen in dit bericht kan vertrouwelijk zijn en
is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht
onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en
de afzender direct te informeren door het bericht te retourneren.

The information contained in this message may be confidential
and is intended to be exclusively for the addressee. Should you
receive this message unintentionally, please do not use the contents
herein and notify the sender immediately by return e-mail.



Ruediger Kurtz

Re: Expanding a column
(in response to Slot J.P.)
Hi Eric,

in your original mail you mentioned Drop & create being done by the DBAs; before I'd fiddle around with Load-Statements I'd rather go and ask them if they have some Change-Tools, be it BMC
ChangeManager or other tools, because these tools automatically do just what you intend to do manually.

Just a thought.

Regards Ruediger

"Slot, JP (Jaap)" schrieb:

> Just putting in my two cents..
>
> All of my previous listers touched the ins and outs of the load and unload.
> I do have a design question, does this expansion have to do with expanding
> use of the attribute? Or is this attribute used in another way.
> You mentioned hte fact that applications will still use 10 character
> strings,
> this looks to me like extra functionality is added int this column.
> Recently we had a lot of trouble in coding and SQL because two attributes
> were combined in one attribute by expanding this particular attribute.
> Take good care you don't make a mistake in modelling your data. It might
> be wiser to add an extra attribute instead of expanding your existing
> attribute.
>
> Kind regards,
>
> Jaap Slot,
> sr. DB2 DBA,
> Rabobank ICT
>
> Phone 0031 (0)30 215 2220
> Mobile 0031 (0)6 5374 0167
> e-Mail <mailto:[login to unmask email]>
>
> Reserve your AGENDA for next
> DB2 Users Group Conference IDUG Florence
> - 8/11 October 2001 - < http://www.idug.org > -
>
> No trees were killed in the sending of this message.
> However - a large number of electrons were terribly inconvenienced.
>
> < ALL DISCLAIMERS DO APPLY >
>
> -----Oorspronkelijk bericht-----
> Van: Eric Robida [mailto:[login to unmask email]
> Verzonden: dinsdag 19 december 2000 20:14
> Aan: [login to unmask email]
> Onderwerp: Expanding a column
>
> I have a bunch of questions regarding expanding a column for this
> exalted
> forum.
> Here is what I need to do: expand a char(10) column to char(13). Any
> existing
> data can remain as char(10) with spaces. This column exists in about 30 or
> so
> tables. The tables range from 100,000 - 45 million rows each. I am looking
> for
> an approach which will take a minimum of application changes.
> This is what I think will work:
> 1) DBA does the drop/create table.
> 2) We load the old data back into the tables using the load utility. I
> assume that the load utility will left justify the 10 character data and
> fill
> the last three bytes with spaces.
> Now some questions. If the character data is left justified will any
> existing queries that are looking for a particular 10 character string need
> to
> change?
> Will the nightly insert programs need to be changed if they are still only
> inserting 10 characters into this column? Will they automatically left
> justify
> and fill with spaces without any changes? Is there something I am not
> thinking
> of, ( it is safe to assume I know nothing)?
>
> I greatly appreciate any thoughts you may have.
>
>
>
>
>
>
>
> De informatie opgenomen in dit bericht kan vertrouwelijk zijn en
> is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht
> onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en
> de afzender direct te informeren door het bericht te retourneren.
>
> The information contained in this message may be confidential
> and is intended to be exclusively for the addressee. Should you
> receive this message unintentionally, please do not use the contents
> herein and notify the sender immediately by return e-mail.
>
>
>

--
HUK-COBURG
Versicherungen - Bausparen
Abteilung Informatik
Bahnhofsplatz
96450 Coburg
Tel. 09561/96-3914
Fax. 09561/96-3678
mailto:[login to unmask email]



Sanjeev (CTS) S

Re: Expanding a column
(in response to Ruediger Kurtz)
Jaap,
You made a very valid point but this is applicable when you are combining
two different business attribute to a single one or adding a new business
attribute to the existing one. We are not sure what is the situation Eric
have related to this thread. Still i need to say some good amount of impact
analysis required for both DB design as well as application design. If this
column is also part of some key then the impact on data modelling can be
there.
Another point which i would like to make that check all the WHERE and INTO
clause using the DCLGEN host variables rather than the program defined
working storage variables because it will cause performance degradation and
sql error respectively. Eric mentioned this is perfectly alright in this
case.
Above all it looks from Eric's mail is the concern here is blank padding of
the character column mentioned, for which all the replies are very useful.
Discussion on Load and Unload was also quite fruitful.

Regards
Sanjeev

> -----Original Message-----
> From: Slot, JP (Jaap) [SMTP:[login to unmask email]
> Sent: Wednesday, December 20, 2000 2:02 PM
> To: [login to unmask email]
> Subject: Re: Expanding a column
>
> Just putting in my two cents..
>
> All of my previous listers touched the ins and outs of the load and
> unload.
> I do have a design question, does this expansion have to do with expanding
> use of the attribute? Or is this attribute used in another way.
> You mentioned hte fact that applications will still use 10 character
> strings,
> this looks to me like extra functionality is added int this column.
> Recently we had a lot of trouble in coding and SQL because two attributes
> were combined in one attribute by expanding this particular attribute.
> Take good care you don't make a mistake in modelling your data. It might
> be wiser to add an extra attribute instead of expanding your existing
> attribute.
>
> Kind regards,
>
> Jaap Slot,
> sr. DB2 DBA,
> Rabobank ICT
>
> Phone 0031 (0)30 215 2220
> Mobile 0031 (0)6 5374 0167
> e-Mail <mailto:[login to unmask email]>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Eric Robida

Re: Expanding a column
(in response to Sanjeev (CTS) S)
No, we are not combining two values. What is happening is we are going through
a system consolidation. The new system uses a 13 character string as an
identifier. However the systems being replaced could be stored in 10
characters. All systems are not being phased out at once. So we will have a mix
of 10 character and 13 character data. What the data represents is not changing,
just the amount of space it will take up. The other option is to not expand the
tables and store a 10 character dummy value and use some x-ref lookups to get
the true value. That creates a situation where the column could contain
legitimate data or the blind key. The scenarios for when which is which would be
easily determined but it is still a risk...
I would like to thank those of you who have helped. You have validated
what I thought was true. I appreciate it.




"Slot, JP (Jaap)" <[login to unmask email]> on 12/20/2000 03:32:04 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Eric Robida/BCBSME)
Subject: Re: Expanding a column



Just putting in my two cents..

All of my previous listers touched the ins and outs of the load and unload.
I do have a design question, does this expansion have to do with expanding
use of the attribute? Or is this attribute used in another way.
You mentioned hte fact that applications will still use 10 character
strings,
this looks to me like extra functionality is added int this column.
Recently we had a lot of trouble in coding and SQL because two attributes
were combined in one attribute by expanding this particular attribute.
Take good care you don't make a mistake in modelling your data. It might
be wiser to add an extra attribute instead of expanding your existing
attribute.

Kind regards,

Jaap Slot,
sr. DB2 DBA,
Rabobank ICT

Phone 0031 (0)30 215 2220
Mobile 0031 (0)6 5374 0167
e-Mail <mailto:[login to unmask email]>

Reserve your AGENDA for next
DB2 Users Group Conference IDUG Florence
- 8/11 October 2001 - < http://www.idug.org > -

No trees were killed in the sending of this message.
However - a large number of electrons were terribly inconvenienced.

< ALL DISCLAIMERS DO APPLY >


-----Oorspronkelijk bericht-----
Van: Eric Robida [mailto:[login to unmask email]
Verzonden: dinsdag 19 december 2000 20:14
Aan: [login to unmask email]
Onderwerp: Expanding a column


I have a bunch of questions regarding expanding a column for this
exalted
forum.
Here is what I need to do: expand a char(10) column to char(13). Any
existing
data can remain as char(10) with spaces. This column exists in about 30 or
so
tables. The tables range from 100,000 - 45 million rows each. I am looking
for
an approach which will take a minimum of application changes.
This is what I think will work:
1) DBA does the drop/create table.
2) We load the old data back into the tables using the load utility. I
assume that the load utility will left justify the 10 character data and
fill
the last three bytes with spaces.
Now some questions. If the character data is left justified will any
existing queries that are looking for a particular 10 character string need
to
change?
Will the nightly insert programs need to be changed if they are still only
inserting 10 characters into this column? Will they automatically left
justify
and fill with spaces without any changes? Is there something I am not
thinking
of, ( it is safe to assume I know nothing)?

I greatly appreciate any thoughts you may have.







De informatie opgenomen in dit bericht kan vertrouwelijk zijn en
is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht
onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en
de afzender direct te informeren door het bericht te retourneren.

The information contained in this message may be confidential
and is intended to be exclusively for the addressee. Should you
receive this message unintentionally, please do not use the contents
herein and notify the sender immediately by return e-mail.








Aurora Dell'Anno

Re: Expanding a column
(in response to Eric Robida)
want my TL 2? (very little worth I am afraid)...

well actually no, you only have to change your existing applications to
SELECT a CHAR(13) item only if you want to avoid truncation of the
exceeding bytes at the end (right) of your column, otherwise if you did not
care you wold not have to.

having said that, I guess if you did not care you would not increase the
size of the data....

ciao.

Aurora Emanuela Dell'Anno
Systems Engineer
Sistemi Informativi S.p.A.
e-mail: [login to unmask email]
tel. +39 335 7429486

no trees were killed in the sending of this message. however - a large
number of electrons were seriously inconvenienced.

*std disclaimer* - my opinions are my own (and not my company's)





"Kornblum,
Kenneth" To: [login to unmask email]
<Kenneth_Kornblu cc:
[login to unmask email]> Subject: Re: Expanding a column
Sent by: DB2
Data Base
Discussion List
<[login to unmask email]>


19/12/2000 21.55
Please respond
to DB2 Data Base
Discussion List





There seems to be some confusion here.

Let's say your LOAD command looks like:

LOAD
REPLACE
INTO TABLE MY.TABLE
(FLD1 POSITION(1:15) CHAR (15)
,FLD2 POSITION(16) VARCHAR
,FLD7 POSITION(100:109) CHAR (10)
)

If the field in question is FLD7 it will load JUST FINE into a CHAR(13)
column. The LOAD command describes the INPUT DATA, not the column in the
table. The LOAD utility figures out that the column is CHAR(13) and pads
on the right.

YOU DO NOT have to convert your data to add the extra pad blanks.
YOU DO NOT have to change the LOAD control cards.
YOU DO have to change your application if it SELECTs the column into a 10
character field.
SELECT FLD1, FLD2 FROM MY.TABLE WHERE FLD7 = 'KENK'
works as expected. DB2 assumes the padding on right.

Vendor Plug: Of course, if you want to perform this process REALLY FAST
you'll want to use BMC LOADPLUS for DB2 :-)

Now, you KNEW I was going to say that!

Hope this helps,
Ken Kornblum
BMC Software
Sr Product Developer
Austin, TX



Andrew Laredo

Re: Expanding a column
(in response to Aurora Dell'Anno)
This is a nit, but may bite you. Notice the WHERE below, using LIKE, only
returns one row. Once your new column is padded with blanks any existing
queries you have, if you do, that look like this may not operate as
expected...

SELECT NAME,CREATOR
FROM "SYSIBM".SYSDATABASE
WHERE
NAME LIKE 'DSNDB04'
OR NAME LIKE 'DSN8D61A'
---------+---------+---------+---------
---------+---------+---------+---------
NAME CREATOR
---------+---------+---------+---------
DSN8D61A JKS
NUMBER OF ROWS SELECTED 1


-----Original Message-----
From: Eric Robida [mailto:[login to unmask email]
Sent: Wednesday, December 20, 2000 8:28 AM
To: [login to unmask email]
Subject: Re: Expanding a column


No, we are not combining two values. What is happening is we are going
through
a system consolidation. The new system uses a 13 character string as an
identifier. However the systems being replaced could be stored in 10
characters. All systems are not being phased out at once. So we will have a
mix
of 10 character and 13 character data. What the data represents is not
changing,
just the amount of space it will take up. The other option is to not expand
the
tables and store a 10 character dummy value and use some x-ref lookups to
get
the true value. That creates a situation where the column could contain
legitimate data or the blind key. The scenarios for when which is which
would be
easily determined but it is still a risk...
I would like to thank those of you who have helped. You have validated
what I thought was true. I appreciate it.




"Slot, JP (Jaap)" <[login to unmask email]> on 12/20/2000 03:32:04 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Eric Robida/BCBSME)
Subject: Re: Expanding a column



Just putting in my two cents..

All of my previous listers touched the ins and outs of the load and unload.
I do have a design question, does this expansion have to do with expanding
use of the attribute? Or is this attribute used in another way.
You mentioned hte fact that applications will still use 10 character
strings,
this looks to me like extra functionality is added int this column.
Recently we had a lot of trouble in coding and SQL because two attributes
were combined in one attribute by expanding this particular attribute.
Take good care you don't make a mistake in modelling your data. It might
be wiser to add an extra attribute instead of expanding your existing
attribute.

Kind regards,

Jaap Slot,
sr. DB2 DBA,
Rabobank ICT

Phone 0031 (0)30 215 2220
Mobile 0031 (0)6 5374 0167
e-Mail <mailto:[login to unmask email]>

Reserve your AGENDA for next
DB2 Users Group Conference IDUG Florence
- 8/11 October 2001 - < http://www.idug.org > -

No trees were killed in the sending of this message.
However - a large number of electrons were terribly inconvenienced.

< ALL DISCLAIMERS DO APPLY >


-----Oorspronkelijk bericht-----
Van: Eric Robida [mailto:[login to unmask email]
Verzonden: dinsdag 19 december 2000 20:14
Aan: [login to unmask email]
Onderwerp: Expanding a column


I have a bunch of questions regarding expanding a column for this
exalted
forum.
Here is what I need to do: expand a char(10) column to char(13). Any
existing
data can remain as char(10) with spaces. This column exists in about 30 or
so
tables. The tables range from 100,000 - 45 million rows each. I am looking
for
an approach which will take a minimum of application changes.
This is what I think will work:
1) DBA does the drop/create table.
2) We load the old data back into the tables using the load utility. I
assume that the load utility will left justify the 10 character data and
fill
the last three bytes with spaces.
Now some questions. If the character data is left justified will any
existing queries that are looking for a particular 10 character string need
to
change?
Will the nightly insert programs need to be changed if they are still only
inserting 10 characters into this column? Will they automatically left
justify
and fill with spaces without any changes? Is there something I am not
thinking
of, ( it is safe to assume I know nothing)?

I greatly appreciate any thoughts you may have.







De informatie opgenomen in dit bericht kan vertrouwelijk zijn en
is uitsluitend bestemd voor de geadresseerde. Indien u dit bericht
onterecht ontvangt, wordt u verzocht de inhoud niet te gebruiken en
de afzender direct te informeren door het bericht te retourneren.

The information contained in this message may be confidential
and is intended to be exclusively for the addressee. Should you
receive this message unintentionally, please do not use the contents
herein and notify the sender immediately by return e-mail.