Blog Archive

Search This Blog

Saturday, August 31, 2013

Connecting to Multiple Data Sources

You can connect to multiple data sources.
The following steps explain how.

To connect to multiple data sources
1 Go to the Tools > Options > General tab.
2 Click Allow connections to multiple data sources (Query windows),
3 Follow the procedure for connecting to a data source.
Each new data source appears in the Database Tree and opens a new query window with the
data source name. To disconnect from one data source, click the Query window that is
connected to the data source and click the disconnect icon.

Monday, March 4, 2013

Teradata Express 14.0 for VMware User Guide

Teradata Express for VMware (TDE-V) is a free, fully operational Teradata VM with up to one terabyte of storage. Imagine being able to install a fully operational Teradata database on your PC and be running queries in five minutes, easy as 1-2-3.
After installing VMware Server/Player and downloading your choice of VM, this is all it takes:
  1. Install the VM
  2. Start the VM and Teradata
  3. Use the Teradata Studio Express to run queries
To help you load data, the new Teradata EZLoader utility is included in the VM.
Depending upon your needs and the resources available on your PC, three versions of Teradata Express14.0 are available. Please note that the resources needed for Teradata Express are in addition to those needed by the operating system on your PC:
  • TD Express 14.0 with 4GB of storage. Requires 13 GB of disk space and 2.0 GB of RAM for the Virtual Machine.
  • TD Express 14.0 with 40GB of storage. Requires 18 GB of disk space and 2.5 GB of RAM for the Virtual Machine.
  • TD Express 14.0 with 1TB of storage. Requires 35 GB of disk space and 4.0 GB of RAM for the Virtual Machine.
A 64-bit virtualization-capable PC is required.  VMware provides a utility to check your system for 64 bit support at this link.
Please note that while the Teradata Express family of products is not officially supported, you can talk to other users and get help in the Cloud Computing forum. Note also that Japanese-language instructions for configuring TDE-V are available for download in PDF format.

Getting Started

The first task is to make sure you have a system capable of handling VMware and VM’s. There are plenty of details on the VMware site but here are some basic requirements that you should be aware of before getting started:
  1. Since the SLES10 VM’s are 64-bit, your CPU must support 64-bit operation.
  2. Your CPU must also support Virtualization. Generally there is a BIOS setting which enables this. Google the topic for your particular CPU for more information but most recent PC’s support both 64-bit and Virtualization.
As soon as you determine your system supports the requirements you can proceed:
It's time to run VMware Player and start Teradata Express.  From the VMware Player Welcome page, choose "Open a Virtual Machine" and click your way through your file directory to the Teradata Express folder, looking for the "TDExpress14.0_sles10.vmx" file.

Figure 1. Loading the TD Express virtual machine in VMWare Player
This step is very important.  As you click through the virtual image directories, VMware is looking for the .vmx file, in order to start the image.  Once you find it, double-click or choose the 'Open' option.  VMware will now present you with a dialog box asking if you copied or moved this image.  Be sure to choose  'I MOVED IT'!
  1. VMware Player and VMware Server are both available for free download from the VMware site and both will work. This tutorial describes using VMware Server (hosted on a Windows system). If you have not already done so, make your choice and install VMware on your system.
  2. Disk space is a big consideration. 4 GB, 40GB and 1TB versions of TDE-V are available depending on your resources and need. Typically you don't actually need the full amount of available disk space (although this would be advisable) to install and get started. Also, isolating VM’s on their own physical disks (if available) can improve performance. Some additional information about disk space is provided below.
  3. Download the appropriate TDE-V image from the downloads section.
  4. If you do not already have 7-zip download it here
  5. Create a directory on your C:\ drive named "virtual-machines". After unzipping (7-zip) the file you will end up with something like “C:\virtual-machines\TD14..”.
  6. Now you need to add the VM to the VMware inventory. Using VMware Player (see Figure 1 above for reference):
    1. Click on Open A Virtual Machine
    2. Drill down under inventory, highlight the folder (sles10_TD1400) and item in contents (sles10.vmx), OK
  7. The VM will show up in the Library under the Home tab and can be started and stopped from there.
  8. Double click on the TD Express image to start.
  9. Login into the SLES10 VM with username root and password root.
  10. Wait. The image will take some time to initialize.
  11. Test with bteq (bteq is the standard Teradata command line query tool. It can be invoked from the Linux command line in the Gnome window as follows.
    1. From the shell prompt: TDExpress14.0_Sles10:~ # bteq
    2. When asked for your logon: .logon
    3. When asked for your password: dbc
    4. You should now be in the bteq session (you'll see a message *** Logon successfully completed).
    5. Now, let's execute some SQL, e.g. select * from dbcinfo; and you should see results similar to:
      select * from dbcinfo;
       *** Query completed. 3 rows found. 2 columns returned.
       *** Total elapsed time was 1 second.
      InfoKey                        InfoData
      ------------------------------ --------------------------------------------
      LANGUAGE SUPPORT MODE          Standard
    6. And you can now quit bteq by executing: quit

Figure 2. Testing Teradata using BTEQ

Monitoring Teradata Express using Viewpoint

You have the option of running Viewpoint on the virtual machine. To see all you can do with Viewpoint look here.
Viewpoint services are not automatically started. To begin the Viewpoint services click on the "Viewpoint Start" icon on the Teradata Express desktop.
To login to Viewpoint follow the steps below.
  1. Open a Gnome terminal. Firefox is included in the image and is able to run inside the virtual machine.
  2. Start Firefox: TDExpress14.0_Sles10:~ # firefox &
    • The "&" starts firefox in the background.
  3. Wait. Firefox will take a couple of minutes to initialize.
  4. The Viewpoint page should come up automatically, but if it doesn't, enter this URL: http://localhost/c
  5. Login to Viewpoint as username = "admin" and password = "teradata".
  6. Towards the upper right of the main Viewpoint portal page, click "Admin" then "Teradata Systems"
  7. Add a System. Under Setup/General
    • System Nickname - "TDExpress"
    • Check the System Enabled box, next to the System Nickname
    • TDPID is set to only monitor localhost and cannot be modifed
    • Login Name = "viewpoint"
    • Login Password = "viewpoint"
    • Check Enable all data collectors.
    • Apply
  8. Under Setup/Data Collectors. Set all data collectors to delete data after 50 MB or 1 week
  9. Under Setup/Monitor rates. Check Enable Session.
See the General Set Up Screen Bleow

Loading Data

On the Teradata version 14.0 VM’s the new EZloader utility is included for fast and easy data loads. Here's an example of loading some data from a comma-separated file (i.e. a CSV). From BTEQ, run the following:

CREATE user vmtest AS password=vmtest perm=524288000 spool=524288000;
CREATE SET TABLE vmtest.test ,
Test_field1 INTEGER,
Test_field2 INTEGER)
PRIMARY INDEX ( Test_field1 );

Create a file called test with contents that look something like this:

Run the load utility:
/opt/teradata/client/13.0/tbuild/bin/tdload -f test -u vmtest -p vmtest -t test
That is it, data loaded!


Tuesday, February 12, 2013

Difference between a View and Materialized View?

View is nothing but a set a sql statements together which join single or multiple tables and shows the data .. however views do not have the data themselves but point to the data .

Whereas Materialized view is a concept mainly used in Datawarehousing .. these views contain the data itself .Reason being it is easier/faster to access the data.The main purpose of Materialized view is to do calculations and display data from multiple tables using joins .

view: View is a logical or virtual memory which is based on select query

and the simple view is the view in which we can not make DML command if the

view is created by multiple tables.

Materialize veiw: It works faster than simple, Its works as snap shot and used for security purposes and we can make DML command in materialize view

materialized views are those to view the data which is located inthe remote place. As name suggest, the materialized view is not the "VIEW" but the data itself..of the remote schema

Materialized View or mainly used in dataware housing.Materialized Views are more effective in multiple conditions in ware clauses.Materialized Views are summery tables(i.e the most frequently viewd data for analysys is kept inside the table) to faster retreval of data so that joins will minimizes so the performance going to be increases.

View is a logical representation of data--- m.view it is physical duplicate representation of data
view dont holds data---m.view holds data
in view for every event on base tables view automatically update immediately---- in m.v we can update for a certain period of time

A view is nothing but a SQL query, takes the output of a query and makes it appear like a virtual table, which does not take up any storage space or contain any data

But Materialized views are schema objects, it storing the results of a query in a separate schema object(i.e., take up storage space and contain datas). This indicates the materialized view is returning a physically separate copy of the table data

This is the major difference between View and Materialized View

A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.
In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.
A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.
The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.
You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.
A materialized view can be stored in the same database as its base table(s) or in a different database.
Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

Tuesday, February 5, 2013