Question Re: Revert BLOB to VARBINARY in Technology Q&A

Revert BLOB to VARBINARY

nicholas_chang — Fri, 13 Apr 2018 02:42:40 GMT

Hi All,

From below notes, we can convert VARBINARY to BLOB, but can we revert the change back to VARBINARY after converted to BLOB?

2220627 - FAQ: SAP HANA LOBs

2375917 - How-To: Converting SAP HANA VARBINARY columns to LOB

Reason of asking is we would like to convert the CLUSTD column from VARBINARY to BLOB as it consume huge amount of HANA memory (200++GB) and afraid we need to reverse it incase of any query performance degradation and implication...

Your kind input is very much appreciated.

Thanks!
Nicholas Chang

Re: Revert BLOB to VARBINARY

lbreddemann — Fri, 13 Apr 2018 04:22:56 GMT

Note 2375917 makes it clear that a copy of the old table structure with VARBINARY has to be kept until the conversion success including performance aspects has been verified. Instead of converting back and forth that should be the fallback option.

Also, this conversion effort, as any other changes, should be done on DEV and QA systems before the production system. If there's any doubt about the success of the change in either of these systems, don't push it to production.

Finally, yes it is possible to change data from LOB to VARBINARY as long as the size of the data fits into VARBINARY fields.

Re: Revert BLOB to VARBINARY

nicholas_chang — Mon, 16 Apr 2018 13:17:49 GMT

Hi Lars,

Thanks for the reply. Yes, we will first do it on DEV, in fact, the POC system.

Just want to find out the option and its possibility.

Would you mind to shed some light on how to convert BLOB back to

VARBINARY?

Thanks!

Re: Revert BLOB to VARBINARY

lbreddemann — Mon, 16 Apr 2018 22:16:14 GMT

That can be done with the same command as the VARBINARY to LOB conversion:

ALTER TABLE ALTER <column>

E.g.

select column_name, data_type_name
from table_columns 
where table_name='RAWSTUFF';
/*
COLUMN_NAME DATA_TYPE_NAME
DATA        BLOB          
ID          BIGINT        
*/

select * from rawstuff;
/*
ID  DATA
1   Àÿî 
*/

alter table rawstuff alter ( data VARBINARY (4000));

/*
COLUMN_NAME DATA_TYPE_NAME
DATA        VARBINARY     
ID          BIGINT        
*/

select * from rawstuff;
/*
ID  DATA  
1   C0FFEE
*/

And with that, I'm getting my second coffee this morning 🙂

Cheers,

Re: Revert BLOB to VARBINARY

nicholas_chang — Mon, 23 Apr 2018 07:50:44 GMT

Thanks for the example! awesome!