Create a control file from Table definition

Shell Programming and Scripting
1

Hi Team,

I need to create a control file with a pre-defined structure for a given table name. The table is in teradata.
Ex: Table Name: TBL1

Table structure: 
create multiset table tbl1, no fallback,
  no before journal,
  no after journal,
  checksum = default,
  default mergeblockratio (
    col1 VARCHAR(10) not null title 'col1' casespecific,
    col2 VARCHAR(200) casespecific,
    col3 CHAR(1) not null casespecific
)
primary index tbl1 (
col1 );

I need to create a control file which will have tptstream script for this table. In this file I need to replace Varchar with the Char of same length and Integer with Char(11),Decimal with Char(length+2) etc.

User will provide the table name/names only. Please help me how to get the structure from the teradata and then create such file. I am in urgent requirement and any help will be highly appreciated.

Thanks in advance.

2

Please use codetags!

You said you have urgent requirement, let us know what you have tried so far ?

3

Hi Akshay,

I was trying to export the table structure in a file using fastexport but that requires select statement only. Right now the problem is getting the table structure, I am writing a perl script which will read all the columns(row by row in the file) and then will create the control file accordingly. I am working on the perl right now. Any help to extract the table structure in a file will be great.

thanks.

4

use bteq to get data from teradata onto unix. you can use export option to copy the following command's (show table) output

5

Hi,

got the Table Structure in a file. Working on creating the control file. Any i/p will be highly appreciated.

Thanks.

6

provide us the input file and expected output, we can try to provide a solution

7

Hi Please find the details below:

My DDL structure file will be like this:

CREATE MULTISET TABLE LMS_STG.tbl1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      col1 DECIMAL(18,0),
      col2 VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX NUPI_PH_STUD_CPNT ( col1 );

I have to create another control file which will be like this:

DEFINE JOB TBL1
DESCRIPTION 'Load a Teradata table from a file'
(
DEFINE SCHEMA DATA_SCHEMA
(
col1 CHAR(20),
col2 CHAR(11),
col3 CHAR(11),
col4 CHAR(11),
,EOL_PAD CHAR(1)
);
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA DATA_SCHEMA
ATTRIBUTES
(
VARCHAR PrivateLogName='PL_TBL1',
VARCHAR IndicatorMode = 'N',
VARCHAR OpenMode = 'read',
VARCHAR Format = 'Unformatted',
VARCHAR DirectoryPath = '/ngs/app/edwt/INFA/tgtfiles/',
VARCHAR FileName = 'tbl1.out'
);
DEFINE OPERATOR STREAM_OPERATOR
TYPE STREAM
INPUT SCHEMA *
ATTRIBUTES
(
INTEGER TenacityHours = 4,
INTEGER TenacitySleep = 6,
INTEGER MaxSessions = 6,
INTEGER MinSessions = 2,
VARCHAR Robust = 'Y',
VARCHAR ARRAYSUPPORT = 'ON',
INTEGER ErrorLimit = 1,
VARCHAR AppendErrorTable = 'Y',
INTEGER Pack = 600,
VARCHAR TdpId = @tdpid,
VARCHAR UserName = @userid ,
VARCHAR UserPassword  = @password ,
VARCHAR WorkingDatabase = 'LMS_STG',
VARCHAR TargetTable  = 'LMS_STG.PA_tbl1_FL',
VARCHAR ErrorTable = 'LMS_STG.ET_tbl1',
VARCHAR LogTable  = 'LMS_STG.LG_tbl1',
VARCHAR PrivateLogName = 'load_log',
VARCHAR Dateform = 'ANSIDATE'
);
APPLY
('
INSERT INTO LMS_STG.tbl1_FL(
col1,
col2

) VALUES ( 
:col1,
:col2
) ; 
')
SERIALIZE ON (
col1
) 
TO OPERATOR (STREAM_OPERATOR())
SELECT 
CASE WHEN col1 = '*' 
THEN NULL(CHAR(20)) 
ELSE col1 
END AS col1,
CASE WHEN col2 = '*' 
THEN NULL(CHAR(11)) 
ELSE col2 
END AS col2
END AS SEQUENCE_NUM
FROM OPERATOR
(
FILE_READER()
ATTR
(
FileName = 'tbl1.out'
)
);
);

I dont know if i can attach the files.

8

I dont see any relation between the input you have provided and the expected output...
If you could tell me where I can get the other attributes (COL3, COL4, EOL_PAD, TargetTable, ErrorTable...), we can give a try