SEO

#1 (**permalink**) 02-27-2008, 04:58 AM

Begin Atomic
Declare a integer;

Set a=(select count(1) from claims.table1);

if a<>0 then

import into c:\tmptyfile.txt of del replace into claims.table1;
end if;
End!
Commit!

the above returns an error. How do I execute db2 commands inside atomic
statement.
Error is shown as below:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "into" was found following "table1);
db2
import". Expected tokens may include: "JOIN". LINE NUMBER=10.
SQLSTATE=42601

#2 (**permalink**) 02-27-2008, 04:58 AM

hikums@gmail.com wrote:
> Begin Atomic
> Declare a integer;
>
> Set a=(select count(1) from claims.table1);
>
> if a<>0 then
>
> import into c:\tmptyfile.txt of del replace into claims.table1;
> end if;
> End!
> Commit!
>
> the above returns an error. How do I execute db2 commands inside atomic
> statement.
> Error is shown as below:
>
> DB21034E The command was processed as an SQL statement because it was
> not a
> valid Command Line Processor command. During SQL processing it
> returned:
> SQL0104N An unexpected token "into" was found following "table1);
> db2
> import". Expected tokens may include: "JOIN". LINE NUMBER=10.
> SQLSTATE=42601
>
BEGIN ATOMIC.. END is an SQL statement, not a command. That's why.
Now I'm curious on this construct:
You count all rows just to test whether the table is empty.
It's a bit like counting stars when the question is whether the night is
clear. If you speed this up using an EXISTS then maybe you can drive teh
logic thorugh your app without much problem. You can use a high
isolation level if you want to make sure noone sneaks in a row.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

#3 (**permalink**) 02-27-2008, 04:58 AM

Thanks Serge for your tip.

Correction: The import/load into is not into table1(T1) but into
table2(T2). Is there no option from task center or some script to
execute this kind of a logic?

I want to check a table say T1 if it contains records, and if so, then
delete rows in the huge table say T2(I wanted to accomplish this
quicker by load replace with empty file or with "alter table T2
activate not logged initially with empty table"). Then I want to insert
records from T3 to T2.

Serge Rielau wrote:
> hikums@gmail.com wrote:
> > Begin Atomic
> > Declare a integer;
> >
> > Set a=(select count(1) from claims.table1);
> >
> > if a<>0 then
> >
> > import into c:\tmptyfile.txt of del replace into claims.table1;
> > end if;
> > End!
> > Commit!
> >
> > the above returns an error. How do I execute db2 commands inside atomic
> > statement.
> > Error is shown as below:
> >
> > DB21034E The command was processed as an SQL statement because it was
> > not a
> > valid Command Line Processor command. During SQL processing it
> > returned:
> > SQL0104N An unexpected token "into" was found following "table1);
> > db2
> > import". Expected tokens may include: "JOIN". LINE NUMBER=10.
> > SQLSTATE=42601
> >
> BEGIN ATOMIC.. END is an SQL statement, not a command. That's why.
> Now I'm curious on this construct:
> You count all rows just to test whether the table is empty.
> It's a bit like counting stars when the question is whether the night is
> clear. If you speed this up using an EXISTS then maybe you can drive teh
> logic thorugh your app without much problem. You can use a high
> isolation level if you want to make sure noone sneaks in a row.
>
> Cheers
> Serge
>
>
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

#4 (**permalink**) 02-27-2008, 04:58 AM

hikums@gmail.com wrote:
> Thanks Serge for your tip.
>
> Correction: The import/load into is not into table1(T1) but into
> table2(T2). Is there no option from task center or some script to
> execute this kind of a logic?
>
> I want to check a table say T1 if it contains records, and if so, then
> delete rows in the huge table say T2(I wanted to accomplish this
> quicker by load replace with empty file or with "alter table T2
> activate not logged initially with empty table"). Then I want to insert
> records from T3 to T2.
Ah.. so in other words this is already teh nth interation of spinning
your tires in the sand ;-)
Let's back up and start this from scratch:
1. You cannot do DDL (such as ALTER TABLE in a standalone BEGIN ATOMIC)
2. Knowing that you CAN do ALTER TABLE in an SQL Procedure
using dynamic SQL (Is that the missing piece??).

So:

--#SET TERMINATOR @
CREATE PROCEDURE proc()
BEGIN
DECLARE alterstr VARCHAR(1000);
IF EXISTS(SELECT 1 FROM T) THEN
SET alterstr = 'ALTER TABLE S ACTIVATE NOT LOGGED '
||'INITIALLY WITH EMPTY TABLE';
EXECUTE IMMEDIATE alterstr;
INSERT INTO S SELECT * FROM T;
COMMIT;
END IF;
END
@
--#SET TERMINATOR ;

Now if you want to do a LOAD instead of a NOT LOGGED INSERT you will
need to write a C Procedure and use the LAOD API.
Eventually DB2 will support LOAD through the admin procedure....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

#5 (**permalink**) 02-27-2008, 04:58 AM

import ###into#### c:\tmptyfile.txt of del replace into claims.table1;

it's "from"

#6 (**permalink**) 02-27-2008, 05:00 AM

Serge Rielau wrote:

> Now if you want to do a LOAD instead of a NOT LOGGED INSERT you will
> need to write a C Procedure and use the LAOD API.
> Eventually DB2 will support LOAD through the admin procedure....

And to make that part easier, have a look at the truncate procedure here:
http://www.ibm.com/developerworks/db...ein/index.html

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

load in an atomic statement -anyone resolved this??