DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: are there serversided, memorybased table-objects?

  1. #1
    michaelt Guest

    are there serversided, memorybased table-objects?


    hello everybody!

    currently i'm migrating a database from oracle to db2 v7.2 including migration
    of the client-programm, written in visual basic 5 and using ado...

    does anybody know if there is the possibility to use or to create (in stored
    procedures written in c) table-objects, which are residing in the servers
    memory and not in the temp-tablespace, like ie.:

    EXEC SQL DECLARE GLOBAL TEMPORARY TABLE SESSION.ltReturnTable
    (...) NOT LOGGED IN tempspace;

    [code continues like this:
    EXEC SQL FETCH lcFullset INTO :hostvar1, ..., :hostvarn;
    ... /* doing some calculations etc. */
    EXEC SQL INSERT INTO SESSION.ltReturnTable (...)
    VALUES (:hostvar1, ..., :hostvarn);
    EXEC SQL DECLARE lcReturn CURSOR FOR
    SELECT ... FROM SESSION.ltReturnTable;
    EXEC SQL OPEN lcReturn;
    /* end program store proc */]

    in other words:
    the returned adodb-recordset will have to contain a table which content is
    not only based on some simple selects. in fact - on the serverside - there
    are selects, cursors and some complex processing. based on this processing,
    rows of a certain table are selected and should be returned via an open cursor.
    i would dislike to use a serverbased temporary table, which is residing in
    the temp-tablespace, not only because of performance costs, but also because
    this table will be very large (over 1 gig), so please forget about adjusting
    bufferpools, upgrading ram capacity on the server etc.
    i would prefer to use an array-struct in my stored-procedure, but haven't
    found any information in the manuals how to open a cursor on this (so this
    is probably impossible)...could there be any other mechanism?
    this here is a general problem for me - above is just an example of code...any
    ideas?

    thanks in advance & regards,

    michael.

    p.s. i know ie. that sql-server has got that feature...

  2. #2
    Greg Nash Guest

    Re: are there serversided, memorybased table-objects?

    Hi,

    I don't believe DB2 will allow you to create a table purely in RAM,
    though it can be tuned to hold an entire table in memory for search
    purposes.

    Yes, being purely in memory would give better performance than being
    cached from disk, but how much depends on the workload. If the table is
    mostly queried and sometimes updated, you'd be no worse off having it
    cached in a buffer pool, though I'd give the table its own DMS
    tablespace and bufferpool.

    If the table has much insert/update/delete activity, it will generate
    disk activity, though again you can tune this, use separate disks for
    the log files, etc.

    As for RAM upgrade, if you have enough memory to hold the table in
    memory, then you have enough memory to cache a disk-stored table in a
    buffer pool. If you're on Windows NT / 2000, you might need to look at
    how to effectively access large amounts of RAM.

    hth,
    --Greg

    michaelt wrote:

    > hello everybody!
    >
    > currently i'm migrating a database from oracle to db2 v7.2 including migration
    > of the client-programm, written in visual basic 5 and using ado...
    >
    > does anybody know if there is the possibility to use or to create (in stored
    > procedures written in c) table-objects, which are residing in the servers
    > memory and not in the temp-tablespace, like ie.:
    >
    > EXEC SQL DECLARE GLOBAL TEMPORARY TABLE SESSION.ltReturnTable
    > (...) NOT LOGGED IN tempspace;
    >
    > [code continues like this:
    > EXEC SQL FETCH lcFullset INTO :hostvar1, ..., :hostvarn;
    > .. /* doing some calculations etc. */
    > EXEC SQL INSERT INTO SESSION.ltReturnTable (...)
    > VALUES (:hostvar1, ..., :hostvarn);
    > EXEC SQL DECLARE lcReturn CURSOR FOR
    > SELECT ... FROM SESSION.ltReturnTable;
    > EXEC SQL OPEN lcReturn;
    > /* end program store proc */]
    >
    > in other words:
    > the returned adodb-recordset will have to contain a table which content is
    > not only based on some simple selects. in fact - on the serverside - there
    > are selects, cursors and some complex processing. based on this processing,
    > rows of a certain table are selected and should be returned via an open cursor.
    > i would dislike to use a serverbased temporary table, which is residing in
    > the temp-tablespace, not only because of performance costs, but also because
    > this table will be very large (over 1 gig), so please forget about adjusting
    > bufferpools, upgrading ram capacity on the server etc.
    > i would prefer to use an array-struct in my stored-procedure, but haven't
    > found any information in the manuals how to open a cursor on this (so this
    > is probably impossible)...could there be any other mechanism?
    > this here is a general problem for me - above is just an example of code...any
    > ideas?
    >
    > thanks in advance & regards,
    >
    > michael.
    >
    > p.s. i know ie. that sql-server has got that feature...
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center