title: Can data and schema be changed with DB2/z load/unload?
body: <p>I'm trying to find an efficient way to migrate tables with DB2 on the mainframe using JCL. When we update our application such that the schema changes, we need to migrate the database to match.</p>
  
  <p>What we've been doing in the past is basically creating a new table, selecting from the old table into that, deleting the original and renaming the new table to the original name.</p>
  
  <p>Needless to say, that's not a very high-performance solution when the tables are big (and some of them are <em>very</em> big).</p>
  
  <p>With latter versions of DB2, I know you can do simple things like alter column types but we have migration jobs which need to do more complicated things to the data.</p>
  
  <p>Consider for example the case where we want to combine two columns into one (<code>firstname + lastname -&gt; fullname</code>). Never mind that it's not necessarily a good idea to do that, just take it for granted that this is the sort of thing we need to do. There may be <em>arbitrarily</em> complicated transformations to the data, basically anything you can do with a <code>select</code> statement.</p>
  
  <p>My question is this. The DB2 <code>unload</code> utility can be used to pull all of the data out of a table into a couple of data sets (the <code>load</code> JCL used for reloading the data, and the data itself). Is there an easy way (or <em>any</em> way) to massage this output of <code>unload</code> so that these arbitrary changes are made when reloading the data?</p>
  
  <p>I assume that I could modify the <code>load</code> JCL member and the data member somehow to achieve this but I'm not sure how easy that would be.</p>
  
  <p>Or, better yet, can the <code>unload/load</code> process itself do this without having to massage the members directly?</p>
  
  <p>Does anyone have any experience of this, or have pointers to redbooks or redpapers (or any other sources) that describe how to do this?</p>
  
  <p>Is there a different (better, obviously) way of doing this <em>other</em> than <code>unload/load</code>?</p>