Moving SLOB columns to another sbspace
Tatiana Flores wanted to size each smart-blob (CLOB/BLOB) column in order to move them to separate sbspaces for parallel backups. Jacques Renaut suggested summing dbms_lob_getlength(column) for columns under 10 MB, noting the docs wrongly show dbms_lob.get_length. Luis Marques suggested modifying the demo program get_lo_info.ec and using ifx_int8toasc to avoid overflow. No confirmation of outcome.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello everybody! We want to move several SLOB columns(CLOB-BLOB) from different tables whom reside in a same sbspace to a new sbspace for each table for backup purpose (to launch parallel backups and reduce the time takes the backup), for this goal, we want to know the approximately size is occupying each SLOB column to request the several sizes we need to create the smart blob spaces.. any ideas to calculate the space I need for each column? Thx in advance.
Original post: Hello everybody! We want to move several SLOB columns(CLOB-BLOB) from different tables whom reside in a same sbspace to a new sbspace for each table for backup purpose (to launch parallel backups and reduce the time takes the backup), for this goal, we want to know the approximately size is occupying each SLOB column to request the several sizes we need to create the smart blob spaces.. any ideas to calculate the space I need for each column? Thx in advance. Response: If your smart blob columns are < 10Mb (individually) you could use some of the provided database extensions. Specifically the DBMS_LOB package. Check this url: https://informix.hcldoc.com/12.10/help/index.jsp?topic=%2Fcom.ibm.dbext.doc%2Fid s_dbxt_530.htm The function you would be interested in is the get_length. The documentation is incorrect and indicates that you would use it like this "dbms_lob.get_length", when it actually would be "dbms_lob_getlength". So I think you could do something like the following to get the total amount of bytes for all the smart blobs for that particular column: select sum (dbms_lob_getlength(<clob/blob column name>)) from table; Jacques Renaut HCL Informix Advanced Support
Another alternative is looking at the demo esql programs. Namely "get_lo_info.ec". You can modify it to retrieve the info you need. Check "https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.esqlc.doc/ ids_esqlc_0860.htm" In my toy linux system I had to change the function "ifx_int8toint" to "ifx_int8tolong" (and correct the variable types because of it), since the size of my test blobs was overflowing. There might be other ways to avoid the overflow (like making esql assume that an int is 4 bytes and not 2). Luis Marques
And there is no need to be messing around with converting int8 values to int4 or int2 if you are not doing the sums in the program. Just use "ifx_int8toasc" to convert the value into an array of chars, print it, then use some other tool to process the output. Luis Marques