Hi all,

I am trying to use IMPORT in ADMIN_CMD in a procedure as shown below

Code:

CREATE TABLE tab1 ( col1 INTEGER NOT NULL PRIMARY KEY, col2 VARCHAR(15) )@

INSERT INTO tab1 VALUES ( 1, 'some data' ), ( 2, NULL )@

--SELECT * FROM tab1 @

CREATE PROCEDURE DELETE_DATA()
SPECIFIC DELETE_DATA
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL
BEGIN ATOMIC

CALL SYSPROC.ADMIN_CMD('IMPORT FROM /dev/null OF IXF
MESSAGES ON SERVER INSERT_UPDATE INTO TAB1');

END

CALL DELETE_DATA()@


I get the following error:

CALL DELETE_DATA()
SQL0104N An unexpected token "IMPORT" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "DESCRIBE".
SQLSTATE=42601

SQL0104N An unexpected token "IMPORT" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "DESCRIBE ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601


Any idea what the problem might be? I ran db2updv8 against the database.

In a nutshell, i want to truncate a table quickly (no logging) and that call should be from a procedure.

Environment: DB2 V8 Fixpak 11 on Solaris 8 box

Thanks in Advance,
Newbie

The answer to your question is in the manual.

try alter table statement wit ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE option

import/export through admin_cmd is supported in DB2 9 . earlier versions have limited functionality

baloo99,
"try alter table statement wit ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE option"

any error between the ALTER and the next COMMIT will cause the table to be placed in DROP PENDING. It is too dangerous.

Any other suggestions?

Thanks rahul_s80, n_i

Thanks,
Newbie

why you cannot commit immediate after alter table?

Because that will switch off the NOT LOGGED state.

Here we described another alternative with your own stored procedure: http://www.ibm.com/developerworks/db...ein/index.html

This may not be an issue in the current scenario because the only goal seems to be to truncate the table. But there will always be the possibility that the client application crashes between the CALL and the COMMIT (or network connection is lost or whatever). The only way to prevent that would be to use auto-commit.

try:

import from /dev/null of del replace into TABNAME

regards,
Harsh Verma