Pefomance Questions

1) it is better to use a single update - it will save you time, io and log space consumption
2) it is better to update in blocks, updating in a bulk cause a very high impact on the log, and if your program will encounter an error, all work will be rolled back. using blocks you can restart your program if it fails. Consider locking the entire table before you begin in order to reduce overhead caused by many lock request
3) yes.
4) i think the best approach would be to create some user defined function that does the operation for you, and then just call it in the update. this way the data does not have to come back to your application at all - will save significant amount of time
update table
set column = my_func(column)

or,
you can save yourself all this trouble and buy a tool that does this,
for example, this is a link to an ibm certified tool that can do this, and it will ofer you more features for example, if you have foreign keys, it will maintain your data integrity
http://www.orbiumsoftware.com/products/data-masking-scrambling-jumbledb-express
you can download a free version in order to try it first

there are more tools in the market that have this functionality, but this is the cheapest one I know of, and it sounds like your needs are rather basic.
maybe the it will cost you more to code and test your cobol program than buying such a tool

When you have a full table to update, there is although another way : EXPORT/IMPORT

You export all columns of the table with modified columns (using a function as stated in the previous post).
You use the LOAD /REPLACE command to massively re-import the full table.

I think this offers 2 benefits :
-performance : Exporting does not require LOG files, Loading already can handle large number of rows and regular (#rows based) commit
-non fragmented table : Loading all table at once means no more rows can be fragmented

Thank you all for the inputs. These are very good tips that I am getting.

I will leave this question open for a couple of days just in case anyone else wants to chip in.

>  As of now the database size is between 200 - 300 GB. The number of distinct records that I have in a table is a minimum of 1.5 million records.

This makes it sound as if you're actually talking about a database with some number of tables. Are you updating /a/ table or are you updating a possibly unknown number of tables due to cascading relationships? Are these DDS native physical files or DML SQL tables?

Tom

I will be updating a set number of tables. The realtionship between tables have been idenitfied and I will have to run the update on all tables which have the field that I am scrambling.

Right now, the program is going to work with DB2 tables, but this program will have to be leveraged to work on physical files too.

On the AS/400, a physical file is a DB2 table, and vice-versa.  There are some relatively minor functional differences in capabilities and performance depending on the mechanism used to create the object (SQL vs. DDS or other "native" method).

Gary Patterson

I provided first a way to reload the full table.

Now I am wondering about just updating in place the rows : that should be faster in your case because:
-You can keep each column it's original size
-You can drop any index concerned by those columns and recreate them after the update

So, I will:
-Create FUNCTIONS to render those modifications
-Create a basic UPDATE loop to ensure regular bulk commit
==> That will be faster than anything else and won't require lots of additional space (LOGs only)

First a quick correction:  Optimum block size for disk I/O with newer RISC hardware is 128K, older CISC machines are 32K.

http://www-912.ibm.com/s_dir/slkbase.nsf/1ac66549a21402188625680b0002037e/d6738e1cd37e1f33862565c2007cef79?OpenDocument&Highlight=0,Block,Length

The method I described earlier will generally  be significantly faster in terms of I/O and runtime than an in-place updat, since opening a single file for both input and output under DB2/400 disables record blocking.

I did a rework on a data conversion for a distribution company  several years ago.  They had hired a programmer to come in and develop custom RPG programs to move data from their old warehouse system to a new system.  Due to production schedules, the conversion needed to fit into a ten hour window over conversion weekend.  Unfortunately, test runtimes were coming in much higher - over 40 hours for dozens of files and millions of records on a relatively small AS/400.

We applied the rules that I have listed above on the largest files, making almost no other changes.  I hardly had to touch the actual conversion program code at all, and the runtime, including index rebuilds, immediately dropped to between 3 and 4 hours.  Not bad for four hours of effort.

Why so drastic a difference?

The biggest difference is record blocking.  On DB2/400, an open data path (ODP) can block records only if a specific set of rules are met, and one of the rules is that the file be opened for Input, or Output, but not both.

Let's say we block to 128K (128*1024 = 131,072 bytes), and for convenience sake say each record is 131 bytes long, and there are a million records.  

This gives us a blocking factor of 1000 records.  When using blocked I/O (1,000,000 logical reads to one file, 1,000,000 logical writes to another), a program will make 2,000 physical I/O requests, since it will be retrieving and storing blocks of 1,000 records at a time.

When using non-blocked I/O, a program will need to make 2,000,000 I/O requests to process the same number of records.  This is very inefficient, especially since the larger blocked I/O requests only take a small additional amount of time over the small non-blocked requests.  This is because so much of the I/O time is spent waiting on queue for your turn to get I/O, for the disk arms to get into position, for the disk to spin, and all of the other components in the chain from IO request to response.  Once you are in position to read a chunk of data, it is more economical to go ahead and grab the data that trails behind it on disk, since you have already paid the big penalties.  The same economies happen on the write side of the transaction, especially if space for the initial size of the file is allocated ahead of time.  also, when you are performing blocked I/O ont eh read side, the DBMS and OS are better able to predict and anticipate your future data requests, and may be able to cache the data your program needs -further speeding up your I/O requests.

Stopping journaling saves the CPU, memory, and IO cost associated with logging each change out to disk.

Dropping indexes and views alleviates the system from having to do synchronous updates to those access paths as each record is changed or written.  The system can then do   bulk index rebuilds (which are much faster than doing them one record at a time on the fly, since the system can, among other things, take advantage of blocking of writes to the index) after the new file is created.

- Gary Patterson

What happen if you explicitly LOCK a table for an inplace UPDATE in DB2/400 ?

That should rocks !

Well, with a commit level of *NONE, you get an exclusively locked table that cannot be accessed by any other job on the system.

As far as I can recall, you still don't get to perform record blocking on updates, though.

In a conversion scenario, we are probably reading a static, unchanging table in a single scan while (hopefully) no other processes access that  table.  Then we are writing out a new table, again that is probably not going to be in use by other processes.

With zero contention on either table, and no competing locks, I don't know if allocating the table exclusively would make any difference at all.  Can't hurt, though, and it is a good mechanism to ensure that there are no accidental updates while the conversion runs.

I do think this that allocating (and later deallocating) the file is a good item to include in the list.  I'd allocate right before stopping journaling, and deallocate right after starting it back up again.

- Gary

The point is that you have NO allocation needed IF you keep all column datas their initial size.

I would;
-stop journaling
-drop columns concerned indexes
-exclusively lock each table
-loop an update committed every 100000 rows
-relock table if commit unlock it
-recreate indexes

If you stop journaling, then there is no "committed every 100000 rows". Commitment control requires journaling.

Tom

I am not sure you can really "stop journaling", I though of redirecting to "nul" was one way to do it...thus requiring some commits

@BigSchmuh:

You might be right, after I read back through to see a confirmation that these are "DB2 tables" and differentiated from "physical files" in the same sentence. I've never actually tried to cease journaling of actual tables in a SQL schema under i5/OS. SQL tables that are merely created in a 'library' aren't restricted and journaling can be stopped at any time.

But "redirecting to "nul"" is not appropriate under i5/OS. It's a different animal under the covers.

Tom

Hi Gary,

Thanks a ton for all this valuable inputs. You mentioned in your first post that I "Perform all of your updates to a given row in a single pass.   You only want to read and write to the file once, since waiting on I/O is by far the biggest component of runtime. ". Can you elaborate a little on this?

After the discussion that I have had with my IT team, we have narrowed down our scrambling fields. For most of the fields that we have idetntified, we are going to update them with a constant value in all records. For e.g., phone number fields in all tables will be updated to all 9's. For such fields, I am just planniing to use the source table as the target table. Because I dont intend to read from the source table, I am just going to go updating in blocks.

There is another kind of field which I need to read, manipulate and update. For those fields, I am going to create a new table (similar to what you have recommended). How would you recommend I update the fileds?

1) Should I read from the source table, copy the block onto an array, make the changes and then write into the target file. (or)
2) Should I read from the source table, write into the target table in blocks and then run an update on the target table.

Thanks for your time.

Regards
Ali

@BigSchmuh,

You know that we are talking about DB2 on AS/400 here, right?  It has some significant  differences from DB2 mainframe and DB2 LUW.  Journaling is either on for a given table, or it is off.  There is no such thing as redirection of journal data to alternate devices on DB2/400.  Journal data gets logged to a journal receiver (*JRNRCV) object, or journaling is off for a given table, period.

And to reiterate what Tom is saying, if journaling is off, then you can't use any commitment definition other than *NONE.

@Tom,

There is no problem starting and ending journaling for DB2/400 files in an SQL collection/schema.  Same rules apply as any other physical file: Stop anytime

@Ali,

Elaboration:  Reading data from disk is extremely time-consuming.  Writing to disk is extremely time consuming.  So much so that it is almost the only thing that counts when you are measuring runtime performance of batch programs (communication IO can be another significant cost, but that doesn't apply in this particular case).

If runtime is your principal concern, you want to develop batch programs that only read each input table once (and read them using a blocking factor that produces as close to a 128K block as possible without exceeding 128K), and only write each output table once (using the same blocking rules).  You never want to update an existing table if you have to touch every row, since you can't block UPDATE I/O.

On your "update with a constant" process:

• If you want to minimize IO and hence, runtime, you HAVE to block read from one table and block write to another.  UPDATing the same table will force unblocked I/O, and increase runtime by a significant factor (depends on record length - the shorter the record, the worse relative performance will be, in general.  Test with a small subset (100K records, for example) to see if the runtime payback is worth the effort.
   

• Using an UPDATE against your source table is liable to cost you significantly in runtime, since the IO can't be blocked. 
• Depends on your goals and restrictions;  coding time and effort versus runtime. 
• Read source, load  array, make changes, then write to target table probably gets you the least IO activity (unless the array is going to be so large it has to be paged in and out), but requires more complex coding, obviously.  Assuming you block read and block write, this is probably far faster than the other alternative.
   
• Read source, write, and update gets you 4 IOs for each row.  The read and write passes can be blocked IO but the Update pass will be non-blocked and very expensive. 

Thanks for taking the time to reply Gary. I will pick up one of my larger tables and do a POC for both approaches.

If you can turn off the journaling that easily, you can issue a single large UPDATE taking care of updating every concerned column by modified value of the exact same length.
An exclusive LOCK on the table before this UPDATE should lower down to a minimum the locking overhead.

Yes, you can just issue an SQL UPDATE.  

For that matter, you can also open the table up in the editor of your choice, and manually edit each record.  

The two techniques have one thing in common - neither one will get you the best performance.

If you re-read the original question, it is:  "How do I do this with the least performance impact?"  

Even with journaling disabled, and indexes deleted and recreated, an UPDATE is still going to get you unblocked I/O.  To update a million records with constant values only, you'll do 1,000,000 IOs.  If you do any WHERE logic or if your updates refer to another field, or if you do anything else that forces the optimizer to do an INPUT prior to the UPDATE, you will do 2 million IOs to get the job done.  

To get the best performance - which was the original question - you have to mimimize I/O.  That means doing a blocked read and a blocked write, since I don't know of any way to do a blocked update (using SQL or native I/O).  If there is a way to do a blocked update, I'd really like to see it, since that would be very handy on occasion.

Anyway, this means you need to to a blocked read from one table, and a blocked write to a different table, stop journaling, drop indexes, etc.  exclusively allocating both tables isn't a bad idea either, but generally conversions like this are done against idle tables anyway ( I suspect that we are converting production data to test data in this particular project, so contention probably isn't an issue.)

Depending on record size, you may be able to reduce to I/Os by a factor of 10, 100, or 1000 or more using this technique, and, all assuming you don't hit other system limitations, you'll improve runtime by a significant factor.

Now, if a simple UPDATE runs in an acceptable amount of time,  then there really is no point  in going further, but that was not the question.  

The question was "how do I do this with the least performance impact", and there is almost no circumstance that I can come up with where doing an SQL UPDATE is the lowest-cost method (exception possibly being extremely large record lengths, and even then it is likely  at best, a tie with the two-table method.)

- Gary Patterson

I have no experience of DB2/400 but a pretty large one on udb.

Is DB2/400 such a piece of crap that there are no lazy writer on it ? Or is the DB2/400 such a poorly optimized app that it does not group io to render those on a like-sequential approach ?

The best performance then should be to switch to a better os and dbms platform ! A 2U bi-nehalem 64GB Ram with DAS attached SSD for example will outperform anything on this 400 world for sure !

bhagatali,

BigSchmuh says: "I have no experience of DB2/400...".

Since he brings it up, I have over 20 years of DB2 on AS/400 experience.

- Gary Patterson

Can you keep your focus on the technical subject and let us know what is the problem with the db2/400 lazy writer ?
I would really like to trust your "best performance" solution...against the single UPDATE one...but i just don't understand what can avoid db2/400 to be efficient in massive writes like that.
Block writes just look like sequential access...and even normal raid hba can render that behaviour.

Thanks a lot for your inputs.