Netezza Database Schema Modification for CI360
This code is also available in:
When modifying a Netezza (IBM PureData) environment for SAS Customer Intelligence 360, the use of SQL Pass-Through (EXECUTE(...) BY NETEZZA) is the most efficient method. It bypasses the SAS engine's translation layer, allowing you to use Netezza-specific syntax like the DISTRIBUTE ON clause, which is vital for performance in a Massively Parallel Processing (MPP) architecture.
Type : EXTERNE
The script does not use any SAS data. It operates exclusively on tables in an external Netezza database, modifying their structure (schema).
| 1 | /*======================================================================*/ |
| 2 | /* Enter Customer Specific Target Source Connection Values - Netezza */ |
| 3 | /*======================================================================*/ |
| 4 | |
| 5 | %let user = |
| 6 | %let pass = |
| 7 | %let db = |
| 8 | %let server = |
| 1 | PROC SQL NOERRORSTOP; |
| 2 | |
| 3 | CONNECT TO NETEZZA (USER=&USER PASS=&PASS DB=&DB SERVER=&SERVER); |
| 4 | |
| 5 | /*** cdm_contact_history ***/ |
| 6 | EXECUTE(ALTER TABLE cdm_contact_history ADD control_group_flg CHAR(1) NULL) BY NETEZZA; |
| 7 | |
| 8 | /*** cdm_response_history ***/ |
| 9 | EXECUTE(ALTER TABLE cdm_response_history ADD properties_map_doc VARCHAR(4000) NULL) BY NETEZZA; |
| 10 | |
| 11 | /*** cdm_task_detail ***/ |
| 12 | EXECUTE(ALTER TABLE cdm_task_detail ADD control_group_action_nm VARCHAR(65) NULL) BY NETEZZA; |
| 13 | EXECUTE(ALTER TABLE cdm_task_detail ADD stratified_sampling_action_nm VARCHAR(65) NULL) BY NETEZZA; |
| 14 | EXECUTE(ALTER TABLE cdm_task_detail ADD segment_tests_flg CHAR(1) NULL) BY NETEZZA; |
| 15 | |
| 16 | EXECUTE (CREATE TABLE cdm_segment_test |
| 17 | ( |
| 18 | test_cd VARCHAR(60) NOT NULL , |
| 19 | task_version_id VARCHAR(36) NOT NULL , |
| 20 | task_id VARCHAR(36) NOT NULL , |
| 21 | test_nm VARCHAR(65) , |
| 22 | test_type_nm VARCHAR(10) , |
| 23 | test_enabled_flg CHAR(1) , |
| 24 | test_sizing_type_nm VARCHAR(65) , |
| 25 | test_cnt INTEGER , |
| 26 | test_pct NUMERIC(5,2) , |
| 27 | stratified_sampling_flg CHAR(1) , |
| 28 | stratified_samp_criteria_txt VARCHAR(1024) , |
| 29 | updated_dttm TIMESTAMP |
| 30 | ) |
| 31 | DISTRIBUTE ON (test_cd) |
| 32 | ) |
| 33 | BY NETEZZA; |
| 34 | |
| 35 | EXECUTE (CREATE TABLE cdm_segment_test_x_segment |
| 36 | ( |
| 37 | test_cd VARCHAR(60) NOT NULL , |
| 38 | task_version_id VARCHAR(36) NOT NULL , |
| 39 | task_id VARCHAR(36) NOT NULL , |
| 40 | segment_id VARCHAR(36) , |
| 41 | updated_dttm TIMESTAMP |
| 42 | ) |
| 43 | DISTRIBUTE ON (test_cd) |
| 44 | ) |
| 45 | BY NETEZZA; |
| 46 | |
| 47 | DISCONNECT FROM NETEZZA; |
| 48 | QUIT; |