We are migrating the Sybase database to DB2 database. As the data has DBCS characters, we are changing the char/varchar datatypes to nchar/nvarchar types. As part of this, the function TO_CHAR is changed to TO_NCHAR() assuming that the behaviour of both is same. But it is observed that while creating a table with TO_NCHAR() gives the column as NOT NULL whereas creating a table with TO_CHAR() gives the column as NULLABLE.
Source table:
CREATE TABLE tt_source
(
StartDate TIMESTAMP NULL,
enddate TIMESTAMP NOT NULL
)
Script used to create target table:
CREATE TABLE tt_target AS
(
SELECT
TO_CHAR(Startdate,'mm/dd/yyyy') StartDate_char
,TO_CHAR(EndDate,'mm/dd/yyyy') EndDate_char
,TO_NCHAR(Startdate,'mm/dd/yyyy') StartDate_Nchar
,TO_NCHAR(EndDate,'mm/dd/yyyy') EndDate_Nchar
FROM tt_source
) DEFINITION ONLY ;
The output is
Column Type Remarks
StartDate_char Null
EndDate_char NOT NULL
StartDate_nchar NOT NULL "This should have been NULLABLE,
but to_nchar() changed to NOT NULL column"
EndDate_nchar NOT NULL
Is there a way to retain the nullability of the column while using TO_NCHAR() function.
are you generating those statements or write them manually?
if you write them manually, why not just write a regular create table (instead of create as with the only definition clause)
did you try
cast (colname as nchar) ?