Using QueryGrid 1.0 to refresh remote Teradata system data

Overview

I lately had to refresh a dev environment with some Prod data (of course consistent and fresh) in order to test a webservice in development environment (From Teradata To Teradata)

I decided to use QueryGrid 1.0 that was deployed on the Teradata systems

About environment:

  • Production system is built with 3 nodes
  • Development system is built with 2 nodes

This is important to know because it will affect the number of concurrentstreams per node you can open between the 2 systems

Choose a value between 1 to 5 (depending on the concuurent jobs). Higher the number is, faster will be the data exchange between the two systems

In this case:

  • you can write clearly in the foreign server creation by applying the instruction

concurrentstreams (2 )

  • Or let it depend on the session by applying a queryband
SET QUERY_BAND = 'concurrentstreams=2;' FOR SESSION;

Steps to follow on the Production Teradata System

CREATE USER PROXY_USER AS PERM=0 PASSWORD=********;

Proxy User creation and trusted authorization

CREATE AUTHORIZATION td_server_db.PROXY_USER_auth AS DEFINER TRUSTED USER 'PROXY_USER' PASSWORD '********';

Create foreign server on production system

This Foreign server will point to the Development server composed by 2 nodes

CREATE FOREIGN SERVER TD_SERVER_DB.DEV 
EXTERNAL SECURITY DEFINER TRUSTED PROXY_USER_auth USING 
Hosttype('Teradata')
remotehost('xxxxcop1,xxxxcop2')
ip_device('bond0')
port('5000')
read_timeout(2000)
listen_timeout(600)
concurrentstreams(2)
DO IMPORT WITH SYSLIB.LOAD_FROM_TD,
DO EXPORT WITH SYSLIB.LOAD_TO_TD;

Applicative account creation

CREATE USER "APP_BATCH" FROM "Comptes"
AS PERM = 0
PASSWORD = "********"
STARTUP = ''
NO FALLBACK
NO BEFORE JOURNAL
NO AFTER JOURNAL
PROFILE="BATCH"
DEFAULT ROLE=ALL

Grant read rights on source database

GRANT SELECT ON DBNAME TO APP_BATCH;

Grant connect through rights to applicative account

GRANT CONNECT THROUGH PROXY_USER TO PERMANENT APP_BATCH WITHOUT ROLE;

Grant right on DEV to the user

GRANT SELECT ON TD_SERVER_DB.DEV TO APP_BATCH;

Refresh Data from Prod to DEV

INSERT INTO
	DBNAME.TBLENAME
SELECT * FROM DBNAME.TBLENAME

Error at Foreign server creation

Each time I was trying to execute the create foreign server script I had the message above.

I solved the problem by droping / recreating the account and paying attention the the password: no ? in the password!!!

DROP
					USER
					PROXY_USER;
					
CREATE
					USER
					PROXY_USER
					FROM
					"Comptes"
					AS
					PERM=0
					PASSWORD="********"

Select failed

The user doesn’t have SELECT access to TD_SERVER_DB.DEV

GRANT
					SELECT
					ON
					TD_SERVER_DB.DEV
					TO
					APP_BATCH;

Insert failed

The user doesn’t have INSERT access to TD_SERVER_DB.DEV

GRANT
					INSERT
					ON
					TD_SERVER_DB.DEV
					TO
					APP_BATCH;

Steps to follow on the Development Teradata System

Create an applicative account

CREATE
				USER
				"APP_BATCH"
				FROM
				"PRE_Comptes"
				
AS
				PERM
				=
				0
				
PASSWORD
				=
				"*********"
				
STARTUP
				=
				''
				
NO
				FALLBACK
				
NO
				BEFORE
				JOURNAL
				
NO
				AFTER
				JOURNAL
				
PROFILE="BATCH"
				
DEFAULT
				ROLE=ALL

Proxy User creation and trusted authorization

CREATE
USER
PROXY_USER
AS
PERM=0
PASSWORD=********

CREATE
					AUTHORIZATION
					td_server_db.PROXY_USER_auth
					AS
					DEFINER
					TRUSTED
					USER
					'PROXY_USER'
					PASSWORD
					'********';

Grant the write rights on destination database

GRANT
					SELECT, INSERT, UPDATE, DELETE
					ON
					DBNAME
					TO
					APP_BATCH

Grant connect through rights to applicative account

GRANT
					CONNECT
					THROUGH
					PROXY_USER
					TO
					PERMANENT
					APP_BATCH
					WITHOUT
					ROLE;

Querying your remote DEV system from Prod system

  • In SQL Assistant, Log on the Production system with your applicative account (APP_BATCH)
  • Launch the following query
SELECT
				COUNT(*) FROM
				DBNAME.W_DBNAME_WS_120R@DEV
  • Launch an insert query in order to refresh the DEV data with Prod Data
INSERT
				INTO
				DBNAME.W_DBNAME_WS_120R@DEV
				
SELECT
				*
				FROM
				DBNAME.W_DBNAME_WS_120R

Checking the configuration on the Prod environment

SELECT
				
NameInfo,

ServerName, 

CAST(ValueInfo
				AS
				VARCHAR(100)) AS
				ValueInfo
				
FROM
				dbc.serverInfoV
				
WHERE
				ServerName='DEV';

Hosttype    DEV    ‘Teradata’

remotehost    DEV    ‘xxxxcop1,xxxxcop2’

ip_device    DEV    ‘bond0’

port    DEV    ‘5000’

read_timeout    DEV    2000

listen_timeout    DEV    600

concurrentstreams    DEV    2