onmode -z does not seem to actally terminate the s

Art/Jack/John/Reinhard/everyone else: OK so I did an insert into an external table (350mil rows) -- done beautifully, took 1hr18 min. I, however, did it to "PIPE" instead of "DISK". Using pipe allows me to compress the output file. So my "external table" is little different, I shouldn't treat it the same way as other tables, for example, do a select * from kernsextenaltab, but I did. I immediately broke it and kill the unix process then onmode -z on the session, onlinelog says "terminated" "killed" -- but it is still visible from onstat -g sql, it's been more than an hour, it doesn't go away... What can I do? Thanks for all the help in advance. ... tid name rstcb flags curstk status 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 2338136 V 76bdfc040 143360 12576 146 17 2338136*O0 V 728435040 4096 808 1 1 name free used name free used ...

Keep patient, until its Rollback work finish? On Tue, Oct 23, 2012 at 2:34 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Art/Jack/John/Reinhard/everyone else: > OK so I did an insert into an external table (350mil rows) -- done > beautifully, took 1hr18 min. I, however, did it to "PIPE" instead of > "DISK". > Using pipe allows me to compress the output file. So my "external table" is > little different, I shouldn't treat it the same way as other tables, for > example, do a select * from kernsextenaltab, but I did. I immediately > broke it > and kill the unix process then onmode -z on the session, onlinelog says > "terminated" "killed" -- but it is still visible from onstat -g sql, it's > been > more than an hour, it doesn't go away... > What can I do? > Thanks for all the help in advance. > > .... > tid name rstcb flags curstk status > 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- > Memory pools count 2 > name class addr totalsize freesize #allocfrag #freefrag > 2338136 V 76bdfc040 143360 12576 146 17 > 2338136*O0 V 728435040 4096 808 1 1 > name free used name free used > .... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f6464ebf5fa0804ccbe8780

Bounce the instance. Yes, if you define the external table file as a pipe for writing to a file piped through a compressor like gzip, to read it back you have to define another external table that is a pipe reading from gunzip to decompress the file's contents. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Tue, Oct 23, 2012 at 2:34 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Art/Jack/John/Reinhard/everyone else: > OK so I did an insert into an external table (350mil rows) -- done > beautifully, took 1hr18 min. I, however, did it to "PIPE" instead of > "DISK". > Using pipe allows me to compress the output file. So my "external table" is > little different, I shouldn't treat it the same way as other tables, for > example, do a select * from kernsextenaltab, but I did. I immediately > broke it > and kill the unix process then onmode -z on the session, onlinelog says > "terminated" "killed" -- but it is still visible from onstat -g sql, it's > been > more than an hour, it doesn't go away... > What can I do? > Thanks for all the help in advance. > > .... > tid name rstcb flags curstk status > 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- > Memory pools count 2 > name class addr totalsize freesize #allocfrag #freefrag > 2338136 V 76bdfc040 143360 12576 146 17 > 2338136*O0 V 728435040 4096 808 1 1 > name free used name free used > .... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042f9f2a37c34a04ccc098c9

Art, I've found out that, it doesn't matter and it doesn't hurt if we do select * on this external table (involved using pipe). The query will returnnothing versus records if it wasn't compressed -- that was my other test environment indicates. However, because I hit control break too soon before the query came back, that complicates the issue. Now that statment is hanging there sleeping forever, and placing an exclusive lock on the external table -- not that I care -- but I don't have to luxury to bounce, therefore, I'm looking for an alternative solution. (remember that there is no unix process, it's gone). If ignoring it and leaving it there until the next reboot, do you think it will hurt anything? will it hurt the backup? tid name rstcb flags curstk status 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- ________________________________ From: Art Kagel <art.kagel@gmail.com> To: ids@iiug.org Sent: Tuesday, October 23, 2012 4:24 PM Subject: Re: onmode -z does not seem to actally termina.... [28623] Bounce the instance. Yes, if you define the external table file as a pipe for writing to a file piped through a compressor like gzip, to read it back you have to define another external table that is a pipe reading from gunzip to decompress the file's contents. Art Art S. Kagel Advanced DataTools (http://www.advancedatatools.com/) Blog: http://informix-myview.blogspot.com/ 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 Tue, Oct 23, 2012 at 2:34 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Art/Jack/John/Reinhard/everyone else: > OK so I did an insert into an external table (350mil rows) -- done > beautifully, took 1hr18 min. I, however, did it to "PIPE" instead of > "DISK". > Using pipe allows me to compress the output file. So my "external table" is > little different, I shouldn't treat it the same way as other tables, for > example, do a select * from kernsextenaltab, but I did. I immediately > broke it > and kill the unix process then onmode -z on the session, onlinelog says > "terminated" "killed" -- but it is still visible from onstat -g sql, it's > been > more than an hour, it doesn't go away... > What can I do? > Thanks for all the help in advance. > > .... > tid name rstcb flags curstk status > 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- > Memory pools count 2 > name class addr totalsize freesize #allocfrag #freefrag > 2338136 V 76bdfc040 143360 12576 146 17 > 2338136*O0 V 728435040 4096 808 1 1 > name free used name free used > .... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042f9f2a37c34a04ccc098c9 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

It should not hurt, but running that by IBM is not a bad idea to double check. As far as archives, they ignore external tables except for the DDL to define them. The data they contain and the files they reference are not included in the archive. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Tue, Oct 23, 2012 at 5:56 PM, Kern Doe <kern_doe@yahoo.com> wrote: > Art, I've found out that, it doesn't matter and it doesn't hurt if we do > select * on this external table (involved using pipe). The query will> return > nothing versus records if it wasn't compressed -- that was my other test > environment indicates. However, because I hit control break too soon before > the query came back, that complicates the issue. Now that statment is > hanging > there sleeping forever, and placing an exclusive lock on the external > table -- > not that I care -- but I don't have to luxury to bounce, therefore, I'm > looking for an alternative solution. (remember that there is no unix > process, > it's gone). > If ignoring it and leaving it there until the next reboot, do you think it > will hurt anything? will it hurt the backup? > > tid name rstcb flags curstk status > 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- > > ________________________________ > From: Art Kagel <art.kagel@gmail.com> > To: ids@iiug.org > Sent: Tuesday, October 23, 2012 4:24 PM > Subject: Re: onmode -z does not seem to actally termina.... [28623] > > Bounce the instance. > > Yes, if you define the external table file as a pipe for writing to a file > piped through a compressor like gzip, to read it back you have to define > another external table that is a pipe reading from gunzip to decompress the > file's contents. > > Art > > Art S. Kagel > Advanced DataTools (http://www.advancedatatools.com/) > Blog: http://informix-myview.blogspot.com/ > > 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 Tue, Oct 23, 2012 at 2:34 PM, Kern Doe <kern_doe@yahoo.com> wrote: > > > Art/Jack/John/Reinhard/everyone else: > > OK so I did an insert into an external table (350mil rows) -- done > > beautifully, took 1hr18 min. I, however, did it to "PIPE" instead of > > "DISK". > > Using pipe allows me to compress the output file. So my "external table" > is > > little different, I shouldn't treat it the same way as other tables, for > > example, do a select * from kernsextenaltab, but I did. I immediately > > broke it > > and kill the unix process then onmode -z on the session, onlinelog says > > "terminated" "killed" -- but it is still visible from onstat -g sql, it's > > been > > more than an hour, it doesn't go away... > > What can I do? > > Thanks for all the help in advance. > > > > .... > > tid name rstcb flags curstk status > > 517464581 sqlexec 8b51e0d50 ---P--- 9695 sleeping forever- > > Memory pools count 2 > > name class addr totalsize freesize #allocfrag #freefrag > > 2338136 V 76bdfc040 143360 12576 146 17 > > 2338136*O0 V 728435040 4096 808 1 1 > > name free used name free used > > .... > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --f46d042f9f2a37c34a04ccc098c9 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d042dfee1a7172904ccc1202c