You can translate the question and the replies:

Base view on a DB2 LUW Global Temp Table

I am trying to grab Denodo logged in user name and then inserting it into DB2 LUW "Declared Global Temp Table" for one of the application running on DB2 LUW. It seems it is only possible if the base view is created as regular view (Not created by query). But I could not see my DB2 LUW global temp table in list of tables under "DB2" schema using JDBC connection. I have following questions. 1. Is it possible to bring a DB2 LUW "Declared Global Temp Table" as baseview? 2. If yes, how can I insert a record into this DB2 LUW "Declared Global Temp Table".
Global Temp Table DB2 LUW
user
17-09-2019 12:57:48 -0400
code

5 Answers

Hi, As mentioned in the offical DB2 [documentation](https://www.ibm.com/support/knowledgecenter/en/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0003272.html), these types of (GLOBAL TEMPORARY TABLE) tables are defined for the current session and cannot be shared with other sessions. i.e, > DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session. > > The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped. Hence, you will be able to introspect normal tables and create views as required apart from 'GLOBAL TEMPORARY TABLE' type of tables. Hope this helps!
Denodo Team
19-09-2019 05:04:39 -0400
code
Thank you for looking at my request. Now we are switching our design to use a "DB2 View" instead of "Declared Global Temp Table". I was able to create base view on top of the DB2 view but I ran into my 2nd questions on "how to insert a row into base view" while executing it. This base view has one field which is "USER_ID". My requirement is to execute following INSERT statement everytime that base view is executed. For ex: INSERT INTO bv_logged_user (user_id) VALUES getsession('user')
user
19-09-2019 16:38:10 -0400
Hi, In order to insert values to a view everytime when a base view is executed, I would create a stored procedure with INSERT statement over the DB2 database and then in Denodo, I would import this stored procedure from DB2 data source and create a base view using the [“Create from query”](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries) option. By doing this, the input parameters will be utilized in Denodo as runtime parameters such that each time you execute this base view you were able to insert the data into the SQL database. You could refer to the section [Importing Stored Procedures from IBM DB2](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#importing-stored-procedures-from-ibm-db2) of Virtual DataPort Administration Guide and Knowledge Base article [Using the Create Base View From Query Option](https://community.denodo.com/kb/view/document/Using%20the%20Create%20Base%20View%20From%20Query%20Option?category=Data+Sources) for more information. Hope this helps!
Denodo Team
27-09-2019 09:01:31 -0400
code
Thank you for the guidence. Following these steps I was able to Insert into DB2 View. Now I have final step to perform, which I am not sure if possible in Denodo. I have created a derived view with this base view and another view (simple select from same DB where other base view was) but I need to execute base view (that was inserting userid using Stored Procedure) first before starting the execution on other base view (simple select). for example: bv_on_stored_procedure need to run first before running bv_simple_selct when both baseviews are in one single derived view.
user
27-09-2019 10:03:29 -0400
Hi, In order for the bv_on_stored_procedure to be executed first, I would make sure that the bv_on_stored_procedure is on the left side and bv_simple_select view is on right side of the 'Model' tab of any derived view. This way bv_on_stored_procedure will be considered as the first view and will be executed first. Hope this helps!
Denodo Team
09-10-2019 08:42:52 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here