Smart Blob Information DataBlade Module (sblob_info.1.5)

This DataBlade module uses the built-in __myErrors__ trace class for debug messages. SblobDebug() sets the trace level of __myErrors__.

Trace messages get output to /tmp/session_id.trc if the trace level for the trace class is greater than the threshold in the DPRINTF statement in the source code. So far, all functions specify a threshold of 20. So calling SblobDebug() with a value >= 20 causes messages to be output to the trace file.

SblobDebug (integer) returns void

SblobStatSize (mi_lo_stat_size)

  execute procedure SblobDebug(30);

SblobStatSize() calls mi_lo_stat_size() to determine the size of a smart blob.

SblobStatSize (blob) returns int8

SblobSbspace (mi_lo_specget_sbspace)

Above, we inserted the stdio.h and stdlib.h include files into the sblob_test table. In a DB-Access window execute the following query:

  select description, blob_column, SblobStatSize(blob_column)  
from sblob_test;

In another window, do the following commands at the operating system prompt to list the file size:

  ls -l /usr/include/stdio.h
  ls -l /usr/include/stdlib.h

(Note that on NT you'll need to modify the path to the standard C include files to point to the copies in your VC++ installation.)

Do the sizes match? (They should.) You can also check the size of each smart blob with the oncheck command:

oncheck -pS sbspace

The size is output in the Size (Bytes) field.

SblobSbspace() calls mi_lo_specget_sbspace() to get the name of the smart blob space in which a smart blob is stored.

SblobSbspace (blob) returns lvarchar

In a DB-Access window execute the following query:

  select description, blob_column, SblobSbspace(blob_column)
  from sblob_test;

This example is more interesting if you do not use the default smart blob space defined by the SBSPACENAME parameter in the onconfig file.

SblobMaxSize

SblobMaxSize() calls mi_lo_specget_maxbytes() to get the maximum size specified for the smart blob.

SblobMaxSize (blob) returns int8

In a DB-Access window execute the following query:

  select description, blob_column, SblobMaxSize(blob_column)
  from sblob_test;

If no maximum size has been set for the smart blob, the result will be -1. You can verify the result with the oncheck command:

oncheck -pS sbspace

The max size is output in the Size Limit field.

SblobEstSize

SblobEstSize() calls mi_lo_specget_estbytes() to get the estimated size specified for the smart blob. The server preallocates space of this size when the smart blob is created, and subsequent updates to the smart blob fill up this space. Once this space is filled, the server adds new extents as needed.

SblobEstSize() (blob) returns int8

In a DB-Access window execute the following query:

  select description, blob_column, SblobEstSize(blob_column)
  from sblob_test;

Currently, if no estimated size has been established, the value returned is the actual size. You may notice, however, that the oncheck command outputs a NULL in the Total Estimated Size field in some releases:

oncheck -pS sbspace

SblobExtSize

SblobExtSize() calls mi_lo_specget_extsz() to get the extent size (EXTENT_SIZE) specified for the smart blob.

SblobExtSize (blob) returns integer

In a DB-Access window execute the following query:

  select description, blob_column, SblobExtSize(blob_column)
  from sblob_test;

A return value of 0 means that an EXTENT_SIZE hint was not provided when the sbspace was created. In this case, the system will figure out the extent size itself.

SblobLog

SblobLog() mi_lo_specget_flags()

mi_lo_specget_flags()

SblobLog (blob) returns boolean

In a DB-Access window execute the following query:

  select description, blob_column, SblobLog(blob_column)
  from sblob_test;

A return value of "t" means that the user data is logged; "f" means it is not logged.

SblobCreate

SblobCreate() creates a smart blob, and allows specifying some smart blob attributes.

The purpose of this function is to create smart blobs with different characteristics, which can be monitored with functions in this blade as well as the oncheck and onstat -l commands. One of the goals is to understand and demonstrate the affects of smart blob operations on smart blob spaces and logical logs.

SblobCreate (sbspace, size, logging, estbytes, maxbytes) returns blob

sbspace	String containing the name of the smart blob space, which gets passed to the call to `mi_lo_specset_sbspace()`. If "default" is specified, `mi_lo_specset_sbspace()` is not called, causing the smart blob to be created in the default `SBSPACENAME` defined in the onconfig file. SblobCreate() does not check the validity of sbspace name.
size	int8 value >= 0, specifying the length (in bytes) of the smart blob to be created. This space gets filled with `X`s.
logging	String containing "log" or "nolog"; any other value causes an error to be raised. The parameter sets the user data logging mode (`LO_LOG` or `LO_NOLOG`) for the call to `mi_lo_specset_flags()`.
estbytes	int8 value specifying the estimated size of the smart blob for the call to `mi_lo_specset_estbytes()`. If set to `<= 0`, `mi_lo_specset_estbytes()` is not called.
maxbytes	int8 value specifying the maximum size of the smart blob for the call to `mi_lo_specset_maxbytes()`. If set to <= 0, `mi_lo_specset_maxbytes()` is not called.

Warning: setting any sizes to 2,147,483,647 or greater has not been tested.

In a DB-Access window execute the following query, while checking with oncheck -pS in another window:

  insert into sblob_test values
 ('create a 1500 byte sblob',
  SblobCreate("default", 1500, "nolog", -1, -1));

    select  description,
          SblobStatSize(blob_column) as statsize,
          SblobSbspace(blob_column)  as sbspace,
        SblobEstSize(blob_column)  as estimated_size,
          SblobMaxSize(blob_column)  as max_size
  from	sblob_test
  where	description="create a 1500 byte sblob";

   insert into sblob_test values
  ('1024 byte sblob',
  SblobCreate("default", 1024, "log", 2048, 4096));

    select  description,
         SblobStatSize(blob_column) as statsize,
          SblobSbspace(blob_column)  as sbspace,
          SblobEstSize(blob_column)  as estimated_size,
          SblobMaxSize(blob_column)  as max_size
  from    sblob_test
  where   description="1024 byte sblob";

SblobUpdate

SblobUpdate() updates a smart blob, and allows appending to or replacing (truncating first) the previous contents.

One of the goals is to understand and demonstrate the affects of smart blob updates on smart blob spaces and logical logs.

SblobUpdate (blob, size, options) returns integer

blob	Smart blob handle.
size	int8 value >= 0 specifying the length (in bytes) of the update. This space is filled with `X`s.
options	String containing "truncate" or "append"; any other value causes an error to be raised. If the parameter is set to "truncate", `mi_lo_open()` flags are set to `MI_LO_WRONLY \| MI_LO_TRUNC`. If the parameter is set to "append", `mi_lo_open()` is called with `MI_LO_WRONLY`.

In a DB-Access window execute the following query, while checking with oncheck -pS in another window:

  insert into sblob_test values
  ('foo', SblobCreate("default", 1024, "log", 2048, 4096));
    -- Add 2000 bytes.
  select SblobUpdate(blob_column, 2000, "append")
  from	sblob_test
  where	description="foo";
    -- The resulting sblob should be 3024 bytes.
  select	description,
  	SblobStatSize(blob_column) as statsize,
  	SblobSbspace(blob_column)  as sbspace,
  	SblobEstSize(blob_column)  as estimated_size,
  	SblobMaxSize(blob_column)  as max_size,
 	SblobLog(blob_column)     as logged  from
	sblob_test  where	description="foo";
    -- Replace with 1800 bytes.  select SblobUpdate(blob_column, 1800, "truncate")
  from	sblob_test
  where	description="foo";
    -- The resulting sblob should be 1800 bytes.
  select	description,
  	SblobStatSize(blob_column) as statsize,
  	SblobSbspace(blob_column)  as sbspace,
  	SblobEstSize(blob_column)  as estimated_size,
  	SblobMaxSize(blob_column)  as max_size,
  	SblobLog(blob_column)      as logged
  from	sblob_test
  where	description="foo";

SblobRefCount

SblobRefCount() returns an integer that indicates how many times a particular blob is referenced by LO handles stored in database tables.

SblobRefCount (blob) returns integer

Information about download methods

In a DB-Access window create a table with a blob column and insert a row:

  create table sblob_tab(blob_column blob);
    insert into sblob_tab values
 ( FileToBlob('/usr/include/stdio.h', 'server'));
    select SblobExtSize(blob_column)  from sblob_tab;

Note that on NT you'll need to modify the path to the standard C include files to point to the copies in your VC++ installation.

Since this sblob is used only once, the value "1" should be returned.

Now create a temp table with a blob column that references the same sblob:

  select blob_column from sblob_tab  into temp sblob_temp_tab;

Check the refcount again: It should rise to "2".

  select SblobExtSize(blob_column)  from sblob_tab;

Revision History

Version 1.0

Implements the first three UDRs (SblobDebug, SblobStatSize, and SblobSbspace).

Version 1.1

Adds the next six UDRs (SblobMaxSize, SblobEstSize, SblobExtSize, SblobLog, SblobCreate, and SblobUpdate).

Version 1.1 fix 1/16/98

Source code replaces calls to ifx_int8toint() with ifx_int8tolong().

ifx_int8toint() corresponds to SQL SMALLINT / mi_smallint ( -32767 to +32767 )
ifx_int8tolong() corresponds to SQL INT / mi_integer ( -2,147,483,647 to +2,147,483,647 )
The next major release will replace all sizes with SQL INT8 / mi_int8.

Version 1.2

Implements SblobRefCount.

Version 1.3

Rebuilds for server release 9.2 and DBDK version 4.00.

Version 1.4

Added pre-compiled shared object for NT.

Version 1.5

Changed program variables associated with size to use int8. This will allow sblob sizes > 2GB.

Download

Description	Name	Size	Download method
Compressed tar archive	sblob_info.1.5.tar.Z	204KB	FTP \| HTTP

Resources

Build your next development project with IBM trial software, available for download directly from developerWorks.
developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.

About the author

Jean Anderson is a Senior Engineer with IBM.

Report abuse help

Report abuse

Thank you. This entry has been flagged for moderator attention.

Report abuse help

Report abuse

Report abuse submission failed. Please try again later.

developerWorks: Sign in

The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

Rate this article

Comments

View your My developerWorks profile

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

Return from help