vjmig
asked on
DB2 to_nchar() functions modifies the behaviour of coulmn from NULL to NOT NULL
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/y
,TO_CHAR(EndDate,'mm/dd/yy
,TO_NCHAR(Startdate,'mm/dd
,TO_NCHAR(EndDate,'mm/dd/y
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.
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/y
,TO_CHAR(EndDate,'mm/dd/yy
,TO_NCHAR(Startdate,'mm/dd
,TO_NCHAR(EndDate,'mm/dd/y
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.
ASKER
We are not writing them manullally. We are migrating 2500 stored procedures using some third party tool that has converted to use TO_CHAR() to similate sybase behaviour. To handle the DBCS characters, we have convetered to_char() to to_nchar().
My question is why to_char() and to_nchar() is behaving differently with respect to nulability.
My question is why to_char() and to_nchar() is behaving differently with respect to nulability.
Hi vj,
I believe that that behavior is a function of your conversion processor. DB2 isn't creating the DDL to define the table.
Can you modify the table after it's created and before the data is loaded? If so, you should be able to recreate the table to meet your needs, or ALTER any column definition that you need.
Good Luck,
Kent
I believe that that behavior is a function of your conversion processor. DB2 isn't creating the DDL to define the table.
Can you modify the table after it's created and before the data is loaded? If so, you should be able to recreate the table to meet your needs, or ALTER any column definition that you need.
Good Luck,
Kent
ASKER
The tt_target table definetion is used to declare global temporary table. This way many temporary tables are declared in our Stored procedures.
CREATE TABLE tt_target AS
(
SELECT
TO_CHAR(Startdate,'mm/dd/y
,TO_CHAR(EndDate,'mm/dd/yy
,TO_NCHAR(Startdate,'mm/dd
,TO_NCHAR(EndDate,'mm/dd/y
FROM tt_source
) DEFINITION ONLY ;
CREATE TABLE tt_target AS
(
SELECT
TO_CHAR(Startdate,'mm/dd/y
,TO_CHAR(EndDate,'mm/dd/yy
,TO_NCHAR(Startdate,'mm/dd
,TO_NCHAR(EndDate,'mm/dd/y
FROM tt_source
) DEFINITION ONLY ;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, we can do that. but that will impact all the stored procedures and all of them around 4000 will need to be modified. I want to trying to avoid this if there is any easy way of doing this.
Also, I did not find any documention stating that to_char() and to_nchar() behave differently apart from character representaion.
Also, I did not find any documention stating that to_char() and to_nchar() behave differently apart from character representaion.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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) ?