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. 8 12012
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" <lbrya...@juno. comwrote:
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 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
On 12 Apr, 00:57, "shorti" <lbrya...@juno. comwrote:
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...nonrecov erable.
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.
On Apr 12, 6:50 am, Phil Sherman <psher...@ameri tech.netwrote:
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).
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 <psher...@ameri tech.netwrote:
>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).
On Apr 13, 8:33 am, Phil Sherman <psher...@ameri tech.netwrote:
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
ChrisC wrote:
On Apr 13, 8:33 am, Phil Sherman <psher...@ameri tech.netwrote:
>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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Steven An |
last post by:
Howdy,
I need to write an update query with multiple aggregate functions.
Here is an example:
UPDATE t
SET
t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),
t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )
FROM dbo.test t
|
by: Gregory.Spencer |
last post by:
Hi there,
Using PHPMyAdmin and it is very usefully reporting problems with my
MySQL DB.
"PRIMARY and INDEX keys should not both be set for column
`column_name`"
and
|
by: pb648174 |
last post by:
I have a single update statement that updates the same column multiple
times in the same update statement. Basically i have a column that
looks like .1.2.3.4. which are id references that need to be updated
when a group of items is copied. I can successfully do this with
cursors, but am experimenting with a way to do it with a single update
statement.
I have verified that each row being returned to the Update statement
(in an...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.
Can I code this somehow. So the code presumabley would loop through all the
tables, open each table in design mode and then add the new field and set its
properties.
Thanks.
--
|
by: =?Utf-8?B?QVRT?= |
last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.
Please help,
I'm trying to write an ASP page to use ADO to run a long query against an
Oracle 10g database, to create tables, if they do not already exist. In terms
of ASP/ADO, that would be fine in a SQL Server Sense by a simply
ASP/Server-Side JavaScript as such:
var cnTemp = Server.CreateObject("ADODB.Connection");
| |
by: =?Utf-8?B?TG9zdEluTUQ=?= |
last post by:
Hi All :)
I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView.
True DBGrid has a MultipleLines property that controls whether individual
records span multiple lines. Is there an equivalent property for the
DataGridView? I have searched, but have not found one.
I would like the user to be able to see all the columns of the table on one
screen - thus eliminating the need to use the horizontal scroll bar to view...
|
by: Darsin |
last post by:
Hi all
I need to perform a summation on a column of a table based on a
criteria given in another column in the same table. The catch is i
need to perform different sums according to the number of criterias in
the criteria column, in a single query. the structure of the table is
somethinmg like this (only relevant columns are shown)
TABLE1
Value - numeric(20,6)
Month - int
|
by: ll |
last post by:
Hi all,
I've inherited a site and am currently looking to redesign a page that
displays a table of course curriculum, with each row representing a
"Topic" of the curriculum. There is a courseID that exists throughout
the site, identifying each course.
Within each row of the aforementioned table, there are 4 columns,
consisting of MainTopics, their associated SubTopics, uploaded
LectureNotes for that MainTopic, and finally a DeleteRow...
|
by: modernshoggoth |
last post by:
G'day all, thanks in advance for reading.
I've got two tables, one that's full of data regularly externally updated called "tblEmployeeLicences":
EmployeeID,Name,Licences
1001,Bill,Drivers
1001,Bill,Forklift
1002,Ted,Drivers
1002,Ted,Forklift
1002,Ted,Crane
The other table is one that is depended upon by an ID-card printing program, and as such only allows a single row per employee, called "IDProjectData":
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |