Teradata Forums / Teradata / Teradata Tools and Utilities

How to make use of ARCMAIN utility to take DB backup.

tvn — Fri, 09 May 2008 05:40:51 GMT

I was asked to take database backup using ARCMAIN. But, not sure how to start using this utility. Expecting valuable helps from the group.thanks in advance.

FAST LOAD SET RECORD in Mainframe batch jobs

vanathi — Fri, 09 May 2008 10:11:15 GMT

Hey,Could anyone let me know whether 'SET RECORD' command in FAST LOAD can be used in Mainframe Batch jobs

Transient Journal

Job_Agent — Mon, 05 May 2008 12:45:33 GMT

is there any database parameter which can stop automatic transient journal in version V2R6.2

SHOW command shows incomplete definition

NehaJoshi — Wed, 07 May 2008 06:25:03 GMT

SHOW command has a limitation of displaying not more than 12500 characters.Is there any way of selecting the complete DDL of a view other than using the SHOW command so that a larger definition may be viewed completely?

Utility Error

Meem — Tue, 06 May 2008 05:59:20 GMT

Hi all,Iam getting the following error while executing Fastload/BTEQ Script under UNIX:ld.so.1: fastload: fatal: /opt/lib/../libcliv2.so: wrong ELF class: ELFCLASS64KilledKindly help me how to resolve this..Thanks in advance,Mm.

Importing data from Teradate to Excel using volatile tables in the query

insanity82007 — Mon, 05 May 2008 18:04:54 GMT

Hi guys,I have the below to run queries from comments in excel. This works for all of the queries I run except for those with volatile tables.I tried adding "(InStr(UCase(sCom), "CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), "INSERT INTO") > 0) Or (InStr(UCase(sCom), ";CREATE VOLATILE TABLE") > 0) Or (InStr(UCase(sCom), ";INSERT INTO") > 0)" to get it to work, but it didn't.I added and tested "(InStr(UCase(sCom), "EXEC") > 0)" successfully for macros.I then tried creating a macro but that came up with an error as well, which I've already read up about saying that Teradata can't create macros with volatile tables included, which I can't understand why.I did try writing the query so that it has no volatile tables, but I can't define a limited enough source of data for it not to run out of spool space.Is there any excel add in out there that I can use to get data from our data warehouse with queries that contain volatile tables? My VB knowledge is very limited at best and I can't modify this.

TERADATA BTEQ

prakhar — Sat, 03 May 2008 02:36:35 GMT

WARNING:OUT OF DATA WEN THIS MESSAGE IS COMING?REPEAT * AND REPEAT 1 WHAT IS DA DIFFERENCE?

Meta Data Services

hotspear — Thu, 30 Dec 2004 22:56:51 GMT

Hello,

Is there an easy way to import ERWIN models into Teradata MDS?

Thanks a lot

Resource Usage in Priority Scheduler

mz — Fri, 02 May 2008 02:56:59 GMT

Hi,if a scenario below happensa lower priority query is using the whole resource of the system as no other queries are running. and then a high priority query comes in. Will the high one take back the resource belongs to it or it has to wait the lower query release the taken resource?thanksMichael

implement Teradata Dynamic workload manager

mz — Fri, 02 May 2008 01:51:43 GMT

Hi,anyone knows how to upgrade the tdwm database? the menu said "go to Teradata Manager --> teradata database setup ..." but not details how...does it means I need to logon as DBC and tick "migrate tdqm database" option??I am in V2R6.2 with Teradata DWM 6.2.0.5 Teradata manager 7.2.0.8. we recently migrate from WvR6.0 and TDWM pops up an 'incompatible' error.thanks,Michael

Can I install TTU onto Windows Vista?

Alfredo — Wed, 23 Apr 2008 13:14:40 GMT

Someone tell me that I can't install SQL Assistant, Teradata Administrator and PMon onto Windows Vista, is it true?My new computer has vista and I don't want to install XP. Are there an option to install the tools without uninstall Vista?

SQL Question

AndrewT — Thu, 17 Apr 2008 23:35:15 GMT

I am working with a VERY large table (appx 15 Billion rows) and I want to pull back only those rows when conditions based on the previous row are met.For example, if the table contained the following rows:Customer Event_Time Event_Type1 1 A 1 6 B 1 7 A 1 9 C 2 2 A1 4 B 1 8 D 2 3 B I would only want to return these two rows:Customer Event_Time Event_Type1 4 B 1 6 B -- in essence, I want to return rows that after being sorted by Customer and Event_Time have the same value in Event_Type. Also, there can be more than two rows consecutive rows with the same Event_Type; in those cases, I need ALL the rows (e.g. if there were another row with Customer=1 and Event_Time=5 and Event_Type=B I would want that row as well).My current approach is to create a table which contains a portion of the data (roughly 1B rows) in sort order and then use SAS to determine which consecutive rows to keep. I know it would be much faster if I could do the row selection in TD rather than bringing it down and processing in SAS.Thanks in advance for any advice.

RC in bteq

shanthuj — Thu, 10 Apr 2008 09:53:46 GMT

Hi,Is it possible to manually set a return code(RC) in a bteq. I need to restart the JCL based on a condition like .IF ACTIVITYCOUNT<>0(present in my BTEQ script) then restart jcl else quit.The JCL should be automatically restarted based on the activitycount. Is there any way to achieve it?Any suggestions would be useful...Thanks

Informatica Loader connection

sakthiganesht — Tue, 22 Apr 2008 01:10:26 GMT

Do informatica 7.1 have Teradata loader connection options? If yes when was it released?Any info on when was informatica 8.1.1 released?

Where to get eclipse plugin from

kutsyy — Mon, 21 Apr 2008 15:30:25 GMT

http://www.teradata.com/datatools/update seems to be down. Does anyone know if eclipse plugin was moved? I can not find any info.Thanks,Vadim

sample FLOAD,MLOAD and FAST EXPORT scripts

ravindrareddy — Thu, 17 Apr 2008 13:15:00 GMT

can anyone please provide few example scripts for FLOAD,MLOAD and FAST EXPORT...Thanks In Advance

MLoad of a file with delimited and quoted fields

Elr0ndK — Wed, 12 Mar 2008 07:00:32 GMT

Hi all, thanks in advance.There is a way to load a file with quoted and delimited fields?For example, a row is like this:"1111"|"XXXX"|"GGGG"All fields are VARCHAR and I need it to do a fixed MLoad script (some field can contains field separator, but with quoted fields this isn't a problem) for some jobs.Thanks!

Fast Export Script Syntax

ravindrareddy — Tue, 15 Apr 2008 06:58:14 GMT

.BEGIN EXPORTSESSIONS 20;.LAYOUT Control ;.FIELD Date1 * Date ;.FIELD Date2 * Date ;.IMPORT INFILE InNameLAYOUT Control ;.EXPORT OUTFILE C:\outputtext.txt ;SELECTRegion,ProdCode,From TranLogTableWHERE TranDate BETWEEN :Date1 and :Date2ORDER BY Region, ProdCode;.END EXPORT ;

teradata sql

shanthuj — Wed, 02 Apr 2008 05:31:10 GMT

i have 1 value in table1.... that one value is a field in table2.... i need to know how to access the values of the field in table2 using the value in table1? my result should be something likesel value(table1.field_nm) from table2;thanks

configuration of PSA in demo CD

dixon — Thu, 10 Apr 2008 11:35:46 GMT

Hi i am trying to connect to the priority scheduler administrator through Demo CD. Its asking System(DBS) Name, userid, password. i gave system name as localhost / mycomputer name and username, password as tpch/tpch, dbc/dbc both i tried both users but its no where connecting. the error is Sql failed to access table DBC.DBCinfo Error: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.Thanksdixon

Fast Export

AndrewLivingston — Tue, 15 Apr 2008 12:19:08 GMT

We recently upgraded from V2R5 to V2R6 and I noticed that when running Fastload and FastExport scripts, it is asking for the user password NUMEROUS times (I do not pass my password within the script).It appeared to be asking for a password for the initial logon, and then for each session as defined in the script, and then again before logging off. I always set SESSIONS to 4, and it is asking for a password up to 8 times until the script completes.Obviously, this is annoying, as one mistype and the script fails.Has anyone encountered this before?Here is an example script:.logtable DM.ACLTemp_log;.logon TDATABOX1/USERID;.begin export sessions 4;.export outfileo:\fastexport\export.txtMode record format text;Select prodid (char (11)) ,prodcode (char (6))From TRAINING.PRODUCT_HELD;.end export;.logoff;I find this quite odd! I just tested it again, and indeed it was 7 password requests. It pauses after the first one, then requests 2 through 6 just pop up one after the other. The remainder of the script runs, and then before it logs off, it asks for the password again.Is there perhaps a flag for load jobs for explicit logging or something like that?Any help would be appreciated.Andrew

Error 1606 when installing Teradata GSS Clientnt-i386 12.0.

JEB — Fri, 21 Sep 2007 07:45:49 GMT

I am trying to install the Teradata GSS Clientnt-i386 12.0.
I get an error 1606. Could not access network location nt-i386\12.00.00.00\.

What do I need to do to address this issue?

Thanks

UNIX bteq sql output

c19 — Tue, 08 Apr 2008 08:20:23 GMT

Hi,I'm wondering how to echo the output of a select statement in bteq.I am able to put the output to a file but not the screen.Here is my script#!/bin/kshDIR="/userhome/isc/SMI_PROCESSES_2/complaints/ksh/parallel_run"bteq &1Any help would be appreciated.Thanks

PMON

vinaymohan — Mon, 07 Apr 2008 17:06:56 GMT

HI all; Can anyone please let me know what exactly is PMON? why is it used where is it used ?what are the advantages of using PMON?Is there any document which tells more about PMON,if yes please let me know.Thanks in advance

Conditinal Logic with dynamic parameteres in BTEQ.

pankajsachdeva — Fri, 28 Mar 2008 02:32:21 GMT

I have to get a value 0 or 1 from a query(dynamically) and want to write the conditional logic in BTEQ with the value i will get.How to handle this in UNIX?Any assistance/examples would be appreciated.

fastload script insert phase error.

teradataguy — Wed, 27 Feb 2008 16:40:27 GMT

Hi all teradata gurus,This fastload example gives an error in the as shown below, any ideas how to correct it,**** 15:35:51 Number of recs/msg: 414**** 15:35:51 Starting to send to RDBMS with record 1**** 15:35:51 I/O Error on File Read: 35, Text: EOF encountered before end of recordName of records file is with data as shown below,|10021 |Brown, Jo |200|2312|Development |63000.00 |20|Jan 01 1955|F| |M|16| 0||10001 |Jones, Bill |100|5376|President |83000.00 |15|Jan 01 1960|M| |M|14| 0||10002 |Smith, Jim |100|4912|Sales |73000.00 |10|Jan 01 1970|M| |M|13| 1||10028 |Lee, Sandra |200|5844|Support |77000.00 | 4|Jan 01 1971|F| |M|18| 0||10029 |Berg, Andy |200|2312|Test |67000.00 |10|Jan 01 1967|M| |M|15| 0||10023 |Ayer, John |300|4432|Accounting |52000.00 | 8|Jan 01 1965|M| |M|13| 0| as shown below,.logon uname,pwd;CREATE TABLE employ (EmpNo SMALLINT NOT NULL,Name VARCHAR(12),DeptNo SMALLINT,PhoneNo SMALLINT,JobTitle VARCHAR(12),Salary DECIMAL(8,2),YrsExp BYTEINT,DOB DATE,Sex CHAR(1) UPPERCASE,Race CHAR(1) UPPERCASE,MStat CHAR(1) UPPERCASE,EdLev BYTEINT,HCap BYTEINT)UNIQUE PRIMARY INDEX(EmpNo);.begin loading employ errorfiles emperr1,emperr2 checkpoint 10000;set record vartext "|";defineEmpNo(varchar(9)),Name(varchar(12)), DeptNo(varchar(13)), PhoneNo(varchar(4)), JobTitle(varchar(12)), Salary(varchar(19)), YrsExp(varchar(12)), DOB(varchar(11)), Sex(varchar(11)), Race(varchar(11)), MStat(varchar(11)), EdLev(varchar(12)), HCap(varchar(12))file=emps.txt;insert into employ values (:EmpNo,:Name,:DeptNo,:PhoneNo,:JobTitle,:Salary,:YrsExp,:DOB,:Sex,:Race,:MStat,:EdLev,:HCap);.end loading;.logoff;thanx all in advancergrdsterdataguy

LDM Mapping

New TD user — Thu, 27 Mar 2008 05:30:16 GMT

When a migration is done to Teradata, 1) Is it better to migrate the data to the warehouse and then map to LDM2) Or do the mapping first and then migrate?Point 1 is an option to save time and have data quicker in the warehouse. Please let me know.

Teradata Bteq

ldmckenzie — Thu, 03 Apr 2008 18:22:19 GMT

Hello all, I am seeking a solution to a possible problem later on. I have a process that simply loads data into a teradata table using bteq. My OS is VM/CMS. On the mainframe platform we use an exec that calls the bteq engine and a cntl file which has the DDL. I would like to test for success or failure of this routine after it is completed. Please advise. If the process is aborted, I would like to rollback any changes that have been made to the database. Obviously some conditional logic is required. Please advise Im a newbee.Thanks very much,L

ARCMAIN Rollforward

Job_Agent — Tue, 01 Apr 2008 13:35:54 GMT

Hi,eI am trying to rollforward restored journals but it is taking long time ,any idea how to improve its performance and any measure of performance

Format Query Icon in SQL Assistant

eRascal — Fri, 02 Nov 2007 13:45:18 GMT

The Text formatting button in SQL Assistant stopped working. This does not enable the user to see the queries more effectively. The Format Query icon in the Toolbar is grey out. does not work either.

Batch queryman (windows) - how to pass dynamic parameter

patmason — Tue, 11 Mar 2008 16:12:22 GMT

I'm trying my first batch queryman (cmd line for it will be within a larger bat file). I will be using the -f option.Within the it will be executing a macro with parameters. I need to set a variable within the outer batch file and have those values passed to queryman to be used as the parameters to the macro.I think I know how to handle it within unix (export then use ${variable} in the script) but haven't been successful within windows/dos.Any assistance/examples would be appreciated.

Mload - error 6706

pranavkbansal — Wed, 27 Feb 2008 11:59:35 GMT

Hi,I am loading data in Teradata using Mload. I have a flat file (Page Code - UTF-8) and this flat file is source file to the mload script. Mload connections also set to UTF-8. While loading, few rows are getting error out and going in to ET table with error code 6706. Please let me know if there is way to catch untranslatable character in Mload Script or if any one know solution to handle such problem.Any help would be highly appreciated.Regards,Pranav

Using TPT, how to specify a starting line when importing from a file.

Chris.Easton — Tue, 18 Mar 2008 14:40:02 GMT

Hi, I'm trying to use TPT to read from a flat-file to import into Teradata and I've hit a little snag. There is some extraneous information at the top of the file that needs to be removed. This can be done using Sed or a script before the file is to be imported via TPT, but there has to be a better way. I know in fastload there is a way to load from a line number onward, does the same exist for TPT scripts?Thanks,- Chris

FLOAD/MLOAD

ragav — Thu, 27 Mar 2008 09:25:56 GMT

Hi All, Can someone tell me whether FLOAD/MLOAD can take XML files as the source files to load.If yes please help me with some examples.Thanks in advance.ThanksRagav

Where I can find the install download for Teradata Tools and Utilities (urgent!)

Miao Fan — Sat, 22 Mar 2008 14:29:45 GMT

We have an urgent case which need to use Teradata FastLoad utility. I assume we need to install Teradata Tools and Utilities. Could anyone tell me where I can find the download for it?Thanks,Miao

bteq on unix

venn — Mon, 24 Mar 2008 01:35:42 GMT

hi frnds,iam new to teradata,please any one tell abt how to run the bteq scripts on unix....if possible pls send sample scripts....

how to get rid of unreadable characters generated by fastExport

Teradata_lcl — Tue, 18 Mar 2008 15:52:03 GMT

I read posts in this forum about how to get rid of unreadable characters generated by fastExport and I tried all the ways you guys suggested but it doesn't work for me. Anyone knows the reason causing this? Thanks in advance!!!Here is the outputs:$"001050|731427|0|0|0|0|0|0|0|0|0|0|$"001050|477550|0|0|0|0|1|0|0|0|0|0|$"001050|441807|1|2|2|1|3|0|2|0|1|0|$"001050|698925|0|1|1|1|1|0|1|0|1|1|#!001050|67691|0|0|0|0|0|0|0|0|0|0|Here is the source codes:.logtable xxx.xxxlog;.logon aaa/bbb,cccdatabase ddd;.begin exportsessions 20;.export outfile xyz mode record;sel trim(cast(cast('0' as char(1))||cast(locationid as char(5))||cast('|' as char(1))||trim(cast(productnumber as int))||cast('|' as char(1))||trim(cast(day01+0.5 as int))||cast('|' as char(1))||trim(cast(day02+0.5 as int))||cast('|' as char(1))||trim(cast(day03+0.5 as int))||cast('|' as char(1))||trim(cast(day04+0.5 as int))||cast('|' as char(1))||trim(cast(day05+0.5 as int))||cast('|' as char(1))||trim(cast(day06+0.5 as int))||cast('|' as char(1))||trim(cast(day07+0.5 as int))||cast('|' as char(1))||trim(cast(day08+0.5 as int))||cast('|' as char(1))||trim(cast(day09+0.5 as int))||cast('|' as char(1))||trim(cast(day10+0.5 as int))||cast('|' as char(1)) as char(80)))from ddd.eee where locationid=1050 and productnumber in (441807,698925,67691,731427,477550);.end export;====The reason I use lots of trim because we don't want to have spaces generated.Any idea about that? By the way, I really hate to use the cast, the data we are going to extract are huge, just hope the cast statements won't slow the run time of the query.

Problems using fastload

deanc — Sun, 23 Mar 2008 00:29:53 GMT

I'm new to Teradata and I've been asked to evaluate it's capabilities. I'm trying to use fastload to load data into my database. Originally when using fastload I was getting the " CLI Error 224: MTDP: EM_NOHOST(224): name not in HOSTS" error. From reading the forums I found that I needed to add my database DemoTDAT to the hosts file. My hosts file now reads127.0.0.1 localhost DemoTDATNow when I attempt to logon through fast load using:.logon DemoTDAT/test1,test1I get the following Warning:**** 00:17:43 Warning: RDBMS or network down. Trying to logon again.I have created a database called Test and a user of test1 with the same password. I'm wondering what I am doing wrong. Any help would be greatly appreciated.

Error in Accessing the teradata from command window

SGK — Fri, 21 Mar 2008 08:13:02 GMT

Hi All,When I give the command BTEQ in the command window (start->run->command) and hit enter, am getting an error saying "BTEQ is not recognized as an internal or external command"I have Teradata SQL Assistant and ODBC Driver installed in my system and am able to access the teradata node through SQL assistant.Please let me know if i need to install anymore software to access the teradata node through the command window.Regards,Gopi

Types of Flat Files

teradataguy — Wed, 19 Mar 2008 22:52:05 GMT

hi all teradata gurus,i jus want to know what are the different types of flat files that can be used to load/export data into/from teradata databasei know some of the examples of flat files are text file with .txt extension, data file .dat, excel file .xsl, access file .mdb etc....if there are some more types pls do let me know.now what are the types of flat files that can be used to load/export data to/from teradata database tables with each of the teradata utilities like bteq import/export, fastload, multiload, tpump and fastexport.thanx all in advance.teradataguy