Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: Create Table in Stored Procedure

    Hi,
    I'm new to DB2. I need to create a physical table in my stored procedure. I cant use the session.temp table. how can i do it in my stored procedure. And also how can I create the physical table into a different database? For example i have 2 database. database1 and database2. my Stored proc is in database1. in my stored proc i need to create a physical table into database2. how can i do it? is there a syntax in db2 like in mssql where you can select and insert it into the created table automatically? tnx. I'm using version 9.
    Last edited by aquiintac; 08-22-07 at 22:28.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    CREATE TABLE statements cannot be run directly from the procedure as static SQL. You need dynamic SQL, i.e. create the statement in a string and then run EXECUTE IMMEDIATE.

    The same applies to creating the base table in a different database. You have to set up federation to access the other database, then switch to PASSTHRU mode and create the table that way - all with dynamic SQL. (I'm not completely sure if you can have federated operations inside a procedure, though. It may depend on the programming language you use to implement the procedure.)

    Note that you cannot simply compare "databases" of one DBMS with another. Many DBMS have a different understanding what a "database" actually is. For example, DB2 LUW considers a database as a closed object with its own catalag, buffer pools, logs, etc.. Each database is physically separated from all other databases DB2 z/OS has multiple databases in a subsystem that share a catalog.

    Anyway, my questions would be:
    [list][*] Why do you have to create a base table inside the procedure? That may hint at a design problem of your application.[*] Are you sure that you really want to have different databases and not just different schemas?[/list
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    it was the requirement of the company. they wanted the created table from the stored proc be placed on other database. they want the default tables to be in the first dbase and the created table during the process in another dbase. by the way im using vb.net as my prog language.

    i have another question. how can i insert the values into table into different database. i already have the table in the different database. i need to access it. do i still need to use federation?

    tnx very much.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by aquiintac
    it was the requirement of the company. they wanted the created table from the stored proc be placed on other database. they want the default tables to be in the first dbase and the created table during the process in another dbase. by the way im using vb.net as my prog language.
    Do they really require a different database or a different schema? Maybe they come from Oracle and don't know that DB2 LUW has a different concept associated with the term "database".

    i have another question. how can i insert the values into table into different database. i already have the table in the different database. i need to access it. do i still need to use federation?
    Yes, you do. Everytime you want to do something in a different database through your current SQL session (connection), you need federation (or some other home-grown mechanism).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2007
    Posts
    3
    yes they require different database. the first database is their server database and the other database is the report database. they want the processed data from the server database during reports to be saved in report database.

    tnx.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You didn't give a compelling reason for two databases, but if your client wants to have it that way, you will have to use federation. (Or you try to sell them different schemas as being different databases.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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