Chapter 2. Financial services business scenario 61
(SELECT A.AMOUNT, A.TRANDATE AS TRANDATE, 'ODS' AS SOURCE
FROM ODS.CARDTRANSACTIONS A, ODS.CARDDETAILS B, CUST
WHERE A.CARDDETAILSFK=B.CARDNUMBER AND B.CARDCUSTID=CUST.CCCUSTID)
UNION
(SELECT A.AMOUNT, A.TRANDATE AS TRANDATE, 'EDW' AS SOURCE
FROM EDW.CARDTRANSACTIONS A, EDW.CARDDETAILS B, CUST
WHERE A.CARDDETAILSFK=B.CARDNUMBER AND B.CARDCUSTID=CUST.CCCUSTID)
2.6.3 Create stored procedures
An authorization service needed to be created for the self service solution to
validate a customer logging in with a social security number. We chose to write
an SQL stored procedure (shown in Example 2-6 on page 61) to perform this
function, and use IBM Information Server to generate an SOA service of it.
Example 2-6 SQL stored procedure authorizeaction
CREATE PROCEDURE feddb.authorizeaction(IN p_ssnumb VARCHAR(13),
IN action VARCHAR(40),
OUT AUTHORIZED INTEGER)
LANGUAGE SQL
P1: BEGIN
DECLARE CUSTID VARCHAR(20) DEFAULT '--';
SET AUTHORIZED = 0;
IF SUBSTR(ACTION, 1, 9) = 'Brokerage' THEN
SELECT B.CUSTID INTO CUSTID FROM BROKERAGE.CUSTOMER AS B WHERE B.SSNUMB =
p_ssnumb;
IF CUSTID<>'--' THEN
SET AUTHORIZED = 1;
END IF;
END IF;
IF ACTION = 'CreditCard' THEN
SELECT C.CUSTID INTO CUSTID FROM CCA.CUSTOMER AS C WHERE C.SSNUMB = p_ssnumb;
IF CUSTID<>'--' THEN
SET AUTHORIZED = 1;
Attention: In the real world, it is highly unlikely that you would implement an
authorization service using a stored procedure. We did so for expedience.

Get SOA Solutions Using IBM Information Server now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.