IDS Forum

Re: Sysmaster - Tabelas Temporária

Posted By: Marco Greco
Date: Monday, 20 January 2014, at 2:01 p.m.

In Response To: cannot initialize root dbspace (NOOR ANSARI AB RAZAK)

On 20/01/14 17:20, Fernando Nunes wrote:
> I'm not absolutely sure I got your requirement correctly... You say:
>
> "a program that runs once .... keep the session alive":
> You cannot keep a session alive without a program running.
>
> Then you say:
> "I need to drop temporary tables (if they exist)"
> Ok... Just DROP them and capture the error... of if you're using 11.70 do
> "DROP TABLE IF EXISTS..."
>
> What you're requesting (gathering the current temporary tables of you
> session) is not possible currently.
> There is already a feature request for it, created by Cesar Martins I
> believe, so please vote for it, if it suites your needs:
>
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36245
>
> We're still missing a feature for knowng who is using the temporary tables
> which should be linked to this one I believe.
> Regards
>
> On Mon, Jan 20, 2014 at 4:58 PM, André Luiz Rufino
> <andre_rufino@msn.com>wrote:
>
>> Hi folks,
>> I need to write a program that must run once, create some temporary tables
>> and
>> keep the session alive. The user must to be able to run again, without
>> close
>> the session. I need to drop temporary tables(if it deos exist) and recreate
>> then again.
>> Does anybody knows what sysmaster table can I access to find which
>> temporary
>> table belongs to my session.
>> In others words, where are the temporary tables joined with session id ?
>> Thanks a lot,
>> André Luiz Rufino
>>

Sessions do not stay alive without a client connected to the engine, but a
specific type of transaction can: I think the OP is looking for XA
transactions functionality - where you can create one, disconnect, the
transaction still survives, new session comes in and attaches to it and
continues where the other left off.
Eventually the transaction can be committed, rolled back, or even forgotten about.

It's important to note that XA is used for a completely different purpose,
which is manage heterogeneous transactions outside of the database engine, ie
where Informix is a participant of a distributed transaction and not the
coordinator (example - you have to move a record from a nondescript system to
an Informix engine: you have a client that connects to the first system, gets
the data, connect to Informix, inserts the data, deletes the data from the
first system and then instructs both Informix and the system to commit),
however, due to the nature of the XA interface, it might do for you the trick
of having a transaction survive across sessions.

What I am puzzled about is the need to drop temporary tables - temporary
tables are private to sessions and by definition, get dropped at the end of
the session (with XA things are slightly more complicated because you can
create a logged temporary table inside the XA transaction, if you really
really want, which will then survive - however this can be easily avoided, and
we'll consider it as not a problem for the purpose of this discussion).

Multiple sessions can have temporary tables by the same name, and when the new
session starts, it will have no knowledge of the temporary tables used by the
earlier session, and there will be no temporary tables that the new session
needs to handle in a special way.
Even if they didn't get dropped - CREATE TEMPORARY TABLE would return an error
if the table existed, which the application would then handle by dropping the
offending table, so I'm not sure what the problem is...

If XA transactions are of use to the OP, here's where to read all about them:

http://publib.boulder.ibm.com/epubs/pdf/7875a.pdf
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Messages In This Thread

• cannot initialize root dbspace
  NOOR ANSARI AB RAZAK -- Sunday, 11 July 2004, at 9:16 p.m.
  • Re: cannot initialize root dbspace
    JOHN FORSYTH -- Wednesday, 14 July 2004, at 4:10 p.m.
  • Re: Sysmaster - Tabelas Temporária
    Marco Greco -- Monday, 20 January 2014, at 2:01 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.