IBM DB2 - Do not unload DECIMAL rows exceeding a certain length

Question

I have an IBM DB2 table where I want to UNLOAD data from so that I can load it into another DB2 table.

Both tables have the same columns (and types), except one decimal field.

It is DECIMAL(6) in the source table and DECIMAL(5) in the destination table.

There are many entries in the source table which only use up to 5 digits in the DECIMAL field and only some use up all 6 of them.

What I am going to do is only copy the entries which go up to 5 digits in the source table and drop all others.

Can I do this only using the UNLOAD statement? So having an option which tells the system "unload column 'id' as DECIMAL(5) (although it is DECIMAL(6) in the table itself) and if an entry of that column uses all 6 digits (>99999) do not unload that row".

Also how would you handle the case if it was the other way around? E.g. unload DECIMAL(5) from source and LOAD as DECIMAL(6) in destination

Why am I doing this? Because the destination table is an older version of the table used by older versions of the applications. We will drop support in 6 months, but until then we need to refresh the datasets in it.

With UNLOAD and LOAD I am talking about the UNLOAD and LOAD utilities for z/OS (?) described under e.g. https://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/ugref/src/tpc/db2z_utl_unload.html

I think this is better suited to dba.stackexchange or serverfault — Charles
... why are you wanting to skip some of your source data rows? If they're actually important, shouldn't they be transferred? If they're not important, why can't you delete them? Loading from a smaller source field works just fine - unloading/loading (ie, as if a backup) is traditionally one of the ways to update a table definition. — Clockwork-Muse

Rob Wilson Rob Wilson · Accepted Answer · 2020-01-16T08:37:53

Without knowing your source environment, for Db2 Linux, Unix, Windows the export statement is used with a select statement. So you'd do whatever logic makes sense to turn a DECIMAL(6) into a DECIMAL(5) - including skipping rows that need all 6 digits since there's no way to make them fit into a 5 digit allocation. The actual preferred method in this environment is now to use external tables but they work in a similar fashion.

export to 'myfile.csv' OF del select col1, dec_col6 from thetable where dec_col6 < 100000

OR

create external table 'myfile.csv' using (DELIMITER ',') as select col1, case when dec_col6 > 99999 then 99999 else dec_col6 end from thetable

External Tables
Export

Since you used the word "unload" I suppose you're either using a tool such as High Performance Unload or you are on a different flavor of Db2.

IBM DB2 - Do not unload DECIMAL rows exceeding a certain length

1 Answers