Multiple column updates in a single query

[shorti]

DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.

[mirof007]

Don't know if it's the most efficient way, but you can use a case
expression that returns the blanks when the column is null, else the
column value itself, then use that to unconditionally update all rows.
Something like (not tested):

update table1 set c2=(case when c2 is null then ' ' else c2 end),
c3=(case when c3 is null then ' ' else c3 end), c4=(case when c4 is
null then ' ' else c4 end), c5=(case when c5 is null then ' '
else c5 end)

You might want to chunk up the update if the table is huge and your
active log space is limited. Hope this helps.

Regards,
Miro

On Apr 11, 7:57 pm, "shorti" <[e-mail]:

DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.

[Lennart]

shorti wrote:

DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

update T
set (col2, col3, ...) = (coalesce(col2,' '), coalesce(col3, '
'), ...)

/Lennart

[james_dey]

On 12 Apr, 00:57, "shorti" <[e-mail]:

DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.

Could you not just do a export (with a case statement for the relevant
4 columns) and then an import from <file>.del of del commitcount 10000
insert_update into <table>.
It might be even quicker, if you were to truncate the table after
you've done the export and then do a load...nonrecoverable.

[Phil Sherman]

Don't forget to check the space consequences of changing the columns
from null to blanks. You'll be adding 8-32 bytes for each row containing
null columns. If you don't have enough free space in the existing pages,
rows will physically move during this update. This could have
significant performance consequences for existing SQL.

Phil Sherman
shorti wrote:

DB2 V8.2 on AIX

I am looking for an efficient way to update several columns in a table
that will have a default change. The problem is the table is large
(million records) and there are 1 to 4 columns that might need to be
changed per record. I wanted to avoid looping through the table 4
times in order to change them.

Here is an example. Basically, the four columns in question were
originally defaulted to NULL. Due to changes elsewhere it can no
longer be NULL so it will be changed to blanks. Here is what the
table would look like prior to changing the default:

table1
col1 bigint
col2 char(8) default null
col3 char(8) default null
col4 char(8) default null
col5 char(8) default null
col6 smallint default 0

So now there are records in the table where some of the values in col2
thru col5 are still NULL and some contain values.

I want to parse the table and change anything that is null in these
four columns to blanks (' '). I am hoping to do with without
having to loop through it four times because I dont know if all
columns are null or maybe only one or two.

Anyone have a good query to do this?

Much Appreciated.

[shorti]

On Apr 12, 6:50 am, Phil Sherman <[e-mail]:

Don't forget to check the space consequences of changing the columns
from null to blanks. You'll be adding 8-32 bytes for each row containing
null columns. If you don't have enough free space in the existing pages,
rows will physically move during this update. This could have
significant performance consequences for existing SQL.

Phil Sherman

Can I just do a reorg after all the records are updated to prevent the
performance problem?

I am leaning towards doing the EXPORT and LOAD of the 4 columns since
I agree this would be the most effecient. I dont expect anyone to be
manipulating the table while this is being done but do I need to
"LOCK" the table? I noticed the LOAD does it's own lock so will this
interfere? I am just concerned with the time between the EXPORT and
the LOAD.

The other queries are great and I will add them to my list for future
use.

(forgive me if this is a duplicate response...my last reply did not
seem to come through after 30+ minutes).

[Phil Sherman]

If you will have to reorg after processing the entire table, another
option is to do a SELECT of the entire table, constructing the output as
csv (comma separated variable) data. The SELECT can, using COALESCE,
replace any nulls with blanks. Once the data has been unloaded, change
the table definition from nullable to NOT NULL WITH DEFAULT and use the
load utility to rebuild the table. This will, however involve a lot more
DBA work. It'll also allow existing code to continue running and have
new data rows have appropriate defaults.

Phil Sherman
shorti wrote:

On Apr 12, 6:50 am, Phil Sherman <[e-mail]:

>Don't forget to check the space consequences of changing the columns
from null to blanks. You'll be adding 8-32 bytes for each row containing
null columns. If you don't have enough free space in the existing pages,
rows will physically move during this update. This could have
significant performance consequences for existing SQL.

Phil Sherman

Can I just do a reorg after all the records are updated to prevent the
performance problem?

I am leaning towards doing the EXPORT and LOAD of the 4 columns since
I agree this would be the most effecient. I dont expect anyone to be
manipulating the table while this is being done but do I need to
"LOCK" the table? I noticed the LOAD does it's own lock so will this
interfere? I am just concerned with the time between the EXPORT and
the LOAD.

The other queries are great and I will add them to my list for future
use.

(forgive me if this is a duplicate response...my last reply did not
seem to come through after 30+ minutes).

[ChrisC]

On Apr 13, 8:33 am, Phil Sherman <[e-mail]:

If you will have to reorg after processing the entire table, another
option is to do a SELECT of the entire table, constructing the output as
csv (comma separated variable) data.

You want to be VERY careful using csv files; most especially you want
to make sure there aren't any commas in your data, or you will not be
able to reload the data correctly. Also, I'm not sure what non-US
ASCII data would do - if your data is like that, you need to read the
documentation very carefully to make sure you know the limitations of
the csv (called DEL format) loading utility.

I'd be more inclined to recomment the ASC format (fixed width) or IXF
(internal) - the first requires quite a bit more setup, though.
However, both of these should successfully reload all of the data
correctly - no matter what the data is.

-Chris

[Ian]

ChrisC wrote:

On Apr 13, 8:33 am, Phil Sherman <[e-mail]:

>If you will have to reorg after processing the entire table, another
option is to do a SELECT of the entire table, constructing the output as
csv (comma separated variable) data.

You want to be VERY careful using csv files; most especially you want
to make sure there aren't any commas in your data, or you will not be
able to reload the data correctly. Also, I'm not sure what non-US
ASCII data would do - if your data is like that, you need to read the
documentation very carefully to make sure you know the limitations of
the csv (called DEL format) loading utility.

DB2 is very good with its delimited format.

By default the DEL format uses both character AND column delimiters.
The default character delimiter is a double-quote ("), and the
default column delimiter is a comma (,).

DB2 will handle column delimiters as long as the character delimiters
exist.

It will even handle embedded newlines (provided you use the
delprioritychar filetype-modifier).

I'd be more inclined to recomment the ASC format (fixed width) or IXF
(internal) - the first requires quite a bit more setup, though.
However, both of these should successfully reload all of the data
correctly - no matter what the data is.

You can't export data in fixed-width format from DB2 (on
Linux/Unix/Windows)