 |
IDS Forum
Re: Creating a table with BLOB in a SBSpace
Posted By: Art Kagel
Date: Thursday, 17 September 2009, at 1:05 p.m.
In Response To: Re: Creating a table with BLOB in a SBSpace (Alberto Romeu Pessonio Filho)
Sorry, senior moment. No, you do not put a table into a smart blobspace,
only a column, but the syntax is different than for simple blobspaces. You
need to use the PUT() clause at the table level:
CREATE TABLE TB_ANEXO_GUIA1
(
ID_ANEXO_GUIA INTEGER NOT NULL,
CD_GUIA_PRESTADOR VARCHAR(20) NOT NULL,
CD_CARTEIRINHA VARCHAR(30) ,
COD_OPERADORA_ANS VARCHAR(6) ,
NM_ARQUIVO VARCHAR(255) ,
DS_EXTENSAO_ARQUIVO VARCHAR(3) ,
NU_TAMANHO_ARQUIVO BIGINT ,
NU_SEQUENCIAL INTEGER ,
ID_STATUS_ANEXO_GUIA INTEGER ,
DT_INCLUSAO DATETIME YEAR TO FRACTION(5) ,
DT_ULTIMO_PROCESSAMENTO DATETIME YEAR TO FRACTION(5) ,
ID_PRESTADOR INTEGER ,
ID_OPERADORA INTEGER ,
ID_MAQUINA INTEGER ,
NM_SERVICO VARCHAR(100) ,
ID_THREAD INTEGER
,
IMAGEM BLOB
)
PUT IMAGEM IN (sbs_photospace01) EXTENT SIZE 102400 NEXT SIZE 102400 lock
mode row;
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Thu, Sep 17, 2009 at 12:21 PM, Alberto Romeu Pessonio Filho <
arfilho@orizonbrasil.com.br> wrote:
> Art:
>
> The error has changed now...
>
> Look:
>
> MODIFY: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R =
> Redraw
>
> CTRL-X = Delete character CTRL-D = Delete rest of line
>
> -- 10 to 20 of 20 ----- imagem@df06_d ---------- Press CTRL-W for Help
> --------
>
> NU_SEQUENCIAL INTEGER ,
>
> ID_STATUS_ANEXO_GUIA INTEGER ,
>
> DT_INCLUSAO DATETIME YEAR TO FRACTION(5) ,
>
> DT_ULTIMO_PROCESSAMENTO DATETIME YEAR TO FRACTION(5) ,
>
> ID_PRESTADOR INTEGER ,
>
> ID_OPERADORA INTEGER ,
>
> ID_MAQUINA INTEGER ,
>
> NM_SERVICO VARCHAR(100) ,
>
> ID_THREAD INTEGER ,
>
> IMAGEM BLOB IN sbs_photospace01 )
>
> EXTENT SIZE 102400 NEXT SIZE 102400 lock mode row;
>
> 201: A syntax error has occurred.
>
> The cursor stopped before "IN sbs_ photospace01". I don't know if I'm
> wrong, but... in my opinion, I can't put just one column inside a
> SBSpace; I must store the entire table, opposite a Blobspace... Is that
> right?
>
> Tks,
>
> Alberto.
>
> De: Art Kagel [mailto:art.kagel@gmail.com]
> Enviada em: quinta-feira, 17 de setembro de 2009 13:06
> Para: Alberto Romeu Pessonio Filho
> Assunto: Re: Creating a table with BLOB in a SBSpace [17064]
>
> Yes, what I didn't notice is that you are trying to put the entire table
> into the sbspace. Tables live in regular dbspaces, you would place ONLY
> the BLOB column into the sb_space. So, that should be:
>
> CREATE TABLE TB_ANEXO_GUIA1
> (
> ID_ANEXO_GUIA INTEGER NOT NULL,
> CD_GUIA_PRESTADOR VARCHAR(20) NOT NULL,
> CD_CARTEIRINHA VARCHAR(30) ,
> COD_OPERADORA_ANS VARCHAR(6) ,
> NM_ARQUIVO VARCHAR(255) ,
> DS_EXTENSAO_ARQUIVO VARCHAR(3) ,
> NU_TAMANHO_ARQUIVO BIGINT ,
> NU_SEQUENCIAL INTEGER ,
> ID_STATUS_ANEXO_GUIA INTEGER ,
> DT_INCLUSAO DATETIME YEAR TO FRACTION(5) ,
> DT_ULTIMO_PROCESSAMENTO DATETIME YEAR TO FRACTION(5) ,
> ID_PRESTADOR INTEGER ,
> ID_OPERADORA INTEGER ,
> ID_MAQUINA INTEGER ,
> NM_SERVICO VARCHAR(100) ,
> ID_THREAD INTEGER
> ,
> IMAGEM BLOB IN sbs_photospace01
> )
> IN some_normal_dbspace EXTENT SIZE 102400 NEXT SIZE 102400 lock mode
> row;
>
> Art S. Kagel
> Oninit (www.oninit.com <http://www.oninit.com> )
> IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org> )
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated
> with any entity with which I am affiliated nor those of the entities
> themselves.
>
> On Thu, Sep 17, 2009 at 12:00 PM, Alberto Romeu Pessonio Filho
> <arfilho@orizonbrasil.com.br> wrote:
>
> Hi Art!!!
>
> Ok, but i changed the datatype from BYTE to BLOB and the error still
> remain.
>
> SQL: New Run Modify Use-editor Output Choose Save Info Drop
> Exit
> Modify the current SQL statements using the SQL editor.
>
> ----------------------- imagem@df06_d ---------- Press CTRL-W for Help
> --------
>
> CREATE TABLE TB_ANEXO_GUIA1
> (
> ID_ANEXO_GUIA INTEGER NOT NULL,
> CD_GUIA_PRESTADOR VARCHAR(20) NOT NULL,
> CD_CARTEIRINHA VARCHAR(30) ,
> COD_OPERADORA_ANS VARCHAR(6) ,
> NM_ARQUIVO VARCHAR(255) ,
> DS_EXTENSAO_ARQUIVO VARCHAR(3) ,
> NU_TAMANHO_ARQUIVO BIGINT ,
> NU_SEQUENCIAL INTEGER ,
> ID_STATUS_ANEXO_GUIA INTEGER ,
> DT_INCLUSAO DATETIME YEAR TO FRACTION(5) ,
> DT_ULTIMO_PROCESSAMENTO DATETIME YEAR TO FRACTION(5) ,
> ID_PRESTADOR INTEGER ,
> ID_OPERADORA INTEGER ,
> ID_MAQUINA INTEGER ,
> NM_SERVICO VARCHAR(100) ,
> ID_THREAD INTEGER
> ,
> IMAGEM BLOB
> )
> IN sbs_photospace01 EXTENT SIZE 102400 NEXT SIZE 102400 lock mode row;
>
> 261: Cannot create file for table (informix.tb_anexo_guia1).
> 130: ISAM error: no such DBspace
>
> Tks anyway,
> Alberto Pessonio.
>
> -----Mensagem original-----
> De: Art Kagel [mailto:art.kagel@gmail.com <mailto:art.kagel@gmail.com> ]
> Enviada em: quinta-feira, 17 de setembro de 2009 12:53
> Para: Alberto Romeu Pessonio Filho
> Assunto: Re: Creating a table with BLOB in a SBSpace [17064]
>
> You cannot place BYTE or TEXT columns in sbspace (SmartBlobSpace). BYTE
> and
> TEXT are simple blob objects and can only be placed IN TABLE or in a
> regular
> BLOBSpace - onspaces -c -b.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com <http://www.oninit.com> )
> IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org> )
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> On Thu, Sep 17, 2009 at 11:24 AM, ALBERTO ROMEU PESSONIO FILHO <
> arfilho@orizonbrasil.com.br <mailto:arfilho@orizonbrasil.com.br> >
> wrote:
>
> > Hi everyone!
> >
> > Version of IDS: 11.50 FC5
> > OS: HP-UX 11.11
> >
> > I'm trying to create a table with a BYTE datatype in a SBSpace. When i
> run
> > the
> > DDL statement on DBAccess, i've got the following error:
> >
> > CREATE TABLE TB_ANEXO_GUIA_1
> > (
> > ID_ANEXO_GUIA INTEGER NOT NULL,
> > CD_GUIA_PRESTADOR VARCHAR(20) NOT NULL,
> > CD_CARTEIRINHA VARCHAR(30) ,
> > COD_OPERADORA_ANS VARCHAR(6) ,
> > NM_ARQUIVO VARCHAR(255) ,
> > DS_EXTENSAO_ARQUIVO VARCHAR(3) ,
> > NU_TAMANHO_ARQUIVO BIGINT ,
> > NU_SEQUENCIAL INTEGER ,
> > ID_STATUS_ANEXO_GUIA INTEGER ,
> > DT_INCLUSAO DATETIME YEAR TO FRACTION(5) ,
> > DT_ULTIMO_PROCESSAMENTO DATETIME YEAR TO FRACTION(5) ,
> > ID_PRESTADOR INTEGER ,
> > ID_OPERADORA INTEGER ,
> > ID_MAQUINA INTEGER ,
> > NM_SERVICO VARCHAR(100) ,
> > ID_THREAD INTEGER ,
> > IMAGEM BYTE )
> > IN sbs_photospace01 EXTENT SIZE 102400 NEXT SIZE 102400 lock mode row;
>
> >
> > 261: Cannot create file for table (informix.tb_anexo_guia_1).
> > 130: ISAM error: no such DBspace
> >
> > I tried to change the datatype from BYTE to BLOB, but the error
> remains. I
> > created the database called "imagem" for use with a system thar store
> > images
> > in a regular DBSpace, but that table i'm trying to store in a SBSpace,
>
> > showed
> > on a onstat -d output below:
> >
> > IBM Informix Dynamic Server Version 11.50.FC5 -- On-Line -- Up
> 01:05:02 --
> > 1229596 Kbytes
> >
> > Dbspaces
> > address number flags fchunk nchunks pgsize flags owner name
> > c00000002cc62028 1 0x40001 1 1 2048 N B informix rootdbs
> > c00000002ec27c38 2 0x40001 2 1 2048 N B informix physdbs
> > c00000002ec27dd0 3 0x40001 3 1 2048 N B informix llogdbs
> > c00000002ec28028 4 0x42001 4 1 2048 N TB informix tempdbs01
> > c00000002ec281c0 5 0x42001 5 1 2048 N TB informix tempdbs02
> > c00000002ec28358 6 0x40001 6 3 8192 N B informix datadbs01
> > c00000002ec284f0 7 0x48001 8 1 2048 N SB informix cdr_sbspace01
> > c00000002ec28688 8 0x40001 9 1 2048 N B informix cdr_dbspace01
> > c00000002ec28820 9 0x48001 11 1 2048 N SB informix sbs_photospace01
> > 9 active, 2047 maximum
> >
> > Chunks
> > address chunk/dbs offset size free bpages flags pathname
> > c00000002cc621c0 1 1 0 2560000 2527259 PO-B-
> > /informix/v11/dbspaces/autorizador/rootdbs
> > c00000002ec289b8 2 2 0 2560000 59947 PO-B-
> > /informix/v11/dbspaces/autorizador/physdbs
> > c00000002ec28ba8 3 3 0 2560000 4043 PO-B-
> > /informix/v11/dbspaces/autorizador/llogdbs
> > c00000002ec28d98 4 4 0 2560000 2556121 PO-B-
> > /informix/v11/dbspaces/autorizador/tempdbs01
> > c00000002ec2a028 5 5 0 2560000 2559947 PO-B-
> > /informix/v11/dbspaces/autorizador/tempdbs02
> > c00000002ec2a218 6 6 0 640000 811 PO-B-
> > /informix/v11/dbspaces/autorizador/datadbs01a
> > c00000002ec2a408 7 6 0 640000 48996 PO-B-
> > /informix/v11/dbspaces/autorizador/datadbs01b
> > c00000002ec2a5f8 8 7 0 2560000 1991184 1991185 POSB-
> > /informix/v11/dbspaces/autorizador/sbsspace01
> >
> > Metadata 568762 457027 568762
> > c00000002ec2a7e8 9 8 0 2560000 2559907 PO-B-
> > /informix/v11/dbspaces/autorizador/cdr_dbspace01
> > c00000002ec2a9d8 10 6 0 640000 419622 PO-B-
> > /informix/v11/dbspaces/autorizador/datadbs01c
> > c00000002ec2abc8 11 9 0 2560000 1059947 1059947 POSB-
> > /informix/v11/dbspaces/autorizador/sbs_photospc01
> >
> > Metadata 1500000 1495875 1500000
> > 11 active, 32766 maximum
> >
> > I have available space on the chunks of sbs_photospace.
> >
> > I also runned oncheck -cr, -ce, -cS,:
> >
> > informix@dvohp006:/informix/v11# oncheck -cr
> > Validating IBM Informix Dynamic Server reserved pages
> >
> > Validating PAGE_PZERO...
> >
> > Validating PAGE_CONFIG...
> >
> > Validating PAGE_1CKPT & PAGE_2CKPT...
> >
> > Using check point page PAGE_2CKPT.
> >
> > Validating PAGE_1DBSP & PAGE_2DBSP...
> >
> > Using DBspace page PAGE_2DBSP.
> >
> > Validating PAGE_1PCHUNK & PAGE_2PCHUNK...
> >
> > Using primary chunk page PAGE_2PCHUNK.
> >
> > Validating PAGE_1ARCH & PAGE_2ARCH...
> >
> > Using archive page PAGE_1ARCH.
> >
> > informix@dvohp006:/informix/v11# oncheck -ce sbs_photospace01
> >
> > Validating extents for Space 'sbs_photospace01' ...
> >
> > Chunk Pathname Pagesize(k) Size(p) Used(p) Free(p)
> >
> > 11 /informix/v11/dbspaces/autorizador/sbs_photospc01 2560000 4178
> 1059947
> >
> > Note: 'Used' = used metadata space + used user data space.
> >
> > 'Free' = free user data space.
> >
> > informix@dvohp006:/informix/v11# oncheck -cS
> >
> > Validating space 'cdr_sbspace01' ...
> >
> > sbspace Metadata Partition Partnum Used Free
> > cdr_sbspace01:'informix'.TBLSpace 0x700001 6 44
> > cdr_sbspace01:'informix'.sbspace_desc 0x700002 2 2
> > cdr_sbspace01:'informix'.chunk_adjunc 0x700003 2 2
> > cdr_sbspace01:'informix'.LO_ud_free 0x700004 2 5529
> > cdr_sbspace01:'informix'.LO_hdr_partn 0x700005 10 106186
> >
> > Large Objects
> > ID Ref Size Allocced Creat Last
> > Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified
> > ---- ---- ----- ---- ---------- -------- ----- -----
> > ------------------------
> >
> > 7 8 3 1 3 1 1 L-N-H Wed Aug 12 11:44:07 2009
> >
> > Validating space 'sbs_photospace01' ...
> >
> > sbspace Metadata Partition Partnum Used Free
> > sbs_photospace01:'informix'.TBLSpace 0x900001 6 44
> > sbs_photospace01:'informix'.sbspace_desc 0x900002 2 2
> > sbs_photospace01:'informix'.chunk_adjunc 0x900003 2 2
> > sbs_photospace01:'informix'.LO_ud_free 0x900004 2 2943
> > sbs_photospace01:'informix'.LO_hdr_partn 0x900005 1 1171
> >
> > informix@dvohp006:/informix/v11# oncheck -cD imagem
> >
> > TBLspace data check for imagem:informix.systables
> > TBLspace data check for imagem:informix.syscolumns
> > TBLspace data check for imagem:informix.sysindices
> > TBLspace data check for imagem:informix.systabauth
> > TBLspace data check for imagem:informix.syscolauth
> > TBLspace data check for imagem:informix.sysviews
> > TBLspace data check for imagem:informix.sysusers
> > TBLspace data check for imagem:informix.sysdepend
> > TBLspace data check for imagem:informix.syssynonyms
> > TBLspace data check for imagem:informix.syssyntable
> > TBLspace data check for imagem:informix.sysconstraints
> > TBLspace data check for imagem:informix.sysreferences
> > TBLspace data check for imagem:informix.syschecks
> > TBLspace data check for imagem:informix.sysdefaults
> > TBLspace data check for imagem:informix.syscoldepend
> > TBLspace data check for imagem:informix.sysprocedures
> > TBLspace data check for imagem:informix.sysprocbody
> > TBLspace data check for imagem:informix.sysprocplan
> > TBLspace data check for imagem:informix.sysprocauth
> > TBLspace data check for imagem:informix.sysblobs
> > TBLspace data check for imagem:informix.sysopclstr
> > TBLspace data check for imagem:informix.systriggers
> > TBLspace data check for imagem:informix.systrigbody
> > TBLspace data check for imagem:informix.sysdistrib
> > TBLspace data check for imagem:informix.sysfragments
> > TBLspace data check for imagem:informix.sysobjstate
> > TBLspace data check for imagem:informix.sysviolations
> > TBLspace data check for imagem:informix.sysfragauth
> > TBLspace data check for imagem:informix.sysroleauth
> > TBLspace data check for imagem:informix.sysxtdtypes
> > TBLspace data check for imagem:informix.sysattrtypes
> > TBLspace data check for imagem:informix.sysxtddesc
> > TBLspace data check for imagem:informix.sysinherits
> > TBLspace data check for imagem:informix.syscolattribs
> > TBLspace data check for imagem:informix.syslogmap
> > TBLspace data check for imagem:informix.syscasts
> > TBLspace data check for imagem:informix.sysxtdtypeauth
> > TBLspace data check for imagem:informix.sysroutinelangs
> > TBLspace data check for imagem:informix.syslangauth
> > TBLspace data check for imagem:informix.sysams
> > TBLspace data check for imagem:informix.systabamdata
> > TBLspace data check for imagem:informix.sysopclasses
> > TBLspace data check for imagem:informix.syserrors
> > TBLspace data check for imagem:informix.systraceclasses
> > TBLspace data check for imagem:informix.systracemsgs
> > TBLspace data check for imagem:informix.sysaggregates
> > TBLspace data check for imagem:informix.syssequences
> > TBLspace data check for imagem:informix.sysdirectives
> > TBLspace data check for imagem:informix.sysxasourcetypes
> > TBLspace data check for imagem:informix.sysxadatasources
> > TBLspace data check for imagem:informix.sysseclabelcomponents
> > TBLspace data check for imagem:informix.sysseclabelcomponentelements
> > TBLspace data check for imagem:informix.syssecpolicies
> > TBLspace data check for imagem:informix.syssecpolicycomponents
> > TBLspace data check for imagem:informix.syssecpolicyexemptions
> > TBLspace data check for imagem:informix.sysseclabels
> > TBLspace data check for imagem:informix.sysseclabelnames
> > TBLspace data check for imagem:informix.sysseclabelauth
> > TBLspace data check for imagem:informix.syssurrogateauth
> > TBLspace data check for imagem:informix.sysproccolumns
> > TBLspace data check for imagem:informix.tb_anexo_guia
> > TBLspace data check for imagem:informix.tb_anexo_guia_status
> >
> > I'm trying to do that because some managers want to put this solution
> to
> > run
> > on SQLServer. If i can do that on Informix, there is a chance that the
>
> > company
> > implement Informix DataBlade Image in a huge solution to store images.
>
> >
> > By the way, I've opened a PMR : 31796,999,631.
> >
> > Any help or hint is welcome.
> >
> > Thanks,
> > Alberto Pessonio.
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --0015173ff2e0e5ead00473c80240
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0015173ff2e0178ecc0473c904ca
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
