Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Arizona, USA
    Posts
    8

    Unanswered: Dynamic identification of Informix temp tables

    I would like to develop a process which can determine if there were temp tables created during my current database connection. I am the system architect for a very large application which establishes a database connection at user startup, and that connection persists while the user navigates from screen to screen. We have a problem with too many concurrent database connections and would like to reduce the number of active connections by disconnecting after a fixed period of inactivity. However, there are processes in this application which retain data in temp tables for use within the same connection by other screens or processes.

    If I can dynamically determine what temp tables exist, and what the column names and datatypes are, we can develop a process which would write these out to the file system and disconnect from the database. At a later point (on request by the user) the system would reestablish a database connection and use the file system to recreate the temp tables. My problem is determining what temp tables exist and the specific structure of those tables.

    We operate in a multi IBM AIX RS/6000 environment running Informix 7.31.UD5.

    Any ideas?

    Dan Dodge

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

    Re: Dynamic identification of Informix temp tables

    Originally posted by ddodgeaz
    I would like to develop a process which can determine if there were temp tables created during my current database connection. I am the system architect for a very large application which establishes a database connection at user startup, and that connection persists while the user navigates from screen to screen. We have a problem with too many concurrent database connections and would like to reduce the number of active connections by disconnecting after a fixed period of inactivity. However, there are processes in this application which retain data in temp tables for use within the same connection by other screens or processes.

    If I can dynamically determine what temp tables exist, and what the column names and datatypes are, we can develop a process which would write these out to the file system and disconnect from the database. At a later point (on request by the user) the system would reestablish a database connection and use the file system to recreate the temp tables. My problem is determining what temp tables exist and the specific structure of those tables.

    We operate in a multi IBM AIX RS/6000 environment running Informix 7.31.UD5.

    Any ideas?

    Dan Dodge
    Hello Dan,

    I have the script in Unix maybe serves to yuo, this is

    {
    dbaccess sysmaster 2>/dev/null << EOF
    SELECT trim(n.dbsname) type,
    trim(n.owner) users,
    trim(n.tabname) table,
    trim(dbinfo('DBSPACE', i.ti_partnum)) dbspace,
    round(i.ti_nptotal*4,0) kb -- 4 for AIX, 2 for HP-UX
    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
    EOF
    } |more

    Gustavo.

  3. #3
    Join Date
    Jan 2003
    Location
    Arizona, USA
    Posts
    8

    Re: Dynamic identification of Informix temp tables

    Originally posted by gurey
    Hello Dan,

    I have the script in Unix maybe serves to yuo, this is

    {
    dbaccess sysmaster 2>/dev/null << EOF
    SELECT trim(n.dbsname) type,
    trim(n.owner) users,
    trim(n.tabname) table,
    trim(dbinfo('DBSPACE', i.ti_partnum)) dbspace,
    round(i.ti_nptotal*4,0) kb -- 4 for AIX, 2 for HP-UX
    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
    EOF
    } |more

    Gustavo.

    THANK YOU Gustavo. I tried the query and it works great! This will satisfy my most important requirement.

    Do you know where I can find the documentation for the systabinfo table, expecially the bit flag values? I found the systabnames documented in Chapter 34 of the Administrator's Guide, but no documentation for the systabinfo table.

    Dan

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Dynamic identification of Informix temp tables

    Originally posted by ddodgeaz
    THANK YOU Gustavo. I tried the query and it works great! This will satisfy my most important requirement.

    Do you know where I can find the documentation for the systabinfo table, expecially the bit flag values? I found the systabnames documented in Chapter 34 of the Administrator's Guide, but no documentation for the systabinfo table.

    Dan
    Hi, DAN

    Congratulation !!!

    The information to all tables of sysmaster this in $INFORMIXDIR/etc/sysmaster.sql.

    a hug !!!

    Gustavo.

  5. #5
    Join Date
    Jan 2003
    Location
    Arizona, USA
    Posts
    8

    Re: Dynamic identification of Informix temp tables

    Gustavo,

    Thank you for your time and assistance. The information is very helpful.

    Dan Dodge
    Arizona, USA

  6. #6
    Join Date
    Jan 2009
    Posts
    1
    Run this Informix SQL to see temporary tables:

    SELECT partnum, dbsname, owner, tabname, dbinfo('UTC_TO_DATETIME',ti_created) created, ti_nrows
    from sysmaster:systabnames s,sysmaster:systabinfo i
    where i.ti_partnum = s.partnum
    and sysmaster:BITVAL(i.ti_flags,'0x0020') = 1
    and owner !="informix"
    order by created asc

Posting Permissions

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