Teradata Forums / Teradata / Teradata Tools and Utilities

List of views created on a table

maddy@1 — Wed, 18 Nov 2009 02:31:15 GMT

HiI would like to the list of view names that are created on a particular table.For example, there are 2 views view1 and view2 that are created on a table Table1. Is there any query by which i can know that there are 2 views (view1 & view2) that are existing on table1.ThanksMadhavi.

any one plz give me brief explain about this error

chinna — Thu, 19 Nov 2009 19:55:52 GMT

iam trying to load data in set and multiset tables with mload ( i tried both ways)i am getting the following error..**** 00:35:00 UTY0805 RDBMS failure, 2801: Duplicate unique prime key error in databasename_tablename_Log.

Tearadata Query and Result using Export Utility

NK — Thu, 19 Nov 2009 07:43:39 GMT

Hi,I want to export the teradata queries and their output into a text file.For doing so I am using the following commands: .EXPORT FILE=filename .SET WIDTH 3000 .SET TITLEDASHES OFF;SELECT col1 ,col2 from T1WHERE id='Z101';.EXPORT RESET;.LOGOFF.EXITEOFBut the above query fetches the data in the file (filename) as:col1 col2v1 v2... ...But I need the output in the file in this format:SELECT col1 ,col2 from T1WHERE id='Z101';col1 col2v1 v2... ...Solutions to this problem will be appreciated.Thanks in advance

comrpess column

rediff123 — Wed, 11 Nov 2009 13:13:41 GMT

Does the compressed column play an impact in query execution, when it is involved in a left outer join? the column is compressed to a value for eg: '$'-Thank you

Teradata SQL Assistant 13 on Windows 7 machine

mame — Fri, 13 Nov 2009 03:55:29 GMT

Hi all,I installed Teradata SQL Assistant 13 on my windows 7 machine, but I can only execute one query.After the first query is done, I can't ever execute another query.All I can do is shut it down and run another Taredata SQL Assistant.Does anyone know how to solve this problem?thanks

How to configure teradata manager 12 in my windows machine?

Big apple — Thu, 12 Nov 2009 21:49:34 GMT

Hi everyone,I am not able to configure teradata manager 12(came with teradata 12 express edition).I refered to many manuals and google for some time but dint do good.so can any one help me out with this.thanks

Fastload when error ran in scheduled job

johnstg — Tue, 10 Nov 2009 21:12:40 GMT

We are using Faslload in a series of ETL jobs, which need to be scheduled.When we double click the .CMD file, the job completed successfully, however, when the same file is scheduled on useing the task scheduler when the job reaches the fastload step, fastload throws an exception the debugger window appears if your logon to the server under the same username, and the job aborts.Host is Windoes Server 2003, fastload version is 12.00.00.004Does anyone have any suggestions ?RegardsGlenn.

Date interval in Teradata

learninggeek — Tue, 10 Nov 2009 19:05:10 GMT

Hi,I need to select data for last seven days from the database that has data for nearly 20 years. Can someone please give me the code to extract data for last seven days using teradata SQL?Thanks in Advance.Regardslearninggeek

MVC TOOL

dhruvhardik — Tue, 17 Jun 2008 12:19:02 GMT

Hi ,This is regarding MVC tool. I have developed a MVC tool, which is smart, powerful and easy to use. I have named it Easy MVC. Easy MVC will not only give you the new MVC DDL but also give you the proposed savings and savings percent in advance. Easy MVC tool is smart enough to give you the actual savings rather then calculating for overall mvc values which are already compressed. Easy MVC is divided into 3 modules.1) Single Table MVC,where in single table MVC can be done at a time.2) Multi Table Auto MVC,where in you just need to give databasename (optional),tablesize (optional), and wheater you want to search for all tables or the tables where are not MVCed.3) Multi Table Manual MVC,This module will provide you the facilty to put the databasename and tablename manually and as many as you want.Its core power lies in its Resumeable facilty. Its capable of restarting in case if the database is down or connectivity is lost, whenever the connectivity is back or databaseis backup just resume from the same point.There are many more features....This a setup file.If any of the Masters require a demo please mail me or can also call on +91-9820472730.RegardsHardik Dhruva.

Teradata Fast Export and Fastload Scripting Syntax

learninggeek — Mon, 09 Nov 2009 13:18:24 GMT

Hi All,I am working with Teradata Fast load and Fast export scripts. I would like to know any site or book that can provide me with in depth knowledge of loading scripts. Kindly suggest.Thanks in Advance.RegardsVarray

TPT Script Multiple Table Question

Ray Uranker — Thu, 05 Nov 2009 16:19:40 GMT

Lets say I have 10 tables in database ABC. I need to move the data in these 10 tables from our production box to our test box. The tables in production are on separate Teradata machine than the ones on test (teraprod vs teratest) but the tables themselves are exactly the same when it comes to fields and data types.I know I can create separate TPT scripts to move the data. My question is whether I can put all the script logic in a single JOB? I used the wizard to create a successful script, tried adding in another table and the script would no longer run (compilation error). Or am I forced to put each table in its own TPT script and execute each script.We are on v12.1 I did not find anything in the TPT user documentation.

TPT And Mainframe Chanel Attached Systems

Ray Uranker — Thu, 05 Nov 2009 16:31:29 GMT

We have our production Teradata box connected to the production mainframe via a TDP channel. Our test Teradata box is connected to the test main frame via a different TDP channel.If I initiate a TPT job on the production mainframe, will the TPT script be able to copy data from the production Teradata box to the test Teradata box? Or will I need to run the TPT script on a platform that has access to both or does TPT really only use the TDP channel to get to Teradata and the rest is between the two Teradata boxes?

Failure 2673 The source parcel length does not match data that was defined.

suchitra — Mon, 11 Jun 2007 23:23:27 GMT

I have two bteq scripts, one exports a table to a flat file, the other imports the data right back from the flat file.

My export script :

.export report file= src_qry.out
select count(*) (TITLE' ') , Null (TITLE' ') , NULL (TITLE ' ') , NULL (TITLE ' ') from fds_etl_target.ods_cntrl_all_d; FROM mydb.mytbl ;
.export reset

My import script :

bteq <.logon system/uid,pwd

.set width 2500;
.set titledashes off;
.set foldline on;

.IMPORT FILE =src_qry.out;
.QUIET ON
.REPEAT *
USING srcresult_nbr1 (INTEGER)
, srcresult_nbr2 (INTEGER)
, srcresult_nbr3 (INTEGER)
, srcresult_nbr4 (INTEGER)

INSERT INTO mydb.mytbl
( 1,
1,
current_timestamp,
:src_nbr1,
:src_nbr2,
:src_nbr3,
:src_nbr4,
'-2',
'MANUAL',
'POST_AGNT',
-2 ,
-1,
'2007-06-01 18:50:18' ,
2,
'2007-06-01 18:50:18' ,
'2007-06-01 18:50:18' ,
'2007-06-01 18:50:18'
);

.logoff
.quit
EOF
The error I am getting
*** Warning: No IMPORT mode was given, assuming field mode.
*** Growing buffer to 65473
*** Failure 2673 The source parcel length does not match data that was defined.
Statement# 1, Info =6

*** Warning: Out of data.

Can I rollback in ANSI mode without Transient Journel?

ravindrareddy — Fri, 06 Nov 2009 14:59:57 GMT

Can I rollback in ANSI mode without Transient Journel?I have a requirement to update a table...if the number of records which are updated is not matched with the intended number of records it should not commit to DB.Is there any way to accomplish this other than using ANSI mode/Transient Journal??Coz..ours is a very small system with 20 AMPS.

ANSI Mode BTEQ Script

ravindrareddy — Fri, 06 Nov 2009 11:18:34 GMT

Hi All,I need to write one Bteq script to update my table.Along with this I need to take the number of records it is going to update by running select statement before Update statement.Once update statement is complete..I want to see how many records this Update statement has updated...If the prior count and after counts are matching Commit this operation else through an error by releasing the lock and do a ROLLBACK.As per my knowledge I need to go for ANSI transaction mode...I'm very new in using ANSI mode in BTEQ...Could any one help me in providing sample BTEQ script for this scenario...Thanks In Advance Learn And Share...URSRavindra Red

passing parameters to mload

TD DEV — Thu, 05 Nov 2009 15:38:17 GMT

Hi guys,I donot know the syntax for passing parameter to mload. I am trying to pass two strings as parameter to mload script file and use these string parameters in the script.something like mload [TableName, SOURCEFILENAME] <Path of sourcefilename. Here is my scenario:This is the part of mload script where i want to use the passed string(in INSERT STAMENT below). In TestTable(Teradata table) , there is a column called stream which contains the source file name from which data is loaded into TestTable in Teradata.In this case the source file name is 'Caliente' which is entered manually in the insert statement below.There are many source files like these from which i need to extract data from and put the corresponding source file name in Stream column. So as first step i would like to pass the source file name as parameter to mload while calling the file that contains the script.INSERT INTO "TestTable " ( Dance_ID, Delete_Flag, Cat_ID, Non_Music, Mark_Status, Run_Time, Daypart, Add_Date, Add_Date_DATE, Add_Date_TIME, Del_Date, Del_Date_DATE, Del_Date_TIME, Cat_Plays, Lib_Plays, Old_Skips, Old_Fails, Perf_Count, Rot_Weight, Max_Daily, Old_Start, Old_Kill, Kill_Plays, Simulcast, Tag_Along, Packet_ID, Audio_Ptr, Master_Ptr, Edit_Date, Edit_Date_DATE, Edit_Date_TIME, Move_Date, Move_Date_DATE, Move_Date_TIME, Move_Cat, Start_Hour, Start_Hour_DATE, Start_Hour_TIME, Kill_Hour, Kill_Hour_DATE, Kill_Hour_TIME, Stream ) VALUES ( :Dance_ID, :Delete_Flag, :Cat_ID, :Non_Music, :Mark_Status, :Run_Time, :Daypart, :Add_Date, :Add_Date_DATE, :Add_Date_TIME, :Del_Date, :Del_Date_DATE, :Del_Date_TIME, :Cat_Plays, :Lib_Plays, :Old_Skips, :Old_Fails, :Perf_Count, :Rot_Weight, :Max_Daily, :Old_Start, :Old_Kill, :Kill_Plays, :Simulcast, :Tag_Along, :Packet_ID, :Audio_Ptr, :Master_Ptr, :Edit_Date, :Edit_Date_DATE, :Edit_Date_TIME, :Move_Date, :Move_Date_DATE, :Move_Date_TIME, :Move_Cat, :Start_Hour, :Start_Hour_DATE, :Start_Hour_TIME, :Kill_Hour, :Kill_Hour_DATE, :Kill_Hour_TIME, 'Caliente' ) ;The other string parameter should be used to specify the source file name in import infile statement. In this case:(see the code below)instead of C:\TD_ETL\ACEESS_ FILES_TEST\TestTable _Caliente.amj i should have C:\TD_ETL\ACEESS_ FILES_TEST\SOURCEFILENAME.amj where SOURCEFILENAME is other parameter passed in the mload. .IMPORT INFILE "C:\TD_ETL\ACEESS_ FILES_TEST\TestTable_Caliente.amj" AXSMOD Oledb_Axsmod 'noprompt' LAYOUT Layout1 APPLY LabelA ;Thanks in advance..

Teradata compatibility with new version of windows (windows 7)

jesu maria antony — Fri, 06 Nov 2009 10:22:34 GMT

hi,i installed the new version of windows (windows 7) . the version of teradata i tried to install was 12.1 . I am not able to install all teradata tools like sql assistant, administrator .etc .. ,. all the installations fail in windows 7 . Is there any way to sort this out ,,, can anyone help me out in this ???Thanks in advanceregards,Jesu

In TPT UPSERT on a table with an Identity Column primary index is illegal

Umesh Goel — Wed, 04 Nov 2009 05:17:07 GMT

Hi While using TPT M getting below error message in upsert UPSERT on a table with an Identity Column primary index is illegalMy Query is like below : APPLY ( 'UPDATE EIWStageTB.CoorsPointInventoryChange SET SuspenseFlag = :SuspenseFlag WHERE DistributorCd = :DistributorCd (DECIMAL(18,0)) AND MaterialNo = :MaterialNo (DECIMAL(18,0)) AND SeqNbr = :SeqNbr (INTEGER) AND WeekNo = :WeekNo (DECIMAL(18,0)); ',

Teradata TPT Script

Ray Uranker — Wed, 04 Nov 2009 16:25:55 GMT

I am attempting to write a TPT script to move data from one table to another. The tables are on two different Teradata systems (one is production the other is test). My source and target table are exactly the same in every way (fields, datatypes,...). I was able to get the script to work no problem.Here is my question. Since the tables are exactly the same, does the script need to have DEFINE SCHEMA listing all the fields and data types? Sure would be nice not to have to define field level details. My ultimate goal is to create a dynamic TPT script moving a variable table list and it would be sure nice not to have to go down to field level definitions.

Database Tree in Teradata SQL Assistant 12.0

peterg — Fri, 30 Oct 2009 00:54:52 GMT

Hopefully a simple question - although trying to find an answer has been anything but!Using Teradata SQL Assistant 12.0 on Windows XP Service Pack 3Have many databases listed in my Database Tree accessing the many different areas of our Teradata DW.Changing laptops, appears I have to re-add these Databases every time.Simple question:Where is this list of Databases stored on my current laptop - they appear to be unique to my username on this device?Appreciate your consideration - Thanks in advance

How to search through multiple stored procedures?

ratu — Wed, 28 Oct 2009 14:17:13 GMT

Hi All,I am trying to search for dependencies in our database, and I have about 500 stored procedures to go through. Is there an easy way to do this?I compiled all the show procedures as follows:SHOW PROCEDURE database_name.procedure_name_1;SHOW PROCEDURE database_name.procedure_name_2;SHOW PROCEDURE database_name.procedure_name_3;SHOW PROCEDURE database_name.procedure_name_4;SHOW PROCEDURE database_name.procedure_name_5;etcDoes anyone know how I can save the result set into a table or export it to a text file where I can perform my searches on the keywords?Thanks!

MLoad Question

Greyghost — Tue, 27 Oct 2009 09:45:08 GMT

Can someone explain this, since I don't understand what is happening. My MLoad runs to a good completion code. However, my question is about the number of records read in, compared to the number inserted. Records read = Records that satisfy the conditions. However, the records inserted does not equal these numbers. Why? There are no UV or ET records written, so I am confused why the number inserted is not the same as the records that satisfied conditions.Here are the stats from the log file: . IMPORT 1 Total thus far . ========= ============== Candidate records considered:........4662284....... 4662284 Apply conditions satisfied:.......... 4662284....... 4662284 Candidate records not applied:....... 0....... 0 Candidate records rejected:.......... 0....... 0----------------------------------------------------------------------------------------------- Inserts: 4624210 Updates: 0 Deletes: 0 ============== ======================================================== 0 TDWWorkTbls.ET_IMFStage 0 TDWWorkTbls.UV_IMFStageThanks!Paul

How to Convert VARCHAR(30) to a DATE in Fastload

JB — Thu, 03 Sep 2009 18:22:43 GMT

I have a table coming in from Sybase using BCP. It is pipe delimited. There is a Date field I will call RecordDate. It is Varchar (30) but it needs to be a DATE so that it can be compared to another table using the date and some other fields. I tried several ways to format the fastload program to bring in and format a DATE instead of a VARCHAR(30) but the program will only work if I bring it in as a varchar (30). I looked at the input file and it is pipe delimited, but in the varchar(30) field, I only see something like this: 12/31/2008 I am a supervisor and have been hands off for awhile, but we are short handed and I'd like to get this working. Any suggestions? Please? Thanks.

Import in TD SQL Assistant

Nisith Bhattacharya — Mon, 27 Aug 2007 18:30:42 GMT

Hi All,
I have exported a table to a file using the export facility of SQL Assistant.
The export was successful and the file was created.
Please help me to import the file.
Thanks.

query does not complete -- but no error message

alandool — Thu, 22 Oct 2009 12:07:11 GMT

Hello All,I have a query that has 11 steps in it where it creates several volatile tables in Teradata SQL Assistant. For some reason it stops after the fourth step and gives no error message, etc. I can then highlight and run the additional steps without problem. I need it to run straight through. Any ideas why this may happen?Alan

Insufficient memory to parse request during Optimizer phase.

Toshu — Mon, 08 Jun 2009 02:06:24 GMT

Hello,i'm running BTEQ via MVS Batch (BTQMAIN program).From yesterday, i'm getting below error for my SQL:Failure 3710 Insufficient memory to parse this request, during Optimizer phase. Statement# 1, Info =0 I figured out that it has something to do with MaxParseTreeSegs. But don't know how to use it in my case. I don't have much access to teradata which is behind the seen.Is there any way to tackle this issue. May be by including some statement at the top of my SQL query?Please suggest!Thanks,Tushar Saxena

mload upload of csv file with Comma ',' and ""

amonadi@hotmail.com — Wed, 21 Oct 2009 17:54:35 GMT

Hi,I would like to know if there is any way to load csv files with mload when they contain quotations. Excel places quotations marks around cells that have "" commas as cell value.I know in Oracle there is and Optional tag in the control file to indicate data file may also be enclosed in double quotes. Is there such a think for Teradata Mload?I am aware of the option to save the file as Tab Delimited and then replace Tabs with "" but was hoping for a better solution than this.Thanks,

MLoad Update

DAZZLER — Tue, 20 Oct 2009 09:59:33 GMT

I have to Update only one field in the table with one valuea using MultiLoad (dont want to use BTEQ since DBA dislike to update table with >100k rows with BTEQ). For instance, SQL is following...Update DB.TABLE1SET FIELD1=703WHERE FIELD1=1;Now, i have done updates usnig field input, but never with one colum value. Do i have to treate this as file input and change SQL as below or is there any other way of doign it?Update DB.TABLE1SET FIELD1=:FIELD1WHERE FIELD1=1;

I am unable to load data using SQL Assistance

kiran_terydata — Thu, 10 Sep 2009 02:06:50 GMT

Hi FriendsI am unable to load the data when inserting nulls or strings in the table using QUERY MAN.insert into fact_current values(id, name, ,"expected",salary);Wat is the problem?Please tell me the solution.KIRANkiran_teradata@ymail.com

mload error 3524

yujing — Fri, 16 Oct 2009 10:32:13 GMT

We have a scheduled Teradata job runs everyday and works fine. Recently, this job failed with error code 3524, the user does not have CREATE TABLE access to database XXX ( our database name). I know that is not correct since it has run a while and nothing has been changed. We rerun the job using the same user after it failed; it ran successfully without any scripts changes. Have you met this problem before? We are on Teradata version 12. Really appreciate your input. Best Regards

Fast Export & Fast loading

Gurunathan — Tue, 13 Oct 2009 03:05:40 GMT

HiIn UNIX, I have fast exported 10 rows from source table(2 columns--item_id, site_id) to dw_abc(text format). For eg: .export outfile dw_abc format text; ** this is the syntax i have used.-------10 rows got exported, but the problem is when i was opened the output file "dw_abc" in UNIX. It was showing like this.item_id site_id100 200300 400500 600700800 ------------- item_id, site_id were not separated900 1000101201-------------item_id, site_id were not separated301 401501601-------------item_id, site_id were not separated701 801901 1001Some of the item_id, site_id got separted by tab as a delimiter. Some item_id, site_id's were not separted. I want, all the item_id, site_id should be separated by tab as a delimiter. Can anyone explain?Thanks for ur support!!!Thanks,Guru

Default value & compress value in table

Gurunathan — Thu, 15 Oct 2009 07:56:26 GMT

Hi everyone,Can anyone explain to me, What is the use of Default value & compress value in the table(DDL)? If we are not using Default & compress value in the DDL, how it would be in the performace wise? Thanks in advance !!! Thanks,Guru

Need of Collect Statistics

Gurunathan — Fri, 25 Sep 2009 01:46:57 GMT

Hi. What is the need of collect statistics in teradata? Can anyone explain with suitable examples? Also, How can we choose the collect statistics columns?

[MLOAD] putting a '?' value

bruce_edwards — Mon, 12 Oct 2009 09:43:36 GMT

Hello all,Having just started working on MLOAD, I stumbled on this problem :When charging a file, the tool finds the correct number of lines.The tools then proceeds to put all those lines in the [name of table]_UV table.When I then make a sel * on this table, I can see that every field of every line has a value which is '?'I can understand that there is a conversion problem.The file layout is defined as every field being a (long) varchar, and is then cast or trimmed in the insert into query...

Loading space into a Decimal(15,2) column

Anand JP — Tue, 05 Sep 2006 02:05:19 GMT

Hi,

We are facing issues in loading a text file from a Mainframe system to Teradata database using Mulitload. Here are the details.

Problem Description :
In the text file that we are loading most of the records has "spaces" instead of packed decimal values for the column/field for which the error is being reported. Mulitload loads the records having columns with proper packed decimal values but rejects records which has spaces with error code 2679.

File Format : Text
Source Field datatype : Packed decimal (cobol comp3)
Target TD Column datatype : Decimal(15,2)
Error Message : 2679 The format or data contains a bad character

Has anyone encountered this problem before.

Please let me know your comments/suggestions.

With Regards,
Anand

COMP causing Mload faliure

Satish Mee — Tue, 29 Jan 2008 04:36:39 GMT

Hi Gurus,As we know we have different ways to Compress the Decimal data Like COMP-3,COMP-4,COMP-5 and COMPFor example if we have COLUMN given as SALARY S9(13)V99 Comp-3 , we create Teradata table as SALARY DECIMAL(15,2)and LAYOUT as .FIELD SALARY * DECIMAL(15,2) and Mload works fine.The alternate way is ,we can define the layout as .FIELD SALARY * CHAR(8) (we are computing it as 13+2/2 = 8( rounding 7.5) )the mention CAST(:SALARY AS DECIMAL(15,2) FORMAT '9(13)V9(2)S') in the DML phase (Insert phase) then Mload works fine.Problem we are facing is with COMP format .we dont know the relevent teradata datatype for COMP.I got a COPY BOOK which has a COLUMN as BALANCE S9(13)V99 COMP.I defined it as BALANCE DECIMAL(15,2) and tried defining the layout in above mentioned 2 ways ,But Mload is failing with error-2679.I knew the data doesnt has any Bad Chars , it contains 0.00 values.What is the relevant teradata data type for S9(13)V99 COMP ?Can you please give me solution for this..?Hope someone who worked on surely gives me a Answer ..Thanks In Advance

NetVault and Alert Policy

SchmoT — Wed, 07 Oct 2009 17:39:48 GMT

Our shop is having difficulty with our NetVault Backup failing due to what seems to be our alert policy through TASM killing "idle" sessions of the backup.Shortly after our upgrade to TD 12.0.2.20 we implemented a policy through Teradata Alert Policy Editor to abort idle sessions after 1 hour. We then noticed that our weekly NetVault backup was failing due to a "DBMS Restart or Session Reset". We tried adding the 2 NetVault users to the Exception list in the Sessions tab of Alter Policy Editor, but the backup still failed with the same restart/reset error as before.Last Friday afternoon we modified the policy to email instead of abort after 1 hr idle time. The backup was successful but we never got an email stating that the NetVault user had exceeded the 1 hour threshold.Is it possible that the policy is aborting some sort of internal session that is used by NetVault but is not recognized as a valid user to send an email about? We have another policy that emails our DBAs if a session is blocked and it DOES email when -internal session- blocks another user's session. This is what is so confusing to me.Is there something I am missing?Thank you,Frustrated

Varchar in Fast Export

Sandi — Sun, 29 Jul 2007 17:06:41 GMT

Hi ,
when i use fast export utility for unloading records from a column having data type "varchar" i am getting two dots (Junk characters) infront of each record. I don't want to cast this field to CHAR. Could anyone please help me to resolve this issue?

Thanks and Regards
Sandi

Collect Stats - can you copy them?

epfatboy — Fri, 02 Oct 2009 08:53:06 GMT

Is there a way to copy STATS collected on a table to a duplicate table?We have a process which we refer to as CLONE TABLE Processing. Basically when we are required to modify a large table rather than performing an ALTER TABLE we create a new table with the new attributes required and perform a FASTLOAD or INSERT INTO. The process to duration to make the changes this way out performs ALTER TABLE for large tables. the problem is collecting stats takes a long time. We are also noticing that table sizes increase dramatically if we alter table vs performing clone table processing. In a recent example we increased a table size by 100GB by adding a single attribute using alter VS CLONE... very strange.

DBC.DBQLogtbl Application id meaning/definitions

Vincent Gabrielli — Thu, 01 Oct 2009 17:07:08 GMT

Hello support team members, I need your help. in the DBC.DBQLogtbl there is a field named APPID (The application ID under which the query is submitted) does anyone know what UVSH stands for? Example you also find BTEQ, SAS, JDBC, TPUMPEXE, TSCOM3.OUT these are known applications id’s