Troubleshooting Reference: Database Queries

Information about some database queries that you can use for gathering additional information that cannot be obtained by the Policy Manager Workbench.

Table of Contents

Overview

This section provides information about some database queries that you can use for gathering additional information that cannot be obtained by Workbench.

You can use your preferred database client, such as TOAD, to run these queries.

Note: These database queries are all available in the Akana Knowledge Base (see Knowledge Base).

Query: Find Consumed Named Contracts Attached at all Levels

Summary:

An organization can have a parent organization and can also have a grandparent organization that serves as an attachment point for a contract to their service. Because of this, when looking for a named contract it is necessary to make sure all parent /grandparent organizations are checked. A query for the named contract for a service will search for four different contracts:

  • Contract attached at org level
  • Contract attached at parent-grandparent org level
  • Contract attached at service level
  • Contract attached at operation level

Only when all four contracts are accounted for will this query return a correct result. This query will not take into account anonymous contracts or deactivated contracts.

The query must be run as a script. Temporary tables are created only for the duration of the query.

Query Syntax for Database:

Oracle

Query:

CREATE TABLE PARENT_ORGS(CHILD_ORG number(38),PARENT_ORG number(38));
CREATE TABLE QUERY_RESULTS( CONTRACTNAME varchar2(512), CONTRACTVERSIONKEY varchar2(64)); DECLARE
var_child_org number; service_uddi varchar2(255); BEGIN
DELETE PARENT_ORGS;
DELETE QUERY_RESULTS;
service_uddi := 'uddi:83e6a3a6-9caa-11e2-8723-c48ae6547392';
SELECT UB.BUSINESS_ENTITY_ID INTO var_child_org FROM uddi_business ub, uddi_service us
WHERE UB.BUSINESS_ENTITY_ID = US.BUSINESS_ENTITY_ID
and US.SERVICE_KEY = service_uddi; INSERT INTO parent_orgs
WITH parent_org_recursion (TO_BUSINESS_ID, FROM_BUSINESS_ID) AS
(
SELECT TO_BUSINESS_ID, FROM_BUSINESS_ID FROM UDDI_PUB_ASSERTION
WHERE TO_BUSINESS_ID = var_child_org
UNION ALL
SELECT upa.TO_BUSINESS_ID, upa.FROM_BUSINESS_ID FROM UDDI_PUB_ASSERTION upa
INNER JOIN parent_org_recursion p ON p.FROM_BUSINESS_ID = upa.TO_BUSINESS_ID
)
SELECT * FROM parent_org_recursion; WHILE var_child_org != '1003'
LOOP
INSERT INTO QUERY_RESULTS (contractname, contractversionkey)
SELECT cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY" from uddi_service us,
uddi_business ub join uddi_pub_assertion upa on UB.BUSINESS_ENTITY_ID = upa.TO_BUSINESS_ID
join resourceset_orgs rsv on UPA.FROM_BUSINESS_ID = RSV.ORGANIZATIONID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where UB.BUSINESS_ENTITY_ID = var_child_org and US.SERVICE_KEY = service_uddi
and CV.ACTIVE = 'Y';
SELECT P.PARENT_ORG INTO var_child_org
FROM PARENT_ORGS p
WHERE P.CHILD_ORG = var_child_org; END LOOP;
INSERT INTO QUERY_RESULTS
(select cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY"
from uddi_business ub, uddi_service us join svc_operations so on US.BUSINES_SERVICE_ID = SO.SERVICEID
join resourceset_svcops rsv on SO.OPERATIONID = RSV.OPERATIONID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where us.service_key = service_uddi and CV.ACTIVE = 'Y'
)
union
(select cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY" from uddi_business ub, uddi_service us join resourceset_svcs rsv on US.BUSINES_SERVICE_ID = RSV.SERVICEID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where us.service_key = service_uddi and CV.ACTIVE = 'Y'
)
union
(select cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY" from uddi_service us, uddi_business ub join resourceset_orgs rsv on UB.BUSINESS_ENTITY_ID = RSV.ORGANIZATIONID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where UB.BUSINESS_ENTITY_ID = US.BUSINESS_ENTITY_ID
and US.SERVICE_KEY = service_uddi and CV.ACTIVE = 'Y'
);
End;
/
SELECT substr(CONTRACTNAME, 1, 64) as CONTRACTNAME, CONTRACTVERSIONKEY FROM QUERY_RESULTS;
DROP TABLE PARENT_ORGS;
DROP TABLE QUERY_RESULTS;

Query Sample Results:

CONTRACTNAME            CONTRACTVERSIONKEY
TEST_CONTRACT           cbc429bf-9caa-11e2-8723-c48ae6547392:2871
AnonymousForPing        829a5dc6-7d22-11e1-8e42-c6230bd8bd38:2641
TEST_CONTRACT_AMEX      df5e3526-9cab-11e2-8723-c48ae6547392:2911

Query: List All Users and the Organizations They Are Assigned to

Summary:

Returns a list of users and the organizations they are assigned to.

Query Syntax for Database:

Oracle, MySql

Query:

SELECT pu.organizationid, un.name, pu.username, pki.purpose,
OI.organizationid AS assignedorganizationid, una.name AS assignedorganizationname 
FROM pm_pkikeys pki, pm_users pu, uddi_business ub, uddi_name un, uddi_name una, 
ORGANIZATION_IDENTITIES OI
WHERE pu.username like '%' 
AND pu.usersid = pki.usersid 
AND pu.usersid = oi.usersid
AND pu.organizationid = ub.business_entity_id
AND ub.business_entity_id = un.ref_id
AND un.ref_type = 'B'
AND una.ref_id = OI.organizationid

Query Sample Results:

ORGANIZATIONID   NAME       USERNAME     PURPOSE    ASSIGNEDORGANIZATIONID   ASSIGNEDORGANIZATIONNAME
1003             Registry   MS_SSL       Identity   3553                     Total Merchant Services - TMS
1003             Registry   SYS_SSL      Identity   3554                     TSYS Acquiring Solutions
1003             Registry   SYS_Message  Identity   3554                     TSYS Acquiring Solutions

Query: Get Service Usage Data and Contract by Organization

Summary:

Provides usage data for all services under a certain organization as well as the contract they are defined in.

Query Syntax for Database:

Oracle

Query:

select 
r.INTVLSTARTDTS,
org.BUSINESS_KEY, orgn.NAME ORGNAME, s.SERVICE_KEY, sn.NAME SVCNAME,
sum(USAGECOUNT) as TOTALUSAGECOUNT, sum(TOTALRESPTIME)/greatest(sum(USAGECOUNT),1) as
AVGRESPTIME,
sum(r.SUCCESSCOUNT), sum(r.TOTALSUCCESSRESPTIME)/greatest(sum(r.SUCCESSCOUNT),1) as 
AVGSUCCESSRESPTIME, 
sum(r.ERRORCOUNT),sum(r.TOTALERRORRESPTIME)/greatest(sum(r.ERRORCOUNT),1) as 
AVGERRORRESPTIME, V.CONTRACTNAME
from
contracts_versions v, UDDI_BUSINESS org, UDDI_NAME orgn, UDDI_NAME sn, UDDI_SERVICE s 
left outer join
mo_rollupdata r on s.BUSINES_SERVICE_ID=r.SERVICEID
and r.INTVLSTARTDTS >= to_date ('2013-03-01', 'YYYY-MM-DD') 
and r.INTVLSTARTDTS < to_date ('2013-03-15', 'YYYY-MM-DD') 
and s.BUSINES_SERVICE_ID=r.SERVICEID
where
r.contractid != '0'
and V.CONTRACTVERSIONID = r.contractid
and org.BUSINESS_ENTITY_ID=orgn.REF_ID and orgn.REF_TYPE='B' 
and s.BUSINES_SERVICE_ID=sn.REF_ID and sn.REF_TYPE = 'S'
and s.BUSINESS_ENTITY_ID=org.BUSINESS_ENTITY_ID
and (org.business_key = '<ORGANIZATION KEY>' 
or
org.business_key in (
select business_key from uddi_business where business_entity_id in
(select to_business_id from
uddi_pub_assertion where from_business_id in 
(select business_entity_id from uddi_business 
where business_key ='<ORGANIZATION KEY>'))) 
or
org.business_key in (select business_key from uddi_business where business_entity_id in
(select to_business_id from uddi_pub_assertion where from_business_id in 
(select business_entity_id from uddi_business where business_key in 
(select business_key from uddi_business where business_entity_id in 
(select to_business_id from uddi_pub_assertion where from_business_id in 
(select business_entity_id from uddi_business
where business_key ='<ORGANIZATION KEY>'))))))
)
group by (
org.BUSINESS_KEY, s.SERVICE_KEY, orgn.NAME, sn.NAME, r.INTVLSTARTDTS, v.contractname)
order by
r.INTVLSTARTDTS desc, s.SERVICE_KEY;

Query Sample Results:

INTVLSTARTDTS    BUSINESS_KEY ORGNAME    SERVICE_KEY SVCNAME TOTALUSAGECOUNT    AVGRESPONSETIME    SUM(R.SUCCESSCOUNT) AVGSUCCESSRESPTIME    SUM(R.ERRORCOUNT) AVGERRORRESPTIME CONTRACTNAME
3/12/2013 2:11:45 A.M.    uddi:4aec3117-7f8f-11e2-bcd9-da0bd5f63124    A-ACherryl-2013 uddi:ccc2141d-8ab9-11e2-9ffb-b0c496eb5bb2    CustomerProfileService_vs0    2    266    2    266    0
0    CustomerProfile
3/12/2013 2:11:40 A.M.    uddi:4aec3117-7f8f-11e2-bcd9-da0bd5f63124    A-ACherryl-2013 uddi:ccc2141d-8ab9-11e2-9ffb-b0c496eb5bb2    CustomerProfileService_vs0    1    230    1    230    0
0    CustomerProfile

Query: Find All Virtual Services (Keys and IDs)

Summary:

Returns all Virtual Services (keys and IDs) not made internally by Policy Manager.

Query Syntax for Database:

MySql

Query:

SELECT US.BUSINES_SERVICE_ID, US.SERVICE_KEY 
FROM UDDI_SERVICE US
  JOIN UDDI_CATEGORY_BAG UCB on US.BUSINES_SERVICE_ID = UCB.REF_ID
  JOIN UDDI_KEYED_REF UKR on UCB.CATEGORY_BAG_ID = UKR.REF_ID 
WHERE BUSINESS_ENTITY_ID <> 1003 AND 1000 AND 1001
  AND UKR.KEY_NAME = 'Virtual Service'

Query Sample Results:

BUSINES_SERVICE_ID    SERVICE_KEY
20051                 uddi:ef2f9784-cc8b-11e2-97db-b7b05e140af3
20052                 uddi:cf7e1e09-c86f-11e2-9770-e11cbec9bf72
20053                 uddi:3e7eb489-a938-11e2-8756-f454b8fcae25

Query: List All Services and Organizations They Are Attached to

Summary:

Returns a list of service names and keys, along with the name and key of the organization that they belong to.

Query Syntax for Database:

Oracle, MySql

Query:

select a.business_key, c.name as business_name, b.service_key, d.name as service_name 
from uddi_business a, uddi_service b, uddi_name c, uddi_name d
where  a.business_entity_id = b.business_entity_id
 and a.business_entity_id = c.ref_id  and c.ref_type = 'B' 
 and b.busines_service_id = d.ref_id  and d.ref_type = 'S' 
 order by a.business_key

Query Sample Results:

BUSINESS_KEY BUSINESS_NAME    SERVICE_KEY SERVICE_NAME
uddi:0270a056-0d00-11de-bbc6-a429f2cfd8d9    Points.com    uddi:041516da-19ff-11df-88e8- eacc60ee139f    PIE
uddi:02d3c77f-f05a-11de-8495-86276bdad2b4    Communications Utility    uddi:a23e51a8-f577-11de-b142-
dcd7878b3ecc    ContextEngineAPIWebServiceWrapped_vs0
uddi:02d3c77f-f05a-11de-8495-86276bdad2b4    Communications Utility    uddi:2a26423a-f576-11de-b142- dcd7878b3ecc    CMSAPIWebServiceWrapped_vs0

Query: Find Services with Basic Auditing Policy Attached

Summary:

Returns name and key of services with basic auditing policy attached.

Query Syntax for Database:

Oracle, MySql

Query:

SELECT n.Name, s.Service_Key
FROM policies p JOIN policy_attachments pa on p.policyid=pa.policyid
  JOIN uddi_service s on pa.attachpointid=s.busines_service_id
  JOIN uddi_name n on s.busines_service_id = n.ref_id
WHERE p.policykey='BasicAuditing';

Query Sample Results:

NAME                                   SERVICE_KEY
Fee&RatesScheduleProcessService_vs0    uddi:b4ecab31-f2e3-11dd-b97e-f7ac69e24a6c
EBSOutboundXML                         uddi:a6f29370-f3ad-11dd-bbc6-a429f2cfd8d9
EBSOutboundXML_vs0                     uddi:d886af83-f3ad-11dd-bbc6-a429f2cfd8d9

Query: Find Services with No Policies Attached

Summary:

Returns name and key of all services with no policies attached.

Query Syntax for Database:

Oracle, MySql

Query:

SELECT n.Name, s.Service_Key
FROM uddi_service s JOIN uddi_name n on s.busines_service_id = n.ref_id
WHERE s.busines_service_id
  NOT IN(
    SELECT s.Busines_service_id
    FROM policies p JOIN policy_attachments pa on p.policyid=pa.policyid 
    JOIN uddi_service s on pa.attachpointid = s.busines_service_id JOIN 
    uddi_name n on s.busines_service_id = n.ref_id
    WHERE s.business_entity_id != 1000 
    and p.policytype != 'Denial of Service'
  )
and s.business_entity_id != 1000;

Query Sample Results:

NAME                                   SERVICE_KEY
Fee&RatesScheduleProcessService_vs0    uddi:b4ecab31-f2e3-11dd-b97e-f7ac69e24a6c
EBSOutboundXML                         uddi:a6f29370-f3ad-11dd-bbc6-a429f2cfd8d9
EBSOutboundXML_vs0                     uddi:d886af83-f3ad-11dd-bbc6-a429f2cfd8d9

Query: Find All Virtual Services and Their Details

Summary:

Finds all virtual services and associated details.

Query Syntax for Database:

Oracle, MySql

Query:

select
    s.SERVICE_KEY, sn.NAME SVCNAME, orgn.NAME ORGNAME,btkrl.KEY_VALUE LISTENERNAME, c.NAME CONATAINERNAME, c.CONTAINERKEY,ap.ACCESS_URL,
    wb.BINDINGTYPE BINDINGTYPE, wb.NAMESPACEURI BINDINGNAMESPACE, wb.LOCALNAME BINDINGLOCALPART from
    UDDI_SERVICE s, UDDI_NAME sn, WSDL_PORT wp, UDDI_BINDING b, UDDI_ACCESS_POINT ap, UDDI_KEYED_REF btkrc,
    UDDI_KEYED_REF btkrl, UDDI_KEYED_REF_GRP btkrg, UDDI_CATEGORY_BAG btcb, MS_SVCCONTAINER c, WSDL_BINDING wb,
    UDDI_NAME orgn
where
    s.BUSINES_SERVICE_ID in (select SERVICEID from SVC_OPERATIONS where
 OPERATIONTYPE='V')
    and sn.REF_ID = s.BUSINES_SERVICE_ID and sn.REF_TYPE='S'
    and orgn.REF_ID = s.BUSINESS_ENTITY_ID and orgn.REF_TYPE='B' and s.BUSINES_SERVICE_ID=wp.SERVICEID
    and ap.BINDING_TEMPLAT_ID=b.BINDING_TEMPLAT_ID
    and wb.BINDINGID = wp.BINDINGID
    and btkrc.REF_TYPE='K'
    and btkrc.REF_ID=btkrg.KEYED_REF_GROUP_ID
    and btkrl.REF_TYPE='K'
    and btkrl.REF_ID=btkrg.KEYED_REF_GROUP_ID
    and btkrl.KEY_NAME='listenerName'
    and btkrg.CATEGORY_BAG_ID=btcb.CATEGORY_BAG_ID
    and btcb.REF_TYPE='BT'
    and btkrc.KEY_NAME='containerKey'
    and btcb.REF_ID=b.BINDING_TEMPLAT_ID and wp.PORTID = b.BINDING_TEMPLAT_ID and btkrc.KEY_VALUE=c.CONTAINERKEY ;

Query Sample Results:

SERVICE_KEY   SCVNAME CONTAINERKEY BINDINDLOCALPART    ORGNAME ACCESS_URL    LISTENERNAME        CONTAINERNAME BINDINGTYPE    BINDINGNAMESPACE
uddi:b4ecab31-f2e3-11dd-b97e-f7ac69e24a6c    Fee&RatesScheduleProcessService_vs0    FX International
Payments    HTTP Outbound  AXPCluster1    AXPCluster1 http://dwebservices.trcw.us.aexp.com:80/Fee&RatesScheduleProcessService_Dev    binding.soap11 http://soap.sforce.com/schemas/class/feeandrateschedulProcess
feeandrateschedulesProcessBinding
uddi:b4ecab31-f2e3-11dd-b97e-f7ac69e24a6c    Fee&RatesScheduleProcessService_vs0    FX International
Payments    HTTP Outbound  NewCluster2-old  container-11
http://devoutboundhttp.americanexpress.com:80/Fee&RatesScheduleProcessService    binding.soap11 http://soap.sforce.com/schemas/class/feeandrateschedulProcess
feeandrateschedulesProcessBinding
uddi:b4ecab31-f2e3-11dd-b97e-f7ac69e24a6c    Fee&RatesScheduleProcessService_vs0    FX International
Payments    HTTP Outbound

Query: Find Services with SOAP 1.1 and 1.2 Bindings

Summary:

Returns service key and access point of service with SOAP 1.1 and 1.2 bindings, and keeps track of duplicate URLs.

Query Syntax for Database:

Oracle, MySql

Query:

SELECT US.SERVICE_KEY, UAP.ACCESS_URL, COUNT(UAP.ACCESS_URL) AS NUMOCC 
FROM UDDI_SERVICE US LEFT OUTER JOIN WSDL_PORT WP ON WP.SERVICEID = 
US.BUSINES_SERVICE_ID
LEFT OUTER JOIN WSDL_BINDING WB ON WB.BINDINGID = WP.BINDINGID
LEFT OUTER JOIN UDDI_ACCESS_POINT UAP ON UAP.BINDING_TEMPLAT_ID = WP.PORTID 
WHERE (WB.BINDINGTYPE = 'binding.soap11'
OR WB.BINDINGTYPE = 'binding.soap12')
AND UAP.BINDING_TEMPLAT_ID = WP.PORTID 
AND US.SERVICE_KEY NOT LIKE 'uddi:soa.com%' 
GROUP BY US.SERVICE_KEY, UAP.ACCESS_URL 
HAVING (COUNT(UAP.ACCESS_URL)>1);

Query Sample Results:

SERVICE_KEY ACESS_URL    NUMOCC
uddi:dbde605b-de99-11de-842b-a55ca974b1ff    http://192.216.212.94:88/Services/2.3/ClientServices/.asmx    2
uddi:5c90ce40-a9ed-11dd-93e6-8a8ce9206e16    https://xmltest.teletrack.com/inquiry.asmx                    2
uddi:db92077d-e09a-11df-b2af-b32e0ed3c2b7    https://utc.b2b.ihg.com/amex/b2b/xml/2005A/hotels.xml         3

Query: Find Active Contracts Attached at the Organization Level by Service

Summary:

Returns contracts attached at the organization level when given service key.

Query Syntax for Database:

Oracle, MySql

Query:

select cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY" 
from uddi_service us, uddi_business ub join resourceset_orgs rsv on UB.BUSINESS_ENTITY_ID = 
RSV.ORGANIZATIONID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where UB.BUSINESS_ENTITY_ID = US.BUSINESS_ENTITY_ID
and US.SERVICE_KEY = '<SERVICE KEY>' 
and CV.active= 'Y'

Query Sample Results:

CONTRACTNAME    CONTRACTKEY
named_contract_MSU    9750914b-7b91-11e2-bcd9-da0bd5f63124:2792
TEST_CONTRACT_AME df5e3526-9cab-11e2-8723-c48ae6547392:2911

Query: Find Active Contracts Attached at the Service Level of a Service

Summary:

Returns all contracts attached at the service level of a particular service.

Query Syntax for Database:

Oracle, MySql

Query:

select UNIQUE cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY" 
from uddi_business ub, uddi_service us join resourceset_svcs rsv on US.BUSINES_SERVICE_ID = 
RSV.SERVICEID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where us.service_key = '<SERVICE_KEY>' 
and CV.active='Y'

Query Sample Results:

CONTRACTNAME    CONTRACTKEY
ZootService4Gaj bee0b952-afc9-11df-baa6-abc70f628665:2139
ZootService4Gaj bee0b952-afc9-11df-baa6-abc70f628665:2795

Query: Find Active Contracts Attached at the Operation Level by Service

Summary:

Returns all contracts that are attached at the operation level of a service.

Query Syntax for Database:

Oracle, MySql

Query:

select UNIQUE cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY" from 
uddi_business ub, uddi_service us join svc_operations so on US.BUSINES_SERVICE_ID = SO.SERVICEID
join resourceset_svcops rsv on SO.OPERATIONID = RSV.OPERATIONID
join auz_rules ar on RSV.RESOURCESETID = AR.RESOURCESETID
join auz_rulesets ars on ARS.RULESETID = AR.RULESETID
join contracts_rulesets cr on CR.RULESETID = ARS.RULESETID
join contracts_versions cv on CV.CONTRACTVERSIONID = CR.CONTRACTVERSIONID
where us.service_key = '<Service Key>' 
and CV.ACTIVE='Y'

Query Sample Results:

OPERATIONKEY    OPERATIONNAME    CONTRACTVERSIONID CONTRACTNAME
7d0b8bd0-afc9-11df-baa6-abc70f628665    getDecision    2766
ZootDecision2ServiceSFDC_MSU_Salesforce

Query: Find Contracts by Provider Organization

Summary:

Returns the contract name and key of contracts provided by organization key input into the query.

Query Syntax for Database:

Oracle, MySql

Query:

select cv.contractname as "CONTRACTNAME", CV.CONTRACTVERSIONKEY as "CONTRACTKEY"
from uddi_business b join contracts_versions cv on ((cv.providerorgkey = b.business_key) or (cv.consumerorgkey =
b.business_key))
where cv.providerorgkey = '<PROVIDER_ORG_KEY>'

Query Sample Results:

Using Provider key uddi:132614f0-3bbb-11df-88e8-eacc60ee139f:

CONTRACTNAME                          CONTRACTKEY
ZootService4Gaj                       bee0b952-afc9-11df-baa6-abc70f628665:2139
ZootService4Gaj                       bee0b952-afc9-11df-baa6-abc70f628665:2139
ZootDecision2ServiceSFDC_MSU_Sales    4ec557bc-6e57-11df-a6b5-a41ff225560f:2766

Query: Find Service by Access Point Keyword

Summary:

Returns the name of all services with a specific keyword in their access point URL.

Query Syntax for Database:

Oracle, MySql

Query:

SELECT UN.name, UAP.access_url
FROM UDDI_ACCESS_POINT UAP, UDDI_BINDING UB, UDDI_NAME UN 
WHERE UAP.binding_templat_id = UB.binding_templat_id
AND UB.busines_service_id = UN.ref_id
AND UN.ref_type = 'S'
AND UAP.access_url like ('%<KEYWORD>%')

Query Sample Results:

Using keyword asmx.

NAME                     ACCESS_URL
SetupAcctEnrollment      http://sdpfessql01.aescf.us.aexp.com/zyncSecure/SetupAcctEnrollment.asmx
GetPaymentEligibility    http://sdpfessql01.aescf.us.aexp.com/bobcatAOT/GetPaymentEligibility.asmx
GetPaymentEligibility    http://sdpfessql01.aescf.us.aexp.com/bobcatAOT/GetPaymentEligibility.asmx

Query: Find Primary Contacts for Organizations

Summary:

Returns the primary contacts for organizations and sub-organizations.

Query Syntax for Database:

Oracle, MySql

Query:

select distinct n.name as Organization, a.USE_TYPE as Class, a.DESCRIPTION,  b.ADDRESS_ID, 
b.ADDRESS_LINE1, b.ADDRESS_LINE2, b.ADDRESS_LINE3, b.ADDRESS_LINE4, b.ADDRESS_LINE5, 
b.USE_TYPE as ADDRESS_USE_TYPE
from uddi_business be inner join uddi_name n
on be.business_entity_id = n.ref_id and n.ref_type='B',
UDDI_CONTACT a left outer join UDDI_ADDRESS b on a.CONTACT_ID = b.CONTACT_ID
where a.USE_TYPE = 'Primary'
and be.business_entity_id = a.business_entity_id

Query Sample Results:

ORGANIZATION CLASS    DESCRIPTION    ADDRESS_ID    ADDRESS_LINE1    ADDRESS_LINE2    
ADDRESS_LINE3    ADDRESS_LINE4    ADDRESS_LINE5    ADDRESS_USE_TYPE
Digitas    Primary    2458   355 Park Avenue South   
New York, 10291 USA    www.digitas.com     Primary
Sample Loyalty Vendor    Primary    Lead Programmer Analyst    2513    American Express Technologies    TRCN,    
Phoenix,    Arizona, USA, 85032    Primary
AXESS INTERNATIONAL NETWORK INC    Primary    Tatsuya Takahashi    2655    SEA FORT SQUARE Center Bldg.    
2-3-12 Higashishinagawa,Shinagawa-ku    Tokyo    140-8619        Primary

Query: Find Contracts Attached to Service

Summary:

Returns all contracts attached to a service in the form of contract key and respective service key.

Query Syntax for Database:

Oracle, MySql

Query:

select cv.CONTRACTVERSIONKEY AS CONTRACTKEY,s.SERVICE_KEY AS SERVICEKEY from 
uddi_business b,uddi_service s,contracts_versions cv
where (s.BUSINESS_ENTITY_ID = b.BUSINESS_ENTITY_ID)
and
(cv.PROVIDERORGKEY = b.BUSINESS_KEY)

Query Sample Results:

CONTRACTKEY                                  SERVICEKEY
ee892c86-a63c-11df-baa6-abc70f628665:2114    uddi:cb818675-bb1a-11dd-b4ed-eb7d71949a33
ca23a7eb-2dd2-11de-bbc6-a429f2cfd8d9:1357    uddi:cb818675-bb1a-11dd-b4ed-eb7d71949a33
29345b92-038e-11de-bbc6-a429f2cfd8d9:1316    uddi:cb818675-bb1a-11dd-b4ed-eb7d71949a33

Query: Find Contacts for Organizations

Summary:

Finds the contacts for organizations.

Query Syntax for Database:

Oracle

Query:

SELECT msc.NAME, msc.ENTITYKEY, a.CONTACT_ID as CONTACT_ID, a.USE_TYPE as 
CONTACT_USE_TYPE, a.DESCRIPTION,  b.ADDRESS_ID, b.ADDRESS_LINE1, b.ADDRESS_LINE2, 
b.ADDRESS_LINE3, b.ADDRESS_LINE4, b.ADDRESS_LINE5, b.USE_TYPE as ADDRESS_USE_TYPE
from UDDI_CONTACT a left outer join UDDI_ADDRESS b on a.CONTACT_ID = b.CONTACT_ID, uddi_business 
ubiz, entity_names msc
where (ubiz.BUSINESS_ENTITY_ID = a.BUSINESS_ENTITY_ID)
and (ubiz.BUSINESS_KEY = msc.ENTITYKEY)

Query Sample Results:

NAME    ENTITYKEY    CONTACT_ID    CONTACT_USE_TYPE    DESCRIPTION    ADDRESS_ID ADDRESS_LINE1    ADDRESS_LINE2    ADDRESS_LINE3    ADDRESS_LINE4
ADRESS_LINE5 ADDRESS_USE_TYPE
Saoirse Bank        uddi:f8cda618-67f8-11dd-8de8-e93862cacbf4    4200    testJan1320125PM 1 testJan1320125PM 1    3800    testJan1320125PM 1    testJan1320125PM 1 testJan1320125PM 1    testJan1320125PM 1    testJan1320125PM 1    testJan1320125PM 1
MYCA    uddi:9a99530d-67f9-11dd-8de8-e93862cacbf4    4300    temp16Jan20112PM
temp16Jan20112PM    3900    temp16Jan20112PM    temp16Jan20112PM
temp16Jan20112PM    temp16Jan20112PM    temp16Jan20112PM    temp16Jan20112PM

Query: Find Services with Attached Contracts

Summary:

Returns service and contract information of all services with an active contract attached.

Query Syntax for Database:

Oracle

Query:

select d.name as service_name, c.name as Organization, a.business_key, b.service_key, 
CONTRACTS_VERSIONS.CONTRACTNAME, CONTRACTS_VERSIONS.ACTIVE, 
CONTRACTS_VERSIONS.STARTDTS,
CONTRACTS_VERSIONS.EXPIRE, CONTRACTS_VERSIONS.ENDDTS, 
CONTRACTS_VERSIONS.DESCRIPTION,
CONTRACTS_VERSIONS.ANONYMOUS, CONTRACTS_VERSIONS.PROVIDERORGKEY,
CONTRACTS_VERSIONS.CONSUMERORGKEY
from uddi_service b, uddi_name c, uddi_name d, CONTRACTS, CONTRACTS_VERSIONS_ARCHIVE, 
uddi_business a
left outer join CONTRACTS_VERSIONS on CONTRACTS_VERSIONS.PROVIDERORGKEY = a.business_key
where  a.business_entity_id = b.business_entity_id
and a.business_entity_id = c.ref_id and c.ref_type = 'B' 
and b.busines_service_id = d.ref_id and d.ref_type = 'S'
and CONTRACTS.ACTIVEVERSIONID = CONTRACTS_VERSIONS.CONTRACTVERSIONID
and CONTRACTS_VERSIONS.ACTIVE = 'Y'
and CONTRACTS.ACTIVEVERSIONID = CONTRACTS_VERSIONS_ARCHIVE.CONTRACTVERSIONID 
ORDER BY service_name ASC

Query Sample Results:

Service_Name    Organization    BUSINESS_KEY SERVICE_KEY CONTRACTNAME    ACTIVE STARTDTS    EXPIRE    ENDDTS    DESCRIPTION ANONYMOUSPROVIDERORGKEY    CONSUMERORGKEY
5.2 Container Service    Akana Policy Manager    uddi:soa.com:managementconfigurationbusinesskey uddi:soa.com:container-servicekey Default Contract for Policy Manager Services    Y    57:36.0
N    Default Contract for Policy Manager Services that allows anonymous access and defers
authorization to the service implementations.    Y    uddi:soa.com:managementconfigurationbusinesskey
5.2 Installation Service    Akana Policy Manager    uddi:soa.com:managementconfigurationbusinesskey uddi:soa.com:installationsvcs-mgr Default Contract for Policy Manager Services    Y    57:36.0
N    Default Contract for Policy Manager Services that allows anonymous access and defers
authorization to the service implementations.    Y    uddi:soa.com:managementconfigurationbusinesskey
ACHTransactionService    TCPS    uddi:72fa44db-a486-11dd-93e6-8a8ce9206e16    uddi:670cfd45-481d-11df-
8d89-bd31f6116dd5    Acxiom AddressStandardizationService to TCPS    Y    00:00.0 N Y    uddi:72fa44db-a486-11dd-93e6-8a8ce9206e16