Changing format of column



IBM's flagship relational database management system

Changing format of column

Postby vegafacundodaniel » Sun Jan 11, 2015 5:55 am

Hi,
I need help please.

I have a DB2 table that has a index. That index has 3 columns. One of thoses columns is numeric and I need to transform it to character.

Current index:
ColumnA  CHAR(3    )
ColumnB  DECIMAL (2 , 0 )   ------------------> I need to change it
ColumnC  CHAR (2    )


New index:
ColumnA  CHAR(3    )
ColumnB  CHAR (2    )  ------------------> New format
ColumnC  CHAR (2    )


The documentation I have says to avoid to changing numeric ---> character because that causes many problems, but it doesn't say which ones....:(
Instead, it says to add 1 new colum at the end of the table but, that is not possible because the ColumnB is part of the key of the table.

Could anyone tell me what should I do ?
Thanks in advance!
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Changing format of column

Postby prino » Sun Jan 11, 2015 5:42 pm

I can't say what you should do, but what you should have done is to think about your design before you started.

And changing data that is intrinsically numerical to character? PMAB!
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: Changing format of column

Postby vegafacundodaniel » Sun Jan 11, 2015 6:10 pm

I did not design that table.

It is strange to change numeric to character, but I need it. The values are numerics in the columnB.
The problem is my ERP that uses DB2 tables. When ColumnB est populated with spaces, it transforms automatically the spaces to zeros because ColumnB is defined as numerique. That causes a problem because I need to verify the ColumnB must be not empty.

It is for that I need to alter the ColumnB.

Thanks
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Changing format of column

Postby prino » Sun Jan 11, 2015 8:15 pm

vegafacundodaniel wrote:The problem is my ERP that uses DB2 tables. When ColumnB est populated with spaces, it transforms automatically the spaces to zeros because ColumnB is defined as numerique. That causes a problem because I need to verify the ColumnB must be not empty

Ever heard of "NULL"?
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy

These users thanked the author prino for the post:
sikkandhar (Wed Feb 17, 2016 2:51 am)
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: Changing format of column

Postby vegafacundodaniel » Sun Jan 11, 2015 8:40 pm

WHen I want to check if columnB is empty (spaces) ou null (nothing), that column contains zeros, populated by the ERP. The ERP is a closed source that I can't not modify.
Thanks
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Changing format of column

Postby enrico-sorichetti » Sun Jan 11, 2015 9:19 pm

that column contains zeros, populated by the ERP. The ERP is a closed source that I can't not modify.

how do You know that after changing the column the ERP application will still work ???
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Changing format of column

Postby vegafacundodaniel » Sun Jan 11, 2015 9:30 pm

After changing the columnB, I need to adapte the customer procedures impacted (=programs).
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Changing format of column

Postby enrico-sorichetti » Sun Jan 11, 2015 10:01 pm

The ERP is a closed source

just take a decision ...

the ERP/application/whateverYouwantto callthething

is it closed source or not ???
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: Changing format of column

Postby vegafacundodaniel » Sun Jan 11, 2015 10:08 pm

The ERP is a semi closed source. It makes somes things that I can't not control, for example, it makes zeros instead of spaces when a column is numeric.
Thanks
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Changing format of column

Postby vegafacundodaniel » Sun Jan 11, 2015 11:01 pm

Question please. If I:

- UNLOAD the old table (definition and data) with IKJEFT01 program. It makes 2 files (*)
- DROP old table (Tablespace, table, index..) with columnB as numeric
- CREATE new table with columnB as CHAR
- LOAD data with (*) using DSNUPROC program.
Question: the data from the old ColumnB (DECIMAL 2) populates the new columnB (CHAR 2) properly?

Thanks
vegafacundodaniel
 
Posts: 61
Joined: Tue Jul 20, 2010 4:27 pm
Has thanked: 1 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post