How can I set query_band for Block Level Compression using Aster's load_to_teradata function,

Question

When you are loading a teradata table in bteq you can set the queryband for block level compression. This even works when you are using querygrid and inserting from a foreign server.

SET QUERY_BAND = 'BlockCompression=Yes;' UPDATE FOR SESSION;

My issue is that I am creating a table on an Aster system and then using the load to teradata function. I suspect there is a syntax call where I can set the query band as part of the load_to_teradata call but after searching the internet and through a reem of teradata documentation I haven't found anything yet.

-- Load Agg data for the YYYYMM to Teradta
SELECT SUM(loaded_row_count),SUM(error_row_count)
       FROM load_to_teradata (
       ON ( select 
              Cust_id
            , cast(lst_cnf_actvy_dt_tm as date) as lst_cnf_actvy_dt
            , cast(sum(str_cnt     ) as INTEGER) as acct_open_brnch_use_cnt
            , cast(sum(phone_cnt   ) as INTEGER) as acct_open_phn_use_cnt
            , cast(sum(mail_cnt    ) as INTEGER) as acct_open_mail_use_cnt
            , cast(sum(onlnchnl_cnt) as INTEGER) as acct_open_onln_use_cnt
            , cast(sum(mblbnk_cnt  ) as INTEGER) as acct_open_mbl_dvc_use_cnt
            , cast(sum(acctopen_cnt) as INTEGER) as acct_open_trck_chnl_cnt
            from <someDB>.<someTBL>
            where acctopen_cnt > 0
              and lower(lst_cnf_actvy_typ_cd) = 'acctopen'
              and cast(lst_cnf_actvy_dt_tm as date) between
                  cast(substring('${YYYYMM}' from 1 for 4) || '-' || substring('${YYYYMM}' from 5 for 2) || '-01' as date) and
                  cast((cast(substring('${YYYYMM}' from 1 for 4) || '-' || substring('${YYYYMM}' from 5 for 2) || '-01' as date) + interval '1 month') - interval '1 day' as date)
              and (str_cnt > 0 or phone_cnt > 0 or mail_cnt > 0 or onlnchnl_cnt > 0 or mblbnk_cnt > 0)
            group by 1,2 )
       TDPID('TD_RDBMS_C2T') USERNAME('${c2tUID}') PASSWORD('${c2tPWD}') ${LDAP_IND_AST_C2T}
       TARGET_TABLE ( 'C2T.t_yyyymm_agg' ) LOG_TABLE ('C2T.t_yyyymm_aggLOG')
       MAX_SESSIONS(120));

Answer 1

Was able to get the syntax for the load_to_teradata options. You can see the query_band_sess_info argument after max_sessions and before query_timeout...

load_to_teradata(
ON (source query)
TDPID('tdpid')
TARGET_TABLE('fully-qualified table name')
[ERROR_TABLES('error table'[, 'unique constraint violation table'])]
[LOG_TABLE('table name')]
Teradata QueryGrid: Aster-Teradata Connector
Loading Data From Aster Database to Teradata
Aster Database User Guide for Aster Appliances 301
[USERNAME('username')]
[PASSWORD('password')]
[LOGON_MECHANISM('TD2' | 'LDAP' | 'KRB5')]
[LOGON_DATA('mechanism-specific logon data')]
[ACCOUNT_ID('account-id')]
[TRACE_LEVEL('trace-level')]
[NUM_INSTANCES('instance-count')]
[START_INSTANCE('start-instance')]
[MAX_SESSIONS('max-sessions-number')]
[QUERY_BAND_SESS_INFO('key1=value1;key2=value2;...')]
[QUERY_TIMEOUT('timeout-in-seconds')]
[AUTO_TUNE_INSTANCES('yes'|'no')]
[WORKINGDATABASE(‘dbname’)]
[DIAGNOSTIC_MODE('All'|['GetCOPEntries','CheckConnectivity',
'CheckAuthentication','GetTPTSessions',
'TargetTableOrQuerySchema'])])
);