/*****************************************************************************************/ /*****************************************************************************************/ /* Copyright(c) 2013 SAS Institute Inc., Cary, NC, USA. All Rights Reserved. */ /* */ /* Name: sna_23to61_upgrade_db2.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 - DB2 */ /*===================================================================*/ %let user = ; /* Other than Default User */ %let pass = ; /* DB2 Password */ %let dsn = ; /* DB2 Data Source */ %let schema = ; /* DB2 Schema */ PROC SQL NOERRORSTOP; CONNECT TO DB2 (USER=&USER PASS=&PASS DSN=&DSN); /*===================================================================*/ /* DB2 alter script for SNA_SOCIAL_NETWORK_CLUSTER */ /*===================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER DROP PRIMARY KEY) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY CLUSTER_ID VARCHAR(255)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY ACTIONABLE_ENTITY_ID VARCHAR(255)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY ACTIONABLE_ENTITY_TYPE VARCHAR(100)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY LAST_UPDATE_DTTM DATE) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY USER_ID VARCHAR(35)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_CLUSTER MODIFY CREATE_DTTM DATE) BY DB2; 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 DB2; /*===================================================================*/ /* DB2 alter script for SNA_SOCIAL_NETWORK_GROUP */ /*===================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_GROUP MODIFY LAST_UPDATE_DTTM DATE) BY DB2; /*===================================================================*/ /* DB2 alter script for SNA_SOCIAL_NETWORK_LINK */ /*===================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK DROP PRIMARY KEY) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_VERSION_ID INTEGER) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CLUSTER_ID NOT NULL) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CLUSTER_ID VARCHAR(255)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY FROM_NODE_UID INTEGER) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY TO_NODE_UID INTEGER) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK ADD CLUSTER_VERSION_ID INTEGER) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_COLOR VARCHAR(255)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LINK_LABEL VARCHAR(255)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY CREATE_DTTM DATE) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK MODIFY LAST_UPDATE_DTTM DATE) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_LINK ADD CONSTRAINT PRIM_KEY PRIMARY KEY(LINK_VERSION_ID)) BY DB2; /*===================================================================*/ /* DB2 alter script for SNA_SOCIAL_NETWORK_NODE */ /*===================================================================*/ EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE DROP PRIMARY KEY) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY NODE_VERSION_ID INTEGER) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD GROUP_VERSION_ID INTEGER) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD NODE_ID VARCHAR(255)) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY CREATE_DTTM DATE) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE MODIFY LAST_UPDATE_DTTM DATE) BY DB2; EXECUTE(ALTER TABLE SNA_SOCIAL_NETWORK_NODE ADD CONSTRAINT PRIM_KEY PRIMARY KEY(NODE_VERSION_ID)) BY DB2; /*===================================================================*/ /* Create New SNA Tables */ /*===================================================================*/ EXECUTE (CREATE TABLE SNA_COLORMODEL ( COLOR_MODEL_SK NUMERIC(20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; EXECUTE (CREATE TABLE SNA_COLUMN_DISPLAY ( COLUMN_DISPLAY_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; EXECUTE (CREATE TABLE SNA_COLUMN_METADATA ( COLUMN_METADATA_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; EXECUTE (CREATE TABLE SNA_CONTEXT_PREFERENCE ( CONTEXT_PREFERENCE_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; 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 DB2; EXECUTE (CREATE TABLE SNA_CUST_MEASURE_ITEMS_SAVED ( CUSTOM_MEASURE_ITEMS_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; EXECUTE (CREATE TABLE SNA_CUSTOM_MEASURE_ITEMS ( CUSTOM_MEASURE_ITEMS_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; EXECUTE (CREATE TABLE SNA_CUSTOM_MEASURES ( CUSTOM_MEASURES_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, ANALYTIC_CONTEXT_NM VARCHAR(255), OBJECT_ID VARCHAR(255) NOT NULL, OBJECT_TYPE VARCHAR(255) NOT NULL, PRIMARY KEY (CUSTOM_MEASURES_SK) )) BY DB2; EXECUTE (CREATE TABLE SNA_EXTERNAL_APP ( EXTERNAL_APP_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, ANALYTIC_CONTEXT_NM VARCHAR(255) NOT NULL, URL_NM VARCHAR(255) NOT NULL, PRIMARY KEY (EXTERNAL_APP_SK) )) BY DB2; EXECUTE (CREATE TABLE SNA_EXTERNAL_APP_DISPLAY_NM ( EXTERNAL_APP_DISPLAY_NAME_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, 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 DB2; EXECUTE (CREATE TABLE SNA_EXTERNAL_APP_ROLE ( EXTERNAL_APP_ROLE_SK NUMERIC (20) NOT NULL GENERATED BY DEFAULT AS IDENTITY, EXTERNAL_APP_SK NUMERIC (20) NOT NULL, METADATA_ROLE_NM VARCHAR(255) NOT NULL, PRIMARY KEY (EXTERNAL_APP_ROLE_SK) )) BY DB2; 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 DB2; 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 DB2; /* 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 DB2; 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 DB2; 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 DB2; 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 DB2; 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 DB2; DISCONNECT FROM DB2; quit;