I am trying to do the following from within a procedure.

set vcsql = 'Call SYSPROC.ADMIN_CMD(' || '''load from /dev/null of del
replace into bi.Provisioned_Spam''' || ')';

execute immediate vcsql;

The documentation says that I can use the sysproc.admin_cmd for load,
but when i use the above statement, it throws me an error.

SQL0104N An unexpected token "load" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "DESCRIBE". SQLSTATE=42601

Are you on v8 or v9? ADMIN_CMD supports LOAD in v9, but not in v8.

v8
DESCRIBE
EXPORT
PRUNE HISTORY/LOGFILE
REORG INDEXES/TABLE
RUNSTATS
UPDATE DATABASE CONFIGURATION

v9
ADD CONTACT
ADD CONTACTGROUP
AUTOCONFIGURE
BACKUP - online only
DESCRIBE
DROP CONTACT
DROP CONTACTGROUP
EXPORT
FORCE APPLICATION
IMPORT
INITIALIZE TAPE
LOAD
PRUNE HISTORY/LOGFILE
QUIESCE DATABASE
QUIESCE TABLESPACES FOR TABLE
REDISTRIBUTE
REORG INDEXES/TABLE
RESET ALERT CONFIGURATION
RESET DATABASE CONFIGURATION
RESET DATABASE MANAGER CONFIGURATION
REWIND TAPE
RUNSTATS
SET TAPE POSITION
UNQUIESCE DATABASE
UPDATE ALERT CONFIGURATION
UPDATE CONTACT
UPDATE CONTACTGROUP
UPDATE DATABASE CONFIGURATION
UPDATE DATABASE MANAGER CONFIGURATION
UPDATE HEALTH NOTIFICATION CONTACT LIST
UPDATE HISTORY

--Jeff

chettiar wrote:
> I am trying to do the following from within a procedure.
>
> set vcsql = 'Call SYSPROC.ADMIN_CMD(' || '''load from /dev/null of del
> replace into bi.Provisioned_Spam''' || ')';
>
> execute immediate vcsql;
>
> The documentation says that I can use the sysproc.admin_cmd for load,
> but when i use the above statement, it throws me an error.
>
> SQL0104N An unexpected token "load" was found following
> "BEGIN-OF-STATEMENT".
> Expected tokens may include: "DESCRIBE". SQLSTATE=42601

Thanks for the clarification Jeff. I am still on v8..

I can use the sysproc.db2load procedure to truncate a table from the
command prompt, but when I wrap it within a procedure, I keep getting
errors.

Can someone please help.


jefftyzzer wrote:
> Are you on v8 or v9? ADMIN_CMD supports LOAD in v9, but not in v8.
>
> v8
> DESCRIBE
> EXPORT
> PRUNE HISTORY/LOGFILE
> REORG INDEXES/TABLE
> RUNSTATS
> UPDATE DATABASE CONFIGURATION
>
> v9
> ADD CONTACT
> ADD CONTACTGROUP
> AUTOCONFIGURE
> BACKUP - online only
> DESCRIBE
> DROP CONTACT
> DROP CONTACTGROUP
> EXPORT
> FORCE APPLICATION
> IMPORT
> INITIALIZE TAPE
> LOAD
> PRUNE HISTORY/LOGFILE
> QUIESCE DATABASE
> QUIESCE TABLESPACES FOR TABLE
> REDISTRIBUTE
> REORG INDEXES/TABLE
> RESET ALERT CONFIGURATION
> RESET DATABASE CONFIGURATION
> RESET DATABASE MANAGER CONFIGURATION
> REWIND TAPE
> RUNSTATS
> SET TAPE POSITION
> UNQUIESCE DATABASE
> UPDATE ALERT CONFIGURATION
> UPDATE CONTACT
> UPDATE CONTACTGROUP
> UPDATE DATABASE CONFIGURATION
> UPDATE DATABASE MANAGER CONFIGURATION
> UPDATE HEALTH NOTIFICATION CONTACT LIST
> UPDATE HISTORY
>
> --Jeff
>
> chettiar wrote:
> > I am trying to do the following from within a procedure.
> >
> > set vcsql = 'Call SYSPROC.ADMIN_CMD(' || '''load from /dev/null of del
> > replace into bi.Provisioned_Spam''' || ')';
> >
> > execute immediate vcsql;
> >
> > The documentation says that I can use the sysproc.admin_cmd for load,
> > but when i use the above statement, it throws me an error.
> >
> > SQL0104N An unexpected token "load" was found following
> > "BEGIN-OF-STATEMENT".
> > Expected tokens may include: "DESCRIBE". SQLSTATE=42601

If all you want to do is truncate the table, you can use ALTER TABLE
ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
The only downside of this statement is that you can't do roll forward
recovery through it.
Also you can use IMPORT to truncate.

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/