Hi Folks.
Let's do the bottom line question first; the rest is commentary.
Is there any way to create a temp table when connected to a database and have it still accessible when I switch databases?I am trying to massively clean up (actually rewrite) some Perl code that works but is very slow. The scheme is as follows:
1. From sysmaster (systabnames and sysptnhdr), get a bunch of metadata about the partitions in the server, pulling that into a
temp table. This includes partnum to use as a unique key column.
2. Foreach [user] database: (a) Connect to it with a separate handle; (b) Run a query that matches up systables.partnum with temp_table.partnum.
By the way, I create the temp table "with no log". In dbaccess, when I did this in database A and switched to database B mid-session, I was still able to access that temp table that I had created while in A. Here's is a paragraph from the Guide to SQL: Syntax 12.10, page 2-719 (page 765 in the PDF) to explain my thought process:
Because nonlogging temporary tables do not disappear when the database is
closed, you can use a nonlogging temporary table to transfer data from one
database to another while the application remains connected. The behavior of a
temporary table that you create with the WITH NO LOG option of the INTO
TEMP clause resembles that of a RAW table.
However, BARF on that scheme: The temp table is not known to the new database handle. Obviously, dbaccess is doing something I can't do in Perl: Using the same database handle to access multiple databases, one at a time. In Perl, when I connect to a database,I get a new handle, a new connection. (HMMmmmm Does this problem happen in 4GL? Moot, since I don't have 4GL installed.)
So, is there *ANY* way to create a temp table so that it is accessible when I switch databases? My new scheme is many pages of code shorter than the old way, if I could make it work. And letting the engine do the joins is SO MUCH FASTER than my Perl code looping to match data from two hashes.
Some would suggest: Stay in sysmaster and just JOIN my temp table to the catalogs of each database by explicitly qualifying the tables, like stores_demo:systables. Great idea, y'all. However, sysmaster is a logged database and, in general, I can't assume all user databases are logged. I bitterly recall discovering that silly restriction that a query cannot join tables across databases unless all have the same logging mode. My feature request to have this restriction lifted for dirty reads was ignored those many years ago.
I am also reluctant to create a work table, to be dropped before exiting, because if the program barfs, that table won't be dropped.
Ideas? Is Jonathan L on this list? Thanks much for ideas!
Whew! Jake is up to his old, long-winded ways!
------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra
------------------------------
#Informix