0

I am working on a data upgrade issue.

A new column has been added to our Existing DB2 table.

In order to upgrade Client's DB2 table, I am unloading the data from the existing DB2 table with a constant value for the new column(lets say D of type SMALLINT) as shown below:

UNLOAD TABLESPACE XYZ.ABC                                       
DELIMITED COLDEL X'2C' CHARDEL X'22'
PUNCHDDN SYSPUN01                                                       
UNLDDN   SYSREC01 CCSID(367)                                                      
FROM TABLE DB2BG111.table_name                                             
(                                                                       
  A                              POSITION(*) CHAR(2)           
, B                              POSITION(*) SMALLINT          
, C                              POSITION(*) CHAR(4)           
, D (new column)                 CONSTANT X'0000'              
)

While unloading the data, we are using following unload parameters:

X'2C': Column Delimiter            
X'22': Character Delimiter            
CCSID(367): EBCDIC to ASCII conversion

Problem tham I am facing is, DB2 is adding character delimiter X'22' after the value of the column D in the unload record.

Please note column B is an existing column and is declared as SMALLINT, DB2 not adding character del for this in the unload record.

This may be happening because new column D added here is not declared as a SMALLINT and hence it is not treated like an SMALLINT and DB2 is ending a char del for this in the unload record.

I am just looking for a way to get out of this situation, i do not want my new column D to be character delimited in the unload record.

Any suggestions to overcome this would be highly appreciated.

Paul Vernon
  • 3,818
  • 1
  • 10
  • 23
Manish
  • 649
  • 2
  • 7
  • 10
  • Can you tell the unload what the datatype of the constant is? If `UNLOAD` thinks it is a string, it will presumably add Character Delimiters around it. – Paul Vernon Oct 03 '19 at 08:43
  • Could not really find any to declare the new column as SMALLINT and assign a constant value to it. – Manish Oct 03 '19 at 08:58
  • 1
    @Manish It's unclear why you need to unload. Usually it's wiser, safer, faster to add columns without unloading. Regardless of that, the docs state that the CONSTANT can only be string type. Either avoid doing unload , or make a temporary table with the new column of type SMALLINT and copy the data into the temporary table and unload from the temporary table. – mao Oct 03 '19 at 10:56

1 Answers1

0

One option would be to use DSNTIAUL to perform the unload, and select the constant in the select list.

SELECT A, B, C, CAST (0 AS SMALLINT) AS D FROM TABLE DB2BG111.table_name;

Best regards, Patrick Bossman

Patrick Bossman
  • 139
  • 4