I know that there is no function that can process LOB type data, so I installed and used the blade manager's bundled module as shown below.
1. First, create the test database stores_demo as shown below.
$ dbaccessdemo -log -dbspace datadbs1
2. Install the module for processing LOB type data in blademgr as shown below.
$ blademgr
ol_informix1210_1>show databases
Databases on server:
stores_demo
sysadmin
sysuser
ol_informix1210_1>list stores_demo
DataBlade modules registered in database stores_demo:
TimeSeries.6.00.FC7 ifxrltree.2.00
spatial.8.22.FC2
ol_informix1210_1>show modules
16 DataBlade modules installed on server ol_informix1210_1:
LLD.1.20.FC2 Node.2.0 c
TSAFuncs.1.00.FC1 TSPIndex.1.00.FC1
TSPMatch.2.00.FC1 TimeSeries.6.00.FC7
binaryudt.1.0 bts.3.10
excompat.1.0 ifxbuiltins.1.1
ifxregex.1.00 ifxrltree.2.00
mqblade.2.0 spatial.8.22.FC2
sts.2.00.FC1 wfs.1.00.FC1
A 'c' indicates DataBlade module has client files.
If a module does not show up, check the prepare log.
ol_informix1210_1>register excompat.1.0 stores_demo
Register module excompat.1.0 into database stores_demo? [Y/n]y
Registering DataBlade module... (may take a while).
DataBlade excompat.1.0 was successfully registered in database stores_demo.
3. After installing the above procedure, you can see that dbms related functions and procedures have been created.
$ dbschema -d stores_demo -f all | egrep 'create function.*dbms_|create procedure.*dbms'
create function "informix".dbms_lob_getlength (blob)
create function "informix".dbms_lob_getlength (clob)
create function "informix".dbms_lob_compare (blob,blob,integer default 2147483647,integer default 1,integer default 1)
create function "informix".dbms_lob_substr (clob,integer default 32767,integer default 1)
create function "informix".dbms_lob_instr (clob,lvarchar,integer default 1,integer default 1)
create function "informix".dbms_lob_new_clob (lvarchar)
create function "informix".dbms_lob_compare (clob,clob,integer default 2147483647,integer default 1,integer default 1)
create function "informix".dbms_random_jrand48 (inout bigint)
create procedure "informix".dbms_output_enable (integer default 20000)
create procedure "informix".dbms_output_disable ()
create procedure "informix".dbms_output_put (lvarchar)
...
4. Check the size before entering the sample image file of the Informix demo program.
$ ls -l demo/esqlc/cn*.gif
-rw-r--r-- 1 informix informix 15736 Apr 19 18:08 demo/esqlc/cn_10001.gif
-rw-r--r-- 1 informix informix 21454 Apr 19 18:08 demo/esqlc/cn_10027.gif
-rw-r--r-- 1 informix informix 11751 Apr 19 18:08 demo/esqlc/cn_10031.gif
-rw-r--r-- 1 informix informix 25610 Apr 19 18:08 demo/esqlc/cn_10046.gif
-rw-r--r-- 1 informix informix 7937 Apr 19 18:08 demo/esqlc/cn_10049.gif
5. Create the table to be entered and calculate the LOB data size.
$ dbaccess stores_demo -
> create table test (filename varchar(30), img blob);
> insert into test values ('cn_10001.gif', filetoblob('./demo/esqlc/cn_10001.gif','client'));
> insert into test values ('cn_10027.gif', filetoblob('./demo/esqlc/cn_10027.gif','client'));
> insert into test values ('cn_10031.gif', filetoblob('./demo/esqlc/cn_10031.gif','client'));
> insert into test values ('cn_10046.gif', filetoblob('./demo/esqlc/cn_10046.gif','client'));
> insert into test values ('cn_10049.gif', filetoblob('./demo/esqlc/cn_10049.gif','client'));
> select filename, dbms_lob_getlength (img) from test;
filename (ｅxpression)
cn_10001.gif 15736
cn_10027.gif 21454
cn_10031.gif 11751
cn_10046.gif 25610
cn_10049.gif 7937
5 row(s) retrieved.
The Knowledge Center does not document DBMS_LOB_LENGTH, so there seems to be confusion.
Hope to see you.
** Reference
http://www-01.ibm.com/support/docview.wss?uid=swg21670983
https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.dbext.doc/ids_r0055123.htm
댓글 없음:
댓글 쓰기