Expand a field while unloading a table

Pradip kumar Mohanty · New User Joined: 16 Sep 2007 Posts: 33 Location: India

Hi,

Is there any built-in function available to expand a field on the fly while unloading.

Essentially what I need is :
Table-A in Database-A, has one of its filed defined as CUST_AMT DECIMAL(5, 0) NOT NULL.
The same Table-A in Database-B has the same filed defined as CUST_AMT DECIMAL(9, 4) NOT NULL.

I need to load the Table-A in Database-B with Table-A-Database-A's data.

ex: CUST_AMT value in one of the records of Table-A is 12345 it shd be expanded/reformated to 12345.0000 to the unload dataset so that it can fit into the corresponding filed in Table-B.

I wish any DB2 function that I can use with the unload query to accompolish this.

Any help?

Best Regards

Craq Giegerich · Posted: Sun Aug 24, 2008 7:52 am

Have you tried CAST?

Lijo · New User Joined: 24 Aug 2008 Posts: 41 Location: Cochin

Hi,

There could be DB2 solutions available. But what I can think of is as follows:
1) Unload Database A table.
2) Convert Decimal(5,0) field to Decimal(9,4) (you can use DFSORT utility to achieve this, by writing a control card like TO=PD,LENGTH=7). Your output dataset will have a record length 4 more than the input one.
3) Use the output of Step2 to load Database B table.