Joined: 01 Feb 2007 Posts: 123 Location: Hyderabad
I have a field FULL_NAME of 100 CHAR in my table. This field has currently more than 30 bytes of data. Is there a query that we can truncate data in this table itself for all rows that have more than 30 bytes?
This is only one time query to update current data in table as the fix is going to be implemented in the program not to insert more than 30 bytes.
1. Check with DBA, I am not sure if you can bypass truncate warnings.
2. Create new table of the desired attributes and load the data from old table , drop the old table and Rename the new table to old one.
3. Take a full back up first (just in case to rollback), Unload this table and use substring in select to restrict it to 35 and then do a Load Replace with this unload data.
4. You can do an UPDATE and SELECT in one query for the rows, in the SELECT use substring to the length of 35.
5. If this table is not used in Multiple places then you can modify the SELECT to use substring to 35 instead of truncating the Db2 data itself.