Teradata C09-0001-07 Скачать руководство пользователя страница 1

Страница: 1 / 235

Содержание C09-0001-07

Страница 1: ...User s Guide C09 0001 07 Release 3 0 DBCjl012 Data Base Computer...

Страница 2: ...User s Guide C09 0001 07 Release 3 0...

Страница 3: ...on All rights reserved No part of this document may be copied or reproduced in any form or by any means without the prior written consent of Teradata Teradata Corporation 12945 Jefferson Boulevard Los...

Страница 4: ...1 1 through 1 8 07 2 1 through 2 2 07 3 1 through 3 30 07 4 1 through 4 8 07 5 1 through 5 20 07 6 1 through 6 38 07 7 1 through 7 20 07 8 1 through 8 6 07 9 1 through 9 10 07 10 1 through 10 12 07 1...

Страница 5: ...quests DCRs Reflected in this Revision DCR 3101 DCR 2214 DCR 3167 DCR 2507 DCR 3785 DCR 2775 DCR 3578 DCR 2515 Discrepancy Reports DRs Reflected in this Revision DR 7729 DR 7789 DR 8202 DR 7811 DR 820...

Страница 6: ...uide for that terminal before you read this guide ABOUT THIS DOCUMENT This document is the DBC 1012 Data Base Computer User s Guide Its purpose is to describe how to communicate with the DBC 1012 in o...

Страница 7: ...ters 6 through 11 describe the use of the DBC SQL statements themselves Appendixes provide supplemental information This guide contains four appendixes Appendix A is a fold out copy of the Personnel d...

Страница 8: ...oftware structure operating characteristics and configuration specifications DBC IOI2 Data Base Computer User s Guide document number C09 OOOI The user s guide is written for the non DP user The guide...

Страница 9: ...d Data Dictionary Directory views the second card lists ITEQ and BTEQ commands and the third card lists COBOL and PL I Preprocessor statements DBC IOI2 Data Base Computer Operator s Guide document num...

Страница 10: ...the DBC IOI2 system The manual presents the details of information flow data structures and the interface routines The manual covers CLIvI for hosts DBC IOI2 Data Base Computer Call Level Interface M...

Страница 11: ...For each set of BTEQ commands it describes when and how to use them and how the commands are related It also describes each command in detail DBC lOl2 Data Base Computer CICS Interface Manual documen...

Страница 12: ...l is written for the COBOL and PL I application programmer This manual presents details of preprocessor use and includes examples in COBOL and PL I DBC 1012 Data Base Computer Glossary document number...

Страница 13: ...xii C09 OOOl 07...

Страница 14: ...ing System Status Messages Interpreting the ITEQ Display Screen Entering Commands and Statements Ending an ITEQ Session EDITING DBC SQL STATEMENTS Entering a Statement from the Input Area Using PF Key...

Страница 15: ...Using DBC SQL Report Writing Aids Defining Summaries WITH Clause Specifying Column Format Defining Headings and Summary Titles printing a Report CREATING A REPORT USING BTEQ SUMMARY AND PREVIEW QUERYI...

Страница 16: ...REATING TABLES Specifying Column Attributes Specifying Data Type Specifying Default Control Specifying Case Specifying Format Specifying a Title Specifying Data Protection Providing for Fallback Data...

Страница 17: ...ING A MACRO 9 10 9 9 SUMMARY AND PREVIEW 9 10 CHAPTER 10 SHARING DBC I012 FACILITIES 10 1 10 1 WHAT ARE PRIVILEGES 10 1 10 2 GRANTING PRIVILEGES 10 4 10 2 1 Granting Privileges to a User 10 6 10 2 2 G...

Страница 18: ...TA DICTIONARY DIRECTORY VIEW FORMATS DEFAULT PF KEYS FOR ITEQ COMMANDS DEFINING ITEQ OUTPUT FILES DEFINING A PRINT OUTPUT FILE DEFINING A RESULT OUTPUT FILE ILLUSTRATIONS Communicating with the DBC 10...

Страница 19: ...Assignments Display Commands 3 23 BTEQ Commands 2 parts 4 2 ITEQ Format Commands 5 3 Format Characters 5 9 BTEQ Formatting Commands 2 parts 5 15 Comparison Operations 6 5 Logical Operations 6 8 Expres...

Страница 20: ...pplication program that contains requests and instructions for the DBC I012 Data Base Computer embedded directly in the source code The preprocessors are described in the DBC I012 Data Base Computer P...

Страница 21: ...LINE BATCH TRANSACTION APPLICATION SYSTEM PROGRAM Teradata 1 TOP INTERACTIVE SUBSYSTEM r r _ I I I ITEQ I L _ _ 1 1tBLOCK MULTIPLEXER ICHANNEL B Teradata V OBC 1012 DIRECT INTERACTIVE ENVIRONMENT Fig...

Страница 22: ...ts is described in Chapters 6 through 10 of this guide For more detailed information on all DBC SQL statements refer to the DBC 1012 Data Base Computer Reference Manual 1 1 HOW A DBC 1012 DATA BASE IS...

Страница 23: ...table The intersection of a column and a row is called a field For example the fourth row of the Mobile Homes table has three fields The data in its Model_Name field Is Del Fuego Figure 1 3 shows two...

Страница 24: ...8 000 00 11 55 11 27 F A H 16 0 0 t1 10012 Watson L 500 Vice Pres 56 000 00 8 43 10 03 H c S 20 0 1001 Regan R 600 Purchaser 44 000 00 10 48 10 20 F C H 16 0 to3 PI 10014 Inglls C 500 Tech Writer 34 0...

Страница 25: ...the statement and ITEQ displays he result on the terminal screen ITEQ allows you to 6 Enter Edit and Execute DBC SQL Statements You can enter and execute DBC SQL statements from the terminal If the re...

Страница 26: ...e DBC SQL statements to the DBC 1012 for processing in batch mode BTEQ commands included with the DBC SQL statements provide for session control formatting of DBC SQL results and handling of output da...

Страница 27: ...1 8 C09 OOOl 07...

Страница 28: ...kept secret to prevent another from accessing data under your username In some cases you may also need to obtain An account number This identifier is associated with your username and is used for acc...

Страница 29: ...the ITEQ FILE command during an ITEQ session to save the result of the current SELECT or EXECUTE MACRO statement a host flat file is created with a logical record length of 30004 bytes Under TSO a dat...

Страница 30: ...ed on to your organization s host computer you may start ITEQ log on to the DBC I012 and begin entering DBC SQL statements and ITEQ commands 3 1 1 Starting ITEQ If the output files needed for your ITE...

Страница 31: ...ement before executing the ITEQ LOGON command described in the next section ITEQ will display a request to please logon Warning while the ITEQ screen is displayed do not press the following keys on yo...

Страница 32: ...p of the screen If a STARTUP string is defined for you using a STARTUP clause in the CREATE USER or MODIFY USER statements Chapter 10 the processing result is displayed following this mes age The stat...

Страница 33: ...ant to check with your System Administrator to see if this logon option is available You may start ITEQ and log on to the DBC 1012 by keying only one command for example Under TSO ITEQ LOG 4 0mura H d...

Страница 34: ...ESS ITEQ is processing a command or has sent a statement to the DBC 1012 system and is waiting for a response DATA AVAILABLE More data from the last data generating READY FOR COMMAND command or statem...

Страница 35: ...tus Area 1 line 24 Figure 3 2 ITEQ Display Screen The display area normally is used to display responses to ITEQ commands and DBC SQL statements However you can also use this area to compose a lengthy...

Страница 36: ...ecuted in the host computer When executed the command is erased from the input area Depending on the command there may also be some visible change on the terminal screen to indicate execution To enter...

Страница 37: ...o IN 100 500 ORDER BY DeptNo Name END OF DATA READY FOR COMMAND Figure 3 3 Result of a SELECT Statement 3 1 6 Ending an ITEQ Session To end an ITEQ session key the command LOGOFF and press ENTER ITEQ...

Страница 38: ...and editing a long DBC SQL statement or macro from the display area may be more convenient because it enables you to view the statement in its entirety Also a statement or macro that is entered in th...

Страница 39: ...t line overlaying the cursor and erasing any characters to the right of the cursor You must assign a PF key to this command as described below REMOVE Removes the line on which the cursor is positioned...

Страница 40: ...re during processing and display of its processing message and its result If an error message or the result tells you that the statement is in error you may edit the command or statement without rekey...

Страница 41: ...your own PF key assignments To assign a PF key to an ITEQ command use the SET PFn ITEQ command For example to assign the PF23 key to the UP edit command key SET PF23 UP 1 in the input area and press E...

Страница 42: ...statement discussed in Chapter 9 is needed to convey the command to ITEQ The entire STARTUP string is enclosed by apostrophes Each SET PFn command is identified within the string by double apostrophes...

Страница 43: ...tNo IN 100 500 600 WITH SUM Salary TITLE TOTAL BY DeptNo ORDER BY Name Use the ollowing procedure to enter this statement 1 Erase the input area by pressing the PF17 key assigned to the CLEAR INPUT ed...

Страница 44: ...P edit command until the second line appears 2 Move the cursor to the incorrect word 3 Key the correct spelling over it 4 Press the PF24 key assigned to the DOWN edit command to return to the line tha...

Страница 45: ...nal keyboard 3 Key the characters 4 Press the RESET key to cancel insert mode To de1ete characters in a statement line 1 position the cursor on the first character to be deleted 2 Press the DELETE key...

Страница 46: ...ASE EOF erases a line from the cursor position to the end of the line When you enter a new statement the display area is cleared 3 2 2 Editing a Statement in the Display Area The display area is used...

Страница 47: ...appears in the display area may be printed using the PRINT command described in Chapter 5 and Appendix D 3 2 3 Aborting a Statement If you want to terminate processing of a DBC SQL statement after the...

Страница 48: ...up string as described above 3 2 3 1 Aborting a Statement Under TSO You may abort processing of the current DBC SQL statement as described in Table 3 5 Interrupt ITEQ 1 Enter the ABORT command 2 The s...

Страница 49: ...s 3110 The transaction was aborted by the user Returns normal processing result Processes new statement or command Exits ITEQ normally displays READY Interrupts terminates ITEQ Aborting a Statement Un...

Страница 50: ...tem Response Begins processing statement Interrupts ITEQ dis plays ITEQ ATTENTION HANDLING Attempts to abort processing of current statement Displays 3110 The transaction was aborted by the user Retur...

Страница 51: ...Commands ITEQ provides display commands that let you view result pages These commands are listed in Table 3 7 Any abbreviation allowed in keying a command is indicated in parentheses following the com...

Страница 52: ...ng the statement completely by executing the SHOW command assigned to the PF13 key The statement is redisplayed in the display area where it can be modified and entered using the SUBMIT command In gen...

Страница 53: ...umn headings are displayed as a single entity If the result exceeds the size of the display area you may view the result as consecutive screen pages without column headings or other embellishment Unfo...

Страница 54: ...09 Marston A 500 Secretary 22 000 00 8 53 0 10010 Reed C 500 Technician 30 000 00 4 49 0 10011 Chin M 100 Accountant 38 000 00 11 55 1 10012 Watson L 500 Vice Pres 56 000 00 8 43 1 10013 Regan R 600 P...

Страница 55: ...esult Regardless of the length of a result you may press PF20 to display each consecutive page until you reach the last page indicated by END OF DATA READY FOR COMMAND At this point pressing PF20 has...

Страница 56: ...e warns you that a report based on this result will not fit on B 5 by II inch paper which has an BO character maximum line width Note that the message also alerts you to the fact that more data is ava...

Страница 57: ...LE READY FOR COMMAND Note that the processing message remains displayed because it is formatted to the size of the display area Press PF23 once more the screen shifts right until only the HCap column...

Страница 58: ...e Before entering a subsequent query you may execute the command FILE to store the spooled result of the current SELECT statement or macro When control is returned to the interactive system this file...

Страница 59: ...3 30 C09 0001 07...

Страница 60: ...eat a request Use of more than one session for improved performance Ability to perform conditional tests and branching In addition BTEQ provides sophisticated report writing commands that allow you to...

Страница 61: ...nds refer to the DBC 1012 Data Base Computer Reference Manual Table 4 1 BTEQ Commands 1 of 2 Activity Command Function Session LOGOFF Terminates a DBC 1012 Control session without exiting BTEQ LOGON Q...

Страница 62: ...ment Limits BTEQ output to reporting only errors and request processing statistics Enables the user to include up to three lines of commentary Causes BTEQ to repeat the next DBC SQL request a specifie...

Страница 63: ...setname is the name of the load library that contains the BTEQ program ITBMAIN You may pass a parameter to BTEQ by entering CALL datasetname ITBMAIN TDP TDPO In this example the BTEQ TDP command is us...

Страница 64: ...the beginning and the end of the data set that contains the script of DBC sQL statements and BTEQ commands that defines the job RUNNING BTEQ UNDER VM CMS You may invoke BTEQ on line by entering BTEQ...

Страница 65: ...DISK filename filetype filemode Identifies the input file that contains the BTEQ input stream the script of DBC SQL statements and BTEQ commands EXTRACTING DBC IOI2 DATA TO A HOST DATA SET The exampl...

Страница 66: ...D DSN CTI SAVEDATA TEXT DISP NEW CATLG II UNIT SYSDA SPACE TRK l l RLSE II DCB LRECL 80 RECFM FB BLKSIZE 800 IISYSIN DD DATA DLM IMPORT DATA DDNAME INFILE EXPORT DATA DDNAME SAVEDATA SESSIONS 2 LOGON...

Страница 67: ...EQ to open two sessions to process the job LOGON Logs the user on to the number of DBC 1012 sessions specified by SESSIONS REPEAT Causes BTEQ to repeat the SELECT request 10 times EXPORT RESET Cancels...

Страница 68: ...the processing message is displayed by itself as the first page of a result Result data is formatted on consecutive pages each containing date page number report title and column headings Formatted pa...

Страница 69: ...umber Blanks in place of null values For the sake of illustration in this example we have shown a null value see Table 7 2 in the salary column for Greene Non suppressed repeating values for example i...

Страница 70: ...FORMAT ON Sets Format mode SFO SET FORMAT OFF Sets Unforrnat mode SFF SET NULL AS string SNA SET PAGELENGTH n SET RTITLE string SET SUPPRESS OFF ALL n n SET SUPPRESS ON ALL n n SET WIDTH n C09 OOOl 0...

Страница 71: ...er 5 1 2 2 Viewing the Effect of Format Commands You normally apply ITEQ format commands only to a result displayed in Format mode However you may apply the following format commands to a result displ...

Страница 72: ...the example result above Note the use of the double slash II character to break the title string into two lines A title may be broken into up to three lines A report title may be up to 254 characters...

Страница 73: ...reene W Jones M 50 000 00 Moffit H 35 000 00 Peterson J 25 000 00 700 Smith T 45 000 00 Note that a blank line is inserted before a value change in a column under SUPPRESS control This feature may be...

Страница 74: ...H clause allows you to specify group subtotals For example to display salary subtotals for each department add another WITH clause to the statement as follows SELECT DeptNo Name Salary FROM Employee W...

Страница 75: ...e WHERE DeptNo IN 100 700 WITH SUM Salary BY DeptNo DESC WITH SUM Salary ORDER BY Name Specifying Column Format You may change the format defined in the CREATE TABLE statement of data displayed in a r...

Страница 76: ...a in the column For example in the statement SELECT Name Salary FORMAT 9 FROM Employee WHERE DeptNo 600 AND Sa1ary 12 2500 1 the phrase FORMAT 9 eliminates the comma and decimal places defined for the...

Страница 77: ...sult is positive or negative A character translates to a blank for a positive result or to a for a negative result A dollar sign character A single character used in a FORMAT phrase places the charact...

Страница 78: ...the column headings of a result originally defined when the table was created as well as the titles of summary results using the DBC SQL TITLE phrase Use TITLE in your report statement to provide mor...

Страница 79: ...e report on narrow printer paper 80 characters by 55 lines set the line width and page length specifications by executing the following format commands WIDTH 80 PAGELENGTH 55 The WIDTH command specifi...

Страница 80: ...use your report to be printed when control is returned to the interactive system execute the PRINT command PRINT The report is printed according to system defaults determined by your DBC 1012 installa...

Страница 81: ...more comprehensive formatting capabilities than does ITEQ These include Flexibility in defining report titles and headings Ability to print a footing at the bottom of each report page Ability to speci...

Страница 82: ...the formatting mode for displaying and printing selected results SET HEADING Sets a heading for a report that appears at the top of each page SET NULL Specifies a character or string that is used to r...

Страница 83: ...ies whether a line of dash characters is printed immediately preceding a summary title as shown in the example report SET TRANSLATE Specifies whether data is trans lated from ASCII to EBCDIC before it...

Страница 84: ...sition Salary YrsExp TITLE Yearsl Experience FROM Department Employee WHERE Loc IN NYC ATL AND Salary 15000 AND Department DeptNo Emp1oyee DeptNo ORDER BY Loc Department DeptNo Name WITH SUM Sa1ary TI...

Страница 85: ...YC 100 Chin M Controller 38 000 00 11 Greene W Payroll Ck 32 000 00 15 Jones M Vice Pres 50 000 00 13 Moffit H Recruiter 35 000 00 3 Peterson J Payroll Ck 25 000 00 _ Total for Department 100 180 500...

Страница 86: ...to up to three separate sections the first left justified the second centered and the third right justified A BTEQ SET RTITLEcommand similar to the ITEQ SET RTITLE command is also available for report...

Страница 87: ...s chapter showed you how to format a report using ITEQ or BTEQ commands and DBC SQL features The next chapter shows you how to compose a DBC SQL SELECT statement so that you can query data stored on t...

Страница 88: ...For example in SELECT DeptNo Name Salary FROM Personnel Employee WHERE DeptNo IN 100 500 ORDER BY DeptNo Name SELECT is the statement keyword DeptNo Name and Salary are column names Personnel is the d...

Страница 89: ...ABLISHING A DEFAULT DATA BASE During a session with the DBC I012 you may repeatedly query define or manipulate data from the same data base To avoid keying the data base name every time you enter a DB...

Страница 90: ...tatement is described in Chapter 10 Henceforth the statement examples in this guide assume that you have established Personnel as your default data base 6 3 SELECTING COLUMNS To get data from a data b...

Страница 91: ...0 Payroll Ck Note that the left to right order of the columns in a result is determined by the order in which the column names are entered in the statement Note also that as long as a statement contai...

Страница 92: ...arch condition in this WHERE clause The comparison operators listed in Table 6 1 are routinely used to specify WHERE search conditions Form Table 6 1 Comparison Operations Meaning value1 va1ue2 value1...

Страница 93: ...ORDER BY clause of the SELECT statement For example to list the name and years of experience of each employee in Department 600 in ascending order of seniority enter SELECT Name YrsExp FROM Employee...

Страница 94: ...CT keyword allows you to specify that no two entries in a result be alike For example if you wanted a listing of employee titles for Department 500 the following statement SELECT JobTitle FROM Employe...

Страница 95: ...WHERE condition OR condition OR WHERE condition AND NOT condi tion Specifies a number of conditions that must be satisfied Specifies a number of conditions one of which must be satisfied Specifies a c...

Страница 96: ...P 28 600 00 M F Chin M 38 000 00 M F Watson L 56 000 00 S M Marston A 22 000 00 M F Moffit H 35 000 00 W F Inglis C 34 000 00 S M Leidner P 23 000 00 M F Smith T 45 000 00 S F Brangle B 30 000 00 S F...

Страница 97: ...by default the NOT operator is evaluated first in a statement then AND then OR the statement above would have the same result without the parentheses However this is not always true Parentheses are o...

Страница 98: ...atches row data Against each member of a directly specified set of values or character combinations Against a set of values or character combinations specified as the result of a SELECT statement The...

Страница 99: ...f this statement is identical to that of the statement above SELECT Name Salary DeptNo FROM Employee WHERE DeptNo 100 OR DeptNo 500 AND MStat NOT M To select rows of the Employee table in which depart...

Страница 100: ...ll within a certain range use the BETWEEN AND comparison operator in the WHERE clause of the SELECT statement For example to get a list of names salaries and titles for employees in Department 500 who...

Страница 101: ...represents any string of zero or more characters The _ character represents any single character The following examples illustrate uses of the LIKE partial string operator For example to get a listing...

Страница 102: ...select rows that satisfy a calculated condition by including arithmetic operators in the WHERE clause of the SELECT statement Arithmetic operators are listed in Table 6 4 Table 6 4 Operator MOD Arithm...

Страница 103: ...LL The result of this query is the names of all employees with a null value in the DeptNo field Because all employees contained in the Employee table have been assigned to a department no rows will be...

Страница 104: ...wo or more SELECT statements Returns result rows that are in all of answer sets generated by individual SELECT statements Subtracts the result rows generated by the second SELECT statement from the re...

Страница 105: ...A set operator cannot be used in a subquery A set operator cannot be used in a view definition 6 4 11 1 UNION Operator You may use the UNION operator to combine the results of two or more SELECT stat...

Страница 106: ...data from two different tables and merge that data into a single list SELECT Name DeptNo FROM Employee_Dept_500 UNION SELECT Name DeptNo FROM Employee_Dept_600 6 4 11 2 INTERSECT Operator The INTERSEC...

Страница 107: ...xample if the following two tables are used Table SPart Table SLocation Column SuppNo PartNo Column SuppNo SuppLoc Row 100 P2 Row 100 London 101 PI 101 London 102 PI 102 Toronto 103 P2 103 Tokyo then...

Страница 108: ...ne the suppliers in London who do not supply part PI 6 5 SELECT SuppNo FROM SLocation WHERE SuppLoc London MINUS SELECT SPart SuppNo FROM SPart SLocation WHERE SPart PartNo PI AND SPart SuppNo SLocati...

Страница 109: ...eration YrsExp 200 would be divided by 200 and the result added to Salary producing an erroneous result Note the use of the NAMED phrase in this statement to associate the arithmetic expression Salary...

Страница 110: ...lOO OO 22 000 00 65 000 00 In this statement SUM totals all values in the salary column MIN locates the minimum salary and MAX locates the maximum salary You may perform an arithmetic operation on th...

Страница 111: ...n the unique occurrences of an expression For example as a prefix operator in the following statement SELECT COUNT Sex FROM Employee WHERE Sex F COUNT provides a total of women Employees Count Sex 9 I...

Страница 112: ...ave non null department numbers Again the two new employees are not included in the count DeptNo Count DeptNo 100 4 300 3 500 7 600 4 700 3 To include the new employees in a count by department use CO...

Страница 113: ...the data type for the DOB Date of Birth column is defined as DATE The following kinds of operations may be performed on data that has the DATE data type Arithmetic addition subtraction and division Co...

Страница 114: ...1938 and August 25 1942 the following query could be entered SELECT Name DOB FROM Employee WHERE DOB BETWEEN 380307 DATE AND 420825 DATE ORDER BY DOB Name DOB Inglis C Mar 07 1938 Peterson J Mar 27 1...

Страница 115: ...atement as follows SELECT Name DaB INTEGER FROM Employee WHERE DOB BETWEEN 380307 DATE AND 420825 DATE ORDER BY DaB The display is changed as follows Name DOB Inglis C 380307 Peterson J 420327 6 7 CHA...

Страница 116: ...2 Kemper R 10006 Inglis C 10014 Leidner P 10003 Smith T 10021 Carter J 10016 Phan A 10018 Regan R 10013 Greene W 10017 Marston A 10009 Moffit H 10002 Reed C 10010 Omura H 10015 Brangle B 10020 Peterso...

Страница 117: ...been suppressed Now when the TRIM function is removed the statement SELECT Last Name I I I I First Name FROM Names returns in the string Jones Mary In this example the trailing blanks are not suppres...

Страница 118: ...The example below illustrates use of the INDEX string function SELECT Name FROM Employee WHERE INDEX Name 6 The DBC 1012 returns all employee names in which the last name is 6 characters or longer 6...

Страница 119: ...the Salary column by department number The aggregate operators AVG MIN and MAX are then applied to each group 6 8 1 Selecting Specific Groups To further refine selected data you may specify that each...

Страница 120: ...clauses ORDER BY WITH BY Note that when non aggregate groups for example DeptNo in the previous example are selected along with aggregate groups the non aggregate groups are always included in the GRO...

Страница 121: ...es a relationship between rows in different tables or views You may also want to establish a relationship between different rows in the same table or view To do this you treat the table or view as two...

Страница 122: ...00 Watson L 8 Omura H 8 500 Watson L 8 Carter J 20 500 Watson L 8 Newman P 6 600 Aguilar J 11 Aguilar J 11 600 Aguilar J 11 Regan R 10 600 Aguilar J 11 Kemper R 7 600 Aguilar J 11 Clements D 9 700 Smi...

Страница 123: ...parameter for the WHERE clause of the third query Name SELECT Name FROM Employee WHERE EmpNo 10012 Watson L Using the subquery feature of DBC SQL you may obtain the final result of these three queries...

Страница 124: ...ELECT statement places a read lock on the table or tables or for a prime key query on one or more rows of these tables that contain the data that it is querying If the SELECT statement places a read l...

Страница 125: ...SELECT statement to obtain data from DBC 1012 data bases The next chapter shows you how to use DBC SQL data definition statements to Create modify rename and drop tables Create and drop indexes Creat...

Страница 126: ...ing the following DBC SQL data definition statements CREATE ALTER DROP RENAME TABLE CREATE DROP RENAME REPLACE VIEW CREATE DROP RENAME REPLACE MACRO CREATE DROP INDEX CREATE MODIFY DROP USER DATABASE...

Страница 127: ...WEEN 1 00 AND 999000 00 YrsExp BYTEINT FORMAT Z9 BETWEEN 99 AND 99 DOB DATE FORMAT MMMBDDbYYYY NOT NULL Sex CHAR l UPPERCASE NOT NULL Race CHAR l UPPERCASE MStat CHAR l UPPERCASE EdLev BYTEINT FORMAT...

Страница 128: ...for null values Case A specification of how character data is to be stored and displayed Range An upper and lower limit on numeric data that is stored Format A specification of how numeric data is to...

Страница 129: ...ned binary whole number BYTE n A fixed length binary string of n bytes A byte is an a bit element of data that is stored on the DBC lOl2 without being translated into an internal representation SMALLI...

Страница 130: ...te notation for example Format Display DDBMMMBYYYY 03 Jul 1984 YY MM DD 84 07 03 MM DD YY 07 03 84 MMMBDD YY Ju103 84 DD MM YYYY 03 07 1984 In addition a day within year format using DDD for day with...

Страница 131: ...or occurs If a null or an expression that evaluates to null is inserted in a numeric field for which a DEFAULT value has been specified a null is inserted instead of the value unless NOT NULL has been...

Страница 132: ...phrase discussed in Chapter 5 For example the specification for the Name column in the CREATE TABLE statement above might be entered as Name CHAR 12 NOT NULL TITLE EmployeeIIName The phrase TITLE Empl...

Страница 133: ...K is specified you must specify the FALLBACK option in your CREATE TABLE statement Conversely if you do not want to create a fallback copy of a new table in a data base or user space for which FALLBAC...

Страница 134: ...a high percentage of compressed values or if the column is type BYTEINT SMALLINT or CHAR n if n is a small number 7 1 4 Establishing Indexes The CREATE TABLE statement lets you establish indexes duri...

Страница 135: ...such as joins However such an advantage is not achieved without a tradeoff Every secondary index defined for a table requires a secondary index subtable Secondary index subtables take up extra space o...

Страница 136: ...ion from the Employee table enter the following two statements in sequence CREATE TABLE Manager NO FALLBACK EmpNo INTEGER FORMAT ZZZZ9 Name VARCHAR 12 NOT NULL UNIQUE PRIMARY INDEX EmpNo INSERT INTO M...

Страница 137: ...mpMonthSal 7 3 ALTERING A TABLE DEFINITION After a table is created you may change its definition in the following ways 1 Add or drop one or more columns from the table 2 Change the default control fo...

Страница 138: ...g statement ALTER TABLE Employee DROP HCap removes the HCap column from the Employee table To enter an ALTER TABLE statement for a table you must have the drop privilege for the table 7 3 2 Changing A...

Страница 139: ...To add a single copy of an after image journal enter ALTER TABLE NewEmployee AFTER JOURNAL 7 3 5 Changing the Data Type Attribute To change the data type attribute for one or more columns of a table u...

Страница 140: ...W TABLE Employee sets the display area for input and displays a synthesized CREATE TABLE statement for the Employee table When the CREATE TABLE statement is displayed you may change the statement as d...

Страница 141: ...strain the values that are inserted or updated in the underlying table Enable other users to insert update or delete specified data in a table as described in Chapter 8 Though it may display the same...

Страница 142: ...ement specifies a join between Employee and Department tables The WHERE clause specifies the condition for the join on DeptNo and excludes information for vice presidents and managers Different names...

Страница 143: ...tatement to allow users to concurrently update and query the same table In this instance an ACCESS lock which allows users to query a table that has been locked for write access is specified in a CREA...

Страница 144: ...view in the display area where you may change it using ITEQ edit commands 7 5 DOCUMENTING TABLES COLUMNS VIEWS After creating a table or a view you may define and store a descriptive comment about th...

Страница 145: ...view you must qualify its name for example COMMENT ON Personnel Employee 7 6 RENAMING TABLES AND VIEWS You may rename an existing table or view using the RENAME statement For example to change the na...

Страница 146: ...ABLE Employee To remove the Emplist view enter DROP VIEW Emplist You must have the drop privilege on the table or view to remove it 7 8 SUMMARY AND PREVIEW This chapter showed you how to work with tab...

Страница 147: ...7 22 C09 OOOl 07...

Страница 148: ...following ways By directly specifying the row data to be inserted By retrieving from another table the row data to be inserted Data inserted in a table must conform to the following constraints In gen...

Страница 149: ...d 8 1 2 Inserting Data by Query You may insert rows in a table by selecting rows from another table or a view For example assume that you have created a table named Promotion to identify employees who...

Страница 150: ...employee Leidner enter UPDATE Employee SET JobTitle Secretary Salary 23000 YrsExp 13 Race C MStat M EdLev 16 HCap O WHERE EmpNo 10003i You use a WHERE clause to specify one or more rows to which data...

Страница 151: ...separate data base these employees could be deleted from the Employee table as follows 8 4 DELETE FROM Employee WHERE Employee DeptNo Department DeptNo AND Department Loc NYC USING A VIEW TO ADD OR CH...

Страница 152: ...ATE Staff_Info SET Department 300 WHERE Number 10024 changes the department number from 200 to 300 entered for Crowell in the preceding INSERT statement To update a table via a view you must have the...

Страница 153: ...serted or changed through the view must satisfy any constraints specified in the WHERE clause A view is a useful device for allowing other users to access table data However as the preceding examples...

Страница 154: ...mmands Parameters that are specified each time the macro is executed Regardless of the number of DBC SQL statements in a macro the DBC 1012 Data Base Computer treats the macro as a single transaction...

Страница 155: ...ample assume that you have entered the statement shown below to create a macro named NewEmp in Personnel the default data base CREATE MACRO NewEmp number INTEGER name VARCHAR 12 dept INTEGER 100 TO 90...

Страница 156: ...rameter value in the EXECUTE statement that does not conform to specified formats data types and defaults an error message is displayed For more information about default controls refer to Chapter 3 o...

Страница 157: ...ny user may display it using the COMMENT statement To display the stored comment on NewEmp enter COMMENT ON NewEmp Note that if you enter a COMMENT statement for a macro table or view that has the sam...

Страница 158: ...at supplies its name and required parameters If you have created the macro for another user you must grant to that user the privilege of executing it For example entering the statement EXECUTE NewEmp...

Страница 159: ...acro is executed in Unformat mode column headings for each statement result appear only once at the beginning of the result Processing messages and results that exceed the size of a single display scr...

Страница 160: ...st recent REPLACE MACRO statement used to modify it would be displayed Using the edit commands described in Chapter 3 you now modify the CREATE MACRO statement as follows so that NewEmp may be used to...

Страница 161: ...me of the data base in which the macro is contained For example to rename the macro NewEmp to AddEmp as in the previous example if your default data base setting is other than Personnel enter the foll...

Страница 162: ...or failure of macro execution and the text of any echoed remark before displaying the result processing messages for individual statements within the macro are suppressed When a macro is executed in...

Страница 163: ...ou must have the drop privilege for the macro refer to Chapter 10 9 9 SUMMARY AND PREVIEW This chapter showed you how to use macros The next chapter shows you how to use data definition statements to...

Страница 164: ...e data base called DBC DBC is usually managed by the system administrator To protect the security of system tables within DBC that are used by DBC 1012 software the system administrator usually assign...

Страница 165: ...macros from a data base SELECT Query data in a table or view INSERT UPDATE and DELETE Insert modify and remove rows from a table EXECUTE Execute a macro in a data base GRANT Grant any privilege to ano...

Страница 166: ...e new data base Jones in turn allocates portions of the Finance data base to create Personnel and other department data bases as well as users Chin and Greene as shown in Figure 10 1 Figure 10 1 Creat...

Страница 167: ...ata bases and lsers Examples in the chapter assume that you have been granted the necessary privileges 10 2 GRANTING PRIVILEGES fter being granted one privilege you may automatically have ther privile...

Страница 168: ...to grant the privilege INSERT Insert for table When a view is used to insert table rows Insert privilege for view is needed MODIFY DATABASE Drop Data Base MODIFY TABLE Drop Table MODIFY USER Drop Use...

Страница 169: ...view created in Chapter 6 To grant limited select insert and update privileges on Employee to Peterson and Moffit enter the following statement GRANT SELECT INSERT UPDATE ON Employee_Info TO Peterson...

Страница 170: ...base enter GRANT ALL BUT CREATE TABLE ON Greene TO Peterson 10 2 3 Granting privileges to a Group of Users Use the ALL keyword to grant one or more privileges to a group of users For example to enable...

Страница 171: ...from space belonging to the system administrator The FALLBACK option specifies that a secondary copy of any table created in Jones user space is maintained in addition to the primary copy for use in...

Страница 172: ...12 The DEFAULT DATABASE option identifies Finance as the default data base for every DBC SQL statement that Jones enters after logging on This eliminates the need for Jones to qualify with Finance the...

Страница 173: ...s AS PASSWORD VP Except for the new password Jones user definition remains as specified in th CREATE USER statement above If Jones wishes to increase the disk space as well as the space allocated to s...

Страница 174: ...ting tables that use the original default journal option will retain the original journal setting To change the journal option for an existing table you must explicitly change it in an ALTER TABLE sta...

Страница 175: ...IP To delegate responsibility for all Personnel matters to controller Chin Jones transfers ownership of the Personnel data base to Chin using the following GIVE statement GIVE Personnel to Chin Jones...

Страница 176: ...tionary Directory information in two ways 1 By querying system defined views of Data Dictionary Directory tables 2 By entering the DBC SQL HELP statement for a data object This chapter shows you how t...

Страница 177: ...ent or the HELP statement described below you may obtain a description of any of these views For example entering COMMENT ON DBC UserGrantedRights displays the following description The DBC UserGrante...

Страница 178: ...pe of journal table maintained for tables created in a data base PermSpace Specifies the total space in bytes allocated to the data base CommentString Contains the text of any user supplied descriptio...

Страница 179: ...tion is in effect for the table as follows Y yes fallback is the default N no no fallback is the default JournalFlag Specifies the default for the type of journal table maintained for the table Creato...

Страница 180: ...g columns are defined DataBaseName Identifies the data base in which the table view or macro resides TableName Identifies the table view or macro ColumnName Names the column or parameter ColumnFormat...

Страница 181: ...the column or parameter DecimalTota1Digits Specifies the total number of digits left and right of the decimal point for a decimal field DecimalFractiona1Digits Specifies the number of fractional digit...

Страница 182: ...vides information about a privilege that you have granted to another user on a data base table view or macro The following columns are defined DataBaseName Gives the name of the data base on which the...

Страница 183: ...that you are Jones have granted to other users enter SELECT DataBaseName TableName Grantee AccessRight FROM DBC UserGrantedRights ORDER BY 1 2 3 4 The result is shown below DataBaseName TableName Gra...

Страница 184: ...tion about privileges that you have been granted on the Personnel data base enter table that have been granted to you enter SELECT DataBaseName TableName AccessRight FROM DBC UserRights WHERE DataBase...

Страница 185: ...enter SELECT FROM DBC SessionInfo WHERE UserName Greene The result is shown below UserName AccountName SessionNo DefaultDataBase Partition Greene F A OOOOEE03 Personnel DBC SQL 11 2 USING THE HELP ST...

Страница 186: ...wner of the object or have any privilege on it When HELP DATABASE is entered the DBC 1012 lists all tables views and macros in the specified data base and the following information about each Name Typ...

Страница 187: ...INDEX statement is used to provide information about all the indexes or a selected index of a table The response to the HELP INDEX statement includes the following information about each index Unique...

Страница 188: ...st requirel Employee number required Department number required Title or position Annual base salary Years of experience The display is truncated other fields occur to the right The statement HELP COL...

Страница 189: ...11 14 C09 OOOI 07...

Страница 190: ...9 78 08 08 UNIQUE PRIMARY INDEX EmpNo 10014 OEI 0002 83 01 H 20 0 AP1 0001 A P Payab1 DB Deslgn 82 08 09 83 04 10 83 04 10 INDEX Name 10002 OEI 0001 83 04 15 33 5 ENG 0003 Deslgo idg t rame 18 01 02 8...

Страница 191: ......

Страница 192: ...cters indicate that a value or name is to be substituted in their place Underscores indicate the default value Special characters including blanks are required as shown unless specifi d otherwise Brac...

Страница 193: ...name datadesc ADD cname datadesc DROP cname DROP cname Any of the following options may be listed in any order NO FALLBACK PROTECTION NO AFTER JOURNAL DUAL BEFORE WITH JOURNAL TABLE tname BEGIN TRANSA...

Страница 194: ...ENT n BYTES option option Any of the following options may be listed in any order SPOOL n BYTES ACCOUNT aeetid NO FALLBACK PROTECTION NO AFTER JOURNAL DUAL BEFORE DEFAULT JOURNAL TABLE tname CREATE UN...

Страница 195: ...PROTECTION NO AFTER JOURNAL DUAL BEFORE WITH JOURNAL TABLE tname CREATE USER username FROM ownerdb AS PERM ANENT n BYTES PASSWORD name opt i on opt i on Any of the following options may be listed SPOO...

Страница 196: ...ame IN READ WRITE SELECT expr expr FROM tname aname tname aname WHERE eond DATABASE dbname WHERE eond DEL ETE FROM tname aname ALL DATABASE DEL ETE dbname USER DATABASE DROP name USER DROP INDEX ename...

Страница 197: ...ALL username WITH GRANT OPTION DATABASE dbname USER username TABLE tname VIEW viewname MACRO maeroname COLUMN ename enamel FROM tname tname COLUMN FROM tname HELP COLUMN tname cname ename COLUMN tname...

Страница 198: ...T JOURNAL TABLE tname DROP DEFAULT JOURNAL TABLE tname MODIFY USER username AS option option Any of the following options may be listed in any order PERM ANENT n BYTES PASSWORD name SPOOL n BYTES STAR...

Страница 199: ...e cname enamel AS ACCESS DATABASE dbname FOR EXCL USIVE LOCK ING TABLE tname SHARE MODE VIEW vname IN READ WRITE SEL ECT expr expr FROM tname aname tname aname WHERE cond ALL PRIVILEGES REVOKE privile...

Страница 200: ...tname aname WHERE cond GROUP BY expr expr HAVING cond DESC DESC ORDER BY expr expr ASC ASC WITH summarylist BY breaklist J MACRO macroname SHOW TABLE tname VIEW viewname UPD ATE tname aname SET cname...

Страница 201: ...lphabetically These modifiers can be used with any DBC SQL statement EXPLAIN statement ACCESS DATABASE dbname FOR EXCL USIVE LOCK ING TABLE tname SHARE MODE VIEW vname IN READ WRITE statement USING na...

Страница 202: ...Q COMMANDS ITEQ commands are listed alphabetically Defaults are underscored ABORT ADD BACKWARD SKIP n BWD 1 BWDS CAN CEL CLEAR INPUT n DO WN 3 FILE name FORWARD SKIP n FWD 1 FWDS INPUT JOIN C09 0001 0...

Страница 203: ...n LE FT 52 LOGOFF LOG ON tdpid username password acctid PRINT name QUIT RECALL REMARK string RMK REM OVE n RI GHT 52 SET DEFAULTS SD ON SET FORMAT OFF SFO SFF B 12 C09 0001 07...

Страница 204: ...INPUTAREA SIZE n SIS 3 SET NULL AS string SNA SET PAGELENGTH n SPL 55 SET PFn command SET RTITLE string SRT ON SET SUPPRESS OFF n SSF SSO ALL SET WIDTH n SW 132 SHO W SHOW CONTROL J SC C09 OOOl 07 n n...

Страница 205: ...SHOW SM SV ST SPLIT SUB MIT n UP 3 B 14 MACRO VIEW TABLE objname C09 000l 07...

Страница 206: ...SET DATA DDNAME ddname LIMIT n INDICDATA DDNAME ddname LIMIT n EXPORT REPORT DDNAME ddname LIMIT n DIF DATALABELS DDNAME ddname RESET n GOTO 1abe1name HANG n HELP BTEQ ERRORCODE IF operator n THEN com...

Страница 207: ...UIT n EXIT ERRORCODE REMARK string string string n REPEAT RUN DDNAME ddname SKIP n SET DEFAULTS OFF SET ECHOREQ ON OFF n n SET FOLDLINE ON ALL SET FOOTING string OFF SET FORMAT ON SET HEADING string O...

Страница 208: ...g OFF n n SET OMIT ON ALL n OFF n n SET PAGEBREAK ON ALL SET PAGELENGTH n OFF SET QUIET ON OFF SET RECORDMORE ON SET RETLIMIT n OFF SET RETRY ON SET RTITLE string SET SEPARATOR string n SET SESSIONS n...

Страница 209: ...FF n n n SET SKIPDOUBLE ON ALL OFF n n SET SKIPLINE ON ALL OFF n n SET SUPPRESS ON ALL OFF n n SET TITLEDASHES n n n OFF SET TRANSLATE ON OFF n n SET UNDERLINE ON ALL SET WIDTH n SHOW CONTROL S SHOW V...

Страница 210: ...TDP nn nnnnnn optional form for VM users only TSO string C09 0001 07 B 19...

Страница 211: ...age AccountName UserName CPUTime DiskIO DBC Children Child Parent DataBaseName TableName ColumnName ColumnFormat ColumnTitle ColumnType DBC Columns ColumnLength DefaultValue RangeLow RangeHi Nullable...

Страница 212: ...aved IndexPresent DupeDumpSet CreateDate CreateTime EventNum EventType DBC Events_Configuration UserName LogProcessor DBC Events_Media PhyProcessor ProcessorState RestartSeqNum CreateDate CreateTime E...

Страница 213: ......

Страница 214: ......

Страница 215: ...tectionType JournalFlag CreatorName RequestText CommentString AMP DataBaseName AccountName DBC TableSize TableName CurrentPerm PeakPerm DataBaseName TableName DBC UserGrantedRights Grantee AccessRight...

Страница 216: ...convenience this appendix lists all default PF key assignments in one place so that you need not refer back and forth between tables in different chapters In the tables below the heading 87 key desig...

Страница 217: ...Table C 2 Default PF Keys for Display Commands 87 Key 75 Key Command _ _ PFl9 PF7 BACKWARD PF20 PF8 FORWARD PF22 PFIO LEFT PF23 PFll RIGHT C 2 C09 OOOI 07...

Страница 218: ...KS ITEQDSKS DATA ITEQDSK6 ITEQDSK6 DATA ITEQDSK7 ITEQDSK7 DATA ITEQDSK8 ITEQDSK8 DATA Use ddnames ITEQPRTI through ITEQPRT3 file names ITEQPRTI DATA through ITEQPRT3 DATA to define print output files...

Страница 219: ...format Assigns the file to output class B After defining a print output file during your ITEQ session you may send the current result to be printed on 8 5 by II inch paper by executing the following I...

Страница 220: ...hen be kept and cataloged for later use For example you may establish file attributes and define an output file during your ITEQ session using the following commands Under TSO tso attrib dbcparms lrec...

Страница 221: ...n as discussed above the result is stored and catalogued in your directory as ITEQDSK2 ITEQDSK2 DATA AS For complete information about the ITEQ FILE command refer to the DBC 10I2 Data Base Computer Re...

Страница 222: ...used with LIKE operator 6 14 character used with LIKE operator 6 14 ANY operator 6 ll ABORT command 3 18 3 21 ABORT statement 3 l8 9 4 9 5 access lock 6 37 account identifier 2 1 3 3 11 10 ACCOUNT op...

Страница 223: ...l see also COBOL Preprocessor l l arithmetic expressions 6 l5 6 21 date 6 26 6 27 in UPDATE statement 8 3 views 7 15 with aggregate result 6 23 arithmetic operators see arithmetic expressions AS keyw...

Страница 224: ...9 3 columns see also relational data base adding and dropping 7 l2 attributes 7 2 7 5 7 10 changing attributes 7 l2 7 13 default control 7 6 format 7 6 title 7 6 Columns view 11 2 11 5 comma 6 l 8 2...

Страница 225: ...data type 7 5 DBC SQL 1 1 DBC 1012 Data Base Computer 1 l 7 1 DECIMAL n m data type 7 4 default control phrases 7 6 default data base see CREATE USER and DATABASE statements default database MODIFY U...

Страница 226: ...BLE statement 7 6 7 7 CREATE USER statement lO 8 fie1d see relational data base FILE command 2 2 3 29 FLOAT data type 7 4 format characters 5 l0 format commands BTEQ 5 l5 5 17 5 19 ITEQ 5 3 5 4 macro...

Страница 227: ...command 3 10 INSERT statement 8 2 8 5 embedded query 7 10 in a view 8 4 8 5 macro 9 2 restrictions 8 1 INTEGER data type 7 4 Interactive Teradata Query see ITEQ INTERSECT operator 6 17 6 19 IS NULL o...

Страница 228: ...7 CREATE USER statement lO 8 MODIFY USER statement lO lO Journals view 11 2 language preprocessors see COBOL Prepocessor LEFT command 3 22 3 28 5 2 LIKE operator 6 l4 6 15 6 34 locking table 6 37 CREA...

Страница 229: ...15 NULL phrase 7 6 null values default 7 3 8 2 default blank in report 5 2 search criteria 6 16 specifying character for report 5 5 OR operator 6 9 ORDER BY clause 6 6 6 7 6 32 6 33 ownership data bas...

Страница 230: ...printing a report 5 l2 5 13 privilege codes ll 8 privileges lO l 10 3 for statement entry 10 4 10 5 granting 10 4 implicit 10 5 processing message 3 7 3 11 3 24 3 26 5 1 9 9 Format mode 5 1 9 6 Unfor...

Страница 231: ...abbreviation for SELECT 6 4 SELECT statement 3 S 3 22 6 3 6 9 6 12 6 21 6 23 6 32 6 33 BTEQ 4 7 embedded query 8 2 self join 6 34 6 35 semicolon 3 3 4 2 6 1 9 3 SessionInfo view ll 2 11 9 SET DEFAULT...

Страница 232: ...a result 2 2 D 1 string expressions 6 28 concatenation operator 6 28 string functions 6 28 string functions 6 28 INDEX 6 30 6 31 SUBSTR 6 30 SUBMIT command 3 10 3 17 3 18 3 23 subqueries 6 36 6 37 SU...

Страница 233: ...24 5 3 5 5 display commands 5 l macro result 9 6 9 9 UNION operator 6 l7 6 lB unique index 7 l0 UP command 3 l0 3 12 3 15 UPDATE statement 8 3 constraints B 3 view B 5 UPPERCASE option 7 6 user identi...

Страница 234: ...4 print file 5 13 D l D 2 PROFILE EXEC 3 4 storing results 3 29 D 3 WHERE clause 6 5 6 12 in a join 6 34 in a self join 6 35 in UPDATE statement 8 3 WIDTH command 5 12 WITH clause 5 7 5 8 5 11 specify...

Страница 235: ......

Результаты поиска

Содержание C09-0001-07

Отзывы:

Бренды по названию

Популярные бренды

Teradata C09-0001-07, Руководство пользователя

Результаты поиска

Содержание C09-0001-07

Отзывы:

Бренды по названию

Популярные бренды