truncate tables in stored procs?

[Henry J.]

I know that we can use import or load to truncate tables without
filling up trans log in DB2. Is it possible to do this in a stored
procedure? How about in CLI/C++? My impression is that import/load
are not SQL statements, hence may not be included in stored procs or
called from CLI. Is that correct? I tried to use import/load in a
stored proc and they were rejected as syntax errors.

If it is the case, we'd have only one option, that is, to call the
import/load commands from command line. I'd be surprised if this is
true since it is such a useful feature, and I hope there is a way
around?

Thanks!

[Serge Rielau]

Re: truncate tables in stored procs?

Henry J. wrote:

I know that we can use import or load to truncate tables without
filling up trans log in DB2. Is it possible to do this in a stored
procedure? How about in CLI/C++? My impression is that import/load
are not SQL statements, hence may not be included in stored procs or
called from CLI. Is that correct? I tried to use import/load in a
stored proc and they were rejected as syntax errors.
>
If it is the case, we'd have only one option, that is, to call the
import/load commands from command line. I'd be surprised if this is
true since it is such a useful feature, and I hope there is a way
around?

You can use the ADMIN_CMD() procedure to invoke LOAD or IMPORT through SQL.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Henry J.]

Re: truncate tables in stored procs?

On Oct 13, 2:53 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Henry J. wrote:

I know that we can use import or load to truncate tables without
filling up trans log in DB2.  Is it possible to do this in a stored
procedure?  How about in CLI/C++?   My impression is that import/load
are not SQL statements, hence may not be included in stored procs or
called from CLI.  Is that correct?  I tried to use import/load in a
stored proc and they were rejected as syntax errors.

>

If it is the case, we'd have only one option, that is, to call the
import/load commands from command line.  I'd be surprised if this is
true since it is such a useful feature, and I hope there is a way
around?

>
You can use the ADMIN_CMD() procedure to invoke LOAD or IMPORT through SQL.
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks Serge. Some more questions:

1. Can I do "call admin_cmd()" in CLI/C++ as well?
2. I haven't tried using LOAD to truncate tables. Wonder what's the
best way to use LOAD for this purpose (I mean the syntax)?
3. If a column in the table is served as the foreign key of a child
table, as I know the IMPORT command will not work even if the child
table is empty. Will LOAD work?

Thanks!

[Serge Rielau]

Re: truncate tables in stored procs?

Henry J. wrote:

1. Can I do "call admin_cmd()" in CLI/C++ as well?

Yes, that's the advantage of SQL statements they work everywhere.

2. I haven't tried using LOAD to truncate tables. Wonder what's the
best way to use LOAD for this purpose (I mean the syntax)?

Look for the REPLACE option in the syntax diagram

3. If a column in the table is served as the foreign key of a child
table, as I know the IMPORT command will not work even if the child
table is empty. Will LOAD work?

Yes LOAD will work. You will need to follow up with a SET INTEGRITY
statement.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab