IBM Support

Return ROW_COUNT on CREATE TABLE and DECLARE GLOBAL TEMPORARY TABLE statements WITH DATA

News


Abstract

Return ROW_COUNT on CREATE TABLE and DECLARE GLOBAL TEMPORARY TABLE statements WITH DATA

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > Return ROW_COUNT on CREATE TABLE and DECLARE GLOBAL TEMPORARY TABLE statements WITH DATA

When the WITH DATA clause is used to insert rows into the new table, the application will be able to retrieve the number of rows inserted in two ways:
  1. By using the GET DIAGNOSTICS statement and extracting the ROW_COUNT.
  2. By directly accessing the SQLERRD3 field within the SQLCA.
CREATE PROCEDURE PRODLIB.CreateSalesTable(out rowCount integer)
LANGUAGE SQL
BEGIN
/*
Build a temporary table with all the sales transactions from the current quarter.
Return the number of rows in the temporary table to the caller.
*/
DECLARE GLOBAL TEMPORARY TABLE SESSION.QuarterlySales as
(SELECT * FROM PRODLIB.SALES WHERE
QUARTER(SALES_DATE) = QUARTER(CURRENT DATE) AND
YEAR(SALES_DATE) = YEAR(CURRENT DATE) ) WITH DATA WITH REPLACE;
GET DIAGNOSTICS rowCount = ROW_COUNT;
END

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167694