load SAS dataset into Teradata Volatile table

Connectivity
Enthusiast

load SAS dataset into Teradata Volatile table

I have several SAS tables that require to be copied over to Teradata platform V13.

I usually use the Fastload option and works very well, however I need to create and

load Teradata Volatile tables.  I have a pgm that works tried with 10K records and takes 8 minutes this is to long. the full table to load is 26 million. has anyone tried loading a TERADATA Volatile table without using the Insert into method

is there any other method? 

/* Set global connection for all tables. */
libname x teradata user=test pw=test server=boom connection=global;

/* Create a volatile table. */
proc sql;
connect to teradata(user=test pw=test server=boom connection=global);
execute (CREATE VOLATILE TABLE temp1 (col1 INT)
ON COMMIT PRESERVE ROWS) by teradata;
execute (COMMIT WORK) by teradata;
quit;

/* Insert 1 row into the volatile table. */
proc sql;
connect to teradata(user=test pw=test server=boom connection=global);
execute (INSERT INTO temp1 VALUES(1)) by teradata;
execute (COMMIT WORK) by teradata;
quit;

7 REPLIES
Not applicable

Re: load SAS dataset into Teradata Volatile table

I had a issue much like yours, SAS provided http://ftp.sas.com/techsup/download/hotfix/HF2/B25.html#45162


This fixed my performance issue.

Enthusiast

Re: load SAS dataset into Teradata Volatile table

not sure, to me it looks like you do single row insert into your volatile table

do you have a scratch place to fastload the data into TD - thats how I do it

Not applicable

Re: load SAS dataset into Teradata Volatile table

take a look at the link below:

http://support.sas.com/kb/21/038.html

Fan

Re: load SAS dataset into Teradata Volatile table

@v_gabreilli   Did you ever solve this problem? If yes how ?  Cn you please let me know.I too am facing a Situation where i have to insert 6 million rows into a  Teradata Volatile table using SAS but it runs forever.

Enthusiast

Re: load SAS dataset into Teradata Volatile table

Hi,

I am having a similar issue of loading a SAS dataset into a volatile table using a SAS libname pass thru:


options nocenter mprint symbolgen;

libname TeraLib teradata user = &user password = &pswd connection=global dbmstemp=yes ;

libname x "&gbRunDir/VolatileTbl" ;


I followed logic given by the link http://support.sas.com/kb/21/038.html suggested by DylanLiu above for creating a volatile table by extracting data from a SAS dataset using a Teradata libname pass thru but not able to get it working:

NOTE: Remote submit to _SERVER_ commencing.
MPRINT(RSUBMIT): options nocenter mprint symbolgen;
MPRINT(RSUBMIT): libname x "&gbRunDir/VolatileTbl" ;
MPRINT(RSUBMIT): libname TeraLib teradata user = &user password = &pswd connection=global dbmstemp=yes ;
MPRINT(RSUBMIT): proc sql noprint ;
MPRINT(RSUBMIT): %ConnectToTeradata ;
MPRINT(RSUBMIT): execute (create volatile table abc (CLNT_NO integer not null , ACCT_MGR_NO integer , ACTV_INA_STS_CD char(1) ,
AGE_VAL integer , BIRTH_DT date , BUS_SEG_CD integer , CLNT_SINCE_DT date , CLNT_TYPE char(1) , ENTITLEMENT_CD integer , LANG_CD
char(2) , PFA_IND char(1) , POSTAL_CD char(6) , PROVINCE_CD char(2) , RELN_MG_INST_NO integer , RELN_MG_UNIT_NO decimal(11,0) ,
STAFF_CLASS_CD char(1) , CLNT_TYPE_CD char(1) , RELATIONSHIP_MANAGEMENT_CD char(5) , MANAGED_BY_CD char(1) , SPLIT_CD char(2) ,
REGION_CD char(3) , LANGUAGE_QUEUE_CD char(1) , MANAGEMENT_TYPE_CD char(2) , CMP_FLAG decimal(11,0) , OPPCRIT_IND char(1) ,
MARCRIT_IND char(1) , MARCRITSTU_IND char(1) , ACTIVE_IND char(1) , ACTIVE_ONLINE_IND char(1) , ACTIVE_ONLINE_DI_DS_IND char(1) ,
ADDRESS_IN_CANADA_IND char(1) , MAILING_ADDRESS_IN_CANADA_IND char(1) , RES_ADDRESS_IN_CANADA_IND char(1) , AGE_OF_MAJORITY_IND
char(1) , BALANCE_PROTECTOR_IND char(1) , BLIP_DISABILITY_IND char(1) , BLIP_LIFE_IND char(1) , BPOL_IND char(1) ,
CATO_CONTROL_TG9_IND char(1) , CLNT_FACING_STAFF_IND char(1) , COLLECTION_LENDING_TRANSIT_IND char(1) , DAY_TO_DAY_SAVINGS_IND
char(1) , DDA_OPEN_ON_PRIMARY_IND char(1) , DDA_SNL_IND char(1) , DDA_VIP_IND char(1) , DECEASED_IND char(1) ,
UNTARGETABLE_POA_DECEASED_IND char(1) , DELINQUENT_IND char(1) , DEPRESSED_ECONOMIC_MARKETS_IND char(1) ,
EQUITY_NEAR_PRIME_MORTGAGE_IND char(1) , EBM_CONTROL_TG8_IND char(1) , ESAVINGS_CAD_OPEN_IND char(1) , ESAVINGS_CAD_OPEN_PRIMARY_IND
char(1) , FIRST_NATIONS_MTG_LOAN_IND char(1) , FIRST_NATIONS_TRANSIT_IND char(1) , FRAUD_VICTIMS_IVR_IND char(1) ,
HOMEPROTECTOR_DISABILITY_IND char(1) , HOMEPROTECTOR_LIFE_IND char(1) , HR_DM_LIST_IND char(1) , LOAN_CLOSED_DEROGATORY_IND char(1)
, LOAN_IN_RESTRAINT_IND char(1) , LOAN_INSURANCE_DSBL_IND char(1) , LOAN_INSURANCE_LIFE_IND char(1) , LOAN_OPEN_CONSOLIDATION_IND
char(1) , MEDICAL_DENTAL_STUDENTS_IND char(1) , MORTGAGE_CLOSED_DEROGATORY_IND char(1) , MORTGAGE_IN_LEGAL_ACTION_IND char(1) ,
MORTGAGE_IN_RESTRAINT_IND char(1) , NEW_CLIENT_IND char(1) , NGEN_IND char(1) , NULL_NAME_IND char(1) , OTHER_EXCLUSION_IND char(1)
, PRIVATE_BANKING_IND char(1) , PRIVATE_BANKING_INTL_IND char(1) , RSP_IND char(1) , RSP_GROUP_IND char(1) , RSP_LOCKED_IN_IND
char(1) , STAFF_IND char(1) , STUDENT_LOAN_IND char(1) , STUDENT_LOAN_NOT_IN_RPYMNT_IND char(1) , STUDENT_SEGMENT_IND char(1) ,
TFSA_IND char(1) , TRIAGE_IND char(1) , TRIAGE_IN_PAST_12_MONTHS_IND char(1) , WRITTEN_OFF_PAST_6_YEARS_IND char(1) ,
APPOINTMENT_BOOKED_CNT integer , APPOINTMENT_KEPT_CNT integer , CREDIT_BUREAU_SCORE_VAL integer , CREDIT_BUREAU_SCORE_DT date ,
CRI_VAL integer , CSR_INBOUND_CNT integer , RD_INBOUND_CNT integer , RIGHT_PARTY_CONTACT_CNT integer , SOR_CD char(1) , PMTG_MDL
char(3) , PMTG_NMDL char(3) , BMTG char(3) , MGD_BM char(1) , APP_BM char(1) , GROUPADVANTAGE_IND char(1) , MOD100 smallint ,
BUSCARD_ONLY_PERS_IND char(1) , FRAUD_DEMARKETED_IND char(1) , NON_RES_DEMARKETED_IND char(1) , NO_PHOTO_ID_AML_IND char(1) ,
CRISIS_MGMT_IND char(1) , REGULATORY_IND char(1) , LOAD_DT date) primary index (CLNT_NO) ON COMMIT PRESERVE ROWS) by teradata;
MPRINT(RSUBMIT): /* execute ( COMMIT WORK ) by teradata;
MPRINT(RSUBMIT): */ execute(drop table &gbUserDataBase..&gbTablePrefix._abc) by teradata;
MPRINT(RSUBMIT): execute(create table &gbUserDataBase..&gbTablePrefix._abc as (select * from abc) with data primary index
(clnt_no)) by teradata;
MPRINT(RSUBMIT): quit;
MPRINT(RSUBMIT): options sastrace=',,,d' sastraceloc=saslog;
MPRINT(RSUBMIT): proc sql;
MPRINT(RSUBMIT): insert into TeraLib.abc select CLNT_NO , ACCT_MGR_NO , ACTV_INA_STS_CD , AGE_VAL , BIRTH_DT , BUS_SEG_CD ,
CLNT_SINCE_DT , CLNT_TYPE , ENTITLEMENT_CD , LANG_CD , PFA_IND , POSTAL_CD , PROVINCE_CD , RELN_MG_INST_NO , RELN_MG_UNIT_NO ,
STAFF_CLASS_CD , CLNT_TYPE_CD , RELATIONSHIP_MANAGEMENT_CD , MANAGED_BY_CD , SPLIT_CD , REGION_CD , LANGUAGE_QUEUE_CD ,
MANAGEMENT_TYPE_CD , CMP_FLAG , OPPCRIT_IND , MARCRIT_IND , MARCRITSTU_IND , ACTIVE_IND , ACTIVE_ONLINE_IND ,
ACTIVE_ONLINE_DI_DS_IND , ADDRESS_IN_CANADA_IND , MAILING_ADDRESS_IN_CANADA_IND , RES_ADDRESS_IN_CANADA_IND , AGE_OF_MAJORITY_IND ,
BALANCE_PROTECTOR_IND , BLIP_DISABILITY_IND , BLIP_LIFE_IND , BPOL_IND , CATO_CONTROL_TG9_IND , CLNT_FACING_STAFF_IND ,
COLLECTION_LENDING_TRANSIT_IND , DAY_TO_DAY_SAVINGS_IND , DDA_OPEN_ON_PRIMARY_IND , DDA_SNL_IND , DDA_VIP_IND , DECEASED_IND ,
UNTARGETABLE_POA_DECEASED_IND , DELINQUENT_IND , DEPRESSED_ECONOMIC_MARKETS_IND , EQUITY_NEAR_PRIME_MORTGAGE_IND ,
EBM_CONTROL_TG8_IND , ESAVINGS_CAD_OPEN_IND , ESAVINGS_CAD_OPEN_PRIMARY_IND , FIRST_NATIONS_MTG_LOAN_IND , FIRST_NATIONS_TRANSIT_IND
, FRAUD_VICTIMS_IVR_IND , HOMEPROTECTOR_DISABILITY_IND , HOMEPROTECTOR_LIFE_IND , HR_DM_LIST_IND , LOAN_CLOSED_DEROGATORY_IND ,
LOAN_IN_RESTRAINT_IND , LOAN_INSURANCE_DSBL_IND , LOAN_INSURANCE_LIFE_IND , LOAN_OPEN_CONSOLIDATION_IND ,
MEDICAL_DENTAL_STUDENTS_IND , MORTGAGE_CLOSED_DEROGATORY_IND , MORTGAGE_IN_LEGAL_ACTION_IND , MORTGAGE_IN_RESTRAINT_IND ,
NEW_CLIENT_IND , NGEN_IND , NULL_NAME_IND , OTHER_EXCLUSION_IND , PRIVATE_BANKING_IND , PRIVATE_BANKING_INTL_IND , RSP_IND ,
RSP_GROUP_IND , RSP_LOCKED_IN_IND , STAFF_IND , STUDENT_LOAN_IND , STUDENT_LOAN_NOT_IN_RPYMNT_IND , STUDENT_SEGMENT_IND , TFSA_IND ,
TRIAGE_IND , TRIAGE_IN_PAST_12_MONTHS_IND , WRITTEN_OFF_PAST_6_YEARS_IND , APPOINTMENT_BOOKED_CNT , APPOINTMENT_KEPT_CNT ,
CREDIT_BUREAU_SCORE_VAL , CREDIT_BUREAU_SCORE_DT , CRI_VAL , CSR_INBOUND_CNT , RD_INBOUND_CNT , RIGHT_PARTY_CONTACT_CNT , SOR_CD ,
PMTG_MDL , PMTG_NMDL , BMTG , MGD_BM , APP_BM , GROUPADVANTAGE_IND , MOD100 , BUSCARD_ONLY_PERS_IND , FRAUD_DEMARKETED_IND ,
NON_RES_DEMARKETED_IND , NO_PHOTO_ID_AML_IND , CRISIS_MGMT_IND , REGULATORY_IND , LOAD_DT from x.marcrit_sample10000 ;
MPRINT(RSUBMIT): quit;
511 options nocenter mprint symbolgen;
512
513 libname x "&gbRunDir/VolatileTbl" ;
SYMBOLGEN: Macro variable GBRUNDIR resolves to /sas/cidm2/DEV/leungs
NOTE: Libref X was successfully assigned as follows:
Engine: V9
Physical Name: /sas/cidm2/DEV/leungs/VolatileTbl
514 libname TeraLib teradata
SYMBOLGEN: Macro variable USER resolves to qjk8snk
SYMBOLGEN: Macro variable PSWD resolves to xxxxxxxx
514 ! user = &user password = &pswd connection=global dbmstemp=yes ;
NOTE: Libref TERALIB was successfully assigned as follows:
Engine: TERADATA
Physical Name:
515
516
517 proc sql noprint ;
518 %ConnectToTeradata ;
SYMBOLGEN: Macro variable INIT_MPRINT resolves to MPRINT
MPRINT(CONNECTTOTERADATA): options NOMPRINT ;
SYMBOLGEN: Macro variable USER resolves to qjk8snk
MPRINT(CONNECTTOTERADATA): connect to teradata (user=qjk8snk password=Xpswd mode=teradata) ;
SYMBOLGEN: Macro variable INIT_MPRINT resolves to MPRINT
MPRINT(CONNECTTOTERADATA): options MPRINT ;
SYMBOLGEN: Macro variable INIT_SYMBOLGEN resolves to SYMBOLGEN
SYMBOLGEN: Macro variable USER resolves to qjk8snk
SYMBOLGEN: Macro variable PSWD resolves to *******
MPRINT(CONNECTTOTERADATA): ;
519 execute (create volatile table abc (CLNT_NO integer not null , ACCT_MGR_NO integer , ACTV_INA_STS_CD char(1) , AGE_VAL
519 ! integer , BIRTH_DT date , BUS_SEG_CD integer ,
520 CLNT_SINCE_DT date , CLNT_TYPE char(1) , ENTITLEMENT_CD integer , LANG_CD char(2) , PFA_IND char(1) , POSTAL_CD char(6) ,
520 ! PROVINCE_CD char(2) , RELN_MG_INST_NO integer , RELN_MG_UNIT_NO decimal(11,0) , STAFF_CLASS_CD char(1) , CLNT_TYPE_CD char(1) ,
521 RELATIONSHIP_MANAGEMENT_CD char(5) , MANAGED_BY_CD char(1) , SPLIT_CD char(2) , REGION_CD char(3) , LANGUAGE_QUEUE_CD char(1) ,
521 ! MANAGEMENT_TYPE_CD char(2) , CMP_FLAG decimal(11,0) , OPPCRIT_IND char(1) , MARCRIT_IND char(1) , MARCRITSTU_IND char(1) ,
522 ACTIVE_IND char(1) , ACTIVE_ONLINE_IND char(1) , ACTIVE_ONLINE_DI_DS_IND char(1) , ADDRESS_IN_CANADA_IND char(1) ,
522 ! MAILING_ADDRESS_IN_CANADA_IND char(1) , RES_ADDRESS_IN_CANADA_IND char(1) , AGE_OF_MAJORITY_IND char(1) , BALANCE_PROTECTOR_IND
522 ! char(1) ,
523 BLIP_DISABILITY_IND char(1) , BLIP_LIFE_IND char(1) , BPOL_IND char(1) , CATO_CONTROL_TG9_IND char(1) , CLNT_FACING_STAFF_IND
523 ! char(1) , COLLECTION_LENDING_TRANSIT_IND char(1) , DAY_TO_DAY_SAVINGS_IND char(1) , DDA_OPEN_ON_PRIMARY_IND char(1) ,
523 ! DDA_SNL_IND
524 char(1) , DDA_VIP_IND char(1) , DECEASED_IND char(1) , UNTARGETABLE_POA_DECEASED_IND char(1) , DELINQUENT_IND char(1) ,
524 ! DEPRESSED_ECONOMIC_MARKETS_IND char(1) , EQUITY_NEAR_PRIME_MORTGAGE_IND char(1) , EBM_CONTROL_TG8_IND char(1) ,
524 ! ESAVINGS_CAD_OPEN_IND
525 char(1) , ESAVINGS_CAD_OPEN_PRIMARY_IND char(1) , FIRST_NATIONS_MTG_LOAN_IND char(1) , FIRST_NATIONS_TRANSIT_IND char(1) ,
525 ! FRAUD_VICTIMS_IVR_IND char(1) , HOMEPROTECTOR_DISABILITY_IND char(1) , HOMEPROTECTOR_LIFE_IND char(1) , HR_DM_LIST_IND char(1)
525 ! ,
526 LOAN_CLOSED_DEROGATORY_IND char(1) , LOAN_IN_RESTRAINT_IND char(1) , LOAN_INSURANCE_DSBL_IND char(1) , LOAN_INSURANCE_LIFE_IND
526 ! char(1) , LOAN_OPEN_CONSOLIDATION_IND char(1) , MEDICAL_DENTAL_STUDENTS_IND char(1) , MORTGAGE_CLOSED_DEROGATORY_IND char(1) ,
527 MORTGAGE_IN_LEGAL_ACTION_IND char(1) , MORTGAGE_IN_RESTRAINT_IND char(1) , NEW_CLIENT_IND char(1) , NGEN_IND char(1) ,
527 ! NULL_NAME_IND char(1) , OTHER_EXCLUSION_IND char(1) , PRIVATE_BANKING_IND char(1) , PRIVATE_BANKING_INTL_IND char(1) , RSP_IND
527 ! char(1) ,
528 RSP_GROUP_IND char(1) , RSP_LOCKED_IN_IND char(1) , STAFF_IND char(1) , STUDENT_LOAN_IND char(1) ,
528 ! STUDENT_LOAN_NOT_IN_RPYMNT_IND char(1) , STUDENT_SEGMENT_IND char(1) , TFSA_IND char(1) , TRIAGE_IND char(1) ,
528 ! TRIAGE_IN_PAST_12_MONTHS_IND char(1) ,
529 WRITTEN_OFF_PAST_6_YEARS_IND char(1) , APPOINTMENT_BOOKED_CNT integer , APPOINTMENT_KEPT_CNT integer , CREDIT_BUREAU_SCORE_VAL
529 ! integer , CREDIT_BUREAU_SCORE_DT date , CRI_VAL integer , CSR_INBOUND_CNT integer , RD_INBOUND_CNT integer ,
530 RIGHT_PARTY_CONTACT_CNT integer , SOR_CD char(1) , PMTG_MDL char(3) , PMTG_NMDL char(3) , BMTG char(3) , MGD_BM char(1) ,
530 ! APP_BM char(1) , GROUPADVANTAGE_IND char(1) , MOD100 smallint , BUSCARD_ONLY_PERS_IND char(1) , FRAUD_DEMARKETED_IND char(1) ,
531 NON_RES_DEMARKETED_IND char(1) , NO_PHOTO_ID_AML_IND char(1) , CRISIS_MGMT_IND char(1) , REGULATORY_IND char(1) , LOAD_DT date)
531 ! primary index (CLNT_NO) ON COMMIT PRESERVE ROWS) by teradata;
101 1532774989 trexec 0 SQL
TERADATA_0: Executed: on connection 1 102 1532774989 trexec 0 SQL
create volatile table abc (CLNT_NO integer not null , ACCT_MGR_NO integer , ACTV_INA_STS_CD char(1) , AGE_VAL integer , BIRTH_DT
date , BUS_SEG_CD integer , CLNT_SINCE_DT date , CLNT_TYPE char(1) , ENTITLEMENT_CD integer , LANG_CD char(2) , PFA_IND char(1) ,
POSTAL_CD char(6) , PROVINCE_CD char(2) , RELN_MG_INST_NO integer , RELN_MG_UNIT_NO decimal(11,0) , STAFF_CLASS_CD char(1) ,
CLNT_TYPE_CD char(1) , RELATIONSHIP_MANAGEMENT_CD char(5) , MANAGED_BY_CD char(1) , SPLIT_CD char(2) , REGION_CD char(3) ,
LANGUAGE_QUEUE_CD char(1) , MANAGEMENT_TYPE_CD char(2) , CMP_FLAG decimal(11,0) , OPPCRIT_IND char(1) , MARCRIT_IND char(1) ,
MARCRITSTU_IND char(1) , ACTIVE_IND char(1) , ACTIVE_ONLINE_IND char(1) , ACTIVE_ONLINE_DI_DS_IND char(1) , ADDRESS_IN_CANADA_IND
char(1) , MAILING_ADDRESS_IN_CANADA_IND char(1) , RES_ADDRESS_IN_CANADA_IND char(1) , AGE_OF_MAJORITY_IND char(1) ,
BALANCE_PROTECTOR_IND char(1) , BLIP_DISABILITY_IND char(1) , BLIP_LIFE_IND char(1) , BPOL_IND char(1) , CATO_CONTROL_TG9_IND
char(1) , CLNT_FACING_STAFF_IND char(1) , COLLECTION_LENDING_TRANSIT_IND char(1) , DAY_TO_DAY_SAVINGS_IND char(1) ,
DDA_OPEN_ON_PRIMARY_IND char(1) , DDA_SNL_IND char(1) , DDA_VIP_IND char(1) , DECEASED_IND char(1) , UNTARGETABLE_POA_DECEASED_IND
char(1) , DELINQUENT_IND char(1) , DEPRESSED_ECONOMIC_MARKETS_IND char(1) , EQUITY_NEAR_PRIME_MORTGAGE_IND char(1) ,
EBM_CONTROL_TG8_IND char(1) , ESAVINGS_CAD_OPEN_IND char(1) , ESAVINGS_CAD_OPEN_PRIMARY_IND char(1) , FIRST_NATIONS_MTG_LOAN_IND
char(1) , FIRST_NATIONS_TRANSIT_IND char(1) , FRAUD_VICTIMS_IVR_IND char(1) , HOMEPROTECTOR_DISABILITY_IND char(1) ,
HOMEPROTECTOR_LIFE_IND char(1) , HR_DM_LIST_IND char(1) , LOAN_CLOSED_DEROGATORY_IND char(1) , LOAN_IN_RESTRAINT_IND char(1) ,
LOAN_INSURANCE_DSBL_IND char(1) , LOAN_INSURANCE_LIFE_IND char(1) , LOAN_OPEN_CONSOLIDATION_IND char(1) ,
MEDICAL_DENTAL_STUDENTS_IND char(1) , MORTGAGE_CLOSED_DEROGATORY_IND char(1) , MORTGAGE_IN_LEGAL_ACTION_IND char(1) ,
MORTGAGE_IN_RESTRAINT_IND char(1) , NEW_CLIENT_IND char(1) , NGEN_IND char(1) , NULL_NAME_IND char(1) , OTHER_EXCLUSION_IND char(1)
, PRIVATE_BANKING_IND char(1) , PRIVATE_BANKING_INTL_IND char(1) , RSP_IND char(1) , RSP_GROUP_IND char(1) , RSP_LOCKED_IN_IND
char(1) , STAFF_IND char(1) , STUDENT_LOAN_IND char(1) , STUDENT_LOAN_NOT_IN_RPYMNT_IND char(1) , STUDENT_SEGMENT_IND char(1) ,
TFSA_IND char(1) , TRIAGE_IND char(1) , TRIAGE_IN_PAST_12_MONTHS_IND char(1) , WRITTEN_OFF_PAST_6_YEARS_IND char(1) ,
APPOINTMENT_BOOKED_CNT integer , APPOINTMENT_KEPT_CNT integer , CREDIT_BUREAU_SCORE_VAL integer , CREDIT_BUREAU_SCORE_DT date ,
CRI_VAL integer , CSR_INBOUND_CNT integer , RD_INBOUND_CNT integer , RIGHT_PARTY_CONTACT_CNT integer , SOR_CD char(1) , PMTG_MDL
char(3) , PMTG_NMDL char(3) , BMTG char(3) , MGD_BM char(1) , APP_BM char(1) , GROUPADVANTAGE_IND char(1) , MOD100 smallint ,
BUSCARD_ONLY_PERS_IND char(1) , FRAUD_DEMARKETED_IND char(1) , NON_RES_DEMARKETED_IND char(1) , NO_PHOTO_ID_AML_IND char(1) ,
CRISIS_MGMT_IND char(1) , REGULATORY_IND char(1) , LOAD_DT date) primary index (CLNT_NO) ON COMMIT PRESERVE ROWS 103 1532774989
trexec 0 SQL
104 1532774989 trexec 0 SQL
532 /*
533 execute ( COMMIT WORK ) by teradata;
534 */
535 execute(drop
SYMBOLGEN: Macro variable GBUSERDATABASE resolves to DDWUTDC
SYMBOLGEN: Macro variable GBTABLEPREFIX resolves to qjk8snk
535 ! table &gbUserDataBase..&gbTablePrefix._abc) by teradata;
105 1532774990 trexec 0 SQL
TERADATA_1: Executed: on connection 1 106 1532774990 trexec 0 SQL
drop table DDWUTDC.qjk8snk_abc 107 1532774990 trexec 0 SQL
108 1532774990 trexec 0 SQL
536
537 execute(create
SYMBOLGEN: Macro variable GBUSERDATABASE resolves to DDWUTDC
SYMBOLGEN: Macro variable GBTABLEPREFIX resolves to qjk8snk
537 ! table &gbUserDataBase..&gbTablePrefix._abc as (select * from abc) with data primary index (clnt_no)) by teradata
537 ! ;
109 1532774991 trexec 0 SQL
TERADATA_2: Executed: on connection 1 110 1532774991 trexec 0 SQL
create table DDWUTDC.qjk8snk_abc as (select * from abc) with data primary index (clnt_no) 111 1532774991 trexec 0 SQL
112 1532774991 trexec 0 SQL
538 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.53 seconds
cpu time 0.03 seconds


539
540
541 options sastrace=',,,d' sastraceloc=saslog;
542
543 proc sql;
544 insert into TeraLib.abc
545 select CLNT_NO , ACCT_MGR_NO , ACTV_INA_STS_CD , AGE_VAL , BIRTH_DT , BUS_SEG_CD , CLNT_SINCE_DT , CLNT_TYPE ,
545 ! ENTITLEMENT_CD , LANG_CD ,
546 PFA_IND , POSTAL_CD , PROVINCE_CD , RELN_MG_INST_NO , RELN_MG_UNIT_NO , STAFF_CLASS_CD , CLNT_TYPE_CD ,
546 ! RELATIONSHIP_MANAGEMENT_CD , MANAGED_BY_CD , SPLIT_CD , REGION_CD , LANGUAGE_QUEUE_CD , MANAGEMENT_TYPE_CD , CMP_FLAG ,
546 ! OPPCRIT_IND , MARCRIT_IND ,
547 MARCRITSTU_IND , ACTIVE_IND , ACTIVE_ONLINE_IND , ACTIVE_ONLINE_DI_DS_IND , ADDRESS_IN_CANADA_IND ,
547 ! MAILING_ADDRESS_IN_CANADA_IND , RES_ADDRESS_IN_CANADA_IND , AGE_OF_MAJORITY_IND , BALANCE_PROTECTOR_IND , BLIP_DISABILITY_IND ,
547 ! BLIP_LIFE_IND , BPOL_IND ,
548 CATO_CONTROL_TG9_IND , CLNT_FACING_STAFF_IND , COLLECTION_LENDING_TRANSIT_IND , DAY_TO_DAY_SAVINGS_IND ,
548 ! DDA_OPEN_ON_PRIMARY_IND , DDA_SNL_IND , DDA_VIP_IND , DECEASED_IND , UNTARGETABLE_POA_DECEASED_IND , DELINQUENT_IND ,
548 ! DEPRESSED_ECONOMIC_MARKETS_IND ,
549 EQUITY_NEAR_PRIME_MORTGAGE_IND , EBM_CONTROL_TG8_IND , ESAVINGS_CAD_OPEN_IND , ESAVINGS_CAD_OPEN_PRIMARY_IND ,
549 ! FIRST_NATIONS_MTG_LOAN_IND , FIRST_NATIONS_TRANSIT_IND , FRAUD_VICTIMS_IVR_IND , HOMEPROTECTOR_DISABILITY_IND ,
549 ! HOMEPROTECTOR_LIFE_IND ,
550 HR_DM_LIST_IND , LOAN_CLOSED_DEROGATORY_IND , LOAN_IN_RESTRAINT_IND , LOAN_INSURANCE_DSBL_IND , LOAN_INSURANCE_LIFE_IND ,
550 ! LOAN_OPEN_CONSOLIDATION_IND , MEDICAL_DENTAL_STUDENTS_IND , MORTGAGE_CLOSED_DEROGATORY_IND , MORTGAGE_IN_LEGAL_ACTION_IND ,
551 MORTGAGE_IN_RESTRAINT_IND , NEW_CLIENT_IND , NGEN_IND , NULL_NAME_IND , OTHER_EXCLUSION_IND , PRIVATE_BANKING_IND ,
551 ! PRIVATE_BANKING_INTL_IND , RSP_IND , RSP_GROUP_IND , RSP_LOCKED_IN_IND , STAFF_IND , STUDENT_LOAN_IND ,
551 ! STUDENT_LOAN_NOT_IN_RPYMNT_IND ,
552 STUDENT_SEGMENT_IND , TFSA_IND , TRIAGE_IND , TRIAGE_IN_PAST_12_MONTHS_IND , WRITTEN_OFF_PAST_6_YEARS_IND ,
552 ! APPOINTMENT_BOOKED_CNT , APPOINTMENT_KEPT_CNT , CREDIT_BUREAU_SCORE_VAL , CREDIT_BUREAU_SCORE_DT , CRI_VAL , CSR_INBOUND_CNT ,
552 ! RD_INBOUND_CNT ,
553 RIGHT_PARTY_CONTACT_CNT , SOR_CD , PMTG_MDL , PMTG_NMDL , BMTG , MGD_BM , APP_BM , GROUPADVANTAGE_IND , MOD100 ,
553 ! BUSCARD_ONLY_PERS_IND , FRAUD_DEMARKETED_IND , NON_RES_DEMARKETED_IND , NO_PHOTO_ID_AML_IND , CRISIS_MGMT_IND , REGULATORY_IND
553 ! , LOAD_DT from
554 x.marcrit_sample10000 ;
NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.
113 1532774993 trprep 0 SQL
TERADATA_3: Prepared: on connection 0 114 1532774993 trprep 0 SQL
SELECT * FROM "abc" 115 1532774993 trprep 0 SQL
116 1532774993 trprep 0 SQL
TERADATA: trforc: COMMIT WORK 117 1532774993 trforc 0 SQL
ERROR: File TERALIB.abc.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
555 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

NOTE: Remote submit to _SERVER_ complete.

Enthusiast

Re: load SAS dataset into Teradata Volatile table

cont. ...

However, my codes runs clean when using this same piece of codes to load SAS data into a Permanent TD table:

 

NOTE: Remote submit to _SERVER_ commencing.
MPRINT(RSUBMIT): %DropTable(&gbUserDataBase..&gbTablePrefix._abc);
MPRINT(RSUBMIT): proc sql noprint ;
MPRINT(RSUBMIT): %ConnectToTeradata ;
MPRINT(RSUBMIT): execute (create table &gbUserDataBase..&gbTablePrefix._abc (CLNT_NO integer not null , ACCT_MGR_NO integer ,
ACTV_INA_STS_CD char(1) , AGE_VAL integer , BIRTH_DT date , BUS_SEG_CD integer , CLNT_SINCE_DT date , CLNT_TYPE char(1) ,
ENTITLEMENT_CD integer , LANG_CD char(2) , PFA_IND char(1) , POSTAL_CD char(6) , PROVINCE_CD char(2) , RELN_MG_INST_NO integer ,
RELN_MG_UNIT_NO decimal(11,0) , STAFF_CLASS_CD char(1) , CLNT_TYPE_CD char(1) , RELATIONSHIP_MANAGEMENT_CD char(5) , MANAGED_BY_CD
char(1) , SPLIT_CD char(2) , REGION_CD char(3) , LANGUAGE_QUEUE_CD char(1) , MANAGEMENT_TYPE_CD char(2) , CMP_FLAG decimal(11,0) ,
OPPCRIT_IND char(1) , MARCRIT_IND char(1) , MARCRITSTU_IND char(1) , ACTIVE_IND char(1) , ACTIVE_ONLINE_IND char(1) ,
ACTIVE_ONLINE_DI_DS_IND char(1) , ADDRESS_IN_CANADA_IND char(1) , MAILING_ADDRESS_IN_CANADA_IND char(1) , RES_ADDRESS_IN_CANADA_IND
char(1) , AGE_OF_MAJORITY_IND char(1) , BALANCE_PROTECTOR_IND char(1) , BLIP_DISABILITY_IND char(1) , BLIP_LIFE_IND char(1) ,
BPOL_IND char(1) , CATO_CONTROL_TG9_IND char(1) , CLNT_FACING_STAFF_IND char(1) , COLLECTION_LENDING_TRANSIT_IND char(1) ,
DAY_TO_DAY_SAVINGS_IND char(1) , DDA_OPEN_ON_PRIMARY_IND char(1) , DDA_SNL_IND char(1) , DDA_VIP_IND char(1) , DECEASED_IND char(1)
, UNTARGETABLE_POA_DECEASED_IND char(1) , DELINQUENT_IND char(1) , DEPRESSED_ECONOMIC_MARKETS_IND char(1) ,
EQUITY_NEAR_PRIME_MORTGAGE_IND char(1) , EBM_CONTROL_TG8_IND char(1) , ESAVINGS_CAD_OPEN_IND char(1) , ESAVINGS_CAD_OPEN_PRIMARY_IND
char(1) , FIRST_NATIONS_MTG_LOAN_IND char(1) , FIRST_NATIONS_TRANSIT_IND char(1) , FRAUD_VICTIMS_IVR_IND char(1) ,
HOMEPROTECTOR_DISABILITY_IND char(1) , HOMEPROTECTOR_LIFE_IND char(1) , HR_DM_LIST_IND char(1) , LOAN_CLOSED_DEROGATORY_IND char(1)
, LOAN_IN_RESTRAINT_IND char(1) , LOAN_INSURANCE_DSBL_IND char(1) , LOAN_INSURANCE_LIFE_IND char(1) , LOAN_OPEN_CONSOLIDATION_IND
char(1) , MEDICAL_DENTAL_STUDENTS_IND char(1) , MORTGAGE_CLOSED_DEROGATORY_IND char(1) , MORTGAGE_IN_LEGAL_ACTION_IND char(1) ,
MORTGAGE_IN_RESTRAINT_IND char(1) , NEW_CLIENT_IND char(1) , NGEN_IND char(1) , NULL_NAME_IND char(1) , OTHER_EXCLUSION_IND char(1)
, PRIVATE_BANKING_IND char(1) , PRIVATE_BANKING_INTL_IND char(1) , RSP_IND char(1) , RSP_GROUP_IND char(1) , RSP_LOCKED_IN_IND
char(1) , STAFF_IND char(1) , STUDENT_LOAN_IND char(1) , STUDENT_LOAN_NOT_IN_RPYMNT_IND char(1) , STUDENT_SEGMENT_IND char(1) ,
TFSA_IND char(1) , TRIAGE_IND char(1) , TRIAGE_IN_PAST_12_MONTHS_IND char(1) , WRITTEN_OFF_PAST_6_YEARS_IND char(1) ,
APPOINTMENT_BOOKED_CNT integer , APPOINTMENT_KEPT_CNT integer , CREDIT_BUREAU_SCORE_VAL integer , CREDIT_BUREAU_SCORE_DT date ,
CRI_VAL integer , CSR_INBOUND_CNT integer , RD_INBOUND_CNT integer , RIGHT_PARTY_CONTACT_CNT integer , SOR_CD char(1) , PMTG_MDL
char(3) , PMTG_NMDL char(3) , BMTG char(3) , MGD_BM char(1) , APP_BM char(1) , GROUPADVANTAGE_IND char(1) , MOD100 smallint ,
BUSCARD_ONLY_PERS_IND char(1) , FRAUD_DEMARKETED_IND char(1) , NON_RES_DEMARKETED_IND char(1) , NO_PHOTO_ID_AML_IND char(1) ,
CRISIS_MGMT_IND char(1) , REGULATORY_IND char(1) , LOAD_DT date) primary index (CLNT_NO) ) by teradata;
SYMBOLGEN: Macro variable GBUSERDATABASE resolves to DDWUTDC
SYMBOLGEN: Macro variable GBTABLEPREFIX resolves to qjk8snk
477 %DropTable(&gbUserDataBase..&gbTablePrefix._abc);
MPRINT(DROPTABLE): options nomprint nonotes
SYMBOLGEN: Macro variable TABLENAME resolves to DDWUTDC.qjk8snk_abc
SYMBOLGEN: Macro variable TABLENAME resolves to DDWUTDC.qjk8snk_abc
SYMBOLGEN: Macro variable INIT_MPRINT resolves to NOMPRINT
SYMBOLGEN: Macro variable INIT_SYMBOLGEN resolves to SYMBOLGEN
SYMBOLGEN: Macro variable USER resolves to qjk8snk
SYMBOLGEN: Macro variable PSWD resolves to *******
SYMBOLGEN: Macro variable TABLENAME resolves to DDWUTDC.QJK8SNK_ABC
SYMBOLGEN: Macro variable TABLENAME resolves to DDWUTDC.QJK8SNK_ABC
74 1532772181 trprep 0 SQL
TERADATA_4: Prepared: on connection 1 75 1532772181 trprep 0 SQL
select count(*) from dbc.tables where upper(databasename) = 'DDWUTDC' and upper(tablename) = 'QJK8SNK_ABC' 76 1532772181 trprep 0
SQL
77 1532772181 trprep 0 SQL
78 1532772181 trget 0 SQL
TERADATA_5: Executed: on connection 1 79 1532772181 trget 0 SQL
select count(*) from dbc.tables where upper(databasename) = 'DDWUTDC' and upper(tablename) = 'QJK8SNK_ABC' 80 1532772181 trget 0 SQL

81 1532772181 trget 0 SQL
TERADATA: trget - rows to fetch: 1 82 1532772181 trget 0 SQL
----------------------------------------------------------------------------------------------------
SYMBOLGEN: Macro variable DTPRESENT resolves to 1
SYMBOLGEN: Macro variable INIT_MPRINT resolves to NOMPRINT
SYMBOLGEN: Macro variable INIT_SYMBOLGEN resolves to SYMBOLGEN
SYMBOLGEN: Macro variable USER resolves to qjk8snk
SYMBOLGEN: Macro variable PSWD resolves to *******
SYMBOLGEN: Macro variable TABLENAME resolves to DDWUTDC.QJK8SNK_ABC
83 1532772181 trexec 0 SQL
TERADATA_6: Executed: on connection 1 84 1532772181 trexec 0 SQL
drop table DDWUTDC.QJK8SNK_ABC 85 1532772181 trexec 0 SQL
86 1532772181 trexec 0 SQL
SYMBOLGEN: Macro variable SQLRC resolves to 0
SYMBOLGEN: Macro variable SQLXRC resolves to 0
SYMBOLGEN: Macro variable TABLENAME resolves to DDWUTDC.QJK8SNK_ABC
--- (DropTable) Table dropped: DDWUTDC.QJK8SNK_ABC
----------------------------------------------------------------------------------------------------
SYMBOLGEN: Macro variable DTMPRINT resolves to MPRINT
SYMBOLGEN: Macro variable DTNOTES resolves to NOTES
MPRINT(DROPTABLE): ;
478
479 proc sql noprint ;
480 %ConnectToTeradata ;
SYMBOLGEN: Macro variable INIT_MPRINT resolves to MPRINT
MPRINT(CONNECTTOTERADATA): options NOMPRINT ;
SYMBOLGEN: Macro variable USER resolves to qjk8snk
MPRINT(CONNECTTOTERADATA): connect to teradata (user=qjk8snk password=Xpswd mode=teradata) ;
SYMBOLGEN: Macro variable INIT_MPRINT resolves to MPRINT
MPRINT(CONNECTTOTERADATA): options MPRINT ;
SYMBOLGEN: Macro variable INIT_SYMBOLGEN resolves to SYMBOLGEN
SYMBOLGEN: Macro variable USER resolves to qjk8snk
SYMBOLGEN: Macro variable PSWD resolves to *******
MPRINT(CONNECTTOTERADATA): ;
481
482 execute (create
SYMBOLGEN: Macro variable GBUSERDATABASE resolves to DDWUTDC
SYMBOLGEN: Macro variable GBTABLEPREFIX resolves to qjk8snk
482 ! table &gbUserDataBase..&gbTablePrefix._abc (CLNT_NO integer not null , ACCT_MGR_NO integer , ACTV_INA_STS_CD
482 ! char(1) , AGE_VAL integer , BIRTH_DT date , BUS_SEG_CD integer ,
483 CLNT_SINCE_DT date , CLNT_TYPE char(1) , ENTITLEMENT_CD integer , LANG_CD char(2) , PFA_IND char(1) , POSTAL_CD char(6) ,
483 ! PROVINCE_CD char(2) , RELN_MG_INST_NO integer , RELN_MG_UNIT_NO decimal(11,0) , STAFF_CLASS_CD char(1) , CLNT_TYPE_CD char(1) ,
484 RELATIONSHIP_MANAGEMENT_CD char(5) , MANAGED_BY_CD char(1) , SPLIT_CD char(2) , REGION_CD char(3) , LANGUAGE_QUEUE_CD char(1) ,
484 ! MANAGEMENT_TYPE_CD char(2) , CMP_FLAG decimal(11,0) , OPPCRIT_IND char(1) , MARCRIT_IND char(1) , MARCRITSTU_IND char(1) ,
485 ACTIVE_IND char(1) , ACTIVE_ONLINE_IND char(1) , ACTIVE_ONLINE_DI_DS_IND char(1) , ADDRESS_IN_CANADA_IND char(1) ,
485 ! MAILING_ADDRESS_IN_CANADA_IND char(1) , RES_ADDRESS_IN_CANADA_IND char(1) , AGE_OF_MAJORITY_IND char(1) , BALANCE_PROTECTOR_IND
485 ! char(1) ,
486 BLIP_DISABILITY_IND char(1) , BLIP_LIFE_IND char(1) , BPOL_IND char(1) , CATO_CONTROL_TG9_IND char(1) , CLNT_FACING_STAFF_IND
486 ! char(1) , COLLECTION_LENDING_TRANSIT_IND char(1) , DAY_TO_DAY_SAVINGS_IND char(1) , DDA_OPEN_ON_PRIMARY_IND char(1) ,
486 ! DDA_SNL_IND
487 char(1) , DDA_VIP_IND char(1) , DECEASED_IND char(1) , UNTARGETABLE_POA_DECEASED_IND char(1) , DELINQUENT_IND char(1) ,
487 ! DEPRESSED_ECONOMIC_MARKETS_IND char(1) , EQUITY_NEAR_PRIME_MORTGAGE_IND char(1) , EBM_CONTROL_TG8_IND char(1) ,
487 ! ESAVINGS_CAD_OPEN_IND
488 char(1) , ESAVINGS_CAD_OPEN_PRIMARY_IND char(1) , FIRST_NATIONS_MTG_LOAN_IND char(1) , FIRST_NATIONS_TRANSIT_IND char(1) ,
488 ! FRAUD_VICTIMS_IVR_IND char(1) , HOMEPROTECTOR_DISABILITY_IND char(1) , HOMEPROTECTOR_LIFE_IND char(1) , HR_DM_LIST_IND char(1)
488 ! ,
489 LOAN_CLOSED_DEROGATORY_IND char(1) , LOAN_IN_RESTRAINT_IND char(1) , LOAN_INSURANCE_DSBL_IND char(1) , LOAN_INSURANCE_LIFE_IND
489 ! char(1) , LOAN_OPEN_CONSOLIDATION_IND char(1) , MEDICAL_DENTAL_STUDENTS_IND char(1) , MORTGAGE_CLOSED_DEROGATORY_IND char(1) ,
490 MORTGAGE_IN_LEGAL_ACTION_IND char(1) , MORTGAGE_IN_RESTRAINT_IND char(1) , NEW_CLIENT_IND char(1) , NGEN_IND char(1) ,
490 ! NULL_NAME_IND char(1) , OTHER_EXCLUSION_IND char(1) , PRIVATE_BANKING_IND char(1) , PRIVATE_BANKING_INTL_IND char(1) , RSP_IND
490 ! char(1) ,
491 RSP_GROUP_IND char(1) , RSP_LOCKED_IN_IND char(1) , STAFF_IND char(1) , STUDENT_LOAN_IND char(1) ,
491 ! STUDENT_LOAN_NOT_IN_RPYMNT_IND char(1) , STUDENT_SEGMENT_IND char(1) , TFSA_IND char(1) , TRIAGE_IND char(1) ,
491 ! TRIAGE_IN_PAST_12_MONTHS_IND char(1) ,
492 WRITTEN_OFF_PAST_6_YEARS_IND char(1) , APPOINTMENT_BOOKED_CNT integer , APPOINTMENT_KEPT_CNT integer , CREDIT_BUREAU_SCORE_VAL
492 ! integer , CREDIT_BUREAU_SCORE_DT date , CRI_VAL integer , CSR_INBOUND_CNT integer , RD_INBOUND_CNT integer ,
493 RIGHT_PARTY_CONTACT_CNT integer , SOR_CD char(1) , PMTG_MDL char(3) , PMTG_NMDL char(3) , BMTG char(3) , MGD_BM char(1) ,
493 ! APP_BM char(1) , GROUPADVANTAGE_IND char(1) , MOD100 smallint , BUSCARD_ONLY_PERS_IND char(1) , FRAUD_DEMARKETED_IND char(1) ,
494 NON_RES_DEMARKETED_IND char(1) , NO_PHOTO_ID_AML_IND char(1) , CRISIS_MGMT_IND char(1) , REGULATORY_IND char(1) , LOAD_DT date)
494 ! primary index (CLNT_NO) ) by teradata;
87 1532772183 trexec 0 SQL
TERADATA_7: Executed: on connection 1 88 1532772183 trexec 0 SQL
create table DDWUTDC.qjk8snk_abc (CLNT_NO integer not null , ACCT_MGR_NO integer , ACTV_INA_STS_CD char(1) , AGE_VAL integer ,
BIRTH_DT date , BUS_SEG_CD integer , CLNT_SINCE_DT date , CLNT_TYPE char(1) , ENTITLEMENT_CD integer , LANG_CD char(2) , PFA_IND
char(1) , POSTAL_CD char(6) , PROVINCE_CD char(2) , RELN_MG_INST_NO integer , RELN_MG_UNIT_NO decimal(11,0) , STAFF_CLASS_CD char(1)
, CLNT_TYPE_CD char(1) , RELATIONSHIP_MANAGEMENT_CD char(5) , MANAGED_BY_CD char(1) , SPLIT_CD char(2) , REGION_CD char(3) ,
LANGUAGE_QUEUE_CD char(1) , MANAGEMENT_TYPE_CD char(2) , CMP_FLAG decimal(11,0) , OPPCRIT_IND char(1) , MARCRIT_IND char(1) ,
MARCRITSTU_IND char(1) , ACTIVE_IND char(1) , ACTIVE_ONLINE_IND char(1) , ACTIVE_ONLINE_DI_DS_IND char(1) , ADDRESS_IN_CANADA_IND
char(1) , MAILING_ADDRESS_IN_CANADA_IND char(1) , RES_ADDRESS_IN_CANADA_IND char(1) , AGE_OF_MAJORITY_IND char(1) ,
BALANCE_PROTECTOR_IND char(1) , BLIP_DISABILITY_IND char(1) , BLIP_LIFE_IND char(1) , BPOL_IND char(1) , CATO_CONTROL_TG9_IND
char(1) , CLNT_FACING_STAFF_IND char(1) , COLLECTION_LENDING_TRANSIT_IND char(1) , DAY_TO_DAY_SAVINGS_IND char(1) ,
DDA_OPEN_ON_PRIMARY_IND char(1) , DDA_SNL_IND char(1) , DDA_VIP_IND char(1) , DECEASED_IND char(1) , UNTARGETABLE_POA_DECEASED_IND
char(1) , DELINQUENT_IND char(1) , DEPRESSED_ECONOMIC_MARKETS_IND char(1) , EQUITY_NEAR_PRIME_MORTGAGE_IND char(1) ,
EBM_CONTROL_TG8_IND char(1) , ESAVINGS_CAD_OPEN_IND char(1) , ESAVINGS_CAD_OPEN_PRIMARY_IND char(1) , FIRST_NATIONS_MTG_LOAN_IND
char(1) , FIRST_NATIONS_TRANSIT_IND char(1) , FRAUD_VICTIMS_IVR_IND char(1) , HOMEPROTECTOR_DISABILITY_IND char(1) ,
HOMEPROTECTOR_LIFE_IND char(1) , HR_DM_LIST_IND char(1) , LOAN_CLOSED_DEROGATORY_IND char(1) , LOAN_IN_RESTRAINT_IND char(1) ,
LOAN_INSURANCE_DSBL_IND char(1) , LOAN_INSURANCE_LIFE_IND char(1) , LOAN_OPEN_CONSOLIDATION_IND char(1) ,
MEDICAL_DENTAL_STUDENTS_IND char(1) , MORTGAGE_CLOSED_DEROGATORY_IND char(1) , MORTGAGE_IN_LEGAL_ACTION_IND char(1) ,
MORTGAGE_IN_RESTRAINT_IND char(1) , NEW_CLIENT_IND char(1) , NGEN_IND char(1) , NULL_NAME_IND char(1) , OTHER_EXCLUSION_IND char(1)
, PRIVATE_BANKING_IND char(1) , PRIVATE_BANKING_INTL_IND char(1) , RSP_IND char(1) , RSP_GROUP_IND char(1) , RSP_LOCKED_IN_IND
char(1) , STAFF_IND char(1) , STUDENT_LOAN_IND char(1) , STUDENT_LOAN_NOT_IN_RPYMNT_IND char(1) , STUDENT_SEGMENT_IND char(1) ,
TFSA_IND char(1) , TRIAGE_IND char(1) , TRIAGE_IN_PAST_12_MONTHS_IND char(1) , WRITTEN_OFF_PAST_6_YEARS_IND char(1) ,
APPOINTMENT_BOOKED_CNT integer , APPOINTMENT_KEPT_CNT integer , CREDIT_BUREAU_SCORE_VAL integer , CREDIT_BUREAU_SCORE_DT date ,
CRI_VAL integer , CSR_INBOUND_CNT integer , RD_INBOUND_CNT integer , RIGHT_PARTY_CONTACT_CNT integer , SOR_CD char(1) , PMTG_MDL
char(3) , PMTG_NMDL char(3) , BMTG char(3) , MGD_BM char(1) , APP_BM char(1) , GROUPADVANTAGE_IND char(1) , MOD100 smallint ,
BUSCARD_ONLY_PERS_IND char(1) , FRAUD_DEMARKETED_IND char(1) , NON_RES_DEMARKETED_IND char(1) , NO_PHOTO_ID_AML_IND char(1) ,
CRISIS_MGMT_IND char(1) , REGULATORY_IND char(1) , LOAD_DT date) primary index (CLNT_NO) 89 1532772183 trexec 0 SQL
90 1532772183 trexec 0 SQL
NOTE: Remote submit to _SERVER_ complete.
NOTE: Remote submit to _SERVER_ commencing.
MPRINT(RSUBMIT): libname TeraLib teradata user = &user password = &pswd connection=global database = &gbUserDataBase ;
MPRINT(RSUBMIT): proc sql;
MPRINT(RSUBMIT): insert into TeraLib.&gbTablePrefix._abc (fastload = yes tpt=no sessions = 4) select CLNT_NO , ACCT_MGR_NO ,
ACTV_INA_STS_CD , AGE_VAL , BIRTH_DT , BUS_SEG_CD , CLNT_SINCE_DT , CLNT_TYPE , ENTITLEMENT_CD , LANG_CD , PFA_IND , POSTAL_CD ,
PROVINCE_CD , RELN_MG_INST_NO , RELN_MG_UNIT_NO , STAFF_CLASS_CD , CLNT_TYPE_CD , RELATIONSHIP_MANAGEMENT_CD , MANAGED_BY_CD ,
SPLIT_CD , REGION_CD , LANGUAGE_QUEUE_CD , MANAGEMENT_TYPE_CD , CMP_FLAG , OPPCRIT_IND , MARCRIT_IND , MARCRITSTU_IND , ACTIVE_IND ,
ACTIVE_ONLINE_IND , ACTIVE_ONLINE_DI_DS_IND , ADDRESS_IN_CANADA_IND , MAILING_ADDRESS_IN_CANADA_IND , RES_ADDRESS_IN_CANADA_IND ,
AGE_OF_MAJORITY_IND , BALANCE_PROTECTOR_IND , BLIP_DISABILITY_IND , BLIP_LIFE_IND , BPOL_IND , CATO_CONTROL_TG9_IND ,
CLNT_FACING_STAFF_IND , COLLECTION_LENDING_TRANSIT_IND , DAY_TO_DAY_SAVINGS_IND , DDA_OPEN_ON_PRIMARY_IND , DDA_SNL_IND ,
DDA_VIP_IND , DECEASED_IND , UNTARGETABLE_POA_DECEASED_IND , DELINQUENT_IND , DEPRESSED_ECONOMIC_MARKETS_IND ,
EQUITY_NEAR_PRIME_MORTGAGE_IND , EBM_CONTROL_TG8_IND , ESAVINGS_CAD_OPEN_IND , ESAVINGS_CAD_OPEN_PRIMARY_IND ,
FIRST_NATIONS_MTG_LOAN_IND , FIRST_NATIONS_TRANSIT_IND , FRAUD_VICTIMS_IVR_IND , HOMEPROTECTOR_DISABILITY_IND ,
HOMEPROTECTOR_LIFE_IND , HR_DM_LIST_IND , LOAN_CLOSED_DEROGATORY_IND , LOAN_IN_RESTRAINT_IND , LOAN_INSURANCE_DSBL_IND ,
LOAN_INSURANCE_LIFE_IND , LOAN_OPEN_CONSOLIDATION_IND , MEDICAL_DENTAL_STUDENTS_IND , MORTGAGE_CLOSED_DEROGATORY_IND ,
MORTGAGE_IN_LEGAL_ACTION_IND , MORTGAGE_IN_RESTRAINT_IND , NEW_CLIENT_IND , NGEN_IND , NULL_NAME_IND , OTHER_EXCLUSION_IND ,
PRIVATE_BANKING_IND , PRIVATE_BANKING_INTL_IND , RSP_IND , RSP_GROUP_IND , RSP_LOCKED_IN_IND , STAFF_IND , STUDENT_LOAN_IND ,
STUDENT_LOAN_NOT_IN_RPYMNT_IND , STUDENT_SEGMENT_IND , TFSA_IND , TRIAGE_IND , TRIAGE_IN_PAST_12_MONTHS_IND ,
WRITTEN_OFF_PAST_6_YEARS_IND , APPOINTMENT_BOOKED_CNT , APPOINTMENT_KEPT_CNT , CREDIT_BUREAU_SCORE_VAL , CREDIT_BUREAU_SCORE_DT ,
CRI_VAL , CSR_INBOUND_CNT , RD_INBOUND_CNT , RIGHT_PARTY_CONTACT_CNT , SOR_CD , PMTG_MDL , PMTG_NMDL , BMTG , MGD_BM , APP_BM ,
GROUPADVANTAGE_IND , MOD100 , BUSCARD_ONLY_PERS_IND , FRAUD_DEMARKETED_IND , NON_RES_DEMARKETED_IND , NO_PHOTO_ID_AML_IND ,
CRISIS_MGMT_IND , REGULATORY_IND , LOAD_DT from x.marcrit_sample10000 ;
MPRINT(RSUBMIT): quit;
495 libname TeraLib teradata
SYMBOLGEN: Macro variable USER resolves to qjk8snk
SYMBOLGEN: Macro variable PSWD resolves to *********
SYMBOLGEN: Macro variable GBUSERDATABASE resolves to DDWUTDC
495 ! user = &user password = &pswd connection=global database = &gbUserDataBase ;
NOTE: Libref TERALIB was successfully assigned as follows:
Engine: TERADATA
Physical Name:
496
NOTE: PROCEDURE SQL used (Total process time):
real time 42.24 seconds
cpu time 0.03 seconds


497 proc sql;
498 insert into TeraLib.&gbTablePrefix._abc (fastload = yes tpt=no sessions = 4)
SYMBOLGEN: Macro variable GBTABLEPREFIX resolves to qjk8snk
499 select CLNT_NO , ACCT_MGR_NO , ACTV_INA_STS_CD , AGE_VAL , BIRTH_DT , BUS_SEG_CD , CLNT_SINCE_DT , CLNT_TYPE ,
499 ! ENTITLEMENT_CD , LANG_CD ,
500 PFA_IND , POSTAL_CD , PROVINCE_CD , RELN_MG_INST_NO , RELN_MG_UNIT_NO , STAFF_CLASS_CD , CLNT_TYPE_CD ,
500 ! RELATIONSHIP_MANAGEMENT_CD , MANAGED_BY_CD , SPLIT_CD , REGION_CD , LANGUAGE_QUEUE_CD , MANAGEMENT_TYPE_CD , CMP_FLAG ,
500 ! OPPCRIT_IND , MARCRIT_IND ,
501 MARCRITSTU_IND , ACTIVE_IND , ACTIVE_ONLINE_IND , ACTIVE_ONLINE_DI_DS_IND , ADDRESS_IN_CANADA_IND ,
501 ! MAILING_ADDRESS_IN_CANADA_IND , RES_ADDRESS_IN_CANADA_IND , AGE_OF_MAJORITY_IND , BALANCE_PROTECTOR_IND , BLIP_DISABILITY_IND ,
501 ! BLIP_LIFE_IND , BPOL_IND ,
502 CATO_CONTROL_TG9_IND , CLNT_FACING_STAFF_IND , COLLECTION_LENDING_TRANSIT_IND , DAY_TO_DAY_SAVINGS_IND ,
502 ! DDA_OPEN_ON_PRIMARY_IND , DDA_SNL_IND , DDA_VIP_IND , DECEASED_IND , UNTARGETABLE_POA_DECEASED_IND , DELINQUENT_IND ,
502 ! DEPRESSED_ECONOMIC_MARKETS_IND ,
503 EQUITY_NEAR_PRIME_MORTGAGE_IND , EBM_CONTROL_TG8_IND , ESAVINGS_CAD_OPEN_IND , ESAVINGS_CAD_OPEN_PRIMARY_IND ,
503 ! FIRST_NATIONS_MTG_LOAN_IND , FIRST_NATIONS_TRANSIT_IND , FRAUD_VICTIMS_IVR_IND , HOMEPROTECTOR_DISABILITY_IND ,
503 ! HOMEPROTECTOR_LIFE_IND ,
504 HR_DM_LIST_IND , LOAN_CLOSED_DEROGATORY_IND , LOAN_IN_RESTRAINT_IND , LOAN_INSURANCE_DSBL_IND , LOAN_INSURANCE_LIFE_IND ,
504 ! LOAN_OPEN_CONSOLIDATION_IND , MEDICAL_DENTAL_STUDENTS_IND , MORTGAGE_CLOSED_DEROGATORY_IND , MORTGAGE_IN_LEGAL_ACTION_IND ,
505 MORTGAGE_IN_RESTRAINT_IND , NEW_CLIENT_IND , NGEN_IND , NULL_NAME_IND , OTHER_EXCLUSION_IND , PRIVATE_BANKING_IND ,
505 ! PRIVATE_BANKING_INTL_IND , RSP_IND , RSP_GROUP_IND , RSP_LOCKED_IN_IND , STAFF_IND , STUDENT_LOAN_IND ,
505 ! STUDENT_LOAN_NOT_IN_RPYMNT_IND ,
506 STUDENT_SEGMENT_IND , TFSA_IND , TRIAGE_IND , TRIAGE_IN_PAST_12_MONTHS_IND , WRITTEN_OFF_PAST_6_YEARS_IND ,
506 ! APPOINTMENT_BOOKED_CNT , APPOINTMENT_KEPT_CNT , CREDIT_BUREAU_SCORE_VAL , CREDIT_BUREAU_SCORE_DT , CRI_VAL , CSR_INBOUND_CNT ,
506 ! RD_INBOUND_CNT ,
507 RIGHT_PARTY_CONTACT_CNT , SOR_CD , PMTG_MDL , PMTG_NMDL , BMTG , MGD_BM , APP_BM , GROUPADVANTAGE_IND , MOD100 ,
507 ! BUSCARD_ONLY_PERS_IND , FRAUD_DEMARKETED_IND , NON_RES_DEMARKETED_IND , NO_PHOTO_ID_AML_IND , CRISIS_MGMT_IND , REGULATORY_IND
507 ! , LOAD_DT from
508 x.marcrit_sample10000 ;
NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.
91 1532772225 trprep 0 SQL
TERADATA_8: Prepared: on connection 0 92 1532772225 trprep 0 SQL
SELECT * FROM "DDWUTDC"."qjk8snk_abc" 93 1532772225 trprep 0 SQL
94 1532772225 trprep 0 SQL
95 1532772227 trins 0 SQL
TERADATA_9: Prepared: on connection 0 96 1532772227 trins 0 SQL
USING ("CLNT_NO" INTEGER,"ACCT_MGR_NO" INTEGER,"ACTV_INA_STS_CD" CHAR (1),"AGE_VAL" INTEGER,"BIRTH_DT" DATE,"BUS_SEG_CD"
INTEGER,"CLNT_SINCE_DT" DATE,"CLNT_TYPE" CHAR (1),"ENTITLEMENT_CD" INTEGER,"LANG_CD" CHAR (2),"PFA_IND" CHAR (1),"POSTAL_CD" CHAR
(6),"PROVINCE_CD" CHAR (2),"RELN_MG_INST_NO" INTEGER,"RELN_MG_UNIT_NO" DECIMAL(11),"STAFF_CLASS_CD" CHAR (1),"CLNT_TYPE_CD" CHAR
(1),"RELATIONSHIP_MANAGEMENT_CD" CHAR (5),"MANAGED_BY_CD" CHAR (1),"SPLIT_CD" CHAR (2),"REGION_CD" CHAR (3),"LANGUAGE_QUEUE_CD" CHAR
(1),"MANAGEMENT_TYPE_CD" CHAR (2),"CMP_FLAG" DECIMAL(11),"OPPCRIT_IND" CHAR (1),"MARCRIT_IND" CHAR (1),"MARCRITSTU_IND" CHAR
(1),"ACTIVE_IND" CHAR (1),"ACTIVE_ONLINE_IND" CHAR (1),"ACTIVE_ONLINE_DI_DS_IND" CHAR (1),"ADDRESS_IN_CANADA_IND" CHAR
(1),"MAILING_ADDRESS_IN_CANADA_IND" CHAR (1),"RES_ADDRESS_IN_CANADA_IND" CHAR (1),"AGE_OF_MAJORITY_IND" CHAR
(1),"BALANCE_PROTECTOR_IND" CHAR (1),"BLIP_DISABILITY_IND" CHAR (1),"BLIP_LIFE_IND" CHAR (1),"BPOL_IND" CHAR
(1),"CATO_CONTROL_TG9_IND" CHAR (1),"CLNT_FACING_STAFF_IND" CHAR (1),"COLLECTION_LENDING_TRANSIT_IND" CHAR
(1),"DAY_TO_DAY_SAVINGS_IND" CHAR (1),"DDA_OPEN_ON_PRIMARY_IND" CHAR (1),"DDA_SNL_IND" CHAR (1),"DDA_VIP_IND" CHAR
(1),"DECEASED_IND" CHAR (1),"UNTARGETABLE_POA_DECEASED_IND" CHAR (1),"DELINQUENT_IND" CHAR (1),"DEPRESSED_ECONOMIC_MARKETS_IND" CHAR
(1),"EQUITY_NEAR_PRIME_MORTGAGE_IND" CHAR (1),"EBM_CONTROL_TG8_IND" CHAR (1),"ESAVINGS_CAD_OPEN_IND" CHAR
(1),"ESAVINGS_CAD_OPEN_PRIMARY_IND" CHAR (1),"FIRST_NATIONS_MTG_LOAN_IND" CHAR (1),"FIRST_NATIONS_TRANSIT_IND" CHAR
(1),"FRAUD_VICTIMS_IVR_IND" CHAR (1),"HOMEPROTECTOR_DISABILITY_IND" CHAR (1),"HOMEPROTECTOR_LIFE_IND" CHAR (1),"HR_DM_LIST_IND" CHAR
(1),"LOAN_CLOSED_DEROGATORY_IND" CHAR (1),"LOAN_IN_RESTRAINT_IND" CHAR (1),"LOAN_INSURANCE_DSBL_IND" CHAR
(1),"LOAN_INSURANCE_LIFE_IND" CHAR (1),"LOAN_OPEN_CONSOLIDATION_IND" CHAR (1),"MEDICAL_DENTAL_STUDENTS_IND" CHAR
(1),"MORTGAGE_CLOSED_DEROGATORY_IND" CHAR (1),"MORTGAGE_IN_LEGAL_ACTION_IND" CHAR (1),"MORTGAGE_IN_RESTRAINT_IND" CHAR
(1),"NEW_CLIENT_IND" CHAR (1),"NGEN_IND" CHAR (1),"NULL_NAME_IND" CHAR (1),"OTHER_EXCLUSION_IND" CHAR (1),"PRIVATE_BANKING_IND" CHAR
(1),"PRIVATE_BANKING_INTL_IND" CHAR (1),"RSP_IND" CHAR (1),"RSP_GROUP_IND" CHAR (1),"RSP_LOCKED_IN_IND" CHAR (1),"STAFF_IND" CHAR
(1),"STUDENT_LOAN_IND" CHAR (1),"STUDENT_LOAN_NOT_IN_RPYMNT_IND" CHAR (1),"STUDENT_SEGMENT_IND" CHAR (1),"TFSA_IND" CHAR
(1),"TRIAGE_IND" CHAR (1),"TRIAGE_IN_PAST_12_MONTHS_IND" CHAR (1),"WRITTEN_OFF_PAST_6_YEARS_IND" CHAR (1),"APPOINTMENT_BOOKED_CNT"
INTEGER,"APPOINTMENT_KEPT_CNT" INTEGER,"CREDIT_BUREAU_SCORE_VAL" INTEGER,"CREDIT_BUREAU_SCORE_DT" DATE,"CRI_VAL"
INTEGER,"CSR_INBOUND_CNT" INTEGER,"RD_INBOUND_CNT" INTEGER,"RIGHT_PARTY_CONTACT_CNT" INTEGER,"SOR_CD" CHAR (1),"PMTG_MDL" CHAR
(3),"PMTG_NMDL" CHAR (3),"BMTG" CHAR (3),"MGD_BM" CHAR (1),"APP_BM" CHAR (1),"GROUPADVANTAGE_IND" CHAR (1),"MOD100"
SMALLINT,"BUSCARD_ONLY_PERS_IND" CHAR (1),"FRAUD_DEMARKETED_IND" CHAR (1),"NON_RES_DEMARKETED_IND" CHAR (1),"NO_PHOTO_ID_AML_IND"
CHAR (1),"CRISIS_MGMT_IND" CHAR (1),"REGULATORY_IND" CHAR (1),"LOAD_DT" DATE)INSERT INTO "DDWUTDC"."qjk8snk_abc"
("CLNT_NO","ACCT_MGR_NO","ACTV_INA_STS_CD","AGE_VAL","BIRTH_DT","BUS_SEG_CD","CLNT_SINCE_DT","CLNT_TYPE","ENTITLEMENT_CD","LANG_CD","
PFA_IND","POSTAL_CD","PROVINCE_CD","RELN_MG_INST_NO","RELN_MG_UNIT_NO","STAFF_CLASS_CD","CLNT_TYPE_CD","RELATIONSHIP_MANAGEMENT_CD","
MANAGED_BY_CD","SPLIT_CD","REGION_CD","LANGUAGE_QUEUE_CD","MANAGEMENT_TYPE_CD","CMP_FLAG","OPPCRIT_IND","MARCRIT_IND","MARCRITSTU_IND
","ACTIVE_IND","ACTIVE_ONLINE_IND","ACTIVE_ONLINE_DI_DS_IND","ADDRESS_IN_CANADA_IND","MAILING_ADDRESS_IN_CANADA_IND","RES_ADDRESS_IN_
CANADA_IND","AGE_OF_MAJORITY_IND","BALANCE_PROTECTOR_IND","BLIP_DISABILITY_IND","BLIP_LIFE_IND","BPOL_IND","CATO_CONTROL_TG9_IND","CL
NT_FACING_STAFF_IND","COLLECTION_LENDING_TRANSIT_IND","DAY_TO_DAY_SAVINGS_IND","DDA_OPEN_ON_PRIMARY_IND","DDA_SNL_IND","DDA_VIP_IND",
"DECEASED_IND","UNTARGETABLE_POA_DECEASED_IND","DELINQUENT_IND","DEPRESSED_ECONOMIC_MARKETS_IND","EQUITY_NEAR_PRIME_MORTGAGE_IND","EB
M_CONTROL_TG8_IND","ESAVINGS_CAD_OPEN_IND","ESAVINGS_CAD_OPEN_PRIMARY_IND","FIRST_NATIONS_MTG_LOAN_IND","FIRST_NATIONS_TRANSIT_IND","
FRAUD_VICTIMS_IVR_IND","HOMEPROTECTOR_DISABILITY_IND","HOMEPROTECTOR_LIFE_IND","HR_DM_LIST_IND","LOAN_CLOSED_DEROGATORY_IND","LOAN_IN
_RESTRAINT_IND","LOAN_INSURANCE_DSBL_IND","LOAN_INSURANCE_LIFE_IND","LOAN_OPEN_CONSOLIDATION_IND","MEDICAL_DENTAL_STUDENTS_IND","MORT
GAGE_CLOSED_DEROGATORY_IND","MORTGAGE_IN_LEGAL_ACTION_IND","MORTGAGE_IN_RESTRAINT_IND","NEW_CLIENT_IND","NGEN_IND","NULL_NAME_IND","O
THER_EXCLUSION_IND","PRIVATE_BANKING_IND","PRIVATE_BANKING_INTL_IND","RSP_IND","RSP_GROUP_IND","RSP_LOCKED_IN_IND","STAFF_IND","STUDE
NT_LOAN_IND","STUDENT_LOAN_NOT_IN_RPYMNT_IND","STUDENT_SEGMENT_IND","TFSA_IND","TRIAGE_IND","TRIAGE_IN_PAST_12_MONTHS_IND","WRITTEN_O
FF_PAST_6_YEARS_IND","APPOINTMENT_BOOKED_CNT","APPOINTMENT_KEPT_CNT","CREDIT_BUREAU_SCORE_VAL","CREDIT_BUREAU_SCORE_DT","CRI_VAL","CS
R_INBOUND_CNT","RD_INBOUND_CNT","RIGHT_PARTY_CONTACT_CNT","SOR_CD","PMTG_MDL","PMTG_NMDL","BMTG","MGD_BM","APP_BM","GROUPADVANTAGE_IN
D","MOD100","BUSCARD_ONLY_PERS_IND","FRAUD_DEMARKETED_IND","NON_RES_DEMARKETED_IND","NO_PHOTO_ID_AML_IND","CRISIS_MGMT_IND","REGULATO
RY_IND","LOAD_DT") VALUES
(:"CLNT_NO",:"ACCT_MGR_NO",:"ACTV_INA_STS_CD",:"AGE_VAL",:"BIRTH_DT",:"BUS_SEG_CD",:"CLNT_SINCE_DT",:"CLNT_TYPE",:"ENTITLEMENT_CD",:"
LANG_CD",:"PFA_IND",:"POSTAL_CD",:"PROVINCE_CD",:"RELN_MG_INST_NO",:"RELN_MG_UNIT_NO",:"STAFF_CLASS_CD",:"CLNT_TYPE_CD",:"RELATIONSHI
P_MANAGEMENT_CD",:"MANAGED_BY_CD",:"SPLIT_CD",:"REGION_CD",:"LANGUAGE_QUEUE_CD",:"MANAGEMENT_TYPE_CD",:"CMP_FLAG",:"OPPCRIT_IND",:"MA
RCRIT_IND",:"MARCRITSTU_IND",:"ACTIVE_IND",:"ACTIVE_ONLINE_IND",:"ACTIVE_ONLINE_DI_DS_IND",:"ADDRESS_IN_CANADA_IND",:"MAILING_ADDRESS
_IN_CANADA_IND",:"RES_ADDRESS_IN_CANADA_IND",:"AGE_OF_MAJORITY_IND",:"BALANCE_PROTECTOR_IND",:"BLIP_DISABILITY_IND",:"BLIP_LIFE_IND",
:"BPOL_IND",:"CATO_CONTROL_TG9_IND",:"CLNT_FACING_STAFF_IND",:"COLLECTION_LENDING_TRANSIT_IND",:"DAY_TO_DAY_SAVINGS_IND",:"DDA_OPEN_O
N_PRIMARY_IND",:"DDA_SNL_IND",:"DDA_VIP_IND",:"DECEASED_IND",:"UNTARGETABLE_POA_DECEASED_IND",:"DELINQUENT_IND",:"DEPRESSED_ECONOMIC_
MARKETS_IND",:"EQUITY_NEAR_PRIME_MORTGAGE_IND",:"EBM_CONTROL_TG8_IND",:"ESAVINGS_CAD_OPEN_IND",:"ESAVINGS_CAD_OPEN_PRIMARY_IND",:"FIR
ST_NATIONS_MTG_LOAN_IND",:"FIRST_NATIONS_TRANSIT_IND",:"FRAUD_VICTIMS_IVR_IND",:"HOMEPROTECTOR_DISABILITY_IND",:"HOMEPROTECTOR_LIFE_I
ND",:"HR_DM_LIST_IND",:"LOAN_CLOSED_DEROGATORY_IND",:"LOAN_IN_RESTRAINT_IND",:"LOAN_INSURANCE_DSBL_IND",:"LOAN_INSURANCE_LIFE_IND",:"
LOAN_OPEN_CONSOLIDATION_IND",:"MEDICAL_DENTAL_STUDENTS_IND",:"MORTGAGE_CLOSED_DEROGATORY_IND",:"MORTGAGE_IN_LEGAL_ACTION_IND",:"MORTG
AGE_IN_RESTRAINT_IND",:"NEW_CLIENT_IND",:"NGEN_IND",:"NULL_NAME_IND",:"OTHER_EXCLUSION_IND",:"PRIVATE_BANKING_IND",:"PRIVATE_BANKING_
INTL_IND",:"RSP_IND",:"RSP_GROUP_IND",:"RSP_LOCKED_IN_IND",:"STAFF_IND",:"STUDENT_LOAN_IND",:"STUDENT_LOAN_NOT_IN_RPYMNT_IND",:"STUDE
NT_SEGMENT_IND",:"TFSA_IND",:"TRIAGE_IND",:"TRIAGE_IN_PAST_12_MONTHS_IND",:"WRITTEN_OFF_PAST_6_YEARS_IND",:"APPOINTMENT_BOOKED_CNT",:
"APPOINTMENT_KEPT_CNT",:"CREDIT_BUREAU_SCORE_VAL",:"CREDIT_BUREAU_SCORE_DT",:"CRI_VAL",:"CSR_INBOUND_CNT",:"RD_INBOUND_CNT",:"RIGHT_P
ARTY_CONTACT_CNT",:"SOR_CD",:"PMTG_MDL",:"PMTG_NMDL",:"BMTG",:"MGD_BM",:"APP_BM",:"GROUPADVANTAGE_IND",:"MOD100",:"BUSCARD_ONLY_PERS_
IND",:"FRAUD_DEMARKETED_IND",:"NON_RES_DEMARKETED_IND",:"NO_PHOTO_ID_AML_IND",:"CRISIS_MGMT_IND",:"REGULATORY_IND",:"LOAD_DT") 97
1532772227 trins 0 SQL
98 1532772227 trins 0 SQL
TERADATA: trforc: COMMIT WORK 99 1532772227 trforc 0 SQL
NOTE: 10000 rows were inserted into TERALIB.qjk8snk_abc.

TERADATA: trforc: COMMIT WORK 100 1532772230 trforc 0 SQL
509
510 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 5.31 seconds
cpu time 0.12 seconds

NOTE: Remote submit to _SERVER_ complete.

 

Apparently, volatile table is not able to load in the SAS table as it sees them as "heterogeneous data sources" and it cannot find the volatile table TERALIB.abc despite the fact it has been created with no data in it.


Can someone tell me why a Permanent table can be loaded with a pass thru libname SAS data vs. a Volatile table that cannot?

 

How can I load a SAS table into a Volatile table using a Teradata libname pass-thru?

 

Thanks

Stephen

Teradata Employee

Re: load SAS dataset into Teradata Volatile table

Volatile tables belong entirely locally to a single session. They cannot be see or operated upon by any other session.

 

SQL Assistant will be a different session from SAS, there is no way for them to share a session. There is no way to create a volatile table in SAS and access it in SQL Assistant.

 

I am not aware of how SAS manages sessions. However it appears that two different mechanisms are being used - a proc and a more direct launch of a statement. It will be necessary to determine how SAS handles database sessions for the operations being performed. If separate sessions are being used, then a volatile table will not be visible from any sessions that were not the session that created the VT.