using ? in "in"
User asked if a single ? placeholder can be used in an IN clause for Perl. Answer: no, you need one ? per list item. To work around the 64K statement length limit, use a temporary table instead. Multiple solutions suggested: build dynamic queries with appropriate placeholders, use PUT cursors for efficient bulk inserts, or use external tables if file access is available.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Anyone know if it's possible to use the ? placeholder in a "in(?)" statement? Specifically I'm attempting this in perl. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu> ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson
You can but you need one for each item in the list. You can't just use one ? for the whole list. For a variable length list use a temp table. Art On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote: --00163630ee9fdea2c004925be309
If you know the number of values, either by program logic or by collecting and scanning user input, you could build the query string with the appropriate number of question marks before it's prepared. Walt. On Oct 11, 2010, at 2:54 PM, Art Kagel wrote: > You can but you need one for each item in the list. You can't just > use one > ? for the whole list. For a variable length list use a temp table. > > Art > On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> > wrote: > > --00163630ee9fdea2c004925be309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Walt Hultgren walt.hultgren@emory.edu
Nah, the problem is the limit in the size of the string. I was hoping to get around the 32k character limit. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walt Hultgren Sent: Monday, October 11, 2010 3:07 PM To: ids@iiug.org Subject: Re: using ? in "in" [21617] If you know the number of values, either by program logic or by collecting and scanning user input, you could build the query string with the appropriate number of question marks before it's prepared. Walt. On Oct 11, 2010, at 2:54 PM, Art Kagel wrote: > You can but you need one for each item in the list. You can't just use > one ? for the whole list. For a variable length list use a temp table. > > Art > On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> > wrote: > > --00163630ee9fdea2c004925be309 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Walt Hultgren walt.hultgren@emory.edu ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
64K actually. But putting the list of values into temp table also solves the max statement length problem. Art On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote: > Nah, the problem is the limit in the size of the string. I was hoping to get > around the 32k character limit. > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu > ============================== > SLES 11x64 & IDS 11.50.FC6 > > "Don't document the problem, fix it." > - Atli Björgvin Oddsson > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Walt > Hultgren > Sent: Monday, October 11, 2010 3:07 PM > To: ids@iiug.org > Subject: Re: using ? in "in" [21617] > > If you know the number of values, either by program logic or by collecting and > scanning user input, you could build the query string with the appropriate > number of question marks before it's prepared. > > Walt. > > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote: > >> You can but you need one for each item in the list. You can't just use >> one ? for the whole list. For a variable length list use a temp table. >> >> Art >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> >> wrote: >> >> --00163630ee9fdea2c004925be309 >> >> >> > > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > -- > Walt Hultgren > walt.hultgren@emory.edu > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --000e0cd30b74875c6d04925cfe58
True, but it seems faster in perl to do this:
INSERT INTO temp_table(id) VALUES (list of values that goes to characterlimit); #run until we run out of values
Than to do this
While(list of values)
INSERT INTO temp_table(id) VALUES(value)
I'd pay good money for the load from file to not be a isql/dbaccess only thing
and to work in DBI.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, October 11, 2010 4:14 PM
To: ids@iiug.org
Subject: Re: RE: using ? in "in" [21621]
64K actually. But putting the list of values into temp table also solves the
max statement length problem.
Art
On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote:
> Nah, the problem is the limit in the size of the string. I was hoping
> to
get
> around the 32k character limit.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Walt
> Hultgren
> Sent: Monday, October 11, 2010 3:07 PM
> To: ids@iiug.org
> Subject: Re: using ? in "in" [21617]
>
> If you know the number of values, either by program logic or by
> collecting
and
> scanning user input, you could build the query string with the
> appropriate
> number of question marks before it's prepared.
>
> Walt.
>
> On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
>
>> You can but you need one for each item in the list. You can't just
>> use one ? for the whole list. For a variable length list use a temp table.
>>
>> Art
>> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
>> wrote:
>>
>> --00163630ee9fdea2c004925be309
>>
>>
>>
>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
> --
> Walt Hultgren
> walt.hultgren@emory.edu
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--000e0cd30b74875c6d04925cfe58
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
On Mon, Oct 11, 2010 at 9:54 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> True, but it seems faster in perl to do this:
>
> INSERT INTO temp_table(id) VALUES (list of values that goes to character> limit); #run until we run out of values
>
>
I'm not sure if I follow you... Using "list of values...." bigger than one
will raise an error... Or am I missing something?
> Than to do this
>
> While(list of values)
> INSERT INTO temp_table(id) VALUES(value)>
> I'd pay good money for the load from file to not be a isql/dbaccess only
> thing
> and to work in DBI.
>
>
If you pay enough to upgrade to 11.50.FC6 and you can put the file
accessible to the engine, you can use external tables :)
And the limit should be 64K as Art pointed out...
And if you can connect through DRDA you could use a bigger limit.
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf301d44b497cd9804925dcf89
IB that INSERT cursors are supported in Perl, so you can do something like
(ESQL/C-ish syntax):
create temp table list_values( int ) with no log;
prepare put_stmt from "insert into list_values values ( ? )"
declare put_to cursor for put_stmt
open put_to;
while (list of values into id)
put put_to using id;
done
flush put_to;
close put_to;
free put_to;
free put_stmt;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Mon, Oct 11, 2010 at 4:54 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> True, but it seems faster in perl to do this:
>
> INSERT INTO temp_table(id) VALUES (list of values that goes to character> limit); #run until we run out of values
>
> Than to do this
>
> While(list of values)
> INSERT INTO temp_table(id) VALUES(value)>
> I'd pay good money for the load from file to not be a isql/dbaccess only
> thing
> and to work in DBI.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, October 11, 2010 4:14 PM
> To: ids@iiug.org
> Subject: Re: RE: using ? in "in" [21621]
>
> 64K actually. But putting the list of values into temp table also solves
> the
> max statement length problem.
>
> Art
> On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote:
> > Nah, the problem is the limit in the size of the string. I was hoping
> > to
> get
> > around the 32k character limit.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > ==============================
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Björgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Walt
>
> > Hultgren
> > Sent: Monday, October 11, 2010 3:07 PM
> > To: ids@iiug.org
> > Subject: Re: using ? in "in" [21617]
> >
> > If you know the number of values, either by program logic or by
> > collecting
> and
> > scanning user input, you could build the query string with the
> > appropriate
>
> > number of question marks before it's prepared.
> >
> > Walt.
> >
> > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
> >
> >> You can but you need one for each item in the list. You can't just
> >> use one ? for the whole list. For a variable length list use a temp
> table.
> >>
> >> Art
> >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> >> wrote:
> >>
> >> --00163630ee9fdea2c004925be309
> >>
> >>
> >>
> >
> >
>
>
>
>
*******************************************************************************
>
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >
> > --
> > Walt Hultgren
> > walt.hultgren@emory.edu
> >
> >
> >
>
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> --000e0cd30b74875c6d04925cfe58
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016361e87acd00d6204925e1ece
Hmm, getting senile, forgot to make my point on that last one. The point is
that PUT cursors are the mechanism that dbaccess uses to implement the LOAD
verb so quickly and dbload uses to outperform dbaccess LOAD. You can do the
same.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Mon, Oct 11, 2010 at 5:33 PM, Art Kagel <art.kagel@gmail.com> wrote:
> IB that INSERT cursors are supported in Perl, so you can do something like
> (ESQL/C-ish syntax):
>
> create temp table list_values( int ) with no log;>
> prepare put_stmt from "insert into list_values values ( ? )"
>
> declare put_to cursor for put_stmt
>
> open put_to;
>
> while (list of values into id)
> put put_to using id;
> done
> flush put_to;
> close put_to;
> free put_to;
> free put_stmt;
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly, implicitly,
> or by inference. 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 Mon, Oct 11, 2010 at 4:54 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
>
>> True, but it seems faster in perl to do this:
>>
>> INSERT INTO temp_table(id) VALUES (list of values that goes to character>> limit); #run until we run out of values
>>
>> Than to do this
>>
>> While(list of values)
>> INSERT INTO temp_table(id) VALUES(value)>>
>> I'd pay good money for the load from file to not be a isql/dbaccess only
>> thing
>> and to work in DBI.
>>
>> Jonathon Wyza
>> CX & CBORD System Administrator
>> CX Programmer/Analyst
>> Administrative Computing
>> Bethel College
>> (574)-257-3381
>> AIM: Iamwyza
>> jonathon.wyza@bethelcollege.edu
>> ==============================
>> SLES 11x64 & IDS 11.50.FC6
>>
>> "Don't document the problem, fix it."
>> - Atli Björgvin Oddsson
>>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
>> Kagel
>> Sent: Monday, October 11, 2010 4:14 PM
>> To: ids@iiug.org
>> Subject: Re: RE: using ? in "in" [21621]
>>
>> 64K actually. But putting the list of values into temp table also solves
>> the
>> max statement length problem.
>>
>> Art
>> On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
>> wrote:
>> > Nah, the problem is the limit in the size of the string. I was hoping
>> > to
>> get
>> > around the 32k character limit.
>> >
>> > Jonathon Wyza
>> > CX & CBORD System Administrator
>> > CX Programmer/Analyst
>> > Administrative Computing
>> > Bethel College
>> > (574)-257-3381
>> > AIM: Iamwyza
>> > jonathon.wyza@bethelcollege.edu
>> > ==============================
>> > SLES 11x64 & IDS 11.50.FC6
>> >
>> > "Don't document the problem, fix it."
>> > - Atli Björgvin Oddsson
>> >
>> > -----Original Message-----
>> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>> > Walt
>>
>> > Hultgren
>> > Sent: Monday, October 11, 2010 3:07 PM
>> > To: ids@iiug.org
>> > Subject: Re: using ? in "in" [21617]
>> >
>> > If you know the number of values, either by program logic or by
>> > collecting
>> and
>> > scanning user input, you could build the query string with the
>> > appropriate
>>
>> > number of question marks before it's prepared.
>> >
>> > Walt.
>> >
>> > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
>> >
>> >> You can but you need one for each item in the list. You can't just
>> >> use one ? for the whole list. For a variable length list use a temp
>> table.
>> >>
>> >> Art
>> >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
>> >> wrote:
>> >>
>> >> --00163630ee9fdea2c004925be309
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>>
>>
*******************************************************************************
>>
>> >> Forum Note: Use "Reply" to post a response in the discussion forum.
>> >>
>> >
>> > --
>> > Walt Hultgren
>> > walt.hultgren@emory.edu
>> >
>> >
>> >
>>
>>
>>
>>
*******************************************************************************
>>
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>> >
>> >
>>
>>
>>
>>
*******************************************************************************
>>
>> > Forum Note: Use "Reply" to post a response in the discussion forum.
>> >
>>
>> --000e0cd30b74875c6d04925cfe58
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
--0016e65b3f04b134e804925e7a86
Have you considered external tables? This does require the load/unload=
file to be
accessible from the database server.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
> [image removed]
>
> RE: RE: using ? in "in" [21624]
>
> Wyza, Jonathon
>
> to:
>
> ids
>
> 10/11/2010 01:54 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> True, but it seems faster in perl to do this:
>
> INSERT INTO temp_table(id) VALUES (list of values that goes to charac=ter
> limit); #run until we run out of values
>
> Than to do this
>
> While(list of values)
> INSERT INTO temp_table(id) VALUES(value)>
> I'd pay good money for the load from file to not be a isql/dbaccess
> only thing
> and to work in DBI.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Bj=F6rgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=
Art
> Kagel
> Sent: Monday, October 11, 2010 4:14 PM
> To: ids@iiug.org
> Subject: Re: RE: using ? in "in" [21621]
>
> 64K actually. But putting the list of values into temp table also sol=
ves
the
> max statement length problem.
>
> Art
> On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
wrote:
> > Nah, the problem is the limit in the size of the string. I was hopi=
ng
> > to
> get
> > around the 32k character limit.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Bj=F6rgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
Of
> > Walt
>
> > Hultgren
> > Sent: Monday, October 11, 2010 3:07 PM
> > To: ids@iiug.org
> > Subject: Re: using ? in "in" [21617]
> >
> > If you know the number of values, either by program logic or by
> > collecting
> and
> > scanning user input, you could build the query string with the
> > appropriate
>
> > number of question marks before it's prepared.
> >
> > Walt.
> >
> > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
> >
> >> You can but you need one for each item in the list. You can't just=
> >> use one ? for the whole list. For a variable length list use a tem=
p
table.
> >>
> >> Art
> >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu=
>
> >> wrote:
> >>
> >> --00163630ee9fdea2c004925be309
> >>
> >>
> >>
> >
> >
>
>
>
***********************************************************************=
********
>
> >> Forum Note: Use "Reply" to post a response in the discussion forum=
.
> >>
> >
> > --
> > Walt Hultgren
> > walt.hultgren@emory.edu
> >
> >
> >
>
>
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
> >
>
>
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
>
> --000e0cd30b74875c6d04925cfe58
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.=
>=
That might work, except it appears that external tables are not capable of
being temporary and must have unique system wide names. Since this process has
the capability of being run more than once at a time then that could present
problems. Beyond that I'd have to make sure it was dropped after the process
finished. Obviously i could use a begin_work statement (and thus if the
program exited then it would rollback), but then it would log all the work,
which i really don't need it to do.
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
Dont document the problem, fix it.
Atli Björgvin Oddsson
________________________________________
From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John Miller iii
[miller3@us.ibm.com]
Sent: Monday, October 11, 2010 6:52 PM
To: ids@iiug.org
Subject: RE: RE: using ? in "in" [21628]
Have you considered external tables? This does require the load/unload=
file to be
accessible from the database server.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
> [image removed]
>
> RE: RE: using ? in "in" [21624]
>
> Wyza, Jonathon
>
> to:
>
> ids
>
> 10/11/2010 01:54 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> True, but it seems faster in perl to do this:
>
> INSERT INTO temp_table(id) VALUES (list of values that goes to charac=ter
> limit); #run until we run out of values
>
> Than to do this
>
> While(list of values)
> INSERT INTO temp_table(id) VALUES(value)>
> I'd pay good money for the load from file to not be a isql/dbaccess
> only thing
> and to work in DBI.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Bj=F6rgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=
Art
> Kagel
> Sent: Monday, October 11, 2010 4:14 PM
> To: ids@iiug.org
> Subject: Re: RE: using ? in "in" [21621]
>
> 64K actually. But putting the list of values into temp table also sol=
ves
the
> max statement length problem.
>
> Art
> On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
wrote:
> > Nah, the problem is the limit in the size of the string. I was hopi=
ng
> > to
> get
> > around the 32k character limit.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Bj=F6rgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
Of
> > Walt
>
> > Hultgren
> > Sent: Monday, October 11, 2010 3:07 PM
> > To: ids@iiug.org
> > Subject: Re: using ? in "in" [21617]
> >
> > If you know the number of values, either by program logic or by
> > collecting
> and
> > scanning user input, you could build the query string with the
> > appropriate
>
> > number of question marks before it's prepared.
> >
> > Walt.
> >
> > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
> >
> >> You can but you need one for each item in the list. You can't just=
> >> use one ? for the whole list. For a variable length list use a tem=
p
table.
> >>
> >> Art
> >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu=
>
> >> wrote:
> >>
> >> --00163630ee9fdea2c004925be309
> >>
> >>
> >>
> >
> >
>
>
>
***********************************************************************=
********
>
> >> Forum Note: Use "Reply" to post a response in the discussion forum=
..
> >>
> >
> > --
> > Walt Hultgren
> > walt.hultgren@emory.edu
> >
> >
> >
>
>
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
> >
>
>
>
***********************************************************************=
********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
>
> --000e0cd30b74875c6d04925cfe58
>
>
>
***********************************************************************=
********
> 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.
When you say system wide name, I think you mean database
wide. That would be true.
How about you create a sequence,
Use this sequence number in the external table name.
create the external table
Take action upon the data
drop the external table
If you are worried about stray external tables, just create a database
scheduler task that will cleanup up all external tables which are older
than 1 day. The creation time is already stored with the creation of the
external table.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/11/2010 04:44:14 PM:
> [image removed]
>
> RE: RE: using ? in "in" [21629]
>
> Wyza, Jonathon
>
> to:
>
> ids
>
> 10/11/2010 04:44 PM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> That might work, except it appears that external tables are not capable
of
> being temporary and must have unique system wide names. Since this
> process has
> the capability of being run more than once at a time then that could
present
> problems. Beyond that I'd have to make sure it was dropped after the
process
> finished. Obviously i could use a begin_work statement (and thus if the
> program exited then it would rollback), but then it would log all the
work,
> which i really don't need it to do.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> “Don’t document the problem, fix it.”
> – Atli Björgvin Oddsson
>
> ________________________________________
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> Miller iii
> [miller3@us.ibm.com]
> Sent: Monday, October 11, 2010 6:52 PM
> To: ids@iiug.org
> Subject: RE: RE: using ? in "in" [21628]
>
> Have you considered external tables? This does require the load/unload=
>
> file to be
> accessible from the database server.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
>
> > [image removed]
> >
> > RE: RE: using ? in "in" [21624]
> >
> > Wyza, Jonathon
> >
> > to:
> >
> > ids
> >
> > 10/11/2010 01:54 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > True, but it seems faster in perl to do this:
> >
> > INSERT INTO temp_table(id) VALUES (list of values that goes to charac=> ter
> > limit); #run until we run out of values
> >
> > Than to do this
> >
> > While(list of values)
> > INSERT INTO temp_table(id) VALUES(value)> >
> > I'd pay good money for the load from file to not be a isql/dbaccess
> > only thing
> > and to work in DBI.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Bj=F6rgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=
> Art
>
> > Kagel
> > Sent: Monday, October 11, 2010 4:14 PM
> > To: ids@iiug.org
> > Subject: Re: RE: using ? in "in" [21621]
> >
> > 64K actually. But putting the list of values into temp table also sol=
> ves
> the
> > max statement length problem.
> >
> > Art
> > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> wrote:
> > > Nah, the problem is the limit in the size of the string. I was hopi=
> ng
> > > to
> > get
> > > around the 32k character limit.
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > >
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Bj=F6rgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
> Of
> > > Walt
> >
> > > Hultgren
> > > Sent: Monday, October 11, 2010 3:07 PM
> > > To: ids@iiug.org
> > > Subject: Re: using ? in "in" [21617]
> > >
> > > If you know the number of values, either by program logic or by
> > > collecting
> > and
> > > scanning user input, you could build the query string with the
> > > appropriate
> >
> > > number of question marks before it's prepared.
> > >
> > > Walt.
> > >
> > > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
> > >
> > >> You can but you need one for each item in the list. You can't just=
>
> > >> use one ? for the whole list. For a variable length list use a tem=
> p
> table.
> > >>
> > >> Art
> > >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu=
> >
> > >> wrote:
> > >>
> > >> --00163630ee9fdea2c004925be309
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
> >
> ********************************
Naw, it's simpler than that. The underlying file that an external table
maps into the data doesn't actually have to exist, so, all you have to do is
include a file or run identifier in the file's records and map the table to
a wildcard representation of the filename(s). Then multiple jobs can map in
their own files and in the IN clause sub-query select .... where jobid =
myjobid. So the external table would be defined as something like:
create external table value_lists (
jobid int,
value_id in
)
USING ( FORMAT DELIMITED DELIMITER ''|' )
DATAFILES ( DISK:/value_files_dir/value_file.%r(1..99999));
Now each job can create a file that has lines like:
<jobnum>|value1|
<jobnum>|value2|
...
and name the file /value_files_dir/value_file.<jobnum>
and the IN clause looks like:
... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
After the job completes just delete, move, or rename the job's values file
and IDS won't know that it exists anymore. Existing jobs that have the file
open will still see its data, but since the records in that file don't
contain the other job's jobid they won't see the rows in there anyway.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> That might work, except it appears that external tables are not capable of
> being temporary and must have unique system wide names. Since this process
> has
> the capability of being run more than once at a time then that could
> present
> problems. Beyond that I'd have to make sure it was dropped after the
> process
> finished. Obviously i could use a begin_work statement (and thus if the
> program exited then it would rollback), but then it would log all the work,
> which i really don't need it to do.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> Dont document the problem, fix it.
> Atli Björgvin Oddsson
>
> ________________________________________
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John Miller
> iii
> [miller3@us.ibm.com]
> Sent: Monday, October 11, 2010 6:52 PM
> To: ids@iiug.org
> Subject: RE: RE: using ? in "in" [21628]
>
> Have you considered external tables? This does require the load/unload=
>
> file to be
> accessible from the database server.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
>
> > [image removed]
> >
> > RE: RE: using ? in "in" [21624]
> >
> > Wyza, Jonathon
> >
> > to:
> >
> > ids
> >
> > 10/11/2010 01:54 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > True, but it seems faster in perl to do this:
> >
> > INSERT INTO temp_table(id) VALUES (list of values that goes to charac=> ter
> > limit); #run until we run out of values
> >
> > Than to do this
> >
> > While(list of values)
> > INSERT INTO temp_table(id) VALUES(value)> >
> > I'd pay good money for the load from file to not be a isql/dbaccess
> > only thing
> > and to work in DBI.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Bj=F6rgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=
> Art
>
> > Kagel
> > Sent: Monday, October 11, 2010 4:14 PM
> > To: ids@iiug.org
> > Subject: Re: RE: using ? in "in" [21621]
> >
> > 64K actually. But putting the list of values into temp table also sol=
> ves
> the
> > max statement length problem.
> >
> > Art
> > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> wrote:
> > > Nah, the problem is the limit in the size of the string. I was hopi=
> ng
> > > to
> > get
> > > around the 32k character limit.
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> =3D=3D=3D=3D=3D=3D=3D
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Bj=F6rgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf =
> Of
> > > Walt
> >
> > > Hultgren
> > > Sent: Monday, October 11, 2010 3:07 PM
> > > To: ids@iiug.org
> > > Subject: Re: using ? in "in" [21617]
> > >
> > > If you know the number of values, either by program logic or by
> > > collecting
> > and
> > > scanning user input, you could build the query string with the
> > > appropriate
> >
> > > number of question marks before it's prepared.
> > >
> > > Walt.
> > >
> > > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
> > >
> > >> You can but you need one for each item in the list. You can't just=
>
> > >> use one ? for the whole list. For a variable length list use a tem=
> p
> table.
> > >>
> > >> Art
> > >> On Oct 11, 2010 2:34 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu=
> >
> > >> wrote:
> > >>
> > >> --00163630ee9fdea2c004925be309
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
> >
> ***********************************************************************=
> ********
>
> >
> > >> Forum Note: Use "Reply" to post a response in the discussion forum=
> ...
> > >>
> > >
> > > --
> > > Walt Hultgren
> > > walt.hultgren@emory.edu
> > >
> > >
> > >
> >
> >
> >
> ***********************************************************************=
> ********
>
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.=
>
> > >
Clear as a bell!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Mon, Oct 11, 2010 at 7:58 PM, John Miller iii <miller3@us.ibm.com> wrote:
>
> When you say system wide name, I think you mean database
> wide. That would be true.
>
> How about you create a sequence,
>
> Use this sequence number in the external table name.
>
> create the external table
> Take action upon the data
> drop the external table
>
> If you are worried about stray external tables, just create a database
> scheduler task that will cleanup up all external tables which are older
> than 1 day. The creation time is already stored with the creation of the
> external table.
>
>
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 10/11/2010 04:44:14 PM:
>
> > [image removed]
> >
> > RE: RE: using ? in "in" [21629]
> >
> > Wyza, Jonathon
> >
> > to:
> >
> > ids
> >
> > 10/11/2010 04:44 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > That might work, except it appears that external tables are not capable
> of
> > being temporary and must have unique system wide names. Since this
> > process has
> > the capability of being run more than once at a time then that could
> present
> > problems. Beyond that I'd have to make sure it was dropped after the
> process
> > finished. Obviously i could use a begin_work statement (and thus if the
> > program exited then it would rollback), but then it would log all the
> work,
> > which i really don't need it to do.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > ==============================
> > SLES 11x64 & IDS 11.50.FC6
> >
> > “Don’t document the problem, fix it.”
> > – Atli Björgvin Oddsson
> >
> > ________________________________________
> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> > Miller iii
> > [miller3@us.ibm.com]
> > Sent: Monday, October 11, 2010 6:52 PM
> > To: ids@iiug.org
> > Subject: RE: RE: using ? in "in" [21628]
> >
> > Have you considered external tables? This does require the load/unload=
> >
> > file to be
> > accessible from the database server.
> >
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
> >
> > > [image removed]
> > >
> > > RE: RE: using ? in "in" [21624]
> > >
> > > Wyza, Jonathon
> > >
> > > to:
> > >
> > > ids
> > >
> > > 10/11/2010 01:54 PM
> > >
> > > Sent by:
> > >
> > > ids-bounces@iiug.org
> > >
> > > Please respond to ids
> > >
> > > True, but it seems faster in perl to do this:
> > >
> > > INSERT INTO temp_table(id) VALUES (list of values that goes to charac=> > ter
> > > limit); #run until we run out of values
> > >
> > > Than to do this
> > >
> > > While(list of values)
> > > INSERT INTO temp_table(id) VALUES(value)> > >
> > > I'd pay good money for the load from file to not be a isql/dbaccess
> > > only thing
> > > and to work in DBI.
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> > =3D=3D=3D=3D=3D=3D=3D
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Bj=F6rgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=
> > Art
> >
> > > Kagel
> > > Sent: Monday, October 11, 2010 4:14 PM
> > > To: ids@iiug.org
> > > Subject: Re: RE: using ? in "in" [21621]
> > >
> > > 64K actually. But putting the list of values into temp table also sol=
> > ves
> > the
> > > max statement length problem.
> > >
> > > Art
> > > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> > wrote:
> > > > Nah, the problem is the limit in the size of the string. I was hopi=
> > ng
> > > > to
> > > get
> > > > around the 32k character limit.
> > > >
> > > > Jonathon Wyza
> > > > CX & CBORD System Administrator
> > > > CX Programmer/Analyst
> > > > Administrative Computing
> > > > Bethel College
> > > > (574)-257-3381
> > > > AIM: Iamwyza
> > > > jonathon.wyza@bethelcollege.edu
> > > >
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
> > =3D=3D=3D=3D=3D=3D=3D
> > > > SLES 11x64 & IDS 11.50.FC6
> > > >
> > > > "Don't document the problem, fix it."
> > > > - Atli Bj=F6rgvin Oddsson
> > > >
> > > > -----Original Message-----
> > > > From: ids-
When I delete the file it will then erase the data from the table then as well
(it's a true link then, not an import)
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, October 11, 2010 8:05 PM
To: ids@iiug.org
Subject: Re: RE: using ? in "in" [21631]
Naw, it's simpler than that. The underlying file that an external table maps
into the data doesn't actually have to exist, so, all you have to do is
include a file or run identifier in the file's records and map the table to a
wildcard representation of the filename(s). Then multiple jobs can map in
their own files and in the IN clause sub-query select .... where jobid =
myjobid. So the external table would be defined as something like:
create external table value_lists (
jobid int,
value_id in
)
USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
DISK:/value_files_dir/value_file.%r(1..99999));
Now each job can create a file that has lines like:
<jobnum>|value1|
<jobnum>|value2|
....
and name the file /value_files_dir/value_file.<jobnum>
and the IN clause looks like:
.... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
After the job completes just delete, move, or rename the job's values file and
IDS won't know that it exists anymore. Existing jobs that have the file open
will still see its data, but since the records in that file don't contain the
other job's jobid they won't see the rows in there anyway.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> That might work, except it appears that external tables are not
> capable of being temporary and must have unique system wide names.
> Since this process has the capability of being run more than once at a
> time then that could present problems. Beyond that I'd have to make
> sure it was dropped after the process finished. Obviously i could use
> a begin_work statement (and thus if the program exited then it would
> rollback), but then it would log all the work, which i really don't
> need it to do.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> ________________________________________
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> Miller iii [miller3@us.ibm.com]
> Sent: Monday, October 11, 2010 6:52 PM
> To: ids@iiug.org
> Subject: RE: RE: using ? in "in" [21628]
>
> Have you considered external tables? This does require the
> load/unload=
>
> file to be
> accessible from the database server.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
>
> > [image removed]
> >
> > RE: RE: using ? in "in" [21624]
> >
> > Wyza, Jonathon
> >
> > to:
> >
> > ids
> >
> > 10/11/2010 01:54 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > True, but it seems faster in perl to do this:
> >
> > INSERT INTO temp_table(id) VALUES (list of values that goes to> > charac=
> ter
> > limit); #run until we run out of values
> >
> > Than to do this
> >
> > While(list of values)
> > INSERT INTO temp_table(id) VALUES(value)> >
> > I'd pay good money for the load from file to not be a isql/dbaccess
> > only thing and to work in DBI.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
> > D=
> =3D=3D=3D=3D=3D=3D=3D
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Bj=F6rgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of=
> Art
>
> > Kagel
> > Sent: Monday, October 11, 2010 4:14 PM
> > To: ids@iiug.org
> > Subject: Re: RE: using ? in "in" [21621]
> >
> > 64K actually. But putting the list of values into temp table also
> > sol=
> ves
> the
> > max statement length problem.
> >
> > Art
> > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> wrote:
> > > Nah, the problem is the limit in the size of the string. I was
> > > hopi=
> ng
> > > to
> > get
> > > around the 32k character limit.
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > =3D=
> =3D=3D=3D=3D=3D=3D=3D
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Bj=F6rgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > =
> Of
> > > Walt
> >
> > > Hultgren
> > > Sent: Monday, October 11, 2010 3:07 PM
> > > To: ids@iiug.org
> > > Subject: Re: using ? in "in" [21617]
> > >
> > > If you know the number of values, either by program logic or by
> > > collecting
> > and
> > > scanning user input, you could build the query string with the
> > > appropriate
> >
> > > number of question marks before it's prepared.
> > >
> > > Walt.
> > >
> > > On Oct 11, 2010, at 2:54 PM, Art Kagel wrote:
> > >
> > >> You can but you need one for each item in the list. You can't
> > >> just=
>
> > >> use one ? for the whole list. For a variable length list use a
> > >> tem=
> p
> table.
> > >>
> > >> Art
> > >> On
On Tue, Oct 12, 2010 at 2:59 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:
> When I delete the file it will then erase the data from the table then as
> well
> (it's a true link then, not an import)
>
>
The idea is to create the external table (link a table definition to a
file), then INSERT INTO real_table SELECT * FROM ext_table and then you can
remove the external table...
Regards.
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, October 11, 2010 8:05 PM
> To: ids@iiug.org
> Subject: Re: RE: using ? in "in" [21631]
>
> Naw, it's simpler than that. The underlying file that an external table
> maps
> into the data doesn't actually have to exist, so, all you have to do is
> include a file or run identifier in the file's records and map the table to
> a
> wildcard representation of the filename(s). Then multiple jobs can map in
> their own files and in the IN clause sub-query select .... where jobid =
> myjobid. So the external table would be defined as something like:
>
> create external table value_lists (
>
> jobid int,
>
> value_id in
> )
> USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
> DISK:/value_files_dir/value_file.%r(1..99999));
>
> Now each job can create a file that has lines like:
> <jobnum>|value1|
> <jobnum>|value2|
> .....
>
> and name the file /value_files_dir/value_file.<jobnum>
>
> and the IN clause looks like:
>
> ..... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
>
> After the job completes just delete, move, or rename the job's values file
> and
> IDS won't know that it exists anymore. Existing jobs that have the file
> open
> will still see its data, but since the records in that file don't contain
> the
> other job's jobid they won't see the rows in there anyway.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
> <wyzaj@bethelcollege.edu>wrote:
>
> > That might work, except it appears that external tables are not
> > capable of being temporary and must have unique system wide names.
> > Since this process has the capability of being run more than once at a
> > time then that could present problems. Beyond that I'd have to make
> > sure it was dropped after the process finished. Obviously i could use
> > a begin_work statement (and thus if the program exited then it would
> > rollback), but then it would log all the work, which i really don't
> > need it to do.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > ==============================
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Björgvin Oddsson
> >
> > ________________________________________
> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> > Miller iii [miller3@us.ibm.com]
> > Sent: Monday, October 11, 2010 6:52 PM
> > To: ids@iiug.org
> > Subject: RE: RE: using ? in "in" [21628]
> >
> > Have you considered external tables? This does require the
> > load/unload=
> >
> > file to be
> > accessible from the database server.
> >
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
> >
> > > [image removed]
> > >
> > > RE: RE: using ? in "in" [21624]
> > >
> > > Wyza, Jonathon
> > >
> > > to:
> > >
> > > ids
> > >
> > > 10/11/2010 01:54 PM
> > >
> > > Sent by:
> > >
> > > ids-bounces@iiug.org
> > >
> > > Please respond to ids
> > >
> > > True, but it seems faster in perl to do this:
> > >
> > > INSERT INTO temp_table(id) VALUES (list of values that goes to> > > charac=
> > ter
> > > limit); #run until we run out of values
> > >
> > > Than to do this
> > >
> > > While(list of values)
> > > INSERT INTO temp_table(id) VALUES(value)> > >
> > > I'd pay good money for the load from file to not be a isql/dbaccess
> > > only thing and to work in DBI.
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
> > > D=
> > =3D=3D=3D=3D=3D=3D=3D
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Bj=F6rgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > Of=
> > Art
> >
> > > Kagel
> > > Sent: Monday, October 11, 2010 4:14 PM
> > > To: ids@iiug.org
> > > Subject: Re: RE: using ? in "in" [21621]
> > >
> > > 64K actually. But putting the list of values into temp table also
> > > sol=
> > ves
> > the
> > > max statement length problem.
> > >
> > > Art
> > > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> > wrote:
> > > > Nah, the problem is the limit in the size of the string. I was
> > > > hopi=
> > ng
> > > > to
> > > get
> > > > around the 32k character limit.
> > > >
> > > > Jonathon Wyza
> > > > CX & CBORD System Administrator
> > > > CX Programmer/Analyst
> > > > Administrative Computing
> > > > Bethel College
> > > > (574)-257-3381
> > > > AIM: Iamwyza
> > > > jonathon.wyza@bethelcollege.edu
> > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > > =3D=
> > =3D=3D=3D=3D=3D=3D=3D
> > > > SLES 11x64 & IDS 11.50.FC6
> > > >
> > > > "Don't document the problem, fix it."
> > > > - Atli Bj=F6rgvin Oddsson
> > > >
> > > > -----Original Message-----
> > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > > =
> > Of
> > > > Walt
> > >
> > > > Hultg
Correct.
On Oct 12, 2010 10:00 AM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu> wrote:
> When I delete the file it will then erase the data from the table then as
well
> (it's a true link then, not an import)
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, October 11, 2010 8:05 PM
> To: ids@iiug.org
> Subject: Re: RE: using ? in "in" [21631]
>
> Naw, it's simpler than that. The underlying file that an external table
maps
> into the data doesn't actually have to exist, so, all you have to do is
> include a file or run identifier in the file's records and map the table
to a
> wildcard representation of the filename(s). Then multiple jobs can map in
> their own files and in the IN clause sub-query select .... where jobid =
> myjobid. So the external table would be defined as something like:
>
> create external table value_lists (
>
> jobid int,
>
> value_id in
> )
> USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
> DISK:/value_files_dir/value_file.%r(1..99999));
>
> Now each job can create a file that has lines like:
> <jobnum>|value1|
> <jobnum>|value2|
> .....
>
> and name the file /value_files_dir/value_file.<jobnum>
>
> and the IN clause looks like:
>
> ..... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
>
> After the job completes just delete, move, or rename the job's values file
and
> IDS won't know that it exists anymore. Existing jobs that have the file
open
> will still see its data, but since the records in that file don't contain
the
> other job's jobid they won't see the rows in there anyway.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
by
> inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
> <wyzaj@bethelcollege.edu>wrote:
>
>> That might work, except it appears that external tables are not
>> capable of being temporary and must have unique system wide names.
>> Since this process has the capability of being run more than once at a
>> time then that could present problems. Beyond that I'd have to make
>> sure it was dropped after the process finished. Obviously i could use
>> a begin_work statement (and thus if the program exited then it would
>> rollback), but then it would log all the work, which i really don't
>> need it to do.
>>
>> Jonathon Wyza
>> CX & CBORD System Administrator
>> CX Programmer/Analyst
>> Administrative Computing
>> Bethel College
>> (574)-257-3381
>> AIM: Iamwyza
>> jonathon.wyza@bethelcollege.edu
>> ==============================
>> SLES 11x64 & IDS 11.50.FC6
>>
>> "Don't document the problem, fix it."
>> - Atli Björgvin Oddsson
>>
>> ________________________________________
>> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
>> Miller iii [miller3@us.ibm.com]
>> Sent: Monday, October 11, 2010 6:52 PM
>> To: ids@iiug.org
>> Subject: RE: RE: using ? in "in" [21628]
>>
>> Have you considered external tables? This does require the
>> load/unload=
>>
>> file to be
>> accessible from the database server.
>>
>> John F. Miller III
>> STSM, Embedability Architect
>> miller3@us.ibm.com
>> 503-578-5645
>> IBM Informix Dynamic Server (IDS)
>>
>> ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
>>
>> > [image removed]
>> >
>> > RE: RE: using ? in "in" [21624]
>> >
>> > Wyza, Jonathon
>> >
>> > to:
>> >
>> > ids
>> >
>> > 10/11/2010 01:54 PM
>> >
>> > Sent by:
>> >
>> > ids-bounces@iiug.org
>> >
>> > Please respond to ids
>> >
>> > True, but it seems faster in perl to do this:
>> >
>> > INSERT INTO temp_table(id) VALUES (list of values that goes to>> > charac=
>> ter
>> > limit); #run until we run out of values
>> >
>> > Than to do this
>> >
>> > While(list of values)
>> > INSERT INTO temp_table(id) VALUES(value)>> >
>> > I'd pay good money for the load from file to not be a isql/dbaccess
>> > only thing and to work in DBI.
>> >
>> > Jonathon Wyza
>> > CX & CBORD System Administrator
>> > CX Programmer/Analyst
>> > Administrative Computing
>> > Bethel College
>> > (574)-257-3381
>> > AIM: Iamwyza
>> > jonathon.wyza@bethelcollege.edu
>> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
>> > D=
>> =3D=3D=3D=3D=3D=3D=3D
>> > SLES 11x64 & IDS 11.50.FC6
>> >
>> > "Don't document the problem, fix it."
>> > - Atli Bj=F6rgvin Oddsson
>> >
>> > -----Original Message-----
>> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
>> > Of=
>> Art
>>
>> > Kagel
>> > Sent: Monday, October 11, 2010 4:14 PM
>> > To: ids@iiug.org
>> > Subject: Re: RE: using ? in "in" [21621]
>> >
>> > 64K actually. But putting the list of values into temp table also
>> > sol=
>> ves
>> the
>> > max statement length problem.
>> >
>> > Art
>> > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
>> wrote:
>> > > Nah, the problem is the limit in the size of the string. I was
>> > > hopi=
>> ng
>> > > to
>> > get
>> > > around the 32k character limit.
>> > >
>> > > Jonathon Wyza
>> > > CX & CBORD System Administrator
>> > > CX Programmer/Analyst
>> > > Administrative Computing
>> > > Bethel College
>> > > (574)-257-3381
>> > > AIM: Iamwyza
>> > > jonathon.wyza@bethelcollege.edu
>> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>> > > =3D=
>> =3D=3D=3D=3D=3D=3D=3D
>> > > SLES 11x64 & IDS 11.50.FC6
>> > >
>> > > "Don't document the problem, fix it."
>> > > - Atli Bj=F6rgvin Oddsson
>> > >
>> > > -----Original Message-----
>> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
>> > > =
>> Of
>> > > Walt
>> >
>> > > Hultgren
>> > > Sent: Monday, October 11, 2010 3:07 PM
>> > > To: ids@iiug.org
>> > > Subject: Re: using ? in "in" [21617]
>> > >
>> > > If you know the number of values, either by program logic or by
>> > > collecting
>> > and
>> > > scanning user input, you could build the query string with the
>> > > appropr
He wants to use the extra table to drive another task.
Art
On Oct 12, 2010 10:12 AM, "Fernando Nunes" <domusonline@gmail.com> wrote:
> On Tue, Oct 12, 2010 at 2:59 PM, Wyza, Jonathon
> <wyzaj@bethelcollege.edu>wrote:
>
>> When I delete the file it will then erase the data from the table then as
>> well
>> (it's a true link then, not an import)
>>
>>
> The idea is to create the external table (link a table definition to a
> file), then INSERT INTO real_table SELECT * FROM ext_table and then you
can
> remove the external table...
>
> Regards.
>
>> Jonathon Wyza
>> CX & CBORD System Administrator
>> CX Programmer/Analyst
>> Administrative Computing
>> Bethel College
>> (574)-257-3381
>> AIM: Iamwyza
>> jonathon.wyza@bethelcollege.edu
>> ==============================
>> SLES 11x64 & IDS 11.50.FC6
>>
>> "Don't document the problem, fix it."
>> - Atli Björgvin Oddsson
>>
>> -----Original Message-----
>> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
>> Kagel
>> Sent: Monday, October 11, 2010 8:05 PM
>> To: ids@iiug.org
>> Subject: Re: RE: using ? in "in" [21631]
>>
>> Naw, it's simpler than that. The underlying file that an external table
>> maps
>> into the data doesn't actually have to exist, so, all you have to do is
>> include a file or run identifier in the file's records and map the table
to
>> a
>> wildcard representation of the filename(s). Then multiple jobs can map in
>> their own files and in the IN clause sub-query select .... where jobid =
>> myjobid. So the external table would be defined as something like:
>>
>> create external table value_lists (
>>
>> jobid int,
>>
>> value_id in
>> )
>> USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
>> DISK:/value_files_dir/value_file.%r(1..99999));
>>
>> Now each job can create a file that has lines like:
>> <jobnum>|value1|
>> <jobnum>|value2|
>> .....
>>
>> and name the file /value_files_dir/value_file.<jobnum>
>>
>> and the IN clause looks like:
>>
>> ..... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
>>
>> After the job completes just delete, move, or rename the job's values
file
>> and
>> IDS won't know that it exists anymore. Existing jobs that have the file
>> open
>> will still see its data, but since the records in that file don't contain
>> the
>> other job's jobid they won't see the rows in there anyway.
>>
>> Art
>>
>> Art S. Kagel
>> Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any
other
>> organization with which I am associated either explicitly, implicitly, or
>> by
>> inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
>> <wyzaj@bethelcollege.edu>wrote:
>>
>> > That might work, except it appears that external tables are not
>> > capable of being temporary and must have unique system wide names.
>> > Since this process has the capability of being run more than once at a
>> > time then that could present problems. Beyond that I'd have to make
>> > sure it was dropped after the process finished. Obviously i could use
>> > a begin_work statement (and thus if the program exited then it would
>> > rollback), but then it would log all the work, which i really don't
>> > need it to do.
>> >
>> > Jonathon Wyza
>> > CX & CBORD System Administrator
>> > CX Programmer/Analyst
>> > Administrative Computing
>> > Bethel College
>> > (574)-257-3381
>> > AIM: Iamwyza
>> > jonathon.wyza@bethelcollege.edu
>> > ==============================
>> > SLES 11x64 & IDS 11.50.FC6
>> >
>> > "Don't document the problem, fix it."
>> > - Atli Björgvin Oddsson
>> >
>> > ________________________________________
>> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
>> > Miller iii [miller3@us.ibm.com]
>> > Sent: Monday, October 11, 2010 6:52 PM
>> > To: ids@iiug.org
>> > Subject: RE: RE: using ? in "in" [21628]
>> >
>> > Have you considered external tables? This does require the
>> > load/unload=
>> >
>> > file to be
>> > accessible from the database server.
>> >
>> > John F. Miller III
>> > STSM, Embedability Architect
>> > miller3@us.ibm.com
>> > 503-578-5645
>> > IBM Informix Dynamic Server (IDS)
>> >
>> > ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
>> >
>> > > [image removed]
>> > >
>> > > RE: RE: using ? in "in" [21624]
>> > >
>> > > Wyza, Jonathon
>> > >
>> > > to:
>> > >
>> > > ids
>> > >
>> > > 10/11/2010 01:54 PM
>> > >
>> > > Sent by:
>> > >
>> > > ids-bounces@iiug.org
>> > >
>> > > Please respond to ids
>> > >
>> > > True, but it seems faster in perl to do this:
>> > >
>> > > INSERT INTO temp_table(id) VALUES (list of values that goes to>> > > charac=
>> > ter
>> > > limit); #run until we run out of values
>> > >
>> > > Than to do this
>> > >
>> > > While(list of values)
>> > > INSERT INTO temp_table(id) VALUES(value)>> > >
>> > > I'd pay good money for the load from file to not be a isql/dbaccess
>> > > only thing and to work in DBI.
>> > >
>> > > Jonathon Wyza
>> > > CX & CBORD System Administrator
>> > > CX Programmer/Analyst
>> > > Administrative Computing
>> > > Bethel College
>> > > (574)-257-3381
>> > > AIM: Iamwyza
>> > > jonathon.wyza@bethelcollege.edu
>> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
>> > > D=
>> > =3D=3D=3D=3D=3D=3D=3D
>> > > SLES 11x64 & IDS 11.50.FC6
>> > >
>> > > "Don't document the problem, fix it."
>> > > - Atli Bj=F6rgvin Oddsson
>> > >
>> > > -----Original Message-----
>> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
>> > > Of=
>> > Art
>> >
>> > > Kagel
>> > > Sent: Monday, October 11, 2010 4:14 PM
>> > > To: ids@iiug.org
>> > > Subject: Re: RE: using ? in "in" [21621]
>> > >
>> > > 64K actually. But putting the list of values into temp table also
>> > > sol=
>> > ves
>> > the
>> > > max statement length problem.
>> > >
>> > > Art
>> > > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
>> > wrote:
>> > > > Nah, the problem is the limit in the size of the string. I was
>> > > > hopi=
>> > ng
>> > > > to
>> > > get
>> > > > around the 32k character limit.
>> > > >
>> > > > Jonathon Wyza
>> > > > CX & CBORD System Administrator
>> > > > CX Programmer/Analyst
>> > > > Administrative Computing
>> > > > Bethel College
>> > > > (574)-257-3381
>> > > > AIM: Iamwyza
>> > > > jonathon.wyza@bethelcollege.edu
>> > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Ups... Sorry. I missed that part.
Regards.
On Tue, Oct 12, 2010 at 3:17 PM, Art Kagel <art.kagel@gmail.com> wrote:
> He wants to use the extra table to drive another task.
>
> Art
> On Oct 12, 2010 10:12 AM, "Fernando Nunes" <domusonline@gmail.com> wrote:
> > On Tue, Oct 12, 2010 at 2:59 PM, Wyza, Jonathon
> > <wyzaj@bethelcollege.edu>wrote:
> >
> >> When I delete the file it will then erase the data from the table then
> as
>
> >> well
> >> (it's a true link then, not an import)
> >>
> >>
> > The idea is to create the external table (link a table definition to a
> > file), then INSERT INTO real_table SELECT * FROM ext_table and then you
> can
> > remove the external table...
> >
> > Regards.
> >
> >> Jonathon Wyza
> >> CX & CBORD System Administrator
> >> CX Programmer/Analyst
> >> Administrative Computing
> >> Bethel College
> >> (574)-257-3381
> >> AIM: Iamwyza
> >> jonathon.wyza@bethelcollege.edu
> >> ==============================
> >> SLES 11x64 & IDS 11.50.FC6
> >>
> >> "Don't document the problem, fix it."
> >> - Atli Björgvin Oddsson
> >>
> >> -----Original Message-----
> >> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
>
> >> Kagel
> >> Sent: Monday, October 11, 2010 8:05 PM
> >> To: ids@iiug.org
> >> Subject: Re: RE: using ? in "in" [21631]
> >>
> >> Naw, it's simpler than that. The underlying file that an external table
> >> maps
> >> into the data doesn't actually have to exist, so, all you have to do is
> >> include a file or run identifier in the file's records and map the table
> to
> >> a
> >> wildcard representation of the filename(s). Then multiple jobs can map
> in
>
> >> their own files and in the IN clause sub-query select .... where jobid =
> >> myjobid. So the external table would be defined as something like:
> >>
> >> create external table value_lists (
> >>
> >> jobid int,
> >>
> >> value_id in
> >> )
> >> USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
> >> DISK:/value_files_dir/value_file.%r(1..99999));
> >>
> >> Now each job can create a file that has lines like:
> >> <jobnum>|value1|
> >> <jobnum>|value2|
> >> .....
> >>
> >> and name the file /value_files_dir/value_file.<jobnum>
> >>
> >> and the IN clause looks like:
> >>
> >> ..... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
> >>
> >> After the job completes just delete, move, or rename the job's values
> file
> >> and
> >> IDS won't know that it exists anymore. Existing jobs that have the file
> >> open
> >> will still see its data, but since the records in that file don't
> contain
>
> >> the
> >> other job's jobid they won't see the rows in there anyway.
> >>
> >> Art
> >>
> >> Art S. Kagel
> >> Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any
> other
> >> organization with which I am associated either explicitly, implicitly,
> or
>
> >> by
> >> inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
> >> <wyzaj@bethelcollege.edu>wrote:
> >>
> >> > That might work, except it appears that external tables are not
> >> > capable of being temporary and must have unique system wide names.
> >> > Since this process has the capability of being run more than once at a
> >> > time then that could present problems. Beyond that I'd have to make
> >> > sure it was dropped after the process finished. Obviously i could use
> >> > a begin_work statement (and thus if the program exited then it would
> >> > rollback), but then it would log all the work, which i really don't
> >> > need it to do.
> >> >
> >> > Jonathon Wyza
> >> > CX & CBORD System Administrator
> >> > CX Programmer/Analyst
> >> > Administrative Computing
> >> > Bethel College
> >> > (574)-257-3381
> >> > AIM: Iamwyza
> >> > jonathon.wyza@bethelcollege.edu
> >> > ==============================
> >> > SLES 11x64 & IDS 11.50.FC6
> >> >
> >> > "Don't document the problem, fix it."
> >> > - Atli Björgvin Oddsson
> >> >
> >> > ________________________________________
> >> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> >> > Miller iii [miller3@us.ibm.com]
> >> > Sent: Monday, October 11, 2010 6:52 PM
> >> > To: ids@iiug.org
> >> > Subject: RE: RE: using ? in "in" [21628]
> >> >
> >> > Have you considered external tables? This does require the
> >> > load/unload=
> >> >
> >> > file to be
> >> > accessible from the database server.
> >> >
> >> > John F. Miller III
> >> > STSM, Embedability Architect
> >> > miller3@us.ibm.com
> >> > 503-578-5645
> >> > IBM Informix Dynamic Server (IDS)
> >> >
> >> > ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
> >> >
> >> > > [image removed]
> >> > >
> >> > > RE: RE: using ? in "in" [21624]
> >> > >
> >> > > Wyza, Jonathon
> >> > >
> >> > > to:
> >> > >
> >> > > ids
> >> > >
> >> > > 10/11/2010 01:54 PM
> >> > >
> >> > > Sent by:
> >> > >
> >> > > ids-bounces@iiug.org
> >> > >
> >> > > Please respond to ids
> >> > >
> >> > > True, but it seems faster in perl to do this:
> >> > >
> >> > > INSERT INTO temp_table(id) VALUES (list of values that goes to> >> > > charac=
> >> > ter
> >> > > limit); #run until we run out of values
> >> > >
> >> > > Than to do this
> >> > >
> >> > > While(list of values)
> >> > > INSERT INTO temp_table(id) VALUES(value)> >> > >
> >> > > I'd pay good money for the load from file to not be a isql/dbaccess
> >> > > only thing and to work in DBI.
> >> > >
> >> > > Jonathon Wyza
> >> > > CX & CBORD System Administrator
> >> > > CX Programmer/Analyst
> >> > > Administrative Computing
> >> > > Bethel College
> >> > > (574)-257-3381
> >> > > AIM: Iamwyza
> >> > > jonathon.wyza@bethelcollege.edu
> >> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
> >> > > D=
> >> > =3D=3D=3D=3D=3D=3D=3D
> >> > > SLES 11x64 & IDS 11.50.FC6
> >> > >
> >> > > "Don't document the problem, fix it."
> >> > > - Atli Bj=F6rgvin Oddsson
> >> > >
> >> > > -----Original Message-----
> >> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> >> > > Of=
> >> > Art
> >> >
> >> > > Kagel
> >> > > Sent: Monday, October 11, 2010 4:14 PM
> >> > > To: ids@iiug.org
> >> > > Subject: Re: RE: using ? in "in" [21621]
> >> > >
> >> > > 64K actually. But putting the list of values into temp table also
> >> > > sol=
> >> > ves
> >> > the
> >> > > max statement length problem.
> >> > >
> >> > > Art
> >> > > On Oct 11, 2010 3:26 PM, @
So I did the following:
create external table mwu_adr_tmp ( jobid int, id int )
USING ( FORMAT 'DELIMITED', DELIMITER '|', DATAFILES
("DISK:/tmp/mwu_adr_tmp.%r(1..99999)"));
Which worked, and I created a file with openable permissions :
/tmp/mwu_adr_tmp.1342 , but if I use this sql:
select * from mwu_adr_tmp# ^
#26154: Could not open file: (file, errno)=(mwu_adr_tmp.99999,2).
#
Thoughts?
Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==============================
SLES 11x64 & IDS 11.50.FC6
"Don't document the problem, fix it."
- Atli Björgvin Oddsson
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, October 11, 2010 8:05 PM
To: ids@iiug.org
Subject: Re: RE: using ? in "in" [21631]
Naw, it's simpler than that. The underlying file that an external table maps
into the data doesn't actually have to exist, so, all you have to do is
include a file or run identifier in the file's records and map the table to a
wildcard representation of the filename(s). Then multiple jobs can map in
their own files and in the IN clause sub-query select .... where jobid =
myjobid. So the external table would be defined as something like:
create external table value_lists (
jobid int,
value_id in
)
USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
DISK:/value_files_dir/value_file.%r(1..99999));
Now each job can create a file that has lines like:
<jobnum>|value1|
<jobnum>|value2|
....
and name the file /value_files_dir/value_file.<jobnum>
and the IN clause looks like:
.... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
After the job completes just delete, move, or rename the job's values file and
IDS won't know that it exists anymore. Existing jobs that have the file open
will still see its data, but since the records in that file don't contain the
other job's jobid they won't see the rows in there anyway.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> That might work, except it appears that external tables are not
> capable of being temporary and must have unique system wide names.
> Since this process has the capability of being run more than once at a
> time then that could present problems. Beyond that I'd have to make
> sure it was dropped after the process finished. Obviously i could use
> a begin_work statement (and thus if the program exited then it would
> rollback), but then it would log all the work, which i really don't
> need it to do.
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> ________________________________________
> From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> Miller iii [miller3@us.ibm.com]
> Sent: Monday, October 11, 2010 6:52 PM
> To: ids@iiug.org
> Subject: RE: RE: using ? in "in" [21628]
>
> Have you considered external tables? This does require the
> load/unload=
>
> file to be
> accessible from the database server.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 10/11/2010 01:54:26 PM:
>
> > [image removed]
> >
> > RE: RE: using ? in "in" [21624]
> >
> > Wyza, Jonathon
> >
> > to:
> >
> > ids
> >
> > 10/11/2010 01:54 PM
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Please respond to ids
> >
> > True, but it seems faster in perl to do this:
> >
> > INSERT INTO temp_table(id) VALUES (list of values that goes to> > charac=
> ter
> > limit); #run until we run out of values
> >
> > Than to do this
> >
> > While(list of values)
> > INSERT INTO temp_table(id) VALUES(value)> >
> > I'd pay good money for the load from file to not be a isql/dbaccess
> > only thing and to work in DBI.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3
> > D=
> =3D=3D=3D=3D=3D=3D=3D
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Bj=F6rgvin Oddsson
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of=
> Art
>
> > Kagel
> > Sent: Monday, October 11, 2010 4:14 PM
> > To: ids@iiug.org
> > Subject: Re: RE: using ? in "in" [21621]
> >
> > 64K actually. But putting the list of values into temp table also
> > sol=
> ves
> the
> > max statement length problem.
> >
> > Art
> > On Oct 11, 2010 3:26 PM, "Wyza, Jonathon" <wyzaj@bethelcollege.edu>
> wrote:
> > > Nah, the problem is the limit in the size of the string. I was
> > > hopi=
> ng
> > > to
> > get
> > > around the 32k character limit.
> > >
> > > Jonathon Wyza
> > > CX & CBORD System Administrator
> > > CX Programmer/Analyst
> > > Administrative Computing
> > > Bethel College
> > > (574)-257-3381
> > > AIM: Iamwyza
> > > jonathon.wyza@bethelcollege.edu
> > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > > =3D=
> =3D=3D=3D=3D=3D=3D=3D
> > > SLES 11x64 & IDS 11.50.FC6
> > >
> > > "Don't document the problem, fix it."
> > > - Atli Bj=F6rgvin Oddsson
> > >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > =
> Of
> > > Walt
> >
> > > Hultgren
> > > Sent: Monday, October 11, 2010 3:07 PM
> > > To: ids@iiug.org
> > > Subject: Re: using ? in "in" [21617]
> > >
> > > If you know the number of values, either by program logic or by
> > > collecting
> > and
> > > scanning user input, you could build the query string with the
> > > appropriate
> >
> > > number of question marks before it's prepared.
> > >@
OK, my mistake or my lack of understanding how they implemented the external
tables file checking. Quick and dirty, you can still do this, it's just a
bit hinkey. Here's the scoop:
- The files listed in the DATAFILES clause don't have to exist at the
time that the external table is created, but they do have to exist at the
time the data is accessed.
- Here's the hinkey part. Although the manual calls the %r(n..m) macro a
"wild card" it is not. It is expanded internally, at the time the files are
opened, into a list of all possible filenames. The engine then tries to
open all of the files. If it encounters any errors, it reports the error
and lists the name of the last expanded filename that it received an error
on. So, in your case, file mwu_adr_tmp.1432 existed, but files
mwu_adr_tmp.1 through mwu_adr_tmp.1431 and mwu_adr_tmp.1433 through
mwu_adr_tmp.99999 did not so it reported an errorno 2 (file doesn't exist)
on that last file mwu_adr_tmp.99999.
So, what's the workaround? You have to touch an empty file for every
possible filename. Here, from my testing:
echo "1|1|" >test.1
echo "2|3|" >>test.1
dbaccess big_test -
> create external table testtbl( one int, two int) using (format
'delimited', delimiter '|', datafiles("disk:/home/art/test.%r(1..99)"));
Table created.
> !ix=1; while [[ ix -le 99 ]]; do touch test.$ix; ix=$(( ix + 1 )); done
> select * from testtbl;
one two
1 1
2 3
2 row(s) retrieved.
> ! rm test.9
> select * from testtbl;
26154: Could not open file: (file, errno)=(/home/art/test.9,2).
Error in line 1Near character position 20
> !echo "9|10|" > test.9
> select * from testtbl;
one two
9 10
1 1
2 3
3 row(s) retrieved.
> select * from testtbl where one = 9;
one two
9 10
1 row(s) retrieved.
> ^D
Told you it's a bit hinkey! So, it is impractical to use the pid of the
process as the key for the job in the filename - though you could still use
it for the key value in the external table. You'll have to use a small
integer that's only big enough to allow for all of the concurrent jobs and
figure out a way to assign the numbers to particular jobs either permanently
or dynamically.
Hmm, or just append the lines to a single file.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. 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 Wed, Oct 13, 2010 at 11:22 AM, Wyza, Jonathon
<wyzaj@bethelcollege.edu>wrote:
> So I did the following:
>
> create external table mwu_adr_tmp ( jobid int, id int )
> USING ( FORMAT 'DELIMITED', DELIMITER '|', DATAFILES
> ("DISK:/tmp/mwu_adr_tmp.%r(1..99999)"));
>
> Which worked, and I created a file with openable permissions :
> /tmp/mwu_adr_tmp.1342 , but if I use this sql:
>
> select * from mwu_adr_tmp> # ^
> #26154: Could not open file: (file, errno)=(mwu_adr_tmp.99999,2).
> #
>
> Thoughts?
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, October 11, 2010 8:05 PM
> To: ids@iiug.org
> Subject: Re: RE: using ? in "in" [21631]
>
> Naw, it's simpler than that. The underlying file that an external table
> maps
> into the data doesn't actually have to exist, so, all you have to do is
> include a file or run identifier in the file's records and map the table to
> a
> wildcard representation of the filename(s). Then multiple jobs can map in
> their own files and in the IN clause sub-query select .... where jobid =
> myjobid. So the external table would be defined as something like:
>
> create external table value_lists (
>
> jobid int,
>
> value_id in
> )
> USING ( FORMAT DELIMITED DELIMITER ''|' ) DATAFILES (
> DISK:/value_files_dir/value_file.%r(1..99999));
>
> Now each job can create a file that has lines like:
> <jobnum>|value1|
> <jobnum>|value2|
> .....
>
> and name the file /value_files_dir/value_file.<jobnum>
>
> and the IN clause looks like:
>
> ..... IN (SELECT value_id FROM value_lists WHERE jobid = <jobnum>)...
>
> After the job completes just delete, move, or rename the job's values file
> and
> IDS won't know that it exists anymore. Existing jobs that have the file
> open
> will still see its data, but since the records in that file don't contain
> the
> other job's jobid they won't see the rows in there anyway.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.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, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. 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 Mon, Oct 11, 2010 at 7:44 PM, Wyza, Jonathon
> <wyzaj@bethelcollege.edu>wrote:
>
> > That might work, except it appears that external tables are not
> > capable of being temporary and must have unique system wide names.
> > Since this process has the capability of being run more than once at a
> > time then that could present problems. Beyond that I'd have to make
> > sure it was dropped after the process finished. Obviously i could use
> > a begin_work statement (and thus if the program exited then it would
> > rollback), but then it would log all the work, which i really don't
> > need it to do.
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu
> > ==============================
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Björgvin Oddsson
> >
> > ________________________________________
> > From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of John
> > Miller iii [miller3@us.ibm.com]
> > Sent: Monday, October 11, 2010 6:52 PM
> > To: ids@iiug.org
> > Subject: RE: RE: using