f



Error while declaring a global temporary table

Hi,

I have very little knowledge about creating Procedures/functions in
DB2.

When i tried to create the test function like

CREATE FUNCTION GET_TEST
 (P_TEST_ID INTEGER,
  P_SEL_OR_SORT INTEGER,
  P_TEST VARCHAR(2)
 )
  RETURNS VARCHAR(1000)
  SPECIFIC GET_RULE
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  STATIC DISPATCH
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  BEGIN ATOMIC
    DECLARE v_TEST VARCHAR(4000);
    DECLARE v_TEST_Select VARCHAR(4000);
    DECLARE v_TEST_Sort VARCHAR(1000);
	DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
	(id INTEGER
	 )
	 ON COMMIT PRESERVE ROWS;

    SET v_TEST_Select = NULL;
    SET v_TEST_Sort = NULL;
    RETURN(v_TEST);
  END;


It give an error SQL 0104N "An unexpected token "Table" was found
following "..are Global Temporary".

What is the problem in that?

0
Rahul
8/13/2007 2:12:18 PM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

5 Replies
738 Views

Similar Articles

[PageSpeed] 6

Rahul B wrote:
> Hi,
> 
> I have very little knowledge about creating Procedures/functions in
> DB2.
> 
> When i tried to create the test function like
> 
> CREATE FUNCTION GET_TEST
>  (P_TEST_ID INTEGER,
>   P_SEL_OR_SORT INTEGER,
>   P_TEST VARCHAR(2)
>  )
>   RETURNS VARCHAR(1000)
>   SPECIFIC GET_RULE
>   LANGUAGE SQL
>   NOT DETERMINISTIC
>   READS SQL DATA
>   STATIC DISPATCH
>   CALLED ON NULL INPUT
>   EXTERNAL ACTION
>   INHERIT SPECIAL REGISTERS
>   BEGIN ATOMIC
>     DECLARE v_TEST VARCHAR(4000);
>     DECLARE v_TEST_Select VARCHAR(4000);
>     DECLARE v_TEST_Sort VARCHAR(1000);
> 	DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
> 	(id INTEGER
> 	 )
> 	 ON COMMIT PRESERVE ROWS;
> 
>     SET v_TEST_Select = NULL;
>     SET v_TEST_Sort = NULL;
>     RETURN(v_TEST);
>   END;
> 
> 
> It give an error SQL 0104N "An unexpected token "Table" was found
> following "..are Global Temporary".
> 
> What is the problem in that?
> 
Rahul,

In DB2 for LUW SQL Functions are macros. As such they cannot do DDL, 
open cursors, etc.
If you have complex SQL PL inside of a function it is advisable to write 
a stored procedure and then CALL that procedure from the UDF.
Either way DDL in a function? That's a really bad idea. It would be slow 
and eat a lot of CPU.
typically temp tables are declared much higher up. E.g. right after the 
connection. Constant DECLARE and DROP of a temp table causes 
recompilation of the statements using them which will eat at your CPU.

For a list of the legal statements in an SQL Function is here:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0004240.htm

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
0
Serge
8/13/2007 2:44:58 PM
On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Rahul B wrote:
> > Hi,
>
> > I have very little knowledge about creating Procedures/functions in
> > DB2.
>
> > When i tried to create the test function like
>
> > CREATE FUNCTION GET_TEST
> >  (P_TEST_ID INTEGER,
> >   P_SEL_OR_SORT INTEGER,
> >   P_TEST VARCHAR(2)
> >  )
> >   RETURNS VARCHAR(1000)
> >   SPECIFIC GET_RULE
> >   LANGUAGE SQL
> >   NOT DETERMINISTIC
> >   READS SQL DATA
> >   STATIC DISPATCH
> >   CALLED ON NULL INPUT
> >   EXTERNAL ACTION
> >   INHERIT SPECIAL REGISTERS
> >   BEGIN ATOMIC
> >     DECLARE v_TEST VARCHAR(4000);
> >     DECLARE v_TEST_Select VARCHAR(4000);
> >     DECLARE v_TEST_Sort VARCHAR(1000);
> >    DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
> >    (id INTEGER
> >     )
> >     ON COMMIT PRESERVE ROWS;
>
> >     SET v_TEST_Select = NULL;
> >     SET v_TEST_Sort = NULL;
> >     RETURN(v_TEST);
> >   END;
>
> > It give an error SQL 0104N "An unexpected token "Table" was found
> > following "..are Global Temporary".
>
> > What is the problem in that?
>
> Rahul,
>
> In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
> open cursors, etc.
> If you have complex SQL PL inside of a function it is advisable to write
> a stored procedure and then CALL that procedure from the UDF.
> Either way DDL in a function? That's a really bad idea. It would be slow
> and eat a lot of CPU.
> typically temp tables are declared much higher up. E.g. right after the
> connection. Constant DECLARE and DROP of a temp table causes
> recompilation of the statements using them which will eat at your CPU.
>
> For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab- Hide quoted text -
>
> - Show quoted text -

Serge,

I am trying to create the temporary table inside the procedure because
i have to use an array of varchars (the code written is only a portion
of the function which i was trying to convert from Oracle to DB2, so i
started with the smallest and basic chunk first), and you have said in
a thread in this group that we have to use a temporary table since DB2
doesn't support arrays.

I will try to write a proc and call it from the function, but still
why should the error come("Unexpected toke Table.....")...
Leaving performance issue, is there a synactical error?

Thanks a lot,

Rahul

0
Rahul
8/13/2007 4:08:56 PM
Rahul B wrote:
> On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
>> Rahul B wrote:
>>> Hi,
>>> I have very little knowledge about creating Procedures/functions in
>>> DB2.
>>> When i tried to create the test function like
>>> CREATE FUNCTION GET_TEST
>>>  (P_TEST_ID INTEGER,
>>>   P_SEL_OR_SORT INTEGER,
>>>   P_TEST VARCHAR(2)
>>>  )
>>>   RETURNS VARCHAR(1000)
>>>   SPECIFIC GET_RULE
>>>   LANGUAGE SQL
>>>   NOT DETERMINISTIC
>>>   READS SQL DATA
>>>   STATIC DISPATCH
>>>   CALLED ON NULL INPUT
>>>   EXTERNAL ACTION
>>>   INHERIT SPECIAL REGISTERS
>>>   BEGIN ATOMIC
>>>     DECLARE v_TEST VARCHAR(4000);
>>>     DECLARE v_TEST_Select VARCHAR(4000);
>>>     DECLARE v_TEST_Sort VARCHAR(1000);
>>>    DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
>>>    (id INTEGER
>>>     )
>>>     ON COMMIT PRESERVE ROWS;
>>>     SET v_TEST_Select = NULL;
>>>     SET v_TEST_Sort = NULL;
>>>     RETURN(v_TEST);
>>>   END;
>>> It give an error SQL 0104N "An unexpected token "Table" was found
>>> following "..are Global Temporary".
>>> What is the problem in that?
>> Rahul,
>>
>> In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
>> open cursors, etc.
>> If you have complex SQL PL inside of a function it is advisable to write
>> a stored procedure and then CALL that procedure from the UDF.
>> Either way DDL in a function? That's a really bad idea. It would be slow
>> and eat a lot of CPU.
>> typically temp tables are declared much higher up. E.g. right after the
>> connection. Constant DECLARE and DROP of a temp table causes
>> recompilation of the statements using them which will eat at your CPU.
>>
>> For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
>>
>> Cheers
>> Serge
>> --
>> Serge Rielau
>> DB2 Solutions Development
>> IBM Toronto Lab- Hide quoted text -
>>
>> - Show quoted text -
> 
> Serge,
> 
> I am trying to create the temporary table inside the procedure because
> i have to use an array of varchars (the code written is only a portion
> of the function which i was trying to convert from Oracle to DB2, so i
> started with the smallest and basic chunk first), and you have said in
> a thread in this group that we have to use a temporary table since DB2
> doesn't support arrays.
> 
> I will try to write a proc and call it from the function, but still
> why should the error come("Unexpected toke Table.....")...
> Leaving performance issue, is there a synactical error?
Yes it is a syntax error. If you check out teh link I posted to:
Compound Statement (dynamic) you will find that DECLARE GLOBAL TEMPORARY 
TABLE is not in the syntax diagram, hence a -104.
Using a temp table to pass around ARRAYS between PROCEDURES using tenmp 
tables has no relevance on DECLARING temps in FUNCTIONs.
DB2 (unlike Oracle) clearly distinguishes between functions and procedures.
Functions extend SQL (either expressions or relational operations)
Procedures encapsulate procedural logic.
In general do not DECLARE TEMP tables in the procedures that use them. 
Declare them at the beginning of your connections.

Cheers
Serge
-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
0
Serge
8/14/2007 3:15:34 AM
On Aug 14, 8:15 am, Serge Rielau <srie...@ca.ibm.com> wrote:
> Rahul B wrote:
> > On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> >> Rahul B wrote:
> >>> Hi,
> >>> I have very little knowledge about creating Procedures/functions in
> >>> DB2.
> >>> When i tried to create the test function like
> >>> CREATE FUNCTION GET_TEST
> >>>  (P_TEST_ID INTEGER,
> >>>   P_SEL_OR_SORT INTEGER,
> >>>   P_TEST VARCHAR(2)
> >>>  )
> >>>   RETURNS VARCHAR(1000)
> >>>   SPECIFIC GET_RULE
> >>>   LANGUAGE SQL
> >>>   NOT DETERMINISTIC
> >>>   READS SQL DATA
> >>>   STATIC DISPATCH
> >>>   CALLED ON NULL INPUT
> >>>   EXTERNAL ACTION
> >>>   INHERIT SPECIAL REGISTERS
> >>>   BEGIN ATOMIC
> >>>     DECLARE v_TEST VARCHAR(4000);
> >>>     DECLARE v_TEST_Select VARCHAR(4000);
> >>>     DECLARE v_TEST_Sort VARCHAR(1000);
> >>>    DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
> >>>    (id INTEGER
> >>>     )
> >>>     ON COMMIT PRESERVE ROWS;
> >>>     SET v_TEST_Select = NULL;
> >>>     SET v_TEST_Sort = NULL;
> >>>     RETURN(v_TEST);
> >>>   END;
> >>> It give an error SQL 0104N "An unexpected token "Table" was found
> >>> following "..are Global Temporary".
> >>> What is the problem in that?
> >> Rahul,
>
> >> In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
> >> open cursors, etc.
> >> If you have complex SQL PL inside of a function it is advisable to write
> >> a stored procedure and then CALL that procedure from the UDF.
> >> Either way DDL in a function? That's a really bad idea. It would be slow
> >> and eat a lot of CPU.
> >> typically temp tables are declared much higher up. E.g. right after the
> >> connection. Constant DECLARE and DROP of a temp table causes
> >> recompilation of the statements using them which will eat at your CPU.
>
> >> For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
>
> >> Cheers
> >> Serge
> >> --
> >> Serge Rielau
> >> DB2 Solutions Development
> >> IBM Toronto Lab- Hide quoted text -
>
> >> - Show quoted text -
>
> > Serge,
>
> > I am trying to create the temporary table inside the procedure because
> > i have to use an array of varchars (the code written is only a portion
> > of the function which i was trying to convert from Oracle to DB2, so i
> > started with the smallest and basic chunk first), and you have said in
> > a thread in this group that we have to use a temporary table since DB2
> > doesn't support arrays.
>
> > I will try to write a proc and call it from the function, but still
> > why should the error come("Unexpected toke Table.....")...
> > Leaving performance issue, is there a synactical error?
>
> Yes it is a syntax error. If you check out teh link I posted to:
> Compound Statement (dynamic) you will find that DECLARE GLOBAL TEMPORARY
> TABLE is not in the syntax diagram, hence a -104.
> Using a temp table to pass around ARRAYS between PROCEDURES using tenmp
> tables has no relevance on DECLARING temps in FUNCTIONs.
> DB2 (unlike Oracle) clearly distinguishes between functions and procedures.
> Functions extend SQL (either expressions or relational operations)
> Procedures encapsulate procedural logic.
> In general do not DECLARE TEMP tables in the procedures that use them.
> Declare them at the beginning of your connections.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Thanks a lot Serge,

Can you please explain what you mean by
"Declare them(temp tables) at the beginning of your connections."
and
" Functions extend SQL (either expressions or relational operations)"

We have a scenario where application code calls a procedure or a
function, and we support both Oracle and DB2.
Further, we don't want the application code to call different
procedures/functions if the databases are different(atleast the name
should be same).
Now Oracle uses arrays in its function, i have to use Temp tables in
DB2 function, which cannot be done.

In the previous post(2nd last), you said, "If you have complex SQL PL
inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF. ".
So i thought of writing a procedure (and create temp table there) and
call that proc from my function.
However, you also said,
"In general do not DECLARE TEMP tables in the procedures that use
them."

In this context, i could not get what you mean by "beginning of
connection"?

Please don't mind any questions that may seem "silly" to you, since i
am an amateur to the Databases.

Thanks again.


0
Rahul
8/14/2007 4:46:36 AM
Rahul B wrote:
> Can you please explain what you mean by
> "Declare them(temp tables) at the beginning of your connections."
> and
> " Functions extend SQL (either expressions or relational operations)"
> 
> We have a scenario where application code calls a procedure or a
> function, and we support both Oracle and DB2.
> Further, we don't want the application code to call different
> procedures/functions if the databases are different(atleast the name
> should be same).
> Now Oracle uses arrays in its function, i have to use Temp tables in
> DB2 function, which cannot be done.
You can use temp tables in a procedure that is called by a fucntion, you 
just cannot DECLARE them

> In the previous post(2nd last), you said, "If you have complex SQL PL
> inside of a function it is advisable to write
> a stored procedure and then CALL that procedure from the UDF. ".
> So i thought of writing a procedure (and create temp table there) and
> call that proc from my function.
> However, you also said,
> "In general do not DECLARE TEMP tables in the procedures that use
> them."
Think about DECLARE GLOBAL TEMPORARY TABLE as being used the same way as
CREATE GLOBAL TEMPORARY TABLE in Oracle.
You would never dare CREATEing a table inside of a stored procedure.
(Some guy got skinned for that last week in c.d.oracle.server ;-)
What you do if you want to use a temp table is that you CREATEit in your 
DB schema and then just use it.
Now DB2's DGTT are not defined in the schema. They are privately defined 
per session.

What I recommend to customer is to use an init-procedure. E.g.
CREATE PROCEDURE inittemps()
BEGIN
   DECLARE GLOBAL TEMPORARY TABLE phonelists(id INT NOT NULL PRIMARY KEY,
                                             number BIGINT);
   DECLARE GLOBAL TEMPORARY TABLE ....;
END

Now sometime after you connect and whenever you create a procedure using 
the temp tables you CALL inittemps().

db2 -td%
CALL inittemps()
%
CREATE PROCEDURE myproc()
BEGIN
   DECLARE firstnum BIGINT;
   SET firstnum = (SELECT number FROM session.phonelists  WHERE id = 1);
END
%
terminate
%

Cheers
Serge




-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
0
Serge
8/14/2007 1:14:48 PM
Reply:

Similar Artilces:

declare global temporary table with com.ibm.as400.access.AS400JDBCDriver
Hello. V5R2 I try to create global temporary table in my Windows java application: ((java.sql.Connection) conn).createStatement().execute("declare global temportary table test (c1 integer)"); With COM.ibm.db2.jdbc.app.DB2Driver it works, but with com.ibm.as400.access.AS400JDBCDriver I get such SQLException: --start-- java.sql.SQLException: [PWS9801] Function rejected by user exit program IMBEPSQL in QUSRSYS. Cause . . . . . : User exit program IMBEPSQL in library QUSRSYS called for the function and returned an indication that the function should not be done. Recovery . . . : Determine why the user exit program rejected the function, correct the problem, and run the function again. --end--- ((SQLException) ex).getErrorCode() = -101 ((SQLException) ex).getSQLState() = "HY001" Is it possible to declare tables with this driver? Sincerely, Mark B ...

Re: Newbie Questio.. Getting a Virtual address space full and global page table full error.. How to repair.? page table full error.. How to repair.? page table full error.. How to repair.?
On 5/21/05, David J Dachtera <djesys.nospam@comcast.net> wrote: > Bruce Cooke wrote: > > > > Hi > > I have a Vaxstation 4000 60, vms 5.5 , 2 rz23l hard drives , and when > > booting get the subject messages. The computer wont boot into dec windo= ws, > > so the only way in is thru the DCL sysboot menu.I know there are spare > > blocks on the boot drive but have no idea how to fix no virtual address > > space error Any one > > here know how to solve this? >=20 > Well, Peter suggested AUTOGEN. If you don't know what that is, I...

DECLARE GLOBAL TEMPORARY TABLE problem
A bit longwinded; apologies in advance. My client has two large iSeries machines, both at V5R2 (they may be logical partitions of the same physical machine; I'm not sure.) The DECLARE GLOBAL TEMPORARY TABLE statement in SQL behaves a little differently on the two machines, causing me problems. I am issuing the statement in the Run SQL Scripts window of the navigator; *SQL naming is in effect (this matters.) On the development machine, the table created is owned by the group profile to which my user profile belongs; there is no *GROUP authority generated, and *PUBLIC authority ...

Reg: Owner for Declared Global Temporary tables
I am using below Query to find the declared global temporary tables in a Database: Below query is for DB2 version 9.1 , you can get same information by taking snapshot of the tables. SELECT * from SYSIBMADM.SNAPTAB WHERE TABSCHEMA = 'SESSION' ORDER BY TABNAME, DBPARTITIONNUM; Is it possible to find the Owner of the declared global Temporary table ? ( who actually created it) Thanks Ravi Ravi wrote: > I am using below Query to find the declared global temporary tables in > a Database: > Below query is for DB2 version 9.1 , you can get same information by > taking snapshot of the tables. > > SELECT * from SYSIBMADM.SNAPTAB WHERE TABSCHEMA = 'SESSION' ORDER BY > TABNAME, DBPARTITIONNUM; > > Is it possible to find the Owner of the declared global Temporary > table ? ( who actually created it) Hmm.. not sure what you are ultimately after, but if you want to enforce a quota you could control it through the user temp table space. I.e. give each group of users a different temp space. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab On Aug 30, 8:12=A0am, Serge Rielau <srie...@ca.ibm.com> wrote: > Ravi wrote: > > I am using below Query to find the declaredglobaltemporarytablesin > > a Database: > > Below query is for DB2 version 9.1 , you can get same information by > > taking snapshot of thetables. > > > SELECT =A0* from...

Getting SQL0526N error when updating a Global Temporary table.
Hi y'all, I am using a Created Global Temporary Table in DB2 OS/390 version 7.1.2. I am inserting some data in it and then updating the table using ODBC but while I try to update it, I am getting following message. SQL0526N The requested function does not apply to declared temporary tables. SQLSTATE=42995 Anyone has any ideas why I getting the above error message. Following is the code snippet. CDatabase* db; .... db->BeginTrans() .... CString sql_insert1 = "INSERT INTO PMD.G_FBLOT (ARRAY_ID, PROD_ID, POOL_NUM, POOL_TYPE, SEC_ID, SEC_TYPE_ID, FACTOR, FACTOR_...

Declared Global Temporary Table in multiple Stored Procedures
Hi, As I understand it, Declared Global Temporary Tables (DGTTs) have a scope that is session/connection based. Using the same connection, I have discovered that if I declare a DGTT in one stored procedure, then I can't create a second stored procedure that uses the DGTT, as the DGTT is not "visible". The only way around this is to use dynamic SQL in the second stored procedure. In this way (using dynamic sql) i can create and use the DGTT across multiple stored procedures. Is there perhaps a way to make DGTT visible across multiple stored procedures using static sql, or is this an inherint limitation of DGTTs. Cheers Otto Otto Carl Marte wrote: > Hi, > > As I understand it, Declared Global Temporary Tables (DGTTs) have a > scope that is session/connection based. > Using the same connection, I have discovered that if I declare a DGTT > in one stored procedure, then I can't create a second stored procedure > that uses the DGTT, as the DGTT is not "visible". The only way around > this is to use dynamic SQL in the second stored procedure. In this way > (using dynamic sql) i can create and use the DGTT across multiple > stored procedures. > > Is there perhaps a way to make DGTT visible across multiple stored > procedures using static sql, or is this an inherint limitation of > DGTTs. The limitation is this: To create a procedure that uses a DGTT the table must be declare...

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL30081N communication error on DB2/POrtal on AIX
Hi, I have installed WebSphere Portal on AIX and connected to DB2 on a remote machine, Getting the followin errors when trying to get the values from database thru applications installed on Portal. Any Help Appreciated. Thanks in advance. Praveen Singh IBM Workplace, India COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "9.33.12.221". Communication function detectin...

DB2 Express-C Global Temporary Table Performance on vista
After I have istalled express version of DB 9.5 on Vista. I create a stored procedure(used global temporary table), When i try to execute this procedure, it's very very slow, even if there is only one row can be fetched. But I try this on XP instead, it's ok. Does it Needs addtional setting for global temporary table on Vista? Thanks in advance. How much RAM is on the machine? Do you think it's possible that it's Vista that is usurping the memory? Larry E. DB2 wrote: > After I have istalled express version of DB 9.5 on Vista. > I create a stored procedure(used global temporary table), > When i try to execute this procedure, it's very very slow, > even if there is only one row can be fetched. > But I try this on XP instead, it's ok. > Does it Needs addtional setting for global temporary table on Vista? > > Thanks in advance. ...

Arrays vs Declared Global Temp Tables for Stored Procs in DB2 9.5
I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use Created Global Temporary Tables (CGTT). I am wondering how Arrays are implemented for stored procedures and what the performance is compared to declared global temporary tables (DGTT). In order to improve performance, I want to use arrays instead of a DGTT, but I want to make sure that Arrays are not creating a DGTT under the covers. Assume that Arrays provides me with the functionality I need. On 7/14/2010 12:45 AM, Martin wrote: > I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use > Created Global Temporary Tables (CGTT). > > I am wondering how Arrays are implemented for stored procedures and what the > performance is compared to declared global temporary tables (DGTT). In order > to improve performance, I want to use arrays instead of a DGTT, but I want > to make sure that Arrays are not creating a DGTT under the covers. > > Assume that Arrays provides me with the functionality I need. I'm actually preparing a talk on the topic. For now: ARRAYs are a continuous piece of memory allocated on the application heap. At lot less overhead than all the table infrastructure. On the flip side if you have some open-ended and huge arrays paired with many concurrent users... well you do the math. Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally different from DGTT (UPDATE, DELETE, INSERT, DEC...

load SAS table into DB2 temporary table
Hello, I have a problem with the load DB2. I'm working on MVS system under Z/OS and with DB2. I have to create a DB2 temporary table from a SAS table. I've tried 2 solution, the first work good but it's slow to store the records The second should be very faster but it doesn't work. The 2 solutions : 1. put all the record read from the SAS table into macro-variable then create the DB2 temporary table then do a loop to insert all macro-variable in the DB2 table then do a SQL request to try if the table is loaded ==> this work very good 2. create a SAS t...

Re: load SAS table into DB2 temporary table
Pascal: Enclose SQL create and delete queries in a SQL EXECUTE() function and pass them through to DB2. Once you have the table created, you can insert rows of data (using BULKLOAD to speed up inserting large numbers of rows). Ask your DB2 DBA about the correct syntax for DB2 temporary table name. Sig -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of tonts Sent: Wednesday, June 30, 2004 2:31 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: load SAS table into DB2 temporary table Lois, Thanks for your answer. But the problem is a little bit different ...

Convert SAP Oracle Database to IBM DB2 Database??
Hello, I would like to hear from anyone here who has converted their SAP Oracle database to IBM DB2 database? Did you realize greater disk saving via DB2 compression? Did you run the latest version of DB2 rather than allow SAP to keep your Oracle version back-leveled? Please, DB2 Bashers need not apply, looking for the business case anyone used to convert to DB2. Thank you. Charles On 6 Feb, 01:52, "Charles Davis" <cdavis10...@comcast.net> wrote: > Hello, > > I would like to hear from anyone here who has converted their SAP Oracle > database to IBM DB2 d...

Connect to IBM DB2 database without installing the DB2 client.
Hi, To connect to the DB2 Driver, from a remote machine. Do we requier DB2 client. Can't we connect by just having IBM DB2 driver which is just a single file (db2cli.dll). I am using vb application from Windows platform to connect to DB2 on Linux machine. Regards, Santosh SB wrote: > Hi, > > To connect to the DB2 Driver, from a remote machine. Do we requier DB2 > client. > Can't we connect by just having IBM DB2 driver which is just a single > file (db2cli.dll). > I am using vb application from Windows platform to connect to DB2 on > Linux machine. > It's imposiblle. The IBM DB2 ODBC Driver is not only db2cli.dll. Try to install DB2 Run Time Client Lite merge modules with your VB application under client workstation. Direct connecting to DB2 databases supported JDBC Type 4 Driver only. P.S. Sorry for my bad english :( With best regards, Dmitry. FYI: On Windows DB2 comes with db2iprune tool. With this utility you can reduce size of installation binaries, for example you can reduce your DB2 runtime client to include only the interfaces you need. The db2iprune tool is located in the \db2\windows\utilities\db2iprune directory on DB2 product installation image. So, if you are planning install DB2 client with/within your application you can combine the tool with the response file and perform quick batch installation. -- Artur Wronski ...

ERROR: CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664]
I am kind of stuck to this... Has anyone experienced this previously.... Following is my code... PROC SQL; CONNECT TO DB2(DB=DOACD011 SCHEMA='MEMBER' USER=&UID PASSWORD=&PWD); CREATE TABLE ODS_ALL_RECORDS AS SELECT * FROM CONNECTION TO DB2 ( SELECT A.SUTL_RGTR_ID ,A.SUTL_RGTR_TYP_CDE ,A.SUTL_RGTR_TITL_NM ,A.CRCT_IND ,CASE A.SUTL_RGTR_STS_DT WHEN '0001-01-01' THEN '1' END AS RSDT ,CASE A.STRG_ANN_INCM_DT WHEN '0001-01-01' THEN '1' END AS AIDT ,CASE A.STRG_NET_WRT...

CURSORS AND manipulating data into a temporary table variable OR global table variable
Help T-SQL Server developer lost trying to implement some simple cursor logic! I need to create a "table object (variable)" OR "TEMP variable" OR "create and drop a table in a procedure" in DB2 to eliminate duplicate records from my query. In the procedure I simply want to insert unique rows into this temporary table ... using a cursor to insert distinct rows by using the (FETCH FIRST 1 ROW ONLY) and (ORDER BY) clauses; into my result set. I know this can be done with a group by but the code gets too messy(and the qery becomes inefficient) (also a temp table would make sense ince the result set is less than a 100 rows out of millions of records), and the data keeps changing so I'm not certain what the distinct field is. Can anyone help with code or pseud-code or link to a good tutorial. I cannot find simple db2 cursor examples or examples on how to create temporary tables or global variables. Please help! My code in T-SQL would look like: ************************************************************************** create procedure returnUniqueMasterRecords as insert into #distinct_Data select * from dbo.Master_SRC_With_Dups where Master_Id in (select Master_Id from dbo.Master_SRC_With_Dups group by Master_Id having count(*) =1) declare @Var_ID varchar(30) DECLARE MasterDup_cursor CURSOR FOR select distinct Master_Id from dbo.Master_SRC_With_Dups where Master_Id in (select Master_Id from dbo.Ma...

global temporary tables?
In our application we have a table that tracks network sessions. The usage is: 1) create a session record 2) read/update the record several times during the session 3) delete the session record when the session ends Update activity on this table is going to be pretty intense, and the transient nature of the data makes it a good candidate for a temporary and/or in-memory table. Unfortunately, different network connections may be updating the same session record at different times (connection pool), so the CREATE TABLE TEMPORARY doesn't seem to fit the bill, since i...

Global Temporary Tables
Each, Am wanting to create a temporary table within a PLSQL package. Have used EXECUTE IMMEDIATE 'create global temporary table temp_status........' Problem now is that package wont compile due to insert statements for this table. Compiler has no idea about this temp table or its structure. I take it temporary tables arent designed for this use are am i missing something here? Cheers Roy Munson wrote: > Each, > > Am wanting to create a temporary table within a PLSQL package. Have > used > > EXECUTE IMMEDIATE 'create global temporary table temp_status.........

Re: ERROR: CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664]
On Wed, 19 Dec 2007 13:01:54 -0800, shaunak.adgaonkar@gmail.com <shaunak.adgaonkar@GMAIL.COM> wrote: >I am kind of stuck to this... Has anyone experienced this >previously.... Following is my code... > >PROC >SQL; >CONNECT TO DB2(DB=DOACD011 >SCHEMA='MEMBER' > USER=&UID >PASSWORD=&PWD); > CREATE TABLE ODS_ALL_RECORDS >AS > SELECT * FROM CONNECTION TO >DB2 > >( > >SELECT > >A.SUTL_RGTR_ID > ,A.SUTL_RGTR_TYP_CDE > ,A.SUTL_RGTR_TITL_NM > ,A.CRCT_IND > ,CASE > A.SUTL_...

Global temporary table
Hi all! I dont know how to declare and use a global temporary table in my java stored procedure.... I used these statements: sql="declare global temporary table session.temp(recordinteger,eventtype character(20)) not logged"; Statement st=con.createStatement(); st.execute(sql); For inserting I'm trying to do.. sql="insert into session.temp values(2003,'fatal error'); st.executeUpdate(sql); But its flagging an error.With just the declare statement there is no error.But i read somewhere that a temporary table space needs to be created.I dont kn...

Global Temporary Table
Hello Oraclegurus, I would like to know if i am doing the right thing or not?? I've created a global temporary table "tempcomp". "create global temporary table tempcomp (col1 number,col2 char,col3varchar2)" . I looked into these groups and it was mentioned somewhere that default is "on commit delete rows"... I am assuming that the rows are being deleted after commit and not stored . We have a web application which would be calling a stored procedure which inserts into a global temporary some values we nee...

ERROR: CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0206N
I am kind of stuck to this... Has anyone experienced this previously.... Following is my code... PROC SQL; CONNECT TO DB2(DB=DOACD011 SCHEMA='MEMBER' USER=&UID PASSWORD=&PWD); CREATE TABLE ODS_ALL_RECORDS AS SELECT * FROM CONNECTION TO DB2 ( SELECT A.SUTL_RGTR_ID ,A.SUTL_RGTR_TYP_CDE ,A.SUTL_RGTR_TITL_NM ,A.CRCT_IND ,CASE A.SUTL_RGTR_STS_DT WHEN '0001-01-01' THEN '1' END AS RSDT ,CASE A.STRG_ANN_INCM_DT WHEN '0001-01-01' THEN '1' END AS AIDT ,CASE A.STRG_NET_WRT...

query sql server/oracle table from Db2 or join db2 table with sqlserver table
My application connects to a DB2 V8.2 EE database running on AIX. I need to now lookup a table in SQL SERVER via the DB2 connection. I also need to run an sql that joins a DB2 table with an Oracle table. How can I accomplish this ? Do i need a seperate product ? I also have license for Db2 v8 connect that I use for connecting to db2 on the mainframe. TIA Roger ...

Global Temporary Tables
Hi, I am inserting records to a global temporary table(say A) in which a trigger is fired and im getting results from other global tempory tables(say B, C, D,..)... This is my scenario... I am able to fetch the results from the global temporary tables B,C,D,...when inserting directly in the table A from the front end. But I am not able to fetch the results from the global temporary tables B,C,D,...when inserting thru a web service (in dotnet)in the table A from the front end. Can somebody explain the reson for the above and give a solution to fetch the records from the global tempory tables thru webservice..Thanks in advance. ...

query sql server/oracle table from Db2 or join db2 table with sqlserver table #2
My application connects to a DB2 V8.2 EE database running on AIX. I need to now lookup a table in SQL SERVER via the DB2 connection. I also need to run an sql that joins a DB2 table with an Oracle table. How can I accomplish this ? Do i need a seperate product ? I also have license for Db2 v8 connect that I use for connecting to db2 on the mainframe. TIA Roger Roger wrote: > My application connects to a DB2 V8.2 EE database running on AIX. I > need to now lookup a table in SQL SERVER via the DB2 connection. I > also need to run an sql that joins a DB2 table with an Oracle table. > How can I accomplish this ? Do i need a seperate product ? > I also have license for Db2 v8 connect that I use for connecting to > db2 on the mainframe. You need to look at Websphere Information Integrator along with the relational wrappers for MS SQL server and Oracle. Ian, its a canned CRM application that currently connects to DB2. I cannot run the app on Websphere. A few years could use relational connect and create federated nickname on db2. Can I still do that ? TIA On Apr 27, 6:01 pm, Ian <ianb...@mobileaudio.com> wrote: > Roger wrote: > > My application connects to a DB2 V8.2 EE database running on AIX. I > > need to now lookup a table in SQL SERVER via the DB2 connection. I > > also need to run an sql that joins a DB2 table with an Oracle table. > > How can I accomplish this ? Do i n...

Web resources about - Error while declaring a global temporary table - comp.databases.ibm-db2

Anonymous declaring war on Singapore Government - YouTube
Anonymous declaring war on Singapore Government Greetings Government of Singapore, We are Anonymous and we believe that we have your undivided ...

Man Haron Monis ordered chocolate cake and a cup of tea before declaring, 'I have a bomb', inquest hears ...
At first, he appeared to be just another person wandering through Martin Place on a sunny Monday morning.

Most ACT Assembly members not declaring or using frequent flyer points
Most Assembly members are not declaring frequent flyer points and fewer still are using the points to offset the cost of flying

Eddie Hayson: How one of Australia’s biggest punters lost a $52 million bet — declaring himself insolvent ...
BROTHEL owner Eddie Hayson is on the brink of bankruptcy after declaring himself insolvent with $52 million in debts.

Malcolm Turnbull breaks ranks on citizenship, declaring constitution cannot be compromised
Malcolm Turnbull has declared official efforts to protect national security and prosecute the war on&nbsp;terrorism must not be allowed to erode ...

Odessa buries its dead, declaring 'never forget, never forgive'
Under thunderous skies, several dozen mourners could contain themselves no longer, bursting into chants of "Glory to the heroes" as the coffin ...

Shorten's delay in declaring
OPPOSITION Leader Bill Shorten failed to declare tens of thousands of dollars in political donations he received in the lead-up to the 2007 election ...

Is Netflix declaring war on geo-dodgers?
Australians' access to US Netflix could soon be cut off, with streaming giant reportedly blocking use of some bypass tools.

Daniel Morcombe murder: Judge stopped short of declaring mistrial twice
During Brett Cowan's five-week trial for the murder of Daniel Morcombe, the case came close to falling over twice.

'Lost Canadians' petition seeks declaring world wars dead as Canadian citizens - CTV News
When tribute is paid on Remembrance Day to the soldiers, sailors and flyers killed in the service of Canada during two world wars, Canadians ...

Resources last updated: 3/6/2016 7:28:03 PM