1stenjoydmoment

Very Often we may need to check avaiable tables in a database or list columns in a table.every database has its own syntax to list the tables and columns.Here we will see How to List All Tables in Oracle, MySql, Teradata, DB2 and PostgreSQL. Oracle To list all tables owned by the current user, type: select tablespace_name, table_name from user_tables; To list all tables in a database: select tablespace_name, table_name from dba_tables; To list all tables accessible to the current user, type: select tablespace_name, table_name from all_tables; To describe a table, type: desc <table_name>; MySQL To list all databases, in the MySQL prompt type: show databases Then choose the database: use <database-name> List all tables in the database: show tables Describe a table: desc <table-name> DB2 List all tables: db2 list tables for all To list all tables in selected schema, use: db2 list tables for schema <schema-name> To describe ...

Explanation: This error occurs when a VProc cannot be started due to an invalid DBS state during StartUp. Generated By: AMP Startup modules. For Whom: Operator. Notes: For a VProc to start, the appropriate DBS state is either SysStartup, SysLogoff or SysLogon state. Remedy: Restart the DBS after booting the node.

Explanation: This is a warning message. It occurs when the total number of rows requested in the sample is larger than the total number of rows in the table. Generated By: AMP Sample Step code - stpsamp. For Whom: End User. Remedy: In this case, all the rows in the table will be returned. Each sample request will be honored until the total number of table rows has been reached. After that, no rows will be returned for the sample.

The below query is used to identify the data distribution across AMP's in Teradata SELECT HASHAMP(HASHBUCKET(HASHROW( index_columns ))) AS AMP_NO , COUNT(*) FROM databasename.tablename GROUP BY 1 ORDER BY 1;

The below query helps you to identify the total space occupied by the tables in teradata system. SELECT databasename, tablename, SUM (currentperm)/1024**3 AS current_GB FROM dbc.allspace WHERE tablename IN ('TableName') AND databasename = 'DatabaseName' GROUP BY 1,2 ORDER BY 1,2; The above query will return 3 columns namely , database name , table name and amount of space table used in teradata system.

Teradata SQL Assistant Shortcuts ctrl + T This will clear all the queries in the Teradata Query Editor ctrl + Q This will format the query in the Teradata Query Editor ctrl + D This is used to comment and uncomment the selected query text in the Teradata Query Editor F11 This will get you the last error message ctrl + C This is used to copy the selected Text in the Teradata Query Editor Ctrl + P This is used to paste the copied content into the Teradata Query Editor Ctrl + F This is used to find a Text in Teradata Query Editor Ctrl + H This is used to Find and Replace the Text in Teradata Query Editor Shift + F10 + I This will get you the current date in the Teradata Query Editor F5 This will execute the Query in the Current Query Tab in the Teradata Query Editor F6 This will get you the execution plan for the query in the Query Editor F9 This is used to execute the Query in Parallel.

Data Mart Pros and Cons Independent Data Marts Independent data marts are usually the easiest and fastest to implement and their payback value can be almost immediate. Some corporations start with several data marts before deciding to build a true data warehouse. This approach has several inherent problems: While data marts have obvious value, they are not a true enterprise-wide solution and can become very costly over time as more and more are added. A major problem with proliferating data marts is that, depending on where you look for answers, there is often more than one version of the truth. They do not provide the historical depth of a true data warehouse. Because data marts are designed to handle specific types of queries from a specific type of user, they are often not good at “what if” queries like a data warehouse would be. Logical Data Marts Logical data marts overcome most of the limitations of independent data marts. They provide a single version of the trut...

Advantages of Using Summary Data Until recently, most business decisions were based on summary data. The problem is that summarized data is not as useful as detail data and cannot answer some questions with accuracy. With summarized data, peaks and valleys are leveled when the peaks fall at the end of reporting period and are cut in half. Here’s another example. Think of your monthly bank statement that records checking account activity. If it only told you the total amount of deposits and withdrawals, would you be able to tell if a certain check had cleared? To answer that question you need a list of every check received by your bank. You need detail data. Decision support-answering business questions-is the real purpose of databases. To answer business questions, decision-makers must have four things: The right data Enough detail data Proper data structure Enough computer power to access and produce reports on the data

Key Features of Teradata The following are the key features of Teradata database, Single data store Scalability Unconditional parallelism (parallel architecture) Ability to model the business Mature, parallel-aware Optimizer Single Data Store The Teradata Database acts as a single data store, with multiple client applications making inquiries against it concurrently. Instead of replicating a database for different purposes, with the Teradata Database you store the data once and use it for many applications. The Teradata Database provides the same connectivity for an entry-level system as it does for a massive enterprise data warehouse. Scalability “Linear scalability” means that as you add components to the system, the performance increase is linear. Adding components allows the system to accommodate increased workload without decreased throughput. Linear scalability enables the system to grow to support more users/data/queries/complexity of queries without experie...

How Is the Teradata Database Used in Data Warehouse Each Teradata Database implementation can model a company’s business. The ability to keep up with rapid changes in today’s business environment makes the Teradata Database an ideal foundation for many applications, including: Enterprise data warehousing Active data warehousing Customer relationship management Internet and E-Business Data marts Enterprise Data Warehouse Data warehousing is a process for properly assembling and managing data from various servers to answer business-critical questions. The Teradata Database is ideal for enterprise data warehousing, which is commonly characterized by: Multiple subject areas Many concurrent users Many concurrent queries, including ad-hoc queries Large quantity of tables Hundreds of gigabytes (and terabytes) of detail data Historical data stored (months or years) Without  an enterprise data warehouse, a financial institution may be able to identify profit...

Internet and E-Business The Teradata Database provides a single repository for customer information that helps E-Businesses build and maintain one-to-one customer relationships that are critical to their success on the Internet. The Teradata Database supports the fast-paced style of E-Business by allowing many concurrent users to ask complicated questions as they think of them — and get quick answers. The Teradata Database allows E-Businesses to: Capture massive amounts of click-stream data. Enable multiple users to ask complex questions of the customer’ click-stream data with near real-time response. Protect customers’ privacy with consumer opt-in/opt-out preferences and ability for consumers to check and revise their information stored on the Teradata Database through the Internet or a company call center.

Enterprise Data Warehouse Data warehousing is a process for properly assembling and managing data from various servers to answer business-critical questions. The Teradata Database is ideal for enterprise data warehousing, which is commonly characterized by: Multiple subject areas Many concurrent users Many concurrent queries, including ad-hoc queries Large quantity of tables Hundreds of gigabytes (and terabytes) of detail data Historical data stored (months or years) Without  an enterprise data warehouse, a financial institution may be able to identify profitable customers for separate products such as mortgages or credit cards, but not know the overall profitability of each customer. An enterprise data warehouse brings together the different subject areas into a central repository, creating “one single view of the business ” for a complete picture of the customer. An enterprise data warehouse environment built on the Teradata Database simplifies the system maintena...

Active Data Warehouse The active data warehouse extends a company’s ability beyond historical data and strategic decisions to bring the decision-making capability to front-line personnel. The tactical decisions such as, “Who should get the empty seat on this airplane?” or “What should I offer this customer to keep her from leaving, based on her history with our company?” can be made more effectively with the right information. With an active data warehouse, employees who interact directly with customers and suppliers are empowered with information-based decision making at their fingertips. The Teradata Warehouse supports active data warehousing with: Capability to handle thousands of additional users and mixed workloads High availability and reliability to support mission-critical applications Scalability to accommodate an increase in the amount of data, the number of data sources, and the number of applications supported in the data warehouse environment

Customer Relationship Management Customer Relationship Management solutions help companies capture and analyze data to maximize customer acquisition, retention, and profitability. You can use the Teradata Database’s detailed data and analysis capabilities to identify and optimize business relationships with the highest potential of profitability and growth. Examples include: A telephone company can conduct and refine marketing programs targeted at a certain type of profitable customer. A supermarket can create incentives based on specific combinations of products that customers tend to buy together. A bank can recognize changes in a customer’s life circumstances, such as a new baby or a college-bound son or daughter, and offer timely services such as a new home loan, mortgage insurance, additional checking account, extra credit card, or student loan. A retailer can run a department store credit card sales program and filter out those customers who already have that card. Te...

Data Mart in Data warehouse A data mart is a special purpose subset of a company’s enterprise data used by a particular department, function, or application. Often, these single-subject area data marts contain data that was aggregated or transformed in some way to better handle the requests of a specific user community. Vendors implement data marts using different architectures: Independent data marts  – Created directly from operational systems to an individual data store. Dependent data marts  – Created from detail data in the data warehouse. It still requires movement and transformation of data, but may provide better performance for some specific user queries. Logical data marts  – Existing parts of the data warehouse, not separate physical structures. Because in theory the data warehouse contains the detail data of the entire enterprise, a logical data mart would then provide the specific information for a specific user community. With the proper technology...