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

how to capture rows affected via merge command?

P: n/a
I've got a batch etl process in which I typically wrap DML with selects
in order to capture the number of rows affected. For example:
SELECT 'rows updated', COUNT(*)
FROM NEW TABLE
( UPDATE stage_asset
SET ip_int = ip_to_bigint(ip_string)
WHERE ip_int IS NULL )

How can I do this for a merge? For example:

MERGE INTO asset_component comp
USING stage_asset stag
ON comp.asset_id = stag.core_asset_id
AND comp.asset_component_type = 'os'
WHEN MATCHED THEN
UPDATE SET
comp.os_name = stag.core_os_name ,

comp.os_id = stag.core_os_id
,
comp.asset_component_name = stag.core_os_name
WHEN NOT MATCHED THEN
INSERT (asset_id,
asset_component_type,
asset_component_name,
os_name,
os_id )
VALUES (stag.core_asset_id,
'os',
stag.core_os_name,
stag.core_os_id )

Thanks in advance!

Ken

Sep 4 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
kenfar wrote:
I've got a batch etl process in which I typically wrap DML with selects
in order to capture the number of rows affected. For example:
SELECT 'rows updated', COUNT(*)
FROM NEW TABLE
( UPDATE stage_asset
SET ip_int = ip_to_bigint(ip_string)
WHERE ip_int IS NULL )

How can I do this for a merge? For example:

MERGE INTO asset_component comp
USING stage_asset stag
ON comp.asset_id = stag.core_asset_id
AND comp.asset_component_type = 'os'
WHEN MATCHED THEN
UPDATE SET
comp.os_name = stag.core_os_name ,

comp.os_id = stag.core_os_id
,
comp.asset_component_name = stag.core_os_name
WHEN NOT MATCHED THEN
INSERT (asset_id,
asset_component_type,
asset_component_name,
os_name,
os_id )
VALUES (stag.core_asset_id,
'os',
stag.core_os_name,
stag.core_os_id )
Unfortunately no can do.
FWIW, SELECT FROM MERGE is on my personal wish-list too.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 4 '06 #2

P: n/a
Serge Rielau wrote:
kenfar wrote:
>I've got a batch etl process in which I typically wrap DML with selects
in order to capture the number of rows affected. For example:
SELECT 'rows updated', COUNT(*)
FROM NEW TABLE
( UPDATE stage_asset
SET ip_int = ip_to_bigint(ip_string)
WHERE ip_int IS NULL )

How can I do this for a merge? For example:

MERGE INTO asset_component comp
USING stage_asset stag
ON comp.asset_id = stag.core_asset_id
AND comp.asset_component_type = 'os'
WHEN MATCHED THEN
UPDATE SET
comp.os_name = stag.core_os_name ,

comp.os_id = stag.core_os_id
,
comp.asset_component_name = stag.core_os_name
WHEN NOT MATCHED THEN
INSERT (asset_id,
asset_component_type,
asset_component_name,
os_name,
os_id )
VALUES (stag.core_asset_id,
'os',
stag.core_os_name,
stag.core_os_id )
Unfortunately no can do.
FWIW, SELECT FROM MERGE is on my personal wish-list too.
BTW, you are aware that you can retrieve the rows affected from the
SQLCA.ERRD[3] or in SQL PL: GET DIAGNOSTICS rcnt = ROW_COUNT

And that, of course, works also with MERGE
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 4 '06 #3

P: n/a
Serge Rielau wrote:
BTW, you are aware that you can retrieve the rows affected from the
SQLCA.ERRD[3] or in SQL PL: GET DIAGNOSTICS rcnt = ROW_COUNT

And that, of course, works also with MERGE
Thanks Serge. I'm piloting this process within a bash shell, and so
calling it like this:
sql="SELECT 'rows updated: ', COUNT(*) \
FROM NEW TABLE \
( UPDATE core.stage_asset \
SET core_org_id = $org_id \
WHERE LOWER(account) = '$account' ) "
db2 -x "$sql"
rc=$?
if [ "$rc" != "0" ]; then
abort $rc
fi

Is there a way to get to the SQLCA from bash (or any shell
interpreter)?

Thanks again,

Ken

Sep 4 '06 #4

P: n/a
kenfar wrote:
Serge Rielau wrote:
>BTW, you are aware that you can retrieve the rows affected from the
SQLCA.ERRD[3] or in SQL PL: GET DIAGNOSTICS rcnt = ROW_COUNT

And that, of course, works also with MERGE

Thanks Serge. I'm piloting this process within a bash shell, and so
calling it like this:
sql="SELECT 'rows updated: ', COUNT(*) \
FROM NEW TABLE \
( UPDATE core.stage_asset \
SET core_org_id = $org_id \
WHERE LOWER(account) = '$account' ) "
db2 -x "$sql"
rc=$?
if [ "$rc" != "0" ]; then
abort $rc
fi

Is there a way to get to the SQLCA from bash (or any shell
interpreter)?
Try the -a option.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 4 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.