WSRR V7 ON AIX WITH ORACLE群集安裝記錄10

— Licensed Materials – Property of IBM

— 5724-N72 5655-W17

— (c) Copyright IBM Corp. 2006, 2009 All Rights Reserved

— US Government Users Restricted Rights – Use, duplication or

— disclosure restricted by GSA ADP Schedule Contract with

— IBM Corp.

ALTER SESSION SET CURRENT_SCHEMA = WSRR;

CREATE TABLE SR_ANALYTICS (

        EVENT BLOB,

        SOURCE_COMPONENT_ID NVARCHAR2(1020),

        REPORTER_COMPONENT_ID NVARCHAR2(1020),

        SITUATION NUMBER(10),

        CREATION_TIME TIMESTAMP(6) NOT NULL ENABLE,

        EXTENSION_NAME NVARCHAR2(1024),

        VERSION NVARCHAR2(16),

        MSG_DATA_ELEMENT_MSGLOCALE NVARCHAR2(11),

        MSG_DATA_ELEMENT_MSGCATLOGTKNS NVARCHAR2(4000),

        MSG_DATA_ELEMENT_MSGID NVARCHAR2(256),

        MSG_DATA_ELEMENT_MSGIDTYPE NVARCHAR2(32),

        MSG_DATA_ELEMENT_MSGCATALOGID NVARCHAR2(128),

        MSG_DATA_ELEMENT_MSGCATLOGTYPE NVARCHAR2(32),

        MSG_DATA_ELEMENT_MSGCATALOG NVARCHAR2(128),

        SITUATION_CATEGORYNAME NVARCHAR2(20) DEFAULT ‘ReportSituation’,

        SITUATION_REPORTCATEGORY NVARCHAR2(5) DEFAULT ‘Log’,

        GLOBAL_INSTANCE_ID NVARCHAR2(48) NOT NULL ENABLE    );

ALTER TABLE SR_ANALYTICS ADD CONSTRAINT SR_ANALYTICS_PK

    PRIMARY KEY (GLOBAL_INSTANCE_ID) ENABLE;

CREATE TABLE SR_ASSERTION (

        GLOBAL_INSTANCE_ID NVARCHAR2(48) NOT NULL ENABLE,

        ASSERTION_TYPE NVARCHAR2(40) NOT NULL ENABLE,

        ASSERTION_ID NVARCHAR2(120),

        ASSERTION_NAME NVARCHAR2(120),

        STATUS CHAR(1) DEFAULT ‘0’ NOT NULL ENABLE);

ALTER TABLE SR_ASSERTION ADD CONSTRAINT SR_ASSERTION_PK

    PRIMARY KEY (GLOBAL_INSTANCE_ID) ENABLE;

ALTER TABLE SR_ASSERTION ADD CONSTRAINT ASSN_ANYTCS_FK

    FOREIGN KEY    (GLOBAL_INSTANCE_ID)

    REFERENCES SR_ANALYTICS(GLOBAL_INSTANCE_ID)

    ON DELETE CASCADE;

CREATE TABLE SR_ASSOCIATED (

        RESOLVED_RECORD NVARCHAR2(48) NOT NULL ENABLE,

        GLOBAL_INSTANCE_ID NVARCHAR2(48) NOT NULL ENABLE    );

ALTER TABLE SR_ASSOCIATED ADD CONSTRAINT ASSCTD_ANLYTICS_FK

    FOREIGN KEY (GLOBAL_INSTANCE_ID)

    REFERENCES SR_ANALYTICS (GLOBAL_INSTANCE_ID)

    ON DELETE CASCADE;

CREATE TABLE SR_ENTITYACTION (

        OPERATION NVARCHAR2(20),

        ENTITY_TYPE NVARCHAR2(1020),

        USERID NVARCHAR2(64),

        ROLE NVARCHAR2(254),

        ENTITY_BSRURI NVARCHAR2(48),

        TRANSITION_URI NVARCHAR2(1020),

        GLOBAL_INSTANCE_ID NVARCHAR2(48) NOT NULL ENABLE    );

ALTER TABLE SR_ENTITYACTION ADD CONSTRAINT SR_ENTITYACTION_PK

    PRIMARY KEY (GLOBAL_INSTANCE_ID) ENABLE;

ALTER TABLE SR_ENTITYACTION ADD CONSTRAINT ENTYUPD_ANYTCS_FK

    FOREIGN KEY    (GLOBAL_INSTANCE_ID)

    REFERENCES SR_ANALYTICS (GLOBAL_INSTANCE_ID)

    ON DELETE CASCADE;

CREATE TABLE SR_VALDTRPOLICY (

        GLOBAL_INSTANCE_ID NVARCHAR2(48) NOT NULL ENABLE,

        POLICYDOMAIN NVARCHAR2(1020),

        POLICYID NVARCHAR2(1020),

        POLICYCLASS NVARCHAR2(1020),

        STATUS CHAR(1) DEFAULT ‘0’ NOT NULL ENABLE,

        POLICYNAME NVARCHAR2(1020),

        POLICYURI NVARCHAR2(1020),

        CREATION_TIME TIMESTAMP(6) NOT NULL ENABLE );

ALTER TABLE SR_VALDTRPOLICY ADD CONSTRAINT SR_VALDTRPOLICY_PK

    PRIMARY KEY    (GLOBAL_INSTANCE_ID) ENABLE;

ALTER TABLE SR_VALDTRPOLICY ADD CONSTRAINT VALDPLCY_ANYTCS_FK

    FOREIGN KEY (GLOBAL_INSTANCE_ID)

    REFERENCES SR_ANALYTICS    (GLOBAL_INSTANCE_ID)

    ON DELETE CASCADE;

CREATE TABLE “WSRRTASK”(“TASKID” NUMBER(19) NOT NULL,

“VERSION” VARCHAR2(5) NOT NULL,

“ROW_VERSION” NUMBER(10) NOT NULL,

“TASKTYPE” NUMBER(10) NOT NULL,

“TASKSUSPENDED” NUMBER(1) NOT NULL,

“CANCELLED” NUMBER(1) NOT NULL,

“NEXTFIRETIME” NUMBER(19) NOT NULL,

“STARTBYINTERVAL” VARCHAR2(254),

“STARTBYTIME” NUMBER(19),

“VALIDFROMTIME” NUMBER(19),

“VALIDTOTIME” NUMBER(19),

“REPEATINTERVAL” VARCHAR2(254),

“MAXREPEATS” NUMBER(10) NOT NULL,

“REPEATSLEFT” NUMBER(10) NOT NULL,

“TASKINFO” BLOB,

“NAME” VARCHAR2(254),

“AUTOPURGE” NUMBER(10) NOT NULL,

“FAILUREACTION” NUMBER(10),

“MAXATTEMPTS” NUMBER(10),

“QOS” NUMBER(10),

“PARTITIONID” NUMBER(10),

“OWNERTOKEN” VARCHAR2(200) NOT NULL,

“CREATETIME” NUMBER(19) NOT NULL,

PRIMARY KEY (“TASKID”) );

CREATE INDEX “WSRRTASK_IDX1” ON “WSRRTASK” (“TASKID”,

“OWNERTOKEN”) ;

CREATE INDEX “WSRRTASK_IDX2” ON “WSRRTASK” (“NEXTFIRETIME” ASC,

“REPEATSLEFT”,

“PARTITIONID”) ;

CREATE TABLE “WSRRTREG” (“REGKEY” VARCHAR2(254) NOT NULL ,

“REGVALUE” VARCHAR2(254) ,

PRIMARY KEY ( “REGKEY” ));

CREATE TABLE “WSRRLMGR” (“LEASENAME” VARCHAR2(254) NOT NULL,

“LEASEOWNER” VARCHAR2(254),

“LEASE_EXPIRE_TIME” NUMBER(19),

“DISABLED” VARCHAR2(254),

PRIMARY KEY ( “LEASENAME” ));

CREATE TABLE “WSRRLMPR” (“LEASENAME” VARCHAR2(254) NOT NULL,

“NAME” VARCHAR2(254) NOT NULL,

“VALUE” VARCHAR2(254) NOT NULL );

CREATE INDEX “WSRRLMPR_IDX1” ON “WSRRLMPR” (“LEASENAME”,

“NAME”) ;

GRANT SELECT,INSERT,UPDATE,DELETE ON SR_VALDTRPOLICY TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON SR_ENTITYACTION TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON SR_ASSOCIATED TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON SR_ANALYTICS TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON SR_ASSERTION TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON WSRRTASK TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON WSRRTREG TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON WSRRLMGR TO WSRR;

GRANT SELECT,INSERT,UPDATE,DELETE ON WSRRLMPR TO WSRR;

sqlplus sys/<yourPassword>@ORCL AS SYSDBA @createWsrrTables2.sql

— remove this line if running through JDBC:

SET CMDS ;

ALTER SESSION SET CURRENT_SCHEMA = WSRR ;

———————————————————————

—- Stored procedure for inserts to W_OBJ_LIT_PLAIN

———————————————————————

CREATE OR REPLACE PROCEDURE PMAPSTR (hash_in IN NCHAR,

fullhash_in IN NCHAR,

xmllang_in IN NVARCHAR2,

litval_in IN NVARCHAR2,

large_in IN NVARCHAR2,

typedstr_in IN NUMBER,

result OUT NUMBER) AS

BEGIN

IF typedstr_in = 1 THEN

SELECT id INTO result FROM w_obj_lit_plain WHERE hash = hash_in AND typedstr = 1;

ELSE

SELECT id INTO result FROM w_obj_lit_plain WHERE fullhash = fullhash_in;

END IF;

EXCEPTION

    WHEN NO_DATA_FOUND

    THEN

     IF typedstr_in = 1 THEN

INSERT INTO w_obj_lit_plain( ID, LARGE, LITVAL, HASH, TYPEDSTR )

VALUES ( SEQ_W_OBJ_LIT_PLAIN_ID.nextval, large_in, litval_in, hash_in, typedstr_in);

SELECT id INTO result FROM w_obj_lit_plain

WHERE hash = hash_in AND typedstr = 1;

ELSE

INSERT INTO w_obj_lit_plain( ID, LARGE, LITVAL, HASH, FULLHASH, XML_LANG, TYPEDSTR )

VALUES ( SEQ_W_OBJ_LIT_PLAIN_ID.nextval, large_in, litval_in, hash_in, fullhash_in, xmllang_in, typedstr_in);

SELECT id INTO result FROM w_obj_lit_plain

WHERE fullhash = fullhash_in;

END IF;

END PMAPSTR;

;

—————————————————————————

—- Stored procedure for inserts to W_URI_TAB

—————————————————————————

CREATE OR REPLACE PROCEDURE PMAPURI (hash_in IN NCHAR,

litval_in IN NVARCHAR2,

namespace_id_in IN NUMBER,

result OUT NUMBER) AS

BEGIN

    SELECT id into result FROM w_uri WHERE litval=litval_in;    

EXCEPTION

    WHEN NO_DATA_FOUND

    THEN

        INSERT INTO w_uri VALUES ( SEQ_W_URI_ID.nextval, litval_in, hash_in, NULLIF(namespace_id_in, -1));

     SELECT id INTO result FROM w_uri WHERE litval=litval_in;

END PMAPURI;

;

—————————————————————————

—- Grant access to stored procedures

—————————————————————————

GRANT EXECUTE ON PMAPSTR TO WSRR ;

GRANT EXECUTE ON PMAPURI TO WSRR ;