473,239 Members | 1,723 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

SQL PL question: Get Diagnostics


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
3 2523
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

"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Steven | last post by:
I just downloaded the June 2005 release and I want to set up basic logging to log problems such as exceptions. I have created a category named Exceptions with the following: <category...
7
by: Yoshi | last post by:
Hi everyone! I am developing an application that runs a seperate executiblie file, calls the SetParent API so the application is contained in my application. The problem that I am having is I...
18
by: Nick Z. | last post by:
I am writing a reusable class for logging. My goal is to make it as fast and as robust as possible, while keeping memory usage to the lowest. All members are static. For some reason I'm stuck on...
4
by: paulrosenthal | last post by:
Hello, I am building a program and I have a question. One of the features is sort of a quick version to get to a website. They will have a text box, which they put in the address. They will then...
1
by: Patrick | last post by:
When Tracing in ASP.NET, the IIS process (on IIs5.1) is locking on the Trace file, and I can't read the trace file without restarting the IIS: Even the following does NOT work (how could I fix...
2
by: topgene | last post by:
question describe: i used C# write the follow method: -------------------------------------------------------------------------------- public static void GetMp3FileFromAudio(string...
0
by: ChopStickr | last post by:
I have a custom control that is embedded (using the object tag) in an html document. The control takes a path to a local client ini file. Reads the file. Executes the program specified in...
3
by: QDL | last post by:
Hello everyone, I have a very simple question about arrays I have an array of Processes objects (retrieved using Process.GetProcesses()). I want to sort them descending on the WorkingSet size. ...
2
by: Dennis | last post by:
I have a service with the following startup code... When I start the service I see a new entry under... Computer Management(local) System Tools Event Viewer Application MyServiceLog ...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.