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.

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

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.

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".

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).

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.

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.)