All Forums Tools
muneer 1 post Joined 11/12
02 Nov 2012
Parameterize source & target table name and also schema details in TPT script

Hi,
I have a requirement to move data from a teradata table (PROD region) to other teradata table(DEV region) using TPT. Say,
Source table Name: Bank_Prod
Columns : ID (interger) , NAME (CHAR)
Target table Name: Bank_Dev
Columns : ID (interger) , NAME (CHAR)
I have 100 such tables to be loaded from PROD to DEV..table structure is same in both region. I want to create a generic TPT script and use that to load any number of tables. For doing this, I need to parameteris the Source table name,target table name,source schema details and target schema details. I have flat file which stores all the table defintions.
Please see below script, I want to parametrize the content highlighted (bold)
Please do the needful
====Script====
/* 1 */ 
/* 2 */ 
/* 3 */  DEFINE JOB Test1
/* 4 */  (
/* 5 */   DEFINE OPERATOR W_1_o_Test1
/* 6 */   TYPE LOAD
/* 7 */   SCHEMA *
/* 8 */   ATTRIBUTES
/* 9 */   (
/* 10 */    VARCHAR UserName,
/* 11 */    VARCHAR UserPassword,
/* 12 */    VARCHAR LogTable,
/* 13 */    VARCHAR TargetTable,
/* 14 */    INTEGER BufferSize,
/* 15 */    INTEGER ErrorLimit,
/* 16 */    INTEGER MaxSessions,
/* 17 */    INTEGER MinSessions,
/* 18 */    INTEGER TenacityHours,
/* 19 */    INTEGER TenacitySleep,
/* 20 */    VARCHAR AccountID,
/* 21 */    VARCHAR DateForm,
/* 22 */    VARCHAR ErrorTable1,
/* 23 */    VARCHAR ErrorTable2,
/* 24 */    VARCHAR NotifyExit,
/* 25 */    VARCHAR NotifyExitIsDLL,
/* 26 */    VARCHAR NotifyLevel,
/* 27 */    VARCHAR NotifyMethod,
/* 28 */    VARCHAR NotifyString,
/* 29 */    VARCHAR PauseAcq,
/* 30 */    VARCHAR PrivateLogName,
/* 31 */    VARCHAR TdpId,
/* 32 */    VARCHAR TraceLevel,
/* 33 */    VARCHAR WorkingDatabase
/* 34 */   );
/* 35 */ 
/* 36 */   DEFINE SCHEMA W_0_s_Test1
/* 37 */   (
/* 38 */    ID INTEGER,
/* 39 */    name CHARACTER(4)
/* 40 */   );

/* 41 */ 
/* 42 */   DEFINE OPERATOR W_0_o_Test1
/* 43 */   TYPE EXPORT
/* 44 */   SCHEMA W_0_s_Test1
/* 45 */   ATTRIBUTES
/* 46 */   (
/* 47 */    VARCHAR UserName,
/* 48 */    VARCHAR UserPassword,
/* 49 */    VARCHAR SelectStmt,
/* 50 */    INTEGER BlockSize,
/* 51 */    INTEGER MaxSessions,
/* 52 */    INTEGER MinSessions,
/* 53 */    INTEGER TenacityHours,
/* 54 */    INTEGER TenacitySleep,
/* 55 */    INTEGER MaxDecimalDigits,
/* 56 */    VARCHAR AccountID,
/* 57 */    VARCHAR DateForm,
/* 58 */    VARCHAR NotifyExit,
/* 59 */    VARCHAR NotifyExitIsDLL,
/* 60 */    VARCHAR NotifyLevel,
/* 61 */    VARCHAR NotifyMethod,
/* 62 */    VARCHAR NotifyString,
/* 63 */    VARCHAR PrivateLogName,
/* 64 */    VARCHAR TdpId,
/* 65 */    VARCHAR TraceLevel,
/* 66 */    VARCHAR WorkingDatabase
/* 67 */   );
/* 68 */ 
/* 69 */   APPLY
/* 70 */    (
/* 71 */     'INSERT INTO DEV.Bank_Dev (ID,name) VALUES (:ID:name);'
/* 72 */    )
/* 73 */   TO OPERATOR
/* 74 */   (
/* 75 */    W_1_o_Test1[1]
/* 76 */ 
/* 77 */    ATTRIBUTES
/* 78 */    (
/* 79 */     UserName = 'xxx',
/* 80 */     UserPassword = 'xxx',
/* 81 */     LogTable = 'DEV.Bank_Dev_log',
/* 82 */     TargetTable = 'DEV.Bank_Dev,
/* 83 */     TdpId = '1234'
/* 84 */    )
/* 85 */   )
/* 86 */   SELECT * FROM OPERATOR
/* 87 */   (
/* 88 */    W_0_o_Test1[1]
/* 89 */ 
/* 90 */    ATTRIBUTES
/* 91 */    (
/* 92 */      UserName = 'xxx',
/* 93 */     UserPassword = 'xxx',
/* 94 */     SelectStmt = 'SELECT * FROM PROD.Bank_Prod;',
/* 95 */     TdpId = '1234'
/* 96 */    )
/* 97 */   );
/* 98 */  );

feinholz 1234 posts Joined 05/08
07 Nov 2012

What version of TPT are you using?
We have made great improvements to the simplification of our script language, such that you do not need to be so verbose.
Also, we can obtain the schema automatically now.
We are trying to get TPT users to move to the use of templates. When you install TPT, we will install template files in the install directories. Those templates are text files that contain the operator definitions, with job variables assigned for every supported attribute.
If you want, you can also obtain the schema of a table by using this syntax:
DEFINE SCHEMA FROM TABLE <table name>;
In fact, we have made enhancements to Easy Loader to support the Export operator so that you can do what you are trying to do just from a command line, wiithout the need for a script.
To answer your question, though, pretty much everything in a TPT script can be replaced by either a job variable, or the use of the %INCLUDE statement to bring in text from an outside source.
However, with TPT's "simplicity", you can do this:

DEFINE JOB . . .
(
   APPLY $INSERT
   TO OPERATOR ($LOAD[1])
   SELECT * FROM OPERATOR ($EXPORT[1]);
);

The $LOAD syntax says to use the Load operator.
The $EXPORT syntax says to use the Export operator.
The $INSERT syntax tells TPT to generate the INSERT statement based on the schema of the source table.
TPT will generate the needed syntax for the script under the covers.
You then just need to supply a job variable file on the command line with the needed metadata items (logon information, table name, etc.).
 
With Easy Loader, we support the Export operator in versions 14.00 and 14.10.
From the command line, you just provide the source table and logon credentials, and the target table and logon credentials, and Easy Loader will generate the script for you, based on the schema of the source table.
The attached Partners presentation may help.
 

--SteveF

Barathe 3 posts Joined 11/12
18 Nov 2012

hi,
I have used the above mentioned code snippet.
 

DEFINE JOB . . .

(

   APPLY $INSERT

   TO OPERATOR ($LOAD[1])

   SELECT * FROM OPERATOR ($EXPORT[1]);

);

But getting the error message like this.
Teradata Parallel Transporter Version 13.10.00.07
TPT_INFRA: TPT04063: Error: Line 6 of Job Script File 'qsetup1.txt': No operator referenced in the job (step) has an
  explicit schema or any table association from which the job (step) schema can be
  inferred.
Job script preprocessing failed.
Job terminated with status 8.
 
please help me in resolving this.
Variable file Content(jobvars1.txt):
LoadPrivateLogName = 'loadoper_private_log',
LoadTargetTable = 'emp_test2',
LoadWorkingDatabase = 'EDW_OFFSHORE_STG_INVNT',
LoadErrorTable1 = 'emp_test1_et',
LoadErrorTable2 = 'emp_test1_uv',
LoadLogTable = 'emp_test1_log',
LoadMaxSessions = 5,
LoadMinSessions = 1,
ExportPrivateLogName = 'exportoper_private_log',
ExportMaxSessions = 5,
ExportMinSessions = 1,
ExportSelectStmt = 'select * from dbnAME.emp_test1;',
TargetUserName = 'xxx',
TargetUserPassword = 'yyy',
TargetTdpId = '000.00.00.91',
SourceUserName = 'xxx',
SourceUserPassword = 'yyy',
SourceTdpId =  '000.00.00.92'
 
 

feinholz 1234 posts Joined 05/08
19 Nov 2012

Please take a look at the templates that came with 13.10.
I think the "Load...." job variables names used to be known by "Target....".
We had to change the names in 14.0 to support the existence of more than one "target" operator.
Since you are using TPT 13.10, you need to use the old names in the job variable file.

--SteveF

Barathe 3 posts Joined 11/12
19 Nov 2012

I have changed the job variable names as u have mentioned.
But I am facing the below issue.
Cannot open the file '$SCHEMA_emp_test2.txt'due to error 13
        Permission denied
TPT_INFRA: TPT04032: Error: Schema generation failed for table 'emp_test2' in DBS '138.83.86.91':
  "GetTableSchema" status: 48.

Job script preprocessing failed.
Job terminated with status 12.

Both 'emp_test1' and 'emp_test2' are present in the same server and same db.
so if it is possible to get 'emp_test1' schema,how is the permission denied for 'emp_test2'.Please help me to resolve this.
Thanks for ur response.

vijaydf 16 posts Joined 06/12
10 Jul 2013

Is it possible to have the File_Loader attributes in a seperate file and just call this file in the attributes session.? currenly in our loader we have only few attributes defined like
TdpId, UserName  , UserPassword  , LogonMech  , QueryBandSessInfo, TargetTable  , LogTable  , ErrorTable1 , ErrorTable2 , WorkTable , MaxSessions
 
But we need to add few other attributes for job control like TenacityHours,TenacitySleep etc.. so instead of having the attributes in individual tpt script can i have this attributes and its value in a file and call this file in the attribute ?
 
 

Vijay Mani

feinholz 1234 posts Joined 05/08
10 Jul 2013

Which version of TPT are you using?
Please refer to the TPT documentation for the use of Job Variables.
Everything in a TPT script is substitutable.
In fact, we do not want you to hardcode anything in scripts. Instead, assign attribute values to job variables and create a job variable file (or use the command line) to provide the runtime values for these job variables.
TPT has also undergone many changes in order to determine the schema for you. The documentation will explain how.
 

--SteveF

vijaydf 16 posts Joined 06/12
11 Jul 2013

we are using ttu 13.10. Can we have the attributes itself in a file and call the those inside the define operator.?
 

Vijay Mani

feinholz 1234 posts Joined 05/08
11 Jul 2013

Please refer to the TPT documentation for the full explanation of how to use Job Variables. The User Guide should provide the needed information.
 

--SteveF

vijaydf 16 posts Joined 06/12
11 Jul 2013

Thanks feinholz..

Vijay Mani

simplesim092 11 posts Joined 04/14
05 May 2014

when I am running TPT , its giving m error as
Teradata Parallel Transporter Update Operator Version 14.00.00.08
UPDATE_OPERATOR: private log not specified
UPDATE_OPERATOR: connecting sessions
UPDATE_OPERATOR: preparing target table(s)
UPDATE_OPERATOR: TPT10508: RDBMS error 3524: The user does not have CREATE TABLE access to database DWH.
UPDATE_OPERATOR: disconnecting sessions
UPDATE_OPERATOR: Total processor time used = '1.04 Second(s)'
UPDATE_OPERATOR: Start : Mon May  5 02:50:03 2014
UPDATE_OPERATOR: End   : Mon May  5 02:50:07 2014
Job step DELETE_TARGET terminated (status 12)
 
why we need This rights for Create table if I am not using any DDL Operator..
Please find attached TPT script
DEFINE JOB A
DESCRIPTION 'Load A table'
(
DEFINE OPERATOR UPDATE_OPERATOR
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER UPDATE OPERATOR'
TYPE UPDATE
SCHEMA *
ATTRIBUTES
(
VARCHAR UserName,
VARCHAR UserPassword,
VARCHAR LogTable,
VARCHAR TargetTable,
INTEGER BufferSize,
INTEGER ErrorLimit,
INTEGER MaxSessions = 12,
INTEGER MinSessions,
INTEGER TenacityHours,
INTEGER TenacitySleep,
VARCHAR AccountID,
VARCHAR DateForm,
VARCHAR ErrorTable1,
VARCHAR ErrorTable2,
VARCHAR NotifyExit,
VARCHAR NotifyExitIsDLL,
VARCHAR NotifyLevel,
VARCHAR NotifyMethod,
VARCHAR NotifyString,
VARCHAR PauseAcq,
VARCHAR PrivateLogName,
VARCHAR TdpId,
VARCHAR TraceLevel,
VARCHAR WorkingDatabase,
VARCHAR DeleteTask
);
DEFINE SCHEMA TGT_TABLE_SCHEMA
DESCRIPTION 'Target Table Schema'
(
A_ID VARCHAR(20)
, A_TYPE VARCHAR(20)
, ACTIVE_IND VARCHAR(1)
, A_MAX_LATITUDE VARCHAR(50)
, A_MAX_LONGITUDE VARCHAR(50)
, A_MIN_LATITUDE VARCHAR(50)
, A_MIN_LONGITUDE VARCHAR(50)
, A_NUMERIC_ID VARCHAR(50)
, COORD_ACQUISITION_ID VARCHAR(20)
, COORD_SYSTEM_ID VARCHAR(20)
, EFFECTIVE_DATE VARCHAR(50)
, EXPIRY_DATE VARCHAR(50)
, LOCAL_COORD_SYSTEM_ID VARCHAR(20)
, PPDM_GUID VARCHAR(38)
, PREFERRED_NAME VARCHAR(255)
, REMARK VARCHAR(2000)
, "SOURCE" VARCHAR(20)
, SOURCE_DOCUMENT VARCHAR(20)
, ROW_CHANGED_BY VARCHAR(30)
, ROW_CHANGED_DATE VARCHAR(50)
, ROW_CREATED_BY VARCHAR(30)
, ROW_CREATED_DATE VARCHAR(50)
, ROW_QUALITY VARCHAR(20)
);
 
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA TGT_TABLE_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector.log',
VARCHAR DirectoryPath = @P_Dir_Path,
VARCHAR FileName = 'A.csv',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR OpenQuoteMark = '"',
VARCHAR CloseQuoteMark = '"',
VARCHAR TextDelimiter = ','
 
);
 
STEP DELETE_TARGET
(
APPLY
(
'DELETE FROM '||@P_TD_DBName||'.'||'A'||';'
)
TO OPERATOR
(
UPDATE_OPERATOR[1]
ATTRIBUTES
(
UserName = @P_TD_USER_NAME,
UserPassword = @P_TD_PWD,
LogTable = @P_DBNameUtil||'.'||'A'||'_LOG',
ErrorTable1 = @P_DBNameUtil||'.'||'A'||'_ET',
ErrorTable2 = @P_DBNameUtil||'.'||'A'||'_UV',
TargetTable = @P_TD_DBName||'.'||'A',
Tdpid = @P_TDPid,
DeleteTask = 'Y'
)
);
);
 
STEP INSERT_TD_TABLE
(
APPLY
(
'INSERT INTO ' ||@P_TD_DBName||'.'||'A' || '
(
A_ID
,A_TYPE
,ACTIVE_IND
,A_MAX_LATITUDE
,A_MAX_LONGITUDE
,A_MIN_LATITUDE
,A_MIN_LONGITUDE
,A_NUMERIC_ID
,COORD_ACQUISITION_ID
,COORD_SYSTEM_ID
,EFFECTIVE_DATE
,EXPIRY_DATE
,LOCAL_COORD_SYSTEM_ID
,PPDM_GUID
,PREFERRED_NAME
,REMARK
,"SOURCE"
,SOURCE_DOCUMENT
,ROW_CHANGED_BY
,ROW_CHANGED_DATE
,ROW_CREATED_BY
,ROW_CREATED_DATE
,ROW_QUALITY
 
)
VALUES
(
:A_ID
,:A_TYPE
,:ACTIVE_IND
,:A_MAX_LATITUDE
,:A_MAX_LONGITUDE
,:A_MIN_LATITUDE
,:A_MIN_LONGITUDE
,:A_NUMERIC_ID
,:COORD_ACQUISITION_ID
,:COORD_SYSTEM_ID
,:EFFECTIVE_DATE
,:EXPIRY_DATE
,:LOCAL_COORD_SYSTEM_ID
,:PPDM_GUID
,:PREFERRED_NAME
,:REMARK
,:SOURCE
,:SOURCE_DOCUMENT
,:ROW_CHANGED_BY
,:ROW_CHANGED_DATE
,:ROW_CREATED_BY
,CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0))
,:ROW_QUALITY
);'
)
TO OPERATOR
(
UPDATE_OPERATOR[1]
ATTRIBUTES
(
UserName = @P_TD_USER_NAME,
UserPassword = @P_TD_PWD,
LogTable = @P_DBNameUtil||'.'||'A'||'_LOG',
ErrorTable1 = @P_DBNameUtil||'.'||'A'||'_ET',
ErrorTable2 = @P_DBNameUtil||'.'||'A'||'_UV',
TargetTable = @P_TD_DBName||'.'||'A',
Tdpid = @P_TDPid
)
)
SELECT
A_ID
,A_TYPE
,ACTIVE_IND
,A_MAX_LATITUDE
,A_MAX_LONGITUDE
,A_MIN_LATITUDE
,A_MIN_LONGITUDE
,A_NUMERIC_ID
,COORD_ACQUISITION_ID
,COORD_SYSTEM_ID
,EFFECTIVE_DATE
,EXPIRY_DATE
,LOCAL_COORD_SYSTEM_ID
,PPDM_GUID
,PREFERRED_NAME
,REMARK
,"SOURCE"
,SOURCE_DOCUMENT
,ROW_CHANGED_BY
,ROW_CHANGED_DATE
,ROW_CREATED_BY
,ROW_CREATED_DATE
,ROW_QUALITY
FROM OPERATOR
(
FILE_READER[1]
);
);
 
);
 

simplesim092 11 posts Joined 04/14
05 May 2014

I am creating the A table in DWH Schema and Error table in other Schema...

feinholz 1234 posts Joined 05/08
05 May 2014

The user must have CREATE TABLE access rights in any database in which tables must be created. Error tables and log tables must be created.
Error tables are created by the DBS.
The restart log table is created by TPT.
But access rights are still required.

--SteveF

simplesim092 11 posts Joined 04/14
10 May 2014

Hi Thanks for your reply..I checkd after tlogview...I forgot to mention Worktable parameter for Upload operator..so it was creating Work table in main table..Which does not have create priviledge...Thanks a lot..!!

You must sign in to leave a comment.