Art:
Perfect!!! I created the table.
No words to tell you how grateful I am!!!
I'd like to thank Bogdam Botez and Everett Mills, as well.
Great Forum!!!
Unfortunately, I've searched all manuals of IDS I have (and I have about
40 manuals), but I didn't find any example of how to create a table
using BLOBS. Maybe, I didn't see at the moment, because I was in a
hurry. Any hint of a specific document / white paper, etc... that I can
use as a reference to work with datablades on IDS and Image Datablade
Module?
Thank you all,
Alberto Pessonio
Orizon Brasil
www.orizonbrasil.com.br
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Art
Kagel
Enviada em: quinta-feira, 17 de setembro de 2009 14:11
Para: ids@iiug.org
Assunto: Re: Creating a table with BLOB in a SBSpace [17073]
It will work that way, but if you want to use a specific sbspace, you
need
to use the PUT clause. I was just having a senior moment there. See my
corrected post.
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:38 PM, Everett Mills <
Everett.Mills@nationalbeef.com> wrote:
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
Of
> Alberto
> > Romeu Pessonio Filho
> > Sent: Thursday, September 17, 2009 11:21 AM
> > To: ids@iiug.org
> > Subject: Re: Creating a table with BLOB in a SBSpace [17070]
> >
> > 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?
>
> I haven't used BLOB columns, but if I'm reading the manual correctly,
> for a BLOB column, you leave the IN clause off and the engine assigns
> the sbspace used on the fly as you store your images are saved. So try
> this, instead:
>
> 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)
>
> > 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.
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0ce044b42377aa0473c919a8
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
