Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: DB2 v8 on Z/OS: Unload and Reload question‏

    Hi all,

    In our z/OS environment, I need to unload a particular table, move some columns around (like from position 10 to position 2, etc., and reload the unloaded data into the now reconstituted table columns.

    Question, can I just unload, move the columns (retains original name) around, perform reload? Or do I have to map the columns individually to their respective target positions in the "new" table columns?

    I thank you in advance for educating me on this.

    Okonita

  2. #2
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    I think it would be easier to unload the columns in the order required for the new table. From the Unload chapter in the DB2 UDB for z/OS V8 Utility Guide and Reference:

    2.30.2.3.3 Selecting and ordering columns to unload

    Use a field specification list in a FROM TABLE clause to unload specified columns in the listed order. If you omit a field specification list, all the columns in the row are unloaded in the order of the columns that are defined on the table.

    You can specify a format conversion option for each field in the field specification list.

    Here's a link to the manuals:
    http://www-306.ibm.com/software/data...s/v8books.html

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by pagwu
    I need to unload a particular table, move some columns around (like from position 10 to position 2, etc.), and reload the unloaded data into the now reconstituted table columns.
    I would say that the simplest (be it maybe not the most performant) way to do this, is:
    - use plain UNLOAD without specifying the columns
    - drop table & create table with columns interchanged
    - LOAD using the load statement generated by UNLOAD without any change.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why do you want to reorder columns in the first place? It is good practice to explicitly specify all columns in a query (and not use SELECT *). Then you could simply select the columns in the order you want to have them.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2007
    Posts
    72
    Quote Originally Posted by stolze
    Why do you want to reorder columns in the first place? It is good practice to explicitly specify all columns in a query (and not use SELECT *). Then you could simply select the columns in the order you want to have them.
    Knut, I am not able to answer your question because I don't know but the hazy response that I got when I asked was that it improved some part of our Business object response time. Supposedl;y tested this in a test environment. The request is coming from some mighty ETL tool user who convinced my boss who nicely ordered me to do it - emphasis ordered.

    Okonita

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, if it is an order that you do that, it will be hard to avoid it, I guess. Maybe you can get those test results as well as a full description of the test harness to verify yourself the impact that a reordering of the columns had.

    ETL tools may indeed have some minor benefit from properly ordered columns. But if that is really measurable and that other things would not have a more significant performance impact is really open for debate.

    Does the "mighty ETL tool user" have access to the database and can verify the schema? If not, then you could rename the table, create a view with the requested column order and the name of that table. Thus, you would "fake" the correct column order the easy way. If this is not an option, you will have to re-create the table as was described by others.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •