Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: session id for temporary tables

    How can I get session id for temporary tables?

    I can find temporary tables and owners from query:

    select t.dbsname, t.tabname, t.owner
    from sysmaster:systabnames t, sysmaster:sysptnhdr p
    where t.partnum = p.partnum
    and bitval(p.flags,32) = 1

    But where is session id?

    Jacek

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    Please test with this from sysmaster:

    SELECT substr(n.dbsname,1,15) type,
    substr(n.owner,1,15) users,
    substr(n.tabname,1,18) table,
    substr(dbinfo('DBSPACE', i.ti_partnum),1,15) dbspace,
    round(i.ti_nptotal*2,0) kb
    FROM systabnames n, systabinfo i
    WHERE (bitval(i.ti_flags, 32) = 1
    OR bitval(i.ti_flags, 64) = 1
    OR bitval(i.ti_flags, 128) = 1)
    AND i.ti_partnum = n.partnum
    into temp ggtmp with no log;
    SELECT type[1,12], users, table, dbspace[1,12], kb
    from ggtmp
    order by 1,2,3,4

    Gustavo.

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    Your example query don't retrive sesion id for temporary tables

    Jacek

  4. #4
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92
    Hi,

    Session id can be retrieved as:
    select dbinfo('sessionid') from systables where tabid=1;

    Hence:
    select t.dbsname, t.tabname, t.owner, dbinfo('sessionid')
    from sysmaster:systabnames t, sysmaster:sysptnhdr p
    where t.partnum = p.partnum
    and bitval(p.flags,32) = 1

    Regards,
    Shriyan

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    It's not working correctly. This query retrieve my current sesion id.
    I need sesion id for another users who create temporary tables.
    I need list of sesion id for active users who have created temporary tables with special names.

    Jacek

  6. #6
    Join Date
    Oct 2004
    Posts
    4
    I explain what is my problem

    One company with about 300 customers use old and new version client/server application. Administrators don't know who use old and who use new version.
    They would like to block database connection for customers who use old software. Old software always create temporary table called "tmp_param".
    I would like to write server process which control who and when temp table "tmp_param" was created. If this table exist the process disconnect application (connected by session id) from database server.

    Jacek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •