By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

SQL PL question: Get Diagnostics

P: n/a

I just stumbled on something odd but I'm not sure if it's a bug in DB2 or
something that is "working as designed."

I have a simple SQL PL stored procedure which is doing an "INSERT SELECT..."
(as opposed to an "INSERT... VALUES ...") and I'm doing a GET DIAGNOSTICS
after the insert to ensure that the expected number of rows was inserted.
However, GET DIAGNOSTICS is reporting that 0 rows were inserted, regardless
of the SELECT that I use in the INSERT. In other words, even if the SELECT
in the INSERT returns 10 rows, GET DIAGNOSTICS is reporting that 0 rows were
inserted.

Is this the correct behaviour for "INSERT SELECT..."? When I did "INSERT ...
VALUES ...", GET DIAGNOSTICS correctly reported the number of rows inserted,
whether it was 10 or 35 or whatever.

According to the manual:

---
ROW_COUNT

Identifies the number of rows associated with the previous SQL statement. If
the previous SQL statement is a DELETE, INSERT, or UPDATE statement,
ROW_COUNT identifies the number of rows that qualified for the operation. If
the previous statement is a PREPARE statement, ROW_COUNT identifies the
estimated number of result rows in the prepared statement.
---

Here is the relevant code from my procedure:

---
INSERT INTO DEPT_BUDGET (DEPTNO, BUDGET)
SELECT WORKDEPT, (SUM(SALARY) + SUM(BONUS) + SUM(COMM)) * 1.1
FROM EMP
WHERE WORKDEPT IS NOT NULL
GROUP BY WORKDEPT;

set local_number_of_rows_to_insert = 8;
get diagnostics local_rows_inserted = row_count; --determine how many
rows were inserted
if local_rows_inserted <> local_number_of_rows_to_insert then
set local_message_text = rtrim(local_procedure_name) || ' inserted '
|| rtrim(char(local_rows_inserted)) || ' of ' ||
rtrim(char(local_number_of_rows_to_insert)) || ' rows.';
signal sqlstate 'ZZ997' set message_text = local_message_text;
end if;
---

I am using DB2 Personal Edition of Windows/Unix/Linux, version 8.2.1 on
Windows XP.

If I have found a bug, what is the appropriate way to report it?

--
Rhino
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Rhino wrote:
I just stumbled on something odd but I'm not sure if it's a bug in DB2 or
something that is "working as designed."

I have a simple SQL PL stored procedure which is doing an "INSERT SELECT..."
(as opposed to an "INSERT... VALUES ...") and I'm doing a GET DIAGNOSTICS
after the insert to ensure that the expected number of rows was inserted.
However, GET DIAGNOSTICS is reporting that 0 rows were inserted, regardless
of the SELECT that I use in the INSERT. In other words, even if the SELECT
in the INSERT returns 10 rows, GET DIAGNOSTICS is reporting that 0 rows were
inserted.

Is this the correct behaviour for "INSERT SELECT..."? When I did "INSERT ...
VALUES ...", GET DIAGNOSTICS correctly reported the number of rows inserted,
whether it was 10 or 35 or whatever.

According to the manual:

---
ROW_COUNT

Identifies the number of rows associated with the previous SQL statement. If
the previous SQL statement is a DELETE, INSERT, or UPDATE statement,
ROW_COUNT identifies the number of rows that qualified for the operation. If
the previous statement is a PREPARE statement, ROW_COUNT identifies the
estimated number of result rows in the prepared statement.
---

Here is the relevant code from my procedure:

---
INSERT INTO DEPT_BUDGET (DEPTNO, BUDGET)
SELECT WORKDEPT, (SUM(SALARY) + SUM(BONUS) + SUM(COMM)) * 1.1
FROM EMP
WHERE WORKDEPT IS NOT NULL
GROUP BY WORKDEPT;

set local_number_of_rows_to_insert = 8;
get diagnostics local_rows_inserted = row_count; --determine how many
rows were inserted
if local_rows_inserted <> local_number_of_rows_to_insert then
set local_message_text = rtrim(local_procedure_name) || ' inserted '
|| rtrim(char(local_rows_inserted)) || ' of ' ||
rtrim(char(local_number_of_rows_to_insert)) || ' rows.';
signal sqlstate 'ZZ997' set message_text = local_message_text;
end if;
---

I am using DB2 Personal Edition of Windows/Unix/Linux, version 8.2.1 on
Windows XP.

If I have found a bug, what is the appropriate way to report it?

ROW_COUNT reads the SQLCA.ERRD[3] field.
I don't know why it works for the VALUES case, but you have to place the
GET DIAGNOSTICS IMMEDIATELY after the INSERT and NOT after the SET.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3n*************@individual.net...
Rhino wrote:
I just stumbled on something odd but I'm not sure if it's a bug in DB2 or something that is "working as designed."

I have a simple SQL PL stored procedure which is doing an "INSERT SELECT..." (as opposed to an "INSERT... VALUES ...") and I'm doing a GET DIAGNOSTICS after the insert to ensure that the expected number of rows was inserted. However, GET DIAGNOSTICS is reporting that 0 rows were inserted, regardless of the SELECT that I use in the INSERT. In other words, even if the SELECT in the INSERT returns 10 rows, GET DIAGNOSTICS is reporting that 0 rows were inserted.

Is this the correct behaviour for "INSERT SELECT..."? When I did "INSERT .... VALUES ...", GET DIAGNOSTICS correctly reported the number of rows inserted, whether it was 10 or 35 or whatever.

According to the manual:

---
ROW_COUNT

Identifies the number of rows associated with the previous SQL statement. If the previous SQL statement is a DELETE, INSERT, or UPDATE statement,
ROW_COUNT identifies the number of rows that qualified for the operation. If the previous statement is a PREPARE statement, ROW_COUNT identifies the
estimated number of result rows in the prepared statement.
---

Here is the relevant code from my procedure:

---
INSERT INTO DEPT_BUDGET (DEPTNO, BUDGET)
SELECT WORKDEPT, (SUM(SALARY) + SUM(BONUS) + SUM(COMM)) * 1.1
FROM EMP
WHERE WORKDEPT IS NOT NULL
GROUP BY WORKDEPT;

set local_number_of_rows_to_insert = 8;
get diagnostics local_rows_inserted = row_count; --determine how many rows were inserted
if local_rows_inserted <> local_number_of_rows_to_insert then
set local_message_text = rtrim(local_procedure_name) || ' inserted ' || rtrim(char(local_rows_inserted)) || ' of ' ||
rtrim(char(local_number_of_rows_to_insert)) || ' rows.';
signal sqlstate 'ZZ997' set message_text = local_message_text;
end if;
---

I am using DB2 Personal Edition of Windows/Unix/Linux, version 8.2.1 on
Windows XP.

If I have found a bug, what is the appropriate way to report it?

ROW_COUNT reads the SQLCA.ERRD[3] field.
I don't know why it works for the VALUES case, but you have to place the
GET DIAGNOSTICS IMMEDIATELY after the INSERT and NOT after the SET.

Thanks, Serge! I moved the SET and everything worked perfectly. It hadn't
occurred to me that the SET would reset the ROW_COUNT since SET isn't an SQL
statement that touches the database. I actually did the SET before the
INSERT...VALUES.... which explains why I didn't see the problem in that
case.

Rhino
Nov 12 '05 #3

P: n/a
Rhino wrote:
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3n*************@individual.net...
Rhino wrote:
I just stumbled on something odd but I'm not sure if it's a bug in DB2
or
something that is "working as designed."

I have a simple SQL PL stored procedure which is doing an "INSERT
SELECT..."
(as opposed to an "INSERT... VALUES ...") and I'm doing a GET
DIAGNOSTICS
after the insert to ensure that the expected number of rows was
inserted.
However, GET DIAGNOSTICS is reporting that 0 rows were inserted,
regardless
of the SELECT that I use in the INSERT. In other words, even if the
SELECT
in the INSERT returns 10 rows, GET DIAGNOSTICS is reporting that 0 rows
were
inserted.

Is this the correct behaviour for "INSERT SELECT..."? When I did "INSERT
...
VALUES ...", GET DIAGNOSTICS correctly reported the number of rows
inserted,
whether it was 10 or 35 or whatever.

According to the manual:

---
ROW_COUNT

Identifies the number of rows associated with the previous SQL
statement. If
the previous SQL statement is a DELETE, INSERT, or UPDATE statement,
ROW_COUNT identifies the number of rows that qualified for the
operation. If
the previous statement is a PREPARE statement, ROW_COUNT identifies the
estimated number of result rows in the prepared statement.
---

Here is the relevant code from my procedure:

---
INSERT INTO DEPT_BUDGET (DEPTNO, BUDGET)
SELECT WORKDEPT, (SUM(SALARY) + SUM(BONUS) + SUM(COMM)) * 1.1
FROM EMP
WHERE WORKDEPT IS NOT NULL
GROUP BY WORKDEPT;

set local_number_of_rows_to_insert = 8;
get diagnostics local_rows_inserted = row_count; --determine how
many
rows were inserted
if local_rows_inserted <> local_number_of_rows_to_insert then
set local_message_text = rtrim(local_procedure_name) || '
inserted '
|| rtrim(char(local_rows_inserted)) || ' of ' ||
rtrim(char(local_number_of_rows_to_insert)) || ' rows.';
signal sqlstate 'ZZ997' set message_text = local_message_text;
end if;
---

I am using DB2 Personal Edition of Windows/Unix/Linux, version 8.2.1 on
Windows XP.

If I have found a bug, what is the appropriate way to report it?


ROW_COUNT reads the SQLCA.ERRD[3] field.
I don't know why it works for the VALUES case, but you have to place the
GET DIAGNOSTICS IMMEDIATELY after the INSERT and NOT after the SET.


Thanks, Serge! I moved the SET and everything worked perfectly. It hadn't
occurred to me that the SET would reset the ROW_COUNT since SET isn't an SQL
statement that touches the database. I actually did the SET before the
INSERT...VALUES.... which explains why I didn't see the problem in that
case.

Rhino

SET x = (select ....);
SET x = y + z;

You are right that a simpel assignment (usually) doesn't touch the core
SQL engine. But as soon as the right hand side requires SQL semnatics
there will be a section (compiled plan) for it.

Either way SQL/PSM does not distinguish between "touches the DB" or not.
It requires that the diagnostics/SQLCA are reset after each statement
except after GET DIAGNOSTICS itself.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.