Inserting BLOBS (and rootdbs)
Posted in 1999
Hello All,
We have come across a situation where the rootdbs is filling up with
TBLSPACE TBLSPACE entries when inserting BLOBS into a table via a stored
procedure on a remote system. Do Blobs flow through the rootdbs (on
there way to the blobspace maybe)? Is there a way to have them go to
another working space?
I have read all that I can find on blobs and nowhere have I found a link
between Blob insertion and rootdbs.
Here are the details:
Informix Dynamic Server Version 7.30.UC3XD
Solaris 2.6
From onstat -d
dbspace size free bpages
rootdbs 50000 8609
tempdbs 314000 313847
blobdbs8_64 {8 chunks}
1000000 ~12186 31250
1000000 ~31246 31250
1000000 ~31246 31250
1000000 ~31246 31250
1000000 ~31246 31250
1000000 ~31246 31250
1000000 ~31246 31250
1000000 ~31246 31250
The inserting application is on a remote system using JDBC to call a
stored procedure "image_ins". "image_ins" has a parameter REFERENCES
BYTE that then gets inserted into a table "image" which has a BLOB
column stored in blobdbs8_64.
create table "informix".image
(
...snipped....
blob byte in blobdbs8_64,
...snipped...
);
CREATE DBA PROCEDURE
image_ins
(
....snipped...
, pass_blob REFERENCES BYTE
....snipped...
)
....snipped...
INSERT INTO
image
(
...snipped...
, blob
,,,snipped... )
VALUES
(
...snipped...
, pass_blob
...snipped...
)
END PROCEDURE;
Each blob is ~200KB and the TBLSPACE TBLSPACE entries on rootdbs (from
the end of the rootdbs listing of oncheck -pe) look like:
TBLSPACE TBLSPACE 15609 50
TBLSPACE TBLSPACE 19579 50
TBLSPACE TBLSPACE 21301 50
TBLSPACE TBLSPACE 24951 50
TBLSPACE TBLSPACE 28601 50
TBLSPACE TBLSPACE 32251 50
TBLSPACE TBLSPACE 35901 50
TBLSPACE TBLSPACE 39551 50
TBLSPACE TBLSPACE 43201 50
FREE
43827 3024
TBLSPACE TBLSPACE 46851 50
FREE
46901 3099
These TBLSPACE entries are also peculiar because the allocation page
numbers do not seem to add up 15609 + 50 <> 19579.
Also these TBLSPACE entries are not cleaning themselves up after the
process finishes running.