Created raw table in Perl program but it vanished when I exit program
Jacob Salomon thought a raw table created via Perl DBI::Informix vanished when the program exited. Mike Walker and Paul Watson suspected a failed load or rollback. Jacob later reported there was no problem: the raw table had been created and filled successfully on the other server, and he withdrew the question.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Greetings y'all.
This is a big mystery for me and I hope there are enough users of the Perl DBI::Informix module to recognize this problem.
Consider this code in a Perl variable:
create raw table $wtab_name
as
select date(ins_dtime) ins_date, hex(rowid) row_address
from $target_tab
where year(today) - year(date(ins_dtime)) > 3 -- 3 years and change
;
The target and work tables are on another host/server but I'm running it from a host where the Informix modules are installed. The table names are in the variables and it works just fine - it takes about 8 minutes to create the work table. Note: It is NOT temp, so if I run this program again, it
should
find that work table already in place. And therein lies the problem:
While the table is building, I can go into dbaccess on the target host and watch the work table growing until is his 58+million rows. I can even select from it. [While still developing the code,] I exit the program shortly after creating the work table. And then
POOF!
The table is gone!
Does this sound familiar to anyone out there? it adds 8+ minutes to every debugging session and, I think, this should not be happening! Can the "raw" designation be a culprit here? I don't want to fill the logs with unneeded stuff. (As I write this it occurs to me to perhaps alter table to standard before exiting the program. I'll try it and let y'all know.) Page 2-63 of the 12.10 Administrator's guide:
The CREATE RAW TABLE statement is not supported on secondary servers within
a high-availability cluster.
Well, this is a dev box, not on a HA cluster but the database is remote from the host running the Perl program.
Is this normal behavior for a raw table?
Thanks for ideas here.
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
Is it possible that the target table load failed (maybe a space issue?) and so the table would not have been created because of the implicit transaction? Can you check the return status of the "create" SQL and confirm that it was successful, and/or do a count(*) of the new table before the perl program finishes to make sure that it is there once the create finished successfully? Do you have any begin/commit in there anywhere? ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
Smells like a rollback Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Paul gave the most plausible suggestion: > Smells like a rollback However, that was not the problem. In fact, I had no problem and I am totally -faced. (What happened to the egg-on-my-face emoji?) The operation was actually a total success! The raw table was successfully created and filled on another server@host . I would just shut up and hope everyone forgets about this but I have to express gratitude to those who offered suggestions. So now let's just forget it happened. ------------------------------ +-----------------------------------------------------------+ | I am pleased to report that I had no problems today. | | I had only issues, opportunities, challenges and valuable | | learning experiences. | +------------------------------------------ Jacob S --------+ ------------------------------