f



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.Master_SRC_With_Dups
 group by Master_Id
 having count(*) >1)
 order by Master_Id desc

OPEN MasterDup_cursor

FETCH NEXT FROM MasterDup_cursor
INTO @Var_ID

WHILE @@FETCH_STATUS = 0
BEGIN

 insert into #distinct_Data
 select top 1 * from dbo.Master_SRC_With_Dups where Master_Id =
@Var_ID
 order by distinctive_Field desc

 FETCH NEXT FROM MasterDup_cursor INTO @Var_ID

END

CLOSE Tower_cursor
DEALLOCATE Tower_cursor

select * from #distinct_Data
return 0

**************************************************************************

I queried the database version in the event that this makes a
difference:

SERVEICE_LEVEL    FIXPACK_NUM
DB2 v8.1.1.112           12

SELECT service_level, fixpack_num FROM TABLE
(sysproc.env_get_inst_info())
as INSTANCEINFO
0
SQLBusinessIntellige
1/27/2008 6:00:34 AM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

4 Replies
568 Views

Similar Articles

[PageSpeed] 10

I'll skip over your comments that writing this is one query is messy.
I see lots of room for optimization in your logic.

What you are looking for is the DECLARE GLOBAL TEMPORARY TABLE statement.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
0
Serge
1/27/2008 12:34:12 PM
Why are you posting SQL Server/Sybase dialect on a DB2 newsgroup?

SELECT X.master_id, etc.,
  FROM (SELECT master_id, etc.,
                ROW_NUMBER()
                OVER(PARTITION BY master_id
                ORDER BY distinctive_field DESC) AS rn
          FROM Foobar) AS X
WHERE X.rn = 1;

Did you notice that you used the dummy name "distinctive_field" and
not "distinctive_column"?  That's why you are looking for cursors and
temp tables!  You are not writing SQL yet, but only faking a magnetic
tape file system in an old procedural language with SQL.  You don't
know that columns are nothing like fields, that tables -- unlike files
-- can be virtual, etc.

Your entire approach to SQL is wrong and you need to stop programming
until you get a basic education in RDBMS.
0
CELKO
1/27/2008 2:34:15 PM
On Jan 26, 10:00 pm, SQLBusinessIntellige...@gmail.com wrote:
> 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.Master_SRC_With_Dups
>  group by Master_Id
>  having count(*) >1)
>  order by Master_Id desc
>
> OPEN MasterDup_cursor
>
> FETCH NEXT FROM MasterDup_cursor
> INTO @Var_ID
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
>  insert into #distinct_Data
>  select top 1 * from dbo.Master_SRC_With_Dups where Master_Id =
> @Var_ID
>  order by distinctive_Field desc
>
>  FETCH NEXT FROM MasterDup_cursor INTO @Var_ID
>
> END
>
> CLOSE Tower_cursor
> DEALLOCATE Tower_cursor
>
> select * from #distinct_Data
> return 0
>
> **************************************************************************
>
> I queried the database version in the event that this makes a
> difference:
>
> SERVEICE_LEVEL    FIXPACK_NUM
> DB2 v8.1.1.112           12
>
> SELECT service_level, fixpack_num FROM TABLE
> (sysproc.env_get_inst_info())
> as INSTANCEINFO


Maybe this will help:

CREATE PROCEDURE DGTT_EXAMPLE()
	SPECIFIC DGTT_EXAMPLE
	INHERIT SPECIAL REGISTERS
	CALLED ON NULL INPUT
	DYNAMIC RESULT SETS 1
BEGIN

	-- Declare variables
	DECLARE V_NO_DATA						SMALLINT DEFAULT 0;--
	DECLARE V_DEADLOCK_OR_LTO	SMALLINT DEFAULT 0;--
	DECLARE V_RETURN 						CHAR(31) DEFAULT 'SELECT * FROM
SESSION.DISTINCT_DATA';--

	-- Declare conditions
	DECLARE C_DEADLOCK_OR_LTO CONDITION FOR SQLSTATE '40001';--

	-- Declare cursor for dynamic SQL statement for the result set
	 DECLARE C_RETURN CURSOR WITH RETURN TO CALLER FOR S_RETURN;--

	-- Declare handlers
	DECLARE CONTINUE HANDLER FOR NOT FOUND
		SET V_NO_DATA = 1;--

	DECLARE CONTINUE HANDLER FOR C_DEADLOCK_OR_LTO
		SET V_DEADLOCK_OR_LTO = 1;--

	DECLARE GLOBAL TEMPORARY TABLE
		SESSION.DISTINCT_DATA
		(
			MASTER_ID	BIGINT,
			ETC....
			)
		ON COMMIT PRESERVE ROWS
		NOT LOGGED
		ON ROLLBACK DELETE ROWS
	WITH REPLACE;--

	INSERT INTO
		SESSION.DISTINCT_DATA
		(
		MASTER_ID,
		ETC....
		)
	VALUES
		(
		ETC.....
		);--

	CREATE INDEX SESSION.DD_N1 ON SESSION.DISTINCT_DATA (MASTER_ID) ALLOW
REVERSE SCANS;--
	CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE
SESSION.SESSION.DISTINCT_DATA WITH DISTRIBUTION AND DETAILED INDEXES
ALL');--

	<<DO MORE STUFF HERE>>

	-- Prepare the result set cursor and return its contents to the SP
caller
	PREPARE S_RETURN FROM V_RETURN;--

	OPEN C_RETURN;--

END;

--Jeff
0
jefftyzzer
1/28/2008 11:29:27 PM
On Jan 28, 3:29 pm, jefftyzzer <jefftyz...@sbcglobal.net> wrote:
> On Jan 26, 10:00 pm, SQLBusinessIntellige...@gmail.com wrote:
>
>
>
> > 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.Master_SRC_With_Dups
> >  group by Master_Id
> >  having count(*) >1)
> >  order by Master_Id desc
>
> > OPEN MasterDup_cursor
>
> > FETCH NEXT FROM MasterDup_cursor
> > INTO @Var_ID
>
> > WHILE @@FETCH_STATUS = 0
> > BEGIN
>
> >  insert into #distinct_Data
> >  select top 1 * from dbo.Master_SRC_With_Dups where Master_Id =
> > @Var_ID
> >  order by distinctive_Field desc
>
> >  FETCH NEXT FROM MasterDup_cursor INTO @Var_ID
>
> > END
>
> > CLOSE Tower_cursor
> > DEALLOCATE Tower_cursor
>
> > select * from #distinct_Data
> > return 0
>
> > **************************************************************************
>
> > I queried the database version in the event that this makes a
> > difference:
>
> > SERVEICE_LEVEL    FIXPACK_NUM
> > DB2 v8.1.1.112           12
>
> > SELECT service_level, fixpack_num FROM TABLE
> > (sysproc.env_get_inst_info())
> > as INSTANCEINFO
>
> Maybe this will help:
>
> CREATE PROCEDURE DGTT_EXAMPLE()
>         SPECIFIC DGTT_EXAMPLE
>         INHERIT SPECIAL REGISTERS
>         CALLED ON NULL INPUT
>         DYNAMIC RESULT SETS 1
> BEGIN
>
>         -- Declare variables
>         DECLARE V_NO_DATA                                               SMALLINT DEFAULT 0;--
>         DECLARE V_DEADLOCK_OR_LTO       SMALLINT DEFAULT 0;--
>         DECLARE V_RETURN                                                CHAR(31) DEFAULT 'SELECT * FROM
> SESSION.DISTINCT_DATA';--
>
>         -- Declare conditions
>         DECLARE C_DEADLOCK_OR_LTO CONDITION FOR SQLSTATE '40001';--
>
>         -- Declare cursor for dynamic SQL statement for the result set
>          DECLARE C_RETURN CURSOR WITH RETURN TO CALLER FOR S_RETURN;--
>
>         -- Declare handlers
>         DECLARE CONTINUE HANDLER FOR NOT FOUND
>                 SET V_NO_DATA = 1;--
>
>         DECLARE CONTINUE HANDLER FOR C_DEADLOCK_OR_LTO
>                 SET V_DEADLOCK_OR_LTO = 1;--
>
>         DECLARE GLOBAL TEMPORARY TABLE
>                 SESSION.DISTINCT_DATA
>                 (
>                         MASTER_ID       BIGINT,
>                         ETC....
>                         )
>                 ON COMMIT PRESERVE ROWS
>                 NOT LOGGED
>                 ON ROLLBACK DELETE ROWS
>         WITH REPLACE;--
>
>         INSERT INTO
>                 SESSION.DISTINCT_DATA
>                 (
>                 MASTER_ID,
>                 ETC....
>                 )
>         VALUES
>                 (
>                 ETC.....
>                 );--
>
>         CREATE INDEX SESSION.DD_N1 ON SESSION.DISTINCT_DATA (MASTER_ID) ALLOW
> REVERSE SCANS;--
>         CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE
> SESSION.SESSION.DISTINCT_DATA WITH DISTRIBUTION AND DETAILED INDEXES
> ALL');--
>
>         <<DO MORE STUFF HERE>>
>
>         -- Prepare the result set cursor and return its contents to the SP
> caller
>         PREPARE S_RETURN FROM V_RETURN;--
>
>         OPEN C_RETURN;--
>
> END;
>
> --Jeff

Typo: the RUNSTATS line should read with one less "SESSION" in it,
i.e.,

CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE SESSION.DISTINCT_DATA WITH
DISTRIBUTION AND DETAILED INDEXES
ALL');--

--Jeff
0
jefftyzzer
1/28/2008 11:38:54 PM
Reply:

Similar Artilces:

What's the difference between temporary table and table variable?
How to use both? On Apr 16, 6:21=A0am, Phper <hi.steven...@gmail.com> wrote: > How to use both? See the link. http://www.lmgtfy.com/?q=3DWhat%27s+the+difference+between+temporary+table+= and+table+variable ...

TABLES TABLES TABLES
How would you best describe to a retiscent SAS student that the concept of TABLES isn't limited to SQL? This came up during a discussion on table lookup methods - and lookup tables in particular (I mean, lookup tables pre- date SQL). Even SAS data sets are referred to as tables. This made the student apoplectic. Words of wisdom most welcome!!! Thanks, Howard sasbum@AOL.COM wrote: >How would you best describe to a retiscent SAS student that the concept of >TABLES isn't limited to SQL? This came up during a discussion on table >lookup methods - and lookup tables in partic...

reorg using system temporary table space for table in tablespaces with variable page size
The main and index table space have page sizes of 8k The long data table space has page size of 4k The page size of the temporary table is 4k REORG TABLE USERBLAH.MYTABLE USE TEMPTBS; When I try to run a reorg on my table I get following errors: 2004-07-30-05.15.01.927000 Instance:DB2 Node:000 PID:1252(db2syscs.exe) TID:1628 Appid:AC10C41E.5611.040730031501 relation_data_serv sqlrreorg_table Probe:15 Database:TEST Reorg: DMS return rc = 06a4 ffff .��� 2004-07-30-05.15.02.036000 Instance:DB2 Node:000 PID:1252(db2sysc...

global variable cannot hook up to Excel Easy Table data in node
I have Labview 8, windows 2000. I am trying to write a global variable into the data node of Excel Easy Table.vi&nbsp; but it won't connect. Easy Table is looking for an 8 bit unsigned 1D array and I can't configure the variable to match. Has anyone had this same problem? I'm trying to write the variable to a specific cell in an excel spreadsheet. Thank you. What is the datatype of your global variable?&nbsp; It would probably help if you attached a sample VI (along with a sample global) that demonstrates the problem.&nbsp; The Excel Easy Table.vi is a polymorphic VI ...

Temp tables vs table variables
I am running SQL Server Best Practices on a SQL 2000 database and it is recommending me to change the temp tables inside SPs to table variables. I had read already in other places to use table variables over temp tables. I also know I can't create indexes as I can on temp tables. Instead I'll have to create either a primary key and/or a unique index on a table variable. One question I have is let's say I will be putting thousands of records in a temp table, should i still choose a table variable over a temp table for this? Or is there a recommended limit where if I have to store ...

DB2 Integrity Checks and Exception Tables- How can I move the exception table data back to the original table?
I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on. A few notes on this migration: We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc). We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database. We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue. After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following: set integrity for . immediate checked This works for most tables, however, some tables give an error: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514 The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any e...

How to display database table data in a html table.
Hi, I'm trying to display data from a mysql database in a HTML table but for some reason my code isn't working. At the moment I have got it to read and display the headers and the first row of the table and it actually creates the remaining rows in the html table but it doesn't put any data in them. This is my code so far: <?php $con = mysql_connect("localhost","REMOVED","REMOVED"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("db_03009319", $con); ?> <?php $selectedTable=$_GET["sel...

create table with variable names of another table
Hi, I have a table with approx. 500 variables and I want to do some analyses om this data. Do you know of a statement to create a table with names of variables (not labels), and then how to call these variables in future analyses? Thx guys On Sep 5, 12:19=A0pm, Espen <espe...@gmail.com> wrote: > Hi, > I have a table with approx. 500 variables and I want to do some > analyses om this data. Do you know of a statement to create a table > with names of variables (not labels), and then how to call these > variables in future analyses? Thx guys Sounds like you should just writ...

Tables of frequencies in TABLES with multiple response variables?
Hi, Is it possible to use multiple response sets in Tables of Frequencies (using TABLES procedure)? The table that I want to build is about brand awareness. I want to make a table that shows frequencies (counts or percentages) of first-mentioned brand in the first column (this is a single coded variable) and in the second column frequencies of total mentioned brands, including the first-mentioned. This second column obviously is a multiple response set, and here it gives me a problem to construct the frequencies table. Tal van Dijk TNS/Monitor ...

Create Table with Variable included in Table Name
Hi, I am trying to create a routine for generating tables from a master response table. The end result would increment the variable "somevalue" by one and then create the next table (ie: TESTTEST2). Is there a way to insert the variable into the Table Name as well as one or more of the Field Names within the table? I've tried some different syntax variations and haven't hit on the right combo yet! .....other stuff..... .... DECLARE @somevalue AS INT SET @somevalue = 1 frontend: IF somevalue > 10 BEGIN TRANSACTION GO CREATE TABLE TESTTEST+CAST(@somevalue AS char(2)) (...

How to transfer data from temporary table to permanent table
Hi all, Need help. 1. I have two tables with same schema one is temporary and another is permanent. How do we move all the data from temporary table to permanent table at once. 2.while using dbload command for a file if I have duplicate records in 1st and 4th , 10th and 15th then my error log is showing error in 1st to 4th then 15th record. But I need only records those having error to be printed in my error file. If I commit dbload on each record the error comes properly but that would be like I am inserting each record through insert command. Please suggest Regards Deba sending to informix-list ...

Table Variable much slower than #Temp table: Why?
I have a statement that looks like this and runs incredibly slowly (over 3 min to return data): SELECT <columns> ,sum(<other_column>) FROM TABLE_A as a INNER JOIN SELECT * FROM UDF_A(<arguments>) as x ON x.<column> = a.<column> GROUP BY <columns> UDF_A is a multi-statement function, but it returns its table valued result in less than a second, which is plenty fast for what it does. The returned table variable has a clustered primary key, and generally ranges in size from 50 to a few hundred rows. If I use a #temp table, the statement returns results...

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

Initialize global variable before any other global variables
I need to initiate a global variable to a class before the initialization of any other global variable. The problem is that when I link the application with a dll, some or all global variables in the dll is initialized before mine. Is there a way to ensure that my global variable is initialized before any other? Take a simple class class test { } #pragma init_seg(compiler) test t; According to msdn, "Objects in this group are constructed first". That's the "compiler" group. I'm using Microsoft Visual Studio.net 2005/VC++ on Windows XP jubelbrus wrote: > ...

global variables in design tables
OK, I'm using SW2K5 with global variables. I created some configurations and now want to manage them via a design table. How do I update these global variables in design table? I went through the help without any luck. What am I missing? Thanks Keith By "global values" do you mean linked values? Did they change the name and I failed to notice? Anyhow, find an instance of a dimension where it is used and link that into the design table in the usual way. In 2005 they introduced global variables in the equation editor. So I can enter dog=25, which becomes "dog&quo...

How to a value from a table into a global variable?
I need read a value from a table into a global variable, which will be used in several places in my SAS code. How can I do that? I tried the following method no success. Looks alphad0 is a local variable in the assign_value_aphad0 macro. while I need the global alphad0 value changed to x&tb1 from table minw. any thoughts? thanks a lot! %let alphad0=0; /*initiate alphad0*/ %Macro assign_value_alphad0; proc iml; use minw; read all var {x&tb1}; alphad0= x&tb1; /*assign x&tb1 value in table minw to alphad0*/ close minw; print alphad0; /* seems fine*/ run; %Mend assign_value_alph...

Global variable in a design table
Can a global variable be used in a design table? I am trying to set up a design table for a set of cylinder pistons. The normal way to call out the piston would be by the Bore of the cylinder it is in. The actual OD of the piston would be .019" smaller. I have the part set up with a global variable "Bore" = 5, then the dimension on the piston is Bore-.019 Thanks, Brian Sure. Easiest way is to have a Bore cell in the table that holds the value. Then the piston OD math is done in its own cell, referencing the Bore cell. WT "Brian" <bputnam@rugbymfg.com>...

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

How to transfer data from temporary table to permanent table #2
Hi all, Need help. 1. I have two tables with same schema one is temporary and another is permanent. How do we move all the data from temporary table to permanent table at once. 2.while using dbload command for a file if I have duplicate records in 1st and 4th , 10th and 15th then my error log is showing error in 1st to 4th then 15th record. But I need only records those having error to be printed in my error file. If I commit dbload on each record the error comes properly but that would be like I am inserting each record through insert command. Please suggest Regards Deba sending to informix-list sending to informix-list debadatta_mishra wrote: > 1. I have two tables with same schema one is temporary and another is > permanent. How do we move all the data from temporary table to permanent > table at once. As OTC said: INSERT INTO Permanent SELECT * FROM Temporary; > 2.while using dbload command for a file if I have duplicate records in > 1st and 4th , 10th and 15th then my error log is showing error in 1st to > 4th then 15th record. But I need only records those having error to be > printed in my error file. If I commit dbload on each record the error > comes properly but that would be like I am inserting each record through > insert command. Your question is not clear - as OTC also commented. However, if I understand correctly, then you have at least 15 records to insert. When using DB-Loa...

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

Re: How to transfer data from temporary table to permanent table
debadatta_mishra said: > > 1. I have two tables with same schema one is temporary and another is > permanent. How do we move all the data from temporary table to permanent > table at once. INSERT INTO permanent_table SELECT * FROM temporary_table; > 2.while using dbload command for a file if I have duplicate records in > 1st and 4th , 10th and 15th then my error log is showing error in 1st to > 4th then 15th record. But I need only records those having error to be > printed in my error file. If I commit dbload on each record the error > comes properly but that would be like I am inserting each record through > insert command. Ne pas comprendez... -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien � dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco Travel broadens a person. You look as if you have been all over the world. I went to the airport to check in and they asked what I did because I looked like a terrorist. I said I was a comedian. They said, "Say something funny then." I told them I had just graduated from flying school. -- Ahmed Ahmed http://i2.photobucket.com/albums/y41/Obnoxio/thinkIfoundtheproblem.jpg sending to informix-list ...

Re: TABLES TABLES TABLES
sasbum@AOL.COM wrote: >How would you best describe to a retiscent SAS student that the concept of >TABLES isn't limited to SQL? This came up during a discussion on table >lookup methods - and lookup tables in particular (I mean, lookup tables >pre- >date SQL). Even SAS data sets are referred to as tables. This made the >student apoplectic. I find a visual works well here. If you show a grid with rows and columns, then you can show a picture of a SAS data set and a SQL table and a lookup table, and show that (regardless of terminology and buzzwords) they're all pr...

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

Global Variables & Design Tables
Does anyone know if a Global Variable can be controlled by a design table? -- Seth Renigar Emerald Tool and Mold Inc. (Remove ".no.spam" from my address) __ Elaborate on Global Variable. NO, in 2005 SW introduced global variables, but did not implement them to be controlled via design tables. I have this one in as a enhancement request or bug fix or what ever to SW support. You will need to create a bogus dim in a sketch and link that to the design table and the global variable with equations as a work around until it's implemented. Keith "Seth Renigar" <...

Web resources about - CURSORS AND manipulating data into a temporary table variable OR global table variable - comp.databases.ibm-db2

123D Tutorial: Manipulating components and incorporating design intent - YouTube
http://www.123Dapp.com/support - get the steps and model and do this tutorial yourself! Have few extra parts left over after putting your project ...

Indigenous land owners accuse lawyer of manipulating nuclear waste storage report
Lawyer who was key to Howard government's plan to store nuclear waste on indigenous land accused of manipulating legal process required to ensure ...


Doctor Mervyn Jacobson faces lengthy jail sentence after being found guilty of manipulating stock market ...
He has been praised by American presidents and British lords for his wildlife conservation efforts, and claims to have saved the brush-tailed ...

Facebook's Adam Kramer defends controversial emotion manipulating study
The creator of the controversial Facebook study now admits the findings "may not have justified all of this anxiety".

Australia accused of manipulating Australian dollar to save iron ore miners
... iron ore miners are so desperate for cash that ''even the kangaroos are for sale". An American iron ore miner says Australia is "manipulating" ...

Government accused of manipulating science news
The federal government engages in "unacceptable political interference" in the communication of government science, says the head of a group ...

Commentary: Japan's Abe manipulating a dangerous coup against pacifist Constitution
TOKYO,June30JapanesePrimeMinisterShinzoAbeismanipulatingadangerouscouptooverturnthecountry'spost-w

Over 50 US military analysts say Pentagon is manipulating Daesh reports
Over 50 US military analysts say Pentagon is manipulating Daesh reports

Volkswagen ordered to recall nearly 500,000 vehicles for manipulating software to circumvent emissions ...
Volkswagen has been manufacturing cars that only fully deploy their emissions control systems when they are undergoing fuel emissions inspection, ...

Resources last updated: 3/7/2016 2:08:31 AM