Informix

 View Only
Expand all | Collapse all

Keep access to a temp table when switching databases mid session

  • 1.  Keep access to a temp table when switching databases mid session

    Posted Wed May 13, 2020 07:20 PM
    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


  • 2.  RE: Keep access to a temp table when switching databases mid session

    IBM Champion
    Posted Wed May 13, 2020 07:32 PM
    Jacob:

    I have a few of suggestions. I was going to suggest creating the temp table in the target database by querying sysmaster remotely, but, as you pointed out, that will not work for a non-logged database. So here are the three I have left for you:
    1. Use an EXTERNAL TABLE instead. You can create it in sysmaster, populate the file, drop the table there and recreate it on the same file in the target database.
    2. Use two separate connections and perform the join in code.
    3. Just suck in the data from sysmaster into a Perl array then switch databases and process the target database's tables against that in-memory data.
    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.






    Original Message


  • 3.  RE: Keep access to a temp table when switching databases mid session

    Posted Wed May 13, 2020 08:12 PM
    Hi Art.

    Here are your stunningly great ideas back with my commentary:
    1. Use an EXTERNAL TABLE instead. You can create it in sysmaster, populate the file, drop the table there and recreate it on the same file in the target database.
      This had occurred to me but it seems no more efficient than if I unload the temp table once, before the loop, and then load it into a new temp table for each database I loop through.
    2. Use two separate connections and perform the join in code.
      I am not following how that syntax would work.  But this sounds best, if I can figure it out.
    3. Just suck in the data from sysmaster into a Perl array then switch databases and process the target database's tables against that in-memory data.
      That's my old algorithm: Preserving the sysmaster connection but creating and closing a new connection for each user database. And like I said, it's slow.  For now, as a much better Perl programmer than 4 years ago, I am using hashes, keyed by partnum, so my matchups should be much faster than before.  I may go this way, if I can't see how to use suggestion 2.
    Much thanks!

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 4.  RE: Keep access to a temp table when switching databases mid session

    IBM Champion
    Posted Thu May 14, 2020 07:46 AM
    Jacob:

    The external table will involve two sets of IO (write the file, read the file) while using a temp table, unload table to file, create temp table, read file into temp table, process temp table will involve four sets of IOs.

    My #2 is how you described you attempt at #3. That is: connect to sysmaster and keep that connection open WITH CONCURRENT TRANSACTION then also connect to each database you have to process one at a time and look up whatever you need to from the sysmaster connection.

    My version of #3 is to read all of the data you might need from sysmaster into hashes and other data structures in Perl then connect to the various databases and process the info there against the memory structures. Connection to sysmaster is no longer needed.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.






    Original Message


  • 5.  RE: Keep access to a temp table when switching databases mid session

    Posted Thu May 14, 2020 07:05 PM
    Hi Art.

    > connect to sysmaster and keep that connection open WITH CONCURRENT TRANSACTION
    I'd love to try that, but I see no way to specify that from within DBD::Informix.  I did just check out, using the Perl debugger, a related variable:
      DB<4> p $frags_h->{ix_MultipleConnections}
    1

    That said, I also see from the doc that the DBI->connect() call *does* specify WITH CONCURRENT TRANSACTIONS  without being asked.
    Still, it is not enough to help me accomplish my aim.

    +----- Jacob Salomon -- jakesalomon@yahoo.com -------------------------+
    | The first lesson of economics is scarcity: there is never enough of  |
    | anything to fully satisfy all those who want it. The first lesson of |
    | politics is to disregard the first lesson of economics.              |
    +------------------------------ Thomas Sowell (Is Reality Optional?) --+





    Original Message


  • 6.  RE: Keep access to a temp table when switching databases mid session

    Posted Thu May 14, 2020 07:49 AM

    Hi Salomon.

    There is an exclusion to the rule that prevents accessing a database with a different logging state: any session can access objects in sysmaster regardless of which database you are in. You could do this once as user "informix" (with or without "RAW"):

    DATABASE sysmaster; -- cannot create table in another database
    CREATE RAW TABLE temp_table …
    GRANT ALL ON temp_table TO PUBLIC;

    Each time you start your program as any user:

    DELETE FROM temp_table; -- cannot TRUNCATE table in another database

    For each database you need to process:

    INSERT INTO sysmaster:temp_table ...
    SELECT * FROM sysmaster:temp_table ...



    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------

    Original Message


  • 7.  RE: Keep access to a temp table when switching databases mid session

    Posted Thu May 14, 2020 04:06 PM
    Doug said:
    For each database you need to process:
    INSERT INTO sysmaster:temp_table ...
    SELECT * FROM sysmaster:temp_table ...

    Doug, I think there's a typo in your example.  I think you meant to say:

    INSERT INTO some_user_database:temp_table ...
    SELECT * FROM sysmaster:temp_table ...

    Your syntax alerted me to a possible problem in my code - I had not explicitly qualified the temp table so I fixed that and tried what I thin you meant. Here's a code snippet - the SQL that joins systables in a local database with the sysmaster:temp table I created in previous code.  (Shortened a bit, of course.)

    select st.partnum,
           tm.dbsname,
           tm.owner,
           tm.tabname,
           tm.full_name,
           tm.partn_type,
           tm.dbspace,
            ....,
           st.tabid
      from systables,
           sysmaster:fragments_19351 tm
     where st.tabtype = 'T'
       and tm.partnum = st.partnum
     order by tm.dbsname, tb.tabname

    (I hope the indentation is maintained when I post this.) fragments_19351 is the name of the temp table from the sysmaster query, generated in a previous step.

    In any case, when I prepare that query, I get this error message:
    SQL: -206: The specified table (sysmaster:fragments_19655) is not in the database.
    ISAM: -111: ISAM error: no record found.

    Of course, the database handle (dbh) for this connection to the local database is different from the dbh I used for the sysmaster query that generated the temp table.  But the sysmaster dbh must still be open or the temp table would have vanished.  Still, as you can see, it does not find the sysmaster temp table. Logging modes have nothing to do with problem.

    Another review of the doc on DBD::Informix found no way to use the same dbh while I switch databases. If I use the same variable it will just put the new handle in there.  One section of the DBD::Informix doc may or may not pertain to my situation. But here is that snippet:
    Multiple calls to $drh->connect will generally switch databases successfully but will invalidate any statements you previously prepared.
    I would presume this applies to temp table previously created.

    WOW! This thread is getting to be real fun!  But if I don't find the elegant solution I was looking for - a "session-global" temp table - I may just go for that hash method I am reluctant to follow. :-(

    Keep smiling!

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 8.  RE: Keep access to a temp table when switching databases mid session

    Posted Fri May 15, 2020 04:36 AM
    Hi Salomon.

    My naming wasn't too clear. I mean that you could create permanent raw table sysmaster:fragments_19351 which would be accessible from any session and database.

    Regards,
    Doug


    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------

    Original Message


  • 9.  RE: Keep access to a temp table when switching databases mid session

    Posted Fri May 15, 2020 01:05 PM
    Hi Doug & rest of the family.

    I had mentioned this notion in my first post in this thread:
    > 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.
    So yes, I had considered this idea before I initially posted.  (And yes, there is a big drawback to long-winded posts. :-)

    Another issue is that I want this utility to be usable by anyone, not just Informix (or others listed in that ONCONFIG parameter whose name escapes me). I tried this in sysmaster as user jake and, of course, I have "No Resource Permission" on sysmaster.

    HMMmmm... This gives me an idea for a feature request: A user_temp database, similar to /tmp in Linux: Public has resource permission in there and it will be automatically purged of all user tables whenever the engine is started up.  That would partially solve my initial objection and certainly solve resource permission issue.  Doesn't help us for now, however.

    Thanks for the clarification, Doug.

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 10.  RE: Keep access to a temp table when switching databases mid session

    Posted Thu May 21, 2020 12:05 AM
    Art, you posted the idea of using an external table last week and, after trying to out-stare the cat confronting me, I finally took a stab at it.

    Bottom line: Since the utility I'm writing must be able to run for anyone (not just ADMIN_MODE_USERS) we encounter a problem: The non-admin cannot create an external (or any) table in sysmaster because No resource permission.

    What follows below is a running stream-of-consciousness of how I tried the syntax and my reasoning but this bottom line seems insurmountable.  Back to the hash, unless Jonathan (of DBD::Informix fame) or Tim (author of DBI) come to the rescue.
    -------------
    This is a segue from the thread.  Even my notorious tenacity, trying to access a temp table in sysmaster when I've switched to a user database, has it limits.

    Last week Art suggested I run my first sysmaster query but to select into an external table rather than a temp table.  I am trying to plough my way through the manual pages on this but it's hard to follow.  Here's how I think the syntax should look:

    In database sysmaster:
    select <all my stuff> from <sysmaster tables> where <whatever conditions>
      into external ext_tab
     using (datafiles ("disk:/tmp/ext_tab.dat"),
             format "informix"
            );

    BTW I used format "informix" because the Guide to SQL(Syntax) claims this internal format is faster to unload & load.

    Problem: I want a non-privileged user to be able to run my utility.  However, it requires resource permission in sysmsaster to create this external table in sysmaster.  I just tested that hypothesis in dbaccess and failed with No resource permission. So the whole scheme goes down the volcano in a puff of vapor.

    But let's plod on anyway for a bit, vapor be damned.  Now I'm trying to see if the logic is sound.

    I also ran [a simplified version of] the above query as user informix so I could create the external table.  And then dropped the table. The data file is still hanging around.  This means that even if I were running this as informix, I must remember to
    • Drop the external table from every database wherein it was used
    • Delete the data file before I exit the program
    And should the program barf before getting to that point, my databases, including sysmaster have useless external tables hanging around as well as a useless data file.  Hence my preference for the temp table method, which requires no resource privilege and I don't need to remember to clean up after myself.

    I'm giving myself a big headache!

    I had started this reply with some tests of how to use that data file in each user database.  But now that I see it is not a feasible solution for my situation I'm stopping here.  (Aren't y'all glad!)

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 11.  RE: Keep access to a temp table when switching databases mid session

    IBM Champion
    Posted Thu May 21, 2020 08:53 AM
    Jacob:

    Create the external table in one of the local databases, then populate it as a remote table. Like this:


    > database art;

    Database closed.


    Database selected.

    > create external table etest sameas systables using ( datafiles( "disk:/tmp/etest.unl" ) );

    Table created.

    > database sysmaster;

    Database closed.


    Database selected.

    > insert into art:etest select * from systables;

    268 row(s) inserted.



    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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.






    Original Message


  • 12.  RE: Keep access to a temp table when switching databases mid session

    Posted Fri May 15, 2020 03:34 PM
    You can use $dbh = DBI::connect('dbi:Informix:.DEFAULT.', …) to create your database handle.

    You can then use $dbh->execute("DATABASE sysmaster"); to access your first database (sysmaster) and create your temporary table, and then use $dbh->execute('DATABASE sales_demo'); to access your second database (sales_demo), and your temporary table will still be accessible (assuming the two databases are hosted on the same Informix server instance; if you switch instances, I'd assume the temporary table is lost, but I haven't formally verified that).

    This information is hidden away in 'perldoc DBD::Informix' - search for '.DEFAULT.'.  You do have to know what to look for (which is often the major stumbling block), or read the whole thing (onerous).

    -- 

    Jonathan Leffler (jonathan.leffler@gmail.com) #include <disclaimer.h>
    Guardian of DBD::Informix v2018.1031 -- http://dbi.perl.org/



    ------------------------------
    Jonathan Leffler
    ------------------------------

    Original Message


  • 13.  RE: Keep access to a temp table when switching databases mid session

    Posted Fri May 15, 2020 05:04 PM
    YEEEOOOOWWWW!  (At the risk of premature celebration :-)

    I'm out of time today - Talmud class in 1 minute.  I guess I'll give it a shot come Sunday or Monday.  The group will hear about it if it works.  You will hear about if it does not.  ;-)   It looks like you have not had the chance for any updates since Oct 2018.

    +----- Jacob Salomon -- jakesalomon@yahoo.com -------------------------+
    | The first lesson of economics is scarcity: there is never enough of  |
    | anything to fully satisfy all those who want it. The first lesson of |
    | politics is to disregard the first lesson of economics.              |
    +------------------------------ Thomas Sowell (Is Reality Optional?) --+





    Original Message


  • 14.  RE: Keep access to a temp table when switching databases mid session

    Posted Tue May 19, 2020 07:20 PM
    Minor Whoops - I thought I was replying privately. In any case:
    Yes, my celebration was a bit premature; perhaps I'm missing something.  I've tried both of these code sequences:
    my $server_dbh = DBI::connect('dbi:Informix:.DEFAULT.') or die ...;
    as well as:
    my $default_connection = sprintf("%s@%s", 'dbi:Informix:.DEFAULT.', $server);  # Without a server name
    # (Note: $server == dbi:Informix:.DEFAULT.@js_server​)         # With the server name
    my $server_dbh = DBI::connect($default_connection) or die ...
    In both cases I die with this error message:
    Can't connect to data source '' because I can't work out what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix and the DBI_DRIVER env var is not set) at /home/jake/scripts/Fragments.pm line 252.
    at /usr/lib/x86_64-linux-gnu/perl5/5.22/DBI.pm line 627.
    DBI::connect("dbi:Informix:.DEFAULT.\@js_server") called at /home/jake/scripts/Fragments.pm line 252
    Fragments::connect_server("database", "sysmaster") called at ./partitions.pl line 18

    It died in DBI.pm, not back in my code, so it emits Tim's error message.  Whaddaymean, data source '' ?  "dbi:Informix:.DEFAULT.@js_server" looks like a perfectly good data source to me!  The code in DBI.pm that barfs looks like this:

    # Set $driver. Old style driver, if specified, overrides new dsn style.
    $driver = $old_driver || $1 || $ENV{DBI_DRIVER}
    or Carp::croak("Can't connect to data source '$dsn' "
    ."because I can't work out what driver to use "
    ."(it doesn't seem to contain a 'dbi:driver:' prefix "
    ."and the DBI_DRIVER env var is not set)");


    What am I missing?  The DBI_DRIVER environment variable?  Like the .DEFAULT. connection, dat's a new one on me.  Besides, I *am* telling it, right in the call, which driver to use.  I think...  Let's see what the POD of DBI.PM has to say about it:

    DBI_DRIVER

    The DBI_DRIVER environment variable is used to fill in the database driver name in DBI->connect if the data source string starts "dbi::" (thereby omitting the driver). If DBI_DSN omits the driver name, DBI_DRIVER can fill the gap.

    I suppose this means I need to set that env variable before I try call DBI::connect

    I'll try it but I don't see why it should be necessary and hence, little confidence.  But worth a try.

    From the gleanings of Lazarus Long (A Robert Heinlein character): "Never try to out-stare a cat"​.  But I'm trying to out-stubborn a program!

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 15.  RE: Keep access to a temp table when switching databases mid session

    Posted Tue May 19, 2020 07:36 PM
    Well, I got a few lines further in DBI.PM - Up to line 664.  My code now sets the env explicitly:
    $ENV{DBI_DRIVER} = "Informix"
    and when I call the connect (same code as above) I got this message:
    Can't locate object method "install_driver" via package "dbi:Informix:.DEFAULT.@js_server"
     (perhaps you forgot to load "dbi:Informix:.DEFAULT.@js_server"?)
     at /usr/lib/x86_64-linux-gnu/perl5/5.22/DBI.pm line 664.
    at /usr/lib/x86_64-linux-gnu/perl5/5.22/DBI.pm line 664.

    There is something wrong with the parameter setup, although I initially copy/pasted from Jonathan's code sample.  DBI::connect() is mistaking my connection string *AKA data source) for a package name.  The POD for DBI.pm has this call setup:
    $dbh = DBI->connect($data_source, $username, $auth, \%attr);

    I had understood that I need only that $data_source parameter.  Again, what's missing from this picture?

    Getting interesting yet?

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message