All Forums General
neilotoole 78 posts Joined 11/08
21 Apr 2010
Posting topics into the correct forum

As the volume of traffic on Teradata Forums increases, we're seeing more instances of topics being posted into the wrong forum. To combat this, forum moderators now have the ability to move topics into a more appropriate forum. If a topic you created (or commented upon) is moved, you will be sent an email with the new URL. But prevention is better than cure, so we ask that users please consider which forum is most appropriate *before* posting a new topic. Some of the forums are self-explanatory (Jobs, Training etc), but there seems to be confusion with some of the following:

  • General: For general discussion of Teradata; Teradata websites (including these forums, Teradata Developer Exchange, Teradata.com etc); strategy; general development topics, and topics that don't belong in any of the other forums. Do not post "general" SQL or database questions here; these belong in the Database forum.
  • Database: SQL questions. Query optimization questions. Performance issues. Most of your general-purpose database questions belong in here. Do not post issues related to Connectivity (JDBC, ODBC etc), Tools (SQL Assistant etc), or Enterprise-class features such as Data Mover, TMSM, TASM.
  • Enterprise: Teradata enterprise-level features and products: fallback; journaling; backup; Dual Active; TASM; TRS; Data Mover, etc.
  • Connectivity: JDBC, ODBC, .NET Provider etc. Everything to do with connecting to the database.
  • Extensibility: If you have questions about UDFs, this is the place. Also, Map-Reduce / Hadoop and Geospatial.
  • Cloud Computing: Teradata Express for VMware and Amazon EC2, and the Elastic Marts Builder portlets.

Also, if you haven't done it in a while, please review the general posting guidelines. It'll only take a moment.

Questions, thoughts?

DevX Platform Architect
Tags:
kattamadhu 6 posts Joined 02/11
10 Nov 2011

Hi any one help on this

 

question

 

I am working on TD13 trial version….

 

CREATE SET TABLE tduser.jn1_emp ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      emp_no INTEGER,

      emp_loc varchar(12))

Unique PRIMARY INDEX ( emp_no );

 

Insert into tduser.jn1_emp(1,’hyd’);

Insert into tduser.jn1_emp(2,’bang’);

Insert into tduser.jn1_emp(3,’visak’);

 

Collect stats on tduser.jn1_emp index(emp_no);

 

 

CREATE SET TABLE tduser.jn2_emp ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      pme_no INTEGER,

      emp_name varchar(12))

Unique PRIMARY INDEX ( pme_no );

 

Insert into tduser.jn2_emp(1,’raj’);

Insert into tduser.jn2_emp(2,’ravi’);

Insert into tduser.jn2_emp(4,’kishore’);

 

Collect stats on tduser.jn2_emp index(pme_no);

 

If I am trying to execute the following it is giving “low confidence” in the explain plan.can anybody suggest how to make it to “high confidence”

 

Explain sel * from tduser.jn1_emp, tduser.jn2_emp

Where emp_no = pme_no

 

chaitanya09 13 posts Joined 07/12
26 Sep 2012

HI All,
I need a small calrification on why my SQl is not giving me subtotals in the Answer Set when I run a query in Teradata SQL Assisstant.
I have used thw WITH .. BY clause in my SQL and still I am facing issue.Could some one help me on this..
Thanks in advance.
 

Chaitanya

Qaisar Kiani 337 posts Joined 11/05
26 Sep 2012

You should be able to see the results in BTEQ output, SQLA are limited to only showing peoper table format results. Subtotals adds additional rows with limited contents only and thus are skipped by SQLA.
Not sure if the newer versions of the SQLA have this capability now or not!
To test your results, you can write a BTEQ script output in file.

chaitanya09 13 posts Joined 07/12
26 Sep 2012

 

 

Hi,
I have used a BTEQ Script and exported the data to CSV file, but what happens is all the columns are being displayed in only one column in the excel. Is there any way, by which I can get the data in seperate columns and in correct format. Below is the script template i used.Could you please suggest something
 

.SESSIONS 1 

.SET SESSION TRANSACTION BTET;

.SET ERROROUT STDOUT ; 

.LOGON hw41,hw41

.EXPORT RESET;

.export report file='c:\us1.csv';

.SET RECORDMODE OFF; 

.SET FORMAT OFF ; 

.SET TITLEDASHES OFF; 

.SET SEPARATOR ' ' ; 

.SET WIDTH 500;

 

 

----- MY Query ------

 

 
.exit

 

Chaitanya

Adeel Chaudhry 773 posts Joined 04/08
26 Sep 2012

Guess you didnt read what Neil wrote above.

-- If you are stuck at something .... consider it an opportunity to think anew.

chaitanya09 13 posts Joined 07/12
27 Sep 2012

I did not get what you exectly meant, Is it that I have posted in wrong forum..If so, am sorry but as I was not sure where to put my question I just came to this "GENERAL" forum...
 

Chaitanya

krsna 1 post Joined 09/12
27 Sep 2012

In teradata
How to select non printable characters without any space?
How to Trim non Printable characters?
 
Regards,

Regards,

WAQ 158 posts Joined 02/10
28 Sep 2012

- General SQL and database question goes under "Database" section.
- Always start a new thread to get your question answered instead of using the existing thread.

ronaldneck 1 post Joined 11/12
09 Nov 2012

First we have to check whether posting a forum site is good for our topic or whatever else?

raviteja4017 1 post Joined 11/12
16 Nov 2012

how many tables we can load in mload?

dnoeth 4628 posts Joined 11/04
19 Nov 2012

Five.
Btw, your topic perfectly matches the topic of this thread :-)
Dieter

Dieter

gryback 271 posts Joined 12/08
20 Nov 2012

Regarding the mload table question, please post this as a new forum posting in either the Tools or Database forum group. Posting it here most likely will not get you an answer. 

30 Nov 2012

Quick question regading NUMBER vs DECIAL in TD 14.0 :
90% of our join access and optimal path based on Pers_id & Clent_Ky. Client keys start from 1 to 450 as smallint and Pers_id is always 22 degit number as DECIMAL(22.0).
Here is the details of datatypes for  these two columns and these two columns part of the PRIMARY INDEX,PPI,  90% of my warehouse tables.
PERS_ID DECIMAL(22,0) NOT NULL,
CLIENT_KY SMALLINT NOT NULL,
PPI on CLIENT_KY:
PARTITION BY ( RANGE_N(CLIENT_KY  BETWEEN 1  AND 450  EACH 1 ),
 
We are not able to accommodate the 22 digits of the Pers_id. As part of Oracle to teradata conversion lift and drop, we moved all objects from Oracle to teradata.
 
In Oracle, PERS_ID is NUMBER 22, they converted PERS_ID as DECIMAL (22,0) in teradata.
My Question is Here:
- Can we change PERS_ID as NUMBER in Teradata? and is there any impact , as this new datatype in 
 TD  14.0
- Looks like NUMBER is belongs to DECIMAL family too?. Correct me if I am wrong, Is it really work as DECIMAL or an INTEGER. Can you please clarify, my PERS_ID column is always 22 digits INTEGER  field.
 
Reasons why we are asking this question because, integers give better performance over decimals, is integer comparison is bit level while decimals at character level(i.e. byte level).
 
The main reason they are higher performing, is that the CPU can perform comparison with binary subtraction, on the CPU's registers, where the Decimal is always character-for-character, usually in a loop in firmware, although the machines might actually compare them several bytes at once, they are still treated as separate bytes. Longer decimal values degrade faster than shorter ones for this reason, so you might not see measurable difference between an integer and decimal for tens of thousands of records, but you will for millions of records. The wider the decimal width, the poorer the performance (compared to integer equivalent).
NUMBER is a new data type, introduced in Teradata 14.0, which is intended to emulate the Oracle number data type. It has an optional precision and scale of up to 38 decimal digits.  It can also represent exponential values. Its storage size varies from 0 to 18 bytes.

bispsolutions 6 posts Joined 12/12
07 Dec 2012

Dear All,
To learn TERADATA from industry experts, join our professsion training program 
 
 

We have scheduled a free demo class ( online ) of  TERADATA DBA on Saturday, 08 December at 8:00 AM IST followed by regular classes from Thursday, 13 December at 8:00 AM IST 

 

Below is the Login details :

 

1.  Please join my meeting.
https://global.gotomeeting.com/join/608903261
 
2.  Use your microphone and speakers (VoIP) - a headset is recommended.  Or, call in using your telephone.
 
Dial +1 (805) 309-0027
 
Access Code: 608-903-261
 
Audio PIN: Shown after joining the meeting
 
Meeting ID: 608-903-261
 
GoToMeeting®
 
Online Meetings Made Easy®
 
Free Sample Video, course content & fee details  you may download from our websitehttp://www.bispsolutions.com

 

vandy 6 posts Joined 01/13
31 Jan 2013

Hi people,
I am new to using Teradata.

Can someone please let me know the below queries:

 

1.Query to find the scale of the column in teradata

2.Query to find the foreign keys of a table in teradata

3.Query to find the association between two tables in teradata.

 

Thanks in advance !

 

Vandy :)

 

 

 

Sudheerkunapuli 2 posts Joined 03/13
01 Mar 2013

 
Which statement is true about a Primary Index operation in Teradata?
 
a. recommend Secondary Indexes for a table
 b. compare performance of multiple workloads
 c. emulate a workload on a test system
 d. recommend Primary Indexes for a table 

26 Jul 2013

ok.thank u

mini.angi 1 post Joined 11/14
02 Nov 2014

Hi 
Im Kind of new to teradata and have been working on a requirement.
I have been doing a lo of reading but i'm unable to find any help on the problem.
My question is- Is it possible to connect teradata to a website and then dowload data from there?
Any help will be highly appreciated.
Thanks
Mini
 

Kumar@TD 7 posts Joined 11/14
21 Nov 2014

Hi 
am facing an issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW
how to use with clause on DBC.DISKSPACE . any help will be appreciated
INSERT INTO TD_DISK_USG

(

DATABASENAME

,CUR_DT

,PRMNT_SPC_ALCTD

,PRMNT_SPC_USD

,RMNG_SPC_GBS

,PRCNT_SPC_USD

,DSK_EFCNCY

)

SELECT

DATABASENAME

,CURRENT_DATE

,SUM(MAXPERM)    AS PRMNT_SPC_ALCTD

,SUM(CURRENTPERM) AS PRMNT_SPC_USD

,(SUM(MAXPERM) - SUM(CURRENTPERM))/(1024*1024*1024) AS RMNG_SPC_GBS

,SUM(CURRENTPERM)/SUM(MAXPERM)*100  AS PRCNT_SPC_USD

,(AVG(CURRENTPERM)/(.1+MAX(CURRENTPERM))*100) AS DSK_EFCNCY

FROM DBC.DISKSPACE

GROUP BY DATABASENAME

HAVING PRMNT_SPC_ALCTD > 0

WITH PRMNT_SPC_ALCTD,PRMNT_SPC_ALCTD,PRCNT_SPC_USD

ORDER BY PRCNT_SPC_USD

;

 
 

kumkum 2 posts Joined 12/15
21 Dec 2015

I have a SCD2 loading requirement which has to be done for some 50-60 tables.source table,target table & Surrogate Key column has to be parameterized.The sample output will look like as follows:

s_key col1 effective_from_date effective_to_date recent_flag
100 abc 1/1/2015 12/31/9999 1
200 xyz 1/1/2015 1/7/2015 0
200 xyzabc 2/7/2015 12/31/9999 1

For update logic is

Select (sysdate - 1) as effective_to_date,
0 as recent_flag ,
B.
From A [Argument 2] ,
B [Argument 1]
WHERE A.< Surrogate Key > = B.< Surrogate Key > [Argument 3]
AND B.recent_flag =1;

Please let me know how can I pass table names in the above code as a parameter.

kumkum 2 posts Joined 12/15
29 Dec 2015

I need to load data from Oracle table to Teradata table.Please let me know the options.
Thanks in advance.

ASHU_123 5 posts Joined 12/15
29 Dec 2015

My source data is in Oracle and target data is in Teradata.Can you please provide me the easy and quick way to validate data .There are 500 tables

Ashu

sgarlapa 88 posts Joined 03/13
02 Jan 2016

I guess you can use TPT . In TPT, temporarily load data from oracle to teradata and with in same script, compare the temp table and corresponding teradata table, export results into an output file and drop the temptable which was loaded with oracle data. 
You can pass the table name as parameters throug unix shell script and rotate the loop till end of the paramters in the while loop of shell.
 

ASHU_123 5 posts Joined 12/15
04 Jan 2016

My SOurce Data is in oracle and Target is in Teradata.Can you please provide me easy and quick way to validate Records between oracle and Teradata.There are 500 Tables.

Ashu

15 Apr 2016

For the following relation schema: 

employee(employee-name, street, city) 

works(employee-name, company-name, salary) 

company(company-name, city) 

manages(employee-name, manager-name)

 

a) Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000.

b) Find the names of all employees in the database who live in the same cities as the companies for which they work.

c) Find the names of all employees in the database who live in the same cities and on the same streets as do their managers.

d) Find the names of all employees in the database who earn more than every employee of 'Small Bank Corporation'

e) Assume that the companies may be located in several cities. Find all companies located in every city in which 'Small Bank Corporation' is located.

You must sign in to leave a comment.