Teradata SQL Assistant for Microsoft Windows. User Guide

(1)

Teradata SQL Assistant for Microsoft

Windows

User Guide

Release 14.01 B035-2430-032A March 2012

(2)

Teradata, Active Enterprise Intelligence, Applications Within, Aprimo, Aprimo Marketing Studio, Aster, BYNET, Claraview, DecisionCast, Gridscale, Managing the Business of Marketing, MyCommerce, Raising Intelligence, Smarter. Faster. Wins., SQL-MapReduce, Teradata Decision Experts, Teradata Labs Logo, Teradata Raising Intelligence Logo, Teradata Source Experts, WebAnalyst, and Xkoto are trademarks or registered trademarks of Teradata Corporation or its affiliates in the United States and other countries.

Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc. AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc.

EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation. GoldenGate is a trademark of Oracle.

Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company. Intel, Pentium, and XEON are registered trademarks of Intel Corporation.

IBM, CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines Corporation. Linux is a registered trademark of Linus Torvalds.

LSI is a registered trademark of LSI Corporation.

Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other countries.

NetVault is a trademark or registered trademark of Quest Software, Inc. in the United States and/or other countries. Novell and SUSE are registered trademarks of Novell, Inc., in the United States and other countries.

Oracle, Java, and Solaris are registered trademarks of Oracle and/or its affiliates. QLogic and SANbox are trademarks or registered trademarks of QLogic Corporation. SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc. SPARC is a registered trademark of SPARC International, Inc.

Symantec, NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other countries.

Unicode is a registered trademark of Unicode, Inc. in the United States and other countries. UNIX is a registered trademark of The Open Group in the United States and other countries.

Other product and company names mentioned herein may be the trademarks of their respective owners.

THEINFORMATIONCONTAINEDINTHISDOCUMENTISPROVIDEDONAN “AS-IS” BASIS, WITHOUTWARRANTYOFANYKIND, EITHER EXPRESSORIMPLIED, INCLUDINGTHEIMPLIEDWARRANTIESOFMERCHANTABILITY, FITNESSFORAPARTICULARPURPOSE, OR NON-INFRINGEMENT. SOMEJURISDICTIONSDONOTALLOWTHEEXCLUSIONOFIMPLIEDWARRANTIES, SOTHEABOVEEXCLUSION MAYNOTAPPLYTOYOU. INNOEVENTWILL TERADATA CORPORATIONBELIABLEFORANYINDIRECT, DIRECT, SPECIAL, INCIDENTAL, ORCONSEQUENTIALDAMAGES, INCLUDINGLOSTPROFITSORLOSTSAVINGS, EVENIFEXPRESSLYADVISEDOFTHEPOSSIBILITYOF SUCHDAMAGES.

The information contained in this document may contain references or cross-references to features, functions, products, or services that are not announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or services available in your country.

Information contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time without notice.

To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please email: [email protected].

Any comments or materials (collectively referred to as “Feedback”) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing products or services incorporating Feedback.

(3)

Preface

Purpose

This book provides information about Teradata® SQL Assistant for Microsoft® Windows® which is a Teradata® Tools and Utilities product. Teradata Tools and Utilities is a group of products designed to work with the Teradata Database or other database.

Teradata SQL Assistant is a Windows-based information discovery tool designed to retrieve, manipulate, and store data from ODBC-compliant database servers.

Audience

This book is intended for use by:

• SQL proficient users who know how to formulate queries for processing on the Teradata Database or other ODBC-compliant systems

• Relational Database developers

Supported Releases

This book supports the following releases: • Teradata Database 14.0

• Teradata Tools and Utilities 14.00 • Teradata SQL Assistant 14.01

Note: See “To display information about SQL Assistant” on page 38 to verify the Teradata

SQL Assistant version number.

To locate detailed supported release information: 1 Go to http://www.info.teradata.com/.

2 Under Online Publications, click General Search. 3 Type 3119 in the Publication Product ID box. 4 Under Sort By, select Date.

5 Click Search.

6 Open the version of the Teradata Tools and Utilities ##.##.## Supported Platforms and

(4)

Prerequisites

The spreadsheet includes supported Teradata Database versions, platforms, and product release numbers.

Prerequisites

The following prerequisite knowledge is required for this product: • Teradata SQL, or the SQL of another ODBC compliant database • Relational Database Management Systems

• Microsoft Windows operating system • ODBC connectivity software

In addition, the following may be helpful to review prior to using Teradata SQL Assistant:

Changes to this Book

The following changes were made to this book in support of the current release. Changes are marked with change bars. For a complete list of changes to the product, see the Release

Definition associated with this release.

This document... contains this information...

Windows Help file Online help, accessible from the Teradata SQL Assistant main window by clicking on the Toolbar.

(5)

Additional Information

Additional information that supports this product and Teradata Tools and Utilities is available at the web sites listed in the table that follows.

Table i: Changes to this Book

Date Description

March 2012 14.01

This release included the following changes:

• Added the ability to display Charts based on data in the Answerset. See “Charting Answerset data” on page 122.

• Added the ability to directly edit the data in a table See “Editing Table Data” on page 44.

• Added an optional pane to display and use Favorites and Statement Examples. See “Favorites and Examples Pane” on page 50.

• Added support for Transactions when connected in ANSI mode. See “Using Transactions” on page 73.

• Added additional information on the use of configuration files to set initial defaults after installation. See Appendix C: “Configuration Files.”

• Added new options for the handling of LOB columns. See “Setting Answerset Options” on page 117.

• Support optional column headers when opening an Answerset. See “Opening a Saved Answerset” on page 111.

• Provided additional information on the Status Bar. See “Using the Status Bar” on page 35.

Table ii: Additional Product Information

Type of Information Description Source

Release Overview Late Information

Use the Release Definition for the following information:

• Overview of all of the products in the release

• Information received too late to be included in the manuals

• Operating systems and Teradata Database versions that are certified to work with each product

• Version numbers of each product and the documentation for each product • Information about available training

and the support center

1 Go to http://www.info.teradata.com/.

2 Under Online Publications, click General Search.

3 Type 2029 in the Publication Product ID box.

4 Click Search.

5 Select the appropriate Release Definition from the search results.

(6)

Additional Information

Additional product information

Use the Teradata Information Products web site to view or download specific manuals that supply related or additional

information to this manual.

2 Under the Online Publications subcategory, Browse by Category, click Data Warehousing. 3 Do one of the following:

• For a list of Teradata Tools and Utilities documents, click Teradata Tools and Utilities, then select an item under Releases or Products.

• Select a link to any of the data warehousing publications categories listed.

Specific books related to Teradata SQL Assistant for

Microsoft Windows User Guide are as follows:

• ODBC Driver for Teradata User Guide

B035-2509

• Teradata Query Scheduler User Guide

B035-2497

• Teradata Visual Explain User Guide

B035-2504 CD-ROM images Access a link to a downloadable CD-ROM

image of all customer documentation for this release. Customers are authorized to create CD-ROMs for their use from this image.

2 Under the Online Publications subcategory, Browse by Category, click Data Warehousing. 3 Click CD-ROM Images.

4 Follow the ordering instructions. Ordering

information for manuals

Use the Teradata Information Products web site to order printed versions of manuals.

2 Under Print & CD Publications, click How to Order.

3 Follow the ordering instructions. General information

about Teradata

The Teradata home page provides links to numerous sources of information about Teradata. Links include:

• Executive reports, case studies of customer experiences with Teradata, and thought leadership

• Technical information, solutions, and expert advice

• Press releases, mentions and media resources

1 Go to Teradata.com.

2 Select a link. Table ii: Additional Product Information (continued)

(7)

Preface

. . . .3 Purpose . . . .3 Audience . . . .3 Supported Releases . . . .3 Prerequisites . . . .4

Changes to this Book . . . .4

Additional Information . . . .5

Chapter 1:

Getting Started

. . . 21

Introduction . . . 21

What is Teradata SQL Assistant? . . . 21

How Teradata SQL Assistant Works . . . 22

Teradata SQL Assistant Features . . . 22

Defining a Data Source. . . 22

Defining an ODBC Data Source . . . 23

Defining a .NET Data Provider for Teradata Data Source. . . 25

Defining a .NET Data Provider for Oracle Data Source. . . 28

Starting Teradata SQL Assistant . . . 30

Connecting to and Disconnecting from a Data Source. . . 30

Connecting to an ODBC Data Source . . . 31

Connecting to .NET for Teradata. . . 32

Connecting to .NET for Oracle. . . 33

Connecting to Multiple Data Sources . . . 33

Re-Connecting to a Data Source . . . 34

Displaying a Color Bar in the Query Window . . . 34

Changing the Database Password. . . 34

Main Window . . . 35

Menu Bar . . . 35

Hiding Toolbars . . . 35

Using the Status Bar. . . 35

Tabbing Windows. . . 36

(8)

Tiling Windows . . . .37

Arranging Windows. . . .37

Changing the text size in a child window . . . .37

Using Online Help . . . .38

Using Shortcut Menus and Commands. . . .38

Database Explorer Tree . . . .38

Viewing Object Types . . . .39

Opening and Closing the Database Explorer Tree . . . .40

Navigating to the Database Explorer Tree . . . .40

Changing the Width of the Tree Area . . . .40

Moving the Explorer Tree Window . . . .40

Dragging Object Names to the Query Pane. . . .41

Dragging Multiple Objects . . . .41

Using Quick Paste . . . .41

Adding Double Quotes around Object Name. . . .42

Adding Object Types . . . .42

Displaying Object Definitions. . . .42

Displaying Data Source Information . . . .43

Generating SQL for use with an Object . . . .43

Editing Table Data . . . .44

Working with the Edit Table Dialog Box. . . .44

Adding Databases. . . .46

Removing Databases . . . .46

Refreshing the Database Explorer Tree . . . .47

Displaying the Shortcut Menu . . . .47

Setting Database Tree Preferences . . . .48

Favorites and Examples Pane. . . .50

Opening and Closing the Favorites Pane. . . .50

Navigating to the Favorites Pane . . . .51

Changing the Width of the Favorites Pane . . . .51

Moving the Favorites Pane Window . . . .51

Listing All Tables or Views In a Database . . . .52

Listing All Columns In a Table or View . . . .52

Setting General Program Preferences . . . .53

Setting Default File Paths . . . .54

Using Page Setup . . . .56

Using Print Preview . . . .57

Support for Unicode® and UTF-8. . . .57

Displaying Unicode® Data . . . .57

Exporting Unicode® Data . . . .57

Importing Unicode® Data. . . .58

(9)

Text Size . . . 58

Color scheme . . . 58

Audible support . . . 58

Limitations. . . 59

General Limitations . . . 59

Teradata.NET Specific Limitations . . . 59

Oracle.NET Specific Limitations . . . 59

ODBC Specific Limitations . . . 59

Chapter 2:

The Query Window

. . . 61

Introduction to the Query Window . . . 61

Using the Query Window. . . 62

Using SQL, DDL, and DML Statements . . . 62

Displaying the Query Window Toolbar. . . 62

Allowing Multiple Queries . . . 62

Splitting the Query Window into Two Windows . . . 63

Selecting Text and Inserting Bookmarks Using the Query Window Margin . . . 63

Creating Statements (Single and Multi). . . 63

Parameterized Queries. . . 64

Magnifying the Query Window . . . 64

Setting Query Options . . . 65

Setting Code Editor Options. . . 67

Entering and Executing Queries . . . 69

Entering a Query . . . 69

Using Conditional Logic in a Query. . . 70

Using Code Completion . . . 71

Customizing Code Completion Lists . . . 72

Executing a Query . . . 73

Using Transactions . . . 73

Running Multiple Queries . . . 74

Executing Multiple Statements in Parallel . . . 74

Submitting Part of a Query . . . 75

Aborting a Query in Progress . . . 75

Executing a Query Saved To a File . . . 76

Automatically Minimizing the Teradata SQL Assistant Window . . . 76

Renaming a Query Tab . . . 76

Deleting a Query Tab. . . 76

Working With SQL Text in the Query Window . . . 77

(10)

Copying SQL from Next History Record to the Query Window . . . .78

Preventing Queries from Being Saved in the History Window . . . .78

Undoing or Redoing Query Window Changes . . . .78

Setting Repeat Count . . . .78

Recording and Executing Query Window Macros . . . .79

Saving a Query . . . .80

Performing a Quick Save. . . .80

Adding a Query to Favorites . . . .80

Opening a Query from a File . . . .81

Copying a Query to Notepad . . . .81

Printing a Query. . . .82

Adding Comments to Queries . . . .82

Highlighting a block of Text . . . .83

Showing Whitespace . . . .83

Converting Tabs to Spaces . . . .83

Indenting Lines in a Query . . . .83

Using the Right-to-Left Editor for Queries . . . .84

Deleting Blank Lines from a Query . . . .84

Finding a Text String in the Query Window . . . .84

Replacing a Text String in the Query Window . . . .85

Displaying an Explain Plan of a Query . . . .85

Displaying a Visual EXPLAIN Plan . . . .86

Displaying a Textual EXPLAIN Plan . . . .86

Query Builder Overview. . . .87

SQL Statements . . . .87

Procedure Builder . . . .88

<User Defined> . . . .88

Using Query Builder . . . .88

Supported Databases for Query Builder . . . .89

Creating Custom SQL Sets . . . .90

Scheduling Queries for Later Execution . . . .91

Before Scheduling Queries . . . .91

Scheduling Queries for Later Execution . . . .92

Formatting a Query . . . .92

Indentation . . . .92

Language Definition Files . . . .93

Importing Data . . . .94

Before You Begin . . . .94

Importing Data from a File . . . .95

Exiting from Import Mode . . . .95

Types of Import Operations . . . .95

(11)

Defining the Null Value for an Import Operation . . . 98

Generating Multiple Reports From a Single Query . . . 98

Using the Query Window Shortcut Menu . . . 99

Displaying the Shortcut Menu . . . 99

Functions in the Query Window . . . 100

Chapter 3:

The Answerset Window

. . . 103

Introduction to the Answerset Window . . . 103

Using the Answerset Window . . . 104

Viewing Your Results. . . 105

Adjusting Row Height . . . 105

Adjusting Column Width . . . 105

Re-arranging Column Order . . . 105

Keeping Selected Columns From Scrolling Out of View . . . 106

Viewing Long Strings of Text Within Cells . . . 106

Closing Answerset Windows Before Submitting a New Query . . . 106

Selecting All Answerset Rows . . . 107

Merging Cells in the Answerset . . . 107

Displaying the SQL That Generated an Answerset . . . 107

Hiding Columns/Showing All Columns . . . 108

Closing All Answersets. . . 108

Hiding Column Headers . . . 108

Hiding Row Headers . . . 108

Displaying Totals for Numeric Columns. . . 109

Displaying Aggregate Values . . . 109

Finding a Text String in the Results Grid. . . 109

Naming an Answerset Window . . . 111

Naming an Answerset Tab . . . 111

Deleting an Answerset Tab . . . 111

Opening a Saved Answerset . . . 111

Saving an Answerset to a File . . . 112

Saving as XML . . . 113

Copying an Answerset to Notepad . . . 114

Printing an Answerset . . . 114

Using Print Preview . . . 114

Cancelling Print Jobs . . . 115

Sorting an Answerset . . . 115

Filtering the Answerset . . . 116

(12)

Setting Answerset Options . . . .117

Setting Data Format Options . . . .119

Changing the Font for the Entire Window . . . .122

Zooming the Answerset Window . . . .122

Charting Answerset data . . . .122

Working with Charts . . . .124

Formatting an Answerset . . . .124

Formatting a Block of Cells. . . .125

Formatting a Single Cell . . . .125

Formatting a Single Row or Column . . . .125

Formatting Multiple Rows or Columns. . . .125

Formatting the Entire Spreadsheet. . . .126

Displaying Commas to Mark Thousand Separators. . . .126

Displaying Numbers in Scientific Notation. . . .126

Displaying Decimal Places. . . .126

Exporting a Resultset . . . .127

Exporting Results . . . .127

Single-Clicking to Display the Export File. . . .128

Saving Multiple Answersets . . . .128

Exporting to Access - Formats and Data Types. . . .128

Setting Export/Import Options . . . .129

Setting Export Options . . . .130

Setting Import Options . . . .131

Understanding Large Object Support . . . .131

Using Answerset Shortcut Menus . . . .132

Chapter 4:

The History Window

. . . .135

Introduction to the History Window . . . .135

The Columns of the History Window . . . .136

Using the History Window . . . .137

Opening the History Window . . . .137

Closing the History Window . . . .137

Viewing the Result Message . . . .137

Viewing DBS Error Messages . . . .138

Viewing the History Rows. . . .138

Rearranging History Columns . . . .138

Filtering the History Rows . . . .138

Copying SQL from Previous History Record to the Query Window . . . .140

(13)

Selecting All History Rows . . . 141

Sorting the History Records . . . 141

Finding a Text String in the History Table . . . 142

Cleaning up the History table . . . 143

Magnifying the History Window . . . 144

Setting History Window Options . . . 145

Editing History Records . . . 146

Displaying and Navigating the Edit History Dialog Box . . . 147

Editing a History Record . . . 147

Compacting History . . . 147

Adding or Change a Note in a History Record . . . 147

Saving, Copying, and Printing History . . . 148

Saving History Rows . . . 148

Copying Rows to the Clipboard . . . 150

Copying Rows to Notepad . . . 150

Printing the Contents of the History Window . . . 150

Cancelling Print Jobs . . . 151

Storing History Files . . . 151

Changing the Location of History Files . . . 151

The History Window Shortcut Menu . . . 152

Appendix A:

Startup Parameters and Default Preferences

. . . 155

Startup Parameters . . . 155

Default Preferences (Options) . . . 156

Miscellaneous and General Default Preference Settings. . . 157

Query Default Preference Settings . . . 159

Code Editor Tab Default Preference Settings . . . 159

Data Format Tab Default Preference Settings . . . 161

Answerset Tab Default Preference Settings . . . 161

Export/Import Tab Default Preference Settings . . . 162

History Tab Default Preferences and Descriptions. . . 162

Database Tree Default Preferences and Descriptions . . . 163

File Paths Tab Default Preference Settings . . . 163

(14)

Appendix B:

Menus, Toolbars and Shortcuts

. . . .165

Using Toolbars and Buttons . . . .165

The Main Toolbar . . . .165

The Answerset Toolbar . . . .165

The Query Toolbar . . . .166

Toolbar Button Descriptions . . . .166

Customizing Menus and Toolbars . . . .172

Adding a Command to a Menu or Toolbar. . . .172

Menu Commands Only. . . .173

Removing a Command from a Menu or Toolbar. . . .173

Moving a Command . . . .174

Adding or Removing a Break Between Commands . . . .174

Changing the Name for a Command. . . .174

Assigning or Changing a Keyboard Shortcut . . . .174

Changing Menu Behavior . . . .175

Showing Recently Used Commands First . . . .175

Resetting the Default Menu Bar . . . .176

Hiding the Toolbars. . . .176

Showing or Hiding Toolbar Screen Tips . . . .176

Adding, Removing, Renaming, or Resetting a Toolbar . . . .176

General Command Shortcut Keys. . . .177

Query Specific Shortcut Keys. . . .178

Window Control Shortcut Keys . . . .181

Answer / History Specific Shortcut Keys . . . .182

Appendix C:

Configuration Files

. . . .183 UserOptions.config. . . .183 Toolbars.config . . . .184 DataSources.config . . . .184 DockMgr.config . . . .184 Vendors.config . . . .184

(15)

Glossary

. . . 187

(16)

(17)

List of Figures

Figure 1: The Default Main Toolbar . . . 165 Figure 2: The Default Answerset Toolbar . . . 166 Figure 3: The Default Query Toolbar . . . 166

(18)

(19)

List of Tables

Table 1: ODBC Data Source Types . . . 23

Table 2: ODBC Driver Setup for Teradata Database Dialog Box: Field Descriptions . . . 24

Table 3: Description of the Teradata.NET Connection Dialog Box. . . 27

Table 4: Description of the Oracle.NET Connection Dialog Box. . . 29

Table 5: Sub Folders Created When Objects Are Added . . . 42

Table 6: Generate SQL Sub Menus . . . 43

Table 7: Explorer Tree Shortcut Menu Commands. . . 47

Table 8: Options Dialog Box, DB Tree Tab . . . 49

Table 9: Options Dialog Box, GeneralTab . . . 54

Table 10: Description of the File Paths Options. . . 55

Table 11: Description of the Page Setup Dialog Box and Print Preview Window . . . 56

Table 12: The Query Tab in the Options Dialog Box. . . 65

Table 13: Code Editor Tab in the Options Dialog Box . . . 68

Table 14: Query Builder Statements . . . 89

Table 15: Query Builder Icon Descriptions and Right-Click Options . . . 90

Table 16: The Query Window Shortcut Menu Commands and Descriptions . . . 99

Table 17: Clipboard Support - Edit Commands . . . 100

Table 18: Find Dialog Box . . . 110

Table 19: Answerset File Format Types. . . 112

Table 20: Answerset Tab in the Options Dialog Box . . . 118

Table 21: Data Format Tab in the Options Dialog Box. . . 120

Table 22: Chart Definition Dialog Box . . . 123

Table 23: Chart Menus and Shortcut Keys . . . 124

Table 24: How SQL Assistant Maps Teradata Data Types to Access Data Types . . . 128

Table 25: Import/Export Tab in the Options Dialog Box . . . 129

Table 26: Export Tab in the Options Dialog Box . . . 130

Table 27: Import Tab in the Options Dialog Box . . . 131

Table 28: The Answerset Window Shortcut Menu Commands and Descriptions . . . 133

Table 29: The History Window Column Descriptions . . . 136

Table 30: Description of History Window Filter Options. . . 139

Table 31: Find Dialog Box . . . 142

(20)

Table 33: History Tab in the Options Dialog Box . . . .145

Table 34: History File Format Types . . . .149

Table 35: The History Window Shortcut Menu Commands and Descriptions . . . .153

Table 36: Startup Parameters . . . .155

Table 37: Miscellaneous and General Default Preferences and Descriptions . . . .157

Table 38: Query Default Preferences and Descriptions. . . .159

Table 39: Code Editor Tab Default Preferences and Descriptions . . . .159

Table 40: Data Format Tab Default Preferences and Descriptions . . . .161

Table 41: Answerset Tab Default Preferences and Descriptions. . . .161

Table 42: Export/Import Tab Default Preferences and Descriptions. . . .162

Table 43: History Tab Default Preferences and Descriptions . . . .162

Table 44: Database Tree Default Preferences and Descriptions . . . .163

Table 45: File Paths Tab Default Preferences . . . .163

Table 46: Page Setup Defaults and Descriptions . . . .163

Table 47: Toolbar Button Descriptions. . . .166

Table 48: Adding, Deleting, Renaming and Resetting a Toolbar . . . .176

Table 49: Shortcut Keys . . . .177

Table 50: Query Specific Shortcut Keys . . . .178

Table 51: Window Control Shortcut Keys . . . .181

Table 52: Answer / History specific Shortcut Keys . . . .182

(21)

CHAPTER 1

Getting Started

The topics in this chapter provide the basic information required to get started using Teradata SQL Assistant:

• Introduction

• Defining a Data Source

• Starting Teradata SQL Assistant

• Connecting to and Disconnecting from a Data Source • Main Window

• Database Explorer Tree • Favorites and Examples Pane

• Listing All Tables or Views In a Database • Listing All Columns In a Table or View • Setting General Program Preferences • Support for Unicode® and UTF-8 • Support for the Visually Impaired • Limitations

Note: This document uses the term “database” as the term for “table qualifier” (the object that contains data tables). This term varies depending on the database vendor, and may be referred to as “owner”, “schema” or “catalog” in the vendor’s own documentation.

Introduction

This topic provides basic information about Teradata SQL Assistant and a brief overview of its features.

• What is Teradata SQL Assistant? • How Teradata SQL Assistant Works • Teradata SQL Assistant Features

What is Teradata SQL Assistant?

Teradata SQL Assistant is an information discovery tool designed for Windows XP and Windows 7. Teradata SQL Assistant retrieves data from any ODBC-compliant database server. The data can then be manipulated and stored on a desktop PC.

(22)

Defining a Data Source

How Teradata SQL Assistant Works

Teradata SQL Assistant combines the data retrieved from ODBC databases with desktop applications such as Excel to create consolidated reports or to analyze the merged data. Teradata SQL Assistant records all SQL activity, complete with source identification, timings, row counts and notes. This is especially useful in data mining because the historical record can be used to build scripts from the SQL that produced positive results.

Teradata SQL Assistant Features

Teradata SQL Assistant has several key features:

• Create reports from Teradata or any Relational Database that provides an ODBC interface. • Export data from the database to a file on a PC

• Import data from a PC file directly to the database

• Use an import file to create many similar reports (query results or Answersets). For example, display the DDL (SQL) that was used to create a list of tables. For more information, see “Generating Multiple Reports From a Single Query” on page 98. • Send queries to any supported database or send the same query to many different

databases

• Create a historical record of the submitted SQL with timings and status information such as success or failure

• Use SQL syntax examples to tailor statements

• Use the Database Explorer Tree to easily view database objects

• Use a procedure builder that provides a list of valid statements for building the logic of a stored procedure

• Limit data returned to prevent runaway queries

Defining a Data Source

When connecting to a database, select either ODBC, the .NET Data Provider for Teradata, or the .NET Data Provider for Oracle. Connection to any other database must be made through an ODBC connection. In order to use the ODBC connection, a vendor specific ODBC driver must be installed. In order to use a .NET Data Provider, that provider must be downloaded and installed.

See also the following sections: • Defining an ODBC Data Source

• Defining a .NET Data Provider for Teradata Data Source • Defining a .NET Data Provider for Oracle Data Source

Note: The .NET providers only appear in the dropdown list if they have been installed on the client system.

(23)

Defining an ODBC Data Source

An ODBC-based application like Teradata SQL Assistant accesses the data in a database through an ODBC data source.

After installing Teradata SQL Assistant on a workstation, start Teradata SQL Assistant. Then define a data source for each database.

The Microsoft ODBC Data Source Administrator maintains ODBC data sources and drivers and can be used to add, modify, or remove ODBC drivers and configure data sources. An About Box for each installed ODBC driver provides author, version number, module size, and release date.

Use the Microsoft ODBC Data Source Administrator program installed on the workstation to define a data source in one of two ways.

To define an ODBC data source

1 Do one of the following:

• From the Windows desktop, select Start > Control Panel > Administrative Tools > Data Sources (ODBC).

• From the Windows desktop, select Start > Programs > Teradata SQL Assistant 14.00 After Teradata SQL Assistant launches, select Tools > Define Data Source.

2 In the ODBC Data Source Administrator dialog box, decide what type of data source to create. Table 1 describes the data source types.

3 After selecting the DSN type, click Add.

For example: In the ODBC Data Source Administrator dialog box, click the System DSN tab to bring to the front. Click Add, then click OK.

4 In the Create New Data Source dialog box, select the appropriate driver and click Finish. For example: Locate the Teradata driver under the column Name. Click to select the driver and click Finish.

Table 1: ODBC Data Source Types

Data Source

Description Explanation

User DSN An ODBC user data source stores information about how to connect to the indicated data provider.

System DSN An ODBC system data source stores information about how to connect to the indicated data provider. A system data source is visible to all users on this machine, including NT services.

File DSN An ODBC file data source connects to a data provider. File DSNs can be shared by users who have the same drivers installed.

(24)

5 A dialog box appears for the selected database. This dialog box requests information that defines the location of the database and the connection parameters to be used when establishing a connection. The parameters vary from one vendor database to another. Table 2 describes the basic parameters used to connect to a database.

Table 2: ODBC Driver Setup for Teradata Database Dialog Box: Field Descriptions

Select This Field... To...

Name Enter a name that identifies this data source.

For example, in some cases there is more than one Teradata server to connect to, or a user may have more than one logon depending on the function the user performs.

Description Enter a description. This is solely a comment field to describe the data source name used.

Name(s) or IP address(es) Enter the name(s) or IP address(es) of each LAN-connected node in the system, one per line. Entering only the first node name or IP address causes the client to communicate only with that node or IP address and can decrease system performance significantly.

Define any names entered here in either Domain Name Services (DNS) or the local hosts file. The hosts file is located in the

system32\drivers\etc subdirectory of the directory in which

Windows is installed.

Enter the name(s) or IP address(es) of the Teradata system. Note: Never enter both a name and an IP address.

Do not resolve alias name

to IP address When this option is selected, setup routine does not attempt to resolve alias names entered into the "Name(s) and IP address(es)" box at setup time.

Instead it is resolved at connect time. When cleared, the setup routine automatically appends COPn (where n = 1, 2, 3, ..., 128) for each alias name entered.

This causes other IP addresses associated with this server to be located, until a break in the sequence is detected.

Use Integrated Security Select this option if logging on using integrated security measures. Mechanism Leave this field blank to use the default mechanism.

Parameter The authentication parameter is a password required for the selected mechanism.

Username Enter a user name.

Password Enter a password to be used for the connection if using Teradata SQL Assistant in an unattended (batch) mode.

(25)

Teradata SQL Assistant is ready to use.

For more information about using Microsoft ODBC Data Source Administrator, refer to the ODBC Data Source Administrator Help system.

Note: The SQL Assistant option Allow use of ODBC SQL Extensions in queries no longer exists. Clear the ODBC DSN option Disable Parsing to allow the use of ODBC SQL Extensions in queries.

ODBC Driver

Before using Teradata SQL Assistant to access the data in the database, first install an ODBC driver on the PC. Each database requires a driver that is designed specifically for that database system. Obtain the appropriate driver from an database vendor or from a third-party supplier. Compatibility

Teradata SQL Assistant is certified to run with any Level 2 compliant 32-bit ODBC driver. The product also works with Level 1 compliant drivers, but may not provide full functionality. Consult the ODBC driver documentation to determine the driver’s conformance level. Most commercially available ODBC drivers conform to Level 2.

Defining a .NET Data Provider for Teradata Data Source

Use the Connection Information dialog box to create, edit, and delete data sources for .Net Data Provider for Teradata. The dialog box is also used to connect to existing Teradata.Net data sources.

Data source definitions are saved to a file named DataSources.config.

This file is located in \Users\<username>\AppData\Teradata\SQL Assistant on Windows 7 or

\Documents and Settings\<username>\Application Data\Teradata\SQL Assistant otherwise.

Additionally, ‘system level’ data sources can be defined. These are stored in a file of the same name, located in \Users\All Users\AppData\Teradata\SQL Assistant on Windows 7 or

\Documents and Settings\All Users\Application Data\Teradata\SQL Assistant otherwise.

When a System level data source is selected, a padlock icon appears to the right of the data source name. For a Windows user with Standard rights, many of the properties within such a

Default Database (optional) Enter the default database name.

If the Default Database is not entered, the Username is used as the default. All tables, views, and macros are assumed to be in this default database unless explicitly prefixed by a database name in the query.

Account String (optional) Enter one of the accounts that the DBA assigned to the Username when it was created.

Session Character Set Use the drop down menu to choose the character set. The default is ASCII.

Table 2: ODBC Driver Setup for Teradata Database Dialog Box: Field Descriptions (continued)

(26)

data source can not be changed. A user with Administrative rights may change any property. However, if he or she changes certain properties, a new user level data source is created. This user level data source appears instead of the system level data source going forward. If he or she later deletes that data source, the original System level data source is restored.

To define a Teradata .NET data source

1 Open Teradata SQL Assistant.

2 Select Teradata .NET from the provider drop down list, next to the Connect tool button.

3 Click the Connect icon or go to Tools > Connect.

4 Use the Connection Information dialog box to choose a .NET data source.

• Create a new data source by entering the name and server and other applicable information

• Delete an existing data source by clicking Delete. • Use this dialog box to modify existing sources.

5 Use the Advanced tab to make additional changes to a data source. The figure below shows the .NET dialog box. Table 3 describes the options in the .NET dialog box.

(27)

Table 3: Description of the Teradata.NET Connection Dialog Box

Dialog Box Item Description Basic tab

Data Source Name Enter the name of the data source or use the drop down menu to choose an existing name.

Server Enter the name of the data source or the IP address. Use Integrated Security Use your Windows credentials for this connection.

Mechanism Select a security mechanism and parameter. The default Mechanism is used if this is left blank.

Parameter Enter your security parameters if required by the selected Mechanism. User Name Enter the appropriate user name information.

Password Enter the appropriate password information. Default Database Enter the default database if applicable.

If the Default Database is not entered, the Username is used as the default. All tables, views, and macros are assumed to be in this default database unless explicitly prefixed by a database name in the query. Account String Enter the default account string if applicable.

(28)

Defining a .NET Data Provider for Oracle Data Source

Use the Connection Information dialog box to create, edit, and delete data sources for .NET for Oracle. The dialog box is also used to connect to existing Oracle .NET data sources.

To define an Oracle .NET data source

1 Open Teradata SQL Assistant.

2 Select Oracle.NET from the provider drop down list next to the Connect tool button. Session Character Set Use the drop down menu to select a character set to be associated with

the data source.

Session Mode Use Session Mode to select the type of session. Options include ANSI, DEFAULT, and TERADATA.

Response Buffer Size Enter a buffer size between 4096 and 1040000. The default is 65535. Port Number Enter a port number. The default is 1025.

Connection Timeout Enter the length of time in seconds that SQL Assistant should wait for a connection to be established before determining that the system is unavailable. The default setting is 20 seconds.

Data Source DNS

Entries Optionally enter the number of IP Addresses defined for the Teradata system (1 to 999). The default setting is blank. Use X views Select this box to force the driver to read the X (restricted) views

instead of the regular views when fetching catalog data for the Database Tree.

Note: This option makes retrieving Database Tree information less efficient but may be required due to security restrictions for some users.

Use Data Encryption Select this box to use data encryption.

Verify Message Integrity Select this box to set the .NET Data Provider to perform integrity checks on all messages sent to and received from Teradata. The box is not selected by default.

Use Enhanced Schema Select this box to set the .NET Data Provider to return additional metadata information. This option also controls whether the

StatementInfo parcel is returned by the database. The box is selected by default.

Show Color Bar Select this box to display a narrow bar of color across the top of any Query window that is connected to this data source.

Use the Color selection control to the right to select a color. This same color is used to underline the Data Source name in the Database Explorer Tree when you are connected to this data source. Table 3: Description of the Teradata.NET Connection Dialog Box (continued)

(29)

3 Click the Connect icon or select Tools > Connect.

4 Use the Connection Information dialog box to choose a .NET data source. • Create a new data source by entering the name, server, and other applicable

information.

• Delete an existing data source by clicking Delete. • Use this dialog box to modify existing sources.

5 Use the Advanced tab to make additional changes to a data source. The figure below shows the .NET dialog box. Table 4 describes the options in the .NET dialog box.

Table 4: Description of the Oracle.NET Connection Dialog Box

Dialog Box Item Description Basic tab

Data Source Name Enter the name of the data source or use the drop down menu to choose an existing name.

Server Enter the name of the data source or the IP address. Use Integrated Security Use your Windows credentials for this connection. User Name Enter the appropriate user name information. Password Enter the appropriate password information.

(30)

Starting Teradata SQL Assistant

After installing the .NET Data Provider for Teradata, Teradata SQL Assistant, and any required ODBC drivers or .Net data providers, start Teradata SQL Assistant.

To start Teradata SQL Assistant

✔ From the Windows desktop, select Start > Programs > Teradata Client 14.00 > Teradata SQL Assistant > Teradata SQL Assistant.

The Teradata SQL Assistant main window appears with a blank Query Window above and a History window below.

Connecting to and Disconnecting from a Data

Source

Before connecting to an ODBC data source, a .NET data source, or both, the data sources should be defined. See “Defining an ODBC Data Source” on page 23 or “Defining a .NET Data Provider for Teradata Data Source” on page 25. More than one data source at a time can be connected if the option is selected on the Options dialog box under the General tab. The following topics provide information about data source connections:

• Setting General Program Preferences • Connecting to an ODBC Data Source • Connecting to .NET for Teradata

Advanced tab

Proxy User Enter the Proxy user to connect through. Proxy Password Enter the password for the Proxy user.

Connection Timeout Enter a time limit in seconds for SQL Assistant to establish a connection to an available system before timing out. The default setting is 20 seconds.

Show Color Bar Select this box to display a narrow bar of color across the top of any Query window that is connected to this data source.

Use the Color selection control, to the right, to select a color. This same color is used to underline the Data Source name in the Database Explorer Tree when you are connected to this data source. Table 4: Description of the Oracle.NET Connection Dialog Box (continued)

(31)

Connecting to and Disconnecting from a Data Source • Connecting to .NET for Oracle

• Connecting to Multiple Data Sources • Re-Connecting to a Data Source

• Displaying a Color Bar in the Query Window • Changing the Database Password

Connecting to an ODBC Data Source

After defining the ODBC data source, connect to it.

To connect to an ODBC data source

1 Use the provider drop down menu to select ODBC data source.

2 Do one of the following: • Select Tools > Connect. • On the Toolbar click .

The prompt asks for the name of the data source. 3 Select a data source and click OK.

4 In the Teradata Database Connect dialog box: a Do one of the following:

• Select Use Integrated Security • Enter the Mechanism and Parameter • Enter the Userid and Password.

b Optionally, enter a Default Database or an Account String.

When the connection is complete, the Connect icon may be disabled and the Disconnect icon to its right is enabled. The connect icon is disabled only if connection to multiple data sources is not allowed.

For more information on the fields in this dialog box, refer to the ODBC Driver for Teradata

User Guide.

When Connected to ODBC

The following are some characteristics that occur when connection is established with an ODBC source:

(32)

Connecting to and Disconnecting from a Data Source

• All columns are returned as character strings when Interval columns are selected. Because of this, you cannot select LOBs or Byte columns if your Select includes Interval columns. • Only select CLOBs if you connect using the UTF-16 Session Character Set.

• Only type F User Defined Functions are listed under the Functions node of the Database tree.

• Time columns do not display fractional seconds or Time Zone information. • Decimal columns with more than 28 digits may display incorrectly.

• If a macro (or execute parallel) returns zero rows from a Select statement, no rows are returned from any later Select statements in that macro or parallel query.

Connecting to .NET for Teradata

Use this procedure to connect to a .NET for Teradata data source. For more information on defining .NET data sources, see “Defining a .NET Data Provider for Teradata Data Source” on page 25.

To connect to a .NET for Teradata data source

1 Use the Provider drop down menu to select Teradata.NET.

The Teradata .NET Connection Information dialog box opens. 3 Select the data source and enter the applicable information. When Connected to a Teradata .NET Data Source

The following are some characteristics that occur when a connection is established with a Teradata .NET data source:

• Teradata.NET always uses ANSI date mode.

All date literals must be entered in 'YYYY-MM-DD' format.

• Timestamp With Time Zone columns only sorts correctly if you specify a date format of 'YYYY-MM-DD'.

(33)

Connecting to .NET for Oracle

For more information on defining .NET data sources, see “Defining a .NET Data Provider for Oracle Data Source” on page 28.

To connect to a .NET for Oracle data source

1 Use the Provider drop down menu to select Oracle.NET.

The Oracle .NET Connection Information dialog box opens. 3 Select the data source and enter the applicable information. When Connected to an Oracle .NET Data Source

The following are some characteristics that occur when a connection is established with a Oracle .NET data source.

• Decimal columns with more than 28 digits may be displayed as <Error>. • The correct case for Database/User names (generally Uppercase) must be used.

This applies to the Connection dialog box, when adding Databases to the Explorer tree, or when enclosing object names within double quotes.

• Create Procedure statements must end with a semicolon.

These statements create, but not compile, the procedure. The procedure must be compiled before it can be executed.

Connecting to Multiple Data Sources

Use the following procedure to connect to multiple data sources.

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.

(34)

Each new data source appears in the Database Tree and opens a new query window with the data source name in its caption. To disconnect from a data source, click the Query window that is connected to that data source and click the disconnect icon.

Re-Connecting to a Data Source

Use the following procedure to reconnect to a data source to which the Query window was previously connected if the connection was dropped due to a network problem or the PC entering Hibernation.

To reconnect to a data source

✔ Do one of the following: • Select Tools > Reconnect. • On the Toolbar click .

Displaying a Color Bar in the Query Window

Use the following procedure to display a colored stripe across the top of a Query window. This provides visual feedback related to the data source to which the Query is connected.

To display a color bar in the Query window

1 When connecting to a Teradata.NET or Oracle.NET data source, click the Advanced tab. 2 Select the Show Color Bar check box.

3 Use the color selection dropdown control to select a color.

Changing the Database Password

Use the following procedure to change the password in the database for Teradata, Oracle, MySQL and SQL Server databases. It may not be possible to change the password on all database systems, like Microsoft Access. Changing the password in the Database does not change a password that is stored in the ODBC data source definition. Storing a password in the ODBC data source is not secure and is not recommended.

To change the database password

1 Select Tools > Change Password.

2 In the Change Database Password dialog box, enter the current password. 3 Enter the new password.

Note: Do not use the semicolon (;) or the equal sign (=) when changing database passwords if any ODBC or .NET-based applications are being used.

(35)

Main Window

The Query Window is used to enter and execute a query. The results from queries are placed into one or more Answerset windows. See “Introduction to the Query Window” on page 61. The Answerset window is a table that Teradata SQL Assistant uses to display the output of a query. See “Introduction to the Answerset Window” on page 103.

The History window is a table that displays past queries and related processing attributes. The past queries and processing attributes are stored locally in a Microsoft Access database. This provides flexibility to work with previous SQL statements in the future. See “Introduction to the History Window” on page 135.

In addition to the three main windows, the optional Database Explorer Tree and Favorites panes can be displayed at the left or right side of the main window. For more information on these features, see “Database Explorer Tree” on page 38 or “Favorites and Examples Pane” on page 50.

Menu Bar

The Teradata SQL Assistant main window features a menu bar that contains menus that display a list of commands. The available commands apply to the active window.

To find information on main menu items, refer to the Index under the main menu name. For information on customizing menus, see “Customizing Menus and Toolbars” on page 172.

Hiding Toolbars

The Toolbars contain tool buttons used to perform the functions of the more commonly used menu commands.

For information on customizing toolbars, see “Customizing Menus and Toolbars” on page 172.

To hide the Toolbars

✔ Select View > Toolbars and then choose the Toolbar to hide.

Using the Status Bar

The status bar appears across the bottom of the main window and displays status information such as:

• The status of an action or descriptive information about the Menu or Tool button that the mouse is currently hovering over

For example, after executing a query, the status bar displays a success or failure message for that query.

(36)

Main Window

Note: If a message is too long to fit on the status bar, click on it to display the full message in a message box.

• The sum, count, average and standard deviation for the highlighted block of cells in the Answerset window

To display this information, see “Displaying Aggregate Values” on page 109.

• The name of the Data Source from which the data was retrieved when an Answer set window is active, or the name of the Data Source to which the Query window is connected if a Query window is active

• The line number of the cursor position in the Query window

The line number is useful for locating syntax errors in stored procedures. • The length of the selected text if any text is selected in the Query window • The current zoom factor

• The current time

By default this displays only the hour and minutes. Click on it to display seconds also. Click on it again to return to the default display.

To hide the Status Bar

✔ Select View > Status Bar.

Tabbing Windows

Query, History, and Answerset windows can be displayed as tabbed windows instead of the usual Tiled, Cascaded, or Maximized options. When tabbed, the windows can be split into two tab groups to allow you to easily compare the contents of each window.

To tab windows

✔ Under Window on the Toolbar, select Tabbed. The open windows arrange in tabs.

After the window is tabbed, a second tab group can be opened either by right-clicking on a tab and using the menu, or by dragging the tab to any edge of the window. (Dragging to a side opens side by side tab groups, while dragging to the top/bottom opens one tab group above the other.)

A tabbed window can also be dragged to a new location within its own tab group, or to another tab group.

(37)

Main Window

Cascading Windows

To display all the open windows, use the cascade function. The following procedure explains how.

To cascade windows

✔ Do one of the following: • On the Toolbar click . • Select Window > Cascade. • Press Shift+F5

Tiling Windows

Use the tiling function to display all of the open windows.

To tile windows

✔ Do one of the following: • On the Toolbar click .

• Select Window > Tile Horizontal or Tile Vertical. • Press Shift+F4 or Shift+F6

Arranging Windows

Use the following procedure to arrange all minimized windows in a row across the bottom of the Teradata SQL Assistant window.

To arrange icons

✔ Select Window > Arrange Icons.

Changing the text size in a child window

Use the following procedure to change the text size used in a child window.

To increase/decrease the text size in a child window

1 Make sure the window is active.

2 Hold the Ctrl key and spin the mouse wheel, or press the + or - keys. The display zooms in or out.

(38)

Database Explorer Tree

Using Online Help

The following explains how to use the help menu items.

To display help menu items

✔ Do one of the following: • Select Help > Help Topics. • On the Toolbar click .

To display context-sensitive help

✔ Press F1 anywhere in the program.

To display information about SQL Assistant

The About screen provides useful information about SQL Assistant and the current session, including the software version of SQL Assistant, the type and version of the connected server, and information about the ODBC driver.

✔ Select Help > About SQL Assistant.

Using Shortcut Menus and Commands

To find information on menu items, refer to the Index under the menu name. Information on the shortcut menus for each window may be found as follows:

• Using the Query Window Shortcut Menu • Using Answerset Shortcut Menus

• The History Window Shortcut Menu • Displaying the Shortcut Menu Customizing Shortcut Menus

Shortcut menus can be customized. For more information, see “Customizing Menus and Toolbars” on page 172.

Database Explorer Tree

The Database Explorer Tree can be displayed at the left or right side of the main Teradata SQL Assistant window. This pane displays each of the Data Sources to which you are connected. Opening a data source displays an alphabetical list of databases on that Data Source. Double-click a database name to expand the tree display for that database.

Use the Database Explorer Tree to reduce the time required to build a query, help reduce errors in object names, or to generate statements for a table.

(39)

Database Explorer Tree Initially, the following databases load into the Database Explorer Tree:

• The user's default database

• The User ID that was used to connect to the database (if applicable)

• The catalog database defined in the Vendors.config file for the database vendor, for example "DBC" when connecting to a Teradata data source

• Any databases that you have previously requested to be loaded for this data source Below each database name the following folders display:

• Tables • Views

• System Tables (not for Teradata) • Macros (for Teradata data sources only) • Functions

• Procedures

Viewing Object Types

The Database Explorer Tree shows the objects in the various databases shown. The following sections explain how the Explorer Tree works.

To expand the Explorer Tree to show all the objects

✔ Do one of the following:

• Double-click the database name or folder.

• Click the plus sign (+) next to the database name or folder.

• Highlight the database name or folder, and press Enter or the right arrow key. • Right-click on the database name or folder, then select Expand/Collapse on the

shortcut menu.

Note: The following notes apply to the Database Tree:

• Expanding the Tables, Views or System Tables folder loads all these object types.

• Expanding the Macros, Functions or Procedures folder loads all these object types. If no objects exist for a given object type, that folder is automatically removed.

• When an index node is expanded for a Table, the approximate index cardinality (number of unique values) appears after the column name(s). (Applies to ODBC connections only.) • When expanding the Tables node of a database any Global Temp Tables in that database

are listed in blue.

To collapse the Explorer Tree to hide all of the objects

(40)

• Click the minus sign (-) next to the database name or folder.

• Highlight the database name or folder, and press Enter or the left arrow key. • Right-click on the database name or folder, then select Expand/Collapse on the

shortcut menu.

Opening and Closing the Database Explorer Tree

To open or close the Database Explorer Tree

✔ Do one of the following: • On the Toolbar click . • Select View > Explorer Tree.

The check mark displays the status by toggling on or off.

Navigating to the Database Explorer Tree

Use the following procedure to move focus to the Database Explorer Tree.

To navigate to the Database Explorer Tree

• Select View > Go To Explorer. • Press Ctrl+G.

Changing the Width of the Tree Area

To change the width of the Explorer Tree area

✔ Click on the right edge of the display and drag it to a new position.

Moving the Explorer Tree Window

The Database Explorer Tree window operates as a separate window that can be moved around and docked to the left or right side of the parent window or can float independently.

The Database Explorer Tree also has an auto-hide feature that causes it to collapse to a bar when the mouse moves away from the window.

To move the Database Explorer Tree

✔ To move the Database Explorer Tree, do the following:

• Click the caption bar of the Database Explorer window and drag it to a desired location. When the cursor is close to the left or right edge of the main window, it

(41)

Database Explorer Tree displays a bold outline indicating that it is ready to dock to that side. Otherwise it floats when the mouse button is released.

• If the Database Tree and Favorites pane appear as tabs and you only want to move one of them, click on the tab itself and drag that to the desired location.

✔ To auto-hide the Database Explorer window, do one of the following:

• Click the push pin on the caption bar to activate auto-hide or “unpin” the Explorer Tree.

• When you move the mouse away from the Explorer Tree, the window collapses to a bar at the side of the main window. When you move the mouse over the Explorer Tree tab on the bar, the Database Explorer Tree window reopens .

• Click the push pin again to turn off auto-hide.

Dragging Object Names to the Query Pane

Click and drag the object from the Explorer Tree to the Query pane. The name of the object appears in the Query Window.

Use the Ctrl key to add a comma after the object name when it is dragged to the Query pane. To qualify object names with their parent name when dragging an object to the Query Window, first select the option Qualify names when dragged or pasted from the Database Tree, on the DB Tree tab of the Options dialog box.

Dragging Multiple Objects

Use the Shift and Ctrl keys to select more than one object from the Database Explorer Tree that can be dragged to the Query window.

Use the Ctrl key to select additional objects. Use the Shift key to select a range of objects.

Using Quick Paste

Quick paste allows you to easily move objects from the Explorer Tree to the query window.

To use quick paste

✔ Perform a quick paste in one of the following ways:

• Double-click a column name in the Database Explorer Tree to quickly move it to the Query window.

• Select the object, then right-click and select Quick Paste from the menu. The object is immediately pasted into the Query window.

(42)

Adding Double Quotes around Object Name

Request that double quotes be automatically added around any object name that is dragged, or quick pasted, from the Database Tree by checking the option Enclose names in quotes when dragged or pasted from the Database Tree on the DB Tree tab of the Options dialog box.

Adding Object Types

A newly creatd object does not appear in the Database Explorer Tree until the parent folder opens. If the parent folder is already open or has been opened during the current session, the new object does not appear until after the Refresh Database command is used, or disconnect and reconnect to the database. The same holds true when deleting an object. Table 5 shows the subfolders that are created when objects are added. For more information, see “Refreshing the Database Explorer Tree” on page 47.

Displaying Object Definitions

Use the following procedures to display the text (the DDL or data definition language) used to create a table, view, macro or stored procedure in the Database Explorer Tree. The resulting text appears in the Answerset or Query window depending on which procedure you use.

To display the text used to create an object in the Answerset window

✔ Right-click on the object, then select Show Definition.

Abort the data retrieval process at any time by clicking the Abort icon on the toolbar. The results appear in the Answerset window.

Definitions are available as follows:

• Tables - Full definition for Teradata only. Column information only for other vendors. • Views, Macros, Procedures and Functions - Teradata, Oracle, SQL Server and Sybase

Note: The definition for a Function or Procedure may not always be available.

To display the text used to create an object in the Query window

✔ Right-click on the object, then select Generate SQL>Create. Table 5: Sub Folders Created When Objects Are Added

When adding a... the following subfolder(s) are added...

Table Columns and Indexes

View Columns

Macro Function Procedure

Teradata SQL Assistant for Microsoft Windows. User Guide