how to capture rows affected via merge command?

[kenfar]

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

[Serge Rielau]

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/

[Serge Rielau]

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/

[kenfar]

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

[Serge Rielau]

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/