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

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

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.

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

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.