FORMAT_UNITS Function

The FORMAT_UNITS function can interpret strings that specify a number and the abbreviated names of units of memory or of mass storage.

This built-in function can accept one, two, or three quoted string arguments. You can invoke FORMAT_UNITS in SQL statements that process size specifications expressed by standard abbreviations for bytes or for larger units (such as kilobytes, megabytes, gigabytes, and so forth) of memory or of mass storage.

The FORMAT_UNITS function can also be called internally in the sysadmin database by the SQL administration API ADMIN and TASK functions, which are described in the HCL OneDB™ Administrator's Reference.

FORMAT_UNITS Function

1  FORMAT_UNITS (
2.1  ' number units '
2.1? 
2.2.1  ' number '
2.2.1 number
2.1 ,
2.1 '
2.1 units
2.1 '
2.1?  ,
2.2.1  ' precision '
2.2.1 precision
1 )
Element Description Restrictions Syntax
number Expression that evaluates to the number of storage or memory units Must be a literal number or a quoted string specifying a number that can be converted to FLOAT Expression
precision Integer number of significant digits to return from number Must be a literal number or a quoted string specifying an integer Expression
units Abbreviation of a unit of storage or memory; the default is 'B' (for bytes) Must begin with 'B', 'K', 'M', 'G', 'T', 'P', or 'PB' (or the lowercase forms of these letters). Any trailing characters are ignored. Quoted String

This built-in function can accept one, two, or three arguments. The returned value is a character string that shows the specified number and an appropriate format label that shows the storage units. If you specify a precision as the last argument, the number is returned with that precision. Otherwise, the number is formatted to precision 3 (%3.3lf) by default.

The same notation also applies to arguments to all SQL administration API ADMIN and TASK commands (except for commands that emulate the Enterprise Replication cdr utility) that specify sizes of memory, of disk storage, or of address offsets:
Notation
Corresponding Units
'B' or 'b'
Bytes (= 2 to the power 0)
'K' or 'k'
Kilobytes (= 2 to the power 10)
'M' or 'm'
Megabytes (= 2 to the power 20)
'G' or 'g'
Gigabytes (= 2 to the power 30)
'T' or 't'
Terabytes (= 2 to the power 40)
'PB'
Petabytes (= 2 to the power 50)
'P'
Pages (= 2 kilobytes or 4 kilobytes, depending on the base page size of the system)
The initial letter in the unit specification ('B', 'K', 'M', 'G' or 'T') determines the units of measure, and any trailing characters are ignored. An exception, however, is if the initial letter 'P' (or 'p') is immediately followed by 'B' or 'b' in the string, because in this case the string is interpreted as petabytes. Any other string starting with "P" (such as "PA", "pc", "PhD", "papyrus" and so forth) is interpreted as specifying pages, rather than petabytes.

If one argument provides both the number and units specifications, HCL OneDB ignores any whitespace that separates the number specification from the units specification within the same argument to the FORMAT_UNITS, or SQL administration API ADMIN or TASK functions. For example, the specifications '128M' and '128 m' are both interpreted as 128 megabytes.

The following examples invoke the FORMAT_UNITS function with a single argument: 
EXECUTE FUNCTION FORMAT_UNITS('1024 M');
The following character string value is returned.
(expression)
1.00 GB 
SELECT FORMAT_UNITS('1024 k') FROM systables WHERE tabid=1;
The following character string value is returned.
(expression)
1.00 MB 
SELECT FORMAT_UNITS(tabid || 'M') FROM systables WHERE tabid=100;
The following character string value is returned.
(expression)
100 MB
The following examples show calls to the FORMAT_UNITS function with two arguments: 
EXECUTE FUNCTION FORMAT_UNITS(1024, 'k');
The following character string value is returned.
(expression)
1.00 MB 
SELECT FORMAT_UNITS( SUM(chksize), 'P') SIZE, 
   FORMAT_UNITS( SUM(nfree), 'p') FREE FROM syschunks;

size  117 MB
free  8.05 MB
This query returns the string values size 117 MB and free 8.05 MB.
The following examples show calls to the FORMAT_UNITS function with three arguments: 
EXECUTE FUNCTION FORMAT_UNITS(1024, 'k', 4);
The following character string value is returned.
(expression)
1.000 MB 
SELECT FORMAT_UNITS( SUM(chksize), 'P', 4), SIZE, 
   FORMAT_UNITS( SUM(nfree), 'p', 4) FREE FROM syschunks;

size  117.2 MB
free  8.049 MB
This query returns the string values size 117.2 MB and free 8.047 MB. These results differ from the previous example of a query only in their non-default precision, which the last argument to FORMAT_UNITS specifies.