load data with admin_cmd()

[joachim.c.mueller]

Hello,

is it not possible to load data with admin_cmd into a table?

This SP ended with SQL3304N The table does not exist.:

CREATE PROCEDURE ADM_LOAD ( )
DYNAMIC RESULT SETS 1

-- SQL Stored Procedure
P1: BEGIN

DECLARE GLOBAL TEMPORARY TABLE NEW
(TABLE_SCHEMA VARCHAR(128) NOT NULL,
TABLE_NAME VARCHAR(128) NOT NULL)
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK PRESERVE ROWS
WITH REPLACE;

CALL SYSPROC.ADMIN_CMD('LOAD FROM "/db2/db2crm/test" OF DEL METHOD P
(1, 2) INSERT INTO SESSION.NEW (TABLE_SCHEMA, TABLE_NAME)
NONRECOVERABLE');

BEGIN
DECLARE cur CURSOR WITH RETURN TO CALLER
FOR SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM SESSION.NEW;
OPEN cur;
END;

END P1

Best regards,
Joachim Müller

[Ian]

jo***************@googlemail.com wrote:

Hello,

is it not possible to load data with admin_cmd into a table?

This SP ended with SQL3304N The table does not exist.:

CREATE PROCEDURE ADM_LOAD ( )
DYNAMIC RESULT SETS 1

-- SQL Stored Procedure
P1: BEGIN

DECLARE GLOBAL TEMPORARY TABLE NEW
(TABLE_SCHEMA VARCHAR(128) NOT NULL,
TABLE_NAME VARCHAR(128) NOT NULL)
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK PRESERVE ROWS
WITH REPLACE;

CALL SYSPROC.ADMIN_CMD('LOAD FROM "/db2/db2crm/test" OF DEL METHOD P
(1, 2) INSERT INTO SESSION.NEW (TABLE_SCHEMA, TABLE_NAME)
NONRECOVERABLE');

You don't say what version of DB2 you're running, but I'll assume it's
at least 9.1.

The issue not that you are trying to use LOAD via ADMIN_CMD: It's that
LOAD does not work with declared global temporary tables. FYI, IMPORT
does not support DGTTs, either.

[Serge Rielau]

Ian wrote:

The issue not that you are trying to use LOAD via ADMIN_CMD: It's that
LOAD does not work with declared global temporary tables. FYI, IMPORT
does not support DGTTs, either.

LOAD builds up it's own connection. Since temps are connection private...

Joachim, we are in process of improving temp table support. If you can
formalize your requirement in an email it will be helpful.

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