Re: table update best practices

On Aug 13, 7:57 pm, "Frank Swarbrick" <Frank.Swarbr...@xxxxxxxxxxxxxx>
wrote:
With mainframe indexed files (VSAM) and DL/I (IMS) databases segments, which
are somewhat analogous to rows in a table, are pretty much just, umm, large
chunks of data. If you want to replace a segment with new values for one or
more fields you must replace the entire segment.

Obviously this is not the case for a relational database.

Currently we have some DL/I databases with segments that contain over 400
individual 'fields'. When migrating to DB2, I'm guessing that having an SQL
UPDATE with SET column-name = :host_var 400+ times (one for each column) is
probably not something that one would want to do. What are the
alternatives? Here's what I can think of, though none of them seem to me to
be ideal...

1) Update one column at a time, and only if it has changed. EG:

EXEC SQL
SELECT column1, column2, column3, -- etc.
INTO :column1, :column2, :column3 -- etc.
FROM A_TABLE
WHERE primary_key = :primary-key

IF column1_new not = column1
EXEC SQL
UPDATE A_TABLE
SET column1 = :column1_new
END-EXEC
END-IF

IF column2_new not = column2
EXEC SQL
UPDATE A_TABLE
SET column2 = :column2_new
END-EXEC
END-IF

2) Break up the monolithic table(s) in to many tables with a smaller numbers
of columns, where the columns in each table are more 'tightly related' in
usage then simply being related to the same primary key, and then do a
single SQL UPDATE for all columns that could have changed, even if they
haven't all changed. This way it's more likely that no columns for a
particular table require updating, and thus an update to that particular
table would not be required.

3) Can't think of a number three.

I'm guessing that number 2 is more likely the way to go, but I'm really
looking for that 'perfect world' third alternative.

Thanks,
Frank

David gave you some good advice.

I'll just suggest you get some help from an experienced Relational Db
designer.

Meanwhile as you experiment, you might look at your data for some
common things such as:
several fields all containing the same data. For example do you have
several phone number fields?
daytime_phone, fax_number, nighttime_phone, dialin_number, ...
Those should go into a table of their own and become multiple rows in
that table.

TABLE PHONE
owner
phone_number
type /* FAX, HOME, WORK, MODEM, CELL */

the Primary key would be the combined (owner, phone_number).

I just thought a specific example might help you right away.

Ed

.

Relevant Pages

  • Re: gc buffer busy and rac testing
    ... "SQL ordered by cluster wait time" - this will probably show you ... the segment statistics "'Segments by Global Cache Buffer Busy Waits " ... Not what I'd hope to see in the simplest case - unless the "simple query" ... Is the number of waits in the "segments by GC buffer busy waits" similar ...
    (comp.databases.oracle.server)
  • Server on DMZ
    ... I have a Sonicwall Pro with my IIS server on the DMZ and it needs to ... I need to move the SQL on the same segment. ... If I put SQL on the DMZ ...
    (microsoft.public.sqlserver.security)
  • SQL Instruction
    ... I'm trying to do a consult in ms access from excel, ... segment of SQL doesn´t work: ... m and yyyymm, I have already put "m", 'm' but it ...
    (microsoft.public.excel)
  • Re: DAP & SQL 2000
    ... I imported everything I had in Access to SQL, ... everything I imported has the owner "quote" ... Even tho I imported 3 tables I only use 1 (which does have a primary key) ... > on the Data Outline view and, in the case of the source, has the owner be ...
    (microsoft.public.access.dataaccess.pages)
  • Re: [PATCH] shmtcl SHM_LOCK perms
    ... > You may be neither the owner, nor the creator of a segment but have read ... and if securing sensitive data against pageout were the only reason ... that the most natural restriction is to owner or creator - ...
    (Linux-Kernel)