Truncate and UDB Version 8
Spencer
anyone have an idea as to when they may?
Spencer
PM (pm3iinc-nospam)
v8.
As you may know, you can achieve the same with (clp):
declare c0 cursor for select * from schema.table where 0=1
open c0
load from c0 of cursor replace into schema.table
You can also export to a file ... where 0=1 and import-replace, etc.
And why not api's...
PM
"Spencer" <spe...@tabbert.net> a écrit dans le message de news:
57dd3c13.02111...@posting.google.com...
Kevin Gashyna
is the link to the documentation:
SQL Reference
TRUNCATE or TRUNC
>>-+-TRUNCATE-+--(--expression--,--expression--)---------------><
'-TRUNC----'
The schema is SYSFUN.
Returns argument1 truncated to argument2 places right of decimal point.
If argument2 is negative, argument1 is truncated to the absolute value
of argument2 places to the left of the decimal point.
The first argument can be any built-in numeric data type. The second
argument has to be an INTEGER or SMALLINT. DECIMAL and REAL are
converted to double-precision floating-point number for processing by
the function.
The result of the function is:
INTEGER if the first argument is INTEGER or SMALLINT
BIGINT if the first argument is BIGINT
DOUBLE if the first argument is DOUBLE, DECIMAL or DOUBLE.
The result can be null; if any argument is null, the result is the null
value.
Swaminaathan
PM : My question
a. How does TRUNCATE equate to select * from schema.table where 0=1
(does this select not return 0 rows)
b. shld we close the cursor after load statement??
c. By your example, can we then use the below command in the pgm? For
what it would be useful to load a table using cursor?
Thanks.
boco...@ca.ibm.com
Swaminaathan wrote:
It loads zero (0) rows - and, since the example from the prev poster is doing a LOAD REPLACE, the end result is a truncated
table.
>
>
> b. shld we close the cursor after load statement??
No, LOAD will close -- matter of fact, it is not necessary to open; but not wrong.
>
>
> c. By your example, can we then use the below command in the pgm? For
> what it would be useful to load a table using cursor?
db2 => declare c1 cursor for select * from t2 where 1=0
DB20000I The SQL command completed successfully.
db2 => load from c1 of cursor replace into t2
....
You don't need an empty file to load from (as shown above). As the other posters indicated, you can of course do a load/import
replace from an empty file too :)
Knut Stolze
> Truncate currently available in version 7 under the SYSFUN schema. Here
> is the link to the documentation:
>
>
http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7s0sqls0495.htm#HDRFNTRUNC
>
>
> SQL Reference
>
> TRUNCATE or TRUNC
>>>-+-TRUNCATE-+--(--expression--,--expression--)---------------><
> '-TRUNC----'
>
> The schema is SYSFUN.
[...]
The original poster wanted to truncato an entire table. This function works
only on a single string. Both are two very different things.
--
Knut Stolze
DB2 Spatial Extender
IBM Germany / University of Jena
Sean C.
> I think that load-replace, import-replace is still the suggested method for
> v8.
>
> As you may know, you can achieve the same with (clp):
> declare c0 cursor for select * from schema.table where 0=1
> open c0
> load from c0 of cursor replace into schema.table
>
> You can also export to a file ... where 0=1 and import-replace, etc.
> And why not api's...
>
> PM
PM:
This seems very handy, but I could not get it to work on UDB 7.2
(Windows NT), from a clp session. The LOAD command returned:
Z:\>db2 load from c0 of cursor replace into schema.table
SQL0104N An unexpected token "cursor" was found following "OF".
Expected
tokens may include: "ASC". SQLSTATE=42601
Is this a platform-specific feature of LOAD?
PM (pm3iinc-nospam)
for v7.x, use the export method first.
db2 export to filename of del select * from tablename where 0=1
db2 load from filename of del replace into tablename ...
I tried something (on a v8.1B) and it seems to also work. (should work on
v7.x also)
db2 load from nul of del replace into t1
PM
Spencer
to easily truncate a table or other non logged delete statement.
Spencer
Knut Stolze <sto...@us.ibm.com> wrote in message news:<ar28lt$1eu$1...@fsuj29.rz.uni-jena.de>...
Blair Kenneth Adamache
import from c:\ixf\rep.del of del replace into testvar
IMPORT is usually the best choice, because it does not lock the table space. If the table has many active pages in the
bufferpool, LOAD will be faster, as IMPORT will flush the bufferpool. LOAD must be used if the target table has Referential
Integrity dependencies or summary tables defined on it. In Version 7 and earlier, LOAD requires exclusive access to the table
space. If you plan to use LOAD a lot, put each table that gets LOADed (or uses LOAD to be emptied) in its own table space, or
use IMPORT REPLACE.
Blair Kenneth Adamache
lsu...@mb.sympatico.ca
> Yes I am on a quest on to find out why DB2 has not allowed the ability
> to easily truncate a table or other non logged delete statement.
> Spencer
>
Create the table with "not logged initially" and when you want to do a
"non logged delete" use the alter table statement to enable the "not
logged" and then do the delete
Lorne
--
Lorne Sunley