/*****************************************************************************************/ /*****************************************************************************************/ /* Copyright(c) 2013 SAS Institute Inc., Cary, NC, USA. All Rights Reserved. */ /* */ /* Name: sna_23to61_upgrade_sqlsever.sas */ /* */ /* Purpose: Upgrade SAS Social Network Analysis version 2.3 tables */ /* to the SAS Social Network Analysis version 6.1 */ /* */ /* Output: SNA 6.1 tables */ /* */ /* Usage: Run this script in a SAS environment with administrative privaleges. */ /* a. LIBNAME is set to SNA. */ /* b. Provide the database specific connection information to */ /* begin the migration process. */ /* */ /* Backup/Restore: Save a backup of the 2.3 table data. Review the changes made to */ /* the database tables and restore the data after the migration */ /* is complete. */ /* */ /*****************************************************************************************/ /*****************************************************************************************/ /*======================================================================*/ /* Enter Customer Specific Target Source Connection Values - SQL Server */ /*======================================================================*/ %let user = ; /* Other than Default User */ %let pwd = ; /* SQL Server Password */ %let dsn = ; /* SQL Server Data Source */ %let schema = ; /* SQL Server Schema */ PROC SQL NOERRORSTOP; CONNECT TO OLEDB (USER=&USER PWD=&PWD DSN=&DSN PROMPT=NO PROVIDER=SQLOLEDB ; /*======================================================================*/ /* OLEDB alter script for SNA_SOCIAL_NETWORK_CLUSTER */ /*======================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER DROP PRIMARY KEY) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY CLUSTER_ID VARCHAR(255)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY ACTIONABLE_ENTITY_ID VARCHAR(255)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY ACTIONABLE_ENTITY_TYPE VARCHAR(100)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY LAST_UPDATE_DTTM DATE) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY USER_ID VARCHAR(35)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY CREATE_DTTM DATE) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER ADD CONSTRAINT PRIM_KEY PRIMARY KEY(CLUSTER_ID, ACTIONABLE_ENTITY_ID, ACTIONABLE_ENTITY_TYPE, LAST_UPDATE_DTTM)) BY OLEDB; EXECUTE(CREATE SEQUENCE SEQ_SNA_SOCIAL_NETWRK_CLSTER INCREMENT BY 1 START WITH 10000) BY OLEDB ; /*======================================================================*/ /* OLEDB alter script for SNA_SOCIAL_NETWORK_GROUP / /*======================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_GROUP MODIFY LAST_UPDATE_DTTM DATE) BY OLEDB; /*======================================================================*/ /* OLEDB alter script for SNA_SOCIAL_NETWORK_LINK */ /*======================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK DROP PRIMARY KEY) BY ; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_VERSION_ID INTEGER) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CLUSTER_ID NOT NULL) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CLUSTER_ID VARCHAR(255)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY FROM_NODE_UID INTEGER) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY TO_NODE_UID INTEGER) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK ADD CLUSTER_VERSION_ID INTEGER) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_COLOR VARCHAR(255)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_LABEL VARCHAR(255)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CREATE_DTTM DATE) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LAST_UPDATE_DTTM DATE) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK ADD CONSTRAINT PRIM_KEY PRIMARY KEY(LINK_VERSION_ID)) BY OLEDB; /*======================================================================*/ /* OLEDB alter script for SNA_SOCIAL_NETWORK_NODE */ /*======================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE DROP PRIMARY KEY) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY NODE_VERSION_ID INTEGER) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD GROUP_VERSION_ID INTEGER) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD NODE_ID VARCHAR(255)) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY CREATE_DTTM DATE) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY LAST_UPDATE_DTTM DATE) BY OLEDB; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD CONSTRAINT PRIM_KEY PRIMARY KEY(NODE_VERSION_ID)) BY OLEDB; /*======================================================================*/ /* Create New SNA Tables */ /*======================================================================*/ EXECUTE (CREATE TABLE SNA_COLORMODEL ( COLOR_MODEL_SK NUMERIC(20) NOT NULL IDENTITY(10000,1), COLUMN_METADATA_SK NUMBER(20) NOT NULL, START_RANGE_NM VARCHAR(60), END_RANGE_NM VARCHAR(60), STYLE_TYPE VARCHAR(255), FORMAT_TYPE VARCHAR(50), SEXCL_FLG VARCHAR(1), EEXCL_FLG VARCHAR(1), PRIMARY KEY (COLOR_MODEL_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_COLUMN_DISPLAY ( COLUMN_DISPLAY_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), COLUMN_METADATA_SK NUMERIC (20) NOT NULL, COLUMN_DISPLAY_NM VARCHAR(100), JUSTIFICATION_SPEC VARCHAR(255), HEADER_JUSTIFICATION_SPEC VARCHAR(255), FORMATSPEC_DESC VARCHAR(255), LOCALE_DESC VARCHAR(255), PRIMARY KEY (COLUMN_DISPLAY_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_COLUMN_METADATA ( COLUMN_METADATA_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), TABLE_METADATA_SK NUMERIC (20) NOT NULL, COLUMN_NM VARCHAR(60), DATATYPE_NM VARCHAR(60), INCLUDE_FACET_SEARCH_FLG VARCHAR(1), FACET_SEARCH_TYPE VARCHAR(100), MIN_WIDTH_SPEC INTEGER, PRIMARY KEY (COLUMN_METADATA_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_CONTEXT_PREFERENCE ( CONTEXT_PREFERENCE_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), ANALYTIC_CONTEXT_NM VARCHAR(255) NOT NULL, PROPERTY_NM VARCHAR(255) NOT NULL, PROPERTY_DESC VARCHAR(255) NOT NULL, PRIMARY KEY (CONTEXT_PREFERENCE_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_CURRENCY ( CURRENCY_SK NUMERIC (20) NOT NULL, ALIGN_SYMBOL_CD VARCHAR(5), CURRENCY_SYMBOL_CD VARCHAR(4), DECIMAL_SEPARATOR_CD VARCHAR(4), PRESION_NO INTEGER, ROUNDING_CD VARCHAR(7), USE_NEGATIVE_SIGN_FLG VARCHAR(5), USE_THOUSANDS_SEPARATOR_FLG VARCHAR(5), THOUSANDS_SEPARATOR_CD VARCHAR(4), CURRENCY_NM VARCHAR(40), PRIMARY KEY (CURRENCY_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_CUST_MEASURE_ITEMS_SAVED ( CUSTOM_MEASURE_ITEMS_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), MEASURE_NM VARCHAR(255) NOT NULL, MEASURE_VALUE VARCHAR(255), DATATYPE_NM VARCHAR(60), LINK_UNIQUE_ID VARCHAR(255), NODE_UNIQUE_ID VARCHAR(255), LAST_UPDATE_DTTM DATE NOT NULL, PRIMARY KEY (CUSTOM_MEASURE_ITEMS_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_CUSTOM_MEASURE_ITEMS ( CUSTOM_MEASURE_ITEMS_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), CUSTOM_MEASURES_SK NUMERIC (20) NOT NULL, MEASURE_NM VARCHAR(255) NOT NULL, MEASURE_VALUE VARCHAR(255), DATATYPE_NM VARCHAR(60) NOT NULL, PRIMARY KEY (CUSTOM_MEASURE_ITEMS_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_CUSTOM_MEASURES ( CUSTOM_MEASURES_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), ANALYTIC_CONTEXT_NM VARCHAR(255), OBJECT_ID VARCHAR(255) NOT NULL, OBJECT_TYPE VARCHAR(255) NOT NULL, PRIMARY KEY (CUSTOM_MEASURES_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_EXTERNAL_APP ( EXTERNAL_APP_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), ANALYTIC_CONTEXT_NM VARCHAR(255) NOT NULL, URL_NM VARCHAR(255) NOT NULL, PRIMARY KEY (EXTERNAL_APP_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_EXTERNAL_APP_DISPLAY_NM ( EXTERNAL_APP_DISPLAY_NAME_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), EXTERNAL_APP_SK NUMERIC (20), DISPLAY_NM VARCHAR(255) NOT NULL, LOCALE_NM VARCHAR(255) NOT NULL, PRIMARY KEY (EXTERNAL_APP_DISPLAY_NAME_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_EXTERNAL_APP_ROLE ( EXTERNAL_APP_ROLE_SK NUMERIC (20) NOT NULL IDENTITY(10000,1), EXTERNAL_APP_SK NUMERIC (20) NOT NULL, METADATA_ROLE_NM VARCHAR(255) NOT NULL, PRIMARY KEY (EXTERNAL_APP_ROLE_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_TABLE_DISPLAY ( TABLE_DISPLAY_SK NUMERIC (20), TABLE_METADATA_SK NUMERIC (20) NOT NULL, TABLE_DISPLAY_NM VARCHAR(100) NOT NULL, LOCALE_DESC VARCHAR(100), PRIMARY KEY (TABLE_DISPLAY_SK) )) BY OLEDB; EXECUTE (CREATE TABLE SNA_TABLE_METADATA ( TABLE_METADATA_SK NUMERIC (20), SEGMENT_ID VARCHAR(128) NOT NULL, ANALYTIC_CONTEXT_NM VARCHAR(255) NOT NULL, TABLE_NM VARCHAR(60), TAB_POSITION_NO NUMERIC (4), PRIMARY KEY (TABLE_METADATA_SK) )) BY OLEDB; /* Add Foreign Keys */ EXECUTE ( ALTER TABLE SNA_COLORMODEL ADD CONSTRAINT XIF1SNA_COLORMODEL FOREIGN KEY (COLUMN_METADATA_SK) REFERENCES SNA_COLUMN_METADATA (COLUMN_METADATA_SK); ) BY OLEDB; EXECUTE ( ALTER TABLE SNA_COLUMN_DISPLAY ADD CONSTRAINT XIF1SNA_SNA_COLUMN_DISPLAY FOREIGN KEY (COLUMN_METADATA_SK) REFERENCES SNA_COLUMN_METADATA (COLUMN_METADATA_SK); ) BY OLEDB; EXECUTE ( ALTER TABLE SNA_COLUMN_METADATA ADD CONSTRAINT XIF1SNA_SNA_COLUMN_METADATA FOREIGN KEY (TABLE_METADATA_SK) REFERENCES SNA_TABLE_METADATA (TABLE_METADATA_SK); ) BY OLEDB; EXECUTE ( ALTER TABLE SNA_CUSTOM_MEASURE_ITEMS ADD CONSTRAINT XIF1SNA_SNA_CUSTOM_MEASURE_ITEMS FOREIGN KEY (CUSTOM_MEASURES_SK) REFERENCES SNA_CUSTOM_MEASURES (CUSTOM_MEASURES_SK); ) BY OLEDB; EXECUTE ( ALTER TABLE SNA_TABLE_DISPLAY ADD CONSTRAINT XIF1SNA_SNA_CUSTOM_MEASURE_ITEMS FOREIGN KEY (TABLE_METADATA_SK) REFERENCES SNA_TABLE_METADATA (TABLE_METADATA_SK); ) BY OLEDB; DISCONNECT FROM DB2; quit;