Hi friends,

DB2 LUW 10.1

I'm trying to execute a procedure on CLP, but there is a syntax error that I was unable to solve.

$ db2 -tvf proc.sql
drop procedure APR.PURGE_TABLE_INFO_TMT
DB20000I The SQL command completed successfully.

CREATE PROCEDURE APR.PURGE_TABLE_INFO_TMT (IN TABLE_NAME varchar(64), IN CHUNK smallint, IN MID varchar(24), IN FIRST_TMT timestamp, IN SECOND_TMT timestamp)
BEGIN
DECLARE SQLCODE INTEGER;--
declare rcount integer;--
declare v_dyn_SQL varchar(200);--
declare stmt_delete statement;--

--version1.0

set v_dyn_SQL = 'delete from (select 1 from ' || TABLE_NAME || ' where MACHINE_ID=''' || MID || ''' and COLLECT_TIME between ''' || varchar(FIRST_TMT) || ''' and ''' || varchar(SECOND_TMT) || ''' FETCH FIRST ' || char(CHUNK) || ' rows only) as D';--

prepare stmt_delete from v_dyn_SQL;--

loop: LOOP
execute stmt_delete;--
get diagnostics rcount = ROW_COUNT;--
IF (SQLCODE = 100 OR RCOUNT < CHUNK) THEN LEAVE loop;--
END IF;--
COMMIT;--
END LOOP loop;--
END

DB20000I The SQL command completed successfully.


$ db2 "call APR.PURGE_TABLE_INFO_TMT ('schema.tabname', 2000, '080027935001DCAD27681116', '2016-09-01-16.00.00.000000', '2016-09-01-16.59.00.000000')"
SQL0104N An unexpected token "FETCH" was found following "-01-16.59.00.000000'". Expected tokens may include: ")". SQLSTATE=42601


When I run this delete statement on CLP, there is no error:

$ db2 "delete from (select 1 from schema.tabname where machine_id = '080027935001DCAD27681116' and collect_time between '2016-09-01-15.00.00.000000' and '2016-09-01-15.59.00.00000' fetch first 2000 rows only) as D "
DB20000I The SQL command completed successfully.

Can you help me with this issue?

Thanks in advance.

Bruno.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Bruno,

It's a trick question, right? To see if we're still all awake on Friday (NZ time)? :-)

Seriously, the text of the delete statement that you have posted is 192 characters *excluding* the schema dot table name, so if your actual schema.tabname name exceeds 8 characters then the value of v_dyn_SQL will be truncated. And you don't always get
very helpful syntax errors from the compiler when it is confused.

Can you try again with v_dyn_SQL declared with a larger size?

If that still fails then maybe output the constructed SQL for checking.

Regards, Jeremy


On Friday, 9 September 2016 06:16:51 UTC+12, Bruno Almeida wrote:

declare v_dyn_SQL varchar(200);--

set v_dyn_SQL = 'delete from (select 1 from ' || TABLE_NAME || ' where MACHINE_ID=''' || MID || ''' and COLLECT_TIME between ''' || varchar(FIRST_TMT) || ''' and ''' || varchar(SECOND_TMT) || ''' FETCH FIRST ' || char(CHUNK) || ' rows only) as D';--

$ db2 "call APR.PURGE_TABLE_INFO_TMT ('schema.tabname', 2000, '080027935001DCAD27681116', '2016-09-01-16.00.00.000000', '2016-09-01-16.59.00.000000')"
SQL0104N An unexpected token "FETCH" was found following "-01-16.59.00.000000'". Expected tokens may include: ")". SQLSTATE=42601

When I run this delete statement on CLP, there is no error:

$ db2 "delete from (select 1 from schema.tabname where machine_id = '080027935001DCAD27681116' and collect_time between '2016-09-01-15.00.00.000000' and '2016-09-01-15.59.00.00000' fetch first 2000 rows only) as D "
DB20000I The SQL command completed successfully.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Jeremy.

Yeap, you know, 24hs on air :)

Yes, when I extended the dynamic query size, the procedure works fine!

Thanks for your reply.

Bruno.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 08-Sep-2016 11:16 -0600, Bruno Almeida wrote:

<<SNIP>>

set v_dyn_SQL = 'delete from (select 1 from ' || TABLE_NAME || '
where MACHINE_ID=''' || MID || ''' and COLLECT_TIME between ''' || varchar(FIRST_TMT) || ''' and ''' || varchar(SECOND_TMT) || '''
FETCH FIRST ' || char(CHUNK) || ' rows only) as D';--

<<SNIP>>

FWiW, a revision offered as possible alternative formatting; although
unsure how accurate is this [untested] revision to the above, and per additional spacing for the particular formatting shown, may require an
even longer declaration for v_dyn_SQL:

declare Q varchar(1) default '''' ; -- escape apostrophe just once

set v_dyn_SQL =
'delete from '
|| ' ( select 1 '
|| ' from ' || TABLE_NAME
|| ' where MACHINE_ID = ' || Q || MID || Q
|| ' and COLLECT_TIME between '
|| Q || varchar(FIRST_TMT) || Q
|| ' and '
|| Q || varchar(SECOND_TMT) || Q
|| ' FETCH FIRST ' || char(CHUNK) || ' rows only'
|| ' ) as D'
;

--
Regards, Chuck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, September 8, 2016 at 3:16:51 PM UTC-3, Bruno Almeida wrote:

Hi friends,

DB2 LUW 10.1

I'm trying to execute a procedure on CLP, but there is a syntax error that I was unable to solve.

$ db2 -tvf proc.sql
drop procedure APR.PURGE_TABLE_INFO_TMT
DB20000I The SQL command completed successfully.

CREATE PROCEDURE APR.PURGE_TABLE_INFO_TMT (IN TABLE_NAME varchar(64), IN CHUNK smallint, IN MID varchar(24), IN FIRST_TMT timestamp, IN SECOND_TMT timestamp)
BEGIN
DECLARE SQLCODE INTEGER;--
declare rcount integer;--
declare v_dyn_SQL varchar(200);--
declare stmt_delete statement;--

--version1.0

set v_dyn_SQL = 'delete from (select 1 from ' || TABLE_NAME || ' where MACHINE_ID=''' || MID || ''' and COLLECT_TIME between ''' || varchar(FIRST_TMT) || ''' and ''' || varchar(SECOND_TMT) || ''' FETCH FIRST ' || char(CHUNK) || ' rows only) as D';--

prepare stmt_delete from v_dyn_SQL;--

loop: LOOP
execute stmt_delete;--
get diagnostics rcount = ROW_COUNT;--
IF (SQLCODE = 100 OR RCOUNT < CHUNK) THEN LEAVE loop;--
END IF;--
COMMIT;--
END LOOP loop;--
END

DB20000I The SQL command completed successfully.

$ db2 "call APR.PURGE_TABLE_INFO_TMT ('schema.tabname', 2000, '080027935001DCAD27681116', '2016-09-01-16.00.00.000000', '2016-09-01-16.59.00.000000')"
SQL0104N An unexpected token "FETCH" was found following "-01-16.59.00.000000'". Expected tokens may include: ")". SQLSTATE=42601

When I run this delete statement on CLP, there is no error:

$ db2 "delete from (select 1 from schema.tabname where machine_id = '080027935001DCAD27681116' and collect_time between '2016-09-01-15.00.00.000000' and '2016-09-01-15.59.00.00000' fetch first 2000 rows only) as D "
DB20000I The SQL command completed successfully.

Can you help me with this issue?

Thanks in advance.

Bruno.

I found out the error reason.

v_dyn_SQL varchar(200) is too short. I extended to varchar(300) and now works fine.

$ db2 -tvf proc.sql
drop procedure APR.PURGE_TABLE_INFO_TMT
DB20000I The SQL command completed successfully.

CREATE PROCEDURE APR.PURGE_TABLE_INFO_TMT (IN TABLE_NAME varchar(64), IN CHUNK smallint, IN MID varchar(24), IN FIRST_TMT timestamp, IN SECOND_TMT timestamp)
BEGIN
DECLARE SQLCODE INTEGER;--
declare rcount integer;--
declare v_dyn_SQL varchar(300);--
declare stmt_delete statement;--

--version1.0

set v_dyn_SQL = 'delete from (select 1 from ' || TABLE_NAME || ' where MACHINE_ID=''' || MID || ''' and COLLECT_TIME between ''' || varchar(FIRST_TMT) || ''' and ''' || varchar(SECOND_TMT) || ''' FETCH FIRST ' || char(CHUNK) || ' rows only) as D';--

prepare stmt_delete from v_dyn_SQL;--

loop: LOOP
execute stmt_delete;--
get diagnostics rcount = ROW_COUNT;--
IF (SQLCODE = 100 OR RCOUNT < CHUNK) THEN LEAVE loop;--
END IF;--
COMMIT;--
END LOOP loop;--
END

DB20000I The SQL command completed successfully.


$ db2 "call APR.PURGE_TABLE_INFO_TMT ('schema.table', 2000, '080027935001DCAD27681116', '2016-09-01-16.00.00.000000', '2016-09-01-16.59.00.000000')"

Return Status = 0

Cheers.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)