Have you ever had a scenario where the DB2 objects in your production and test environment were not in sync ? Well, recently I encountered one such problems during a simple data refresh from production to test. The load job failed with the error message FIELD 'COL_NAME' IS NOT DEFAULTABLE.
Here is the entire error message :
09:44:04.12 DSNURWI - COLUMN_010 POSITION(389:393) DECIMAL NULLIF(394)='?')
09:44:04.12 DSNURWUF - FIELD 'COLUMN_010' IS NOT DEFAULTABLE
COLUMN_010 is the column name of the DB2 table. It so happened that the COLUMN_010 was defined as WITH DEFAULT NULL, dataype - DECIMAL(3,0) in production, but the column definition in test was NOT NULL WITH DEFAULT . Now we know why this error occurred.
For my application team, they were not concerned with the data in COLUMN_010 - All I had to do is remove the column from the LOAD punch card and submit my LOAD REPLACE job as this column was the last column of the table, pretty much easy this way. The job ran fine. But hey, you will have to always brings subsystems to sync - Drop and Re-create with COLUMN_010 defined as WITH DEFAULT NULL will be the ultimate solution to bring the test table in sync with production.
Note :
If you plan to use the platinum strategy it will allow you to retain the data by replacing high value ( x'FF') which is the default for DECIMAL dataype with NULLS ( x'00') , here is how the strategy punch card will look for COLUMN_010 if you plan to change from NOT NULL to NULL's -
,COLUMN_010 POSITION( *)
DECIMAL NULLIF( 10) = X'FF'
)
The number 10 is just a counter/identifier for the platinum strategy to keep track of NULL columns, you will have to retain its significance by not modifying this number in the SYSPUNCH
No comments:
Post a Comment