Stored Procedure - Table arguments

[Bruno Almeida]

Hello friends,

DB2 V10.1 LUW

I would like to create a stored procedure using a table as argument. This table will be use as a global temporary table name, as follow:

***@renata-molinaro:$ db2 -tvf p.sql
drop procedure INIT_TEMP_TABLE
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DB2INST1.INIT_TEMP_TABLE" is an undefined name. SQLSTATE=42704

CREATE PROCEDURE INIT_TEMP_TABLE(in TABNAME)

BEGIN

DECLARE GLOBAL TEMPORARY TABLE TABNAME ( MACHINE_ID VARCHAR(24) ) ON COMMIT PRESERVE ROWS IN USER_TEMP;--
CREATE INDEX SESSION.IDXMID ON TABNAME (MACHINE_ID ASC) ALLOW REVERSE SCANS;--

END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "TABNAME" is an undefined name. LINE NUMBER=1. SQ/LSTATE=42704

Yes, I know that there isn't a table called TABNAME in my DB.

My idea is: call apr.init_temp_table (SESSION.TMP_TABLE_ABC)

My application build the temp table's name aleatory, by concurrent problems.

Is there a way to solve this issue?

Thanks in advance.

-B

[CRPence]

<<SNIP>>
CREATE PROCEDURE INIT_TEMP_TABLE(in TABNAME)
BEGIN
DECLARE GLOBAL TEMPORARY TABLE TABNAME ( MACHINE_ID VARCHAR(24) )
ON COMMIT PRESERVE ROWS IN USER_TEMP;--
CREATE INDEX SESSION.IDXMID ON TABNAME (MACHINE_ID ASC)
ALLOW REVERSE SCANS;--
END
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0204N "TABNAME" is an undefined name. LINE NUMBER=1.
SQ/LSTATE=42704
Yes, I know that there isn't a table called TABNAME in my DB.
My idea is: call apr.init_temp_table (SESSION.TMP_TABLE_ABC)
<<SNIP>>
Is there a way to solve this issue?

...

I expect variables are disallowed in that context [i.e. used to
replace directly, an identifier in a statement], even if the input
parameter were properly typed.

The -204 presumably intends to suggest that a User Defined Type named
TABNAME is not found; i.e. the parameter definition is ([usage]
parameter-name data-type) so lacking a third token between the
parentheses, the first argument is interpreted as the parameter-name and
the second argument is interpreted as a date-type specification -- that
the parameter-name is accepted as "IN" is what makes that effect confusing.

What about the following?:

CREATE PROCEDURE INIT_TEMP_TABLE(in TABNAME varchar(256))
BEGIN
declare dynStmt varchar(2000) ;
set dynStmt=
'DECLARE GLOBAL TEMPORARY TABLE '
concat TABNAME concat
' ( MACHINE_ID VARCHAR(24) )
ON COMMIT PRESERVE ROWS IN USER_TEMP';
execute immediate dynStmt ;
set dynStmt=
'CREATE INDEX SESSION.IDXMID ON '
concat TABNAME concat
' (MACHINE_ID ASC)
ALLOW REVERSE SCANS';
END


example invocation:

call init_temp_table('SESSION.TMP_TABLE_ABC')

[Bruno Almeida]

Post by CRPence

<<SNIP>>
CREATE PROCEDURE INIT_TEMP_TABLE(in TABNAME)
BEGIN
DECLARE GLOBAL TEMPORARY TABLE TABNAME ( MACHINE_ID VARCHAR(24) )
ON COMMIT PRESERVE ROWS IN USER_TEMP;--
CREATE INDEX SESSION.IDXMID ON TABNAME (MACHINE_ID ASC)
ALLOW REVERSE SCANS;--
END
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0204N "TABNAME" is an undefined name. LINE NUMBER=1.
SQ/LSTATE=42704
Yes, I know that there isn't a table called TABNAME in my DB.
My idea is: call apr.init_temp_table (SESSION.TMP_TABLE_ABC)
<<SNIP>>
Is there a way to solve this issue?

I expect variables are disallowed in that context [i.e. used to
replace directly, an identifier in a statement], even if the input
parameter were properly typed.
The -204 presumably intends to suggest that a User Defined Type named
TABNAME is not found; i.e. the parameter definition is ([usage]
parameter-name data-type) so lacking a third token between the
parentheses, the first argument is interpreted as the parameter-name and
the second argument is interpreted as a date-type specification -- that
the parameter-name is accepted as "IN" is what makes that effect confusing.
CREATE PROCEDURE INIT_TEMP_TABLE(in TABNAME varchar(256))
BEGIN
declare dynStmt varchar(2000) ;
set dynStmt=
'DECLARE GLOBAL TEMPORARY TABLE '
concat TABNAME concat
' ( MACHINE_ID VARCHAR(24) )
ON COMMIT PRESERVE ROWS IN USER_TEMP';
execute immediate dynStmt ;
set dynStmt=
'CREATE INDEX SESSION.IDXMID ON '
concat TABNAME concat
' (MACHINE_ID ASC)
ALLOW REVERSE SCANS';
END
call init_temp_table('SESSION.TMP_TABLE_ABC')
--
Regards, Chuck

...

Hi Chuck,

Thanks a lot for your tip!

Works fine for me!

Best Regards,
Bruno.