Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query to Truncate value in table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 123
Location: Hyderabad

PostPosted: Mon Aug 12, 2019 3:26 pm    Post subject: Query to Truncate value in table
Reply with quote

Hi,

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.

Thanks,
Bhaskar
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2299
Location: NY,USA

PostPosted: Mon Aug 12, 2019 4:58 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1330
Location: Israel

PostPosted: Mon Aug 12, 2019 7:40 pm    Post subject: Reply to: Query to Truncate value in table
Reply with quote

Code:
UPDATE table
SET FULL_NAME = SUBSTR(FULL_NAME,1,30)


Please backup table before !
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Query on JCL abdulrafi DFSORT/ICETOOL 5 Wed Oct 16, 2019 7:02 pm
No new posts LOAD JCL for db2 table, taking too lo... Somnath Giri DB2 5 Mon Oct 14, 2019 3:35 pm
No new posts Adding FIND to ISPF Panel display of ... Harold Barnes TSO/ISPF 3 Mon Oct 07, 2019 5:46 pm
No new posts DB2 Query runs very long John F Dutcher DB2 21 Wed Aug 21, 2019 11:44 pm
No new posts RACF RANGE TABLE Martin Wickenden All Other Mainframe Topics 1 Mon Jul 08, 2019 9:06 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us