Attention : This code requires administrator privileges.
The script uses a pass-through connection via PROC SQL to connect to a Netezza database. It executes DDL (Data Definition Language) statements to alter three existing tables (cdm_contact_history, cdm_response_history, cdm_task_detail) by adding new columns. Additionally, it creates two new tables (cdm_segment_test and cdm_segment_test_x_segment) required for the updated data model. Connection information is parameterized using macro variables.
Data Analysis
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 Code Block
Macro
Explanation :
This block defines macro variables to store connection information (user, password, database name, server) to the Netezza database. This allows parameterizing the script without modifying the main code.
This block defines macro variables to store connection information (user, password, database name, server) to the Netezza database. This allows parameterizing the script without modifying the main code.
Copied!
| 1 | /*======================================================================*/ |
| 2 | /* Enter Customer Specific Target Source Connection Values - Netezza */ |
| 3 | /*======================================================================*/ |
| 4 | |
| 5 | %let user = |
| 6 | %let pass = |
| 7 | %let db = |
| 8 | %let server = |
2 Code Block
PROC SQL
Explanation :
This block uses the PROC SQL procedure to connect to the Netezza database using the previously defined macros. It then executes native SQL commands via the EXECUTE ... BY NETEZZA clause to alter and create tables. The NOERRORSTOP option ensures that the script continues even if a command fails. The connection is then closed with DISCONNECT.
This block uses the PROC SQL procedure to connect to the Netezza database using the previously defined macros. It then executes native SQL commands via the EXECUTE ... BY NETEZZA clause to alter and create tables. The NOERRORSTOP option ensures that the script continues even if a command fails. The connection is then closed with DISCONNECT.
Copied!
| 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; |
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
Expert Advice
Simon
Expert SAS et fondateur.
« 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. »